Python DB-API 规范及 MySQL Connector/Python 实现

RiemannHypothesis

发布于 2022-09-26 16:24:16

7650

发布于 2022-09-26 16:24:16

文章被收录于专栏：Elixir

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第2天，点击查看活动详情

一、持久化存储与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对象的属性和方法如下：

使用fetchmany()或获取多行结果时，指定获取的行数，默认为1

三、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语句。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-06-01，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度