一安装pythonmysql连接数据库
在开始之前,我们需要安装Python的MySQL连接库。mysql-connector-python是一个官方推荐的库,用于与MySQL数据库进行交互。如果你更习惯使用其他库,PyMySQL也可以作为替代方案
目录
1.安装mysql-connector-python
pip3 install mysql-connector-python2.安装pymysql (代为替代）
pip3 install pymysql二 python 链接mysql数据库
1.导入链接库
首先,我们需要导入pymysq1模块,使用它来连接MySQL数据库并执行SQL语句。
import pymysql2:创建数据库连接
我们使用pymysql.connect()方法来建立数据库连接。连接时需要提供MySQL服务器的地址、用名、密码和要访问的数据库名。
db = pymysql.connect(
host="localhost",
user="root",
password="password",
database="testdb"3:创建游标对象
建立连接后,我们需要创建一个游标对象,通过它来执行SQL语句。
cursor = db. cursor()4.执行sql语句
通过游标对象的execute()方法,我们可以执行SQL语句。在执行SQL时,可以使用%s占位符来避免sql注入攻击
cursor.execute("SELECT * FROM users")5:获取查询结果
对于查询操作,fetchall()方法用于获取所有结果,fetchone()方法用于获取单条记录
results = cursor.fetchall()
for row in results:
print(row)6:关闭连接
操作完成后,记得关闭游标和数据库连接。
cursor.close()
db.close()三:常见的MySQL 操作
1:插入数据(INSERT)
插入数据时,我们使用INSERT INTO语句,通过execute()方法执行插入操作。为了防止SQL注入击,入语句中的值应使用%s占位符
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 25))
db.commit()2:更新数据(UPDATE)
更新数据时,我们使用UPDATE语句,通过execute()方法执行。通常我们会添加WHERE条件,以确只更新需要更新的记录。
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (26, "Alice") )
db.commit()3:删除数据(DELETE)
删除数据时,我们使用DELETE语句,并通过WHERE条件确保删除特定记录。
cursor. execute("DELETE FROM users WHERE name = %s", ("Alice", ))
db.commit()4:查询数据(SELECT)
查询数据时,使用SELECT语句。你可以使用fetchall()获取所有记录,或使用fetchone()获取一条记录。
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
print(row)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", ("%a%", ) )
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:创建连接池
PyMySQL 并不直接支持连接池,但我们可以使用DBUtils 库来创建连接池。首先需要安装 DBUtils:
pip install dbutils
from dbutils.pooled_db import PooledDB
import pymysql
dbconfig = {
"host": "localhost",
"user": "root",
"password": "password",
"database": "testdb"
}
connection_pool = PooledDB(
creator=pymysq1,
maxconnections=5,
** dbconfig
)3:获取连接
从连接池中获取连接时,可以使用connection()方法。每次获取到的连接都可以直接执行数据库操作
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()4:连接池的优势
· 性能提升:连接池减少了每次数据库操作时创建新连接的开销,提高了数据库操作的效率。 · 资源管理:连接池能够限制最大连接数,避免因过多的数据库连接导致数据库过载。 · 更易管理:通过连接池,可以统一管理连接的生命周期,简化代码结构。
五:事务管理
事务是由多个SQL语句组成的一个工作单元。事务保证了数据的原子性,即所有操作要么都成功,么都失败。
1:开始事务
事务可以通过START TRANSACTION来显式开启,但一般我们通过执行SQL语句来启动事务。
cursor.execute("START TRANSACTION")2:提交事务
如果事务中的所有操作都成功,我们使用commit()方法提交事务,保存对数据库的更改。
db.commit()3:回滚事务
如果事务中的某些操作失败,我们可以使用rollback()方法回滚事务,将所有更改撤销。
db.rollback()4:事务的隔离级别
MySQL支持四种事务隔离级别,它们定义了在并发事务执行时一个事务的操作对于其他事务的响。隔离级别的设置越高,事务间的干扰越小,但同时可能导致性能下降。MySQL的默认隔离级别是
REPEATABLE READ,具体如下：
(1)READ UNCOMMITTED(未提交读
>描述:事务可以读取其他事务未提交的数据,可能导致“脏读”(Dirty Read)。这种级别
下,事务间的隔离性最差>应用场景:通常不推荐使用,除非对数据一致性要求不高。 >使用方法:
cursor.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
(2)READ COMMITTED(提交读)
描述:事务只能读取其他事务已经提交的数据,避免了脏读,但仍然可能遇到“不可重复读”
(Non-repeatable Read)的问题。即事务中读取的数据在两次读取时可能发生变化(另
一个事务已提交了修改)>应用场景:适用于大多数常见场景,提供了一定的隔离性,同时保证了较好的性能 >使用方法:
cursor.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")(3)REPEATABLE READ(可重复读)
>描述:事务可以保证在事务内多次读取同一数据时,其值不会发生变化。这避免了“不可重
复读”的问题,但依然可能会出现“幻读”(Phantom Read),即一个事务读取的数据集
在事务执行过程中发生了变化。>应用场景:对于需要保证事务数据一致性的场景,例如金融系统中的余额操作等,可以考虑
使用该隔离级别。▶使用方法:
cursor. execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")(4)SERIALIZABLE(串行化)
>描述:这是最严格的事务隔离级别,事务会被执行得像串行一样,完全避免了脏读、不可重
复读和幻读。然而,这种隔离级别的性能开销最大,可能导致大量的锁竞争。>应用场景:适用于对数据一致性要求极高的场景,如库存管理系统、银行转账等。 >使用方法:
cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")5:事务隔离级别总结
READ UNCOMMITTED:允许脏读,最低的隔离级别,性能最好,但容易出现数据不一致的情况。 > READ COMMITTED:解决了脏读问题,但可能出现不可重复读 >REPEATABLE READ:解决了脏读和不可重复读问题,但可能出现幻读。 SERIALIZABLE:解决了所有问题,但性能最差,可能导致事务长时间等待。
在选择事务隔离级别时,需要根据应用的具体需求平衡数据一致性和性能。如果事务数据不频繁突,可以选择较低的隔离级别以提升性能;而对于数据一致性要求极高的场景,则应选择更高的隔离级别尽管这会带来性能上的损失。
在MySQL中,事务的默认隔离级别是 REPEATABLE READ
6.案例
conn = pymysq1.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 use
rs WHERE name = 'Eve'), 120.50)")
conn.commit()
print("事务已提交")
except pymysql.MySQLError as err:
print(f"错误:{err}")
conn.rollback()
print(“事务已回滚。”)
finally:
cursor.close()
conn.close()