Python连接MySQL教程，PyMySQL使用详解

想要轻松连接Python与MySQL数据库？本文为你提供一份详尽的PyMySQL使用教程，助你快速上手！PyMySQL作为Python与MySQL交互的首选库，以其纯Python实现、安装简便而备受青睐。本文将深入讲解PyMySQL连接MySQL数据库的核心步骤，包括**导入库、建立连接、创建游标、执行SQL查询、事务处理**以及**关闭连接**。同时，着重介绍了如何通过参数化查询有效**防止SQL注入**，并分享了利用DBUtils连接池**优化性能**、提升资源复用效率的实用技巧。无论你是新手还是有一定经验的开发者，都能从中受益，掌握PyMySQL连接MySQL的最佳实践。

PyMySQL连接MySQL数据库的核心步骤包括导入库、建立连接、创建游标、执行SQL、事务处理及关闭连接。1. 导入pymysql模块；2. 使用pymysql.connect()建立连接，传入数据库配置参数；3. 通过with conn.cursor()创建并自动管理游标；4. 使用cursor.execute()执行SQL语句，支持参数化查询防止SQL注入；5. 对于写操作需调用conn.commit()提交事务，出错时调用conn.rollback()回滚；6. 最后在finally块中确保conn.close()关闭连接。此外，高并发场景建议使用DBUtils连接池优化性能，提升资源复用效率。

PyMySQL是Python与MySQL数据库交互的首选库之一，它提供了一套直观的API，让开发者能够轻松地建立连接、执行SQL查询并管理数据。它的纯Python实现意味着安装和部署通常非常简单，不需要额外的编译步骤或系统依赖，这对于许多项目来说是一个巨大的便利。

解决方案

使用PyMySQL连接MySQL数据库的核心步骤通常包括导入库、建立连接、创建游标、执行SQL、提交或回滚事务，以及最终关闭连接。

首先，你需要确保PyMySQL已经安装： pip install PyMySQL

接下来，一个基本的连接和查询流程会是这样：

import pymysql

# 数据库连接参数
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4', # 推荐使用utf8mb4以支持更广泛的字符集
    'cursorclass': pymysql.cursors.DictCursor # 推荐使用DictCursor，结果以字典形式返回
}

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

    # 创建游标对象，用于执行SQL查询
    # 使用with语句可以确保游标在代码块结束时自动关闭
    with conn.cursor() as cursor:
        # 示例：创建表（如果不存在）
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        """
        cursor.execute(create_table_sql)
        print("表 'users' 检查或创建完成。")

        # 示例：插入数据
        # 注意：使用参数化查询来防止SQL注入
        insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
        users_to_insert = [
            ("张三", "zhangsan@example.com"),
            ("李四", "lisi@example.com")
        ]
        for user_data in users_to_insert:
            try:
                cursor.execute(insert_sql, user_data)
                print(f"插入数据: {user_data[0]} 成功。")
            except pymysql.err.IntegrityError as e:
                # 捕获唯一键冲突等错误
                print(f"插入数据: {user_data[0]} 失败，可能已存在或数据冲突: {e}")
            except Exception as e:
                print(f"插入数据: {user_data[0]} 发生未知错误: {e}")

        # 提交事务，使更改生效
        conn.commit()
        print("数据插入事务已提交。")

        # 示例：查询数据
        select_sql = "SELECT id, name, email FROM users WHERE name = %s"
        cursor.execute(select_sql, ("张三",))
        result = cursor.fetchone() # 获取一条结果
        if result:
            print(f"查询到用户张三: ID={result['id']}, Name={result['name']}, Email={result['email']}")

        # 示例：查询所有数据
        cursor.execute("SELECT id, name, email FROM users")
        all_users = cursor.fetchall() # 获取所有结果
        print("\n所有用户：")
        for user in all_users:
            print(f"ID={user['id']}, Name={user['name']}, Email={user['email']}")

except pymysql.err.OperationalError as e:
    # 捕获连接或操作数据库时的错误
    print(f"数据库操作错误: {e}")
    if conn:
        conn.rollback() # 发生错误时回滚事务
        print("事务已回滚。")
except Exception as e:
    # 捕获其他未知错误
    print(f"发生未知错误: {e}")
    if conn:
        conn.rollback()
        print("事务已回滚。")
finally:
    # 确保连接最终被关闭
    if conn:
        conn.close()
        print("数据库连接已关闭。")

这段代码展示了一个相对完整的流程，包括了连接、创建表、插入数据（并处理了可能的冲突）、查询以及错误处理和资源释放。使用with conn.cursor() as cursor:是管理游标的好习惯，它能确保游标在不再需要时被正确关闭。

如何优化PyMySQL的性能与资源管理？

在实际应用中，尤其是在高并发或长时间运行的服务里，如何高效地管理数据库连接是至关重要的。直接每次请求都建立和关闭连接的开销是巨大的，这不仅影响性能，还可能耗尽数据库服务器的连接资源。我曾经在开发一个高并发API服务时，就遇到过因为连接管理不当导致的数据库连接池耗尽问题，服务直接挂掉，那真是个头疼的下午。

一个常见的解决方案是使用连接池（Connection Pool）。连接池预先创建并维护一定数量的数据库连接，当应用程序需要连接时，它从池中获取一个空闲连接；使用完毕后，连接不是关闭，而是归还到池中，供其他请求复用。这样就大大减少了连接的建立和关闭开销。

PyMySQL本身不内置连接池功能，但你可以使用第三方库，比如DBUtils（特别是其中的PooledDB或PersistentDB模块），或者自己实现一个简单的连接池。

以下是一个使用DBUtils.PooledDB的简要示例：

from DBUtils.PooledDB import PooledDB
import pymysql

# 数据库配置，与之前类似
db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# 初始化连接池，通常在应用启动时执行一次
# mincached: 启动时创建的空闲连接数
# maxcached: 连接池中允许的最大空闲连接数
# maxconnections: 连接池允许的最大连接数（包括使用中和空闲的）
# blocking: 如果连接池已满，是否阻塞等待
pool = PooledDB(
    creator=pymysql, # 指定底层数据库模块
    mincached=5,
    maxcached=10,
    maxconnections=20,
    blocking=True,
    ping=1, # 检查连接是否可用，如果断开会自动重新连接
    **db_config
)

def execute_query_with_pool(sql, params=None):
    conn = None
    cursor = None
    result = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        with conn.cursor() as cursor:
            cursor.execute(sql, params)
            if sql.strip().upper().startswith("SELECT"):
                result = cursor.fetchall()
            else:
                conn.commit()
                result = cursor.rowcount # 返回影响的行数
    except Exception as e:
        print(f"数据库操作错误: {e}")
        if conn:
            conn.rollback() # 发生错误时回滚
        raise # 重新抛出异常，让调用者处理
    finally:
        if conn:
            # 连接被归还到连接池，而不是真正关闭
            conn.close()
    return result

# 示例使用
try:
    # 插入数据
    execute_query_with_pool("INSERT INTO users (name, email) VALUES (%s, %s)", ("王五", "wangwu@example.com"))
    print("王五插入成功。")

    # 查询数据
    users = execute_query_with_pool("SELECT * FROM users")
    print("\n通过连接池查询到的所有用户:")
    for user in users:
        print(user)
except Exception as e:
    print(f"操作失败: {e}")

使用连接池后，每次调用pool.connection()都会从池中获取一个连接，conn.close()实际上是把连接归还给连接池。这显著提高了性能和资源利用率。同时，ping=1参数在PooledDB中也很关键，它能在连接被使用前检查其有效性，避免使用到已断开的连接，这在网络环境复杂或数据库空闲超时设置较短时特别有用。

PyMySQL中处理SQL注入和事务管理的最佳实践是什么？

SQL注入是一个古老但依然普遍存在的安全漏洞，而事务管理则是确保数据一致性的基石。在PyMySQL中，遵循一些最佳实践可以有效规避这些风险。

防止SQL注入：参数化查询

PyMySQL（以及几乎所有现代数据库驱动）都提供了参数化查询的机制，这是防御SQL注入最有效的方法。它的原理是，SQL语句的结构和数据是分开传递的。数据库驱动会负责将数据安全地插入到SQL模板中，无论是字符串还是数字，都会被正确地转义，从而避免恶意代码被当作SQL指令执行。

永远不要通过字符串拼接的方式来构建SQL查询，特别是当查询中包含用户输入时。

错误示例（易受SQL注入攻击）：

# 千万不要这样做！
user_input_name = "张三'; DROP TABLE users;--" # 恶意输入
sql = f"SELECT * FROM users WHERE name = '{user_input_name}'"
cursor.execute(sql)

如果执行上述代码，数据库表可能就会被删掉。

正确做法（使用参数化查询）：

# 推荐做法
user_input_name = "张三" # 或者任何用户输入
sql = "SELECT * FROM users WHERE name = %s" # 使用%s作为占位符
cursor.execute(sql, (user_input_name,)) # 将参数作为元组传递给execute方法

对于INSERT、UPDATE、DELETE等操作也是同理。PyMySQL会根据%s占位符自动处理参数的转义，确保它们被视为数据而不是可执行的SQL代码。

事务管理：确保数据一致性

事务是一系列数据库操作的逻辑单元，这些操作要么全部成功（提交），要么全部失败（回滚）。这对于保持数据的一致性和完整性至关重要，特别是在涉及多个相关操作时。

PyMySQL默认是自动提交（autocommit）模式关闭的，这意味着你需要显式地调用conn.commit()来保存更改，或者调用conn.rollback()来撤销更改。我个人更倾向于这种显式控制，它让事务的边界更加清晰。

基本事务流程：

conn = None
try:
    conn = pymysql.connect(**db_config)
    # 默认autocommit=False，所以需要手动提交
    with conn.cursor() as cursor:
        # 操作1：更新用户余额
        cursor.execute("UPDATE accounts SET balance = balance - %s WHERE user_id = %s", (100, 1))
        # 模拟一个可能失败的操作
        # 如果这里发生错误，下面的commit就不会执行
        # 操作2：记录交易日志
        cursor.execute("INSERT INTO transactions (user_id, amount, type) VALUES (%s, %s, %s)", (1, -100, 'expense'))

    conn.commit() # 所有操作都成功，提交事务
    print("事务提交成功，数据已更新。")

except Exception as e:
    print(f"事务失败: {e}")
    if conn:
        conn.rollback() # 任何一个操作失败，回滚所有更改
        print("事务已回滚，数据未更改。")
finally:
    if conn:
        conn.close()

如果你希望PyMySQL在每次执行SQL后都自动提交，可以设置autocommit=True在连接参数中： conn = pymysql.connect(autocommit=True, **db_config) 但对于复杂的业务逻辑，我通常不建议开启autocommit，因为这会让你失去对事务的精细控制，一旦某个操作出错，前面的操作也可能已经提交，导致数据不一致。显式提交和回滚能让你更好地把握数据流向。

PyMySQL连接遇到问题时，如何进行故障排除？

在使用PyMySQL连接MySQL数据库时，遇到各种连接或操作错误是常有的事。这些问题可能源于配置、网络、权限或数据库状态。我记得有一次，花了半天时间才发现是MySQL服务器的端口被防火墙挡住了，这种低级错误也常常让人抓狂。

以下是一些常见的故障排除步骤和思路：

1. 检查连接参数：

   • 主机（host）：确保你连接的是正确的IP地址或域名。如果是本地开发，通常是localhost或127.0.0.1。
   • 端口（port）：MySQL默认端口是3306。如果MySQL服务器使用了非标准端口，请确保在配置中指定。
   • 用户名（user）和密码（password）：这是最常见的错误来源。仔细检查，区分大小写。
   • 数据库名（database）：确保你要连接的数据库存在且名称正确。

2. 网络连通性：

   • 防火墙：检查客户端机器和MySQL服务器上的防火墙规则。确保MySQL端口（默认3306）是开放的，并且允许你的客户端IP连接。
   • 网络路径：尝试从客户端机器pingMySQL服务器的IP地址，看是否能通。
   • Telnet/nc测试：在客户端机器上使用telnet 3306或nc -vz 3306来测试端口是否开放且可达。如果连接被拒绝或超时，很可能是网络或防火墙问题。

3. MySQL服务器状态：

   • 服务是否运行：登录到MySQL服务器，检查MySQL服务是否正在运行。例如，在Linux上可以使用sudo systemctl status mysql或sudo service mysql status。
   • 最大连接数：MySQL服务器有最大连接数限制。如果连接池或应用程序创建了太多连接，可能会超出此限制。可以通过SHOW VARIABLES LIKE 'max_connections';查看，并通过SHOW STATUS LIKE 'Threads_connected';查看当前连接数。
   • 用户权限：确保连接使用的MySQL用户拥有从客户端IP连接到指定数据库的权限。例如，用户可能只被授权从localhost连接，而你却尝试从远程IP连接。可以使用SELECT user, host FROM mysql.user;查看用户权限配置。

4. 编码问题：

   • 如果数据库中的字符显示乱码，或者插入数据时报错Incorrect string value，很可能是编码问题。在PyMySQL连接时，确保charset参数与数据库的编码设置一致，通常推荐使用utf8mb4。
   • 检查数据库、表和列的字符集设置。

5. 错误日志：

   • PyMySQL错误信息：Python代码中的try...except块会捕获pymysql.err下的各种异常，如OperationalError（连接或操作错误）、ProgrammingError（SQL语法错误）、IntegrityError（数据完整性错误，如唯一键冲突）。仔细阅读这些错误信息，它们通常会提供关键线索。
   • MySQL服务器错误日志：MySQL服务器有自己的错误日志文件（通常在/var/log/mysql/error.log或my.cnf中配置的路径）。查看这个日志文件，可能会发现服务器端拒绝连接或查询失败的具体原因。

6. 代码审查：

   • 资源未释放：确保连接和游标在使用完毕后都被正确关闭。虽然Python的垃圾回收机制会处理，但在高并发场景下，不及时关闭会耗尽资源。使用with语句是最佳实践。
   • SQL语法错误：ProgrammingError通常指向SQL语句本身的问题。检查SQL的拼写、关键字、表名和列名是否正确。

通过系统地排查这些方面，通常能定位并解决PyMySQL连接和操作中的大部分问题。

理论要掌握，实操不能落！以上关于《Python连接MySQL教程，PyMySQL使用详解》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！