第13章 数据库支持

13.1 Python数据库API

为了解决不同数据库模块的接口（API）不同的问题，指定了标准数据库API（DB API）

1. 全局变量

所有与DB API2.0兼容的数据库模块必须包含3个全局变量：

apilevel：使用的Python API版本，是一个字符串常量。DB API 2.0指出，这个变量的值为'1.0'或'2.0'。如果没有这个变量，就说明模块不与DB API 2.0兼容，应假定使用的是DB API 1.0。编写代码时，允许这个变量为其他值也没有害处，因为说不定什么时候B API 3.0就出来了。

threadsafety：线程安全程度，是一个0~3（含）的整数。 0表示线程不能共享模块；1表示线程可共享模块本身，但不能共享连接；2表示线程可共享模块和连接，但不能共享游标；3表示模块是绝对线程安全的。

paramstyle：参数风格表示当你执行多个类似的数据库查询时，如何在SQL查询中插入参数。 '例如format'表示标准字符串格式设置方式（使用基本的格式编码），如在要插入参数的地方插入%s。

2. 异常

Python DB API指定的异常

异常	超类	描述
StandardError		所有异常的超类
Warning	StandardError	发生非致命问题时引发
Error	StandardError	所有错误条件的超类
InterfaceError	Error	与接口（而不是数据库）相关的错误
DatabaseError	Error	与数据库相关的错误的超类
DataError	DatabaseError	与数据相关的问题，如值不在合法的范围内
OperationalError	DatabaseError	数据库操作内部的错误
IntegrityError	DatabaseError	关系完整性遭到破坏，如键未通过检查
InternalError	DatabaseError	数据库内部的错误，如游标无效
ProgrammingError	DatabaseError	用户编程错误，如未找到数据库表
NotSupportedError	DatabaseError	请求不支持的功能，如回滚

2. 连接和游标

连接数据库系统，函数connect返回一个连接对象。

函数connect的常用参数

参数名	描述	是否可选
dsn	数据源名称，具体含义随数据库而异	否
user	用户名	是
password	用户密码	是
host	主机名	是
database	数据库名称	是

连接对象的方法

方法名	描述
close()	关闭连接对象。之后，连接对象及其游标将不可用
commit()	提交未提交的事务——如果支持的话；否则什么都不做
rollback()	回滚未提交的事务（可能不可用）
cursor	返回连接的游标对象

游标对象的方法

名称	描述
callproc(name[, params])	使用指定的参数调用指定的数据库过程（可选）
close()	关闭游标。关闭后游标不可用
execute(oper[, params])	执行一个SQL操作——可能指定参数
executemany(oper, pseq)	执行指定的SQL操作多次，每次都序列中的一组参数
fetchone()	以序列的方式取回查询结果中的下一行；如果没有更多的行，就返回None
fetchmany([size])	取回查询结果中的多行，其中参数size的值默认为arraysize
fetchall()	以序列的序列的方式取回余下的所有行
nextset()	跳到下一个结果集，这个方法是可选的
setinputsizes(sizes)	用于为参数预定义内存区域
setoutputsize(size[, col])	为取回大量数据而设置缓冲区长度

游标对象的属性

名称	描述
description	由结果列描述组成的序列（只读）
rowcount	结果包含的行数
arraysize	fetchmany返回的行数，默认为1

4. 类型

---

13.2 SQLite和PySQLite

#导入模块
import sqlite3
#创建连接
conn = sqlite3.connect('somedatabase.db')
#获取游标
curs = conn.cursor()
#提交修改
conn.commit()
#关闭连接
conn.close()

例：创建小型的营养成文数据库

数据来源美国农业部的文件ABBREV.txt，该文件的结构为：每行都是一条数据记录，字段之间用脱字符（ ^）分隔。数字字段直接包含数字，而文本字段用两个波浪字符（ ~）将其字符串值括起。下面是一个示例行（为简洁起见删除了部分内容）：

~07276~^~HORMEL SPAM ... PORK W/ HAM MINCED CND~^ ... ^~1 serving~^^~~^0

将数据导入数据库：

在代码清单13-1中，使用的参数风格为qmark，即使用问号来标记字段。

#importdata.py
import sqlite3

def convert(value):
    if value.startswith('~'):
        return value.strip('~')
    if not value:
        value = '0'
    return float(value)

conn = sqlite3.connect('food.db')
curs = conn.cursor()

curs.execute('''
CREATE TABLE food (
    id         TEXT PRIMARY KEY,
    desc       TEXT,
    water      FLOAT,
    kcal       FLOAT,
    protein    FLOAT,
    fat        FLOAT,
    ash        FLOAT,
    carbs      FLOAT,
    fiber      FLOAT,
    sugar      FLOAT
)
''')

query = 'INSERT INTO food VALUES (?,?,?,?,?,?,?,?,?,?)'
field_count = 10

for line in open('ABBREV.txt'):
    fields = line.split('^')
    vals = [convert(f) for f in fields[:field_count]]
    curs.execute(query, vals)

conn.commit()
conn.close()

搜索并处理结果：

import sqlite3, sys

conn = sqlite3.connect('food.db')
curs = conn.cursor()

query = 'SELECT * FROM food WHERE ' + sys.argv[1]
print(query)
curs.execute(query)
names = [f[0] for f in curs.description]
for row in curs.fetchall():
    for pair in zip(names, row):
        print('{}: {}'.format(*pair))
    print()

使用

 >>>python food_query.py "kcal <= 100 AND fiber >= 10 ORDER BY sugar"