PyMySQL连接MySQL教程详解

本文详细介绍了如何使用PyMySQL这一纯Python实现的MySQL驱动进行数据库连接与操作。从安装PyMySQL开始，逐步讲解了如何配置连接参数、建立连接、执行SQL语句，并特别强调了字符集设置以避免中文乱码问题。文章还深入探讨了PyMySQL相比其他驱动的优势，例如其轻量级和跨平台特性，以及`DictCursor`带来的便利。此外，还分享了实际项目中高效且安全地管理数据库连接的最佳实践，包括使用环境变量或配置文件管理数据库凭证，以及利用DBUtils等工具构建连接池来提升高并发场景下的性能。最后，针对大数据量查询，提出了使用SSCursor实现逐行读取，防止内存溢出的优化策略。掌握这些技巧，能让你在Python项目中轻松应对各种MySQL数据库操作。

答案：PyMySQL是纯Python实现的MySQL驱动，安装简单、跨平台兼容性好，支持参数化查询和DictCursor返回字典结果，避免SQL注入并提升代码可读性；实际项目中应通过环境变量或配置文件管理数据库凭证以确保安全，并使用DBUtils等工具构建连接池提升高并发场景下的性能；处理大数据量时可采用SSCursor实现逐行读取，防止内存溢出。

说起Python连接MySQL，PyMySQL绝对是绕不开的一个高效且友好的选择。其实，整个流程并不复杂：无非就是导入模块、配置连接参数、建立那个‘桥梁’（连接对象）、然后通过‘笔杆子’（游标）去执行你的SQL语句，最后别忘了收拾残局——提交事务或者回滚，然后关闭连接。掌握了这几步，你就能让Python程序和MySQL数据库愉快地“对话”了。

在Python项目中，连接MySQL数据库并进行数据操作，PyMySQL是一个非常流行的选择。它完全用Python实现，不需要额外的C语言库依赖，这使得它在部署和跨平台兼容性上表现出色。

我们先从最基础的安装开始，毕竟巧妇难为无米之炊：

pip install PyMySQL

安装完成后，接下来就是编写代码实现连接与操作。这里我会给出一个比较完整的示例，涵盖了连接、插入、查询以及必要的错误处理和资源关闭。

import pymysql
import pymysql.cursors # 引入游标类型，方便后续使用DictCursor

# 数据库连接参数，这些通常不应该硬编码在代码里，后面会提到更好的管理方式
DB_CONFIG = {
    'host': 'localhost',  # 数据库服务器地址，如果是远程服务器，请填写对应IP或域名
    'user': 'your_mysql_user',  # 你的MySQL用户名
    'password': 'your_mysql_password',  # 你的MySQL密码
    'database': 'your_database_name',  # 要连接的数据库名称
    'port': 3306,  # MySQL默认端口
    'charset': 'utf8mb4',  # 字符集，非常重要，避免中文乱码问题
    'cursorclass': pymysql.cursors.DictCursor # 默认返回元组，DictCursor返回字典，更方便处理
}

conn = None # 初始化连接对象
cursor = None # 初始化游标对象

try:
    # 建立数据库连接
    # 这里的**DB_CONFIG是将字典解包成关键字参数，很Pythonic的写法
    conn = pymysql.connect(**DB_CONFIG)
    print("数据库连接成功！")

    # 创建游标对象
    # 如果没有指定cursorclass，默认是pymysql.cursors.Cursor，返回元组
    cursor = conn.cursor()

    # --- 示例1：创建一张表 (DDL操作) ---
    # 实际项目中，表结构通常在数据库初始化时就确定了，这里只是演示
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE,
        age INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
    cursor.execute(create_table_sql)
    # DDL操作通常是自动提交的，但显式提交也无妨
    conn.commit()
    print("表 'users' 检查或创建成功。")

    # --- 示例2：插入数据 (DML操作) ---
    insert_sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
    user_data = ('张三', 'zhangsan@example.com', 30)
    cursor.execute(insert_sql, user_data) # 参数化查询，防止SQL注入
    print(f"插入了 {cursor.rowcount} 条数据。")

    # 批量插入数据
    users_to_insert = [
        ('李四', 'lisi@example.com', 25),
        ('王五', 'wangwu@example.com', 35),
        ('赵六', 'zhaoliu@example.com', 28)
    ]
    cursor.executemany(insert_sql, users_to_insert)
    print(f"批量插入了 {cursor.rowcount} 条数据。")

    # 提交事务，保存更改到数据库
    conn.commit()
    print("数据插入并提交成功。")

    # --- 示例3：查询数据 (DQL操作) ---
    select_sql = "SELECT id, name, email, age, created_at FROM users WHERE age > %s"
    cursor.execute(select_sql, (25,)) # 参数必须是元组，即使只有一个参数也要加逗号

    # 获取所有查询结果
    results = cursor.fetchall()
    print("\n查询结果 (年龄大于25的用户):")
    for row in results:
        print(row) # DictCursor会打印字典，方便通过键访问字段

    # 获取单条查询结果
    select_one_sql = "SELECT name FROM users WHERE id = %s"
    cursor.execute(select_one_sql, (1,))
    one_result = cursor.fetchone()
    if one_result:
        print(f"\nID为1的用户姓名: {one_result['name']}")

except pymysql.Error as e:
    # 捕获PyMySQL特有的异常
    print(f"数据库操作发生错误: {e}")
    if conn:
        conn.rollback() # 发生错误时回滚事务，撤销未提交的更改
        print("事务已回滚。")

except Exception as e:
    # 捕获其他通用异常
    print(f"发生未知错误: {e}")
    if conn:
        conn.rollback()
        print("事务已回滚。")

finally:
    # 确保在任何情况下都关闭游标和连接
    if cursor:
        cursor.close()
        print("游标已关闭。")
    if conn:
        conn.close()
        print("数据库连接已关闭。")

在实际操作中，我个人觉得`charset='utf8mb4'`和`cursorclass=pymysql.cursors.DictCursor`这两个配置特别重要。前者能有效避免各种字符编码问题，尤其是处理中文时，而后者则让查询结果以字典形式返回，比元组更直观，通过字段名就能访问数据，代码可读性会好很多。至于参数化查询，那更是防止SQL注入的基石，任何时候都应该坚持使用。

### PyMySQL相比其他Python数据库驱动（如mysql-connector-python）有何独特之处？

在我看来，选择数据库驱动，就像是选择一款工具，各有各的哲学和适用场景。PyMySQL和`mysql-connector-python`都是Python连接MySQL的常用选择，但它们确实有各自的特点。

PyMySQL最显著的特点，也是我个人偏爱它的原因之一，就是它是**纯Python实现**。这意味着它不依赖任何C语言扩展或者外部编译库。对于开发者而言，这意味着安装和部署过程通常更简单、更少遇到兼容性问题，尤其是在一些对环境有严格要求的部署场景下，这简直是福音。你不需要担心系统上有没有安装特定的开发工具链，`pip install PyMySQL`往往就能一劳永逸。

相比之下，`mysql-connector-python`是Oracle官方提供的MySQL连接器。它的优势在于“官方”二字，通常意味着对MySQL新特性和协议的更新支持会更及时、更全面。但它的缺点也可能在于，有时它会依赖一些C语言库，这在某些环境下可能会导致安装过程略显复杂，或者出现一些编译问题。

另一个值得一提的是，PyMySQL在性能上往往表现不俗，而且它的API设计非常Pythonic，用起来很顺手。特别是它提供的`DictCursor`，能够直接将查询结果封装成字典，这在处理数据时简直不要太方便，直接通过字段名访问数据，代码清晰度提升不止一个档次。`mysql-connector-python`也有类似的功能，但PyMySQL的纯Python特性让它在轻量级应用和快速原型开发中更具吸引力。

总的来说，如果你追求**部署的简便性、纯Python的优雅，以及对Python 3的良好兼容性**，PyMySQL会是一个非常棒的选择。而如果你需要**严格遵循官方标准、追求最新MySQL特性的无缝支持，或者在特定企业级环境中对官方支持有硬性要求**，那么`mysql-connector-python`可能更适合你。在我日常的开发中，PyMySQL往往是我的首选。

### 在实际项目中，如何高效且安全地管理PyMySQL的数据库连接？

在真实的项目开发中，数据库连接的管理可不仅仅是写几行代码连接关闭那么简单。它涉及到安全性和效率两个核心维度，这两者往往需要精心的设计和权衡。

**安全性方面：绝不硬编码凭证**

这是首要原则，也是我看到很多新手容易犯的错误。将数据库的用户名、密码、主机等敏感信息直接写在代码里，简直是把“请来攻击我”写在了脸上。正确的做法应该是：

1.  **环境变量：** 这是最简单也最常用的方法之一。将数据库凭证配置为操作系统的环境变量。在Python代码中，通过`os.getenv()`来获取。这样，你的代码可以保持通用，而部署环境则负责提供正确的凭证。
    ```python
    import os
    # ...
    DB_CONFIG = {
        'host': os.getenv('DB_HOST', 'localhost'), # 提供一个默认值，方便本地开发
        'user': os.getenv('DB_USER'),
        'password': os.getenv('DB_PASSWORD'),
        'database': os.getenv('DB_NAME'),
        'port': int(os.getenv('DB_PORT', 3306)),
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }

2. 配置文件： 对于更复杂的配置，可以使用.ini文件（如configparser库）、.env文件（如python-dotenv库）或YAML文件来存储。这些文件应该被妥善管理，例如不在版本控制中提交敏感信息，或者加密存储。
3. 密钥管理服务： 在大型企业级应用中，可以利用专业的密钥管理服务（如AWS Secrets Manager, HashiCorp Vault等）。这些服务能够集中管理、加密和轮换数据库凭证，提供更高的安全性和审计能力。

我个人的经验是，对于中小型项目，环境变量通常就足够了，它简单、有效且易于部署。

效率方面：引入连接池

每次应用程序需要与数据库交互时都建立一个新的连接，然后操作完毕再关闭它，这个过程其实是相当耗费资源的。建立连接需要进行网络握手、身份验证等一系列操作，这些开销在并发量大或者操作频繁的场景下会显著降低应用性能。

连接池（Connection Pool）就是为了解决这个问题而生的。它的核心思想是：预先创建好一定数量的数据库连接，并将它们放入一个“池子”中。当应用程序需要连接时，不是新建一个，而是从池子里“借用”一个；用完之后，也不是关闭它，而是将它“归还”到池子里，供其他请求复用。

PyMySQL本身不提供内置的连接池，但我们可以借助第三方库，比如DBUtils中的PooledDB或PersistentDB。

# 示例：使用DBUtils的PooledDB
from DBUtils.PooledDB import PooledDB
import os

# 同样从环境变量获取配置
DB_CONFIG_POOL = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'database': os.getenv('DB_NAME'),
    'port': int(os.getenv('DB_PORT', 3306)),
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 初始化连接池
# mincached: 启动时创建的最小连接数
# maxcached: 池中最多可存储的空闲连接数
# maxconnections: 最大允许的连接数，包括正在使用的和空闲的
# blocking: 当连接池满时，是否阻塞等待，True表示阻塞
POOL = PooledDB(
    creator=pymysql, # 指定连接模块
    mincached=5,
    maxcached=10,
    maxconnections=20,
    blocking=True,
    **DB_CONFIG_POOL
)

def get_db_connection():
    """从连接池获取一个连接"""
    return POOL.connection()

# 示例使用
if __name__ == '__main__':
    try:
        conn_from_pool = get_db_connection()
        cursor_from_pool = conn_from_pool.cursor()

        cursor_from_pool.execute("SELECT VERSION()")
        version = cursor_from_pool.fetchone()
        print(f"从连接池获取的连接成功查询到MySQL版本: {version['VERSION()']}")

        cursor_from_pool.close() # 关闭游标
        conn_from_pool.close() # 将连接归还到池中，而不是真正关闭
        print("连接已归还到连接池。")

    except Exception as e:
        print(f"使用连接池时发生错误: {e}")

使用连接池后，每次conn.close()实际上只是将连接标记为空闲并归还到池中，而不是真正断开TCP连接。这大大减少了连接建立和关闭的开销，显著提升了高并发场景下的应用响应速度和吞吐量。在我参与的Web服务项目中，连接池几乎是标配，没有它，性能瓶颈很快就会显现。

PyMySQL在处理并发请求和大数据量时有哪些性能优化策略？

当你的Python应用需要处理大量并发请求，或者需要从MySQL数据库中查询海量数据时，仅仅是建立连接和执行SQL就不够了。我们需要更精细的策略来确保性能和稳定性。

处理高并发请求：

在高并发场景下，核心问题是资源竞争和I/O阻塞。

1. 连接池的合理配置： 如前所述，连接池是解决并发请求的关键。但连接池的大小需要仔细权衡。
   • 太小： 请求会因为等待可用连接而阻塞，导致响应时间增加。
   • 太大： 会占用过多的数据库服务器资源（每个连接都会消耗内存和CPU），可能导致数据库自身过载。
   • 最佳实践： 通常根据数据库服务器的配置、应用程序的并发量以及数据库连接的平均使用时间来调整。一个常见的经验法则是：connections = ((core_count * 2) + effective_spindle_count)，但实际情况要根据压测结果来定。
2. 异步编程（asyncio）： 对于I/O密集型应用（如Web服务，大部分时间在等待数据库响应），Python的asyncio框架结合aiomysql（它是基于PyMySQL的异步驱动）可以极大地提升并发处理能力。aiomysql允许你在等待数据库响应时，CPU可以去处理其他任务，而不是空闲等待。这对于构建高并发的API服务非常有效。虽然这超出了PyMySQL本身的功能，但它是一个值得考虑的生态选择。
   • 个人思考： 如果你的项目已经拥抱了异步，那么aiomysql几乎是必然的选择。它能让你的服务器在相同硬件下处理更多的并发连接，而不会出现阻塞。
3. 事务管理： 确保事务尽可能短，减少锁的持有时间。长事务会增加数据库的锁竞争，降低并发性能。

处理大数据量查询：

当查询结果集非常庞大时，将所有数据一次性加载到内存中可能会导致内存溢出，或者仅仅是网络传输时间过长。

1. 服务器端游标（SSCursor）： PyMySQL提供了SSCursor（或SSDictCursor）。与默认的Cursor（客户端游标）不同，SSCursor不会一次性将所有查询结果加载到客户端内存。它会保持一个服务器端的游标，每次只从数据库服务器获取少量数据（例如一行），直到所有数据都被读取完毕。这对于处理数百万甚至上亿条记录的查询至关重要。

   import pymysql
   # ... (DB_CONFIG 定义)
   
   conn = pymysql.connect(**DB_CONFIG)
   # 使用SSDictCursor
   cursor = conn.cursor(pymysql.cursors.SSDictCursor)
   try:
       cursor.execute("SELECT * FROM large_table WHERE some_

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~