一、持久化存储与PEP 249
持久化存储有3中基础的存储机制：文件、数据库(关系型和非关系型)以及一些混合类型。文件存储不适合大型项目，需要使用数据库存储，MySQL是目前持久化存储中最流行的解决方案。
Python访问关系型数据库有两种方式，一种是通过数据库接口既Python DB-API，另一种方式是通过ORM来访问；DB-API是一套访问数据库的标准或者规范，它可以为不同的数据库适配器和底层数据库系统提供一致性访问，类似Java中的JDBC。DB-API 已移动至 PEP 249 中（PEP 248 中的老版 DB-API 1.0 标准已经废弃）。可以参考官网 PEP 249 – Python Database API Specification v2.0
基于PEP 249 DB-API规范的 MySQL 访问实现，类似Spring Data JPA 以及 Hibernate
- MySQL Connector/Python
- PyMySQL
- MySQL for Python
- mysqlclient
更多实现可以参考官网
二、Python DB-API
全局属性
DB-API规范要求必须提供以下全局属性：
| 属性 |
描述 |
| apilevel | 适配器兼容的DB-API版本，默认为1.0 |
| threadsafety | 线程安全级别，整数值类型 |
| paramstyle | SQL语句参数风格 |
| connect() | Connect()函数 |
线程安全级别属性是一个整数类型，有以下几个选择：
- 0：不支持线程安全，线程间不能共享模块
- 1：最小化的线程安全支持，线程间可以共享模块，但是不能共享连接
- 2：适度的线程安全支持，线程间可以共享模块和连接，但是不能共享游标
- 3：完整的线程安全支持，线程间可以共享模块、连接和游标
connect() 函数 与 Connection 对象
connect()函数可以返回一个Connection对象既一个数据库连接，该函数可以使用办函多个参数的字符串传递数据库连接信息，也可以安装位置传递每个参数，或者通过关键字方式传参
- user：用户名
- password：密码
- host：主机名
- database：数据库名
- dsn：数据源名
具体使用的参数还需要根据适配器的不同而是用不同的参数，如ODBC或者JDBC的API需要用大DSN，而MySQL Connector就不需要使用DSN.
Connection对象是一个具体的数据库连接，可以用于创建游标，使用游标执行SQL语句，Connection对象不包含任何属性，但是包含了以下这些方法:
- close()：关闭数据库连接，关闭之后连接将无法使用
- commit()：提交当前事务
- rollback()：取消当前事务
- cursor()：创建并返回一个游标对象
- errorhandler():给定连接的游标的处理程序
Cursor
当建立好连接之后就可以和数据库进行通信了，游标可以让用户提交数据库命令，并获取的执行结果，DB-API规范中定义了游标的功能，基于DB-API规范实现的适配器都是实现游标的功能 ，以此来保证访问不同数据库时的一致性。
游标可以执行查询或者其他命令，可以通过execute和executemany执行一条或者多条命令，并支持从结果集中取出一行或者多行结果。
Cursor对象的属性和方法如下：
| 对象属性或者方法 |
描述 |
| arraysize | 使用fetchmany()或获取多行结果时，指定获取的行数，默认为1 |
| connection | 创建游标使用的连接 |
| description | 返回游标的活动状态 |
lastrowid |
上次修改行的id |
rowcount |
执行SQL影响的行数 |
| rownumber | 当前结果集中游标的索引 |
| messages | 游标执行后从数据库中获得的消息列表 |
| callproc() | 调用存储过程 |
close() |
关闭游标 |
execute() |
执行一条数据库命名 |
| executemany | 执行多条命令，类似execute()和map()的组合 |
fetchone() |
获取一行数据 |
fetchmany() |
获取多行数据，可以指定行数 |
fetchall() |
获取全部数据 |
| __iter__() | 为游标创建迭代器 |
| next() | 游标迭代器查询下一行数据 |
| nextset() | 移动到下一个结果集合 |
| fetchmany() | 获取多行数据，可以指定行数 |
| setinputsize() | 设置允许的最大输入大小，可选 |
| setoutputsize() | 设置列获取的最大缓冲区的大小 |
三、MySQL Connector/Python
安装MySQL Connector
MySQL Connector是MySQL官方提供的适配器，基于PEP249规范。
- mplements the Python DB API 2.0 (PEP 249).
- Pure Python implementation of the MySQL protocol.
- Actively developed and maintained by Oracle.
- Includes Django database backend.
MySQL Connector可以通过pip进行安装。
python3 -m pip install mysql-connector -i https://pypi.tuna.tsinghua.edu.cn/simple 复制代码
也可以通过MySQL 官方网站下载安装包进行安装
创建连接
创建MySQL连接有两种方式，第一种是直接传入数据连接信息来创建连接。
import mysql.connector conn = mysql.connector.connect( host='localhost', port=3306, user='root', password='123456', database='test' ) # 打印连接 print(conn) # 关闭连接 conn.close() 复制代码
连接无异常。
第二种方式是传入一个配置，配置中包含了数据库连接信息。
import mysql.connector import mysql.connector.connection mysql_connect_config = { 'host': 'rm-uf67r962043910k193o.mysql.rds.aliyuncs.com', 'port': 3306, 'user': 'root', 'password': 'Abc*123456', 'database': 'test' } conn = mysql.connector.connect( **mysql_connect_config ) print(conn) # 关闭连接 conn.close() 复制代码
通过游标来执行SQL语句，在上述脚本尾部中增加代码
# 使用游标执行SQL cursor = conn.cursor() sql = "SELECT * FROM porsche" cursor.execute(sql) print(cursor) print(type(cursor)) print(isinstance(cursor, list)) for i in cursor: print(i) print(type(i)) print(i[0], i[1], i[2]) # 关闭连接 conn.close() 复制代码
游标中存储了一行行数据，这些数据以元组类型存储，通过索引可以获取指定列的元素
executemany()
当需要执行批量操作时，for循环执行SQL和executemany函数可以到达相同的效果，以实现实现批量插入功能为例。
import mysql.connector mysql_connect_config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'password': 'Abc*123456', 'database': 'test' } conn = mysql.connector.connect( **mysql_connect_config ) try: # 开启事务 conn.start_transaction() # 使用游标执行SQL cursor = conn.cursor() sql = 'INSERT INTO porsche (por_name, por_price, por_stock) VALUES (%s, %s, %s)' data = [['Taycan 2020', 1200000.00, 100], ['Taycan 2021', 1200000.00, 100], ['Taycan 2022', 1200000.00, 100]] cursor.executemany(sql, data) conn.commit() except Exception as e: if 'conn' in dir(): conn.rollback() print(e) 复制代码
SQL执行无异常，并且成功插入到数据库中。
SQL 注入
import mysql.connector mysql_connect_config = { 'host': 'rm-uf67r962043910k193o.mysql.rds.aliyuncs.com', 'port': 3306, 'user': 'root', 'password': 'Abc*123456', 'database': 'test' } conn = mysql.connector.connect( **mysql_connect_config ) # 根据条件查询 id = '1 OR 1=1' sql = 'SELECT * FROM porsche WHERE por_id=' + id # 获取游标 cursor = conn.cursor() cursor.execute(sql) print(type(cursor)) for i in cursor: print(i) # 关闭连接 conn.close() 复制代码
正常通过WHERE por_id=?条件应该只能查出一个数据，但是通过拼接加入OR True可以使where条件失效，进而查询到所有的数据，因此使用拼接的方式传递参数非常容易出现SQL注入漏洞。
预编译SQL
预编译SQL就是数据库提前把SQL语句编译成二级制，这样反复执行同一条SQL语句的效率就会提升。
预编译过程中，关键字会被解析，向编译后的SQL语句传入参数，都会被当做字符串串处理，数据库不会解析其中注入的SQL语句。
注意预编译传参方式，虽然预编译使用%s进行占位，但是传参的时候一定更不要通过%(参数1，参数2)方式传参，要区分预编译占位符和Python格式化操作符。
# 根据条件查询 id = '2 OR 1=1' # sql = 'SELECT * FROM porsche WHERE por_id=' + id sql = 'SELECT * FROM porsche WHERE por_id=%s' # 获取游标 cursor = conn.cursor() cursor.execute(sql, (id,)) # cursor.execute(sql) # print(type(cursor)) for i in cursor: print(i) # 关闭连接 conn.close() 复制代码
只查出一条符合WHERE条件的数据，避免了SQL注入。
MySQL Connector 异常处理
import mysql.connector try: # 数据库连接 conn = mysql.connector.connect( host='localhost', port=3306, user='root', password='Abc123456', database='test' ) conn.start_transaction() # 数据库操作 insert_sql = 'INSERT INTO porsche(por_id, por_name, por_price, por_stock) ' \ 'VALUES (%s, %s, %s, %s)' cursor = conn.cursor() cursor.execute(insert_sql, (18, 'Taycan 2024', 880000.00, 100)) conn.commit() except Exception as e: # 出现异常回滚 conn.rollback() print(e) finally: # 关闭连接 if 'conn' in dir(): conn.close 复制代码
查看数据库，数据成功插入
模拟一个异常情况，将数据库连接密码改成错误的密码，是否能捕获异常。
在except代码中增加判断，执行回滚。
except Exception as e: if 'conn' in dir(): conn.rollback() print(e) 复制代码
数据库连接池
数据库连接是一种关键的、有限的、昂贵的资源，在高并发执行时表现得尤为突出，建立以及释放连接还需要经过三次握手、四次挥手而且还需要校验数据库连接信息，这会导致一定的资源开销，而数据库连接池会预先创建出一些数据库连接，缓存起来，避免程序反复创建和关闭连接。
数据库连接池通过mysql.connector.pooling包下的MySQLConnectionPool函数创建，需要传入数据库连接信息以及要创建连接的个数。
import mysql.connector.pooling mysql_connect_config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'password': 'Abc*123456', 'database': 'test' } try: pool = mysql.connector.pooling.MySQLConnectionPool(**mysql_connect_config, pool_size=10) # print(pool) # 获取数据库连接 conn = pool.get_connection() # 开启事务 conn.start_transaction() # 使用游标执行SQL cursor = conn.cursor() sql = 'UPDATE porsche SET por_stock=100 WHERE por_id=%s' # 只传递一个参数的情况，传递的参数的集合必须是元组类型 cursor.execute(sql,(3,)) conn.commit() except Exception as e: if 'conn' in dir(): conn.rollback() print(e) 复制代码
MySQL Connector 删除数据的两种方式
MySQL Connector可以通过执行delete语句来执行删除操作，delete语句依赖事务，删除之后要进行commit()。
import mysql.connector.pooling mysql_connect_config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'password': '123456', 'database': 'test' } try: pool = mysql.connector.pooling.MySQLConnectionPool(**mysql_connect_config, pool_size=10) conn = pool.get_connection() # 开启事务 conn.start_transaction() # 游标 cursor = conn.cursor() # delete 删除语句 delete_sql = 'DELETE FROM porsche WHERE por_name=%s AND por_price=%s' por_name = 'Cayman' por_price = 720000 # 执行sql cursor.execute(delete_sql, (por_name, por_price)) conn.commit() except Exception as e: # 回滚 if 'conn' in dir(): conn.rollback print(e) 复制代码
SQL 执行无异常。
第二种删除方式是TRUNCATE删除， 不依赖事务。
truncate_sql = 'TRUNCATE TABLE porsche' # 执行sql # cursor.execute(delete_sql, (por_name, por_price)) cursor.execute(delete_sql) 复制代码
执行TRUNCATE SQL 删除。cursor的execute函数只能执行一条SQL语句。
