18.1 使用Python连接MySQL：pymysql模块

      将Python与MySQL结合，是开发数据驱动应用的关键技能。PyMySQL 是一个纯Python实现的MySQL客户端库，它让你能在Python代码中直接执行SQL语句，操作数据库。这一节将带你从零开始，掌握使用PyMySQL连接并操作MySQL数据库的完整流程。

1. 安装PyMySQL

在使用之前，需要先安装PyMySQL库。最简便的方式是使用Python的包管理工具pip。

打开你的终端（命令提示符或Terminal），执行以下命令：

pip install pymysql

如果你同时安装了Python2和Python3，可能需要使用pip3来确保为Python3安装：

pip3 install pymysql

安装成功后，你就可以在Python脚本中导入它了。

2. 建立数据库连接

要与MySQL服务器通信，首先需要建立一个连接。这类似于你使用MySQL命令行客户端时进行的登录操作。

2.1 核心连接参数

pymysql.connect() 函数负责创建连接，你需要提供数据库的连接信息：

• host: 数据库服务器地址，本地通常为 'localhost' 或 '127.0.0.1'。

• port: MySQL服务端口，默认为 3306。

• user: 数据库用户名，例如 'root'。

• password: 对应用户的密码。

• database: 要连接的数据库名称。

• charset: 字符集，通常设为 'utf8mb4' 以支持完整的中文和表情符号。

2.2 连接示例

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='your_username',      # 替换为你的用户名
    password='your_password',  # 替换为你的密码
    database='your_database',  # 替换为你的数据库名
    charset='utf8mb4'
)

print("连接成功！")

为了确保资源被正确释放，一种更健壮的写法是使用 try...finally 或 with 语句来管理连接。但请注意，pymysql的连接对象本身并不直接支持with上下文管理器，因此通常我们手动管理或在try...finally中关闭连接。

3. 创建游标并执行SQL

连接建立后，你需要一个“游标（Cursor）”来实际执行SQL语句并获取结果。游标可以理解为在数据库结果集上移动的指针。

3.1 执行查询（SELECT）

import pymysql

# 1. 建立连接
connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database',
    charset='utf8mb4'
)

try:
    # 2. 创建游标对象
    # 使用 pymysql.cursors.DictCursor 可以让结果以字典形式返回，更方便处理
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        # 3. 编写SQL语句
        sql = "SELECT id, name, email FROM users WHERE age > %s"
        
        # 4. 执行SQL，参数使用元组传递
        cursor.execute(sql, (20,))
        
        # 5. 获取所有结果
        results = cursor.fetchall()
        print("查询结果:")
        for row in results:
            print(f"ID: {row['id']}, Name: {row['name']}, Email: {row['email']}")

finally:
    # 6. 关闭连接
    connection.close()
    print("连接已关闭。")

关键点:

• cursor.execute(sql, parameters) 中的参数使用 %s 作为占位符，这是为了防止SQL注入攻击的安全做法。

• cursor.fetchall() 获取所有行，fetchone() 获取单行，fetchmany(size) 获取指定行数。

• 使用 pymysql.cursors.DictCursor 可以让游标返回字典，通过列名访问数据，代码更清晰。

4. 修改数据（INSERT, UPDATE, DELETE）

执行插入、更新或删除操作时，流程与查询类似，但有一个至关重要的区别：你需要提交事务（commit），更改才会最终保存到数据库。

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database',
    charset='utf8mb4'
)

try:
    with connection.cursor() as cursor:
        # 插入数据
        sql_insert = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
        cursor.execute(sql_insert, ('王小明', 'wangxm@example.com', 25))
        
        # 更新数据
        sql_update = "UPDATE users SET age = %s WHERE name = %s"
        cursor.execute(sql_update, (26, '王小明'))
        
        # 删除数据
        sql_delete = "DELETE FROM users WHERE email = %s"
        cursor.execute(sql_delete, ('olduser@example.com',))
        
    # ！！！重要：提交事务，使所有更改生效 ！！！
    connection.commit()
    print("数据更改已提交。")

except Exception as e:
    # 如果发生错误，回滚事务，撤销所有更改
    connection.rollback()
    print(f"操作失败，已回滚。错误信息: {e}")

finally:
    connection.close()

关键点:

• connection.commit() 必须显式调用，更改才会写入数据库。PyMySQL默认是开启事务的，即autocommit=False，这遵循了Python DB-API规范。

• 使用 try...except 并在异常时调用 connection.rollback() 是一个好习惯，可以保证数据一致性。

• cursor.rowcount 属性可以获取到上一条操作影响的行数，常用于验证操作是否成功。

5. 使用参数化查询防止SQL注入

永远不要通过字符串格式化（如 f"SELECT * FROM users WHERE name = '{name}'"）来拼接SQL语句。这会让你面临严重的SQL注入攻击风险。

PyMySQL 的参数化查询功能可以完全避免这个问题：

# 安全的方式
sql = "SELECT * FROM users WHERE email = %s AND age > %s"
cursor.execute(sql, (user_email, min_age))

PyMySQL 会自动处理和转义参数中的特殊字符，确保其仅被作为数据处理，而不会改变SQL语句的结构。

6. 完整示例：一个简单的数据访问函数

下面是一个封装了查询和修改操作的示例，展示了完整的流程。

import pymysql
from pymysql.cursors import DictCursor

db_config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor  # 直接在这里配置游标类
}

def fetch_users_by_age(min_age):
    """根据最小年龄查询用户"""
    connection = None
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "SELECT id, name, age, email FROM users WHERE age >= %s"
            cursor.execute(sql, (min_age,))
            users = cursor.fetchall()
            return users
    except pymysql.Error as e:
        print(f"查询错误: {e}")
        return None
    finally:
        if connection:
            connection.close()

def add_new_user(name, email, age):
    """添加新用户"""
    connection = None
    try:
        connection = pymysql.connect(**db_config)
        with connection.cursor() as cursor:
            sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
            cursor.execute(sql, (name, email, age))
        connection.commit()
        print(f"用户 {name} 添加成功！")
        return True
    except pymysql.IntegrityError as e:
        print(f"添加失败，可能邮箱已存在: {e}")
        if connection:
            connection.rollback()
        return False
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
        if connection:
            connection.rollback()
        return False
    finally:
        if connection:
            connection.close()

# 使用示例
if __name__ == '__main__':
    # 添加新用户
    add_new_user('赵灵儿', 'zhaoll@example.com', 18)

    # 查询年龄大于等于18的用户
    users = fetch_users_by_age(18)
    if users:
        for user in users:
            print(user)

核心要点与最佳实践

• 安装: pip install pymysql。

• 连接: 使用 pymysql.connect() 并提供主机、用户、密码、数据库等信息。

• 游标: connection.cursor() 创建游标以执行SQL。DictCursor 让结果更易用。

• 参数化查询: 始终使用 %s 占位符和参数元组来执行SQL，这是防御SQL注入的唯一正确方法。

• 事务管理:

• 查询操作无需提交。

• 修改操作（INSERT/UPDATE/DELETE）后，必须调用 connection.commit()。

• 使用 try...except...finally 并在异常时调用 connection.rollback() 来保证数据一致性。

• 资源释放: 操作完成后，务必调用 connection.close() 关闭连接，释放数据库资源。