一、前言

在现代应用开发中，数据库是支撑数据存储与业务逻辑的核心组件，而 MySQL 作为全球最流行的开源关系型数据库管理系统（RDBMS），凭借其高性能、高稳定性和开源免费的特性，被广泛应用于从小型工具到大型互联网系统的各类场景中。

Python 作为一门简洁高效、生态丰富的编程语言，拥有大量成熟的数据库操作库，能够轻松实现与 MySQL 数据库的交互，完成数据的增、删、改、查等核心操作。同时，在高并发、高可用的生产环境中，数据库连接管理、事务控制、性能优化是决定应用稳定性与效率的关键。

本文将系统、全面地讲解 Python 操作 MySQL 数据库的全流程，从基础的库安装、连接建立，到常见的 CRUD 操作，再到进阶的连接池优化、事务管理与隔离级别，覆盖开发中从入门到生产的全场景需求，帮助读者彻底掌握 Python 与 MySQL 的交互技术。

---

二、技能目标

通过本文的学习，你将掌握以下核心技能：

1. 熟练掌握 Python 连接 MySQL 数据库的基础操作，包括库安装、连接建立、游标使用；
2. 学会使用 Python 执行各类 SQL 语句，完成数据的增、删、改、查、批量操作、模糊查询、联合查询等；
3. 熟悉 MySQL 的四种事务隔离级别，理解其原理、适用场景与性能差异；
4. 掌握 Python 中连接池的使用与优化，解决高并发场景下的数据库连接性能问题；
5. 理解事务管理、错误处理的最佳实践，提升数据库操作的健壮性。

---

三、一、安装 Python MySQL 连接库

在 Python 中操作 MySQL，首先需要安装对应的数据库连接库。目前主流的库有两个：mysql-connector-python（MySQL 官方推荐）和PyMySQL（开源社区常用，兼容 MySQL 协议），两者功能相近，可根据习惯选择。

1. 安装 mysql-connector-python

mysql-connector-python是 MySQL 官方推出的 Python 连接库，完全遵循 MySQL 协议，兼容性强，适合对稳定性要求高的场景。安装命令（pip 工具）：

pip install mysql-connector-python

如果需要指定国内镜像加速安装（解决网络问题）：

pip install mysql-connector-python -i https://pypi.tuna.tsinghua.edu.cn/simple

2. 安装 PyMySQL（作为替代方案）

PyMySQL是开源社区维护的轻量级连接库，语法简洁，与mysql-connector-python高度兼容，是很多开发者的首选替代方案。安装命令：

pip install pymysql

同样可使用国内镜像加速：

pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

本文后续所有示例，均以PyMySQL库为基础进行讲解，mysql-connector-python的使用方法几乎完全一致，仅需修改导入语句即可。

---

四、二、Python 连接 MySQL 数据库

完成库安装后，我们就可以通过 Python 建立与 MySQL 数据库的连接，执行 SQL 操作。完整的连接流程分为 6 个核心步骤：导入连接库、创建数据库连接、创建游标对象、执行 SQL 语句、获取查询结果、关闭连接。

1. 导入连接库

首先需要导入pymysql模块，这是所有操作的基础：

import pymysql

2. 创建数据库连接

使用pymysql.connect()方法建立与 MySQL 服务器的连接，该方法需要传入 MySQL 服务器的核心配置信息：

• host：MySQL 服务器地址，本地服务通常为localhost或127.0.0.1，远程服务器填写对应 IP；
• user：数据库用户名，默认超级管理员为root；
• password：数据库用户对应的密码；
• database：要连接的目标数据库名称；
• 可选参数：port（MySQL 端口，默认 3306）、charset（字符集，推荐utf8mb4）、connect_timeout（连接超时时间）等。

示例代码：

# 创建数据库连接
db = pymysql.connect(
    host="localhost",       # MySQL服务器地址
    user="root",            # 数据库用户名
    password="password",    # 数据库密码
    database="testdb",      # 要连接的数据库名称
    port=3306,              # MySQL端口，默认3306可省略
    charset="utf8mb4"       # 字符集，支持emoji等特殊字符
)

注意：生产环境中绝对不要将密码硬编码在代码中，建议通过环境变量、配置文件（如 yaml、ini）等方式读取，避免信息泄露。

3. 创建游标对象

建立连接后，需要创建游标（Cursor）对象，它是 Python 与 MySQL 交互的桥梁，所有 SQL 语句的执行都通过游标完成。

# 创建游标对象
cursor = db.cursor()

游标对象支持多种类型，默认游标为普通游标，也可创建字典游标（返回字典格式结果，更便于处理）：

# 创建字典游标，查询结果以字典形式返回（键为列名，值为数据）
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

4. 执行 SQL 语句

通过游标对象的execute()方法执行 SQL 语句，这是数据库操作的核心方法。

重要安全提示：执行 SQL 时，必须使用 % s 占位符传递参数，绝对不要使用字符串拼接，否则会引发严重的 SQL 注入攻击风险。

示例（查询 users 表所有数据）：

# 执行SQL查询语句
cursor.execute("SELECT * FROM users")

带参数的查询示例（查询 name 为 Alice 的用户）：

# 使用%s占位符，参数以元组形式传入
cursor.execute("SELECT * FROM users WHERE name = %s", ("Alice",))

5. 获取查询结果

对于SELECT等查询操作，执行execute()后需要通过游标方法获取结果，常用方法有 3 种：

• fetchall()：获取所有查询结果，返回一个包含元组（或字典）的列表；
• fetchone()：获取单条查询结果，返回单个元组（或字典），多次调用依次获取下一条；
• fetchmany(size)：获取指定数量size的结果，返回列表。

示例：

# 获取所有查询结果
results = cursor.fetchall()
# 遍历结果并打印
for row in results:
    print(row)

如果是字典游标，结果格式为{'id': 1, 'name': 'Alice', 'age': 25}，可直接通过列名取值：

for row in results:
    print(f"用户名：{row['name']}，年龄：{row['age']}")

6. 关闭连接

数据库操作完成后，必须关闭游标和数据库连接，释放资源，避免连接泄漏导致数据库性能下降。

# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()

最佳实践：使用try...finally或with语句管理连接，确保无论是否发生异常，连接都能正常关闭。

---

五、三、常见的 MySQL 操作（CRUD 全解析）

掌握基础连接后，我们来详细讲解 Python 中 MySQL 的核心操作：增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT），以及批量操作、模糊查询、联合查询等进阶操作。

1. 插入数据（INSERT）

插入数据使用INSERT INTO语句，通过execute()方法执行，插入后必须调用db.commit()提交事务，否则数据不会真正写入数据库。

单条数据插入

# 插入单条数据：使用%s占位符，参数以元组形式传入
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
# 要插入的数据
data = ("Alice", 25)
# 执行SQL
cursor.execute(sql, data)
# 提交事务，保存数据
db.commit()
print(f"成功插入{cursor.rowcount}条数据")

批量数据插入

使用executemany()方法一次性插入多条数据，效率远高于循环执行execute()：

# 批量插入数据
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
# 多条数据组成的列表
data_list = [
    ("Bob", 30),
    ("Charlie", 35),
    ("David", 28)
]
# 执行批量插入
cursor.executemany(sql, data_list)
# 提交事务
db.commit()
print(f"成功插入{cursor.rowcount}条数据")

2. 更新数据（UPDATE）

更新数据使用UPDATE语句，通常配合WHERE条件，确保只更新目标数据，避免全表更新。

# 更新数据：将name为Alice的用户年龄更新为26
sql = "UPDATE users SET age = %s WHERE name = %s"
data = (26, "Alice")
# 执行SQL
cursor.execute(sql, data)
# 提交事务
db.commit()
print(f"成功更新{cursor.rowcount}条数据")

注意：执行UPDATE前，建议先执行SELECT验证WHERE条件的正确性，避免误更新全表数据。

3. 删除数据（DELETE）

删除数据使用DELETE FROM语句，同样必须配合WHERE条件，否则会删除表中所有数据。

# 删除name为Alice的用户数据
sql = "DELETE FROM users WHERE name = %s"
data = ("Alice",)
# 执行SQL
cursor.execute(sql, data)
# 提交事务
db.commit()
print(f"成功删除{cursor.rowcount}条数据")

高危操作提醒：DELETE操作不可逆，执行前务必做好数据备份，严格验证WHERE条件。

4. 查询数据（SELECT）

查询是数据库最常用的操作，支持条件查询、排序、分页、聚合等多种场景。

全表查询

# 查询users表所有数据
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

条件查询

# 查询年龄大于25的用户
sql = "SELECT * FROM users WHERE age > %s"
cursor.execute(sql, (25,))
results = cursor.fetchall()

分页查询

使用LIMIT实现分页，适合大数据量场景：

# 分页查询：第1页，每页10条数据（LIMIT 偏移量, 条数，偏移量从0开始）
page = 1
page_size = 10
offset = (page - 1) * page_size
sql = "SELECT * FROM users LIMIT %s, %s"
cursor.execute(sql, (offset, page_size))
results = cursor.fetchall()

排序查询

使用ORDER BY对结果排序：

# 按年龄升序查询（ASC为升序，DESC为降序）
sql = "SELECT * FROM users ORDER BY age ASC"
cursor.execute(sql)
results = cursor.fetchall()

5. 执行多条 SQL 语句（executemany）

除了批量插入，executemany()也可用于批量更新、删除等操作，核心是 SQL 语句结构一致，仅参数不同：

# 批量更新多个用户的年龄
sql = "UPDATE users SET age = %s WHERE name = %s"
data_list = [
    (27, "Bob"),
    (36, "Charlie"),
    (29, "David")
]
cursor.executemany(sql, data_list)
db.commit()

6. 使用 LIKE 进行模糊查询

LIKE关键字用于模糊匹配字符串，支持两个通配符：

• %：匹配任意长度（包括 0）的任意字符；
• _：匹配单个任意字符。

示例：

# 查询name以A开头的用户
sql = "SELECT * FROM users WHERE name LIKE %s"
cursor.execute(sql, ("A%",))
results = cursor.fetchall()

# 查询name包含li的用户
sql = "SELECT * FROM users WHERE name LIKE %s"
cursor.execute(sql, ("%li%",))
results = cursor.fetchall()

7. 使用 JOIN 进行联合查询

当数据存储在多个关联表中时，使用JOIN关键字进行多表联合查询，常见的 JOIN 类型有：

• INNER JOIN（内连接）：只返回两个表中匹配的记录；
• LEFT JOIN（左连接）：返回左表所有记录，右表无匹配则显示 NULL；
• RIGHT JOIN（右连接）：返回右表所有记录，左表无匹配则显示 NULL。

示例（users 表与 orders 表关联，查询用户订单信息）：

# 内连接查询：查询有订单的用户及其订单金额
sql = """
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
"""
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
    print(f"用户：{row[0]}，订单金额：{row[1]}")

---

六、四、使用连接池优化数据库连接

在高并发场景下，频繁创建和销毁数据库连接会带来巨大的性能开销，而连接池（Connection Pool） 是解决该问题的核心方案。

1. 连接池简介

连接池技术的核心原理是：预先创建一定数量的数据库连接，放入连接池中统一管理。当应用需要操作数据库时，直接从连接池中获取一个空闲连接；使用完成后，将连接归还到池中，而非销毁。

连接池的优势：

• 性能提升：大幅减少连接创建 / 销毁的开销，提升数据库操作效率；
• 资源管控：限制最大连接数，避免过多连接导致数据库过载；
• 统一管理：统一管理连接的生命周期，简化代码结构，避免连接泄漏。

2. 创建连接池

PyMySQL 本身不直接支持连接池，我们可以使用DBUtils库中的PooledDB来实现连接池。首先安装 DBUtils：

pip install dbutils

安装完成后，创建连接池的核心代码如下：

from dbutils.pooled_db import PooledDB
import pymysql

# 数据库连接配置
dbconfig = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "testdb",
    "port": 3306,
    "charset": "utf8mb4"
}

# 创建连接池
connection_pool = PooledDB(
    creator=pymysql,          # 使用PyMySQL作为数据库连接库
    maxconnections=5,          # 连接池最大连接数，0表示无限制（不推荐）
    mincached=2,               # 连接池初始化时创建的空闲连接数
    maxcached=3,              # 连接池最大空闲连接数，超过则销毁多余连接
    maxshared=0,              # 连接池最大共享连接数，0表示所有连接私有
    blocking=True,            # 连接池满时，是否阻塞等待（True阻塞，False报错）
    setsession=[],            # 连接创建时执行的SQL语句列表
    ping=1,                    # 检查连接有效性的时机（1=从池中获取时检查）
    **dbconfig                 # 数据库连接配置
)

关键参数说明：

• maxconnections：根据数据库最大连接数（MySQL 默认最大连接数为 151）和业务并发量设置，避免超过数据库上限；
• ping：设置为 1 可自动检测连接有效性，避免使用已失效的连接；
• blocking：高并发场景建议设为 True，避免连接不足时程序报错。

3. 从连接池获取连接并操作

创建连接池后，通过connection()方法获取连接，操作完成后关闭连接（自动归还到池中）：

# 从连接池获取连接
db_connection = connection_pool.connection()
# 创建游标
cursor = db_connection.cursor()

# 执行SQL操作
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# 关闭游标
cursor.close()
# 关闭连接（自动归还到连接池，而非销毁）
db_connection.close()

注意：从连接池获取的连接，关闭操作只是将连接归还到池中，不会真正销毁，因此无需担心性能开销。

4. 连接池的优势总结

表格

特性	普通连接	连接池
连接创建 / 销毁	每次操作都创建 / 销毁，开销大	预先创建，复用连接，开销极小
资源管控	无限制，易导致数据库过载	限制最大连接数，保护数据库
并发性能	高并发下性能急剧下降	高并发下性能稳定，吞吐量高
代码复杂度	需手动管理连接生命周期	统一管理，代码更简洁

---

七、五、事务管理与隔离级别

事务是数据库保证数据一致性的核心机制，在 Python 操作 MySQL 时，事务管理是必须掌握的核心技能。

1. 事务简介

事务是由多个 SQL 语句组成的逻辑工作单元，事务必须满足 ACID 四大特性：

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败回滚；
• 一致性（Consistency）：事务执行前后，数据库的完整性约束不被破坏；
• 隔离性（Isolation）：多个并发事务之间相互隔离，互不干扰；
• 持久性（Durability）：事务提交后，对数据的修改永久生效，不会因系统故障而丢失。

2. 事务的核心操作

（1）开启事务

MySQL 默认自动开启事务，也可通过START TRANSACTION手动开启：

cursor.execute("START TRANSACTION")

（2）提交事务

当事务中所有操作执行成功后，调用commit()提交事务，数据永久生效：

db.commit()

（3）回滚事务

当事务中任意操作失败时，调用rollback()回滚事务，撤销所有修改：

db.rollback()

重要提示：PyMySQL 默认开启自动提交吗？答：PyMySQL 默认关闭自动提交，因此执行 INSERT/UPDATE/DELETE 后，必须手动调用commit()，否则数据不会写入数据库；而mysql-connector-python默认开启自动提交，需注意差异。

3. 事务的隔离级别

事务的隔离级别用于控制多个并发事务之间的相互影响，MySQL 支持 4 种隔离级别，从低到高依次为：

• READ UNCOMMITTED（未提交读）
• READ COMMITTED（已提交读）
• REPEATABLE READ（可重复读，MySQL 默认）
• SERIALIZABLE（串行化）

（1）READ UNCOMMITTED（未提交读）

• 原理：允许事务读取其他事务未提交的数据；
• 问题：会出现脏读（读取到未提交的临时数据，若其他事务回滚，数据失效）；
• 性能：最高，隔离性最低；
• 适用场景：对数据一致性要求极低的场景，几乎不推荐使用；
• 设置方法：

cursor.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

（2）READ COMMITTED（已提交读）

• 原理：仅允许事务读取其他事务已提交的数据；
• 解决的问题：避免了脏读；
• 存在的问题：会出现不可重复读（同一事务中，两次读取同一数据，结果不一致，因为其他事务提交了修改）；
• 性能：较高；
• 适用场景：大多数业务场景，如电商普通订单查询；
• 设置方法：

cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")

（3）REPEATABLE READ（可重复读，MySQL 默认）

• 原理：同一事务中，多次读取同一数据，结果一致；
• 解决的问题：避免了脏读、不可重复读；
• 存在的问题：会出现幻读（同一事务中，两次查询同一范围的数据，结果行数不一致，因为其他事务插入了新数据）；
• 性能：中等；
• 适用场景：对数据一致性要求较高的场景，如金融转账、库存扣减；
• 设置方法：

cursor.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")

（4）SERIALIZABLE（串行化）

• 原理：所有事务串行执行，完全隔离；
• 解决的问题：避免了脏读、不可重复读、幻读；
• 性能：最低，并发能力差；
• 适用场景：对数据一致性要求极高的场景，如金融核心交易；
• 设置方法：

cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")

4. 事务隔离级别对比总结

表格

隔离级别	脏读	不可重复读	幻读	并发性能	适用场景
READ UNCOMMITTED	存在	存在	存在	最高	几乎不使用
READ COMMITTED	避免	存在	存在	较高	普通业务场景
REPEATABLE READ	避免	避免	存在	中等	MySQL 默认，金融、库存等
SERIALIZABLE	避免	避免	避免	最低	核心金融交易

核心结论：隔离级别越高，数据一致性越好，但并发性能越低。开发中需根据业务场景选择合适的隔离级别，MySQL 默认的REPEATABLE READ是绝大多数场景的最优选择。

5. 事务完整案例（错误处理 + 回滚）

下面是一个完整的事务操作案例，包含异常捕获、提交 / 回滚逻辑，是生产环境的标准写法：

import pymysql

# 连接数据库
conn = pymysql.connect(
    host="localhost",
    user="root",
    password="password",
    database="testdb",
    charset="utf8mb4"
)
# 创建游标
cursor = conn.cursor()
# 关闭自动提交（默认已关闭，显式声明更安全）
conn.autocommit = False

try:
    # 开启事务
    cursor.execute("START TRANSACTION")
    
    # 事务操作1：插入用户数据
    sql1 = "INSERT INTO users (name, age) VALUES (%s, %s)"
    cursor.execute(sql1, ("Eve", 22))
    
    # 事务操作2：插入订单数据（关联用户ID）
    sql2 = """
    INSERT INTO orders (user_id, amount) 
    VALUES ((SELECT id FROM users WHERE name = %s), %s)
    """
    cursor.execute(sql2, ("Eve", 199.9))
    
    # 所有操作成功，提交事务
    conn.commit()
    print("事务执行成功，数据已保存")

except pymysql.MySQLError as err:
    # 发生异常，回滚事务
    print(f"事务执行失败，错误信息：{err}")
    conn.rollback()
    print("事务已回滚，数据已撤销")

finally:
    # 无论成功失败，都关闭游标和连接
    cursor.close()
    conn.close()
    print("数据库连接已关闭")

---

八、六、最佳实践与性能优化

1. 连接管理最佳实践

• 使用连接池：生产环境必须使用连接池，避免频繁创建 / 销毁连接；
• 避免长连接：不要长时间占用数据库连接，操作完成后立即归还；
• 参数化查询：永远使用 % s 占位符，杜绝 SQL 注入；
• 环境变量存储密码：不要硬编码数据库密码，通过环境变量、配置文件读取。

2. 事务管理最佳实践

• 缩短事务时长：事务中不要包含耗时操作（如网络请求、文件读写），避免锁表；
• 合理设置隔离级别：根据业务选择隔离级别，不要盲目使用最高隔离级别；
• 异常处理 + 回滚：所有事务操作必须包含异常捕获和回滚逻辑，避免数据不一致；
• 批量操作替代循环：使用executemany()批量操作，减少事务次数。

3. 性能优化技巧

• 索引优化：为查询条件、关联字段创建索引，大幅提升查询效率；
• 分页查询：大数据量场景使用LIMIT分页，避免全表查询；
• ** 避免 SELECT ***：只查询需要的字段，减少数据传输量；
• 批量提交：批量操作时，一次提交事务，而非逐条提交；
• 连接池参数调优：根据业务并发量调整连接池大小，避免连接不足或资源浪费。

---

九、总结

本文系统、全面地讲解了 Python 操作 MySQL 数据库的全流程技术，从基础的库安装、连接建立，到 CRUD 操作、连接池优化，再到事务管理与隔离级别，覆盖了开发中从入门到生产的全场景需求。