目录
Python操作MySQL高效封装：告别重复代码，一键执行SQL
摘要：本文将教你如何封装mysql-connector-python，实现传入SQL字符串即可返回结果的通用函数，彻底解决数据库操作中的代码冗余问题！
一、为什么需要封装数据库操作？
在Python操作MySQL时，我们经常遇到这样的痛点：
# 传统方式：每次操作都要重复这些步骤
conn = mysql.connector.connect(...)
cursor = conn.cursor()
try:
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
finally:
cursor.close() # 容易忘记关闭连接！
conn.close() # 资源泄露风险！
重复代码带来的问题：
- 连接管理代码占用了60%以上的数据库操作篇幅
- 忘记关闭连接会导致数据库连接耗尽
- 异常处理分散在各处，难以统一管理
- 业务逻辑与基础设施代码混杂，可读性差
二、通用数据库操作函数封装
完整封装方案（支持查询与非查询操作）
import mysql.connector
from mysql.connector import Error
from typing import Union, List, Tuple, Dict, Any
class MySQLExecutor:
"""MySQL操作通用封装类"""
def __init__(self, config: Dict[str, Any]):
"""
初始化数据库连接配置
:param config: 数据库配置字典
"""
self.config = config
self.connection = None
def __enter__(self):
"""支持with上下文管理"""
self.connect()
return self
def __exit__(self, exc_type, exc_val, exc_tb):
"""退出时自动关闭连接"""
self.close()
def connect(self):
"""建立数据库连接"""
try:
self.connection = mysql.connector.connect(**self.config)
print("数据库连接成功!")
return True
except Error as e:
print(f"连接数据库失败: {e}")
return False
def close(self):
"""关闭数据库连接"""
if self.connection and self.connection.is_connected():
self.connection.close()
print("数据库连接已关闭")
def execute_query(
self,
sql: str,
params: Union[Tuple, List, None] = None,
dictionary: bool = False
) -> Union[List[Tuple], List[Dict]]:
"""
执行查询语句（SELECT）
:param sql: SQL查询语句
:param params: 查询参数（可选）
:param dictionary: 是否返回字典格式
:return: 查询结果列表
"""
if not self.connection:
self.connect()
try:
with self.connection.cursor(dictionary=dictionary) as cursor:
cursor.execute(sql, params)
return cursor.fetchall()
except Error as e:
print(f"查询执行失败: {e}")
raise
finally:
# 注意：不关闭cursor，with语句自动处理
pass
def execute_command(
self,
sql: str,
params: Union[Tuple, List, None] = None,
commit: bool = True
) -> int:
"""
执行非查询语句（INSERT/UPDATE/DELETE）
:param sql: SQL操作语句
:param params: 操作参数（可选）
:param commit: 是否自动提交事务
:return: 受影响的行数
"""
if not self.connection:
self.connect()
try:
with self.connection.cursor() as cursor:
cursor.execute(sql, params)
affected_rows = cursor.rowcount
if commit:
self.connection.commit()
return affected_rows
except Error as e:
print(f"操作执行失败: {e}")
self.connection.rollback()
raise
三、封装后的使用示例
1. 基础使用方式
# 数据库配置
db_config = {
"host": "localhost",
"user": "your_username",
"password": "your_password",
"database": "your_database",
"charset": "utf8mb4", # 支持中文
"pool_size": 5, # 连接池大小
"pool_name": "mypool" # 使用连接池
}
# 创建执行器实例
executor = MySQLExecutor(db_config)
# 执行查询（元组格式）
users = executor.execute_query("SELECT * FROM users WHERE age > %s", (20,))
print("查询结果（元组）:", users[:2]) # 打印前两条
# 执行查询（字典格式）
products = executor.execute_query(
"SELECT name, price FROM products WHERE category = %s",
('电子产品',),
dictionary=True
)
print("查询结果（字典）:", products[0])
# 执行插入操作
new_user = ("张三", 25, "zhangsan@example.com")
insert_sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
affected = executor.execute_command(insert_sql, new_user)
print(f"插入成功，影响行数: {affected}")
# 关闭连接（可选，类内部已处理）
executor.close()
2. 更优雅的with语句方式
# 使用with上下文管理器（自动处理连接）
with MySQLExecutor(db_config) as executor:
# 执行批量插入
employees = [
("李四", "开发部", 28),
("王五", "市场部", 32),
("赵六", "财务部", 45)
]
insert_sql = "INSERT INTO employees (name, department, age) VALUES (%s, %s, %s)"
# 关闭自动提交（事务处理）
executor.execute_command("START TRANSACTION", commit=False)
try:
for emp in employees:
executor.execute_command(insert_sql, emp, commit=False)
# 手动提交事务
executor.execute_command("COMMIT", commit=False)
print("批量插入成功!")
except Exception as e:
executor.execute_command("ROLLBACK", commit=False)
print(f"操作失败已回滚: {e}")
四、封装的高级技巧
1. 结果集转换装饰器
def to_dict(func):
"""将元组结果转换为字典的装饰器"""
def wrapper(*args, **kwargs):
result = func(*args, **kwargs)
if not result:
return []
# 获取字段名
with args[0].connection.cursor() as cursor:
cursor.execute("SELECT 1") # 仅用于获取描述信息
columns = [col[0] for col in cursor.description]
return [dict(zip(columns, row)) for row in result]
return wrapper
# 在类中使用装饰器
class AdvancedMySQLExecutor(MySQLExecutor):
@to_dict
def query_to_dict(self, sql: str, params=None):
"""自动转换为字典格式的查询"""
return self.execute_query(sql, params)
2. 连接池优化配置
# 在配置中添加连接池参数
pool_config = {
**db_config,
"pool_name": "mypool",
"pool_size": 10, # 连接池大小
"pool_reset_session": True,
"autocommit": False # 建议手动控制事务
}
# 使用连接池
with MySQLExecutor(pool_config) as executor:
# 执行操作...
3. 支持异步操作（Python 3.7+）
import asyncio
from mysql.connector import pooling
class AsyncMySQLExecutor:
"""异步MySQL执行器"""
def __init__(self, config):
self.pool = pooling.MySQLConnectionPool(**config)
async def execute(self, sql, params=None):
loop = asyncio.get_running_loop()
conn = self.pool.get_connection()
try:
with conn.cursor() as cursor:
await loop.run_in_executor(
None,
lambda: cursor.execute(sql, params)
)
if sql.strip().lower().startswith('select'):
return await loop.run_in_executor(None, cursor.fetchall)
else:
conn.commit()
return cursor.rowcount
finally:
conn.close()
五、封装带来的优势
-
代码复用率提升
数据库操作代码减少70%，核心业务逻辑更突出 -
资源管理自动化
自动处理连接关闭，避免资源泄露 -
统一异常处理
集中管理所有数据库操作异常 -
灵活的事务控制
支持手动/自动提交模式切换 -
多种返回格式
自由选择元组或字典格式结果集 -
线程安全
通过连接池支持并发操作
六、封装使用注意事项
-
SQL注入防护
务必使用参数化查询（%s占位符），避免直接拼接SQL字符串 -
长连接管理
长时间空闲的连接需设置wait_timeout或使用连接池 -
事务边界控制
复杂业务逻辑中合理划分事务范围 -
连接池限制
避免创建过多连接池（每个进程一个连接池最佳） -
二进制数据编码
处理BLOB类型数据时需指定binary_prefix=True
# 二进制数据安全处理示例
with MySQLExecutor(db_config) as exe:
# 读取图片
with open('photo.jpg', 'rb') as f:
image_data = f.read()
# 插入数据库
exe.execute_command(
"INSERT INTO images (name, data) VALUES (%s, %s)",
('avatar', image_data),
commit=True
)
结语：通过合理封装mysql-connector-python，我们不仅消除了重复代码，还实现了更安全、高效的数据库操作模式。这种封装思想同样适用于其他数据库驱动（如psycopg2、pymssql等），是Python工程师必备的高级技能！
扩展建议：
- 集成配置管理（如从环境变量读取数据库配置）
- 添加SQL执行日志记录功能
- 实现数据库迁移脚本管理
- 整合到Web框架（Flask/Django）作为扩展
GitHub参考项目：
关键词：
Python MySQL封装、mysql-connector高级用法、数据库连接池、SQL执行封装、Python数据库最佳实践
扫一扫
2340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
