Python 操作 MySQL 数据库（长文解析）

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

• 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;演示链接： http://116.62.199.48:7070 ;
• 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 90w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 3100+ 小伙伴加入学习 ，欢迎点击围观

在编程领域中，Python 操作 MySQL 数据库是一项核心技能。无论是构建小型项目还是大型应用，数据库的读写操作都是数据持久化与管理的关键环节。对于编程初学者来说，理解如何通过 Python 与 MySQL 进行交互，能够显著提升开发效率；而中级开发者则可以通过深入掌握高级技巧，优化代码性能与安全性。本文将从基础概念到实战案例，逐步讲解如何用 Python 实现对 MySQL 数据库的高效操作，并通过生动的比喻帮助读者建立直观理解。

环境准备：搭建 Python 与 MySQL 的桥梁

1. 安装 MySQL 数据库

MySQL 是一款开源的关系型数据库管理系统，用户可通过官方安装包或 Docker 快速部署。对于初学者，推荐使用 XAMPP 或 WampServer 等集成环境，它们会自动安装 MySQL 并配置好默认账户（如用户名 root，密码为空）。

2. 安装 Python 的 MySQL 连接库

Python 与 MySQL 的交互依赖驱动程序。最常用的库是 mysql-connector-python，可通过以下命令安装：

pip install mysql-connector-python  

此外，PyMySQL 也是一个流行的选择，两者功能相似，但 mysql-connector-python 由 Oracle 官方维护，稳定性更高。

基础操作：从连接到执行 SQL 语句

3. 连接数据库的流程

连接 MySQL 数据库的过程类似于快递员将包裹送至分拣中心：

1. 建立连接：提供数据库地址、用户名、密码等信息；
2. 创建游标：游标（Cursor）如同分拣员，负责执行 SQL 命令并获取结果；
3. 执行 SQL 语句：通过游标发送指令；
4. 提交或回滚：根据操作类型决定是否需要提交更改；
5. 关闭资源：释放数据库连接，避免占用系统资源。

示例代码：

import mysql.connector  

conn = mysql.connector.connect(  
    host="localhost",  
    user="root",  
    password="",  
    database="test_db"  
)  

cursor = conn.cursor()  

cursor.execute("SELECT VERSION()")  
print(cursor.fetchone())  

cursor.close()  
conn.close()  

4. 创建与操作数据表

创建表结构

数据库表的设计如同快递柜的存储规则，需要定义每个字段的类型和约束。例如，创建一个 users 表：

create_table_sql = """  
CREATE TABLE IF NOT EXISTS users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50) NOT NULL,  
    email VARCHAR(100) UNIQUE,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);  
"""  
cursor.execute(create_table_sql)  
conn.commit()  # 提交更改  

插入数据

插入数据时，应避免直接拼接字符串（如 f"INSERT INTO users(name) VALUES('{user_name}')"），因为这可能导致 SQL 注入攻击。推荐使用参数化查询：

insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"  
data = ("Alice", "alice@example.com")  
cursor.execute(insert_sql, data)  
conn.commit()  

参数 %s 会自动转义特殊字符，确保安全性。

查询数据

查询结果通过游标逐条读取，类似快递员分拣包裹时逐个扫描条形码：

select_sql = "SELECT * FROM users WHERE name = %s"  
cursor.execute(select_sql, ("Alice",))  
for row in cursor.fetchall():  
    print(f"ID: {row[0]}, Name: {row[1]}")  

fetchall() 返回所有结果，若数据量较大，可改用 fetchone() 或 fetchmany(size) 分批处理。

更新与删除操作

更新或删除数据时，需谨慎使用 WHERE 子句，否则可能误操作整张表。例如：

update_sql = "UPDATE users SET email = %s WHERE name = %s"  
cursor.execute(update_sql, ("alice_new@example.com", "Alice"))  

delete_sql = "DELETE FROM users WHERE id < 5"  
cursor.execute(delete_sql)  
conn.commit()  

高级技巧：事务与性能优化

5. 事务处理：银行转账的启示

事务（Transaction）是数据库操作的“原子单位”，例如银行转账需要确保“从 A 账户扣除金额”和“向 B 账户存入金额”同时成功或失败。通过 BEGIN、COMMIT 和 ROLLBACK 可实现这一逻辑：

try:  
    conn.start_transaction()  # 开始事务  
    cursor.execute("UPDATE accounts SET balance=balance-100 WHERE user='A'")  
    cursor.execute("UPDATE accounts SET balance=balance+100 WHERE user='B'")  
    conn.commit()  # 提交事务  
except Exception as e:  
    conn.rollback()  # 出错时回滚  
    print(f"Error: {e}")  

在 MySQL 中，默认引擎 InnoDB 支持事务，而 MyISAM 不支持。

6. 批量操作：高效处理数据流

单条插入数据时，每次操作都会产生网络开销。批量操作如同批量邮寄包裹，能显著提升速度：

data = [  
    ("Bob", "bob@example.com"),  
    ("Charlie", "charlie@example.com")  
]  

cursor.executemany("INSERT INTO users (name, email) VALUES (%s, %s)", data)  
conn.commit()  

7. 预处理语句与存储过程

预处理语句（Prepared Statements）可复用 SQL 逻辑，减少解析时间。例如：

insert_prep = cursor.prepate_statement("INSERT INTO users (name) VALUES (?)")  
cursor.execute(insert_prep, ("Dave",))  

存储过程（Stored Procedures）则允许将复杂逻辑封装在数据库端，减少客户端与服务器的交互次数。

实战案例：用户管理系统

8. 完整代码示例

以下是一个简单的用户管理系统，包含增删改查功能：

import mysql.connector  

def connect():  
    return mysql.connector.connect(  
        host="localhost",  
        user="root",  
        password="",  
        database="test_db"  
    )  

def create_table():  
    conn = connect()  
    cursor = conn.cursor()  
    cursor.execute("""  
    CREATE TABLE IF NOT EXISTS users (  
        id INT AUTO_INCREMENT PRIMARY KEY,  
        name VARCHAR(50) NOT NULL,  
        email VARCHAR(100) UNIQUE,  
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
    );  
    """)  
    conn.commit()  
    cursor.close()  
    conn.close()  

def add_user(name, email):  
    conn = connect()  
    cursor = conn.cursor()  
    insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"  
    cursor.execute(insert_sql, (name, email))  
    conn.commit()  
    cursor.close()  
    conn.close()  

def get_all_users():  
    conn = connect()  
    cursor = conn.cursor(dictionary=True)  # 返回字典格式结果  
    cursor.execute("SELECT * FROM users")  
    users = cursor.fetchall()  
    cursor.close()  
    conn.close()  
    return users  

def update_email(user_id, new_email):  
    conn = connect()  
    cursor = conn.cursor()  
    update_sql = "UPDATE users SET email = %s WHERE id = %s"  
    cursor.execute(update_sql, (new_email, user_id))  
    conn.commit()  
    cursor.close()  
    conn.close()  

def delete_user(user_id):  
    conn = connect()  
    cursor = conn.cursor()  
    delete_sql = "DELETE FROM users WHERE id = %s"  
    cursor.execute(delete_sql, (user_id,))  
    conn.commit()  
    cursor.close()  
    conn.close()  

if __name__ == "__main__":  
    create_table()  
    add_user("Eve", "eve@example.com")  
    users = get_all_users()  
    print(users)  
    update_email(1, "eve_new@example.com")  
    delete_user(2)  

最佳实践与注意事项

9. 安全性与性能优化

防止 SQL 注入

永远不要直接拼接用户输入的字符串到 SQL 中。例如，以下代码存在高风险：

user_input = "'); DROP TABLE users; --"  
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")  

参数化查询是唯一安全的解决方案。

使用连接池

频繁创建和关闭数据库连接会消耗资源。通过 mysql-connector-python 的 pooling 模块可实现连接复用：

from mysql.connector import pooling  

pool = mysql.connector.pooling.MySQLConnectionPool(  
    pool_name="my_pool",  
    pool_size=5,  
    host="localhost",  
    user="root",  
    password="",  
    database="test_db"  
)  

conn = pool.get_connection()  

索引优化

对高频查询的字段添加索引，例如：

ALTER TABLE users ADD INDEX idx_email (email);  

但需注意索引会占用存储空间，并可能降低写入速度。

结论

通过本文，读者已掌握了从基础到进阶的 Python 操作 MySQL 数据库 技能。从连接数据库、执行 SQL 命令，到事务管理与性能优化，每个环节都需结合实际场景灵活运用。对于初学者，建议从简单项目入手（如用户管理系统），逐步理解数据交互的逻辑；中级开发者则可通过优化查询语句、使用连接池等手段提升系统效率。

数据库操作是编程的“基础设施”，如同快递系统的分拣流程，看似基础却不可或缺。掌握这一技能后，读者可更自信地构建复杂应用，并为后续学习分布式数据库或 NoSQL 技术打下坚实基础。记住，实践是检验真理的唯一标准——动手编写代码，让理论转化为能力！