我要评论前言
在现代应用程序中，数据库至关重要。mysql 是流行的关系型数据库管理系统，广泛用于各类应用。本文以 mysql 服务器：192.168.10.101、python 服务器：192.168.10.102 为例，讲解 python 远程操作 mysql 完整流程，包含连接、crud、模糊查询、join、事务、隔离级别、连接池，并补充 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. 授权数据库权限
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. 刷新权限
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
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;
补充知识点
- read uncommitted：可读取未提交数据 → 脏读。
- read committed：只能读已提交数据 → 解决脏读。
- repeatable read：mysql 默认 → 解决脏读、不可重复读。
- 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操作mysql数据库的文章就介绍到这了,更多相关python操作mysql数据库内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论