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

2022-09-26 16:24:16 浏览数 (1)

持续创作，加速成长！这是我参与「掘金日新计划 · 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对象的属性和方法如下：

代码语言：javascript复制
使用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进行安装。

代码语言：javascript复制
python3 -m pip install mysql-connector -i https://pypi.tuna.tsinghua.edu.cn/simple

也可以通过MySQL 官方网站下载安装包进行安装

创建连接

创建MySQL连接有两种方式，第一种是直接传入数据连接信息来创建连接。

代码语言：javascript复制
import mysql.connector

conn = mysql.connector.connect(
    host='localhost', port=3306, user='root',
    password='123456', database='test'
)
# 打印连接
print(conn)
# 关闭连接
conn.close()

连接无异常。

第二种方式是传入一个配置，配置中包含了数据库连接信息。

代码语言：javascript复制
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语句，在上述脚本尾部中增加代码

代码语言：javascript复制
# 使用游标执行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函数可以到达相同的效果，以实现实现批量插入功能为例。

代码语言：javascript复制
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 注入

代码语言：javascript复制
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格式化操作符。

代码语言：javascript复制
# 根据条件查询
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 异常处理

代码语言：javascript复制
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代码中增加判断，执行回滚。

代码语言：javascript复制
except Exception as e:
    if 'conn' in dir():
        conn.rollback()
    print(e)

数据库连接池

数据库连接是一种关键的、有限的、昂贵的资源，在高并发执行时表现得尤为突出，建立以及释放连接还需要经过三次握手、四次挥手而且还需要校验数据库连接信息，这会导致一定的资源开销，而数据库连接池会预先创建出一些数据库连接，缓存起来，避免程序反复创建和关闭连接。

数据库连接池通过mysql.connector.pooling包下的MySQLConnectionPool函数创建，需要传入数据库连接信息以及要创建连接的个数。

代码语言：javascript复制
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()。

代码语言：javascript复制
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删除， 不依赖事务。

代码语言：javascript复制
truncate_sql = 'TRUNCATE TABLE porsche'
# 执行sql
# cursor.execute(delete_sql, (por_name, por_price))
cursor.execute(delete_sql)

执行TRUNCATE SQL 删除。cursor的execute函数只能执行一条SQL语句。

数据库 sql 云数据库SQLServer api jdbc

0 人点赞