在现代应用程序中，数据库至关重要。MySQL 是流行的关系型数据库管理系统，广泛用于各类应用。本文以 MySQL 服务器：192.168.10.101、Python 服务器：192.168.10.102 为例，讲解 Python 远程操作 MySQL 完整流程，包含连接、CRUD、模糊查询、JOIN、事务、隔离级别、连接池，并补充 MySQL 端创建远程用户、授权、旧认证插件兼容。
一、安装 Python MySQL 连接库
1. 安装命令
bash
运行
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. 创建远程访问用户
sql
CREATE USER 'root'@'192.168.10.102' IDENTIFIED BY 'pwd123';
2. 授权数据库权限
sql
GRANT REPLICATION ON *.* TO 'root'@'192.168.10.102';
3. 旧密码认证插件兼容
sql
ALTER USER 'root'@'192.168.10.102' IDENTIFIED WITH mysql_native_password BY 'pwd123';
4. 刷新权限
sql
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),
('David', 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：MySQL 5.x 与 pymysql 兼容插件。
- @'192.168.10.102'：仅允许该 Python 服务器连接，更安全。
- 授权后必须 FLUSH PRIVILEGES 才能生效。
三、Python 连接 MySQL 数据库
创建文件：01_connect.py
python
运行
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、获取结果的对象。
- 必须关闭游标与连接，避免资源泄漏。
执行命令
bash
运行
python3 01_connect.py
四、常见的 MySQL 操作
创建文件：02_operate.py （根据情况来写是添加还是删除，可以在01_connect.py的文件基础上做改动，改SQL语句就行！！！然后根据情况改是添加还是修改什么的。然后可以在MySQL上面进行查看）
python
运行
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：只返回匹配成功的记录。
执行命令
bash
运行
python3 02_operate.py
五、事务管理
创建文件：03_transaction.py
python
运行
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）事务提交后，修改永久生效，数据库崩溃也不丢失。
执行命令
bash
运行
python3 03_transaction.py
六、MySQL 四种事务隔离级别
执行位置：MySQL 命令行
sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
补充知识点
七、使用连接池
1.需要先安装相关软件
pip install dbutils
2.创建文件：04_pool.py
python
运行
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：初始空闲连接数，提升启动速度。
- 高并发项目必须使用连接池。
执行命令
bash
运行
python3 04_pool.py
