Python 操作 MySQL 数据库

db= pymysql.connect(
    host="localhost",        # MySOL数据库的地址，通常是1ocalhost或IP地址
    user="root",             #数据库的用户名，通常是 root
    password="password",        # 数据库的密码
    database-"testdb")        #要连接的数据库名称

3. 创建游标对象

建立连接后，我们需要创建一个游标对象，通过它来执行SQL语句。

cursor=db.cursor()

4.俗行 SQL语句

通过游标对象的execute()方法,我们可以执行 SQL语句。在执行 SQL时,可以使用%s 占位符来避免 SQL注入攻击。

cursor.execute("SELECT* FROM users")

5. 获取查询结果

对于查询操作，fetcha11()方法用于获取所有结果，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语句。你可以使用 fetcha11()获取所有记录，或使用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 进行联合查询

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

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. 创建连接池

PyySQL 并不直接支持连接池，但我们可以使用 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=pymysql,    #使用 PyMySQL作为数据库连接库
    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. 回滚事务

如果事务中的某些操作失败，我们可以使用ro11back()方法回滚事务，将所有更改撤销。

db.rollback()

4. 事务的隔离级别

MySQL支持四种事务隔离级别，它们定义了在并发事务执行时一个事务的操作对于其他事务的影响。隔离级别的设置越高，事务间的干扰越小，但同时可能导致性能下降。ySQL的默认隔离级别是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