python操作MySQL数据库

目录

一：安装python MySQL连接库

1：安装mysql-connector-python

2：安装pyMySQL（作为替代）

二：python连接MySQL数据库

三：常见的MySQL操作

1：插入数据（INSERT）

2：修改/更新数据 (UPDATE)

3：删除数据 (DELETE)

4：查询数据 (SELECT)

5：执行多条SQL语句

6：使用like进行模糊查询

7：使用JOIN进行联合查询

四：使用连接池

1：连接池简介

2：创建连接池，并获取连接

3：连接池的优势

五：事务管理

1：开始事务

2：提交事务

3：回滚事务

4：事务的隔离级别

（1）读未提交(READ UNCOMMITTED)

（2）读已提交(READ COMMITTED)

（3）可重复读(REPEATABLE READ)

（4）串行化(SERIALIZABLE)

5：事务隔离级别总结

6：案例

一：安装python MySQL连接库

1：安装mysql-connector-python

提前建立用户：

mysql -uroot -ppwd123
create user 'root'@'%' identified by 'pwd123';
grant all on *.* to 'root'@'%';
alter user 'root'@'%' identified with mysql_native_password by 'pwd123';
flush privileges;
show databases;

导入数据库：

mysql -uroot -ppwd123
source ./init.sql
show databases;
show tables;
select * from user;

安装mysql-connector-python：

pip3 install mysql-connector-python

2：安装pyMySQL（作为替代）

pip3 install pymysql	#第二个模块

二：python连接MySQL数据库

#导入连接库
vim aaa.py	#创建py文件
import pymysql	#导入模块

#定义连接
db=pymysql.connect(
	host="192.168.10.101",
	user="root",
	password="pwd123",		# 数据库的密码
	database="testdb"		# 要连接的数据库名称
)

#创建连接
cursor=db.cursor () 	#创建游标对象，意味着和testdb连接起来
#执行 SQL 语句
cursor.execute ("SELECT * FROM users")
#获取查询结果
results=cursor.fetchall ()	 	#fetchall () 方法用于获取所有结果
for row in results:
	print (row)		#row：行

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

#运行aaa.py   
python3 aaa.py

三：常见的MySQL操作

1：插入数据（INSERT）

插入数据时，我们使用INSERT INTO语句，通过execute（）方法执行插入操作。为了防止SQL注入攻击，插入语句中的值应使用%s占位符。

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

#运行aaa.py   
python3 aaa.py

2：修改/更新数据 (UPDATE)

更新数据时，我们使用UPDATE语句，通过execute（）方法执行。通常我们会添加where条件，以确保只更新需要更新的记录。

cursor.execute ("UPDATE users SET age = % s WHERE name = % s", (26, "Alice"))
db.commit () # 提交事务，保存更新的数据

#运行aaa.py   
python3 aaa.py

3：删除数据 (DELETE)

删除数据时，我们使用 DELETE 语句，并通过 WHERE 条件确保删除特定记录。

cursor.execute ("DELETE FROM users WHERE name = % s", ("Alice",))
db.commit () # 提交事务，保存删除的数据

#运行aaa.py   
python3 aaa.py

4：查询数据 (SELECT)

查询数据时，使用 SELECT 语句。你可以使用 fetchall () 获取所有记录，或使用 fetchone () 获取一条记录。

cursor.execute ("SELECT * FROM users where name like %s",("zhang%"))		#模糊查询

5：执行多条SQL语句

对于批量插入、更新等操作，可以使用 executemany () 方法一次执行多条 SQL 语句。

cursor.executemany (
"INSERT INTO users (name, age) VALUES (% s, % s)",
[("Bob", 30), ("Charlie", 35), ("David", 28)]
)
db.commit () # 提交事务

6：使用like进行模糊查询

LIKE 关键字允许你进行模糊查询。你可以使用 % 通配符来匹配任意字符。

cursor.execute ("SELECT * FROM users WHERE name LIKE % s", ("%%%",))
results = cursor.fetchall ()
for row in results:
print (row)

7：使用JOIN进行联合查询

在多个表之间建立关系时，JOIN 关键字用于合并多个表的数据。

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)

四：使用连接池

1：连接池简介

连接池是一种管理数据库连接的技术，通过预先创建并维护一组可复用的连接对象，避免频繁创建和销毁连接的开销。连接池的核心思想是连接复用，从而提升系统性能、降低资源消耗。

2：创建连接池，并获取连接

1.安装dbutils
pip3 install dbutils	#模块

2.导入连接库 
vim aaa.py	#创建py文件
import pymysql	#导入模块
from dbutils.pooled_db import PooledDB

#定义连接
dbconfig={
	"host":"192.168.10.101",
	"user":"root",
	"password":"pwd123",		# 数据库的密码
	"database":"testdb"		# 要连接的数据库名称
}
#创建连接池
connection_pool = PooledDB (
	creator=pymysql,	 # 使用 PyMySQL 作为数据库连接库
	maxconnections=5, 	# 连接池中最大连接数
	**dbconfig		#**远程连接，没有**是本地连接
)
#获取连接
db_connection=connection_pool.connection ()
cursor=db_connection.cursor ()		#负责连接数据库

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

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

#运行aaa.py   
python3 aaa.py

3：连接池的优势

• 性能提升：复用现有连接，减少创建/销毁连接的时间。

• 资源管理：控制最大连接数，避免数据库过载。

• 稳定性：支持连接保活、超时重试等机制，增强容错能力。

• 简化开发：开发者无需手动管理连接生命周期。

常见连接池库：HikariCP（高性能）、Druid（阿里开源，功能丰富）、Tomcat JDBC Pool 等。

五：事务管理

事务是由多个 SQL 语句组成的一个工作单元。事务保证了数据的原子性，即所有操作要么都成功，要么都失败。

1：开始事务

cursor.execute ("START TRANSACTION")

使用 BEGIN 或 START TRANSACTION 语句显式开始一个事务

在自动提交模式下，每条SQL语句都是一个独立的事务

事务开始后，所有操作都在一个临时环境中执行，直到提交或回滚

2：提交事务

db.commit ()

使用 COMMIT 语句提交事务

提交后，事务中的所有修改将永久保存到数据库

提交后释放事务持有的所有锁

3：回滚事务

db.rollback ()

使用 ROLLBACK 语句回滚事务

撤销事务中所有未提交的修改

回滚到事务开始前的状态

可以回滚到指定的保存点(SAVEPOINT)

4：事务的隔离级别

数据库提供四种标准隔离级别：

（1）读未提交(READ UNCOMMITTED)

cursor.execute ("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

最低隔离级别

可能读取到其他事务未提交的数据（脏读）

性能最好但数据一致性最差

（2）读已提交(READ COMMITTED)

cursor.execute ("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")

只能读取已提交的数据

避免脏读，但可能出现不可重复读

大多数数据库的默认级别

（3）可重复读(REPEATABLE READ)

cursor.execute ("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")

保证同一事务中多次读取同样数据结果一致

避免脏读和不可重复读

MySQL的默认级别

可能出现幻读

（4）串行化(SERIALIZABLE)

cursor.execute ("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")

最高隔离级别

完全串行执行事务

避免所有并发问题（脏读、不可重复读、幻读）

性能最差

5：事务隔离级别总结

• READ UNCOMMITTED: 允许脏读，最低的隔离级别，性能最好，但容易出现数据不一致的情况。
• READ COMMITTED: 解决了脏读问题，但可能出现不可重复读。
• REPEATABLE READ: 解决了脏读和不可重复读问题，但可能出现幻读。
• SERIALIZABLE: 解决了所有问题，但性能最差，可能导致事务长时间等待。

在选择事务隔离级别时，需要根据应用的具体需求平衡数据一致性和性能。如果事务数据不频繁冲突，可以选择较低的隔离级别以提升性能；而对于数据一致性要求极高的场景，则应选择更高的隔离级别，尽管这会带来性能上的损失。

6：案例

1: 开始事务：
#事务可以通过 START TRANSACTION 来显式开启
cursor.execute ("START TRANSACTION")
2: 提交事务：db.commit ()
3: 回滚事务：db.rollback ()

vim bbb.py
import pymysql
#连接到数据库
conn = pymysql.connect(
	host='localhost',
	user='root',
	password='password',
	database='testdb'
)
#创建一个游标对象
cursor = conn.cursor()
#关闭自动提交模式
conn.autocommit = False
try:
	#开始事务
	#执行插入操作
	cursor.execute("INSERT INTO users (name, age) VALUES ('Eve', 22)")
	cursor.execute("INSERT INTO orders (user_id, amount) VALUES ((SELECT id FROM users WHERE name = 'Eve'), 120.50)")

	#提交事务
	conn.commit ()
	print ("事务已提交。")

except pymysql.MySQLError as err:
	#如果发生错误，回滚事务
	print (f"错误: {err}")
	conn.rollback ()
	print ("事务已回滚。")

finally:
	#关闭游标和连接
	cursor.close()
	conn.close()