Python操作Mysql数据库

百结214

746人浏览 · 2026-04-09 11:56:43

百结214 · 2026-04-09 11:56:43 发布

一、前言

在现代应用程序中，数据库是核心数据存储组件。MySQL 是最流行的开源关系型数据库管理系统（RDBMS），Python 可通过专用连接库与 MySQL 交互，实现数据的增删改查；同时连接池是高并发场景下优化数据库连接管理的关键技术，能大幅提升应用性能。

本笔记完整覆盖：环境安装、基础连接、CRUD 操作、连接池优化、事务管理、隔离级别等核心知识点，可直接用于学习、开发和面试备考。

二、环境准备：安装 Python MySQL 连接库

Python 操作 MySQL 需要依赖第三方连接库，主流有两种方案：

1. 官方推荐库：`mysql-connector-python`

安装命令：

pip install mysql-connector-python

2. 第三方替代库：`PyMySQL`（本笔记示例采用）

安装命令：

pip install pymysql

三、Python 连接 MySQL 数据库基础流程

1. 核心步骤总览

导入连接库
创建数据库连接
创建游标对象（执行 SQL 的载体）
执行 SQL 语句
获取 / 处理查询结果
关闭游标与数据库连接

2. 完整基础代码

python

# 1. 导入连接库
import pymysql

# 2. 创建数据库连接
db = pymysql.connect(
    host="localhost",      # MySQL 服务器地址，本地为 localhost，远程填 IP
    user="root",           # 数据库用户名，默认 root
    password="password",   # 数据库密码
    database="testdb"      # 要连接的目标数据库名
)

# 3. 创建游标对象
cursor = db.cursor()

# 4. 执行 SQL 语句（示例：查询 users 表所有数据）
cursor.execute("SELECT * FROM users")

# 5. 获取查询结果
# fetchall()：获取所有结果；fetchone()：获取单条记录
results = cursor.fetchall()
for row in results:
    print(row)

# 6. 关闭连接（必须执行，避免资源泄漏）
cursor.close()
db.close()

四、常见 MySQL 操作（CRUD）

核心注意事项

写操作（增 / 改 / 删）必须执行 db.commit() 提交事务，否则数据不会真正写入数据库；
所有参数化查询使用 %s 占位符，禁止字符串拼接 SQL，避免 SQL 注入攻击。

1. 插入数据（INSERT）

python

# 单条插入
cursor.execute(
    "INSERT INTO users (name, age) VALUES (%s, %s)",
    ("Alice", 25)
)
db.commit()  # 提交事务，保存数据

# 批量插入（使用 executemany 提升效率）
data = [("Bob", 30), ("Charlie", 35), ("David", 28)]
cursor.executemany(
    "INSERT INTO users (name, age) VALUES (%s, %s)",
    data
)
db.commit()

2. 更新数据（UPDATE）

python

cursor.execute(
    "UPDATE users SET age = %s WHERE name = %s",
    (26, "Alice")
)
db.commit()

3. 删除数据（DELETE）

python

cursor.execute(
    "DELETE FROM users WHERE name = %s",
    ("Alice",)  # 单元素元组必须加逗号
)
db.commit()

4. 查询数据（SELECT）

python

# 全表查询
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# 条件查询
cursor.execute("SELECT * FROM users WHERE age > %s", (25,))
results = cursor.fetchall()

5. 高级查询

（1）模糊查询（LIKE）

使用 % 通配符匹配任意字符：

python

# 查询 name 包含字母 a 的所有用户
cursor.execute(
    "SELECT * FROM users WHERE name LIKE %s",
    ("%a%",)
)
results = cursor.fetchall()

（2）联合查询（JOIN）

多表关联查询，示例：users 表与 orders 表内连接：

python

cursor.execute("""
    SELECT users.name, orders.amount
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
""")
results = cursor.fetchall()
for row in results:
    print(row)

五、连接池优化：DBUtils 实现高并发连接管理

1. 连接池核心原理

传统方式：每次请求都新建 / 销毁数据库连接，开销极大，高并发下性能骤降；
连接池：提前创建一批连接放入池中，请求时直接从池获取，用完归还，大幅减少连接创建 / 销毁开销，同时限制最大连接数，避免数据库过载。

2. 环境准备

PyMySQL 不原生支持连接池，需安装 DBUtils 库：

bash

pip install dbutils

3. 完整连接池实现代码

python

from dbutils.pooled_db import PooledDB
import pymysql

# 1. 数据库连接配置
dbconfig = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "testdb"
}

# 2. 创建连接池
connection_pool = PooledDB(
    creator=pymysql,       # 指定连接库为 PyMySQL
    maxconnections=5,       # 连接池最大连接数，根据业务调整
    **dbconfig              # 传入连接配置
)

# 3. 从连接池获取连接、执行操作
# 获取连接
db_connection = connection_pool.connection()
cursor = db_connection.cursor()

# 执行SQL（示例：查询数据）
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# 4. 关闭游标与连接（连接会自动归还到池，无需手动销毁）
cursor.close()
db_connection.close()

4. 连接池核心优势

优势	说明
性能提升	减少连接创建 / 销毁的系统开销，高并发下性能提升显著
资源管控	限制最大连接数，避免过多连接导致数据库崩溃
简化管理	统一管理连接生命周期，代码结构更清晰，减少资源泄漏风险

六、事务管理

1. 事务核心概念

事务是由多个 SQL 组成的工作单元，保证数据的原子性：所有操作要么全部成功，要么全部失败，避免数据不一致。

2. 事务核心操作

python

# 1. 开启事务（可显式执行，默认执行SQL即自动开启）
cursor.execute("START TRANSACTION")

# 2. 执行事务内SQL（示例：转账操作）
cursor.execute("UPDATE account SET balance = balance - 100 WHERE user = %s", ("A",))
cursor.execute("UPDATE account SET balance = balance + 100 WHERE user = %s", ("B",))

# 3. 提交事务：所有操作成功，永久保存修改
db.commit()

# 4. 回滚事务：操作失败，撤销所有修改
# db.rollback()

3. 事务四大特性（ACID）

原子性（Atomicity）：事务是不可分割的最小单位，全成或全败；
一致性（Consistency）：事务执行前后，数据库完整性约束不变；
隔离性（Isolation）：多个事务并发执行时，互不干扰；
持久性（Durability）：事务提交后，数据永久写入数据库，不会回滚。

七、事务隔离级别

MySQL 支持 4 种事务隔离级别，隔离性越高，数据一致性越好，但性能越低。MySQL 默认隔离级别为 REPEATABLE READ。

1. 四种隔离级别详细对比

隔离级别	英文名称	核心描述	解决的问题	存在的问题	适用场景	Python 设置命令
未提交读	READ UNCOMMITTED	可读取其他事务未提交的数据	无	脏读、不可重复读、幻读	数据一致性要求极低的场景（几乎不推荐）	`cursor.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")`
提交读	READ COMMITTED	仅读取其他事务已提交的数据	脏读	不可重复读、幻读	大多数普通业务场景，平衡一致性与性能	`cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")`
可重复读	REPEATABLE READ	事务内多次读取同一数据，值保持不变	脏读、不可重复读	幻读	MySQL 默认级别，适用于金融、余额等一致性要求高的场景	`cursor.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")`
串行化	SERIALIZABLE	事务完全串行执行，完全隔离	脏读、不可重复读、幻读	性能开销极大，锁竞争严重	数据一致性要求极高的场景（如银行转账、库存管理）	`cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")`

2. 三个并发问题定义

脏读：一个事务读取了另一个事务未提交的数据，若该事务回滚，读取的数据无效；
不可重复读：同一事务内，两次读取同一数据，结果不一致（其他事务提交了修改）；
幻读：同一事务内，两次执行相同范围查询，结果集行数不一致（其他事务插入 / 删除了数据）。

八、完整实战示例：带异常处理的数据库操作

生产环境必须添加异常处理，避免程序崩溃：

python

import pymysql
from dbutils.pooled_db import PooledDB

# 1. 初始化连接池
dbconfig = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "testdb"
}
pool = PooledDB(creator=pymysql, maxconnections=5, **dbconfig)

def db_operation():
    conn = None
    cursor = None
    try:
        # 从连接池获取连接
        conn = pool.connection()
        cursor = conn.cursor()
        
        # 开启事务
        conn.begin()
        
        # 执行操作：插入数据
        cursor.execute(
            "INSERT INTO users (name, age) VALUES (%s, %s)",
            ("Tom", 28)
        )
        
        # 提交事务
        conn.commit()
        print("操作成功")
        
    except Exception as e:
        # 异常回滚
        if conn:
            conn.rollback()
        print(f"操作失败: {str(e)}")
    finally:
        # 关闭资源
        if cursor:
            cursor.close()
        if conn:
            conn.close()  # 归还连接到池

if __name__ == "__main__":
    db_operation()

九、总结

Python 操作 MySQL 主要使用 PyMySQL 库，流程为连接数据库、创建游标、执行 SQL 并关闭连接，增删改需 commit 提交事务，异常时 rollback 回滚保证数据安全，使用 % s 占位符防止 SQL 注入，批量操作用 executemany 提高效率，高并发场景用连接池复用连接、减少开销，事务具有 ACID 特性，MySQL 默认隔离级别为可重复读，代码中要捕获异常并关闭连接，避免资源泄漏。