使用 SQLObject 连接数据库与 Python

核心提示：本文示例源代码或素材下载 通过提供用于操作数据库表的类和对象，对象关系映射工具有助于提高生产率，使用 SQLObject 连接数据库与 Python，Python 最好的对象关系映射工具是 SQLObject —— 一个开放源码项目，它几乎完成编程数据库所需的所有操作，它的限制易于理解，

本文示例源代码或素材下载

通过提供用于操作数据库表的类和对象，对象关系映射工具有助于提高生产率。Python 最好的对象关系映射工具是 SQLObject —— 一个开放源码项目，它几乎完成编程数据库所需的所有操作。本文介绍 SQLObject 及其功能。阅读本文后，您将能够不编写任何 SQL 代码而连接 Python 与数据库。

当面向对象编程范例满足大多数数据库的关系范例时，通常会看到对象关系映射。对象关系映射是这两个世界的桥梁。它允许您定义与数据库表对应的类。然后您可以使用这些类及其实例上的方法来与数据库交互，而不用编写 SQL。使用对象关系映射并不意味着不需要知道关系数据库如何工作，而是不必要编写 SQL，从而避免编程错误。

您可以找到一打以上的操作 SQL 数据库的开放源码 Python 包，这还没包括用于连接 Python 与特定数据库的特殊用途模块。SQLObject 是其中最好的模块。它是简单易用的完全对象关系映射包。SQLObject 几乎可以完成编程数据库所需的所有操作。

本文展示了 SQLObject 如何与数据库交互，如何使用 SQLObject 编写数据库访问和数据验证代码，以及如何将它用于遗留或现有数据库。这里假设您已经具备 Python 和关系数据库的知识。

安装和设置 SQLObject

SQLObject 具有一个 setup.py 文件，安装方式与其他任何 Python 包一样。如果您使用的是 Python V2.2，则还需要安装 mxDateTime Python 包（SQLObject 使用 Python V2.3 的内置 datetime 模块，如果该模块可用的话）。

要实际使用 SQLObject，需要设置数据库包以及这种数据库的 Python 接口。SQLObject 连接多种数据库，其中包括三个大的开放源码产品：MySQL、PostgreSQL 和无服务器 SQLite。

最后，需要为应用程序创建数据库。对于 SQLite，这意味着创建一个存储该数据库的文件。对于其他数据库，这意味着连接数据库服务器，执行 CREATE DATABASE 命令，并授权数据库用户对新数据库的一些访问，以便 SQLObject 可以使用该用户帐户来连接。

清单 1 展示了如何用 MySQL 创建新数据库。

清单 1. 用 MySQL 创建新数据库的代码

mysql> use mysql;
Database changed
mysql> create database sqlobject_demo;
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on sqlobject_demo to 'dbuser'@'localhost'
identified by 'dbpassword';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

连接数据库

需要编写的第一个 Python 代码是数据库连接代码。基于所使用的数据库，这是惟一需要编写不同代码的地方。

例如，如果想让应用程序使用 SQLite 数据库，则需要将数据库文件的路径写入位于 sqlobject.sqlite 包的 SQLite 连接构建器中。如果数据库文件不存在，QLObject 将告诉 SQLite 创建一个，代码如下：

import sqlobject
from sqlobject.sqlite import builder
conn = builder()('sqlobject_demo.db')

如果使用的是 MySQL 或带有服务器的其他数据库，则将数据库连接信息传递到连接构建器中。清单 2 提供了在上一节创建的 MySQL 数据库的示例。

清单 2. 传递 MySQL 数据库连接信息的代码

import sqlobject
from sqlobject.mysql import builder
conn = builder()(user='dbuser', passwd='dbpassword',
　　　　　　　　 host='localhost', db='sqlobject_demo')

不管连接哪种数据库，连接代码都应该放置在一个名称类似 Connection.py 的文件中，且该文件存储在一些通常可访问的位置中。这样，可以导入您定义的所有类，并使用已经构建的 conn 对象。conn 变量将包含所有与数据库相关的详细信息。

但是要注意，SQLObject 的一些特性不可用于 SQLite 或 MySQL。不能将数据库选择与连接之后编写的代码完全分离。（参阅 Using SQLObject with pre-existing tables 获得更多信息。）

定义模式

SQLObject 使得操作数据库表变得容易。看第一个简单的例子，考虑一个电话簿应用程序的由单个表组成的数据库模式，如表 1 所示。

表 1. phone_number 表的描述

字段	类型	说明
id	Int	主键
number	String	“(###) ###-####”字符串格式；应该惟一
owner	String	这是谁的号码？
last_call	Date	用户最后一次呼叫该号码是什么时候？

取决于您的 SQL 风格，该表的 SQL 类似如下：

CREATE TABLE phone_number (
　id INT PRIMARY KEY AUTO_INCREMENT,
　number VARCHAR(14) UNIQUE,
　owner VARCHAR(255),　
　last_call DATETIME,
　notes TEXT
)

使用 SQLObject，不需要编写该 SQL 代码。通过定义 Python 类来定义该数据表。该代码将进入名为 PhoneNumber.py 的文件中，如清单 3 所示。

清单 3. PhoneNumber.py

import sqlobject
from Connection import conn
class PhoneNumber(sqlobject.SQLObject):
　　_connection = conn
　　number = sqlobject.StringCol(length=14, unique=True)
　　owner = sqlobject.StringCol(length=255)
　　lastCall = sqlobject.DateTimeCol(default=None)
PhoneNumber.createTable(ifNotExists=True)

在此使用了先前定义的 conn 变量。每个表类需要将对数据库连接的引用存储在它的 _connection 成员中。该信息隐式用于对该类的表的所有数据库访问中。因此，不必担心 SQL 或任何特殊数据库，因为代码可以按照抽象关系模式来表示。

定义表的类还有一组定义表字段的成员。SQLObject 提供了 StringCol、BoolCol 等等 —— 一个类对应一种数据库字段类型。

createTable() 方法第一次运行时，SQLObject 将创建一个名为 phone_number 的表。然后，它将只使用该表，因为您将 ifNotExists 设置为 True 来调用该方法。

最后注意，无需在 PhoneNumber 中为 id 字段创建字段对象。因为 SQLObject 总是需要该字段对象，所以它总会创建一个。

处理旧 CRUD

著名的缩写词 CRUD 代表对数据库行进行的四种操作：Create、Read、Update 和 Delete。定义了与数据库表对应的类之后，SQLObject 将对表行的操作表示为对类及其实例的操作。

创建

要创建数据库行，需创建对应类的实例，代码如下：

>>> from PhoneNumber import PhoneNumber
>>> myPhone = PhoneNumber(number='(415) 555-1212',
owner='Leonard Richardson')

现在 phone_number 表有一个存储我的姓名的行。PhoneNumber 构造函数将表列的值作为关键字参数。它使用您提供的数据创建一行 phone_number。如果由于某种原因，数据不能进入数据库，则构造函数抛出一个异常。当电话号码无效时，会发生如下情况：

清单 4. 无效的电话号码

>>> badPhone = PhoneNumber()
Traceback (most recent call last):
　File "<stdin>", line 1, in ?
...
TypeError: PhoneNumber() did not get expected keyword argument number

如果电话号码已经在数据库中，将会看到：

清单 5. 电话号码已经在数据库中

>>> duplicatePhone = PhoneNumber(number="(415) 555-1212")
Traceback (most recent call last):
　File "<stdin>", line 1, in ?
...
TypeError: PhoneNumber() did not get expected keyword argument owner

读取（和查询）

SQLObject 类的实例的所有字段可用作成员。这与其他一些将数据库行当作字典的数据库映射工具相反。因此，对于数据库行中的每个字段，PhoneNumber 对象都具有一个成员。

清单 6. 可用作成员的字段

>>> myPhone.id
1
>>> myPhone.owner
'Leonard Richardson'
>>> myPhone.number
'(415) 555-1212'
>>> myPhone.lastCall == None
True

但如何检索数据库中已经存在的 PhoneNumber 对象呢？需要对数据库执行查询来获取。这就是 SQLObject 的查询构造工具包（该软件包最有趣的特性之一）发挥作用的地方。它允许将 SQL 查询表示为 Python 对象链。如果您熟悉 Java™ 编程语言的对象关系包的话，它与可以对 Torque 中 Criteria 对象执行的操作一样。

您定义的每个 SQLObject 类都有一个 select() 方法。该方法接受一个定义查询的对象，返回与该查询匹配的项列表。例如，下面这个方法调用返回包含数据库中第一个电话号码的列表：

清单 7. select() 方法

>>> PhoneNumber.select(PhoneNumber.q.id==1)
<sqlobject.main.SelectResults object at 0xb7b76cac>
>>> PhoneNumber.select(PhoneNumber.q.id==1)[0]
<PhoneNumber 1 number='(415) 555-1212' lastCall=None
owner='Leonard Richardson'>

PhoneNumber.q.id 指明想要对 phone_number 表的 id 字段运行查询。SQLObject 过载比较操作符（==、!=、<、>= 等等）来执行除布尔表达式之外的查询。表达式 PhoneNumber.q.id==1 是与 id 字段值为 1 的每行相匹配的查询。更多示例如下：

清单 8. 更多示例

>>> PhoneNumber.select(PhoneNumber.q.id < 100)[0]
<PhoneNumber 1 number='(415) 555-1212' lastCall=None
owner='Leonard Richardson'>
>>> PhoneNumber.select(PhoneNumber.q.owner=='Leonard Richardson').count()
1
>>> PhoneNumber.select(PhoneNumber.q.number.startswith('(415)')).count()
1

可以使用 SQLObject 的 AND 和 OR 函数来组合查询子句：

清单 9. 用于组合查询子句的 AND 和 OR 函数

>>> from sqlobject import AND, OR
>>> PhoneNumber.select(AND(PhoneNumber.q.number.startswith('(415)'),
>>>　　　　　　　　　　　　PhoneNumber.q.lastCall==None)).count()
1

下列查询获取一年中呼叫过的所有人以及从未呼叫过的所有人：

清单 10. 一年中呼叫过的所有人以及从未呼叫过的所有人

>>> import datetime
>>> oneYearAgo = datetime.datetime.now() - datetime.timedelta(days=365)
>>> PhoneNumber.select(OR(PhoneNumber.q.lastCall==None,
...　　　　　　　　　　　 PhoneNumber.q.lastCall < oneYearAgo)).count()
1

更新

如果更改 PhoneNumber 对象的一个成员，则该更改被自动镜像映射至数据库：

清单 11. 更改被自动镜像映射至数据库

>>> print myPhone.owner
Leonard Richardson
>>> print myPhone.lastCall
None
>>> myPhone.owner = "Someone else"
>>> myPhone.lastCall = datetime.datetime.now()
>>> #Fetch the object fresh from the database.
>>> newPhone = PhoneNumber.select(PhoneNumber.q.id==1)[0]
>>> print newPhone.owner
Someone else
>>> print newPhone.lastCall
2005-05-22 21:20:24.630120

这只是一个警告：SQLObject 不允许更改对象的主键。通常最好是让 SQLObject 来管理表的 id 字段，即使是您不使用 SQLObject 时碰巧用另一个字段作为主键。

删除

删除特定行对象的方法是将其 ID 传递到其类的 delete() 方法中：

清单 12. 删除行对象

>>> query = PhoneNumber.q.id==1
>>> print "Before:", PhoneNumber.select(query).count()
Before: 1
>>> PhoneNumber.delete(myPhone.id)
>>> print "After:", PhoneNumber.select(query).count()
After: 0

验证和转换数据

到目前为止，一直用 14 字符美国格式传递电话号码。数据库模式被设计成接受这种格式的数字，这暗示着底层应用程序 —— 不管怎样 —— 都希望数字是这种格式。尽管如此，代码不能确保笨拙的用户或程序员漏洞不会导致未正确格式化的数据放入 number 字段中。

SQLObject 通过允许定义验证和转换入站数据的钩子方法来解决这个问题。可以为表中的每个字段定义一个方法。字段的钩子方法命名为 _set_[field name]()，不管是作为 create 操作还是 update 操作的一部分，每当要为该字段设置一个值时，都会调用该方法。钩子方法应（可选）将入站值转换为可接受格式，然后设置该值。否则，它应抛出异常。要实际设置一个值，该方法需要调用 SQLObject 方法 _SO_set_(field name)。

清单 4 展示了 PhoneNumber 的 _set_number() 方法。如果电话号码完全没有格式化，比如 4155551212，则该方法将该数字格式化为 (415) 555-1212。否则，如果数字格式不正确，该方法会抛出 ValueError。正确格式化的电话号码 —— 或者是转换为正确格式的电话号码 —— 被正确传递给 SQLObject 的 _SO_set_number() 方法。

清单 13. PhoneNumber 的 _set_number () 方法

　 import re
　 def _set_number(self, value):
　　　if not re.match('([0-9]{3}) [0-9]{3}-[0-9]{4}', value):
　　　　 #It's not in the format we expect.
　　　　 if re.match('[0-9]{10}', value):
　　　　　　#It's totally unformatted; add the formatting.
　　　　　　value = "(%s) %s-%s" % (value[:3], value[3:6], value[6:])
　　　　 else:
　　　　　　raise ValueError, 'Not a phone number: %s' % value
　　　self._SO_set_number(value)

定义表之间的关系

到目前为止，看到的所有操作都针对单个表：phone_number。但真正的数据库应用程序通常具有多个相关表。SQLObject 允许将表之间的关系定义为外键。作为演示，我们将一个小的数据库规范化（normalization）应用于上一示例，将 PhoneNumber 的 owner 字段分割到单独的 person 表中。清单 14 所示的代码保存在名为 PhoneNumberII.py 的文件中。

清单 14. PhoneNumberll.py 的代码

import sqlobject
from Connection import conn
class PhoneNumber(sqlobject.SQLObject):
　　_connection = conn
　　number = sqlobject.StringCol(length=14, unique=True)
　　owner = sqlobject.ForeignKey('Person')
　　lastCall = sqlobject.DateTimeCol(default=None)
class Person(sqlobject.SQLObject):
　　_idName='fooID'
　　_connection = conn
　　name = sqlobject.StringCol(length=255)
　　#The SQLObject-defined name for the "owner" field of PhoneNumber
　　#is "owner_id" since it's a reference to another table's primary
　　#key.
　　numbers = sqlobject.MultipleJoin('PhoneNumber', joinColumn='owner_id')
Person.createTable(ifNotExists=True)
PhoneNumber.createTable(ifNotExists=True)

该 PhoneNumber 类具有与旧类相同的成员，但它的 owner 成员是对 person 表的主键的引用，而不是对 phone_number 表中字符串列的引用。这使得表示具有两个电话号码的个人成为可能：

清单 15. 表示具有两个电话号码的个人

>>> from PhoneNumberII import PhoneNumber, Person
>>> me = Person(name='Leonard Richardson')
>>> work = PhoneNumber(number="(650) 555-1212", owner=me)
>>> cell = PhoneNumber(number="(415) 555-1212", owner=me)

Person 的 numbers 成员，一个 SQLObject MultipleJoin，使得基于 person 到 phone_number 的连接进行查询变得容易：

清单 16. 基于 person 到 phone_number 的连接的查询

>>> for phone in me.phoneNumbers:
...　　 print phone.number
...
(650) 555-1212
(415) 555-1212

同样，SQLObject 允许使用 MultipleJoin 类进行多对多连接的查询。

将 SQLObject 用于现有表

SQLObject 的一个常见用途是为另一个应用程序创建的数据库提供 Python 接口。SQLObject 有多个特性可用于实现这一点。

数据库内省

如果正在使用数据库中已经存在的表，则不需要在 Python 中定义列。SQLObject 可以通过数据库内省来提取它需要的信息。例如，清单 17 中的代码保存在 PhoneNumberIII.py 中。

清单 17. PhoneNumberlll.py 的代码

import sqlobject
from Connection import conn
class PhoneNumber(sqlobject.SQLObject):
　　_connection = conn
　　_fromDatabase = True

该类将使用现有 phone_number 数据表的属性。您可以与它交互，就好像已经手动定义了该类及其所有列一样，如前面的示例所示。使用 SQLObject，只需要编写表定义一次 —— 用 SQL 还是用 Python 编写就取决于您了。

但是，该特性又带来了数据库的选择问题。例如，该特性完全不能用于 SQLite。它基本上能用于 MySQL，但不能提取外键关系。如果使用的是 MySQL，而且想要为表定义外键，则需要在从数据库中加载模式之后，编写代码定义这些字段。

命名约定

上一部分中的代码假设现有表符合 SQLObject 的命名约定（例如，表的主键字段名为 id，且列名中的词用下划线分隔）。表的命名约定在 Style 类中定义。

SQLObject 提供了一些与常见数据库命名约定对应的 Style 类。例如，如果列名类似 likeThis 而非 like_this，则可以使用 MixedCaseStyle：

清单 19. 使用 MixedCaseStyle

import sqlobject
from sqlobject.styles import MixedCaseStyle
from Connection import conn
class PhoneNumber(sqlobject.SQLObject):
　　_connection = conn
　　_fromDatabase = True
　　_style = MixedCaseStyle

如果没有预包装的 Style 类符合您的需要，那么您可以定义 Style 基类的子类，并定义自己的命名约定。在最坏的情况下，如果表的字段名分配得毫无道理，则可以逐个命名每个字段。

关于 SQLObject 限制

SQLObject 想让您用面向对象的方式而非关系方式进行思考。这有利于您的理解和您的编程生产率，但不利于性能。毕竟，数据库仍是关系型的。如何标记呼叫过的每个电话号码？使用 SQL，您将使用单个 UPDATE 命令。使用 SQLObject，您需要迭代通过整个结果集，并修改每个对象的 last_call 成员，这是非常低效的。

SQLObject 为开发人员时间牺牲了处理器时间。这通常是好的交易，但甚至在简单的应用程序中，您也可能需要下降一个级别到达 Python 数据库接口，为一些关键路径的操作编写原始 SQL。

结束语

本文广泛介绍了 SQLObject，但并不十分深入。它是一个万能工具，具有许多方便的小特性。它的限制易于理解，但可以通过编写需要的 SQL 来绕过它们。SQLObject 针对关系数据库编程而 Python 针对应用程序编程：一种用很少的时间完成工作的方便方法。