python MySQL执行数据库查询

from common.log import LogManager
from common.read_config import ReadConfig
import pymysql
from pymysql import Error as PyMySQLError
from typing import Optional, Any, List, Tuple

localReadConfig = ReadConfig()


class MyDB:
    def __init__(self, env: str):
        """数据库操作类
        Args:
            env (str): 环境标识 (e.g., 'dev', 'prod')
        """
        self.env = env
        self.logger = LogManager('size').get_logger()
        self._connection = None  # 类型: Optional[pymysql.Connection]
        self._cursor = None      # 类型: Optional[pymysql.cursors.Cursor]
        self.config = self._load_config()
        self.logger.debug("Initialized database client for env: %s", env)

    def _load_config(self) -> dict:
        """加载并验证数据库配置"""
        try:
            return {
                'host': str(localReadConfig.get_db(self.env, "host")),
                'user': localReadConfig.get_db(self.env, "username"),
                'passwd': localReadConfig.get_db(self.env, "password"),
                'port': int(localReadConfig.get_db(self.env, "port")),
                'db': localReadConfig.get_db(self.env, "database"),
                'charset': 'utf8mb4',
                'cursorclass': pymysql.cursors.DictCursor,  # 返回字典格式结果
                'autocommit': True
            }
        except ValueError as e:
            self.logger.error("Configuration error: %s", e)
            raise
        except KeyError as e:
            self.logger.error("Missing configuration key: %s", e)
            raise

    def connect(self) -> None:
        """建立数据库连接"""
        if self.is_connected:
            self.logger.warning("Connection already exists")
            return

        try:
            self._connection = pymysql.connect(**self.config)
            self._cursor = self._connection.cursor()
            self.logger.info(f"Connected to {self.config['db']}@{self.config['host']}")
        except PyMySQLError as e:
            self.logger.error("Connection failed (Error %s): %s", e.args[0], e)
            self.close()
            raise RuntimeError(f"Database connection failed: {e}") from e

    @property
    def is_connected(self) -> bool:
        """检查当前连接状态"""
        return self._connection is not None and self._connection.open

    def close(self) -> None:
        """安全关闭数据库连接"""
        if self._cursor:
            self._cursor.close()
            self._cursor = None
        if self._connection:
            self._connection.close()
            self._connection = None
            self.logger.info("Database connection closed")

    def __enter__(self):
        """支持上下文管理器"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """退出上下文时自动关闭连接"""
        self.close()

    def _ensure_connected(self) -> None:
        """确保处于已连接状态"""
        if not self.is_connected:
            self.logger.warning("Attempting to reconnect...")
            self.connect()

    def fetch_one(self, query: str, params: tuple = None) -> Optional[dict]:
        """查询单条记录
        Args:
            query: SQL查询语句
            params: 查询参数
        Returns:
            字典形式的单条记录或None
        """
        try:
            self._ensure_connected()
            self._cursor.execute(query, params or ())
            return self._cursor.fetchone()
        except PyMySQLError as e:
            self.logger.error("Query failed (Error %s):\nSQL: %s\nParams: %s",
                            e.args[0], query, params)
            raise
        except AttributeError:
            self.logger.error("Cursor not initialized")
            raise RuntimeError("Database connection not established")

    def fetch_all(self, query: str, params: tuple = None) -> List[dict]:
        """查询所有记录
        Args:
            query: SQL查询语句
            params: 查询参数
        Returns:
            字典形式的记录列表
        """
        try:
            self._ensure_connected()
            self._cursor.execute(query, params or ())
            return self._cursor.fetchall()
        except PyMySQLError as e:
            self.logger.error("Query failed (Error %s):\nSQL: %s\nParams: %s",
                            e.args[0], query, params)
            raise


if __name__ == '__main__':
    select_sql = f"""SELECT id,name FROM user WHERE name = %s and age =  %s"""
    paramas = (test1, test2)
    # 使用上下文管理器自动管理连接
    try:
        with MyDB('env6') as db:
            id= db.fetch_one(select_sql, paramas)
        return id["id"]
    except RuntimeError as e:
        print(f"Connection error: {e}")
    except PyMySQLError as e:
        print(f"Query error: {e}")

此代码经过ds优化后的结果