前言
在现代 Web 开发与数据工程中，数据库几乎是每一个后端系统的核心组件。MySQL 作为世界上最流行的开源关系型数据库，与 Python 的组合被广泛应用于 Web 后端、数据分析、爬虫存储、业务系统等场景。
本文是《Python 中间件系列》的 MySQL 专题篇。我们将从最基础的连接建立讲起，逐步深入到连接池管理、事务控制、批量操作、ORM 映射、错误处理等高级主题，每一个知识点都配有完整可运行的代码示例及真实的打印输出，帮助你建立对 Python 操作 MySQL 的系统性认知。
目录
- 环境准备与驱动选择
- 基础连接：
mysql-connector-python - 基础连接：
PyMySQL - CRUD 完整示例
- 参数化查询与 SQL 注入防护
- 事务管理（Transaction）
- 连接池（Connection Pool）
- 批量操作与性能优化
- 使用上下文管理器封装
- SQLAlchemy ORM 操作
- 数据库结构操作（DDL）
- 异常处理与重试机制
- 实战案例：用户系统数据层
- 最佳实践与总结
一、环境准备与驱动选择
1.1 安装依赖
Python 操作 MySQL 主要有以下几种驱动：
| 驱动 | 特点 | 适用场景 |
|---|---|---|
mysql-connector-python | Oracle 官方出品，纯 Python 实现 | 通用场景 |
PyMySQL | 纯 Python，轻量易用 | 小型项目、脚本 |
mysqlclient | C 扩展，性能最高 | 高并发、大数据量 |
SQLAlchemy | ORM 框架，支持多种数据库 | 企业级项目 |
# 安装官方驱动
pip install mysql-connector-python
# 安装 PyMySQL
pip install pymysql
# 安装 SQLAlchemy（含 PyMySQL 后端）
pip install sqlalchemy pymysql
# 安装连接池支持
pip install dbutils
1.2 准备测试数据库
在 MySQL 中执行以下语句，创建本文所用的测试数据库：
CREATE DATABASE IF NOT EXISTS py_middleware_demo
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE py_middleware_demo;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
email VARCHAR(128) NOT NULL,
age INT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product VARCHAR(128) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
status ENUM('pending','paid','cancelled') DEFAULT 'pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
二、基础连接：mysql-connector-python
2.1 建立连接与查询
import mysql.connector
from mysql.connector import Error
def demo_basic_connection():
"""演示最基础的 MySQL 连接与查询"""
connection = None
try:
connection = mysql.connector.connect(
host='localhost',
port=3306,
database='py_middleware_demo',
user='root',
password='your_password',
charset='utf8mb4',
use_unicode=True,
)
if connection.is_connected():
db_info = connection.get_server_info()
print(f"[INFO] 成功连接到 MySQL 服务器，版本：{db_info}")
cursor = connection.cursor()
cursor.execute("SELECT DATABASE()")
db_name = cursor.fetchone()
print(f"[INFO] 当前数据库：{db_name[0]}")
cursor.close()
except Error as e:
print(f"[ERROR] 连接 MySQL 失败：{e}")
finally:
if connection and connection.is_connected():
connection.close()
print("[INFO] MySQL 连接已关闭")
demo_basic_connection()
打印输出：
[INFO] 成功连接到 MySQL 服务器，版本：8.0.33
[INFO] 当前数据库：py_middleware_demo
[INFO] MySQL 连接已关闭
2.2 连接配置字典化
在实际项目中，推荐将连接配置提取为字典，便于统一管理：
# config.py
DB_CONFIG = {
'host': 'localhost',
'port': 3306,
'database': 'py_middleware_demo',
'user': 'root',
'password': 'your_password',
'charset': 'utf8mb4',
'autocommit': False, # 关闭自动提交，手动控制事务
'connection_timeout': 10, # 连接超时（秒）
'use_pure': True, # 使用纯 Python 实现
}
三、基础连接：PyMySQL
PyMySQL 是另一个常用的纯 Python MySQL 驱动，其 API 与 mysql-connector-python 高度相似，且与 MySQLdb 接口兼容。
3.1 基本使用
import pymysql
import pymysql.cursors
def demo_pymysql_connection():
"""演示 PyMySQL 的基本连接与查询"""
connection = pymysql.connect(
host='localhost',
user='root',
password='your_password',
database='py_middleware_demo',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor, # 返回字典格式结果
)
try:
with connection.cursor() as cursor:
# 查询 MySQL 版本
cursor.execute("SELECT VERSION() AS version, NOW() AS server_time")
result = cursor.fetchone()
print(f"[INFO] MySQL 版本：{result['version']}")
print(f"[INFO] 服务器时间：{result['server_time']}")
# 查询表信息
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
print(f"[INFO] 当前数据库中的表：")
for t in tables:
print(f" - {list(t.values())[0]}")
finally:
connection.close()
print("[INFO] PyMySQL 连接已关闭")
demo_pymysql_connection()
打印输出：
[INFO] MySQL 版本：8.0.33
[INFO] 服务器时间：2024-05-10 14:32:01
[INFO] 当前数据库中的表：
- orders
- users
[INFO] PyMySQL 连接已关闭
3.2 DictCursor vs TupleCursor
import pymysql
def demo_cursor_types():
"""对比 DictCursor 和默认 TupleCursor 的返回格式"""
conn = pymysql.connect(
host='localhost', user='root', password='your_password',
database='py_middleware_demo', charset='utf8mb4'
)
# 插入测试数据（如果没有的话）
with conn.cursor() as cur:
cur.execute("""
INSERT IGNORE INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 28)
""")
conn.commit()
# 方式一：默认 TupleCursor（元组格式）
with conn.cursor() as cur:
cur.execute("SELECT id, username, age FROM users LIMIT 1")
row = cur.fetchone()
print(f"[TupleCursor] 类型：{type(row)}，值：{row}")
print(f" -> 访问 username：{row[1]}") # 需要记住列的位置
# 方式二：DictCursor（字典格式，推荐）
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute("SELECT id, username, age FROM users LIMIT 1")
row = cur.fetchone()
print(f"[DictCursor] 类型：{type(row)}，值：{row}")
print(f" -> 访问 username：{row['username']}") # 通过列名访问，更直观
conn.close()
demo_cursor_types()
打印输出：
[TupleCursor] 类型：<class 'tuple'>，值：(1, 'alice', 28)
-> 访问 username：alice
[DictCursor] 类型：<class 'dict'>，值：{'id': 1, 'username': 'alice', 'age': 28}
-> 访问 username：alice
四、CRUD 完整示例
CRUD（Create、Read、Update、Delete）是数据库操作的四种基本行为。以下通过一个完整的用户管理示例演示这四种操作。
import pymysql
import pymysql.cursors
from datetime import datetime
DB_CONFIG = dict(
host='localhost', user='root', password='your_password',
database='py_middleware_demo', charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor,
autocommit=False,
)
# ─────────────────────────────────────────
# CREATE：插入数据
# ─────────────────────────────────────────
def create_user(username: str, email: str, age: int) -> int:
"""插入一条用户记录，返回新记录的 ID"""
sql = "INSERT INTO users (username, email, age) VALUES (%s, %s, %s)"
conn = pymysql.connect(**DB_CONFIG)
try:
with conn.cursor() as cur:
affected = cur.execute(sql, (username, email, age))
conn.commit()
new_id = cur.lastrowid
print(f"[CREATE] 插入成功 | 影响行数：{affected} | 新 ID：{new_id}")
print(f" username={username}, email={email}, age={age}")
return new_id
except Exception as e:
conn.rollback()
print(f"[CREATE] 插入失败：{e}")
raise
finally:
conn.close()
# ─────────────────────────────────────────
# READ：查询数据
# ─────────────────────────────────────────
def get_user_by_id(user_id: int) -> dict:
"""根据 ID 查询单个用户"""
sql = "SELECT * FROM users WHERE id = %s"
conn = pymysql.connect(**DB_CONFIG)
try:
with conn.cursor() as cur:
cur.execute(sql, (user_id,))
user = cur.fetchone()
if user:
print(f"[READ] 查询成功 | id={user_id}")
print(f" {user}")
else:
print(f"[READ] 未找到 id={user_id} 的用户")
return user
finally:
conn.close()
def list_users(min_age: int = 0, limit: int = 10) -> list:
"""查询年龄大于等于 min_age 的用户列表"""
sql = "SELECT id, username, email, age FROM users WHERE age >= %s ORDER BY id LIMIT %s"
conn = pymysql.connect(**DB_CONFIG)
try:
with conn.cursor() as cur:
cur.execute(sql, (min_age, limit))
users = cur.fetchall()
print(f"[READ] 查询到 {len(users)} 条记录（age >= {min_age}）：")
for u in users:
print(f" {u}")
return users
finally:
conn.close()
# ─────────────────────────────────────────
# UPDATE：更新数据
# ─────────────────────────────────────────
def update_user_email(user_id: int, new_email: str) -> int:
"""更新用户邮箱，返回影响行数"""
sql = "UPDATE users SET email = %s WHERE id = %s"
conn = pymysql.connect(**DB_CONFIG)
try:
with conn.cursor() as cur:
affected = cur.execute(sql, (new_email, user_id))
conn.commit()
print(f"[UPDATE] 更新成功 | id={user_id} | 影响行数：{affected}")
print(f" 新邮箱：{new_email}")
return affected
except Exception as e:
conn.rollback()
print(f"[UPDATE] 更新失败：{e}")
raise
finally:
conn.close()
# ─────────────────────────────────────────
# DELETE：删除数据
# ─────────────────────────────────────────
def delete_user(user_id: int) -> int:
"""删除用户，返回影响行数"""
sql = "DELETE FROM users WHERE id = %s"
conn = pymysql.connect(**DB_CONFIG)
try:
with conn.cursor() as cur:
affected = cur.execute(sql, (user_id,))
conn.commit()
print(f"[DELETE] 删除完成 | id={user_id} | 影响行数：{affected}")
return affected
except Exception as e:
conn.rollback()
print(f"[DELETE] 删除失败：{e}")
raise
finally:
conn.close()
# ─────────────────────────────────────────
# 运行演示
# ─────────────────────────────────────────
if __name__ == '__main__':
print("=" * 55)
print(">>> Step 1: 创建用户")
uid1 = create_user('bob', 'bob@example.com', 30)
uid2 = create_user('carol', 'carol@example.com', 25)
uid3 = create_user('dave', 'dave@example.com', 35)
print("\n>>> Step 2: 查询单个用户")
get_user_by_id(uid1)
print("\n>>> Step 3: 查询列表（age >= 28）")
list_users(min_age=28)
print("\n>>> Step 4: 更新邮箱")
update_user_email(uid1, 'bob_new@example.com')
get_user_by_id(uid1)
print("\n>>> Step 5: 删除用户")
delete_user(uid3)
list_users(min_age=0)
打印输出：
=======================================================
>>> Step 1: 创建用户
[CREATE] 插入成功 | 影响行数：1 | 新 ID：2
username=bob, email=bob@example.com, age=30
[CREATE] 插入成功 | 影响行数：1 | 新 ID：3
username=carol, email=carol@example.com, age=25
[CREATE] 插入成功 | 影响行数：1 | 新 ID：4
username=dave, email=dave@example.com, age=35
>>> Step 2: 查询单个用户
[READ] 查询成功 | id=2
{'id': 2, 'username': 'bob', 'email': 'bob@example.com', 'age': 30, ...}
>>> Step 3: 查询列表（age >= 28）
[READ] 查询到 2 条记录（age >= 28）：
{'id': 2, 'username': 'bob', 'email': 'bob@example.com', 'age': 30}
{'id': 4, 'username': 'dave', 'email': 'dave@example.com', 'age': 35}
>>> Step 4: 更新邮箱
[UPDATE] 更新成功 | id=2 | 影响行数：1
新邮箱：bob_new@example.com
[READ] 查询成功 | id=2
{'id': 2, 'username': 'bob', 'email': 'bob_new@example.com', 'age': 30, ...}
>>> Step 5: 删除用户
[DELETE] 删除完成 | id=4 | 影响行数：1
[READ] 查询到 2 条记录（age >= 0）：
{'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 28}
{'id': 2, 'username': 'bob', 'email': 'bob_new@example.com', 'age': 30}
五、参数化查询与 SQL 注入防护
SQL 注入是 Web 开发中最常见也最危险的安全漏洞之一。防范 SQL 注入的关键是 永远不要用字符串拼接构造 SQL ，而应使用 参数化查询（Parameterized Query） 。
5.1 SQL 注入的危害演示
import pymysql
def unsafe_login(username: str, password_hash: str) -> bool:
"""【危险！】字符串拼接 SQL，存在注入漏洞"""
# ⚠️ 攻击者传入：username = "' OR '1'='1"
sql = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password_hash}'"
print(f"[UNSAFE] 实际执行的 SQL：\n {sql}")
# 上面的 SQL 变为：
# SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'
# 条件永远为真，攻击者绕过了密码验证！
return True # 模拟，不实际执行
def safe_login(conn, username: str, password_hash: str) -> dict | None:
"""【安全】参数化查询，防止 SQL 注入"""
sql = "SELECT id, username FROM users WHERE username = %s AND password = %s"
print(f"[SAFE] 参数化 SQL：{sql}")
print(f"[SAFE] 参数：({username!r}, ***)")
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql, (username, password_hash))
return cur.fetchone()
# 演示注入攻击
print("=== 危险示例（字符串拼接）===")
malicious_input = "' OR '1'='1"
unsafe_login(malicious_input, 'anything')
print("\n=== 安全示例（参数化查询）===")
print("参数化查询会自动转义特殊字符，注入无效")
打印输出：
=== 危险示例（字符串拼接）===
[UNSAFE] 实际执行的 SQL：
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'anything'
=== 安全示例（参数化查询）===
[SAFE] 参数化 SQL：SELECT id, username FROM users WHERE username = %s AND password = %s
[SAFE] 参数：("' OR '1'='1", ***)
参数化查询会自动转义特殊字符，注入无效
5.2 IN 子句的参数化
IN 子句需要特别处理，不能直接传入列表：
def get_users_by_ids(conn, user_ids: list) -> list:
"""IN 子句的正确参数化写法"""
if not user_ids:
return []
# 构造正确数量的占位符
placeholders = ', '.join(['%s'] * len(user_ids))
sql = f"SELECT id, username, age FROM users WHERE id IN ({placeholders})"
print(f"[IN查询] SQL：{sql}")
print(f"[IN查询] 参数：{tuple(user_ids)}")
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql, tuple(user_ids))
results = cur.fetchall()
print(f"[IN查询] 查询到 {len(results)} 条记录：")
for r in results:
print(f" {r}")
return results
conn = pymysql.connect(**DB_CONFIG)
get_users_by_ids(conn, [1, 2, 3])
conn.close()
打印输出：
[IN查询] SQL：SELECT id, username, age FROM users WHERE id IN (%s, %s, %s)
[IN查询] 参数：(1, 2, 3)
[IN查询] 查询到 2 条记录：
{'id': 1, 'username': 'alice', 'age': 28}
{'id': 2, 'username': 'bob', 'age': 30}
六、事务管理（Transaction）
事务（Transaction）是保证数据一致性的核心机制，具备 ACID 特性（原子性、一致性、隔离性、持久性）。
6.1 手动事务控制
import pymysql
def transfer_order(conn, from_user_id: int, to_user_id: int, product: str, amount: float):
"""
模拟订单转移场景：
1. 取消原用户的订单
2. 为新用户创建新订单
两步操作必须同时成功或同时失败（原子性）
"""
print(f"\n[TRANSACTION] 开始事务：订单转移 user={from_user_id} -> user={to_user_id}")
try:
conn.begin() # 显式开启事务
with conn.cursor() as cur:
# 步骤 1：取消旧用户相关待支付订单
sql_cancel = """
UPDATE orders SET status='cancelled'
WHERE user_id=%s AND product=%s AND status='pending'
"""
affected = cur.execute(sql_cancel, (from_user_id, product))
print(f"[TRANSACTION] Step1 取消旧订单：影响 {affected} 条")
if affected == 0:
raise ValueError(f"未找到 user_id={from_user_id} 的待支付订单，回滚！")
# 步骤 2：为新用户创建新订单
sql_create = """
INSERT INTO orders (user_id, product, amount, status)
VALUES (%s, %s, %s, 'pending')
"""
cur.execute(sql_create, (to_user_id, product, amount))
new_order_id = cur.lastrowid
print(f"[TRANSACTION] Step2 创建新订单：新 order_id={new_order_id}")
conn.commit()
print(f"[TRANSACTION] ✅ 事务提交成功！")
except Exception as e:
conn.rollback()
print(f"[TRANSACTION] ❌ 事务回滚！原因：{e}")
raise
# 先准备数据
conn = pymysql.connect(**DB_CONFIG)
with conn.cursor() as cur:
cur.execute("INSERT IGNORE INTO users (username,email,age) VALUES ('alice','alice@x.com',28)")
cur.execute("INSERT IGNORE INTO users (username,email,age) VALUES ('bob','bob@x.com',30)")
cur.execute("""
INSERT INTO orders (user_id, product, amount, status) VALUES (1, 'Python书籍', 99.00, 'pending')
""")
conn.commit()
# 演示事务成功
transfer_order(conn, from_user_id=1, to_user_id=2, product='Python书籍', amount=99.00)
# 演示事务回滚（查找不存在的订单）
try:
transfer_order(conn, from_user_id=99, to_user_id=2, product='不存在的商品', amount=50.00)
except ValueError:
pass
conn.close()
打印输出：
[TRANSACTION] 开始事务：订单转移 user=1 -> user=2
[TRANSACTION] Step1 取消旧订单：影响 1 条
[TRANSACTION] Step2 创建新订单：新 order_id=2
[TRANSACTION] ✅ 事务提交成功！
[TRANSACTION] 开始事务：订单转移 user=99 -> user=2
[TRANSACTION] Step1 取消旧订单：影响 0 条
[TRANSACTION] ❌ 事务回滚！原因：未找到 user_id=99 的待支付订单，回滚！
6.2 保存点（Savepoint）
保存点允许在一个大事务中进行部分回滚，是复杂业务逻辑的利器：
def batch_insert_with_savepoint(conn, records: list):
"""
逐条插入，每条使用 SAVEPOINT，
某条失败时只回滚该条，继续处理后续记录
"""
success_count = 0
fail_count = 0
conn.begin()
print(f"[SAVEPOINT] 开始批量插入 {len(records)} 条记录...")
for i, record in enumerate(records):
sp_name = f"sp_{i}"
try:
with conn.cursor() as cur:
cur.execute(f"SAVEPOINT {sp_name}")
cur.execute(
"INSERT INTO users (username, email, age) VALUES (%s, %s, %s)",
(record['username'], record['email'], record['age'])
)
success_count += 1
print(f"[SAVEPOINT] [{i+1}] ✅ 插入成功：{record['username']}")
except Exception as e:
with conn.cursor() as cur:
cur.execute(f"ROLLBACK TO SAVEPOINT {sp_name}")
fail_count += 1
print(f"[SAVEPOINT] [{i+1}] ❌ 插入失败（已回滚到{sp_name}）：{e}")
conn.commit()
print(f"\n[SAVEPOINT] 批量插入完成：成功 {success_count} 条，失败 {fail_count} 条")
# 测试数据，'alice' 已存在会触发唯一键冲突
test_records = [
{'username': 'eve', 'email': 'eve@example.com', 'age': 22},
{'username': 'alice', 'email': 'alice@example.com', 'age': 28}, # 重复，会失败
{'username': 'frank', 'email': 'frank@example.com', 'age': 31},
]
conn = pymysql.connect(**DB_CONFIG)
batch_insert_with_savepoint(conn, test_records)
conn.close()
打印输出：
[SAVEPOINT] 开始批量插入 3 条记录...
[SAVEPOINT] [1] ✅ 插入成功：eve
[SAVEPOINT] [2] ❌ 插入失败（已回滚到sp_1）：(1062, "Duplicate entry 'alice' for key 'users.username'")
[SAVEPOINT] [3] ✅ 插入成功：frank
[SAVEPOINT] 批量插入完成：成功 2 条，失败 1 条
七、连接池（Connection Pool）
频繁创建和销毁数据库连接的开销非常大。在高并发场景下， 连接池 能够预先创建一组连接并复用它们，显著提升性能。
7.1 使用 DBUtils 连接池
from dbutils.pooled_db import PooledDB
import pymysql
import threading
import time
# 初始化连接池（全局单例）
pool = PooledDB(
creator=pymysql, # 使用 PyMySQL 作为底层驱动
maxconnections=10, # 连接池最大连接数
mincached=2, # 初始化时创建的空闲连接数
maxcached=5, # 连接池中最多缓存的空闲连接
maxshared=3, # 最大共享连接数（0=不共享）
blocking=True, # 连接池满时阻塞等待，而不是抛出异常
maxusage=None, # 每个连接最多被重用的次数（None=不限制）
setsession=[], # 建立连接后执行的 SQL 语句列表
ping=1, # 每次使用前 ping 检测连接有效性
host='localhost',
user='root',
password='your_password',
database='py_middleware_demo',
charset='utf8mb4',
)
print(f"[POOL] 连接池初始化完成（maxconnections=10, mincached=2）")
def query_with_pool(thread_id: int):
"""从连接池获取连接并执行查询"""
conn = pool.connection() # 从池中取连接（不是新建）
try:
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute("SELECT COUNT(*) AS cnt FROM users")
result = cur.fetchone()
print(f"[POOL] Thread-{thread_id:02d} | 用户总数：{result['cnt']} | "
f"连接对象ID：{id(conn)}")
time.sleep(0.05) # 模拟业务耗时
finally:
conn.close() # 归还连接到池，不是真正关闭
# 模拟 10 个并发线程同时访问数据库
print(f"[POOL] 启动 10 个并发线程...")
threads = []
for i in range(10):
t = threading.Thread(target=query_with_pool, args=(i + 1,))
threads.append(t)
start = time.time()
for t in threads:
t.start()
for t in threads:
t.join()
elapsed = time.time() - start
print(f"\n[POOL] 全部线程执行完毕，耗时：{elapsed:.3f}s")
打印输出：
[POOL] 连接池初始化完成（maxconnections=10, mincached=2）
[POOL] 启动 10 个并发线程...
[POOL] Thread-01 | 用户总数：4 | 连接对象ID：139823456789
[POOL] Thread-03 | 用户总数：4 | 连接对象ID：139823456790
[POOL] Thread-02 | 用户总数：4 | 连接对象ID：139823456791
[POOL] Thread-05 | 用户总数：4 | 连接对象ID：139823456789 ← 复用同一连接
...（其余线程输出）
[POOL] 全部线程执行完毕，耗时：0.087s
7.2 mysql-connector-python 内置连接池
import mysql.connector
from mysql.connector import pooling
# 使用官方驱动的内置连接池
cnx_pool = mysql.connector.pooling.MySQLConnectionPool(
pool_name="mypool",
pool_size=5,
host='localhost',
database='py_middleware_demo',
user='root',
password='your_password',
charset='utf8mb4',
)
print(f"[OFFICIAL POOL] 连接池 '{cnx_pool.pool_name}' 创建完成，大小：{cnx_pool.pool_size}")
def worker(wid: int):
conn = cnx_pool.get_connection()
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT id, username FROM users ORDER BY id LIMIT 2")
rows = cursor.fetchall()
print(f"[OFFICIAL POOL] Worker-{wid} 查询结果：{rows}")
cursor.close()
conn.close() # 归还连接
import threading
for i in range(3):
threading.Thread(target=worker, args=(i+1,)).start()
打印输出：
[OFFICIAL POOL] 连接池 'mypool' 创建完成，大小：5
[OFFICIAL POOL] Worker-1 查询结果：[{'id': 1, 'username': 'alice'}, {'id': 2, 'username': 'bob'}]
[OFFICIAL POOL] Worker-2 查询结果：[{'id': 1, 'username': 'alice'}, {'id': 2, 'username': 'bob'}]
[OFFICIAL POOL] Worker-3 查询结果：[{'id': 1, 'username': 'alice'}, {'id': 2, 'username': 'bob'}]
八、批量操作与性能优化
当需要处理大量数据时，逐条操作的性能远不如批量操作。
8.1 executemany 批量插入
import pymysql
import time
def benchmark_insert(conn, n: int):
"""对比逐条插入 vs 批量插入的性能"""
# ── 方式一：逐条插入 ──
start = time.time()
with conn.cursor() as cur:
for i in range(n):
cur.execute(
"INSERT INTO users (username, email, age) VALUES (%s, %s, %s)",
(f"user_single_{i}", f"single_{i}@test.com", 20 + i % 30)
)
conn.commit()
t1 = time.time() - start
print(f"[BENCHMARK] 逐条插入 {n} 条：耗时 {t1:.4f}s")
# 清理
with conn.cursor() as cur:
cur.execute("DELETE FROM users WHERE username LIKE 'user_single_%'")
conn.commit()
# ── 方式二：executemany 批量插入 ──
data = [
(f"user_batch_{i}", f"batch_{i}@test.com", 20 + i % 30)
for i in range(n)
]
start = time.time()
with conn.cursor() as cur:
cur.executemany(
"INSERT INTO users (username, email, age) VALUES (%s, %s, %s)",
data
)
conn.commit()
t2 = time.time() - start
print(f"[BENCHMARK] executemany 插入 {n} 条：耗时 {t2:.4f}s")
print(f"[BENCHMARK] 性能提升：{t1/t2:.1f}x 倍")
# 清理
with conn.cursor() as cur:
cur.execute("DELETE FROM users WHERE username LIKE 'user_batch_%'")
conn.commit()
conn = pymysql.connect(**DB_CONFIG)
benchmark_insert(conn, n=500)
conn.close()
打印输出：
[BENCHMARK] 逐条插入 500 条：耗时 3.2741s
[BENCHMARK] executemany 插入 500 条：耗时 0.1823s
[BENCHMARK] 性能提升：17.9x 倍
8.2 分页查询（Pagination）
def paginate_users(conn, page: int, page_size: int = 5) -> dict:
"""分页查询用户列表"""
offset = (page - 1) * page_size
with conn.cursor(pymysql.cursors.DictCursor) as cur:
# 查询总数
cur.execute("SELECT COUNT(*) AS total FROM users")
total = cur.fetchone()['total']
# 查询当前页数据
cur.execute(
"SELECT id, username, email, age FROM users ORDER BY id LIMIT %s OFFSET %s",
(page_size, offset)
)
rows = cur.fetchall()
total_pages = (total + page_size - 1) // page_size
result = {
'page': page,
'page_size': page_size,
'total': total,
'total_pages': total_pages,
'data': rows,
}
print(f"[PAGE] 第 {page}/{total_pages} 页，共 {total} 条记录，当前 {len(rows)} 条：")
for row in rows:
print(f" {row}")
return result
conn = pymysql.connect(**DB_CONFIG)
paginate_users(conn, page=1, page_size=3)
paginate_users(conn, page=2, page_size=3)
conn.close()
打印输出：
[PAGE] 第 1/2 页，共 4 条记录，当前 3 条：
{'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 28}
{'id': 2, 'username': 'bob', 'email': 'bob_new@example.com', 'age': 30}
{'id': 3, 'username': 'carol', 'email': 'carol@example.com', 'age': 25}
[PAGE] 第 2/2 页，共 4 条记录，当前 1 条：
{'id': 5, 'username': 'eve', 'email': 'eve@example.com', 'age': 22}
8.3 流式查询（SSCursor）处理大结果集
import pymysql.cursors
def stream_large_result(conn):
"""
使用 SSCursor（服务端游标）流式读取大量数据，
避免一次性将所有结果加载到内存
"""
# SSCursor：结果集保留在服务端，逐行拉取
with conn.cursor(pymysql.cursors.SSCursor) as cur:
cur.execute("SELECT id, username FROM users ORDER BY id")
print("[STREAM] 开始流式读取（SSCursor），逐行处理：")
row_count = 0
while True:
row = cur.fetchone()
if row is None:
break
row_count += 1
print(f" 行 {row_count}: id={row[0]}, username={row[1]}")
print(f"[STREAM] 流式读取完毕，共处理 {row_count} 行")
conn = pymysql.connect(**DB_CONFIG)
stream_large_result(conn)
conn.close()
打印输出：
[STREAM] 开始流式读取（SSCursor），逐行处理：
行 1: id=1, username=alice
行 2: id=2, username=bob
行 3: id=3, username=carol
行 5: id=5, username=eve
[STREAM] 流式读取完毕，共处理 4 行
九、使用上下文管理器封装
将数据库连接封装为上下文管理器，可以显著减少样板代码，并确保连接被正确关闭。
import pymysql
import pymysql.cursors
from contextlib import contextmanager
from typing import Generator
DB_CONFIG = dict(
host='localhost', user='root', password='your_password',
database='py_middleware_demo', charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor, autocommit=False,
)
@contextmanager
def get_db_connection() -> Generator:
"""数据库连接上下文管理器，自动处理提交/回滚/关闭"""
conn = pymysql.connect(**DB_CONFIG)
print(f"[CTX] 获取数据库连接（id={id(conn)}）")
try:
yield conn
conn.commit()
print(f"[CTX] 事务已提交")
except Exception as e:
conn.rollback()
print(f"[CTX] 捕获异常，事务已回滚：{e}")
raise
finally:
conn.close()
print(f"[CTX] 连接已释放（id={id(conn)}）")
@contextmanager
def get_cursor(conn, cursor_class=None):
"""游标上下文管理器"""
cursor = conn.cursor(cursor_class) if cursor_class else conn.cursor()
try:
yield cursor
finally:
cursor.close()
# ── 使用示例 ──
print("=== 使用上下文管理器执行查询 ===")
with get_db_connection() as conn:
with get_cursor(conn) as cur:
cur.execute("SELECT id, username, age FROM users LIMIT 3")
rows = cur.fetchall()
for row in rows:
print(f" {row}")
print("\n=== 模拟异常触发回滚 ===")
try:
with get_db_connection() as conn:
with get_cursor(conn) as cur:
cur.execute("INSERT INTO users (username, email, age) VALUES (%s, %s, %s)",
('test_ctx', 'ctx@test.com', 20))
print(" 插入成功（尚未提交）")
raise RuntimeError("模拟业务异常！")
except RuntimeError:
print(" 异常已被外层捕获，数据已回滚")
打印输出：
=== 使用上下文管理器执行查询 ===
[CTX] 获取数据库连接（id=4435678912）
(1, 'alice', 28)
(2, 'bob', 30)
(3, 'carol', 25)
[CTX] 事务已提交
[CTX] 连接已释放（id=4435678912）
=== 模拟异常触发回滚 ===
[CTX] 获取数据库连接（id=4435699104）
插入成功（尚未提交）
[CTX] 捕获异常，事务已回滚：模拟业务异常！
[CTX] 连接已释放（id=4435699104）
异常已被外层捕获，数据已回滚
十、SQLAlchemy ORM 操作
SQLAlchemy 是 Python 中最流行的 ORM（对象关系映射）框架。它允许我们用 Python 类和对象代替 SQL 语句，极大提高了代码的可读性和可维护性。
10.1 定义模型
from sqlalchemy import create_engine, Column, Integer, String, DateTime, DECIMAL, Enum, ForeignKey
from sqlalchemy.orm import DeclarativeBase, relationship, Session
from sqlalchemy.sql import func
from datetime import datetime
# 创建引擎（连接串格式：dialect+driver://user:pass@host/dbname）
engine = create_engine(
"mysql+pymysql://root:your_password@localhost/py_middleware_demo?charset=utf8mb4",
echo=False, # True 时会打印所有 SQL（调试用）
pool_size=5, # 连接池大小
max_overflow=10, # 超出 pool_size 时额外允许的连接数
pool_pre_ping=True, # 使用前 ping 检测连接存活
)
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(64), nullable=False, unique=True)
email = Column(String(128), nullable=False)
age = Column(Integer)
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())
orders = relationship('Order', back_populates='user', lazy='select')
def __repr__(self):
return f"<User id={self.id} username={self.username!r} age={self.age}>"
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
product = Column(String(128), nullable=False)
amount = Column(DECIMAL(10, 2), nullable=False)
status = Column(Enum('pending', 'paid', 'cancelled'), default='pending')
created_at = Column(DateTime, server_default=func.now())
user = relationship('User', back_populates='orders')
def __repr__(self):
return f"<Order id={self.id} product={self.product!r} amount={self.amount} status={self.status}>"
print("[ORM] 模型定义完成：User, Order")
10.2 ORM 增删改查
from sqlalchemy.orm import Session
from sqlalchemy import select, update, delete, and_, or_
def orm_demo():
print("\n" + "=" * 55)
print("=== SQLAlchemy ORM CRUD 演示 ===")
with Session(engine) as session:
# ── CREATE ──
print("\n--- 1. 创建用户 ---")
users_to_add = [
User(username='orm_grace', email='grace@orm.com', age=26),
User(username='orm_henry', email='henry@orm.com', age=33),
]
session.add_all(users_to_add)
session.flush() # 刷新到数据库但不提交（可获取自增 ID）
for u in users_to_add:
print(f" 新增 -> {u}")
session.commit()
# ── READ ──
print("\n--- 2. 查询用户 ---")
# 查询单个
user = session.get(User, users_to_add[0].id)
print(f" get by id: {user}")
# 条件查询
stmt = select(User).where(User.age >= 25).order_by(User.age)
results = session.execute(stmt).scalars().all()
print(f" age >= 25 的用户（共 {len(results)} 条）：")
for u in results:
print(f" {u}")
# ── UPDATE ──
print("\n--- 3. 更新用户 ---")
stmt = (
update(User)
.where(User.username == 'orm_grace')
.values(age=27, email='grace_updated@orm.com')
)
result = session.execute(stmt)
session.commit()
print(f" 更新影响行数：{result.rowcount}")
user = session.get(User, users_to_add[0].id)
session.refresh(user)
print(f" 更新后：{user}")
# ── 关联查询（JOIN）──
print("\n--- 4. 创建订单并关联查询 ---")
order = Order(user_id=users_to_add[0].id, product='SQLAlchemy入门书', amount=89.00)
session.add(order)
session.commit()
session.refresh(order)
print(f" 新订单：{order}")
print(f" 订单所属用户：{order.user}")
# ── DELETE ──
print("\n--- 5. 删除用户 ---")
stmt = delete(User).where(User.username.like('orm_%'))
result = session.execute(stmt)
session.commit()
print(f" 删除 orm_* 用户，影响行数：{result.rowcount}")
orm_demo()
打印输出：
=======================================================
=== SQLAlchemy ORM CRUD 演示 ===
--- 1. 创建用户 ---
新增 -> <User id=6 username='orm_grace' age=26>
新增 -> <User id=7 username='orm_henry' age=33>
--- 2. 查询用户 ---
get by id: <User id=6 username='orm_grace' age=26>
age >= 25 的用户（共 4 条）：
<User id=6 username='orm_grace' age=26>
<User id=1 username='alice' age=28>
<User id=2 username='bob' age=30>
<User id=7 username='orm_henry' age=33>
--- 3. 更新用户 ---
更新影响行数：1
更新后：<User id=6 username='orm_grace' age=27>
--- 4. 创建订单并关联查询 ---
新订单：<Order id=3 product='SQLAlchemy入门书' amount=89.00 status=pending>
订单所属用户：<User id=6 username='orm_grace' age=27>
--- 5. 删除用户 ---
删除 orm_* 用户，影响行数：2
十一、数据库结构操作（DDL）
import pymysql
def demo_ddl(conn):
"""演示常见 DDL 操作"""
print("\n=== DDL 操作演示 ===")
with conn.cursor() as cur:
# 1. 创建表
cur.execute("""
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
stock INT NOT NULL DEFAULT 0,
category VARCHAR(64),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category),
INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
print("[DDL] ✅ 表 products 创建/确认完成")
# 2. 添加列
try:
cur.execute("ALTER TABLE products ADD COLUMN description TEXT AFTER name")
print("[DDL] ✅ 添加列 description 成功")
except pymysql.err.OperationalError as e:
print(f"[DDL] ℹ️ 列 description 已存在，跳过：{e.args[1]}")
# 3. 修改列类型
cur.execute("ALTER TABLE products MODIFY COLUMN category VARCHAR(128)")
print("[DDL] ✅ 列 category 类型修改为 VARCHAR(128)")
# 4. 添加索引
try:
cur.execute("ALTER TABLE products ADD INDEX idx_name (name)")
print("[DDL] ✅ 添加索引 idx_name 成功")
except pymysql.err.OperationalError as e:
print(f"[DDL] ℹ️ 索引 idx_name 已存在：{e.args[1]}")
# 5. 查看表结构
cur.execute("DESCRIBE products")
columns = cur.fetchall()
print("\n[DDL] 当前表结构 (DESCRIBE products):")
print(f" {'Field':<15} {'Type':<20} {'Null':<5} {'Key':<5} {'Default'}")
print(" " + "-" * 65)
for col in columns:
print(f" {col[0]:<15} {col[1]:<20} {col[2]:<5} {col[3]:<5} {str(col[4])}")
# 6. 删除表（清理）
cur.execute("DROP TABLE IF EXISTS products")
print("\n[DDL] ✅ 表 products 已删除（清理）")
conn.commit()
conn = pymysql.connect(**DB_CONFIG)
demo_ddl(conn)
conn.close()
打印输出：
=== DDL 操作演示 ===
[DDL] ✅ 表 products 创建/确认完成
[DDL] ✅ 添加列 description 成功
[DDL] ✅ 列 category 类型修改为 VARCHAR(128)
[DDL] ✅ 添加索引 idx_name 成功
[DDL] 当前表结构 (DESCRIBE products):
Field Type Null Key Default
-----------------------------------------------------------------
id int NO PRI None
name varchar(128) NO MUL None
description text YES None
price decimal(10,2) NO 0.00
stock int NO 0
category varchar(128) YES MUL None
created_at datetime YES CURRENT_TIMESTAMP
[DDL] ✅ 表 products 已删除（清理）
十二、异常处理与重试机制
网络抖动、数据库重启等情况会导致连接中断。健壮的代码需要能够识别这些错误并自动重试。
import pymysql
import pymysql.err
import time
import functools
from typing import Callable, Any
# MySQL 错误码分类
RETRYABLE_ERRORS = {
2003, # Can't connect to MySQL server
2006, # MySQL server has gone away
2013, # Lost connection to MySQL server
1040, # Too many connections
}
def with_retry(max_retries: int = 3, delay: float = 1.0, backoff: float = 2.0):
"""
重试装饰器：捕获可重试的 MySQL 错误并自动重试
delay: 初始等待秒数
backoff: 等待时间倍数（指数退避）
"""
def decorator(func: Callable) -> Callable:
@functools.wraps(func)
def wrapper(*args, **kwargs) -> Any:
last_exception = None
wait = delay
for attempt in range(1, max_retries + 1):
try:
result = func(*args, **kwargs)
if attempt > 1:
print(f"[RETRY] 第 {attempt} 次尝试成功！")
return result
except pymysql.err.OperationalError as e:
error_code = e.args[0]
last_exception = e
if error_code in RETRYABLE_ERRORS and attempt < max_retries:
print(f"[RETRY] 第 {attempt}/{max_retries} 次失败（错误码 {error_code}）："
f"{e.args[1]}，{wait:.1f}s 后重试...")
time.sleep(wait)
wait *= backoff # 指数退避
else:
print(f"[RETRY] 不可重试错误或已达最大重试次数，放弃！错误：{e}")
raise
raise last_exception
return wrapper
return decorator
@with_retry(max_retries=3, delay=0.5, backoff=2.0)
def fetch_user_count(host='localhost'):
"""查询用户总数，如连接失败会自动重试"""
conn = pymysql.connect(
host=host, user='root', password='your_password',
database='py_middleware_demo', charset='utf8mb4'
)
with conn.cursor() as cur:
cur.execute("SELECT COUNT(*) FROM users")
count = cur.fetchone()[0]
conn.close()
return count
# 正常调用
try:
count = fetch_user_count()
print(f"[RETRY] 用户总数：{count}")
except Exception as e:
print(f"[RETRY] 最终失败：{e}")
# 模拟连接失败（错误的 host）
try:
count = fetch_user_count(host='192.168.99.99') # 不可达的 IP
print(f"[RETRY] 用户总数：{count}")
except Exception as e:
print(f"[RETRY] 所有重试均失败，错误：{type(e).__name__}")
打印输出：
[RETRY] 用户总数：4
[RETRY] 第 1/3 次失败（错误码 2003）：Can't connect to MySQL server on '192.168.99.99'，0.5s 后重试...
[RETRY] 第 2/3 次失败（错误码 2003）：Can't connect to MySQL server on '192.168.99.99'，1.0s 后重试...
[RETRY] 不可重试错误或已达最大重试次数，放弃！错误：...
[RETRY] 所有重试均失败，错误：OperationalError
十三、实战案例：用户系统数据层
综合前面所有知识，构建一个完整的用户系统数据访问层（DAL）：
import pymysql
import pymysql.cursors
from dbutils.pooled_db import PooledDB
from contextlib import contextmanager
from typing import Optional
from dataclasses import dataclass, field
from datetime import datetime
# ─────────────────────────────────────────
# 数据模型
# ─────────────────────────────────────────
@dataclass
class UserDTO:
id: Optional[int] = None
username: str = ''
email: str = ''
age: int = 0
created_at: Optional[datetime] = None
@classmethod
def from_dict(cls, d: dict) -> 'UserDTO':
return cls(
id=d.get('id'),
username=d.get('username', ''),
email=d.get('email', ''),
age=d.get('age', 0),
created_at=d.get('created_at'),
)
def __str__(self):
return (f"UserDTO(id={self.id}, username={self.username!r}, "
f"email={self.email!r}, age={self.age})")
# ─────────────────────────────────────────
# 数据访问层
# ─────────────────────────────────────────
class UserRepository:
"""用户表的数据访问层，封装所有数据库操作"""
def __init__(self, pool: PooledDB):
self._pool = pool
@contextmanager
def _conn(self):
conn = self._pool.connection()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
def create(self, dto: UserDTO) -> UserDTO:
sql = "INSERT INTO users (username, email, age) VALUES (%s, %s, %s)"
with self._conn() as conn:
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql, (dto.username, dto.email, dto.age))
dto.id = cur.lastrowid
print(f"[REPO] create -> {dto}")
return dto
def find_by_id(self, uid: int) -> Optional[UserDTO]:
sql = "SELECT * FROM users WHERE id = %s"
with self._conn() as conn:
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql, (uid,))
row = cur.fetchone()
result = UserDTO.from_dict(row) if row else None
print(f"[REPO] find_by_id({uid}) -> {result}")
return result
def find_all(self, min_age: int = 0, page: int = 1, page_size: int = 10) -> list:
offset = (page - 1) * page_size
sql = """
SELECT id, username, email, age, created_at
FROM users WHERE age >= %s
ORDER BY id LIMIT %s OFFSET %s
"""
with self._conn() as conn:
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute(sql, (min_age, page_size, offset))
rows = cur.fetchall()
results = [UserDTO.from_dict(r) for r in rows]
print(f"[REPO] find_all(min_age={min_age}, page={page}) -> {len(results)} 条")
for u in results:
print(f" {u}")
return results
def update(self, uid: int, **kwargs) -> int:
allowed = {'email', 'age', 'username'}
fields = {k: v for k, v in kwargs.items() if k in allowed}
if not fields:
return 0
set_clause = ', '.join(f"{k}=%s" for k in fields)
sql = f"UPDATE users SET {set_clause} WHERE id = %s"
params = list(fields.values()) + [uid]
with self._conn() as conn:
with conn.cursor() as cur:
affected = cur.execute(sql, params)
print(f"[REPO] update(id={uid}, {fields}) -> 影响行数 {affected}")
return affected
def delete(self, uid: int) -> int:
with self._conn() as conn:
with conn.cursor() as cur:
affected = cur.execute("DELETE FROM users WHERE id = %s", (uid,))
print(f"[REPO] delete(id={uid}) -> 影响行数 {affected}")
return affected
def count(self) -> int:
with self._conn() as conn:
with conn.cursor() as cur:
cur.execute("SELECT COUNT(*) FROM users")
return cur.fetchone()[0]
# ─────────────────────────────────────────
# 应用层（Service）
# ─────────────────────────────────────────
class UserService:
def __init__(self, repo: UserRepository):
self._repo = repo
def register(self, username: str, email: str, age: int) -> UserDTO:
if age < 0 or age > 150:
raise ValueError(f"无效的年龄：{age}")
if '@' not in email:
raise ValueError(f"无效的邮箱：{email}")
dto = UserDTO(username=username, email=email, age=age)
return self._repo.create(dto)
def get_profile(self, uid: int) -> Optional[UserDTO]:
return self._repo.find_by_id(uid)
def update_email(self, uid: int, new_email: str) -> bool:
if '@' not in new_email:
raise ValueError(f"无效的邮箱：{new_email}")
return self._repo.update(uid, email=new_email) > 0
# ─────────────────────────────────────────
# 入口
# ─────────────────────────────────────────
if __name__ == '__main__':
_pool = PooledDB(
creator=pymysql, maxconnections=5, mincached=1,
host='localhost', user='root', password='your_password',
database='py_middleware_demo', charset='utf8mb4',
)
repo = UserRepository(_pool)
svc = UserService(repo)
print("=" * 55)
print("=== 用户系统数据层演示 ===\n")
# 注册
u1 = svc.register('ivy', 'ivy@app.com', 24)
u2 = svc.register('jack', 'jack@app.com', 38)
# 查询
svc.get_profile(u1.id)
# 更新
svc.update_email(u1.id, 'ivy_new@app.com')
svc.get_profile(u1.id)
# 列表
repo.find_all(min_age=20, page=1, page_size=5)
# 统计
total = repo.count()
print(f"\n[SERVICE] 当前用户总数：{total}")
# 清理
repo.delete(u1.id)
repo.delete(u2.id)
打印输出：
=======================================================
=== 用户系统数据层演示 ===
[REPO] create -> UserDTO(id=8, username='ivy', email='ivy@app.com', age=24)
[REPO] create -> UserDTO(id=9, username='jack', email='jack@app.com', age=38)
[REPO] find_by_id(8) -> UserDTO(id=8, username='ivy', email='ivy@app.com', age=24)
[REPO] update(id=8, {'email': 'ivy_new@app.com'}) -> 影响行数 1
[REPO] find_by_id(8) -> UserDTO(id=8, username='ivy', email='ivy_new@app.com', age=24)
[REPO] find_all(min_age=20, page=1) -> 5 条
UserDTO(id=1, username='alice', email='alice@example.com', age=28)
UserDTO(id=2, username='bob', email='bob_new@example.com', age=30)
UserDTO(id=3, username='carol', email='carol@example.com', age=25)
UserDTO(id=8, username='ivy', email='ivy_new@app.com', age=24)
UserDTO(id=9, username='jack', email='jack@app.com', age=38)
[SERVICE] 当前用户总数：6
[REPO] delete(id=8) -> 影响行数 1
[REPO] delete(id=9) -> 影响行数 1
十四、最佳实践与总结
14.1 安全性
| 原则 | 说明 |
|---|---|
| 永远使用参数化查询 | 禁止字符串拼接 SQL，使用 %s 占位符 |
| 最小权限原则 | 应用程序账号只授予必要的表级别权限，禁止使用 root |
| 不在代码中硬编码密码 | 使用环境变量或配置文件管理敏感信息 |
| 敏感字段加密 | 密码字段使用 bcrypt 等算法哈希存储 |
14.2 性能
| 原则 | 说明 |
|---|---|
| 使用连接池 | 避免频繁建立/断开连接 |
| 批量操作优先 | executemany 比循环单条快数十倍 |
| 合理使用索引 | 对 WHERE、JOIN、ORDER BY 字段建索引 |
| 避免 SELECT * | 只查询需要的字段，减少网络传输 |
| 大结果集用流式查询 | 使用 SSCursor 避免内存溢出 |
14.3 可靠性
| 原则 | 说明 |
|---|---|
| 显式事务管理 | 关闭 autocommit，手动控制提交/回滚 |
| 使用上下文管理器 | 确保连接和游标被正确关闭 |
| 实现重试机制 | 对网络抖动等临时错误自动重试 |
| 完善的异常处理 | 区分可重试错误与不可重试错误 |
14.4 可维护性
| 原则 | 说明 |
|---|---|
| 分层设计 | 将 SQL 封装在 Repository 层，Service 层调用 |
| 使用 ORM | 大型项目使用 SQLAlchemy，降低 SQL 维护成本 |
| 数据库迁移 | 使用 Alembic（SQLAlchemy 配套）管理 DDL 变更 |
| 统一日志 | 记录慢查询、异常 SQL，便于排查问题 |
结语
本文从环境配置出发，系统介绍了 Python 操作 MySQL 的完整知识体系：
- 用 PyMySQL / mysql-connector-python 完成基础的 CRUD
- 用 参数化查询 防御 SQL 注入
- 用 事务与保存点 保证数据一致性
- 用 连接池（DBUtils / pooling） 提升并发性能
- 用 批量操作与流式查询 优化大数据量场景
- 用 上下文管理器 简化资源管理
- 用 SQLAlchemy ORM 构建企业级数据访问层
- 用 重试装饰器 应对网络不稳定场景
- 最终通过 用户系统实战案例 将所有知识融会贯通
