【循序渐进学Python】14.数据库的支持

纯文本只能够实现一些简单有限的功能。如果想要实现自动序列化，也可以使用 shelve 模块和 pickle 模块来实现。但是，如果想要自动的实现数据并发访问，以及更标准，更通用的数据库(database)存储方式还是使用数据库。

1. Python 数据库 API

很多支持SQL标准的数据库在Python中都有对应的客户端模块。为了在提供相同功能(基本相同)的不同模块之间进行切换(兼容)，Python 规定了一个标准的 DB API。目前API最新版本时 2.0，具体可以参考这里：http://www.python.org/dev/peps/pep-0249/。

1.1 全局变量

任何支持2.0版本的DB API的数据库模块都定义了3个描述模块特性的全局变量：

apilevel            所使用的Python DB API版本threadsafety        模块的线程安全级别       paramstyle          在SQL查询中使用的参数风格

apilevel 是个字符串常量，提供正在使用API的版本号，对于DB API 2.0 版本来说

• 值可能是 ‘1.0’，
  
• 也可能是 ‘2.0’；

threadsafety 这这只有在使用多线程时才有参考价值，取值范围为0~3的整数。

• 0 表示完全不共享模块
  
• • 1 表示线程本身可以共享模块，但不对连接共享
    
• 3 表示模块完全是线程安全的

paramstyle 表示在执行多次类似查询的时候，参数是如何被拼接到SQL查询中的：

• 'format'表示标准的字符串格式化
  
• 'pyformat'表示扩展的格式代码，使用字典拼接
  
• 'qmark'表示使用问号
  
• 'numberic'表示使用 :1 或 :2 格式的字段
  
• 'named’表示使用 :name 格式的字段

1.2 异常

API中定义了(层次结构)一些异常类，可以通过 except 块捕捉多种异常。如果想要知道更多DB API异常的想想可以参考这里：http://www.python.org/dev/peps/pep-0249/#exceptions，下面是DB API中使用的异常：

===============================================================================异常                       超类               描述                             ===============================================================================StandarError                                所有异常的泛型基类Waring                  StandarError        在非致命错误发生时引发Error                   StandarError        所有错误条件的泛型超类InterfaceError          Error               关于接口而非数据库的错误DatabaseError           Error               与数据库相关的错误的基类 DataError               DatabaseError       与数据库相关的问题，比如值超出范围OperationalError        DatabaseError       数据库内部操作错误IntegrityError          DatabaseError       关系完整性受到影响，比如键检查失败InternalError           DatabaseError       数据库内部错误，比如非法游标PRogramningError        DatabaseError       用户编程错误，比如未找到表NotSupportedError       DatabaseError       请求不支持的特性(比如回滚)

1.3 连接和游标

使用 connect 函数连接到数据库，该函数有多个参数，具体使用方法取决于对应的数据库。下面是具体的参数含义(使用时推荐使用关键字参数的方式)：

==================================================参数名         描述                                 ==================================================dsn         数据库源名称，给出参数表示数据库依赖(必选)  user        用户名(可选)                           passowrd    密码(可选)                                     host        主机名(可选)                                      database    数据库名(可选)                                    

函数返回连接对象，该对象表示目前和数据库的会话，对象支持的方法如下：

=============================================================方法名                 描述                                 =============================================================close()             关闭连接之后，连接对象和它的游标均不可用commit()            如果支持的话就提交挂起事务，否则不可用rollback()          回滚挂起的事务cursor()            返回连接的游标对象

rollback 方法如果使用的数据库支持的话，就会撤销所有未提交的事务。而 cursor 方法则返回一个游标对象，可以通过游标执行SQL查询并检查结果。游标比连接支持更多方法，也更加易用，下面是游标方法的概述：

====================================================================================名称                                     描述                                 ====================================================================================callproc(name[, params])            使用给定的名称和参数(可选)调用以命名的数据库程序close()                             关闭游标后，游标不可用execute(oper[, params])             执行SQL操作，可选的参数executemany(oper, pseq)             对序列中的每个参数执行SQL操作fetchone()                          把查询结果集中的下一行保存为序列或者Nonefetchmany([size])                   获取查询的结果集中的多行，默认尺寸为arraysizefetchall()                          将所有(剩余)的行作为序列的序列nextset()                           跳至下一个可用的结果集(可选)setinputsizes(sizes)                为参数预先定义的内存区域setoutputsize(size[, col])          为获取的大数据库值设定缓冲区尺寸

游标对象还包含一些特性：

==================================================名称                   描述                                 ==================================================description         结果列描述的序列，只读                       rowcount            结果中的行数，只读arraysize           fetchmany中返回的行数，默认为1

1.4 类型

DB API 定义了Python中的类型和数据库类型的对应关系。通过特殊的类型和值的构造函数以及常量(单例模式)，如下(一些模块可能并不是完全按照这些定义)：

==================================================名称                                  描述                                 ==================================================Date(year, month, day)              创建保存日期值的对象Time(hour, minute, second)          创建保存时间值的对象Timestamp(y, mon, d, h, min, s)     创建保存时间戳的对象DateFromTicks(ticks)                创建保存自新纪元以来的秒数的对象TimeFromTicks(ticks)                创建保存来自秒数的时间值的对象TimestampFromTicks(ticks)           创建保存来自秒数的时间戳值的对象Binary(string)                      创建保存二进制字符串值的对象STRING                              描述二进制列(比如LONG或RAW)BINARY                              描述数字列MUMBER                              描述数字列DATETIME                            描述日期/时间列ROWID                               描述行ID列

2. SQLite 和PySQLite

SQLite是一个小型的数据库引擎，它不基于集中式数据库存储机制，而是直接作用于本地文件。在Python 2.5 之后，SQLite 的包装(PySQLite)已经被包含在标准库中了(在最新的Python版本中已经包含了SQLite数据库了，并不需要单独安装)。

2.1 简单示例

将SQLite作为名为sqlite3的模块导入，即可创建一个数据库文件(不存在则创建，可指定路径)，并连接到数据库，下面是一个简单的示例：

# -- coding:utf-8 --import sqlite3conn = sqlite3.connect('myDatabase.db')curs = conn.cursor() # 获得游标conn.commit() # 提交更改conn.close()  # 关闭连接

2.2 创建和填充表

通过下面的示例来学习如果创建和向表中插入数据，在这里使用的数据库是基于USDA营养数据库实验室提供的文本格式的数据库，在这里使用的文件地址是：http://sr23-nutrient-calculator.googlecode.com/svn-history/r9/trunk/ABBREV.txt，示例如下：

import sqlite3def 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 = r'INSERT INTO food VALUES(?,?,?,?,?,?,?,?,?,?)'for line in open('ABBREV.txt'):    fields = line.split('^')    vals = [convert(f) for f in fields[0:10]]    curs.execute(query,vals)conn.commit()conn.close()

2.3 搜索和处理结果

查询数据库内容只需要创建连接，并获得该连接的游标即可，通过 execute 方法执行SQL查询，使用 fetchall 等方法提取结果，如下所示：

# food_query.pyimport sqlite3,sysconn = sqlite3.connect('food.db')curs = conn.cursor()query = 'select * from food WHERE %s' % sys.argv[1]print querycurs.execute(query)names = [f[0] for f in curs.description]for row in curs.fetchall():    for pair in zip(names,row):        print '%s: %s'% pair    print

运行程序，通过命令行传递查询参数：

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

参考资料&进一步阅读

Python Doc —— sqlite3

Python基础教程