Python与数据库[1] -> 数据库接口/DB-API[0] -> 通用标准
数据库接口 / DB-API
在Python中，数据库是通过适配器(Adaptor)来连接访问数据库的，适配器通常与数据库客户端接口(通常为C语言编写)想连接，而不同的适配器都会尽量满足相同的DB-API标准。
为了保证不同数据库的数据库接口能够通用于适配器，以减少使用不同数据库接口是需要对代码进行大幅改动，Python的DB-SIG(数据库特殊兴趣小组)制定了DB-API的标准，该API为不同的关系数据库提供了一致的接口，使得使用不同数据库的代码间移植变得更加简单。数据库接口目前的版本为DB-API(2.0)，可前往Python官网查看最新版本。
通用标准 / General Standard
对于任意的数据库适配器db，需要提供至少以下几项通用标准，以保证可移植性。
1 属性 / Attribute
1.1 apilevel属性
属性调用: al = db.apilevel
属性功能: 返回当前适配器支持的DB-API版本
属性参数: al
al: str类型，目前支持"1.0"和"2.0"两种，如果该适配器未指定，则假定为"1.0"
1.2 threadsafety属性
属性调用: tsf = db.threadsafety
属性功能: 返回当前适配器支持的线程安全等级
属性参数: tsf
tsf: int类型，线程安全等级
|
threadsafety |
Meaning |
|
0 |
Threads may not share the module. |
|
1 |
Threads may share the module, but not connections. |
|
2 |
Threads may share the module and connections. |
|
3 |
Threads may share the module, connections and cursors. |
1.3 paramstyle属性
属性调用: psy = db.paramstyle
属性功能: 返回当前适配器支持的参数风格
属性参数: psy
psy: str类型，参数风格的说明
|
paramstyle |
Meaning |
|
qmark |
Question mark style, e.g. ...WHERE name=? |
|
numeric |
Numeric, positional style, e.g. ...WHERE name=:1 |
|
named |
Named style, e.g. ...WHERE name=:name |
|
format |
ANSI C printf format codes, e.g. ...WHERE name=%s |
|
pyformat |
Python extended format codes, e.g. ...WHERE name=%(name)s |
2 函数 / Function
2.1 connect ()函数
函数调用: cnx = db.connect/Connection/Connect(*args, **kwargs)
函数功能:生成一个数据库的连接对象
传入参数: args, kwargs
host: str类型，连接的主机地址，未传入或‘localhost’都将连接本地数据库
user: str类型，登入数据库的用户名
password/passwd: str类型，登录数据库的密码
database/db: str类型，使用的数据库schema名称
返回参数: cnx
cnx: obj类型，一个数据库连接实例
3 类 / Class
3.1 Connection类
类实例化：cnx = db.connect/Connection/Connect(*args, **kwargs)
类的功能:用于联系数据库API的连接对象
传入参数: args, kwargs
host: str类型，连接的主机地址，未传入或‘localhost’都将连接本地数据库
user: str类型，登入数据库的用户名
password/passwd: str类型，登录数据库的密码
database/db: str类型，使用的数据库schema名称
返回参数: cnx
cnx: obj类型，一个数据库连接实例
3.1.1 close()方法
函数调用: cnx.close()
函数功能:关闭数据库连接
传入参数: 无
返回参数: 无
3.1.2 commit()方法
函数调用: cnx.commit()
函数功能:提交当前事务，通常当命令具有执行修改等性质的时候需要此函数完成确认
传入参数: 无
返回参数: 无
3.1.3 rollback()方法
函数调用: cnx.rollback()
函数功能:取消当前事务
传入参数: 无
返回参数: 无
3.1.4 cursor()方法
函数调用: cur = cnx.cursor()
函数功能:使用该连接对象创建返回一个游标对象
传入参数: 无
返回参数: cur
cur: obj类型，游标对象的实例，可以通过游标进行操作数据库
3.2 Cursor类
类实例化：cur = cnx.cursor()
类的功能:一个用于操作数据库的游标对象
传入参数: args, kwargs
host: str类型，连接的主机地址，未传入或‘localhost’都将连接本地数据库
user: str类型，登入数据库的用户名
password/passwd: str类型，登录数据库的密码
database/db: str类型，使用的数据库schema名称
返回参数: cnx
cnx: obj类型，一个数据库连接实例
3.2.1 arraysize属性
属性调用: as = cur.arraysize
属性功能: 表示使用fetchmany()方法时，一次性取出的结果行数，默认为1
属性参数: as
as: int类型，一次性fetch的行数
3.2.2 connection属性
属性调用: cnx = cur.connection
属性功能: 返回生成该游标的connection实例
属性参数: cnx
cnx: obj类型，生成当前游标的连接实例
3.2.3 description属性
属性调用: dsp = cur.description
属性功能: 返回当前游标的活动状态
属性参数: dsp
dsp: tuple/NoneType类型，一个7元素元组描述当前状态
3.2.4 lastrowid属性
属性调用: rid = cur.lastrowid
属性功能: 上次修改行的ID，不支持则返回None
属性参数: rid
rid: int/NoneType类型，修改的行ID
3.2.5 rowcount属性
属性调用: roc = cur.rowcount
属性功能: 上次execute*()修改行所影响的行数
属性参数: roc
roc: int类型，修改影响的行数
3.2.6 close()方法
函数调用: cur.close()
函数功能:关闭游标
传入参数: 无
返回参数: 无
3.2.7 execute()方法
函数调用: [r =] cur.execute(query, args=None)
函数功能:执行数据库命令
传入参数: query, args
query: str类型，执行的SQL命令行
args: sequence/mapping类型，query使用的参数
返回参数: r
r: int类型，执行所影响的行数
3.2.8 executemany()方法
函数调用: [r =] cur.executemany(query, args)
函数功能:执行多条数据库命令，类似execute()和map()的结合
传入参数: query, args
query: str类型，执行的SQL命令行
args: sequence/mapping类型，query使用的参数
返回参数: r
r: int类型，执行所影响的行数
3.2.9 fetchone()方法
函数调用: r = cur.fetchone()
函数功能:获取查询结果的下一行
传入参数: 无
返回参数: r
r: tuple类型，查询结果的下一条信息的元组
3.2.10 fetchmany()方法
函数调用: r = cur.fetchmany(size=cursor.arraysize)
函数功能:获取查询结果的下n行，默认为arraysize的设置值
传入参数: size
size: int类型，查询下n行
返回参数: r
r: tuple类型，查询结果的下n条信息的元组
3.2.11 fetchall()方法
函数调用: r = cur.fetchall()
函数功能:获取查询结果的所有(剩余)行
传入参数: 无
返回参数: r
r: tuple类型，查询结果的所有剩余信息元组
4 补充知识 / Complement Knowledge
4.1 Cursor与Connection
在MySQL中，Connection生成的实例利用query函数已经可以完成基本的SQL数据库操作，而Cursor的execute功能与其类似，但是引入Cursor可以使在不必要的时候销毁Cursor节省资源占用，同时无需断开连接。参考 stack overflow 链接。
5 应用实例 / Complement Knowledge
下面的例子中将构建一个简单的通用适配器驱动，其中实现了各种适配器通用的函数方法。
1 class SqlConnector(): 2 def __init__(self, adaptor): 3 self.adaptor = adaptor 4 self.result = None 5 # Check Adaptor info 6 print('Adaptor %s apply DB-API %s, thread safety: %d, parameter style: %s' % (adaptor.__name__, adaptor.apilevel, adaptor.threadsafety, adaptor.paramstyle)) 7 8 # Login by user name and password 9 def login(self, **kwargs): 10 # Create a connection obj 11 self.cnx = self.adaptor.connect(**kwargs) 12 13 def logoff(self): 14 self.cnx.close() 15 16 def query_sql(self, sql, show=True): 17 # Method one: Use Connection 18 ''' 19 self.cnx.query(sql) 20 self.result = self.cnx.store_result() 21 r = self.result.fetch_row(0, ) 22 self.cnx.commit() 23 ''' 24 # Method two: Use Cursor 25 cur = self.cnx.cursor() 26 cur.execute(sql) 27 r = cur.fetchall() 28 self.cnx.commit() 29 cur.close() 30 31 if show: 32 splt = '\n' + ((len(sql)+6)*'-') 33 msg = ''.join(('\n{: ^10} | ').format(str(i)) if x.index(i) == 0 else ('{: ^10} | ').format(str(i)) for x in r for i in x) 34 s = ('{:-^%d}'%(len(sql)+6)).format(sql) + msg + splt 35 print(s) 36 return (i for x in r for i in x) 37 38 def exec_sql(self, sql): 39 cur = self.cnx.cursor() 40 cur.execute(sql) 41 self.cnx.commit() 42 cur.close() 43 44 def commit(self): 45 self.cnx.commit() 46 47 if __name__ == '__main__': 48 import MySQLdb 49 import pymssql 50 r = input('Please choose your adaptor:\n(M)MySQL\n(S)MsSQL\n') 51 adaptor_list = {'M': MySQLdb, 'S': pymssql} 52 c = SqlConnector(adaptor_list[r.upper()]) 53 # No local MSSQL server 54 c.login(host='localhost', user='root', password='root') 55 c.logoff()
第 1-6 行，建立适配器的类，在初始化用利用适配器的通用属性查看适配器的基本信息，包括适配器名称，使用的DB-API等级，线程安全等级，参数风格等。
第 8-14 行，定义登录退出函数，登录时创建连接实例，退出时关闭。
第 16-36 行，定义请求函数，对于数据库的操作有两种方式实现，第一种是使用Connection，第二种是使用Cursor，同时对请求的结果进行显示。
第 38-45 行，定义执行函数，用于执行SQL的命令语句，对于有修改性质的语句必须commit后才能在数据库端生效。
第 47-55 行，最后仅对数据库进行登录退出测试，由于本地仅安装了MYSQL，没有安装MSSQL的服务器，因此无法登录MSSQL。
运行结果如下
Please choose your adaptor:
(M)MySQL
(S)MsSQL
M
Adaptor MySQLdb apply DB-API 2.0, thread safety: 1, parameter style: format
参考链接
