3.python操作mysql数据库

前言：在现代应用程序中，数据库扮演者至关重要的角色。mysql是一个流行的关系型数据库管理系统，广泛应用于各种规模的应用中。在pytho中，我们可以通过连接库与mysql数据库进行交互，实现数据的增删改查操作。与此同时，为了提高应有的性能，连接池作为一个优化数据库连接管理技术，得到了广泛的应用

一：安装Python MySQL连接库

用于与mysql数据库进行交互

1：安装mysql-connector-python

pip install mysql-connector-python

2：安装pymysql（作为代替）

pip install pymysql

二：python连接mysql数据库

vim aaa.py

import pymysql                                                                   /1

db = pymysql.connect(host="192.168.10.101",user="root",password="pwd123",database="testdb")  /2

cursor = db.cursor()                                                             /3

cursor.execute("select * from users")                                  /4

results = cursor.fetchall()
for row in results:
         print(row)                                                                       /5

cursor.close()
db.close()                                                                                /6
以上五个部分解释：

1：导入数据库，需要导入pymysql模块，使用它来连接mysql数据库并执行sql语句

2：创建数据库连接，pymysql.connect()方法来建立数据库连接。连接时需要提供mysql服务器的地址、用户名、密码和我们要访问的数据库名

3：创建游标对象，建立连接之后，需要创建一个游标对象，通过它来执行sql语句

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

5：获取查询结果，对于查询操作，fetchall()方法用于获取所有结果，fetchone()方法用于获取单条记录

6：关闭连接：操作完成后，需要关闭游标和数据库连接

三：常见的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 user (name,age) values (%s,%s)",[ ("Bob",30),("charlie",35)] )

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":"pwd123",

        "database":"testdb"

        }                                                     /数据库连接配置

connection_pool=PooledDB(

        creator=pymysql,                           /使用pymysql作为数据库连接库

        maxconnections=5,                       /连接池中最大连接数

        **dbconfig

)

3：获取连接

从连接池中获取连接时，可以使用connection()方法。每次获取到的连接都可以直接执行数据库操作

db_connection=connection_pool.connection()

cursor=db_connection.cursor()

cursor.exesute("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:解决了所有问题，但性能最差，可能导致事务长时间等待。