【Python】Python接入MySQL数据库

宅男很神经

于 2025-05-26 22:44:12 发布

阅读量893

点赞数 15

文章标签：数据库 python mysql

本文链接：https://blog.csdn.net/yangjia96/article/details/148240918

版权

Python 接入 MySQL 数据库

一、Python 与 MySQL 数据库交互概览 (Overview of Python-MySQL Interaction)

1. 为何选择 Python 与 MySQL 结合 (Why Combine Python with MySQL?)

Python 以其简洁的语法、丰富的库生态系统以及在Web开发、数据分析、人工智能、自动化脚本等领域的广泛应用而著称。MySQL 则是世界上最流行的开源关系型数据库之一，以其稳定性、高性能和易用性而受到青睐。将两者结合，可以构建出功能强大、数据驱动的应用程序。

Web 开发: 诸如 Django、Flask、FastAPI 等流行的 Python Web 框架都能够与 MySQL 无缝集成，用于存储用户信息、应用数据、会话管理等。
数据分析与科学: Python 的 Pandas、NumPy、SciPy 等库与 MySQL 结合，可以方便地从数据库中提取数据进行分析、处理和可视化。
自动化与脚本: Python 脚本可以轻松连接 MySQL，执行数据迁移、备份、报表生成、ETL（Extract, Transform, Load）等任务。
企业应用: 许多企业级应用后台使用 Python 处理业务逻辑，并依赖 MySQL 作为其核心数据存储。
易用性与社区支持: Python 提供了多种成熟的 MySQL 连接器（驱动程序），简化了开发过程。同时，Python 和 MySQL 都有庞大而活跃的开发者社区，可以方便地找到解决方案和支持。

2. Python 访问 MySQL 的基本架构 (Basic Architecture of Python Accessing MySQL)

Python 应用程序通过一个称为数据库连接器 (Database Connector) 或 驱动程序 (Driver) 的特定库来与 MySQL 服务器进行通信。这个连接器扮演着翻译官的角色，将 Python 代码中的数据库操作指令转换为 MySQL 服务器能够理解的协议和SQL语句，并将服务器返回的结果转换回 Python 的数据结构。

其基本交互流程如下：

加载连接器: Python 程序导入所需的 MySQL 连接器库。
建立连接 (Connection): Python 程序使用连接器提供的函数，传入必要的参数（如主机名、端口、用户名、密码、数据库名等）来与 MySQL 服务器建立一个网络连接。
创建游标 (Cursor): 一旦连接建立，程序会创建一个游标对象。游标可以看作是数据库操作的一个句柄，它允许你执行SQL语句并处理结果集。
执行 SQL 语句: 通过游标对象，程序可以执行各种 SQL 语句（SELECT, INSERT, UPDATE, DELETE, DDL语句等）。
处理结果:
- 对于 SELECT 查询，游标可以用来获取查询结果（一行、多行或所有行）。
- 对于 DML 语句（INSERT, UPDATE, DELETE），可以获取受影响的行数。
事务处理: 对于需要原子性的操作序列，程序可以通过连接对象来管理事务（提交 commit 或回滚 rollback）。
关闭游标和连接: 操作完成后，为了释放资源，程序需要关闭游标和数据库连接。

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
(图片仅为示意，表示Python应用通过Connector与MySQL Server通信)

二、选择合适的 Python MySQL 连接器 (Choosing the Right Python MySQL Connector)

Python 社区提供了多个用于连接 MySQL 的库。选择哪个连接器取决于项目的具体需求，如性能要求、部署环境、Python 版本兼容性、特定特性需求以及个人偏好。

2.1 主流 Python MySQL 连接器

以下是几个最常用和推荐的连接器：

mysql-connector-python (官方连接器)
- 开发者: Oracle Corporation (MySQL 的母公司)
- 类型: 纯 Python 实现 (Pure Python)。这意味着它不依赖外部C库（除了可选的C扩展以提高性能），通常更容易安装在各种平台上。
- 特性:
  - 完全实现了 Python DB-API 2.0 规范 (PEP 249)。
  - 支持 MySQL 的所有最新特性，包括X Plugin (用于文档存储和异步操作)、新的认证插件 (如 caching_sha2_password)。
  - 提供了连接池功能。
  - 支持压缩、SSL连接。
  - 可以不安装 MySQL客户端库。
  - 可选的 C 扩展 (mysql-connector-python-cext) 可以提高性能，但会引入编译依赖。
- 许可: 通常是 GPLv2 或商业许可（取决于你如何获取和使用）。
- 安装: pip install mysql-connector-python
PyMySQL
- 开发者: PyMySQL 社区
- 类型: 纯 Python 实现。
- 特性:
  - 几乎完全实现了 Python DB-API 2.0 规范。
  - 轻量级，易于安装和部署，尤其在没有C编译器或受限环境中。
  - 被许多第三方库（如 SQLAlchemy, Django - 作为备选驱动）良好支持。
  - 支持 Python 3.x (Python 2.x 支持已在较旧版本中)。
  - 支持 caching_sha2_password 认证 (需要 cryptography 库)。
- 许可: MIT License (非常宽松)。
- 安装: pip install PyMySQL
mysqlclient (MySQLdb 的现代分支)
- 开发者: mysqlclient 维护者 (基于 MySQLdb)
- 类型: C 扩展模块。它包装了 MySQL C 客户端库。
- 特性:
  - 实现了 Python DB-API 2.0 规范。
  - 性能通常被认为是最高的，因为它直接使用C库。
  - 是 Django 框架默认推荐的 MySQL 连接器。
  - 支持 Python 3.x (Python 2.x 支持通过旧版 MySQLdb)。
  - 支持最新的 MySQL 特性（取决于链接的C客户端库版本）。
- 缺点:
  - 安装可能更复杂: 需要系统上安装了 MySQL 开发库 (C headers and libraries) 和 C 编译器。在某些操作系统（尤其是Windows）上直接通过 pip 安装可能遇到编译问题，可能需要预编译的二进制包 (wheels)。
- 许可: GPLv2。
- 安装:
  - Linux: sudo apt-get install python3-dev default-libmysqlclient-dev build-essential (Debian/Ubuntu) 或 sudo yum install python3-devel mysql-devel gcc (CentOS/RHEL) 然后 pip install mysqlclient
  - macOS: brew install mysql-client (或 mysql) 然后 pip install mysqlclient
  - Windows: 通常建议从 Christoph Gohlke’s Unofficial Windows Binaries for Python Extension Packages 下载预编译的 .whl 文件安装，或者使用 pip install mysqlclient （如果环境中配置好了编译工具和库）。

2.2 连接器选择考量因素

特性/考量因素	`mysql-connector-python` (官方)	`PyMySQL`	`mysqlclient`	中文解释
实现类型	纯Python (可选C扩展)	纯Python	C扩展 (包装C库)	纯Python易安装部署，C扩展通常性能更好但安装可能复杂。
性能	中等到高 (使用C扩展时)	中等	通常最高	C扩展因接近底层而快；纯Python实现会有Python解释器开销。
安装便捷性	高 (纯Python部分)	非常高	中等到低 (依赖C库和编译器)	纯Python库通常`pip install`即可；C扩展可能需要系统依赖。
Python 版本	Python 3.x (旧版支持2.x)	Python 3.x (旧版支持2.x)	Python 3.x (旧版`MySQLdb`支持2.x)	主流库都已转向Python 3。
DB-API 2.0 兼容性	完全兼容	几乎完全兼容	完全兼容	DB-API 2.0是Python数据库编程的标准接口。
MySQL 最新特性支持	优秀 (官方维护)	良好 (社区驱动，可能稍有延迟)	良好 (依赖C客户端库版本)	官方连接器通常最快支持MySQL新特性。
依赖	无 (纯Python核心) / C编译器(C扩展)	可选`cryptography` (用于新认证)	MySQL C 开发库, C编译器	依赖越少，部署越简单。
连接池	内置	无内置 (需第三方库)	无内置 (需第三方库)	对于Web应用或高并发服务，连接池很重要。
许可	GPLv2 / 商业	MIT	GPLv2	MIT许可最宽松，GPL对商业应用有特定要求。
社区与文档	良好 (官方)	良好 (活跃社区)	良好 (广泛使用)	都有不错的社区支持和文档资源。
Django/SQLAlchemy集成	支持	广泛支持	Django首选, SQLAlchemy支持	主流ORM和框架通常支持多种连接器。

企业级项目选择建议:

追求极致性能且能处理C扩展安装: mysqlclient 往往是首选，尤其在Django项目中。
要求纯Python实现、易于部署、许可宽松: PyMySQL 是一个非常好的选择，并且由于其轻量和纯粹性，在Serverless、容器化等环境中也受欢迎。
需要MySQL官方支持、最新特性、内置连接池且不排斥GPL: mysql-connector-python 是一个可靠的选择。如果性能是瓶颈，可以尝试其C扩展。
初学者或快速原型: PyMySQL 或 mysql-connector-python (纯Python模式) 通常更容易上手。

在后续的示例中，我们将主要使用 mysql-connector-python 和 PyMySQL 进行演示，因为它们代表了纯Python实现的便利性，并且功能全面。如果需要，也会提及 mysqlclient 的特定之处。

2.3 安装连接器

使用 pip (Python的包安装器) 来安装这些库。建议在虚拟环境 (virtual environment) 中安装项目依赖。

安装 mysql-connector-python:

pip install mysql-connector-python

或者，如果需要其C扩展以获得更好性能 (可能需要编译环境)：

# pip install mysql-connector-python-cext # 通常这个包名不直接用
# 正确的方式是，mysql-connector-python 会尝试自动编译C扩展如果可用
# 如果你想要一个只包含C扩展的版本，你可能需要寻找特定的wheel或从源码编译
# 一般情况下，直接安装 mysql-connector-python 即可，它会按需处理

注意: Oracle 官方有时会提供名为 mysql-connector-python-rf (release candidate for features) 或其他后缀的包，通常应使用稳定版 mysql-connector-python。

安装 PyMySQL:
```
pip install PyMySQL
```
如果需要支持 caching_sha2_password 认证方式（MySQL 8.0+ 默认），PyMySQL 可能需要 cryptography 库：
```
pip install PyMySQL[rsa]
# 或者
pip install PyMySQL cryptography
```
安装 mysqlclient:
确保你已经安装了必要的系统依赖 (如前文所述的C开发库和编译器)。
```
pip install mysqlclient
```
如果遇到编译错误，请根据错误信息查找特定于你操作系统的解决方案，或者寻找预编译的wheel包。

三、Python DB-API 2.0 核心规范 (Core Specifications of Python DB-API 2.0)

Python DB-API 2.0 (PEP 249) 定义了一个标准的数据库接口，大多数Python数据库连接器都遵循这个规范。理解它有助于你编写更具移植性的数据库访问代码（尽管不同连接器的某些扩展特性可能不同）。

3.1 规范的目标

一致性: 为不同的数据库提供一个统一的编程接口。
易用性: 提供简单直观的方法来连接数据库、执行SQL和获取结果。
功能性: 包含处理事务、错误、多种数据类型等基本数据库操作所需的功能。

3.2 关键组件和方法

全局变量:
- apilevel: 字符串，表示API级别（通常是 “2.0”）。
- threadsafety: 整数，表示线程安全级别 (0到3)。
- paramstyle: 字符串，表示SQL参数占位符的风格（如 qmark, numeric, named, format, pyformat）。
connect() 函数 (模块级别):
用于建立到数据库的连接。它返回一个连接对象 (Connection Object)。
- 参数通常包括：dsn (数据源名称字符串，可选)，user, password, host, database, port 等。不同连接器支持的参数名和具体细节可能略有差异，但核心参数类似。
连接对象 (Connection Object):
代表与数据库的一个会话。
- close(): 关闭数据库连接。
- commit(): 提交当前事务中所有挂起的操作。
- rollback(): 回滚当前事务中所有挂起的操作。
- cursor([cursor_type]): 返回一个新的游标对象 (Cursor Object)。可以指定游标类型（如 buffered, dictionary cursor，具体支持依赖连接器）。
- Error (异常类): 连接器定义的异常基类。
- Warning, InterfaceError, DatabaseError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, NotSupportedError (标准异常类，通常是 Connection.Error 的子类)。
游标对象 (Cursor Object):
用于执行SQL语句并管理结果集。
- description: 只读属性。在执行 SELECT 查询后，它是一个包含7个元素的序列的序列，每个内部序列描述结果集中的一列（name, type_code, display_size, internal_size, precision, scale, null_ok）。
- rowcount: 只读属性。对于 DML 语句（INSERT, UPDATE, DELETE），返回受影响的行数。对于 SELECT 语句，其行为未被严格规定，可能返回 -1 或实际行数（取决于连接器和游标类型，例如buffered cursor可能知道行数）。
- callproc(procname [, parameters]): (可选) 调用存储过程。
- close(): 关闭游标。
- execute(operation [, parameters]): 执行一个数据库操作（SQL语句）。parameters 是一个可选的序列或映射，用于替换 operation 字符串中的参数占位符，以防止SQL注入。
- executemany(operation, seq_of_parameters): 对参数序列中的每一项重复执行同一个数据库操作。通常用于批量插入或更新。
- fetchone(): 获取查询结果集中的下一行，返回一个序列（通常是元组），或者在没有更多行时返回 None。
- fetchmany([size=cursor.arraysize]): 获取查询结果集中的下一批行，返回一个序列的序列（列表的列表或元组的列表）。size 参数指定要获取的行数，默认为 cursor.arraysize。如果剩余行数少于 size，则返回所有剩余行。如果没有更多行，返回空序列。
- fetchall(): 获取查询结果集中的所有（剩余）行，返回一个序列的序列。
- nextset(): (可选) 移动到下一个结果集（如果执行的操作返回多个结果集，例如某些存储过程）。
- arraysize: 可读写属性。fetchmany() 默认获取的行数。默认为1。
- setinputsizes(sizes): (可选) 声明输入参数的大小。
- setoutputsize(size [, column]): (可选) 声明大列（如LOB）的缓冲区大小。

3.3 参数样式 (`paramstyle`)

DB-API 2.0 规范定义了多种参数化查询的占位符风格，连接器会声明它支持哪种。

qmark: 问号风格。例如：SELECT * FROM users WHERE id = ? AND name = ? 参数：(1, 'Alice')
numeric: 数字，位置风格。例如：SELECT * FROM users WHERE id = :1 AND name = :2 参数：(1, 'Alice')
named: 命名风格。例如：SELECT * FROM users WHERE id = :user_id AND name = :user_name 参数：{'user_id': 1, 'user_name': 'Alice'}
format: ANSI C printf 格式代码。例如：SELECT * FROM users WHERE id = %s AND name = %s 参数：(1, 'Alice')
pyformat: Python 扩展 printf 格式代码。例如：SELECT * FROM users WHERE id = %(user_id)s AND name = %(user_name)s 参数：{'user_id': 1, 'user_name': 'Alice'}

mysql-connector-python 和 PyMySQL 通常支持 format (%s) 和 pyformat (%(name)s) 风格。mysqlclient 也类似。 使用 %s 是最常见和推荐的方式，因为它简单且不易出错。

理解 DB-API 2.0 是掌握 Python 数据库编程的基础。即使你将来使用 ORM（如 SQLAlchemy），了解其底层是如何通过 DB-API 与数据库交互也是非常有益的。

四、基本连接与查询操作 (Basic Connection and Query Operations)

现在，我们将通过具体的代码示例来演示如何使用 Python 连接到 MySQL 数据库并执行基本的CRUD（Create, Read, Update, Delete）操作。我们将首先以 mysql-connector-python 为例，然后展示 PyMySQL 的等效实现。

4.1 使用 `mysql-connector-python`

4.1.1 建立数据库连接

首先，你需要安装 mysql-connector-python。

pip install mysql-connector-python

然后，在 Python 脚本中导入并使用它：

import mysql.connector # 导入 mysql.connector 模块
from mysql.connector import errorcode # 导入 errorcode 用于更具体的错误处理

def connect_to_mysql_server_only(db_host, db_user, db_password):
    """尝试只连接到MySQL服务器，不指定数据库。"""
    try:
        cnx = mysql.connector.connect( # 调用 connect 函数建立连接
            host=db_host, # MySQL 服务器主机名或IP地址
            user=db_user, # MySQL 用户名
            password=db_password # MySQL 用户密码
            # port=3306, # 可选，MySQL 服务器端口，默认为3306
            # connection_timeout=10 # 可选，连接超时时间（秒）
        )
        print(f"成功连接到 MySQL 服务器 {
     db_host} (版本: {
     cnx.get_server_info()})") # 打印服务器信息
        return cnx # 返回连接对象
    except mysql.connector.Error as err: # 捕获 mysql.connector 的错误
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: # 如果是访问被拒绝错误
            print(f"连接失败: 用户名或密码不正确 (错误码: {
     err.errno}, {
     err.msg})")
        elif err.errno == errorcode.ER_BAD_DB_ERROR: # 如果是数据库不存在错误 (这里不应发生，因为未指定数据库)
            print(f"连接失败: 数据库不存在 (错误码: {
     err.errno}, {
     err.msg})")
        else: # 其他 MySQL 连接错误
            print(f"连接 MySQL 服务器失败: {
     err} (错误码: {
     err.errno})")
        return None # 连接失败返回 None

def connect_to_mysql_database(db_host, db_user, db_password, db_name):
    """尝试连接到MySQL服务器上的特定数据库。"""
    try:
        cnx = mysql.connector.connect( # 调用 connect 函数建立连接
            host=db_host,
            user=db_user,
            password=db_password,
            database=db_name # 指定要连接的数据库名称
            # auth_plugin='mysql_native_password' # 如果遇到认证插件问题 (如caching_sha2_password)，可尝试指定
        )
        print(f"成功连接到数据库 '{
     db_name}' 在服务器 {
     db_host}")
        return cnx
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print(f"连接失败: 用户名或密码不正确 (错误码: {
     err.errno}, {
     err.msg})")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print(f"连接失败: 数据库 '{
     db_name}' 不存在 (错误码: {
     err.errno}, {
     err.msg})")
        else:
            print(f"连接数据库 '{
     db_name}' 失败: {
     err} (错误码: {
     err.errno})")
        return None

# --- 主程序逻辑 ---
if __name__ == "__main__":
    # 请替换为你的MySQL服务器的实际连接信息
    DB_HOST = "localhost"  # 或者你的MySQL服务器IP
    DB_USER = "your_mysql_user" # 替换为你的MySQL用户名
    DB_PASSWORD = "your_mysql_password" # 替换为你的MySQL密码
    DB_NAME = "your_database_name" # 替换为你要连接的数据库名

    # 示例1: 只连接服务器
    server_connection = connect_to_mysql_server_only(DB_HOST, DB_USER, DB_PASSWORD)
    if server_connection:
        print(f"服务器连接对象: {
     server_connection}")
        server_connection.close() # 操作完成后关闭连接
        print("服务器连接已关闭。")
    print("-" * 30)

    # 示例2: 连接到特定数据库
    db_connection = connect_to_mysql_database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
    if db_connection:
        print(f"数据库连接对象: {
     db_connection}")
        # 在这里可以进行后续的数据库操作 (创建游标、执行SQL等)
        db_connection.close() # 操作完成后关闭连接
        print(f"数据库 '{
     DB_NAME}' 连接已关闭。")

    # 准备一个示例表用于后续操作
    # 为了运行后续示例，请确保你的MySQL中存在名为 your_database_name 的数据库，
    # 并且该用户 your_mysql_user 有权限在该数据库中创建表和执行DML。
    # 你可以使用 phpMyAdmin, MySQL Workbench, 或 MySQL命令行客户端来创建数据库。
    # CREATE DATABASE IF NOT EXISTS your_database_name;

代码解释:

import mysql.connector 和 from mysql.connector import errorcode: 导入必要的模块。errorcode 包含MySQL服务器返回的错误代码常量，便于进行更精确的错误判断。
mysql.connector.connect(...): 这是建立连接的核心函数。
- host, user, password, database: 是最基本的连接参数。
- 其他可选参数如 port, connection_timeout, auth_plugin 等可以根据需要配置。例如，如果MySQL 8.0+ 使用 caching_sha2_password 认证，而你的连接器版本较旧或配置不当，可能需要显式指定 auth_plugin='mysql_native_password'（但这通常是针对旧客户端兼容旧服务器的情况，或反之）。
错误处理: 使用 try...except mysql.connector.Error as err: 来捕获连接过程中可能发生的各种MySQL错误。
- err.errno: 错误码，可以与 errorcode 中的常量比较。
- err.msg: 错误的文本描述。
cnx.get_server_info(): 获取MySQL服务器的版本信息。
cnx.close(): 非常重要。在完成所有数据库操作后，必须关闭连接以释放服务器和客户端的资源。未关闭的连接可能会导致资源泄露，最终耗尽服务器的最大连接数。

企业级连接配置考量:

配置文件/环境变量: 不应将数据库凭证（用户名、密码）硬编码在源代码中。应使用配置文件 (如 INI, YAML, JSON, .env 文件) 或环境变量来存储这些敏感信息，并在程序启动时读取。
```
# 示例：从环境变量读取 (更安全)
import os
# DB_USER = os.getenv("DB_USER_PROD")
# DB_PASSWORD = os.getenv("DB_PASS_PROD")
```

SSL/TLS 加密连接: 对于生产环境，特别是当Python应用和MySQL服务器不在同一台机器或安全网络中时，应使用SSL/TLS加密连接以保护数据传输安全。mysql-connector-python 支持SSL连接，需要在 connect() 函数中配置 ssl_ca, ssl_cert, ssl_key 等参数。

# config = {
     
#     'user': 'your_user',
#     'password': 'your_password',
#     'host': 'your_host',
#     'database': 'your_db',
#     'ssl_ca': '/path/to/ca.pem',       # CA证书路径
#     'ssl_cert': '/path/to/client-cert.pem', # 客户端证书路径 (如果需要双向SSL)
#     'ssl_key': '/path/to/client-key.pem',   # 客户端私钥路径 (如果需要双向SSL)
#     'ssl_verify_cert': True # (MySQL 8.0.27+/Connector 8.0.27+) 强制验证服务器证书，更安全
# }
# cnx = mysql.connector.connect(**config)

连接超时: 设置合理的 connection_timeout 可以防止程序在网络问题时无限期等待。
字符集: 确保Python连接器使用的字符集（通常默认为 utf8mb4 或可配置）与数据库/表的字符集匹配，以避免乱码问题。可以在 connect() 时指定 charset='utf8mb4'。
时区: 处理日期时间数据时，要注意Python应用、MySQL服务器、连接器之间的时区设置，避免混淆。

4.1.2 创建游标对象

连接建立后，需要创建一个游标对象来执行SQL。

# (接上文 db_connection 成功建立后)
# if db_connection and db_connection.is_connected(): # 确保连接是活动的
#     try:
#         cursor = db_connection.cursor() # 创建一个默认类型的游标
#         print("默认游标已创建。")
#
#         # 不同类型的游标 (mysql-connector-python 支持)
#         buffered_cursor = db_connection.cursor(buffered=True) # 缓冲游标，获取所有结果到客户端内存
#         print("缓冲游标已创建。当结果集不大时，或需要在获取所有数据后释放服务器资源时有用。")
#
#         dictionary_cursor = db_connection.cursor(dictionary=True) # 字典游标，每行结果作为字典返回 (列名: 值)
#         print("字典游标已创建。方便按列名访问数据。")
#
#         named_tuple_cursor = db_connection.cursor(named_tuple=True) # 命名元组游标，每行结果作为命名元组返回
#         print("命名元组游标已创建。提供类似字典的属性访问，但更轻量。")
#
#     except mysql.connector.Error as err:
#         print(f"创建游标失败: {err}")
#     finally:
#         if 'cursor' in locals() and cursor: cursor.close()
#         if 'buffered_cursor' in locals() and buffered_cursor: buffered_cursor.close()
#         if 'dictionary_cursor' in locals() and dictionary_cursor: dictionary_cursor.close()
#         if 'named_tuple_cursor' in locals() and named_tuple_cursor: named_tuple_cursor.close()
#         if db_connection and db_connection.is_connected():
#             db_connection.close()
#             print("数据库连接已关闭。")

代码解释:

db_connection.cursor(): 返回一个标准的游标对象。默认情况下，对于 SELECT 查询，它可能是非缓冲的，意味着数据逐行从服务器获取。
buffered=True: 创建一个缓冲游标。它会在执行查询后立即将整个结果集从服务器下载到客户端内存中。
- 优点: 获取数据后，可以立即释放服务器端的资源（与该查询相关的锁或临时表）。可以多次遍历结果集（如果需要）。
- 缺点: 如果结果集非常大，会消耗大量客户端内存。
dictionary=True: 创建一个字典游标。fetchone() 和 fetchall() 等方法返回的每一行都是一个字典，键是列名，值是对应的数据。
```
# row = dictionary_cursor.fetchone()
# if row:
#     print(row['column_name'])
```

named_tuple=True: 创建一个命名元组游标。每一行都是一个命名元tuple，可以通过列名作为属性访问，也可以通过索引访问。

# from collections import namedtuple # 命名元组游标内部会使用类似机制
# row = named_tuple_cursor.fetchone()
# if row:
#     print(row.column_name)
#     print(row[0])

游标也需要关闭: cursor.close()。与连接类似，游标也占用资源，使用完毕后应关闭。使用 with 语句管理游标是更好的做法（后续会讲）。

企业级游标选择:

默认游标: 适用于大多数情况，特别是当结果集可能很大，或者只需要逐行处理时。
缓冲游标 (buffered=True): 当结果集确定不大，并且需要在获取数据后快速与服务器断开（或执行其他操作），或者需要多次迭代结果时使用。例如，获取少量配置数据。
字典游标 (dictionary=True): 提高代码可读性，因为可以直接通过列名访问数据，无需记住列的索引顺序。但相比元组，字典会有轻微的额外开销。
命名元组游标 (named_tuple=True): 兼具字典游标的可读性和元组的轻量级特性。

4.1.3 执行 `SELECT` 查询并获取结果

def create_sample_employees_table(connection):
    """在数据库中创建一个示例 employees 表 (如果不存在)"""
    try:
        cursor = connection.cursor() # 创建游标
        table_description = """
        CREATE TABLE IF NOT EXISTS employees (
            emp_no INT AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(50) NOT NULL,
            last_name VARCHAR(50) NOT NULL,
            hire_date DATE,
            salary DECIMAL(10, 2)
        ) ENGINE=InnoDB CHARACTER SET=utf8mb4
        """
        cursor.execute(table_description) # 执行建表语句
        print("表 'employees' 已检查/创建。")

        # 检查表是否为空，如果为空则插入一些示例数据
        cursor.execute("SELECT COUNT(*) FROM employees") # 查询员工数量
        if cursor.fetchone()[0] == 0: # 如果员工数量为0
            print("表 'employees' 为空，插入示例数据...")
            insert_query = """
            INSERT INTO employees (first_name, last_name, hire_date, salary) VALUES
            ('John', 'Doe', '2020-01-15', 60000.00),
            ('Jane', 'Smith', '2019-03-01', 75000.00),
            ('Alice', 'Johnson', '2021-07-22', 80000.00),
            ('Bob', 'Williams', '2018-06-10', 50000.00),
            ('Charlie', 'Brown', '2020-01-15', 62000.00)
            """
            cursor.execute(insert_query) # 执行插入语句
            connection.commit() # 提交事务 (DML操作后需要)
            print(f"{
     cursor.rowcount} 条示例数据已插入。")
        cursor.close() # 关闭游标
    except mysql.connector.Error as err:
        print(f"创建或填充 'employees' 表失败: {
     err}")

def query_all_employees(connection):
    """查询所有员工信息"""
    print("\n--- 查询所有员工 ---")
    try:
        # 使用字典游标方便按列名访问
        cursor = connection.cursor(dictionary=True) # 创建字典游标
        query = "SELECT emp_no, first_name, last_name, hire_date, salary FROM employees"
        cursor.execute(query) # 执行 SELECT 查询

        print(f"查询找到 {
     cursor.rowcount} 行 (注意: rowcount 对SELECT的行为可能不一致，建议迭代判断)")

        # 打印列描述信息 (可选)
        print("列描述:")
        for col_desc in cursor.description: # 遍历列描述
            print(col_desc) # 打印每一列的描述 (name, type_code, etc.)

        print("\n员工列表:")
        # 方式1: fetchall() 获取所有行
        # all_rows = cursor.fetchall()
        # if not all_rows:
        # print("没有找到员工数据。")
        # else:
        # for row in all_rows:
        # print(f"  ID: {row['emp_no']}, Name: {row['first_name']} {row['last_name']}, "
        #              f"Hired: {row['hire_date']}, Salary: {row['salary']}")

        # 方式2: 逐行获取 fetchone()
        # print("\n员工列表 (逐行获取):")
        # row = cursor.fetchone()
        # if not row:
        # print("  没有找到员工数据。")
        # while row:
        # print(f"  ID: {row['emp_no']}, Name: {row['first_name']} {row['last_name']}, "
        #              f"Hired: {row['hire_date']}, Salary: {row['salary']}")
        #     row = cursor.fetchone()

        # 方式3: 直接迭代游标 (最Pythonic的方式)
        print("\n员工列表 (迭代游标):")
        count = 0
        for row in cursor: # 直接迭代游标对象
            count += 1
            print(f"  ID: {
     row['emp_no']}, Name: {
     row['first_name']} {
     row['last_name']}, "
                  f"Hired: {
     row['hire_date']}, Salary: {
     row['salary']}")
        if count == 0:
            print("  没有找到员工数据。")

    except mysql.connector.Error as err:
        print(f"查询员工失败: {
     err}")
    finally:
        if 'cursor' in locals() and cursor: # 检查cursor是否已定义且非None
            cursor.close() # 确保游标被关闭

def query_employees_with_condition(connection, min_salary):
    """查询薪水高于指定值的员工 (参数化查询)"""
    print(f"\n--- 查询薪水高于 {
     min_salary} 的员工 ---")
    try:
        cursor = connection.cursor(dictionary=True)
        # 参数化查询: 使用 %s 作为占位符
        query = """
        SELECT emp_no, first_name, last_name, salary
        FROM employees
        WHERE salary > %s
        ORDER BY salary DESC
        """
        cursor.execute(query, (min_salary,)) # 第二个参数是元组，包含要替换占位符的值

        print("符合条件的员工:")
        fetched_rows = cursor.fetchall() # 获取所有结果
        if not fetched_rows:
            print(f"  没有找到薪水高于 {
     min_salary} 的员工。")
        else:
            for row in fetched_rows:
                print(f"  ID: {
     row['emp_no']}, Name: {
     row['first_name']} {
     row['last_name']}, Salary: {
     row['salary']}")

    except mysql.connector.Error as err:
        print(f"条件查询员工失败: {
     err}")
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()

# --- 主程序逻辑 (续) ---
if __name__ == "__main__":
    # (前面的连接代码)
    DB_HOST = "localhost"
    DB_USER = "your_mysql_user"
    DB_PASSWORD = "your_mysql_password"
    DB_NAME = "your_database_name"

    db_connection = connect_to_mysql_database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

    if db_connection and db_connection.is_connected():
        create_sample_employees_table(db_connection) # 创建/准备示例表

        query_all_employees(db_connection) # 执行查询所有员工
        query_employees_with_condition(db_connection, 60000.00) # 执行条件查询

        db_connection.close() # 完成所有操作后关闭连接
        print(f"\n数据库 '{
     DB_NAME}' 连接已彻底关闭。")

代码解释:

create_sample_employees_table(): 一个辅助函数，用于创建示例表并填充数据，确保后续查询可以执行。
- CREATE TABLE IF NOT EXISTS ...: 避免表已存在时报错。
- cursor.execute("SELECT COUNT(*)...") 和 cursor.fetchone()[0]: 用于检查表是否为空。fetchone() 返回一行，如果表为空或查询无结果，它返回 None；否则返回一个元组（对于默认游标）或字典（对于字典游标）。这里假设默认游标，所以用 [0] 取第一个元素（即COUNT(*)的值）。
- connection.commit(): 对于 INSERT, UPDATE, DELETE 等修改数据的操作，执行后必须调用 connection.commit() 才能将更改永久保存到数据库。 否则，在连接关闭或事务回滚时，这些更改会丢失。MySQL 默认可能是自动提交模式（autocommit=True），但这取决于服务器和连接器配置。显式提交是良好实践。
query_all_employees():
- cursor = connection.cursor(dictionary=True): 使用字典游标。
- cursor.execute(query): 执行 SELECT 语句。
- cursor.description: 打印列信息。每个元组包含 (name, type_code, display_size, internal_size, precision, scale, null_ok)。
- 获取结果的方式：
  - cursor.fetchall(): 一次性获取所有行，返回一个列表，列表中的每个元素是代表一行的字典（因为是字典游标）。适合结果集不大时。
  - cursor.fetchone(): 每次获取一行，直到返回 None。适合逐行处理，或结果集非常大时，避免一次性加载到内存。
  - 直接迭代游标 (for row in cursor:): 这是最 Pythonic 且通常最高效的方式来处理多行结果。它在内部可能类似于 while True: row = cursor.fetchone(); if not row: break; ...。
query_employees_with_condition(): 演示了参数化查询 (Parameterized Query)。
- query = "SELECT ... WHERE salary > %s ...": SQL 语句中使用 %s 作为占位符。
- cursor.execute(query, (min_salary,)): execute 方法的第二个参数是一个元组（或列表），其元素会按顺序替换SQL语句中的 %s 占位符。这是防止 SQL 注入攻击的正确方法。 绝不要使用Python的字符串格式化（如 f-string 或 % 操作符）将变量直接拼接到SQL语句中。
  - 即使只有一个参数，也要传入一个单元素的元组，例如 (value,)。
finally 块: 确保无论 try 块中是否发生异常，游标 (cursor.close()) 和连接 (connection.close()) 都会被关闭。这是非常重要的资源管理实践。

4.1.4 执行 `INSERT`, `UPDATE`, `DELETE` 操作 (DML)

def insert_new_employee(connection, first_name, last_name, hire_date, salary):
    """插入一个新员工记录 (参数化)"""
    print(f"\n--- 插入新员工: {
     first_name} {
     last_name} ---")
    new_emp_no = None
    try:
        cursor = connection.cursor()
        insert_query = """
        INSERT INTO employees (first_name, last_name, hire_date, salary)
        VALUES (%s, %s, %s, %s)
        """
        employee_data = (first_name, last_name, hire_date, salary) # 准备要插入的数据元组
        cursor.execute(insert_query, employee_data) # 执行插入语句，传入数据

        connection.commit() # !! 提交事务以保存更改 !!
        new_emp_no = cursor.lastrowid # 获取最后插入行的 AUTO_INCREMENT ID (如果主键是自增的)
        print(f"员工 '{
     first_name} {
     last_name}' 成功插入。受影响行数: {
     cursor.rowcount}, 新员工ID: {
     new_emp_no}")
    except mysql.connector.Error as err:
        print(f"插入员工失败: {
     err}")
        if connection.is_connected(): # 如果连接仍然有效，可以选择回滚
            print("尝试回滚事务...")
            connection.rollback() # 回滚未提交的更改
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()
    return new_emp_no # 返回新员工的ID

def update_employee_salary(connection, emp_no, new_salary):
    """更新指定员工的薪水 (参数化)"""
    print(f"\n--- 更新员工 ID {
     emp_no} 的薪水为 {
     new_salary} ---")
    try:
        cursor = connection.cursor()
        update_query = "UPDATE employees SET salary = %s WHERE emp_no = %s"
        cursor.execute(update_query, (new_salary, emp_no)) # 执行更新语句，传入参数

        connection.commit() # 提交事务
        if cursor.rowcount > 0:
            print(f"员工 ID {
     emp_no} 的薪水已成功更新。受影响行数: {
     cursor.rowcount}")
        else:
            print(f"未找到员工 ID {
     emp_no}，或薪水未改变。受影响行数: {
     cursor.rowcount}")
    except mysql.connector.Error as err:
        print(f"更新员工薪水失败: {
     err}")
        if connection.is_connected(): connection.rollback()
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()

def delete_employee(connection, emp_no):
    """删除指定员工 (参数化)"""
    print(f"\n--- 删除员工 ID {
     emp_no} ---")
    try:
        cursor = connection.cursor()
        delete_query = "DELETE FROM employees WHERE emp_no = %s"
        cursor.execute(delete_query, (emp_no,)) # 执行删除语句，传入参数

        connection.commit() # 提交事务
        if cursor.rowcount > 0:
            print(f"员工 ID {
     emp_no} 已成功删除。受影响行数: {
     cursor.rowcount}")
        else:
            print(f"未找到员工 ID {
     emp_no} 进行删除。受影响行数: {
     cursor.rowcount}")
    except mysql.connector.Error as err:
        print(f"删除员工失败: {
     err}")
        if connection.is_connected(): connection.rollback()
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()

# --- 主程序逻辑 (续) ---
if __name__ == "__main__":
    # (前面的连接和建表示例代码)
    DB_HOST = "localhost"
    DB_USER = "your_mysql_user"
    DB_PASSWORD = "your_mysql_password"
    DB_NAME = "your_database_name"

    db_connection = connect_to_mysql_database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

    if db_connection and db_connection.is_connected():
        create_sample_employees_table(db_connection) # 确保表存在

        # 执行DML操作
        new_id = insert_new_employee(db_connection, "Peter", "Jones", "2023-01-10", 95000.00)
        if new_id:
            update_employee_salary(db_connection, new_id, 98000.00)
            # query_all_employees(db_connection) # 可以取消注释查看更新后的结果
            delete_employee(db_connection, new_id)
            # query_all_employees(db_connection) # 可以取消注释查看删除后的结果
        else:
            print("由于插入失败，后续的更新和删除操作未执行。")

        # 演示插入一个不存在的ID的更新和删除
        update_employee_salary(db_connection, 9999, 100000.00) # 更新不存在的员工
        delete_employee(db_connection, 9999) # 删除不存在的员工

        db_connection.close()
        print(f"\n数据库 '{
     DB_NAME}' DML操作演示完成，连接已关闭。")

代码解释:

参数化查询: 所有DML语句 (INSERT, UPDATE, DELETE) 都使用了参数化查询 (%s 占位符和 cursor.execute(query, params)），这是防止SQL注入的最佳实践。
connection.commit(): 在执行任何会修改数据的SQL语句后，必须调用 connection.commit() 来使更改生效。否则，当连接关闭或程序结束时，这些更改可能会丢失（取决于MySQL服务器的autocommit设置和连接器的默认行为，但显式提交总是更安全）。
connection.rollback(): 在 except 块中，如果发生错误，可以调用 connection.rollback() 来撤销当前事务中尚未提交的所有更改。这有助于保持数据的一致性。
cursor.lastrowid: 对于 INSERT 操作，如果插入的表有一个 AUTO_INCREMENT 主键，cursor.lastrowid 属性会返回最后插入行的ID。这对于获取新生成的记录ID非常有用。注意：如果一次 INSERT 插入了多行，或者主键不是自增的，其行为可能不同或不可靠。
cursor.rowcount:
- 对于 INSERT, UPDATE, DELETE，rowcount 返回受这些操作影响的行数。
- 对于 SELECT，其行为在DB-API 2.0中没有严格定义，可能返回-1，或者在某些情况下（如使用缓冲游标）返回获取到的行数。不应完全依赖它来判断 SELECT 是否有结果，更好的方式是检查 fetchone()/fetchall() 的返回值。

4.1.5 使用 `with` 语句管理连接和游标 (Context Managers)

Python 的 with 语句提供了一种更优雅、更安全的方式来管理资源（如文件句柄、网络连接、数据库连接和游标），它可以确保资源在使用完毕后即使发生异常也能被正确释放。

mysql-connector-python 的连接对象和游标对象都支持上下文管理协议。

def query_with_context_manager(db_config):
    """使用 with 语句管理连接和游标"""
    print("\n--- 使用 with 语句查询员工 (薪水 > 70000) ---")
    query = """
    SELECT emp_no, first_name, last_name, salary
    FROM employees
    WHERE salary > %s
    ORDER BY first_name
    """
    min_salary = 70000.00
    results = [] # 用于存储查询结果

    try:
        # 'with' 语句确保连接在使用后自动关闭，即使发生错误
        with mysql.connector.connect(**db_config) as connection: # db_config 是一个包含连接参数的字典
            print("数据库连接已通过 'with' 语句打开。")
            # 'with' 语句确保游标在使用后自动关闭
            with connection.cursor(dictionary=True) as cursor:
                print("游标已通过 'with' 语句创建。")
                cursor.execute(query, (min_salary,))
                for row in cursor:
                    results.append(row)
                    print(f"  Fetched: ID={
     row['emp_no']}, Name={
     row['first_name']}, Salary={
     row['salary']}")
            print("游标已通过 'with' 语句自动关闭。")
        print("数据库连接已通过 'with' 语句自动关闭。")

        if not results:
            print(f"  没有找到薪水高于 {
     min_salary} 的员工。")

    except mysql.connector.Error as err:
        print(f"使用 'with' 语句查询失败: {
     err}")
    # 注意: connection.commit() 仍然需要显式调用，'with' 语句不自动提交事务。
    # 如果这里有DML操作，且未在with块内commit，则不会保存。

# --- 主程序逻辑 (续) ---
if __name__ == "__main__":
    DB_CONFIG = {
    # 将连接参数组织成字典
        'host': "localhost",
        'user': "your_mysql_user",
        'password': "your_mysql_password",
        'database': "your_database_name",
        'charset': 'utf8mb4' # 明确指定字符集是一个好习惯
    }
    # 假设 employees 表已存在且有数据
    # create_sample_employees_table(mysql.connector.connect(**DB_CONFIG)) # 如果需要确保表存在

    query_with_context_manager(DB_CONFIG)

代码解释:

with mysql.connector.connect(**db_config) as connection::
- 当进入 with 块时，mysql.connector.connect() 被调用并返回一个连接对象，赋值给 connection。
- 当退出 with 块时（无论是正常结束还是发生异常），连接对象的 __exit__ 方法会被自动调用，该方法会负责关闭连接 (connection.close())。
with connection.cursor(dictionary=True) as cursor::
- 类似地，游标对象也实现了上下文管理协议。当退出内部的 with 块时，游标会自动关闭 (cursor.close())。
事务管理: with 语句本身不负责自动提交或回滚事务。对于DML操作，你仍然需要在 with connection ... 块内部显式调用 connection.commit() 或 connection.rollback()。如果 with 块因为异常而退出，且你没有 commit，那么未提交的更改（如果事务是活动的）通常会被数据库服务器隐式回滚（当连接断开时），或者你需要更细致地在 except 块中处理 rollback。

企业级实践: 强烈推荐使用 with 语句来管理数据库连接和游标，因为它可以减少因忘记关闭资源而导致的资源泄露风险，使代码更整洁、更健壮。

四、基本连接与查询操作

4.2 使用 `PyMySQL`

PyMySQL 是一个非常受欢迎的纯Python MySQL驱动程序，以其易用性、轻量级和良好的社区支持而闻名。它的API在很大程度上遵循Python DB-API 2.0规范，因此很多操作与 mysql-connector-python 类似，但在某些细节和特定功能上会有所不同。

4.2.1 安装 `PyMySQL`

首先，确保你已经安装了 PyMySQL。如果需要支持 MySQL 8.0+ 的 caching_sha2_password 认证，建议同时安装 cryptography。

pip install PyMySQL
# 或者为了更好的认证支持 (推荐):
pip install PyMySQL[rsa]
# 或者分开安装:
# pip install PyMySQL
# pip install cryptography

4.2.2 建立数据库连接

PyMySQL 的连接方式与 mysql-connector-python 类似，但函数名和一些参数可能略有不同。

import pymysql # 导入 pymysql 模块
import pymysql.cursors # 导入 pymysql.cursors 用于指定游标类型，例如字典游标
import os # 用于从环境变量读取敏感信息 (推荐做法)

def pymysql_connect_to_db(db_host, db_user, db_password, db_name, db_port=3306, db_charset='utf8mb4'):
    """使用 PyMySQL 连接到指定的数据库。"""
    try:
        connection = pymysql.connect( # 调用 pymysql.connect() 函数建立连接
            host=db_host, # MySQL 服务器主机名或IP
            user=db_user, # MySQL 用户名
            password=db_password, # MySQL 密码
            database=db_name, # 要连接的数据库名
            port=db_port, # MySQL 端口，默认为3306
            charset=db_charset, # 指定连接字符集，推荐 utf8mb4 以支持各种字符包括emoji
            connect_timeout=10, # 连接超时时间（秒）
            # cursorclass=pymysql.cursors.DictCursor # 可以在连接时全局指定默认游标类型
            # ssl={'ca': '/path/to/ca.pem'} # SSL连接配置示例，需要提供正确的证书路径
        )
        print(f"PyMySQL: 成功连接到数据库 '{
     db_name}' 在服务器 {
     db_host}:{
     db_port}")
        # PyMySQL 连接对象没有直接的 get_server_info() 方法，可以通过查询获取
        with connection.cursor() as cursor: # 使用 with 管理游标
            cursor.execute("SELECT VERSION()") # 执行查询服务器版本的SQL
            server_version = cursor.fetchone() # 获取查询结果
            if server_version:
                print(f"PyMySQL: MySQL 服务器版本: {
     server_version[0]}") # server_version 是一个元组
        return connection # 返回连接对象
    except pymysql.Error as err: # 捕获 pymysql 的错误
        # PyMySQL 的错误代码和消息格式可能与 mysql.connector 不同
        # err.args[0] 通常是错误码, err.args[1] 是错误消息
        error_code = err.args[0] if len(err.args)