Python操作MySQL数据库的方法实例

前言

数据库是现代应用的基石，MySQL作为关系型数据库的代表，几乎无处不在。今天这篇就来聊聊如何用Python远程操作MySQL，从连接、CRUD、模糊查询、JOIN，到事务、隔离级别和连接池，一步到位。文章里会以两台服务器为例：MySQL服务器：192.168.10.101，Python服务器：192.168.10.102。同时，MySQL端创建远程用户、授权、旧认证插件兼容这些前置操作也会一并讲清楚。

一、安装 Python MySQL 连接库

1. 安装命令

先把必要的库装上，两条命令搞定：

pip install mysql-connector-python
pip install pymysql

2. 补充知识点

• pymysql：纯Python实现的MySQL客户端，跨平台，支持TCP/IP远程连接，轻量好用。
• dbutils：数据库连接池组件，用来复用连接，减少反复创建/销毁连接的开销。
• mysql-connector-python：MySQL官方驱动，可以作为pymysql的备选。
• 生产环境建议固定库版本，避免兼容性问题翻车。

二、MySQL 服务器端配置（192.168.10.101）

所有操作都在MySQL命令行下执行（mysql -u root -p）。

1. 创建远程访问用户

CREATE USER 'root'@'192.168.10.102' IDENTIFIED BY 'pwd123';

2. 授权数据库权限

GRANT REPLICATION  ON *.* TO 'root'@'192.168.10.102';

3. 旧密码认证插件兼容

MySQL 8.0+默认认证插件是caching_sha2_password，pymysql不支持它，所以需要改成老插件：

ALTER USER 'root'@'192.168.10.102' IDENTIFIED WITH mysql_native_password BY 'pwd123';

4. 刷新权限

FLUSH PRIVILEGES;

5. 添加数据

创建测试数据库和表，插入一些示例数据：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS testdb;

-- 切换到 testdb 数据库
USE testdb;

-- 创建 users 表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

-- 创建 orders 表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 插入数据到 users 表
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35),
('Da vid', 28);

-- 插入数据到 orders 表
INSERT INTO orders (user_id, amount) VALUES
(1, 100.50),
(2, 150.75),
(3, 200.00),
(1, 50.25);

-- -- 查询所有用户信息
-- SELECT * FROM users;

-- -- 查询所有订单信息
-- SELECT * FROM orders;

-- -- 查询用户及其对应的订单信息（JOIN查询）
-- SELECT users.name, users.age, orders.amount
-- FROM users
-- JOIN orders ON users.id = orders.user_id;

-- -- 更新某个用户的数据，修改 Bob 的年龄
-- UPDATE users SET age = 31 WHERE name = 'Bob';

-- -- 删除 Charlie 用户及其对应的订单数据
-- DELETE FROM orders WHERE user_id = (SELECT id FROM users WHERE name = 'Charlie');
-- DELETE FROM users WHERE name = 'Charlie';

-- -- 设置事务隔离级别为 REPEATABLE READ
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

补充知识点

• MySQL 8.0+ 默认认证插件：caching_sha2_password，pymysql 不支持，得改成 mysql_native_password。
• @'192.168.10.102' 限制了只允许该 Python 服务器连接，更安全。
• 授权后必须执行 FLUSH PRIVILEGES 才能生效。

三、Python 连接 MySQL 数据库

创建文件 01_connect.py：

import pymysql

db = pymysql.connect(
    host="192.168.10.101",  #MySQL数据库的地址
    user="root",           #数据库的用户名
    password="pwd123",     #密码
    database="testdb",     #要连接的数据库的名称
    charset="utf8mb4"
)         #创建数据库的连接

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

cursor.execute("select * from users")   #执行SQL语句

results=cursor.fetchall()
for row in results:
    print(row)       #获取查询结果


data = cursor.fetchone()
print("MySQL 版本：", data)


cursor.close()   #关闭游标连接
db.close()     #关闭数据库连接

补充知识点

• host：必须写 MySQL 远程 IP，不能写 localhost。
• charset=utf8mb4：支持中文和表情，生产环境的标准字符集。
• cursor：执行 SQL、获取结果的对象。
• 一定要记得关闭游标和连接，避免资源泄漏。

执行命令：

python3 01_connect.py

四、常见的 MySQL 操作

创建文件 02_operate.py（可以在01_connect.py的基础上改SQL语句，然后根据操作类型调整增删改查。改完后可以到MySQL上面查看效果）：

import pymysql

db = pymysql.connect(
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb",
    charset="utf8mb4"
)
cursor = db.cursor()

# 创建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
""")

# 插入
cursor.execute("INSERT INTO users(name, age) VALUES(%s, %s)", ("Alice", 25))
db.commit()

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

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

# 删除
cursor.execute("DELETE FROM users WHERE name=%s", ("Alice",))
db.commit()

# 模糊查询
cursor.execute("SELECT * FROM users WHERE name LIKE %s", ("%li%",))
print("模糊查询：", cursor.fetchall())

# 联合查询
cursor.execute("""
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
""")
print("联合查询：", cursor.fetchall())

cursor.close()
db.close()

补充知识点

• 增删改操作必须调用 commit()，否则不会写入数据库。
• 使用 %s 占位符可以防止 SQL 注入。
• fetchall() 获取全部结果，fetchone() 只获取一条。
• LIKE %xxx% 实现全模糊匹配。
• INNER JOIN 只返回匹配成功的记录。

执行命令：

python3 02_operate.py

五、事务管理

创建文件 03_transaction.py：

import pymysql

db = pymysql.connect(
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb"
)
cursor = db.cursor()

db.autocommit = False

try:
    cursor.execute("INSERT INTO users(name, age) VALUES('Tom', 20)")
    cursor.execute("UPDATE users SET age=21 WHERE name='Tom'")
    db.commit()
    print("事务执行成功")
except:
    db.rollback()
    print("事务已回滚")
finally:
    cursor.close()
    db.close()

补充知识点（ACID 完整）

• 原子性（Atomicity）：事务是最小单元，所有 SQL 要么全部成功，要么全部失败回滚。
• 一致性（Consistency）：事务前后数据完整性约束（主键、外键、非空）不被破坏。
• 隔离性（Isolation）：并发事务相互隔离，避免脏读、不可重复读、幻读。
• 持久性（Durability）：事务提交后，修改永久生效，即使数据库崩溃也不丢失。

执行命令：

python3 03_transaction.py

六、MySQL 四种事务隔离级别

在 MySQL 命令行中设置：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

补充知识点

1. READ UNCOMMITTED：可读取未提交数据，容易产生脏读。
2. READ COMMITTED：只能读已提交数据，解决了脏读。
3. REPEATABLE READ：MySQL 默认级别，解决了脏读和不可重复读。
4. SERIALIZABLE：最高级别，串行执行，不会出现并发问题，但性能最低。

七、使用连接池

1. 先安装相关软件：

pip install dbutils

2. 创建文件 04_pool.py：

from dbutils.pooled_db import PooledDB
import pymysql

#数据库的连接配置
pool = PooledDB(
    creator=pymysql,
    host="192.168.10.101",
    user="root",
    password="pwd123",
    database="testdb",
    charset="utf8mb4",
    maxconnections=10,
    mincached=2
)     
  
#创建连接池
connection_pool=PooledDb(
    creator=pymysql,    #使用pymysql作为数据库连接库
    maxconnection=5,    #连接池中最大连接数
    **dbconfig
)

#获取连接
db_connection=connection_pool.connection()
cursor=db_connecction.cursor()

cursor.execute("select * from users")
results=cursor.fetchall()

for row in results:
    print(row)

cursor.close()
db_connection.close()   #连接会自动归还给连接池

补充知识点

• 连接池预先创建连接，避免频繁创建/销毁，性能提升非常明显。
• close() 是归还连接给池，不是关闭物理连接。
• maxconnections：最大连接数，保护数据库不被压垮。
• mincached：初始空闲连接数，提升启动速度。
• 高并发项目必须使用连接池，这是基本功。

执行命令：

python3 04_pool.py

总结

从远程用户创建到Python连接、CRUD、事务、隔离级别，再到连接池，这套流程覆盖了日常开发中绝大部分场景。掌握了这些，基本就能应对大多数Python+MySQL的远程操作需求了。