21.2　Python数据库应用程序程序员接口（DB-API）

去哪儿找一个合适的接口访问数据库？很简单，去python.org找到数据库主题那一节，你会发现所有支持DB-API 2.0的各种数据库模块、文档、SIG等。从那时起，DB-API被移到PEP 249中（这个PEP废弃了老的DB-API 1.0，也就是PEP248标准）。那么，什么是DB-API?

DB-API是一个规范。它定义了一系列必需的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。像绝大多数社区成果一样，这个API的产生来自于强烈的需求。

在过去，不同的人为各种各样的数据库实现了各种各样的数据库接口程序。同一个轮子被不同的人一遍又一遍地重复发明。这些接口由不同的人在不同的时间实现，功能接口各不兼容，这意味着使用这些接口的程序必须自定义他们选择的接口模块。当这个接口模块变化时，应用程序的代码也必须随之更新。

一个处理Python数据库事务的特殊事物小组（special interest group, SIG）因此诞生，最后DB-API1.0问世。DB-API为不同的数据库提供了一致的访问接口，在不同的数据库之间移植代码成为一件轻松的事情（一般来说，只修要修改几行代码）。接下来你会看到这样的例子。

21.2.1　模块属性

DB-API规范里的以下特性和属性必须提供。一个DB-API兼容的模块必须定义如下，表21.1中定义的所有全局属性。

1.数据属性

（1） apilevel

apilevel这个字符串（不是浮点型）表示这个DB-API模块所兼容的DB-API最高版本号。如“1.0”，“2.0”等。如果未定义，则默认是“1.0”。

（2） Threadsafety

这是一个整型，取值范围如下：

• 0：不支持线程安全，多个线程不能共享此模块

• 1：初级线程安全支持：线程可以共享模块，但不能共享连接

• 2：中级线程安全支持：线程可以共享模块和连接，但不能共享游标

• 3：完全线程安全支持：线程可以共享模块、连接及游标

如果一个资源被共享，就必需使用自旋锁或者是信号量这样的同步原语对其进行原子目标锁定。对这个目标来说，磁盘文件和全局变量都不可靠，并且有可能妨碍mutex（互斥量）的操作。请参阅threading模块或第16章（多线程编程）来了解如何使用锁。

（3） Paramstyle

DB-API支持多种方式的SQL参数风格。这个参数是一个字符串，表明SQL语句中字符串替代的方式。（参阅表21.2）

2.函数属性

connect方法生成一个connect对象，我们通过这个对象来访问数据库。符合标准的模块都会实现connect方法。表21.3列出了connect()函数的参数。

数据库连接参数可以以一个DSN字符串的形式提供，也可以以多个位置相关参数的形式提供（如果你明确知道参数的顺序的话），也可以以关键字参数的形式提供。下面是一个来自PEP 249的使用connect()的例子：

使用DSN字符串还是独立参数？这要看你连接的是哪种数据库。举例来说，如果你使用类似ODBC或JDBC的API，你就应该使用DSN字符串。如果你直接访问数据库，你就会更倾向于使用独立参数。另一个使用独立参数的原因是，很多数据库接口程序还不支持DSN参数。下面是一个非DSN的例子。

connect()调用。注意不是所有的接口程序都是严格按照规范实现的。MySQLdb就使用了db参数而不是规范推荐的database参数来表示要访问的数据库。

3.异常

兼容标准的模块也应该提供这些异常类。见表21.4。表21.4 DB-API异常类

21.2.2　连接对象

要与数据库进行通信，必须先和数据库建立连接。连接对象处理将命令送往服务器，以及从服务器接收数据等基础功能。连接（或一个连接池）成功后你就能够向数据库服务器发送请求，得到响应。

方法

连接对象没有必须定义的数据属性，但是它至少应该定义表21.5中的这些方法。

一旦执行了close()方法，再试图使用连接对象的方法将会导致异常。

对不支持事务的数据库，或者虽然支持事务但设置了自动提交（auto-commit）的数据库系统来说，commit()方法什么也不做。如果你确实需要，可以实现一个自定义方法来关闭自动提交行为。由于DB-API要求必须实现此方法，所以对那些没有事务概念的数据库来说，这个方法只需要有一条pass语句就可以了。

类似commit()、rollback()方法仅对支持事务的数据库有意义。执行完rollback()，数据库将恢复到提交事务前的状态。根据PEP249，在提交commit()之前关闭数据库连接将会自动调用rollback()方法。

对不支持游标的数据库来说，cursor()方法仍然会返回一个尽量模仿游标对象的对象。这是最低要求。特定数据库接口程序的开发者可以任意为他们的接口程序添加额外的属性，只要他们愿意。

DB-API规范建议但不强制接口程序的开发者为所有数据库接口模块编写异常类。如果没有提供异常类，则假定该连接对象会引发一致的模块级异常。一旦你完成了数据库连接，并且关闭了游标对象，你应该执行commit()提交你的操作，然后关闭这个连接。

21.2.3　游标对象

当你建立连接之后，就可以与数据库进行交互。就像我们在前一小节提到的，一个游标允许用户执行数据库命令和得到查询结果。一个Python DB-API游标对象总是扮演游标的角色，无论数据库是否真正支持游标。从这一点讲，数据库接口程序必须实现游标对象。只有这样，才能保证无论使用何种后端数据库你的代码都不需要做任何改变。

创建游标对象之后，你就可以执行查询或其他命令（或者多个查询和多个命令），也可以从结果集中取出一条或多条记录。表21.6列举了游标对象拥有的属性和方法。

游标对象最重要的属性是execute()和fetch()方法。所有对数据库服务器的请求都由它们来完成。对fetchmany()方法来说，设置一个合理的arraysize属性会很有用。当然，在不需要时关掉游标对象也是个好主意。如果你的数据库支持存储过程，你就可以使用callproc()方法。

21.2.4　类型对象和构造器

通常两个不同系统的接口要求的参数类型是不一致的，譬如Python调用C函数时Python对象和C类型之间就需要数据格式的转换，反之亦然。类似地，在Python对象和原生数据库对象之间也是如此。对于Python DB-API的开发者来说，你传递给数据库的参数是字符串形式的，但数据库会根据需要将它转换为多种不同的形式。以确保每次查询能被正确执行。

举例来说，一个Python字符串可能被转换为一个VARCHAR或一个TEXT，或一个BLOB，或一个原生BINARY对象，或一个DATE或TIME对象。一个字符串到底会被转换成什么类型？必须小心地尽可能以数据库期望的数据类型来提供输入，因此另一个DB-API的需求是创建一个构造器以生成特殊的对象，以便能够方便地将Python对象转换为合适的数据库对象。表21.7描述了可以用于此目的的类。SQL的NULL值被映射为Pyhton的NULL对象，也就是None。

DB-API版本变更

有几个重要的变更发生在DB-API从1.0（1996）升级到2.0（1999）时：

• 从API中移除了原来必须的dbi模块；

• 更新了类型对象；

• 增加了新的属性以提供更易用的数据库绑定；

• 变更了callproc()的语义并重定义了execute()的返回值；

• 基于异常的错误处理。

自从DB-API 2.0发布以来，曾经在2002年加入了一些可选的DB-API扩展，但一直没有什么重大的变更。在DB-SIG邮件列表中一直在讨论DB-API的未来版本——暂时命名为DB-API 3.0。它将包括以下特性：

• 当有一个新的结果集时nextset()会有一个更合适的返回值；

• float变更为Decimal;

• 支持更灵活的参数风格；

• 预备语句或语句缓存；

• 优化事务模型；

• 确定DB-API可移值性的角色；

• 增加单元测试。

如果你对这些API特别感兴趣，欢迎积极参与。下面有一些手边的资源。

• http://python.org/topics/database 。

• http://www.linuxjournal.com/article/2605 。

• http://wiki.python.org/moin/DbApi3 。

21.2.5　关系数据库

现在我们准备开始，一个问题摆在面前，在Pyhton里我可以使用哪种数据库接口？换言之，Python支持哪些平台？答案是几乎所有的平台。下面是一个不怎么完整的数据库支持列表。

商业关系数据库管理系统

• Informix;

• Sybase；

• Oracle；

• MS SQL Server；

• DB/2；

• SAP；

• Interbase；

• Ingres。

开源关系数据库管理系统

• MySQL；

• PostgreSQL；

• SQLite；

• Gadfly。

数据库API

• JDBC；

• ODBC。

想要了解Python都支持哪些数据库，请参阅下面网址：

http://python.org/topics/database/modules.html

21.2.6　数据库和Python：接口程序

对每一种支持的数据库，都有一个或多个Python接口程序允许你连接到目标数据库系统。某些数据库，比如Sybase、SAP、Oracle和SQLServer，都有两个或更多个接口程序可供选择。你要做的就是挑选一个最能满足你需求的接口程序。你挑选接口程序的标准可以是，性能如何、文档或WEB站点的质量如何、是否有一个活跃的用户或开发社区、接口程序的质量和稳定性如何等。记住绝大多数接口程序只提供基本的连接功能，你可能需要一些额外的特性。高级应用代码，如线程和线程管理及数据库连接池的管理等，需要你自己来完成。

如果你不想处理这些，比方说你不喜欢自己写SQL，也不想参与数据库管理的细节——那么本章后面讲到的ORM （Object-Relational Mappers，对象-关系管理器）应该可以满足你的要求。现在来看一些使用接口程序访问数据库的例子，关键之处在于设置数据库连接。在建立连接之后，不管后端是何种数据库，对DB-API对象的属性和方法进行操作都是一样的。

21.2.7　使用数据库接口程序举例

首先，我们来看一下例子代码：创建数据库、创建表、使用表。我们分别提供了使用MySQL、PostgreSQL和SQLite的例子。

1. MySQL

这里我们以MySQL数据库为例，使用唯一的MySQL接口程序MySQLdb，这个接口程序又名MySQL-python。在这部分代码里，我们故意在例子里埋下一个错误。

首先我们以管理员身份登录，创建一个数据库，并赋予相应权限，之后我们再以普通用户身份登录数据库，以便你能了解你希望得到什么，这样你会想到为它创建一个事件处理程序。

在上面的代码中，我们没有使用cursor对象。某些（但不是所有的）接口程序拥有连接对象，这些连接对象拥有query()方法，可以执行SQL查询。我们建议你不要使用这个方法，或者事先检查该方法在当前接口程序当中是否可用。之后我们以普通用户身份再次连接这个新数据，创建表，然后通过Python执行SQL查询和命令，来完成我们的工作。这次我们使用游标对象（cursors）和它们的execute()方法，下一个交互集演示了创建表。

下面的代码演示了如何创建一个表。在删除一个表之前如果试图重建这个表将产生错误。

现在我们来插入几行数据到数据库，然后再将它们取出来。

最后一个特性是更新表，包括更新或删除数据。

MySQL是最流行的开源数据库之一。毫无疑问会有一个针对MySQL的Python接口程序。不过Python标准库中并没有集成这个接口程序，这是一个第三方包，你需要单独下载并安装它。在本章末尾的索引页，你可以找到如何下载它。

1. PostgreSQL

另一个著名的开源数据库是PostgreSQL。与MySQL不同，有至少3个Python接口程序可以访问PosgreSQL: psycopg, PyPgSQL和PyGreSQL，第四个，PoPy，现在已经被废弃（2003年，它贡献出自己的代码，与PygreSQL整合在一起）。这三个接口程序各有长处，各有缺点，根据实践结果来选择使用哪个接口更为明智。

多亏他们都支持DB-API，所以他们的接口基本一致，你只需要写一个应用程序，然后分别测试这三个接口的性能（如果性能对你的程序很重要的话）。下面我给出这三个接口的连接代码：

好，下面的代码就能够在所有接口程序下工作了。

最后，你会发现他们的输出有一点点轻微的不同。

1. SQLite

对非常简单的应用来说，使用文件进行持久存储通常就足够了。但对于绝大多数数据驱动的应用程序必须使用全功能的关系数据库。SQLite介于二者之间，它定位于中小规模的应用。它是相当轻量级的全功能关系型数据库，速度很快，几乎不用配置，并且不需要服务器。

SQLite正在迅速流行起来。并且在各个平台上都能用。Python2.5中就集成了前面介绍的pysqlite数据库接口程序，作为Python2.5的sqlite3模块。这是Python第一次将一个数据库接口程序纳入标准库，也许这标志着一个新的开始。

它被打包到Python当中并不是因为他比其他的数据库接口程序更优秀，而是因为他足够简单，使用文件（或内存）作为它的后端存储，就像DBM模块做的那样，不需要服务器，而且也不存在授权问题。它是Python中其他的持久存储解决方案的一个替代品，一个拥有SQL访问界面的优秀替代品。在标准库中有这么一个模块，就能方便用户使用Python和SQLite进行软件开发，等到软件产品正式上市发布时，只要有需要，就能够很容易的将产品使用的数据库后端变更为一个全功能的、更强大的类似MySQL、PostgreSQL、Oracle或SQL Server那样的数据库。当然，对那些不需要那么大马力的应用程序来说，SQLite已经足够使用。

尽管标准库已经提供了数据库接口程序，你仍然需要自己下载真正的数据库软件。一旦安装好之后，你就只需要打开Python解释器，下面是一个例子：

OK，这个小例子已经足够了。接下来，我们来看一个小程序，它类似前面使用MySQL的例子，但完成几种新的功能：

• 创建一个数据库（如果必要）

• 创建一个表

• 在表中插入行

• 在表中更新行

• 在表中删除行

• 删除表

这个例子中，我们仍然使用两个其他的开源数据库。SQLite现如今已经相当流行。它体积小，而且足够快，是一个拥有几乎全部功能的相当轻量级的数据库。这个例子中用到的另一个数据库是Gadfly，一个基本兼容SQL的纯Python写成的关系数据库。（某些关键的数据库结构有一个C模块，不过Gadfly没有它也一样可以运行（当然，会慢不少，嘿嘿）)。

在进入代码之前，有几件事要提醒。SQLite和Gadfly需要用户指定保存数据库文件的位置（MySQL有一个默认区域保存数据，在使用MySQL数据库时无需指定这个）。另外，Gadfly目前的版本还不兼容DB-API 2.0，也就是说，它缺失一些功能，尤其是缺少我们例子中用到的cursor属性rowcount。

4.数据库接口程序应用程序举例

在下面这个例子里，我们演示了Python如何访问数据库。事实上，我们的程序支持三种不同的数据库系统：Gadfly、SQLite和MySQL。我们将要创建一个数据库（如果它不存在的话），然后进行多种数据库操作，比如创建表、删除表、插入数据、更新数据、删除数据等。在下一小节中的ORM中我们将重复例子21.1的这些功能。

5.逐行解释

第1 ~ 18行

脚本的第一部分导入必须的模块，创建一些”全局常量”（列的显示大小及我们的程序支持的数据库）。其中setup()函数提供一个简单界面让用户选择使用哪种数据库。

值得留意的是DB_EXC常量，它代表数据库异常。他最终的值由用户最终选择使用的数据库决定。也就是说，如果用户选择MySQL, DB_EXC将是_mysql_exceptions，依此类推。如果我们用流行的面向对象的方式来开发这个应用，它将会以一个实例属性的方式表示，比如self.db_exc_module或者什么别的名字。

第20 ~ 75行

这里的connect()函数表现了数据库存取一致性。在每一小节的开头，我们尝试载入需要的数据库模块。如果找不到合适的模块，None值被返回，表示这个数据库系统暂不支持。

在数据库连接建立以后，其余的代码对数据库和接口程序来说都是透明的（不区分哪种数据库、哪种接口程序，代码都可以工作）。有一个唯一的例外，就是脚本的insert()函数。在这部分代码的所有3小段中，数据库连接成功后会返回一个连接对象cxn。

如果选中了SQLite（24行~36行），我们尝试载入一个数据库接口程序。我们首先尝试载入标准库模块sqlite3（Python2.5及更高版本支持），如果载入失败，就会去寻找第三方pysqlite2包。这个包支持Python2.4.x或更老些的系统。

如果成功导入合适的接口程序，由于SQLite是基于文件的数据库系统，同我们需要确认一下数据库文件所在的目录是否存在（当然，你也可以选择在内存里创建一个数据库）。当调用connect()函数时，如果这个数据库文件已经存在，SQLite会使用这个数据库，如果文件不存，它就会创建一个新文件。

例21.1　数据库接口程序示例

这段脚本使用同样的接口对多种数据库执行了一些数据库基本操作。

MySQL（38~57行）的数据文件会存保在默认的数据存储区域，所以不需要用户指定存储位置。我们的代码尝试连接指定的数据库。如果发生错误，有可能是数据库不存在，或者虽然数据库存在但我们没有权限访问它。由于这仅仅是一个测试应用程序，我们选择完全先删掉这个数据库（忽略掉如果数据库不存在可能引发的错误），然后重建该库，然后给访问它的用户赋予权限。

我们的应用程序支持的最后一个数据库是Gadfly（第59~75行）。在本书写作的时候，这个数据库已经几乎但还没有完全兼容DB-API，你也会在这个程序里看到这一点。）它使用类似SQLite的启动机制：它的启动目录是数据文件所在的目录。如果数据文件在那儿，那没有问题，如果那儿没有数据文件，你必须重新启动一个新的数据库（为什么非要这样，我们也不十分清楚。我们认为startup()函数应该被合并到构造器函数gadfly.gadfly()当中去）。

77 ~ 89行

create()函数在数据库中创建一个新的users表，如果中间产生问题，几乎肯定是因为这个表已经存在。如果正是这个原因的话，删掉这个表，然后递归调用create()函数来重新创建它。这个代码有一个缺陷，就是当重建表仍然失败的话，你将陷入死循环，直至内存耗尽。在本章最后有一道习题就是这个问题，你可以试着修复这个潜在的bug。

91 ~ 103行

这可能是除了数据库操作之外最有趣的代码部分了。它由一组固定用户名及ID值的集合及一个生成器函数randName()构成。这个函数的代码也可以在11.10节找到。NAMES常量是一个元组，因为我们在randName()这个生成器里需要改变它的值，所以我们必须在randName()里先将它转换为一个列表。我们一次随机移除一个名字，直到列表为空为止。如果NAMES本身是一个列表，我们只能使用它一次（它就被消耗光了）。我们将它设计成为一个元组，这样我们就可以多次从这个元组生成一个列表供生成器使用。

105 ~ 115行

由于各种数据库之间有一些细微差别，insert()函数里的代码是依赖具体数据库的。举例来说，SQLite和MySQL的接口程序都是DB-API兼容的，所以它们的游标对象都拥有executemany()方法，可是Gadfly没有这个方法，因此它只能一次插入一行。

另一个不同之处在于SQLite和Gadfly的参数风格是qmark，而MySQL的参数风格是format。由于这些原因，格式字符串必须不同。如果你比较细心的话，你会看到他们的参数创建过程非常相似。

这段代码的功能是：对每个name-userlD数据对，随机分配一个项目小组ID，然后存入数据库。

117行

这独立的一行是有一个条件表达式（读作Python 3目操作符），它返回最后一步操作所影响的行数，如果游标对象不支持这个属性（也就是说这个接口程序不兼容DB-API）的话，它返回-1。python2.5中新增了条件表达式，如果你使用的是python 2.4.x或更老版本，你可能就需要将它转换为老风格的方式了，如下所示。

如果你看不太明白这行代码，不用着急。看看FAQ就能知道为什么最终Python2.5中加入了条件表达式。如果你能弄明白，你就彻底搞明白了Python对象以及他们的布尔值。

119 ~ 129行

update()和delete()函数随机从一个组里选择了几条记录，如果是update操作，就将他们从当前小组移到另一个小组（也是随机选择的）。如果是delete操作，则删除它们。

131 ~ 137行

dbDump()函数从数据库中读取所有数据，并将数据进行格式化，然后显示给用户看。print语句显示每个用户不够清晰，所以我们将它分开显示。

首先，通过fetchall()方法读取数据，然后迭代遍历每个用户，将三列数据（login、uid、 prid）转换为字符串（如果它们还不是的话），并将姓和名的首字母大写，再格式化整个字符为左对齐的COLSIZ列（右边留白）。由代码生成的字符串是一个列表（通过列表解析），我们需要将它们转换成一个元组以支持%操作符。

139 ~ 174行

本部影片的导演main()出场。它将上面定义的这些函数组织起来，让它们尽情发挥。（假定它们没有因为找不到数据库接口程序或者不能得到有效连接对象而中途退出（第143~145行））。它的大部分代码都是能够自我解释的print语句。最后main()关闭游标对象，提交操作，然后关闭数据库连接。脚本的最后几行代码用来启动脚本的执行。