Python数据库编程之关系数据库API规范

Python关系数据库API规范

对于关系数据库的访问，Python社区已经制定出一个标准，称为Python Database API Specification。Mysql，Oracal等特定数据库模块遵从这一规范，而且可以添加更多特性。

高级数据库API定义了一组用于连接数据库服务器、执行SQL查询并获得结果的函数和对象。其中有两个主要对象：

• 用于管理数据库连接的Connection对象
• 用于执行查询的Cursor对象

一、连接(Connection)

为了连接到数据库，每个数据库模块都提供了一个模块级函数connect(parameters)。其中实际使用的参数因为数据库不同而可能不同，但是通常都包含数据源名称、用户名、密码、主机名称和数据库名称等信息。通常情况下，这些信息分别通过关键字参数dsn/user/password/host/database提供。所以可以这样调用：

connect(dsn="hostname:DBNAME", user="xxx", password="xxx")
1

如果成功，返回Connection对象。Connection对象的实例c有如下方法：

• c.close()
  关闭与服务器的连接

• c.commit()
  将所有未完成的事务提交到数据库。如果数据库支持事务处理，那么要使任何变更生效都必须调用这一方法。如果底层数据库不支持事务处理，这一方法没有任何作用。

• c.rollback()
  将数据库回滚到未完成事务的开始状态。有时，这一方法可用于不支持事务处理的数据库，以撤销对数据库作出的更改。例如，如果在更新数据库的过程中代码发生异常，可以使用这个方法来在异常出现之前撤销对数据库做出的更改。

• c.cursor()
  创建一个使用连接的新的Cursor对象。cursor是一个对象，你可以使用它执行SQL查询并获得结果。

二、游标(Cursor)

为了在数据库上执行操作，首先必须创建一个连接c，然后调用c.cursor()方法创建Cursor对象。Cursor的实例cur有一些用于执行查询的标准方法和属性。

• cur.callproc(procname [, parameters])
  调用一个名为procname的存储过程，parameters是一个序列的值，用作该存储过程的参数。函数的结果也是一个序列，项数与parameters相同。它本来是parameters的副本，函数执行后，每一个输出参数的值都被修改值所替换。如果该过程还生成一组输出，可以使用fetch*()方法来读取。

• cur.close()
  关闭游标，防止再对其进行操作。

• cur.execute(query, [, parameters])
  在数据库上执行查询或query命令。query是一个包含命令（通常是SQL语句）的字符串，parameters是一个序列或映射，用于为查询字符串中的变量赋值。

• cur.executemany(query, [, parameters])
  重复执行查询或命令。query是一个查询字符串，parameters是一个参数序列。这一序列的每一项都是一个序列或映射对象，他们可以使用上面提到的execute()方法得到。

• cur.fetchone()
  返回有execute()或executemany()生成的下一行结果集。这一结果通常是列表或元组，包含结果中的不同列的值。如果没有更多的行，返回None。如果没有未处理完的结果或者如果以前执行的操作没有生成结果集，就会提示异常。

• cur.fetchmany([size])
  返回结果行的序列（例如，元组列表）。size是要返回的行数。如果省略，cur.arraysize的值就会作为默认值使用。实际返回的行数可能比请求的少，如果没有更多的行，就会返回空的序列。

• cur.fetchall()
  返回全部剩余结果行的序列。（例如，元组列表）

• cur.nextset()
  放弃当前结果集中的所有剩余行，跳至下一个结果集（如果有的话）。如果没有更多的结果集，返回None，否则返回True，接着通过fetch*()操作从新的集合中返回数据。

• cur.setinputsize(sizes)
  给游标一个提示，说明要在接下来的execute*()方法中传递的参数。sizes是一个剪短描述类型对象的序列或者是代表每一参数预期最高字符串长度的整数序列。在内部，这用于预定义内存缓冲区，来创建发送至数据库的查询和命令。使用这个方法能够加速接下来的execute*()操作。

• cur.setoutputsize(size [, column])
  为特定的列设定缓冲区容量。column是结果行的整数索引，而size是字节的数目。这种方法通常用于在调用execute*()之前，在一个大型的数据库列（如字符串、BLOB和LONG）上设定上限。如果column省略，则为结果中的所有列设定的上限。

游标有一系列属性，用来描述当前的结果集，并提供关于游标本身的信息。

• cur.arraysize
  为fetchmany()操作提供默认的一个整数值。该值因数据库模块的不同而有所不同，可将其初始值设置为在本模块中“最佳”的值。

• cur.description
  提供当前结果集中的每一列的信息的一个元组序列。每一个元组都包括（name, type_code, display_size, internal_size, precision, scale, null_ok）。通常需要定义第一个字段并使之与列名称相对应。type_code可以用于涉及类型对象的比较。其他字段如果不适用该列，可以设置为None。

• cur.rowcount
  表示由一种execute*()方法生成的最后结果中的行数。如果设置为-1，意味着既没有结果集，行数也不能确定。

虽然没有在规范中要求，但大多数数据库模块中的Cursor对象也执行迭代协议。在这种情况下，像for row in cur:这样的语句将迭代由最后一次execute*()方法生成的结果集的行。

下面是一个简单的例子，演示了如何通过sqlite3数据库模块使用这些操作，sqlite3数据库模块是一个内置库。

import sqlite3

connection = sqlite3.connect("dbfile")
cur = connection.cursor()

# 简单的查询示例
cur.execute('select name, age, sex from students where age < 20')

# 循环查询结果
while True:
    row = cur.fetchone()
    if not row:
        break
    # 处理每行的结果
    name, age, sex = row
    print(f'name:{name}, age:{age}, sex:{sex}')
    
# 一种替代方法，使用迭代
cur.execute('select name, age, sex from students where age > 20')
for name, age, sex in cur:
    # 处理每行的结果
    print(f'name:{name}, age:{age}, sex:{sex}')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

三、生成查询

使用数据库API的关键是生成SQL查询字符串，以便将其传递到execute*()方法中。问题是，你需要根据用户提供的参数填充查询字符串的各个部分。例如：

my_name = 'hubing'
my_account = 123456

cur.execute(f"select shares from bank where name = {my_name} and account = {my_account}")
1
2
3
4

虽然这能够工作，但也绝不能这样使用python字符串操作手动生成查询。如果这样做，代码将可能受到SQL注入的攻击。它是一个漏洞，攻击者可以利用它在数据库服务器上随意执行语句。例如，在以前的代码中，有些人可能为my_name应用了类似于“EVEL LAUGH; drop table bank; --"的值，而这可能得到出乎意料的结果。

所有的数据库模块在值替换上都有各自的机制。例如，下面这段代码，与生成整个查询不同，你可能会这样做：

my_name = 'hubing'
my_account = 123456
cur.execute("select shares from bank where name = ? and account = ?", (my_name, my_account))
1
2
3

这里，占位符 ？ 后来被元组 (my_name, my_account) 中的值替换。

然而，在整个数据库模块实现中没有关于占位符的标准规则。但是，每一个模块都定义了一个变量paramstyle, 它描述了将在查询中使用的值替换格式。这一变量可能的值如下：

四、类型对象

当使用数据库时，内置类型（例如整数和字符串）通常以相应类型映射到数据库中。然而，对于日期、二进制数据和其他特殊类型，数据管理变得棘手。为了辅助这一映射，数据库模块实现了一组构造函数，用于创建各种类型的对象。

• Date(year, month, day)
  创建表示日期的对象。

• Time(hour, minute, second)
  创建表示时间的对象

• Timestamp(year, month, day, hour, minute, second)
  创建表示时间戳的对象

• DateFromTicks(ticks)
  根据系统时间创建日期对象。ticks是秒数，就是函数time.time()返回的一样

• TimeFromTicks(ticks)
  根据系统时间创建时间对象。

• TimestampFromTicks(ticks)
  根据系统时间创建时间戳对象。

• Binary(s)
  根据字节字符串s创建二进制对象。

除了上述构造函数外，可以定义如下类型的对象。这些代码的目的是对type_code的cur.description字段执行类型检查。该字段描述了当前结果集的内容。

五、处理错误

数据库模块定义了一个高级异常Error，作为所有其他错误的基类。下列的异常是更加具体的数据库错误。

模块也可以定义一个Warning异常，由数据库模块使用，就更新过程中出现的数据截断等事件发出警告。

六、多线程

如果将数据库访问与多线程混合，底层的数据库模块可能是线程安全的，也可能不安全。下列变量已在每一个模块中定义，以提供更多的信息。

• threadsafety

这是一个说明模块线程安全性的整数。其可能的值是：

• 0， 没有线程安全。线程不能共享模块的任何部分
• 1， 该模块是线程安全的。但是连接是不能共享的
• 2， 模块和连接都是线程安全的，但是游标是不能共享的
• 3， 模块、连接和游标都是线程安全的

七、将结果映射到字典中

关于数据库结果的共同问题是：要将元组或列表映射到命名字段字典中。例如，如果查询的结果集包含大量的列，使用描述性字段名称来利用数据就更加容易，而不用通过硬编码元组中特定字段的数字索引来实现。

有许多方式实现这一点，但是处理结果数据最好的方式是使用生成器函数，例如：

def generate_dicts(cur):
    import itertools
    fieldnames = [ d[0].lower() for d in cur.description ]
    while True:
        rows = cur.fetchmany()
        if not rows:
            return
        for row in rows:
            yield dict(itertools.izip(fieldnames, row))

# 使用样例
cur.execute("select name, shares, price from testtable")
for r in generate_dicts(cur):
    print(r['name'])
    print(r['shares'])
    print(r['price'])
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意， 列的名称在不同的数据库中并不完全一致，特别是否区分大小写之类的事情。所以，在使用这一方法来编码时必须小心，因为这意味着与多个不同的数据库模块一起使用。

小手一抖，点个赞再走哦