Python3 MySQL 数据库连接 - PyMySQL 驱动详解：从入门到精通

前言

在当今的数据驱动时代，几乎所有应用都离不开与数据库的交互。Python，作为最流行和高效的编程语言之一，提供了多种方式来连接和操作关系型数据库。其中，MySQL 以其高性能、高可靠性和开源免费的特性，成为了无数开发者的首选。而在 Python 生态中，PyMySQL 无疑是连接 MySQL 最耀眼、最主流的驱动之一。本指南将带你从零开始，深入掌握使用 PyMySQL 驱动 Python 与 MySQL 数据库进行交互的全套技能，涵盖从基础连接到高级性能优化和安全管理。

1. 为什么选择 PyMySQL？

在开始之前，我们需要了解为什么 PyMySQL 如此受欢迎。目前 Python 连接 MySQL 主要有三个驱动：PyMySQL、mysql-connector-python 和 mysqlclient（即 MySQLdb）。它们各有优劣，但对于大多数现代 Python 3 项目而言，PyMySQL 具有显著优势。

• 纯 Python 实现：PyMySQL 是一个纯 Python 写的 MySQL 客户端库，不依赖任何 C 扩展或外部库。这一点至关重要，因为它消除了编译环境的依赖，无论是在 Windows、Linux 还是 macOS 上，都能做到“一次安装，处处运行”，极大地降低了部署门槛。
• 安装极其简单：与需要 C 编译器的 mysqlclient 不同，PyMySQL 的安装一条 pip 命令即可搞定。
• 完美的 Python 3 支持：它是为 Python 3 设计的，完美支持 Python 3.6 及以上版本（官方最新要求 CPython >= 3.9）。
• 社区活跃，文档完善：作为开源项目，PyMySQL 拥有非常活跃的社区支持和完善的官方文档，遇到问题可以轻松找到解决方案。
• 与 MySQLdb 兼容：PyMySQL 可以作为 MySQLdb 的“即插即用”替代品。你可以通过 pymysql.install_as_MySQLdb() 函数，让那些原本依赖 MySQLdb 的旧代码无缝迁移到 Python 3 环境。

结论：对于绝大多数不需要极致性能碾压的 Python Web 应用、数据分析脚本和自动化工具，PyMySQL 都是最平衡、最明智的选择。

2. 环境准备与安装

在编写任何代码之前，请确保你的环境满足以下条件。

2.1 前提条件

• Python 环境：确保你安装的是 Python 3.6 或更高版本（官方推荐 >= 3.9）。可以通过输入 python --version 或 python3 --version 来验证。
• MySQL 服务：确保你的本地或远程 MySQL（或 MariaDB）服务已经安装并启动。PyMySQL 支持 MySQL >= 5.7 和 MariaDB >= 10.3。你可以通过 mysql -u root -p 命令在终端测试能否成功登录 MySQL。
• 测试数据库：为了后续的实战演练，建议你提前创建一个测试数据库和表。例如，在 MySQL 客户端中执行以下 SQL 语句：

  -- 创建数据库
  CREATE DATABASE IF NOT EXISTS test_pymysql 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
  
  -- 使用数据库
  USE test_pymysql;
  
  -- 创建用户表
  CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(50) NOT NULL,
      age INT NOT NULL,
      email VARCHAR(100) UNIQUE NOT NULL,
      create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  这条语句创建了一个名为 test_pymysql 的数据库和一张 users 测试表，并指定了字符集为支持中文和 emoji 的 utf8mb4。

2.2 安装 PyMySQL

安装过程极其简单，只需一行命令：

pip install pymysql

如果你使用的是 Python 3，并且系统同时安装了 Python 2，可能需要使用 pip3：

pip3 install pymysql

对于需要特定加密认证（如 caching_sha2_password）的场景，可以安装额外依赖：

pip install PyMySQL[rsa]

安装完成后，在 Python 终端中验证是否成功：

import pymysql
print(pymysql.__version__)  # 输出类似 1.1.1 即表示成功

3. 建立数据库连接

连接数据库是进行任何操作的第一步。PyMySQL 使用 pymysql.connect() 函数来建立连接。

3.1 核心连接参数

connect() 方法需要你提供一系列参数来指定数据库服务器的信息。理解这些参数至关重要。

参数名	说明	默认值	示例
host	数据库服务器的主机名或 IP 地址。	localhost	'127.0.0.1'
port	数据库服务监听的端口号。	3306	3306
user	用于连接数据库的用户名。	无	'root'
password	对应用户的密码。	无	'your_password'
database	连接后默认使用的数据库名称。	无	'test_pymysql'
charset	通信使用的字符集。强烈建议使用 'utf8mb4' 以完整支持中文和特殊字符。	'utf8'	'utf8mb4'
cursorclass	游标类型，决定了查询结果的返回格式。	Cursor (返回元组)	DictCursor (返回字典)
autocommit	是否开启自动提交事务。	False	True 或 False
connect_timeout	连接超时时间（秒）。	10	10

3.2 基本连接示例

以下是一个标准的数据库连接代码片段，它展示了如何建立连接、验证连接并最终安全地关闭它。

import pymysql
from pymysql.cursors import DictCursor

# 连接配置
config = {
    "host": "localhost",
    "user": "root",
    "password": "your_mysql_password",  # 替换为你的密码
    "database": "test_pymysql",
    "port": 3306,
    "charset": "utf8mb4",
    "cursorclass": DictCursor  # 使用字典游标，使结果更易读
}

try:
    # 建立连接
    conn = pymysql.connect(**config)
    print("数据库连接成功!")

    # 此时可以执行数据库操作...
    # ...

except pymysql.MySQLError as e:
    # 捕获连接相关的错误
    print(f"数据库连接失败: {e}")
finally:
    # 在 finally 块中确保连接被关闭，避免资源泄露
    if 'conn' in locals() and conn.open:
        conn.close()
        print("数据库连接已关闭")

重要提示：

• 资源管理：数据库连接是宝贵的系统资源，使用后务必关闭。try...finally 是一种非常可靠的模式。在 Python 中，连接对象本身不直接支持 with 语句，所以我们通常手动管理或使用连接池来优化。
• 密码安全：永远不要在代码中硬编码密码。在生产环境中，应将密码存储在环境变量或配置文件（并添加到 .gitignore 中）中。

4. 核心操作：数据读写（CRUD）

建立连接后，我们需要通过 游标（Cursor） 来执行 SQL 语句。游标是操作数据库的核心对象，负责发送 SQL 命令和获取结果。

4.1 游标对象

游标可以通过连接对象的 cursor() 方法获得。你可以指定其返回结果的格式。

# 获取默认游标（返回结果集为元组）
cursor = conn.cursor()

# 获取字典游标（推荐，返回结果集为字典，键为列名）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

为什么推荐 DictCursor？

• 通过列名访问数据（如 row['name']），代码可读性更高。
• 避免了因查询字段顺序变化导致的代码错误。
• 与 JSON 序列化天然兼容。

4.2 查询操作（SELECT）

查询是数据库最频繁的操作。执行查询使用 cursor.execute()，获取结果则通过以下方法：

• fetchone(): 获取结果集的下一行（单条记录）。
• fetchall(): 获取所有剩余的行（返回列表）。
• fetchmany(size): 获取指定 size 条记录。

示例：条件查询

try:
    # SQL 语句，使用 %s 作为占位符
    sql = "SELECT id, name, age, email FROM users WHERE age > %s"

    # 执行查询，参数通过元组传递
    cursor.execute(sql, (18,))

    # 获取所有结果
    users = cursor.fetchall()
    
    for user in users:
        # 如果使用了 DictCursor，可以直接通过列名访问
        print(f"ID: {user['id']}, Name: {user['name']}, E-mail: {user['email']}")
        
except pymysql.MySQLError as e:
    print(f"查询失败: {e}")

4.3 插入操作（INSERT）

插入数据后，必须调用 conn.commit() 提交事务，数据才会被真正写入数据库。否则，当连接关闭时，所有更改都会丢失。通过 cursor.lastrowid 可以获取刚插入记录的自增 ID。

try:
    sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
    data = ("张三", 25, "zhangsan@example.com")

    cursor.execute(sql, data)
    # 提交事务，这是关键步骤！
    conn.commit()
    
    print(f"插入成功，新记录的 ID 为: {cursor.lastrowid}")
    
except pymysql.MySQLError as e:
    # 如果出错，回滚事务，撤销任何未提交的更改
    conn.rollback()
    print(f"插入失败，已回滚: {e}")

4.4 批量插入（executemany）

当需要插入大量数据时，使用 executemany() 方法可以显著提升性能，因为它减少了与数据库服务器之间的网络往返次数。

try:
    sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
    # 准备一个包含多条数据的列表
    data_list = [
        ("李四", 30, "lisi@example.com"),
        ("王五", 28, "wangwu@example.com"),
        ("赵六", 35, "zhaoliu@example.com"),
    ]

    # 使用 executemany 执行批量插入
    cursor.executemany(sql, data_list)
    conn.commit()
    
    print(f"批量插入成功，共插入 {cursor.rowcount} 条记录")

except pymysql.MySQLError as e:
    conn.rollback()
    print(f"批量插入失败: {e}")

4.5 更新与删除操作（UPDATE & DELETE）

更新和删除操作与插入类似，都需要提交事务，并通过 cursor.rowcount 属性获取操作影响的行数。

# 更新操作
try:
    sql = "UPDATE users SET age = %s WHERE name = %s"
    cursor.execute(sql, (26, "张三"))
    conn.commit()
    print(f"更新成功，影响了 {cursor.rowcount} 行数据")
except pymysql.MySQLError as e:
    conn.rollback()
    print(f"更新失败: {e}")

# 删除操作
try:
    sql = "DELETE FROM users WHERE id = %s"
    cursor.execute(sql, (1,))
    conn.commit()
    print(f"删除成功，影响了 {cursor.rowcount} 行数据")
except pymysql.MySQLError as e:
    conn.rollback()
    print(f"删除失败: {e}")

5. 安全性：防范 SQL 注入

这是数据库操作中最重要的一环，绝对不可忽视！ SQL 注入是一种常见且危险的安全漏洞，攻击者可以通过在输入中嵌入恶意 SQL 代码，从而操控你的数据库。

错误示例（绝对禁止！）：

# 危险！用户输入可能包含恶意代码，如 name = "张三' OR '1'='1"
user_input = "张三' OR '1'='1"
sql = f"SELECT * FROM users WHERE name = '{user_input}'"
cursor.execute(sql)  # 这条语句将返回所有用户，因为 OR '1'='1' 总是为真

正确做法：参数化查询：
PyMySQL 强制使用 %s 作为参数占位符，而不是其他库中的 ?。所有用户输入都通过 execute() 方法的第二个参数（一个元组或列表）传递，驱动会自动对其进行转义和引用，确保输入被当作纯数据，而不是 SQL 代码的一部分。

# 安全！即使用户输入包含恶意字符，也会被安全处理
user_input = "张三' OR '1'='1"
sql = "SELECT * FROM users WHERE name = %s"
cursor.execute(sql, (user_input,))  # 安全，数据库只查找名为该字符串的用户

牢记：永远不要使用字符串拼接或 f-string 来构造 SQL 语句中的动态值部分。这是保护数据安全的基本红线。

6. 事务管理

事务用于确保一系列数据库操作具有“原子性”，即要么全部成功，要么全部失败（如在银行转账场景中，扣钱和加钱必须同时成立）。

PyMySQL 默认不开启自动提交（autocommit=False），这给了我们手动控制事务的灵活性。

try:
    # 操作1：扣钱
    sql1 = "UPDATE accounts SET balance = balance - 100 WHERE user_id = %s"
    cursor.execute(sql1, (1,))
    
    # 操作2：加钱
    sql2 = "UPDATE accounts SET balance = balance + 100 WHERE user_id = %s"
    cursor.execute(sql2, (2,))
    
    # 如果以上两步都成功，提交事务
    conn.commit()
    print("转账成功")

except pymysql.MySQLError as e:
    # 如果任一步骤出错，回滚所有操作，保证数据一致性
    conn.rollback()
    print(f"转账失败，已回滚: {e}")

7. 进阶技巧与性能优化

7.1 使用连接池

在高并发场景（如 Web 应用）下，频繁地创建和关闭数据库连接会带来巨大的性能开销。连接池技术可以预先创建并维护一组连接，当需要时从池中取出，用完后归还（而不是关闭），从而复用连接，极大地提升了应用性能和资源利用率。

推荐方案：DBUtils (需要注意的是，pymysqlpool 库在搜索结果中被提到，但 DBUtils 更为通用和流行)

安装 DBUtils：

pip install DBUtils

使用 PooledDB 创建连接池：

from dbutils.pooled_db import PooledDB
import pymysql

pool_config = {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "test_pymysql",
    "charset": "utf8mb4",
    "autocommit": False,
    "cursorclass": pymysql.cursors.DictCursor,
    "maxconnections": 10,   # 连接池允许的最大连接数
    "mincached": 2,         # 初始化时建立的空闲连接数
    "maxcached": 5,         # 池中允许的最大空闲连接数
    "blocking": True        # 无可用连接时，是否阻塞等待
}

# 创建连接池对象（全局唯一）
pool = PooledDB(pymysql, **pool_config)

# 在任何需要的地方，从池中获取连接
def get_user(user_id):
    conn = pool.connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        return cursor.fetchone()
    finally:
        # 注意：这里不是真正关闭，而是将连接归还给连接池
        cursor.close()
        conn.close()

# 使用
user = get_user(1)

7.2 游标类型详细对比

选择正确的游标类型可以优化性能和内存使用：

游标类型	描述	适用场景
Cursor	默认游标。客户端游标，结果集一次从服务端拉到客户端内存中，返回元组。	数据量小，对性能不敏感的简单场景。
DictCursor	与 Cursor 类似，但结果以字典形式返回。	大多数场景的推荐选择。
SSCursor	服务端游标。结果集不缓存到客户端，而是留在服务端，客户端逐行获取。	处理百万级、千万级的大数据量，节省客户端内存。但会长时间占用数据库连接。
SSDictCursor	服务端游标 + 字典结果。	大数据量且需要字典格式。

7.3 异常处理详析

PyMySQL 遵循标准的数据库异常层级。捕获具体的异常有助于进行精确的错误处理和调试。

try:
    # 可能引发各种错误的数据库操作
    cursor.execute("SELECT * FROM non_existent_table")
except pymysql.ProgrammingError as e:
    # SQL 语法错误、表不存在等程序性错误
    print(f"SQL 编程错误: {e}")
except pymysql.OperationalError as e:
    # 与数据库连接或操作环境相关的错误（如连接丢失、表锁）
    print(f"数据库操作错误: {e}")
except pymysql.IntegrityError as e:
    # 数据完整性错误（如主键冲突、外键约束失败）
    print(f"数据完整性错误: {e}")
except pymysql.DataError as e:
    # 数据格式或类型错误
    print(f"数据错误: {e}")
except pymysql.MySQLError as e:
    # 所有 MySQL 异常的基类
    print(f"通用数据库错误: {e}")
except Exception as e:
    # 处理其他所有未知异常
    print(f"未知错误: {e}")

8. 常见问题与解决方法

8.1 安装失败：No module named 'pymysql'

问题：提示找不到模块。
解决：使用 pip install pymysql 重新安装。如果系统有多个 Python 版本，确保使用正确的 pip（如 pip3）。

8.2 连接失败：(2003, "Can't connect to MySQL server on 'localhost'")

问题：Python 无法连接到 MySQL 服务器。
解决：

1. 检查服务状态：确认 MySQL 服务已启动（Windows: net start mysql, Linux: systemctl status mysql）。
2. 检查连接参数：确认 host、port、user、password 是否正确。
3. 防火墙与网络：如果连接远程数据库，确保服务器防火墙放行 3306 端口，并且 MySQL 用户授权了客户端的 IP 地址。

8.3 插入中文乱码：Incorrect string value

问题：插入或查询中文时出现乱码或报错。
解决：这通常是由于字符集不统一造成的。

1. 在 connect() 时设置 charset='utf8mb4'。
2. 确保数据库、数据表的字符集也是 utf8mb4。可以使用 SQL 命令检查和修改：

   ALTER DATABASE test_pymysql CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

8.4 修改不生效：数据没有写入数据库

问题：INSERT、UPDATE、DELETE 操作后，数据库没有变化。
解决：最常见的原因是忘记提交事务。确保在执行写操作后调用了 conn.commit()。

8.5 SQL 语法错误：(1064, ...)

问题：执行 SQL 时报 1064 错误。
解决：

1. 仔细检查 SQL 语句的拼写，特别是保留了关键字的字段名（如 order, group）是否使用了反引号 ` 包裹。
2. 确认表名、字段名是否存在。
3. 检查参数占位符是否为 %s。

9. 总结

PyMySQL 作为 Python 生态中最流行的 MySQL 驱动，以其纯 Python 实现的简洁性、安装的便捷性和强大的功能，成为了现代 Python 开发者的不二之选。

核心要点回顾：

1. 安装：pip install pymysql。
2. 连接：使用 pymysql.connect()，并配置正确的连接参数，强烈推荐使用 charset='utf8mb4' 和 cursorclass=DictCursor。
3. CRUD：通过游标 cursor.execute() 执行 SQL 语句。所有写操作（增、删、改）后必须执行 conn.commit() 提交事务。
4. 安全：永远使用参数化查询（%s 占位符）来防止 SQL 注入攻击。
5. 性能：对于频繁的数据库操作，使用连接池（如 DBUtils）进行连接复用。对于批量数据，使用 executemany() 提升效率。
6. 事务：理解并利用事务的 commit() 和 rollback() 来保证数据的一致性。
7. 排错：善于利用异常处理来定位和解决字符集、连接、语法等常见问题。