Archive for the ‘Database’ Category

使用SQLObject

Monday, June 2nd, 2008

使用SQLObject

译者: gashero

目录

1   导入模块

from sqlobject import *

2   定义MySQL使用的URI连接

mysqluri="mysql://user:password@host:port/database"

端口号为3306,一定要指定的。否则按照旧式连接方法里面,端口port属性设为None,就会抛出TypeError异常,因为要求必须是一个整数类型的端口号。如果按照新式的URI方式连接,如果不指定端口号则port默认为0,也会出现连接失败的问题。

sqlite的连接:

sqlite:///full/path/to/database
sqlite:/C|full/path/to/database
sqlite:/:memory:

postgre数据库的连接:

postgres://user@host/database?debug=&cache=
postgres://host:5432/database

3   连接

conn=connectionForURI(mysqluri)
sqlhub.processConnection=conn

4   定义一个表格类

class Person(SQLObject):
    firstName=StringCol()
    middleInitial=StringCol(length=1,default=None)
    lastName=StringCol()

如果没有定义sqlhub,则需要使用 Person._connection=conn 来指定连接。

5   创建表格

Person.createTable()

可以指定参数ifNotExists=True,仅仅在表格不存在时才创建这个表格。

6   自动索引

自动给出一个叫做id的索引,所以不需要人为指定。

在MySQL中定义是:

INT PRIMARY KEY AUTO_INCREMENT

需要使用这个字段时,使用.id属性。

7   创建一个对象

就是创建类的实例:

Person(firstName="John",lastName="Doe")

在SQLObject中的NULL/None并不是代表缺省。NULL代表完全不同的事物、正文或者是人。有时NULL也代表缺省(default),有时代表不适用,有时代表未知。如果希望缺省一个值,可以使用NULL或其他。

SQLObject的default不同于数据库的default。SQLObject从不使用数据库的default。

注意,创建一个对象时,构造方法的参数必须要指定列名,也就是映射类的属性名,否则会出现异常。

8   空值

如果在Person的实例中省略了firstName和lastName则会产生错误,因为没有赋予缺省值。如果是赋予了缺省值的如middleInitial字段,则会被设为NULL值,等同于数据库的None。

9   查询

可以使用类的.get(n)方法得到已经存在的实例。

当创建一个对象时,SQLObject会自动将其存入数据库,而不像其他系统那样,需要手动提交修改。另外,对已有对象的属性修改也会直接存入数据库。

列可以按照属性来存取。注意,对象是独一无二的(unique),如果两次获取同一ID的记录,则获得的是同一对象。这种机制可以确保多个线程存取 同一对象时的一致性。当然,多个线程之间可以不共享对象实例。但是在使用事务(transaction)时,因为事务的隔离性而不成立。

10   开启调试显示

同步显示SQL语句和调试状态。建立连接时使用debug选项:

mysql://user:passwd@host:port/database?debug=t

或:

Person._connection.debug=True

还可以选用的其他选项包括debugOutput(缺省为False),cache(True),autoCommit(True),debugThreading(False)。

在可以看到SQL语句的情况下可以清除的理解工作过程。只需在连接URI中加入”?debug=t”即可。或者设置debug属性。这样,所有的SQL语句都会打印到控制台。这是可靠的,也推荐使用。

11   set方法

用于微弱的提高性能,一次指定多个属性,如:

>>> p.set(firstName="Robert",lastName="Hope Jr.")

12   懒惰的更新

缺省时,每修改一个属性就会导致一个UPDATE的发生,或者每次调用 .set() 时。如果想要避免(avoid)这种仿佛的更新,加入 _lazyUpdate=True 到类定义。这样只有在每次调用 inst.syncUpdate() 或者 obj.sync() 时才写入更新, .sync() 也同时会取出数据库中的最新数据,而 .syncUpdate() 并不这样做。

如果一个实例含有”.sqlmeta.dirty”属性,就是用来指示含有未提交数据的属性。则插入动作会立即提交,这时是没有办法延迟提交的。

13   一对多联系

就是需要建立外键。例如一个地址本类,需要建立外键对应到Person类。

class Address(SQLObject):
    street=StringCol()
    city=StringCol()
    state=StringCol(length=2)
    zip=StringCol(length=9)
    person=ForeignKey('Person')
Address.createTable()

这样通过ForeignKey()方法建立对Person类的外键。实际上是引用了Person对象。实际是按照类的名字(字符串)来引用一个类。数据库中对应person_id列,对应指向person列。

注意:SQLObject使用字符串来引用一个类是因为很多时候也许一个类还不存在。class关键字作为一个命令,在导入一个模块时才执行,并绑定类的名字和类。所以为了确保类之间的引用正确,在所有的类都装载完成之后才建立类之间的联系。

在需要一个人Person对应多个地址时,可用如下连接:

class Person(SQLObject):
    ...
    addresses=MultipleJoin('Address')

在我们已经拥有了Person类的情况下,可以用如下方式修改:

Person.sqlmeta.addJoin(MultipleJoin('Address',joinMethodName='addresses'))

大多数时候可以在创建SQLObject类之后再修改他们。在类定义中使用*Col对象属性等同于使用类方法addColumn()。

然后我们就可以使用自动联系aPerson.addresses,这将会返回一个列表。例如:

>>> p.addresses
[]
>>> Address(street='123 W Main St',city='Smallsville',
...         state="MN",zip='55407',person=p)
<Address 1 ...>
>>> p.addresses
[<Address 1 ...>]

多连接(Multiple Join)类似于关系连接(Related Join),返回结果的列表。你可能更喜欢得到SelectResults对象,可以使用SQLMultipleJoin和SQLRelatedJoin。

14   多对多联系

这个例子中包含用户(user)和角色(role)两个对象,其间包含多对多联系,将使用RelatedJoin来实现。

class User(SQLObject):
    class sqlmeta:
        #user是一些数据库的保留字,所以用了别名
        table="user_table"
    username=StringCol(alternateID=True,length=20)
    #暂时先定义这个,其他还可以有很多字段
    role=RelatedJoin("Role")
class Role(SQLObject):
    name=StringCol(alternateID=True,length=20)
    users=RelatedJoin('User")
User.createTable()
Role.createTable()

注意:使用sqlmeta类。这个类用于存储多种元信息(metadata)。这是SQLObject 0.7中新引入的特性。查看Class sqlmeta节了解详细。

使用:

bob=User(username="bob")
tim=User(username="tim")
admin=Role(name='admin')
editor=Role(name='editor')
bob.addRole(admin)
bob.addRole(editor)
tim.addRole(editor)
>>> bob.roles
[<Role 1 name="admin">, <Role 2 name='editor'>]
>>> tim.roles
[<Role 2 name='editor'>]
>>> admin.users
[<User 1 username='bob'>]
>>> editor.users
[<User 1 username='bob'>, <User 2 username='tim'>]

这会自动生成一个中间表role_user。用来同时引用其他类。这个表不会成为一个暴露(expose)的类,这个表的行也不会等同于Python对象。这种多对多的关系完全被隐藏了。

如果想要自己创建中间表,用来增加一些字段,可以查看标准的SQLObject的add/remove方法来工作。假设你可以提供连接列和其他类的 正确连接,也无法通过这些方法插入扩展信息,而且也无法设置默认值。(Assuming that you are providing the join with the correct joinColumn and otherColumn arguments, be aware it’s not possible to insert extra data via such methods, nor will they set any default value.)。

如前述的User/Role系统,如果创建了UserRole中间表,通过创建两个外键来建立MTM(多对多Many-to-Many)联系,并且 附加了DateTimeCol类型的字段(缺省为当前时间)。那么这个列在使用addRole()方法加入role之前会保持为空。

你可能会注意到列加入了扩展保留字alternateID。可以使用alternateID=True来确保该字段的值唯一(uniquely)。 有如确保用户名必须唯一一样。这个标识符会成为主键,且可以访问的。对于这种字段,如果添加了相同值,则会抛出异常 pysqlite2.dbapi2.IntegrityError: column [列名] is not unique。

注意:SQLObject必须确保主键是唯一且不可改变的。可以通过SQLObject来改变主键,但是你要自己提供确保数据一致性的机制。正是因为这个原因,才推荐使用无意义的整数ID,这样可以确保在未来改变时比较安全。

一个alternateID类创建一个类方法,形如byUsername来对应一个叫做username的列(也可以使用alternateMethodName关键字参数来重载)。如下使用:

>>> User.byUsername('bob')
<User 1 username='bob'>
>>> Role.byName('admin')
<Role 1 name='admin'>

15   选择多个对象(查询)

查询才是真正有用的东西,比显示联系重要的多。select是一个类方法,可以按照如下方式使用:

>>> Person._connection.debug=True
>>> peeps=Person.select(Person.q.firstName=="John")
>>> list(peeps)
 1/Select : 使用的SQL语句
 1/COMMIT : auto
[<Person 1 firstName='John' ...>]

这个例子放回使用John作为firstName的所有人。一个使用表达式的复杂例子:

>>> peeps=Person.select(
...     AND(Address.q.personID==Person.q.id,
...         Address.q.zip.startswith('504')))
>>> list(peeps)
..............
[]

属性q用于给出存取特定对象的结构化查询子句。所有被q所引用的列名将会转换成SQL语句。当然也可以手工生成SQL语句:

>>> peeps=Person.select("""address.id=person.id AND
...                        address.zip LIKE '504%'""",
                        clauseTable=['address'])

注意必须使用clauseTable(子表)来指定子表。如果使用q属性,SQLObject会自动计算出(figure out)所需要使用的扩展信息类。

你也可以使用MyClass.sqlrepr来手工指定任何SQL语句,而在使用q属性时是自动指定的。

还可以使用orderBy关键字创建select语句中的”ORDER BY”。orderBy获取一个字符串,表示数据库的列名,例如Person.q.firstName。也可以使用”-colname”来反向排序。或者 调用MyClass.select().reversed()。

也可以使用类实例的_defaultOrder属性指定缺省的排序列。如果在这时需要获得未排序的结果,使用orderBy=None。

select的结果是一个生成器(generator),可以用于后续调用。所以SQL仅在列出选择结果时才执行,或者使用list()返回所有结 果时。当列举查询结果时,每次取回一个行。这种方法可以在返回结果很大时避免将所有结果放入内存。也可以使用.reversed()而不必获得所有结果实 体,取而代之的是自动修改了SQL语句来获得需要的结果。

还可以对查询结果分片。这将会修改SQL语句,所以peeps[:10]将会把”LIMIT 10″加入SQL语句中。如果切片无法反映到SQL(如peeps[:-10]),则执行查询之后,对查询结果列表进行操作。当然,这只是会出现在使用负索引时。

大多数情况会得到多个查询结果对象。如果不希望这样,可以加入关键字MyClass.select(…,distinct=True),对应SQL中的SELECT DISTINCT。

你也可以通过count得到查询结果的个数,比如MyClass.select().count()。这将会导致一个COUNT(*)查询。这时并不会从数据库中取得对象,而仅仅是获得结果数量。

在少数特别注重效率的时候,效率实际上是依赖于批处理的使用方法。提高排序和查找效率的好办法是使用索引。且缓存比切片更好。

在这种情况下缓存意味着响应所有的结果。可以使用list(MyClass.select(…))来实现。可以在规定的时间内保存查询结果,来让用户分页查看结果。这样,第一次查询会看上去花费更多的时间,但是后面的页面显示却非常快速。

更多关于查询子表的问题参见”SQLBuilder documentation”。

16   selectBy方法

除了.select之外的另一个选择是.selectBy。按照如下工作:

>>> peeps=Person.selectBy(firstName="John",lastName="Doe")

每个关键字对应一个列,且所有的键值对都是以AND进行逻辑连接。返回结果是SelectResult。所以可以切片,计数,排序等等。

17   sqlmeta类

这是在SQLObject 0.7中引入的,允许使用一种清晰的方式指定数据库的元数据,而不需要使用类的命名空间中的属性来指定。

有一些特别的属性可以用在这个类当中,可以用于改变类的行为。他们包括:

  1. table 数据库的表名,是从style派生而来的,仅用于没有指定类名时。如果没有指定名字,且没有定义可选的style,则标准方式是指定MixedCase为mixed_case。
  2. idName 指定数据库表的主键名,如未指定时继承自style。缺省为id。
  3. idType 设置ID时的强制函数。缺省为int。
  4. style 一个样式(style)对象,这个对象允许使用其他算法翻译Python属性和类名称与数据库列名和表名之间的对应关系。参考”Changing the Naming Style”了解更多。它是一个IStyle实例的接口。
  5. lazyUpdate 布尔值,缺省为False。如果为True,那么改变属性时不会自动更新查询。而等待调用inst.syncUpdates()或inst.sync()时才执行更新。
  6. defaultOrder 查询数据时的缺省排序方式。
  7. cacheValues 布尔值,缺省为True。如果为True,保存在行中的值一直缓存到inst.expire()被调用。如果设为False,属性值不会被缓存,所以每次 存取一个属性的值都会使用查询来返回结果。如果需要处理多进程并发处理时,也许需要这样。当然也可以使用事务(transactions),这不是默认 的。
  8. registry SQLObject使用字符串来连接一个类,且这些类必须避开模块名,而又是又会出现在不同的系统中的命名冲突。这个属性提供了类的命名空间。
  9. fromDatabase 布尔值,缺省为False。如果为True,创建一个类时会自动查询数据库的各个列,如果有缺失的列则自动加上。
  10. columns 形如{columnName:anSOColInstance}形式的字典。可以通过只读属性获取列的信息。
  11. columnList columns的列表,用于需要一个有序而牢固的列信息列表时使用。
  12. columnDefinitions 类似columns的字典,但是包含列的原始定义信息。并非是特定类的,且没有逻辑。
  13. joins 这个类的所有联系对象。
  14. indexes 这个类的所有索引。
  15. createSQL 创建表之后的SQL查询。createSQL可以是单行的SQL命令或者是一些SQL命令组成的列表,或者是按照数据库名(dbNames)和值组成的字典,值可以是单行SQL或者是SQL列表。这经常用于ALTER TABLE命令来修改表定义。
  16. expired 布尔值。如果为True,那么下次存取对象列属性时,将会执行查询。

在上一版本的SQLObject中这些属性是作为类的属性而直接存在的,属性名前加上一个下划线。现在推荐将代码改成新的样式(style)。这些旧的方法在SQLObject 0.8释出时将不再支持。

注意:当继承SQLObject时,sqlmeta属性不会继承。也无法通过sqlmeta.columns词典访问父类列对象。

18   使用sqlmeta

按照如下代码:

class MyClass(SQLObject):
    class sqlmeta:
        lazyUpdate=True
        cacheValues=False
    columnA=StringCol()
    columnB=IntCol()
    def _set_attr1(self,value):
        #设置值时需要做的事情
    def _get_attr1(self):
        #取得值时需要作的事情

如上定义将会创建表my_class(表名在更改style属性时会有所不同),包含两个列columnA和columnB。还有第三个可以被存取 的属性MyClass.attr1。sqlmeta改变了MyClass的行为,可以延迟更新,并告知不要使用缓存,所以每次请求信息时都会查询数据库。

19   SQLObject

除了sqlmeta和列规范之外,其他的特定属性可以设置在类中。这些属性可以用除了_connection属性之外都可以在sqlmeta中定义。如果在SQLObject 0.7中使用,可以得到不赞成使用的警告。最好及时修改代码来保持对未来的兼容。

  1. _connection 使用的连接对象,从DBConnection类实现。也可以在包定义中使用__connection__供使用,但是必须确保在类定义之前定义 __connection__。也可以在实例创建时传递connection对象,有如transaction中描述的那样。如果已经定义了 sqlhub.processConnection,则会忽略这个属性而使用sqlhub。如果只有少数几个类使用相同的连接是有好处的,除了 (besides)使用了多种类型。
  2. _table 这是旧样式(style)的属性,等同于sqlmeta类中的table属性。
  3. _joins 同上,对应于sqlmeta的joins属性。
  4. _cacheValues 同上,对应于sqlmeta的cacheValues属性。
  5. _idName 同上,对应于sqlmeta的idName属性。
  6. _style 同上,对应于sqlmeta的style属性。

20   自定义对象

自定义类往往需要一些自定义方法,但是需要注意一些细节。

初始化对象:

有两种方式实例化SQLObject对象,从数据库引出和插入数据库。相同的是都要创建Python对象。这导致了__init__的微笑差异。

一般来说,不需要更改__init__。作为替换的_init方法,可以在引出或插入之后执行。方法定义如同_init(self,id, connection=None,selectResults=None),也许你喜欢使用_init(self,*args,**kw)的形式。注意, 如果重载方法,一定要调用SQLObject._init(self,*args,**kw)。

添加魔术属性:

你可以使用任何已有的技术来定义这种新样式中的方法,包括classmethod(类方法),static(静态方法),和property(属 性),而且还可以使用捷径。比如你有一个方法名以_set_、_get_、_del_、_doc_开始,它将会被用于创建属性。所以,如果对应 Person的ID下包含图像在/var/people/images目录下,可以使用:

class Person(SQLObject):
    # ...
    def imageFilename(self):
        return 'images/person-%s.jpg'%self.id
    def _get_image(self):
        if not os.path.exists(self.imageFilename()):
            return None
        f=open(self.imageFilename())
        v=f.read()
        f.close()
        return v
    def _set_image(self,value):
        f=open(self.imageFilename(),'w')
        f.write(value)
        f.close()
    def _del_image(self,value):
        os.unlink(self.imageFilename())

然后,可以像使用普通属性(attribute)一样使用.image属性(property)。对它的修改也会直接反映到文件系统当中。这是保存无意义数据的好方法。

同样也可以传递一个image关键字参数到构造方法或者set方法,形如Person(…,image=imageText)。所有的方法 (_get_、_set_等)都是可选的,你可以使用其中的任何一个而省略其他的。这样如果只定义了_get_attr方法,那么attr属性就是只读 的。

重载列属性:

重载数据库列属性时有些复杂。例如(for instance),想要在改变一个人名字的时候执行特定代码。在大多数系统中你需要自己实现这个功能,然后再调用父类的代码。但是父类(SQLObject)并不知道子类的列。

SQLObject创建了形如_set_lastName的方法定义你的列,当时当你再次想要使用时却发现父类没有相关引用(即不可以写 SQLObject._set_lastName(…),因为SQLObject类并不知道你的类的列)。你需要自己重载_set_lastName 方法。

为了处理这种问题,SQLObjec类创建了两个方法作为getter和setter,例如:_set_lastName和_SO_set_lastName。所以可以截获所有对lastName的更改:

class Person(SQLObject):
    lastName=StringCol()
    firstName=StringCol()
    def _set_lastName(self,value):
        self.notifyLastNameChange(value)
        self._SO_set_lastName(value)

或者你可能想要包含电话号码的数字,需要限制长度,可以按照如下格式:

import re
class PhoneNumber(SQLObject):
    phoneNumber=StringCol(length=30)
    _garbageCharactersRE=re.compile(r'[\-\.\(\) ]')
    _phoneNumberRE=re.compile(r'^[0-9]+$')
    def _set_phoneNumber(self,value):
        value=self._garbageCharactersRE.sub('',value)
        if not len(value)>=10:
            raise ValueError(
                'Phone numbers must be at least 10 digits long')
        if not self._phoneNumberRE.match(value):
            raise ValueError,'Phone numbers can contain only digits'
        self._SO_set_phoneNumber(value)
    def _get_phoneNumber(self):
        value=self._SO_get_phoneNumber()
        number='(%s) %s-%s'%(value[0:3],value[3:6],value[6:10])
        if len(value) > 10:
            number+=' ext.%s'%value[10:]
        return number

在修改从属性中获得的数据时必须小心。有些时候,人们希望设置与返回的值相同。这个例子中我们在存入数据库之前去除了一些字符,并在取出的时候重新格式化了。这个方法(反对存取属性)的优点之一是程序员往往希望将这些分开。

当然,还应该注意,这些转换在存入和取出时都会发生,但是在查询的时候却不会发生。所以如果你将值从Pythonic形式转换为SQLish形式 时,你的查询(当使用.select()或者.selectBy()方法)需要使用SQL/Database形式(因为这些命令是按照SQL来在数据库上 运行的)。

取消属性定义(Undefined attributes)

还有一个有用的特性,因为你有时需要返回奇怪的结果。SQLObject在你设置一个未定义的属性时不会跑出异常;这很好解释,并且不会改变数据库。他的工作方式有如其他Python类一样,但是在SQLObject类中却没有。

这在有些时候可能会出问题,如果你已经有了一个’name’属性,而你却写了’a.namme=”Victor”‘,这时不会跑出异常,但是却是错误的。

21   参考手册(Reference)

上面的信息可以让你快速进入工作,下面的信息让你定义更加完整。

22   Col类,定义列

列的列表是Col对象的列表。这些对象本身并没有功能,用于定义列。

  1. dbName 数据库的列名,如果不指定你指定的Python名称将会从大小写混用的形式转换到小写加下划线的形式。
  2. default 列的缺省值,在创建一个新行时使用。如果指定了一个可调用对象或函数,将会调用这个函数,并且使用其返回值。所以你可以使用 DateTimeCol.now作为当前时间的缺省值。或者你可以使用sqlbuilder.func.NOW()来设置数据库使用NOW()内部函数。 如果你不指定缺省值,则在调用这个记录的new时会抛出异常。
  3. alternateID 这是一个布尔型变量,缺省为False。指定列是否作为ID属性,例如用户名,尽管并不一定是主键。如果是这样,会添加一个类方法,例如 byUsername将会返回这个对象,使用laternateMethodName,当你希望使用by*类似的名称时,例如 alternateMethodName=”username”。这个列将会被声明为UNIQUE。
  4. unique 如果为True,当SQLObject创建一个表格时,将会指定这个列为UNIQUE。
  5. notNone 如果为True,则这个列不允许使用空值,用于创建表格。
  6. sqlType 这个列的SQL类型,例如INT、BOOLEAN等。你可以使用下面的类来定义,但是有时候使用sqlType更容易一些。仅在SQLObject创建表格时有效。

23   列类型

ForeignKey类可以替换Col来使用,当列是其他表的外键时。一般使用方法如ForeignKey(‘Role’),在这个列子中创建了一 个到表Role的引用。这基本等同于Col(foreignKey=’Role’,ssqlType=’INT’)。这会创建两个属性,role,会返回 Role的实例;roleID会返回与之关联的role的整数ID。

Col还有一些其他子类,用于SQLObject创建表格时指示列的类型。

  1. BLOBCol 二进制数据列,目前只能在MySQL、PostgreSQL、SQLite中使用。

  2. BoolCol 创建一个BOOLEAN列在Postgre,或者INT在其他数据库中。而且会将”t”/”f”或者0/1转换到数据库后端。

  3. CurrencyCol 等同于DecimalCol(size=10,precision=2)。注意DecimalCol可能不会返回正确的值,这个列可能共享一些行为。注意阅读DecimalCol的注意事项。

  4. DateTimeCol 日期时间,一般返回datetime或mxDateTime对象。

  5. DateCol 一个日期对象,一般返回datetime或mxDateTime对象。

  6. TimeCol 一个日期对象,一般返回datetime或mxDateTime对象。

  7. DecimalCol 以10为基础的,正确数据,使用关键字参数size指定存储的数字位数,precision指定小数点位数。警告:偶尔会发生在DecimalCol值, 尽管正确的存入数据库,但是可能返回浮点数而不是decimals。你可以自己测试一下,也可以试着导入Decimal类型,在你的数据库适配器导入 SQLObject之前。

  8. EnumCol 枚举类型,包含有限个数的字符串值。给出列表中可能的字符串,依靠enumValues关键字参数。MySQL有内置的本地ENUM类型,但是在其他数据库中也可以工作,只不过效率上并不占优势。

  9. FloatCol 浮点数

  10. ForeignKey 其他表/类的外键,例如user=ForeignKey(‘User’)

  11. IntCol 整数

  12. PickleCol 一种扩展的BLOBCol,这个列可以存储/取出任何Python对象;实际上是使用了Python的pickle来对对象串行化的压缩和解压缩的,而最终存取的是字符串。

  13. StringCol 一个字符串列。String(character)列。扩展关键字如下:

    length:如果给定了则字段类似于VARCHAR(length)。如果未指定则使用TEXT字段类型。

    varchar:如果包含了length,则用于区别CHAR和VARCHAR,缺省为True,使用VARCHAR。

  14. UnicodeCol StringCol的子类,接受dbEncoding关键字参数,缺省为”UTF-8″。其值在存取数据库的过程中被编码和解码。在使用UnicodeCol进行查询时有些限制:

    只有简单的q-magic字段支持,不允许表达式;只支持”==”和”<>”操作符。

    如下为示例代码:

    MyTable.select(u'value'==MyTable.q.name)
    MyTable.select(MyTable.q.name<>u'value')
    MyTable.select(OR(MyTable.q.col1==u'value1',MyTable.q.col2<>u'value2'))
    MyTable.selectBy(name=u'value')
    MyTable.selectBy(col1=u'value1',col2=u'value2')
    MyTable.byCol1(u'value1') #假设col1是一个alternetID字段

    如下为错误代码:

    MyTable.select((MyTable.q.name+MyTable.q.surname)==u'value')

    如下情况,必须先转换编码:

    MyTable.select((MyTable.q.name+MyTable.q.surname)==u'value'.encode(dbEncoding))

24   两个类/表之间的关系

必须使用ForeignKey来处理表的外键,后台实际使用连接(join)。

25   多连接和SQL多连接:一对多

查看”One-to-Many关系”查看一对多连接的例子。

多连接(MultipleJoin)返回结果类表,而SQLMultipleJoin返回SelectResults对象。

少数关键字参数允许MultipleJoin构造器:

joinColumn:

列名

在基于Pylons的服务器上测试使用DBUtils前后的性能对比

Sunday, September 30th, 2007

在基于Pylons的服务器上测试使用DBUtils前后的性能

目录

为了测试使用DBUtils实现的数据库连接池的性能,在Pylons写了一个测试服务,并将服务器架设在lab2服务器上,使用apache ab进行服务器压力测试。

1   测试环境

lab2服务器,MySQL 4.1.20,Pylons 0.9.4.1,apache ab 2.0.59。

为了确保测试的可靠性,每次测试之前都重启服务器。

在Pylons上假设的应用有3个URL用于测试,分别如下:

URL 说明
/testdb/test1 不使用连接池,每次访问都建立对数据库的连接
/testdb/test2 使用DBUtils.PersistentDB连接池,线程专用连接
/testdb/test3 使用DBUtils.PooledDB连接池,线程间共享连接

测试代码如下:

from helloworld.lib.base import *
import time
import random
import MySQLdb
import DBUtils.PersistentDB
import DBUtils.PooledDB

conn_kwargs={'host':'192.168.1.239','user':'ro','passwd':'','db':'test','port':3306}
sql="""SELECT * FROM test_table WHERE id=%d"""
persist=DBUtils.PersistentDB.PersistentDB(dbapi=MySQLdb,maxusage=1000,**conn_kwargs)
pooled=DBUtils.PooledDB.PooledDB(dbapi=MySQLdb,maxusage=1000,**conn_kwargs)

def query(conn):
    cur=conn.cursor()
    cur.execute(sql%(random.randint(1,1000)))
    data=cur.fetchall()
    cur.close()
    return data

class TestdbController(BaseController):
    def index(self):
        return Response('index')

    def test1(self):
        conn=MySQLdb.connect(**conn_kwargs)
        data=query(conn)
        conn.close()
        return Response(str(data))

    def test2(self):
        conn=persist.connection()
        data=query(conn)
        conn.close()
        return Response(str(data))

    def test3(self):
        conn=pooled.connection()
        data=query(conn)
        conn.close()
        return Response(str(data))

2   10线程并发

一共10000次测试,测试所用命令如下:

./ab -n 10000 -c 10 http://192.168.1.239:5000/testdb/test*

测试结果如下:

测试目标 总时间 请求处理速度 平均处理时间 错误率 100% 99% 90% 50%
/test1 32.764 305.22 32.764 ms 10.32% 776 237 40 29
/test2 27.895 358.49 27.895 ms 10.00% 3032 222 31 22
/test3 29.513 338.83 29.513 ms 10.46% 3037 58 36 27

3   50线程并发

一共10000次测试,测试所用命令如下:

./ab -n 10000 -c 50 http://192.168.1.239:5000/testdb/test*

测试结果如下:

测试目标 总时间 请求处理速度 平均处理时间 错误率 100% 99% 90% 50%
/test1 32.786 305.00 163.932 ms 9.48% 21031 3048 49 31
/test2 27.884 358.62 139.424 ms 9.65% 9227 3032 33 22
/test3 29.256 341.81 146.281 ms 9.88% 3654 328 151 136

4   远程10线程并发

一共10000次测试,测试所用命令如下:

./ab -n 10000 -c 10 http://192.168.1.241:5000/testdb/test*

测试结果如下:

测试目标 总时间 请求处理速度 平均处理时间 错误率 100% 99% 90% 50%
/test1 24.891 401.75 24.891 ms 9.07% 3035 44 31 22
/test2 21.652 461.85 21.652 ms 9.86% 256 59 26 19
/test3 23.952 432.99 23.095 ms 9.59% 239 38 28 22

5   远程50线程并发

一共10000次测试,测试命令如下:

./ab -n 10000 -c 50 http://192.168.1.241:5000/testdb/test*

测试结果如下:

测试目标 总时间 请求处理速度 平均处理时间 错误率 100% 99% 90% 50%
/test1 24.915 401.36 124.575 ms 9.82% 9280 3033 53 27
/test2 21.521 464.66 107.607 ms 9.47% 9621 3022 32 20
/test3 22.808 438.45 114.038 ms 9.11% 9107 145 114 95

6   干扰因素

测试过程中发现,MySQL服务器的同时并发连接数一直没有超过10,所以在进行50线程并发操作时可能会出现一些干扰。

7   单线程测试

使用代码如下:

import time
import random
import MySQLdb
import DBUtils.PersistentDB
import DBUtils.PooledDB

conn_kwargs={'host':'192.168.1.239','user':'ro','passwd':'','db':'test','port':3306}
sql="""SELECT * FROM test_table WHERE id=%d"""
persist=DBUtils.PersistentDB.PersistentDB(dbapi=MySQLdb,maxusage=1000,**conn_kwargs)
pooled=DBUtils.PooledDB.PooledDB(dbapi=MySQLdb,maxusage=1000,**conn_kwargs)

def query(conn):
    cur=conn.cursor()
    cur.execute(sql%(random.randint(1,1000)))
    data=cur.fetchall()
    cur.close()
    return data

def print_now():
    print time.strftime("%H:%M:%S")
    return

def test1(times):
    print_now()
    for i in range(0,times):
        conn=MySQLdb.connect(**conn_kwargs)
        query(conn)
        conn.close()
    print_now()
    return

def test2(times):
    print_now()
    for i in range(0,times):
        conn=persist.connection()
        query(conn)
        conn.close()
    print_now()
    return

def test3(times):
    print_now()
    for i in range(0,times):
        conn=pooled.connection()
        query(conn)
        conn.close()
    print_now()
    return

8   单线程测试

执行10000次查询,进入Python交互模式,调用各个函数并传递执行次数,每次执行过后重启MySQL服务器:

# python -i ttss.py
>>> test1(10000)
18:59:30
18:59:40
>>> test2(10000)
19:00:16
19:00:19
>>> test3(10000)
19:00:46
19:00:49

可见查询次数太少,以致难以精确测定时间,所以执行100000次查询,过程如下:

# python -i ttss.py
>>> test1(100000)
19:01:57
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '192.168.1.239' (99)")

连续两次都出现异常,之后改为30000也是如此。出现这个异常之后数据库服务器不经过重启就无法再使用了。经过测试发生这种连接异常之后,还是可以使用mysql客户端登录本机的MySQL服务器的。所以改为20000次查询,过程如下:

# python -i ttss.py
>>> test1(20000)
19:06:47
19:07:07
>>> test2(20000)
19:28:23
19:28:28
>>> test3(20000)
19:29:27
19:29:34

测试远程连接MySQL服务器:

# python -i ttss.py
>>> test1(10000)
20:25:23
20:25:57
>>> test2(10000)
20:27:18
20:27:26
>>> test3(10000)
20:27:46
20:27:56

9   结论

总体上来看,使用了DBUtils之后数据库的访问性能有了很大的提高。

DBUtils超快速入门指南

Tuesday, September 11th, 2007

DBUtils超快速入门指南

版本: 0.9.2

目录

1   简介

DBUtils是一套Python数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。

DBUtils提供两种外部接口:

  • PersistentDB :提供线程专用的数据库连接,并自动管理连接。
  • PooledDB :提供线程间可共享的数据库连接,并自动管理连接。

实测证明 PersistentDB 的速度是最高的,但是在某些特殊情况下,数据库的连接过程可能异常缓慢,而此时的PooledDB则可以提供相对来说平均连接时间比较短的管理方式。

另外,实际使用的数据库驱动也有所依赖,比如SQLite数据库只能使用PersistentDB作连接池。

下载地址:

http://www.webwareforpython.org/downloads/DBUtils/

2   使用方法

连接池对象只初始化一次,一般可以作为模块级代码来确保。

PersistentDB的连接例子:

import DBUtils.PersistentDB
persist=DBUtils.PersistentDB.PersistentDB(dbpai=MySQLdb,maxusage=1000,**kwargs)

这里的参数dbpai指使用的底层数据库模块,兼容DB-API的。maxusage则为一个连接最大使用次数,参考了官方例子。后面的**kwargs则为实际传递给MySQLdb的参数。

获取连接:

conn=persist.connection()

实际编程中用过的连接直接关闭 conn.close() 即可将连接交还给连接池。

PooledDB使用方法同PersistentDB,只是参数有所不同。

  • dbapi :数据库接口
  • mincached :启动时开启的空连接数量
  • maxcached :连接池最大可用连接数量
  • maxshared :连接池最大可共享连接数量
  • maxconnections :最大允许连接数量
  • blocking :达到最大数量时是否阻塞
  • maxusage :单个连接最大复用次数
  • setsession :用于传递到数据库的准备会话,如 ["set name UTF-8"]

一个使用过程:

db=pooled.connection()
cur=db.cursor()
cur.execute(...)
res=cur.fetchone()
cur.close() # or del cur
db.close() # or del db

DBUtils 用户指南(更新至0.9.3)

Tuesday, September 11th, 2007

DBUtils 用户指南

版本: 0.9.3

目录

摘要

DBUtils 是一套允许线程化 Python 程序可以安全和有效的访问数据库的模块。DBUtils已经作为 Webware for Python 一部分用来结合 PyGreSQL 访问 PostgreSQL 数据库,当然他也可以用在其他Python应用程序中来访问 DB-API 2 兼容的数据库接口。

模块

DBUtils实际上是一个包含两个子模块的Python包,一个用于连接DB-API 2模块,另一个用于连接典型的PyGreSQL模块。

全局的DB-API 2变量
SteadyDB.py 用于稳定数据库连接
PooledDB.py 连接池
PersistentDB.py 维持持续的数据库连接
SimplePooledDB.py 简单连接池
典型的 PyGreSQL 变量
SteadyPg.py 稳定PyGreSQL连接
PooledPg.py PyGreSQL连接池
PersistentPg.py 维持持续的PyGreSQL连接
SimplePooledPg.py 简单的PyGreSQL连接池

对标准DB-API 2模块的依赖如下图所示:

dbdep.gif对典型的PyGreSQL模块依赖如下图所示:

pgdep.gif

下载

你可以从 Webware 的网站下载最新版本:

http://www.webwareforpython.org/downloads/DBUtils/

也可以从Python Package Index来下载:

http://www.python.org/pypi/DBUtils/

安装

安装为顶层模块

如果你打算在除了Webware之外的程序中使用,推荐安装为顶层模块:

python setup.py install

安装为Webware的子模块(插件)

如果你只是打算在Webware中使用,则可以按照如下安装:

python setup.py install --install-lib=/path/to/Webware

替换 /path/to/Webware 为Webware安装的根路径。你还需要运行Webware的安装程序来同时包含DBUtils的文档:

cd path/to/Webware
python install.py

功能

这一节的主要例子面向DB-API 2,但是也适用于典型的PyGreSQL模块。

SimplePooledDB

DBUtils.SimplePooledDB 是一个非常简单的数据库连接池实现。他比完善的 PooledDB 模块缺少很多功能。 DBUtils.SimplePooledDB 本质上类似于 MiscUtils.DBPool 这个Webware的组成部分。你可以把它看作一种演示程序。

SteadyDB

DBUtils.SteadyDB 是一个模块实现了”强硬”的数据库连接,基于DB-API 2建立的原始连接。一个”强硬”的连接意味着在连接关闭之后,或者使用次数操作限制时会重新连接。

一个典型的例子是数据库重启时,而你的程序仍然在运行并需要访问数据库,或者当你的程序连接了一个防火墙后面的远程数据库,而防火墙重启时丢失了状态时。

一般来说你不需要直接使用 SteadyDB 它只是给接下来的两个模块提供基本服务, PersistentDBPooledDB

PersistentDB

DBUtils.PersistentDB 实现了强硬的、线程安全的、顽固的数据库连接,使用DB-API 2模块。如下图展示了使用 PersistentDB 时的连接层步骤:

persist.gif当一个线程首次打开一个数据库连接时,一个连接会打开并仅供这个线程使用。当线程关闭连接时,连接仍然持续打开供这个线程下次请求时使用这个已经打开的连接。连接在线程死亡时自动关闭。

简单的来说 PersistentDB 尝试重用数据库连接来提高线程化程序的数据库访问性能,并且他确保连接不会被线程之间共享。

因此, PersistentDB 可以在底层DB-API模块并非线程安全的时候同样工作的很好,并且他会在其他线程改变数据库会话或者使用多语句事务时同样避免问题的发生。

PooledDB

DBUtils.PooledDB 实现了一个强硬的、线程安全的、有缓存的、可复用的数据库连接,使用任何DB-API 2模块。如下图展示了使用 PooledDB 时的工作流程:

pool.gif如图所示 PooledDB 可以在不同线程之间共享打开的数据库连接。这在你连接并指定 maxshared 参数,并且底层的DB-API 2接口是线程安全才可以,但是你仍然可以使用专用数据库连接而不在线程之间共享连接。除了共享连接以外,还可以设立一个至少 mincached 的连接池,并且最多允许使用 maxcached 个连接,这可以同时用于专用和共享连接池。当一个线程关闭了一个非共享连接,则会返还到空闲连接池中等待下次使用。

如果底层DB-API模块是非线程安全的,线程锁会确保使用 PooledDB 是线程安全的。所以你并不需要为此担心,但是你在使用专用连接来改变数据库会话或执行多命令事务时必须小心。

该选择哪一个?

PersistentDBPooledDB 都是为了重用数据库连接来提高性能,并保持数据库的稳定性。

所以选择何种模块,可以参考上面的解释。 PersistentDB 将会保持一定数量的连接供频繁使用。在这种情况下你总是保持固定数量的连接。如果你的程序频繁的启动和关闭线程,最好使用 PooledDB 。后面将会提到更好的调整,尤其在使用线程安全的DB-API 2模块时。

当然,这两个模块的接口是很相似的,你可以方便的在他们之间转换,并查看哪个更好一些。

使用方法

所有模块的使用方法都很相似,但是在初始化 “Pooled” 和 “Persistent” 时还有有些不同,尤其是DB-API和PyGreSQL之间。

这里只讲解 PersistentDB 和更复杂的 PooledDB 模块。其他模块的细节请参与其文档。使用Python解释器控制台,你可以显示 PooledDB 的文档,如下:

help(PooledDB)

PersistentDB

为了使用 PersistentDB 你首先需要通过创建 PersistentDB 的实例来设置一个特定数据库连接的生成器,床底如下参数:

  • dbapi: 需要使用的DB-API 2兼容的数据库模块
  • maxusage: 一个连接最大允许复用次数(缺省为 0False 意味着无限制的重用),当达到限制时,将会重新连接数据库
  • setsession: 一个可选的SQL命令列表可以用于准备会话,如 ["set datestyle to german", ...]
  • 其他的,你还可以传递用于传递给真实的DB-API 2模块的参数,例如主机名、数据库、用户名、密码等。

举个例子,如果你正在使用 pgdb 作为数据库模块并想要连接本机数据库 mydb ,允许重用1000次:

import pgdb # import used DB-API 2 module
from PersistentDB import PersistentDB
persist = PersistentDB(pgdb, 1000, database='mydb')

按照如上设置完成了连接生成器之后,你可以按照如下来请求一个连接:

db = persist.connection()

你可以使用这些连接就像使用原始的DB-API 2连接一样。实际上你得到的是一个通过SteadyDB得到的强硬的连接,基于DB-API 2。

关闭一个强硬的连接使用 db.close() ,这在内部实际上被忽略掉了,并且供下次使用。在线程关闭时,也会自动关闭数据库连接。你可以改变这个行为通过 persist._closeableTrue

PooledDB

为了使用 PooledDB 模块,你首先需要通过创建 PooledDB 来设置数据库连接池,传递如下参数:

  • dbapi: 需要使用的DB-API 2模块
  • mincached : 启动时开启的空连接数量(缺省值 0 意味着开始时不创建连接)
  • maxcached: 连接池使用的最多连接数量(缺省值 0 代表不限制连接池大小)
  • maxshared: 最大允许的共享连接数量(缺省值 0 代表所有连接都是专用的)如果达到了最大数量,被请求为共享的连接将会被共享使用。
  • maxconnections: 最大允许连接数量(缺省值 0 代表不限制)
  • blocking: 设置在达到最大数量时的行为(缺省值 0False 代表返回一个错误;其他代表阻塞直到连接数减少)
  • maxusage: 单个连接的最大允许复用次数(缺省值 0False 代表不限制的复用)。当达到最大数值时,连接会自动重新连接(关闭和重新打开)
  • setsession: 一个可选的SQL命令列表用于准备每个会话,如 ["set datestyle to german", ...]
  • 其他,你可以设置用于传递到真正的DB-API 2的参数,例如主机名、数据库、用户名、密码等。

举个例子,如果你正在使用 pgdb 作为DB-API模块,并希望连接池中至少有5个连接到数据库 mydb

import pgdb # import used DB-API 2 module
from PooledDB import PooledDB
pool = PooledPg(pgdb, 5, database='mydb')

一旦设置好了连接池,你就可以按照如下请求一个连接:

db = pool.connection()

你可以使用这些连接有如原始的DB-API 2一样。而实际使用的是SteadyDB版本的强硬连接。

请注意连接可以与其他线程共享,只要你设置 maxshared 参数为非零,并且DB-API 2模块也允许。如果你想要使用专用连接则使用:

db = pool.connection(0)

如果你不再需要这个连接了,则可以返回给连接池使用 db.close() 。你也可以使用相同的方法获取另一个连接。

警告: 在一个多线程环境,不要使用下面的方法:

pool.connection().cursor().execute(...)

这将会导致过早的释放连接以供复用,而且如果是非线程安全还会出错。确保连接对象在你的使用过程中是一直存在的,例如:

db = pool.connection()
cur = db.cursor()
cur.execute(...)
res = cur.fetchone()
cur.close() # or del cur
db.close() # or del db

在Webware中使用

如果你正在 Webware for Python 的 servlets 中使用DBUtils来存取数据库,你要确保数据库连接生成器只被应用启动一次,而不是每个servlet启动时都创建一个。为了达到这个目的,你可以在模块或类的初始化代码中添加这些代码,或者使用 __init__.py 中的 contextInitialize() 函数。

目录 Examples 是DBUtils发行包的一部分,包含了一个使用示例数据库的Webware的例子,用来跟踪演讲会的出席者(这个例子的主意来自Andrew Kuchling的 “The Python DB-API“)。

例子的正文可以通过创建配置文件 Configs/Database.config 来配置,改变例子 Examples/DBUtilsExample.py 的缺省参数。这种方式可以设置一个专用数据库的用户名和密码,你也可以选择底层的数据库模块。如果设置了 maxcached ,则例子会使用 “Pooled” 模块,否则会使用 “Persistent” 模块。

注意

如果你正在使用流行的ORM SQLObject ,你并不需要使用DBUtiils,因为他已经内含连接池了。 SQLObject 2 (SQL-API) 事实上还从DBUtils这里借用了连接池分层的代码。

未来功能

一些未来会使用的方法:

  • 一个连接最大被使用的次数,或一个连接最大活动时间。
  • 创建模块 MonitorDBMonitorPg 运行在单独的线程中,监控连接池中各个共享连接的状态。如果检测到一个损坏的连接,则会自动恢复这个连接。这在很多网站中是很实用的,因为晚上往往要重启数据库服务器。如果不使用监控线程,则用户要等到第二天早上才可以使用。正是因为如此,检测损坏的连接并自动恢复是很有用的。使用了监控线程之后,间断时间在晚上,而且很短。监控线程同样可以配置连接生成器的线程池,并且确保用户到达之前完成。
  • 可选的日志,记录损坏的连接和最大限制。

错误报告与回馈

请将错误报告、补丁、回馈直接发送给作者(使用下面给出的邮件地址)。

如果有Webware相关的问题,可以到邮件列表讨论 Webware for Python mailing list

链接

一些相关软件的链接:

作者列表

作者: Christoph Zwerschke <cito@online.de>

版权与许可

Copyright @ 2005-2006 by Christoph Zwerschke. All Rights Reserved.

DBUtils是一个自由开源软件,使用 Open Software License version 2.1 许可。

pysqlite使用指南

Tuesday, September 11th, 2007

pysqlite使用指南

最后更新: pysqlite 2.3.0

目录

1 简介

本使用指南并非Python、SQL、SQLite的入门指南;而是pysqlite功能的介绍,并且包含了使用模式的示例代码。这个指南将会指导你使用Python的数据库接口和SQLite的文档。

2 Python数据库接口2.0兼容

2.1 不兼容的选项

pysqlite包含不兼容的DB API 2.0的功能,如下:

  • 在cursor.description中并不包含类型信息

    cursor.description是一个元组包含如下字段(name,type_code,display_size,internal_size,precision,scale,null_ok)来定义每个列。特定的数据库接口需要至少name和type_code,但是有时cursor.description是内置的,pysqlite不只能检测到任何类型。所以,pysqlite只在cursor.description中填入name字段。其他的字段全部设置为None。

  • 没有类型对象

    同样的,模块级并没有STRING、BINARY、NUMBER、DATETIME、ROWID,并且也没什么用。

2.2 不支持的可选功能

Cursor
不支持nextset方法。这个方法没有实现是因为数据库引擎也没有实现单个游标返回多个结果集。

2.3 有名无实支持的可选功能

Cursor

  • arraysize 属性

    作为最基本的需求,这个属性的值用于支持 fetchmany 方法。不过改变这个值也没什么关系,因为数据库引擎根本就只支持一次获取一行。

  • setinputsizes 方法

    尽管提供了这个方法,但是什么都不做。

  • setoutputsize 方法

    什么活都不干的方法。

2.4 扩展与警告

pysqlite提供了大量的功能来实现对Python DB API的基本支持。大部分扩展在已经在本节的Native Database Engine Features and Extensions Beyond the Python DB API中有所介绍。

  • connect 函数

    参数 database 指定SQLite数据库的文件。一般指定文件系统的绝对或相对路径。

    这个链接函数支持如下可选参数:

    • timeout 当多个连接同时存取数据库时,并且其中一个进程修改了数据库,SQLite数据库会锁定(locked)直到事务被提交。这个超时参数指定连接会等待这个锁定多长时间,并抛出异常。缺省的超时参数是5.0秒。例如:
      sqlite.connect(database="mydb",timeout=10.0)
    • isolation_level pysqlite将会缺省的以”BEGIN”语句开始一个事务,当使用DML语句如INSERT/UPDATE/DELETE/REPLACE。一些用户并不想pysqlite暗自打开事务,而希望使用自动提交模式。其他用户想要pysqlite打开不同类型的事务,例如”BEGIN IMMEDIATE”。查看 控制事务 了解更多细节。注意你通过设置isolation_level属性可以选择不同的isolation级别。例如:
      # 打开自动提交模式
      con=sqlite.connect("mydb",isolation_level=None)
      # 将isolation_level设置到"IMMEDIATE"
      con.isolation_level="IMMEDIATE"
    • detect_types SQLite本来支持的类型包括TEXT、INTEGER、FLOAT、BLOB、NULL。如果你想使用其他类型,你需要自己添加支持。 detect_types 参数和使用自定义的 converters 来使用 register_converter 函数允许你做到这些。 detect_types 缺省是0,就是关闭,你可以设置 PARSE_DECLTYPES 和 PARSE_COLNAMES 的组合来改变设置的类型。参见 SQLite与Python类型 来了解更多。
      • sqlite.PARSE_DECLTYPES 这会

Warning

pause

3 简要入门

这个教会你如何初步的使用pysqlite。而并非是一个Python Database API入门,也不是覆盖其他使用的方面。

3.1 连接到数据库

例子1

连接到数据库文件 mydb

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")

例子2

创建一个内存数据库:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect(":memory:")

3.2 执行SQL语句

为了执行这一节,我们需要一个数据库按照如下定义:

CREATE TABLE people
(
    name_last   varchar(20),
    age         integer
);
INSERT INTO people (name_last,age) VALUES ('Yeltsin',72);
INSERT INTO people (name_last,age) VALUES ('Putin',51);

例子1

这个例子显示了一个打印 people 表格内容的最简单的例子:

from pysqlite2 import dbapi2 as sqlite
# 创建数据库连接到文件"mydb"
con=sqlite.connect("mydb")
# 获取游标对象
cur=con.cursor()
# 执行SELECT语句
cur.execute("SELECT * FROM people ORDER BY age")
# 获取所有行并显示
print cur.fetchall()

输出:

[(u'Putin', 51), (u'Yeltsin', 72)]

例子2

如下是另一个小例子展示了如何单行显示记录:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
cur=con.cursor()
SELECT="SELECT name_last,age FROM people ORDER BY age, name_last"
# 1. 第一种显示记录的方法
cur.execute(SELECT)
for (name_last,age) in cur:
    print '%s is %d years old.'%(name_last,age)
# 2. 第二种显示记录的方法
cur.execute(SELECT)
for row in cur:
    print '%s is %d years old.'%(row[0], row[1])

输出:

Putin is 51 years old.
Yeltsin is 72 years old.
Putin is 51 years old.
Yeltsin is 72 years old.

例子3

如下的程序以表格的方式打印表格内容:

from pysqlite2 import dbapi2 as sqlite
FIELD_MAX_WIDTH=20
TABLE_NAME='people'
SELECT="SELECT * FROM %s ORDER BY age,name_last"%TABLE_NAME
con=sqlite.connect("mydb")
cur=con.cursor()
cur.execute(SELECT)
#打印表头
for fieldDesc in cur.description:
    print fieldDesc[0].ljust(FIELD_MAX_WIDTH),
print #结束表头行
print '-'*78

#每行打印一次值
fieldIndices=range(len(cur.description))
for row in cur:
    for fieldIndex in fieldIndices:
        fieldValue=str(row[fieldIndex])
        print fieldValue.ljust(FIELD_MAX_WIDTH),
    print

输出:

name_last               age
---------------------------------------------
Putin                   51
Yeltsin                 72

例子4

插入人员信息到 people 表格:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
cur=con.cursor()
newPeople=(
    ('Lebed',53),
    ('Zhirinovsky',57),
)
for person in newPeople:
    cur.execute("INSERT INTO people (name_last,age) VALUES (?,?)",person)
#修改之后必须明确的提交
con.commit()

注意参数化的SQL语句。当处理重复语句时,这会更快并产生更少的错误,相对于手动生成SQL语句。

而上面的核心语句:

for person in newPeople:
    cur.execute("INSERT INTO people (name_last,age) VALUES (?,?)",person)

可以被重写为:

cur.executemany("INSERT INTO people (name_last,age) VALUES (?,?)",newPeople)

在例子4的后面的打印结果为:

name_last                 age
-------------------------------------------
Putin                     51
Lebed                     53
Zhirinovsky               57
Yeltsin                   72

4 数据库引擎的本地功能和Python DB API的扩展功能

4.1 创建用户自定义函数

SQLite支持用户自定义函数。使用pysqlite,你使用连接的 create_function 方法来创建新函数:

def create_function(self,name,numparams,func)

如下是参数说明:

  • name

    SQL函数的名字

  • numparams

    函数接收的参数数量,-1是接收不限制数量的参数

  • func

    Python函数

函数可以返回任何pysqlite支持的SQLite类型:unicode、str、int、long、float、buffer和None。任何用户自定义函数中的异常都会导致SQL语句的执行中断。

例子:

from pysqlite2 import dbapi2 as sqlite
import md5
def md5sum(t):
    return md5.md5(t).hexdigest()
con=sqlite.connect(":memory:")
con.create_function("md5",1,md5sum)
cur=con.cursor()
cur.execute("SELECT md5(?)", ("foo",))
print cur.fetchone()[0]

4.2 创建用户自定义聚合体类型

SQLite支持用户自定义聚合类型函数。使用

Warning

pause

4.3 创建和使用对比函数

Warning

pause

4.4 检查语句完整性

Warning

pause

4.5 启用SQLite的共享缓存

Warning

pause

4.6 设置认证回调函数

Warning

pause

5 SQLite与Python类型

5.1 介绍

详见 http://sqlite.org/datatype3.html

Warning

pause

5.2 使用Python类型的SQLite数据库存储适配器

Warning

pause

5.3 转换SQLite值到Python类型

Warning

pause

5.4 缺省的pysqlite适配器和转换器

Warning

pause

6 控制事务

缺省时,pysqlite在DML语句(INSERT/UPDATE/DELETE/REPLACE)语句之前自动开启一个事务,并且在一个非DML语句或非DQL语句之前自动提交(除了SELECT/INSERT/UPDATE/DELETE/REPLACE)语句之外。

所以如果你在一个事务当中,要执行类似 CREATE TABLE … , VACUUM , PRAGMA 之类的命令,则pysqlite会在执行这些命令之前提交。有两个原因需要这么做。首先,大多数此类命令并不是工作在事务当中的。第二个原因是pysqlite需要保持对事务状态的跟踪,无论事务处于活跃状态与否。

你可以自己控制何种类型的”BEGIN”语句会自动执行(没有,或者所有),通过设置 connect 的 isolation_level 参数,或者连接对象的 isolation_level 属性。

如果你想要开启 autocommit 模式,设置 isolation_level 为None。

否则让这个值为缺省,将会使用平坦的”BEGIN”语句。或者设置一个SQLite支持的隔离(isolation)级别:DEFERRED(延时)、IMMEDIATE(立即)或EXCLUSIVE(独占)。

因为pysqlite需要对事务状态进行跟踪,所以你将不能使用 OR ROLLBACK 或 ON CONFLICT ROLLBACK 。作为交换,捕捉 IntegrityError 异常并手动调用连接的 rollback 方法。

7 高效的使用pysqlite

7.1 使用命令的捷径

使用非标准的连接的 execute() 、 executemany() 、 executescript() 方法,你的代码可以更加简明,因为你不需要再创建多余的游标对象了。作为交换,游标对象会隐含的创建并使用。这种方法,你可以直接在对象之上使用SELECT语句并使用序列方法。:

from pysqlite2 import dbapi2 as sqlite
persons=[
    ("Hugo","Boss"),
    ("Calvin","Klein")
    ]
con=sqlite.connect(":memory:")
#创建表格
con.execute("CREATE TABLE person (firstname,lastname)")
#填写表格
con.executemany("INSERT INTO person(firstname,lastname) VALUES (?,?)",
    persons)
#打印表格内容
for row in con.execute("SELECT firstname,lastname FROM person"):
    print row
#使用一个哑WHERE子句来让SQLite删除表格
print "I just deleted",con.execute("DELETE FROM person WHERE 1=1").rowcount,"rows"

7.2 使用列名而不是序号存取字段

一个pysqlite2.1.0的新功能是新的内建的sqlite.Row类设计作为记录工厂。 Rows包装了这个类并允许同时使用列序号(类似元组)和大小写不敏感的列名:

from pysqlite2 import dbapi2 as sqlite
con=sqlite.connect("mydb")
con.row_factory=sqlite.Row
cur=con.cursor()
cur.execute("SELECT name_last,age FROM people")
for row in cur:
    assert row[0] == row["name_last"]
    assert row["name_last"] == row["nAmE_lAsT"]
    assert row[1] == row["age"]
    assert row[1] == row["Age"]

Firebird笔记

Monday, September 10th, 2007

Firebird笔记

目录

1   选用理由

我正在开发一个爬虫,原来使用的SQLite数据库的多线程并发操作很麻烦,我一直都没能找到比较好的方法来避免数据库锁定,所以在尝试了一个多月之后我最终放弃了。放弃之后使用了BerkeleyDB作为数据库,自己写接口。运行过程尽管还算很快,但是也有问题,就是多线程操作BerkeleyDB时会让Python解释器出现段错误而退出,这也是无法忍受的错误。

所以在权衡之后我最终还是决定捡起一度放弃的Firebird数据库。决定未来将其集成到我的爬虫当中。另外,我的另外一个产品也需要使用足够健壮的数据库服务器。

关于MySQL,我还是很信赖其稳定性的,使用感受也不错,但是在我的程序中并不需要复杂的权限控制,而且通过网络连接数据库的安全性和速度也让我很讨厌。所以选用了Firebird的嵌入式版本。

2   与Python的结合使用

Firebird的Python接口叫做KinterbasDB,支持所有版本的Firebird和部分版本的Interbase。尽管支持,但是对1.0、1.5、2.0版本的Firebird是使用不同的安装包来支持的。

在Windows 2000系统之下我使用了 kinterbasdb-3.2.win32-FB-2.0-py2.4.exe 来进行安装,版本为3.2,对应Firebird版本为2.0。双击然后一路继续就完成了。

3   安装Firebird的嵌入式版本

因为考虑到未来程序的可移植性,所以我坚决避免使用Firebird的安装包进行安装。在安装完成 KinterbasDB 之后尝试在Python中导入:

>>> import kinterbasdb

发生了错误,提示找不到DLL文件,然后我使用了压缩包 Firebird-2.0.0.12748-0_embed_win32.zip 。对缺少的DLL文件依次从压缩包中解压出来并放置到目录 C:Python24Libsite-packageskinterbasdb 目录中。因为我发现这个目录是可以被路径识别的,并且跟 KinterbasDB 的关系密切。如下几个文件是导入语句所必须的:

  • fbclient.dll :最初叫做 fbembeb.dll 需要复制过来以后该名为新名字的,否则 KinterbasDB 是不认的。
  • icuuc30.dll :不知道干什么的,反正必须要有。
  • icudt30.dll :不知道干什么的。
  • icuin30.dll :不知道干什么的。

这四个动态链接库的大小就已经达到了 3.68MB ,不过面前还是可以接受的。

在这样一番折腾之后就可以成功的导入了,做了一下dir,结果超长,此处也就略掉吧。FB支持的功能非常多,包括触发器、事务、存储过程之类的,这些东西在开源数据库当中是非常少有的。

4   《Firebird嵌入版本开发方案》笔记

Firebird的官方网站( http://www.ibphoenix.com )。这里还推荐了一个叫UIB的东西,网站是( http://www.progdigy.com/UIB/ )。原来用于Interbase的可视化管理工具IBExpert ( http://www.ibexpert.com )从某种程度上来说也可以用于Firebird。

拒作者说IBExpert好像是有限制的,所以推荐去 ftp://hdkej.8800.org 下载一个修改过的版本,没有限制。运行IBExpert新建数据库时服务器选择Local,Client Library选择fbclient.dll。

看来UIB是给Delphi使用的一套接口,Python的就免了。作者的调试过程发现不能让两个进程同时访问数据库。

5   博客 http://tb.blog.csdn.net 的Firebird使用 笔记

推荐下载IBExpert而已,另外就是FB内置了isql.exe在控制台进行数据库的基本操作。

6   《python2.4 连接firebird1.5 一个连接数据库的文件》笔记

作者使用Python2.4和KinterbasDB连接了FB数据库。首先是导入:

import kinterbasdb as kdb

初始化连接并返回连接对象:

conn=kdb.connect(dsn='lq:D:\\data\\aaa.FDB',user='sysdba',password='masterkey')
conn.text_factory=str

连接选项的dsn用来指定数据源,用户名密码sysdba:masterkey是默认的。第二行是用来避免 “Could not decode to UTF-8 column” 错误的。

因为很多时候程序需要随身携带数据库,所以需要使用相对路径,文中给出了获取当前路径的方法:

import os
ypath=os.path.abspath(os.path.dirname(sys.argv[0]))

如果需要通过列名来返回值,则可以作如下设置:

conn.row_factory=kdb.Row

通过ConfigParser模块支持INI格式的配置文件,如下是某配置文件:

[SQLFbDB]
dbname=data\aaa.FDB

如下是处理配置文件的代码:

import ConfigParser
config=ConfirParser.ConfigParser()
config.read('config.txt')
dbname=config.get('SQLFbDB','dbname')

7   《本地数据源:使用firebird数据库》笔记

Windows下的Firebird有classical和superserver两种版本。配置文件aliases.conf配置数据库别名,firebird.conf配置数据库参数如rootpath。

嵌入的FB数据库不再支持多用户访问和用户安全控制,不过对于本机程序来说很正常。

在.net下使用时有个连接选项叫server type,需要设置为1才是嵌入FB数据库。

8   Python-Chinese邮件列表上朋友的回复

  1. 安装KInterbasDB,会产生一个 lib\site-package\kinterbasdb 目录。

  2. 在kinterbasdb目录下新建一个embedded目录,然后将嵌入式的Firebird中的 fbembed.dllfirebird.msgib_util.dll 这3个文件放入embedded目录下。如果需要国际化支持,则同时复制intl子目录,这个目录包含 fbintl.dll 文件。

  3. fbembed.dll 文件改名为 fbclient.dll

  4. 这时就可以使用FB数据库了。

  5. KInterbasDB为了向后兼容,默认使用 mx.DateTime 模块,但是在Python2.4下并不需要而且多余。在Python标准库中的datetime模块更好用。需要用datetime替代mx.DateTime则按照如下修改:

    import kinterbasdb
    kinterbasdb.init(type_conv=200)

实际测试发现如果把几个动态链接库放在 embedded 目录中,则会提示动态链接库找不到。

SQLAlchemy指南(tutorial)

Thursday, September 6th, 2007

SQLAlchemy指南(tutorial)

对应版本: 0.3.4

目录

这个入门指导用于SQLAlchemy的快速入门,并便利SQLAlchemy的简单功能。如果你可以跳过这一部分进入 主文档 会涉及更多内容。如下的例子全部是在Python交互模式下完成了,并且全部通过了 doctest 测试。

1 安装

1.1 安装SQLAlchemy

setuptools 安装是非常简单的,只要运行如下命令即可:

# easy_install SQLAlchemy

这将会在Python Cheese Shop获取SQLAlchemy的最新版本并安装。或者你也可以使用setup.py安装一个发行包:

# python setup.py install

1.2 安装一个数据库API

SQLAlchemy被设计用于操作一个 DBAPI 实现,包括大多数常见的数据库。如果你有一个支持DBAPI的实现,那么可以跳入下一节。另外SQLite是一个易于使用的数据库,可以快速开始,并且他可以使用内存数据库。如果要使用SQLite,你将会需要:

  • pysqlite – SQLite的Python接口
  • SQLite函数库

注意在Windows下并不需要SQLite函数库,因为Windows版的pysqlite已经内含了。pysqlite和SQLite可以被安装到Linux或FreeBSD,通过预编译或从源码安装。

预编译包的地址为:

http://initd.org/tracker/pysqlite/wiki/PysqlitePackages

2 快速开始

2.1 导入

SQLAlchemy提供了完整的命名空间,只要导入sqlalchemy即可,无需其子包。为了方便使用本教程,我们导入所有命名到本地命名空间:

>>> from sqlalchemy import *

2.2 连接到数据库

导入之后,下一步是连接到需要的数据库,表现为(represent)为一个Engine对象。这个对象处理了连接的管理和特定数据库的操作。下面,我们连接SQLite基于文件的数据库”tutorial.db”

>>> db=create_engine("sqlite:///tutorial.db")

创建数据库引擎的更多信息查看”Database Engines”。

3 SQLAlchemy是两个库的包装

现在已经完成了安装和连接数据库,可以开始做点实际的事情了。但首先需要有些解释。

SQLAlchemy的核心有两个完全不同的功能,一个在另一个之上工作。一个是 SQL语言构造器 ,另一个是 ORM 。SQL语言构造器允许调用 ClauseElements 来构造SQL表达式。这些 ClauseElements 可以在编译成字符串并绑定到数据库后用于执行,并返回一个叫做 ResultProxy 的对象,类似于一个结果集对象,但是更象dbapi高版本的 cursor 对象。

ORM是建立在SQL语言构造器之上的工具集,用于将Python对象映射到数据库的行,提供了一系列接口用于从数据库中存取对象(行)。在ORM工作时,在底层调用SQL语言构造器的API,这些通用的操作有些许的不同。不同的是,你不再使用行,而是使用自定义类的对象来操作。另外,数据库的查询方式也不同,ORM的可以生成大多数的SQL查询,除此之外还可以在类中定义更多操作。

SA功能强大,无与伦比,只是有两个混合在一起的方法有些复杂。有效的使用SA的方法是先了解这两种不同的工具集,这是两个不同的概念,而大家常常混交SQL语言构造器和ORM。关键的不同是,使用cursor形式的结果集时使用的是SQL语言构造器;而使用类实例进行管理时使用的是ORM。

本指南首先介绍SQL语言构造器,首先需要声明的数据库信息叫做 table metadata 。本指南包含了一些SQL构造的例子,包括如何有效的使用SQL语言构造器的例子。

4 操作数据库对象

在SQLAlchemy的核心哲学中表格和类是不同的。因为如此,SQLAlchemy提供了构造表格的方法(使用表格的元信息table metadata)。所以我们从构造表格的元信息对象和定制操作他们的对象开始。稍后我们可以看到SQLAlchemy的ORM,提供了表格元信息的高层封装,允许我们随心所欲的装载和保存Python类。

4.1 定义元信息,绑定到引擎

首先,你的表格必须已经在MetaData集合中。我们将要创建简单(handy)表格的MetaData,并自动连接到引擎(将一个模式(schema)对象连接到引擎成为绑定binding):

>>> metadata=BoundMetaData(db)

一个构造BoundMetaData对象的等同方法是直接使用引擎URL,这将会帮我们调用 create_engine

>>> metadata=BoundMetaData("sqlite:///tutorial.db")

现在,我们告知metadata关于数据库中的表格,我们可以使用(issue)CREATE语句来创建表格,并且通过他们来创建和执行SQL语句,除非需要打开和关闭任何连接。这都是自动完成的。注意这个功能是推荐使用的。SQLAlchemy包含了使用模式进行连接管理和SQL构造的全部功能,并可以在任何引擎上进行操作。

本教程的目的,是教会大家使用”bound”对象,他可以使得代码简单和易读。

4.2 创建表格

使用metadata作为基本连接,我们可以创建表格:

>>> users_table=Table('user',metadata,
...     Column('user_id',Integer,primary_key=True),
...     Column('user_name',String(40)),
...     Column('password',String(10))
... )

有如你看到的,我们刚刚定义了一个叫做users的表格并拥有3个列:user_id作为主键,user_name和password。它现在只是一个对象而与数据库中的表格没有必然联系。为了让表格生效,我们使用create()方法。有趣的是,我们可以让SQLAlchemy发送SQL语句到数据库时同时显示SQL语句,只要设置BoundMetaData关联的Engine的echo选项即可:

>>> metadata.engine.echo=True
>>> users_table.create()
CREATE TABLE users (
    user_id INTEGER NOT NULL,
    user_name VARCHAR(40),
    password VARCHAR(10),
    PRIMARY KEY (user_id)
)
...

或者,如果users表格已经存在了(比如你第二次运行这些例子),在这种情况下你可以跳过create()方法的调用。你设置可以跳过列定义,而是让SQLAlchemy自动从数据库装入定义:

>>> users_table=Table('users',metadata,autoload=True)
>>> list(users_table.columns)[0].name
'user_id'

关于表格元信息的文档在”Database Meda Data”中。

4.3 插入记录

插入记录是通过表格对象的insert()方法实现的,这将会定义一个子句对象(clause object)(就是CluseElement)来代理INSERT语句:

>>> i=users_table.insert()
>>> i
<sqlalchemy.sql._Insert object at 0x...>
>>> print i
INSERT INTO users (user_id,user_name,password) VALUES (?,?,?)

当我们创建这个插入语句对象时,语句本身也绑定到了Engine,并已经可以执行了。子句对象的execute()方法将会将对象编译为特定引擎的SQL方言,并且执行语句:

>>> i.execute(user_name='Mary',password='secure')
INSERT INTO users (user_name,password) VALUES (?,?)
['Mary','secure']
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>
>>> i.execute({'user_name':'Tom'},{'user_name':'Fred'},{'user_name':'Harry'})
INSERT INTO users (user_name) VALUES (?)
[['Tom'],['Fred'],['Harry']]
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>

注意VALUES子句会自动调整参数数量。这是因为ClauseElement的编译步骤并不依赖于特定的数据库,执行的参数也是如此。

当构造子句对象时,SQLAlchemy会绑定所有的值到参数。在构造时,参数绑定总是依靠键值对。在编译时,SQLAlchemy会转换他们到适当的格式,基于DBAPI的参数风格。这在DBAPI中描述的参数位置绑定中同样工作的很好。

这些文档继承于”Inserts”。

4.4 查询

我们可以检查users表中已经存在的数据。方法同插入的例子,只是你需要调用表格的select()方法:

>>> s=users_table.select()
>>> print s
SELECT users.user_id,users.user_name,users.password
FROM users
>>> r=s.execute()
SELECT users.user_id,users.user_name,users.password
FROM users
[]

这时,我们并没有忽略execute()的返回值。他是一个ResultProxy实例,保存了结果,而行为非常类似于DBAPI中的cursor对象:

>>> r
<sqlalchemy.engine.base.ResultProxy object at 0x...>
>>> r.fetchone()
(1,u'Mary',u'secure')
>>> r.fetchall()
[(2,u'Tom',None),(3,u'Fred',None),(4,u'Harry',None)]

查询条件同Python表达式,使用Column对象。所有表达式中的Column对象都是ClauseElements的实例,例如Select、Insert和Table对象本身:

>>> r=users_table.select(users_table.c.user_name=='Harry').execute()
SELECT users.user_id,users.user_name,users.password
FROM users
WHERE users.user_name=?
['Harry']
>>> row=r.fetchone()
>>> print row
(4,u'Harry',None)

所幸的是所有标准SQL操作都可以用Python表达式来构造,包括连接(join)、排序(order)、分组(group)、函数(function)、子查询(correlated subquery)、联合(union)等等。关于查询的文档”Simple Select”。

4.5 操作记录

你可以看到,当我们打印记录时返回的可执行对象,它以元组打印记录。这些记录实际上同时支持列表(list)和字典(dict)接口。字典接口允许通过字符串的列名定位字段,或者通过Column对象:

>>> row.keys()
['user_id','user_name','password']
>>> row['user_id'],row[1],row[users_table.c.password]
(4,u'Harry',None)

通过Column对象来定位是很方便的,因为这样避免了使用列名的方式。

结果集也是支持序列操作的。但是相对于select还有微小的差别,就是允许指定所选的列:

>>> for row in select([user_table.c.user_id,users_table.c.user_name]).execute():
...     print row
SELECT users.user_id,users.user_name
FROM users
[]
(1,u'Mary')
... ...

4.6 表间关系

我们可以创建第二个表格,email_addresses,这会引用users表。定义表间的关联,使用ForeignKey构造。我们将来也会考虑使用表格的CREATE语句:

>>> email_addresses_table=Table('email_addresses',metadata,
...     Column('address_id',Integer,primary_key=True),
...     Column('email_address',String(100),nullable=False),
...     Column('user_id',Integer,ForeignKey('users.user_id')))
>>> email_addresses_table.create()
CREATE TABLE email_addresses (
    address_id INTEGER NOT NULL,
    email_address VARCHAR(100) NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (address_id),
    FOREIGN KEY(user_id) REFERENCES users (user_id)
)
...

上面的email_addresses表与表users通过ForeignKey(‘users.user_id’)相联系。ForeignKey的构造器需要一个Column对象,或一个字符串代表表明和列名。当使用了字符串参数时,引用的表必须已经存在于相同的MetaData对象中,当然,可以是另外一个表。

下面可以尝试插入数据:

>>> email_addresses_table.insert().execute(
...     {'email_address':'tom@tom.com','user_id':2},
...     {'email_address':'mary@mary.com','user_id':1})
INSERT INTO email_addresses (email_address,user_id) VALUES (?,?)
[['tom@tom.com',2],['mary@mary.com',1]]
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...>

在两个表之间,我们可以使用 join 方法构造一个连接:

>>> r=users_table.join(email_addresses_table).select().execute()
SELECT users.user_id, users.user_name, users.password,
email_addresses.address_id, email_addresses.email_address,
email_addresses.user_id
FROM users JOIN email_addresses ON users.user_id=email_addresses.user_id
[]
>>> print [row for row in r]
[(1, u'Mary', u'secure', 2, u'mary@mary.com', 1),
(2,u'Tom', None, 1, u'tom@tom.com', 2)]

join 方法同时也是 sqlalchemy 命名空间中的一个独立的函数。连接条件指明了表对象给定的外键。条件(condition),也可以叫做 “ON 子句” ,可以被明确的指定,例如这个例子我们查询所有使用邮件地址作为密码的用户:

>>> print join(users_table, email_addresses_table,
...     and_(users_table.c.user_id==email_addresses_table.c.user_id,
...     users_table.c.password==email_addresses_table.c.email_address)
...     )
users JOIN email_addresses ON users.user_id=email_addresses.user_id AND
users.password=email_address.email_address

5 使用ORM工作

现在我们已经有了一些表格和SQL操作的知识了,让我们来看看SQLAlchemy的ORM (Object Relational Mapper) 。使用ORM,你可以将表格(和其他可以查询的对象)同Python联系起来,放入映射集(Mappers)当中。然后你可以执行查询并返回 对象实例 列表,而不是结果集。 对象实例 也被联系到一个叫做 Session 的对象,确保自动跟踪对象的改变,并可以使用 flush 立即保存结果。

5.1 创建一个映射

一个映射通常对应一个Python类,其核心意图是,“这个类的对象是用作这个表格的行来存储的”。让我们创建一个类叫做 User ,描述了一个用户对象,并保存到 users 表格。:

>>> class User(object):
...     def __repr__(self):
...         return "%s(%r,%r)"%(
...             self.__class__.__name__,self.user_name,self.password)

这个类是一个新形式(new style)的类(继承自 object )并且不需要构造器(在需要时默认提供)。我们只实现了一个 __repr__ 方法,用于显示 User 对象的基本信息。注意 __repr__ 方法应用了实例变量 user_namepassword ,这是还没定义的。我们可选的定义这些属性,并可以进行处理;SQLAlchemy的 Mapper 的构造器会自动管理这些,而且会自动协调到 users 表格的列名。让我们创建映射,并观察这些属性的定义:

>>> usermapper=mapper(User,users_table)
>>> ul=User()
>>> print ul.user_name
None
>>> print ul.password
None

函数 mapper 返回新建的 Mapper 实例。这也是我们为 User 类创建的第一个映射,也就是类的 主映射 。一般来说,不需要保存 usermapper 变量;SA的ORM会自动管理这个映射。

5.2 获取会话(Session)

创建了一个映射之后,所有对映射的操作都需要一个重要的对象叫做 Session 。所有对象通过映射的载入和保存都 必须 通过 Session 对象,有如对象的工作空间一样被加载到内存。特定对象在特定时间只能关联到一个 Session

缺省时,需要在载入和保存对象之前明确的创建 Session 对象。有多种方法来管理会话,但最简明的方法是调用 create_session()

>>> session=create_session()
>>> session
<sqlalchemy.orm.session.Session object at 0x...>

5.3 查询对象

会话对象拥有载入和存储对象的所有方法,同时也可以查看他们的状态。会话还提供了查询数据库的方便接口,你可以获取一个 Query 对象:

>>> query=session.query(User)
>>> print query.select_by(user_name='Harry')
SELECT users.user_name AS users_user_name, users.password AS users_password,
users.user_id AS users_user_id
FROM users
WHERE users.user_name=? ORDER BY users.oid
['Harry']
[User(u'Harry',None)]

对象所有的查询操作实际上都是通过 Query 的。 Mapper 对象的多种 select 方法也是偷偷的在使用 Query 对象来执行操作。一个 Query 总是联系到一个特定的会话上。

让我们暂时关闭数据库回显,并尝试 Query 的几个方法。结尾是 _by 的方法主要用于对象的键参数。其他的方法允许接受 ClauseElement 对象,使用 Column 对象的Python表达式产生,同样的方法我们在上一节使用过。使用 ClauseElement 结构来查询更加冗长,但是更加灵活:

>>> metadata.engine.echo=False
>>> print query.select(User.c.user_id==3)
[User(u'Fred',None)]
>>> print query.get(2)
User(u'Tom',None)
>>> print query.get_by(user_name='Mary')
User(u'Mary',u'secure')
>>> print query.selectfirst(User.c.password==None)
User(u'Tom',None)
>>> print query.count()
4

Note

User类有一个特别的属性 c ,这个属性描述了User映射表格对象的列。

User.c.user_name 等同于 users_table.c.user_name ,记得 User 是Python对象,而 usersTable 对象。

5.4 修改数据

作为小经验,我们看看如何做出修改。首先,创建一个新的用户”Ed”,然后加入会话:

>>> ed=User()
>>> ed.user_name='Ed'
>>> ed.password='edspassword'
>>> session.save(ed)
>>> ed in session
True

也可以修改数据库中的其他对象。使用 Query 对象载入,然后改变:

>>> mary=query.get_by(user_name='Mary')
>>> harry=query.get_by(user_name='Harry')
>>> mary.password='marysnewpassword'
>>> harry.password='harrysnewpassword'

这时,什么东西都没有保存到数据库;我们所有的修改都在内存中。如果这时程序其他部分尝试修改’Mary’会发生什么呢?因为使用相同的会话,所以再次载入’Mary’实际上定位到相同的主键’Mary’,并且 返回同一对象实例 。这个行为用在会话中确保数据库的一致性:

>>> mary2=query.get_by(user_name='Mary')
>>> mary is mary2
True

在唯一映射中,单一的会话可以确保安全的操作对象。

如果两个不同的会话同时操作一个对象,会检测到并发;SA会使用简单的并发控制来保存对象,可以选择使用拥有更强的使用ids的检查。参考 Mapper Arguments 了解更多细节。

5.5 保存

在新建了用户”ed”并对”Mary”和”Harry”作修改之后,我们再删除”Fred”

>>> fred=query.get_by(user_name='Fred')
>>> session.delete(fred)

然后发送更改到数据库,使用会话的 flush() 方法。开启回显来查看过程:

>>> metadata.engine.echo=True
>>> session.flush()
BEGIN
UPDATE users SET password=? WHERE users.user_id=?
['marysnewpassword',1]
UPDATE users SET password=? WHERE users.user_id=?
['harrysnewpassword',4]
INSERT INTO users (user_name,password) VALUES (?,?)
['Ed','edspassword']
DELETE FROM users WHERE users.user_id=?
[3]
COMMIT

5.6 关系

如果一个关系包含其他信息时,例如包含邮件地址的列表,我们可以在使用 relation() 创建 Mapper 时声明。当然,你还可以对这个关系作很多事情,我们举几个简单的例子。首先使用 users 表,它拥有一个外键关系连接到 email_addresses 表。 email_addresses 表中的每一行都有列 user_id 用来引用 users 表中的一行;而且 email_addresses 表中的多行可以引用 users 表中的同一行,这叫一对多关系。

首先,处理 email_addresses 表。我们创建一个新的类 Address 描述了 email_addresses 表中的一行,并且也创建了用于 Address 类的映射对象:

>>> class Address(object):
...     def __init__(self,email_address):
...         self.email_address=email_address
...     def __repr__(self):
...         return "%s(%r)"%(
...             self.__class__.__name__,self.email_address)
>>> mapper(Address, email_addresses_table)
<sqlalchemy.orm.mapper.Mapper object at 0x...>

然后,我们通过使用 relation() 创建一个关系连接 UserAddress 类,并且添加关系到 User 映射,使用 add_property 函数:

>>> usermapper.add_property('addresses',relation(Address))

函数 relation() 需要一个类或映射作为首参数,并且还有很多选项来控制行为。 User 映射现在给每一个 User 实例添加了一个属性叫 addresses 。SA将会自动检测这个一对多关系。并且随后创建 addresses 列表。当新的 User 对象创建时,这个列表为空 。

让我们看看数据库做了什么。当我们修改映射的配置时,最好清理一下会话,让所有载入的 User 对象可以重新载入:

>>> session.clear()

我们随之可以使用 User 对象的 addresses 属性来象列表一样处理:

>>> mary=query.get_by(user_name='Mary')
SELECT users.user_name AS users_user_name, users.password AS users_password,
users.user_id AS users_user_id
FROM users
WHERE users.user_name=? ORDER BY users.oid
LIMIT 1 OFFSET 0
['Mary']
>>> print [a for a in mary.address]
SELECT email_addresses.user_id AS email_address_user_id,
email_addresses.address_id AS email_addresses_address_id,
email_addresses.email_address AS email_addresses_email_address
FROM email_addresses
WHERE ?= email_addresses.user_id ORDER BY email_addresses.oid
[1]
[Address(u'mary@mary.com')]

向列表添加元素也很简单。新的 Address 对象将会在调用会话的flush()时保存:

>>> mary.addresses.append(Address('mary2@gmail.com'))
>>> session.flush()
BEGIN
INSERT INTO email_addresses (email_address,user_id) VALUEs (?,?)
['mary2@gmail.com',1]
COMMIT

主文档中关于使用映射的部分在如下地址:

http://www.sqlalchemy.org/docs/datamapping.myt#datamapping

5.7 事务

你可能已经注意到在上面例子中的 session.flush() ,SQLAlchemy使用 BEGINCOMMIT 来使用数据库事务。 flush() 方法使用事务来对一些记录执行一系列的指令。如果希望在 flush() 之外使用更大规模的事务,可以通过 SessionTransaction 对象,其生成使用 session.create_transaction() 。下面将会执行一个非常复杂的 SELECT 语句,进行大量的修改,然后再创建一个有两个邮箱的用户,这些都在事务中完成。而且将会在中间使用 flush() 来保存,然后在执行最后的 commit() 时将所有改变写入数据库。我们把事务封装在一个 try/except 语句块当中确保资源的安全释放:

>>> transaction=session.create_transaction()
>>> try:
...     (ed,harry,mary)=session.query(User).select(
...         User.c.user_name.in_('Ed','Harry','Mary'),
...         order_by=User.c.user_name
...     )
...     del mary.address[1]
...     harry.addresses.append(Address('harry2@gmail.com'))
...     session.flush()
...     print "***flushed the session***"
...     fred=User()
...     fred.user_name='fred_again'
...     fred.addresses.append(Address('fred@fred.com'))
...     fred.addresses.append(Address('fredsnewemail@fred.com'))
...     session.save(fred)
...     transaction.commit()
... except:
...     transaction.rollback()
...     raise
BEGIN
SELECT users.user_name AS users_user_name,
users.password AS users_password,
users.user_id AS users_user_id
FROM users
WHERE users.user_name IN (?, ?, ?) ORDER BY users.user_name
['Ed', 'Harry', 'Mary']
SELECT email_addresses.user_id AS email_addresses_user_id,
email_addresses.address_id AS email_addresses_address_id,
email_addresses.email_address AS email_addresses_email_address
FROM email_addresses
WHERE ? = email_addresses.user_id ORDER BY email_addresses.oid
[4]
UPDATE email_addresses SET user_id=? WHERE email_addresses.address_id = ?
[None, 3]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['harry2@gmail.com', 4]
***flushed the session***
INSERT INTO users (user_name, password) VALUES (?, ?)
['fred_again', None]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['fred@fred.com', 6]
INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)
['fredsnewemail@fred.com', 6]
COMMIT

对应的主文档:

http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork

5.8 下一步

如上已经介绍了一下SQLAlchemy。但是不同的人可能有不同的做事方法,比如定义不同风格的映射的关系,所以还是允许使用原始的SQL来定义表格,还有Engine、SQL语句、数据库连接等。

Berkeley DB 3.x & 4.x Python扩展包

Thursday, September 6th, 2007

Berkeley DB 3.x & 4.x Python扩展包

翻译: gashero <harry.python@gmail.com>

目录

1 简介

这里介绍了一点关于bsddb3.db的Python扩展模块的东西,它包装了Berkeley DB 3.x和4.x的C库。这里的扩展模块指部分纯Python模块。

本模块希望可以应用到如下情况中。这个模块用于确保作事情不要太复杂,而在需要复杂的时候也可以提供相关功能。

  1. 向后兼容。本模块一直希望可以适应各种版本的BDB接口,甚至于1.85接口。这意味着需要创建具备相同接口的,比如btopen()、hashopen()、rnopen()和他们返回的对象接口,特别是first()、last()、next()、prev()这些经常用于避免使用游标的接口。这些都是在 bsddb3.__init__.py 中以Python代码实现的。
  2. 简单的持续字典。在前面实现的前进了一小步。程序员可能希望直接使用新的DB对象,但是可能仅仅在单一进程或线程中使用。这时程序员并不需要被DBEnv所骚扰,应该尽可能的表现的像一个字典。
  3. 并发存取字典。这需要具备处理单一写者和多读者的DB对象,常见于多线程或多进程环境。这时需要使用适当的参数创建DBEnv对象。这时并不需要其他附加的操作。
  4. 高级的基于事务的数据存储。这将会发挥BerkeleyDB的所有能力。程序员在这时可能并非使用字典活其他类似的DB接口,而是传递一个事务对象。另外,这类功能大部分可以简单的通过设置DBEnv的参数来实现,这时可以使用事务,并且在发现死锁时产生异常,等等。

2 提供的类型

bsddb3.db 扩展模块提供如下对象类型:

  • DB :简单的数据库对象,支持Hash/BTree/Recno/Queue的存取方法。
  • DBEnv :提供数据库环境以进行更多高级设置,如事务、日志、并发存取等。
  • DBCursor :一个类似指针的对象,用于遍历数据库。
  • DBTxn :一个数据库事务。支持多文件提交,中断和数据库修改的检查点。
  • DBLock :一个锁的不透明句柄。查看 DBEnv.lock_get()DBEnv.lock_put() 。锁对于数据库中的内容并不重要,但是对多线程或多进程的并发异步访问是很重要的,需要DBEnv的支持。

3 提供的异常

BerkeleyDB的C API使用函数返回码表示错误。而bsddb3.db模块把这些转换成了Python异常,允许使用try-except来处理。

每个错误代码都转换成了一种异常,如下表所示。如果你使用C API文档,也会很容易的关联这些异常。

所有的异常都是继承自DBError异常,如果你希望处理所有异常,可以直接使用DBError。DBNotFoundError在找不到对应的键名时抛出,DBNotFoundError同时也继承自标准的KeyError异常,用来把DB模拟的像一个字典一样。

当抛出任何一个异常时,都会同时包含一个整数值指定错误代码,和一个字符串指定错误信息。

DBError 基类,以下所有都是从这里继承的
DBIncompleteError DB_INCOMPLETE
DBKeyEmptyError DB_KEYEMPTY
DBKeyExistError DB_KEYEXIST
DBLockDeadlockError DB_LOCK_DEADLOCK
DBLockNotGrantedError DB_LOCK_NOTGRANTED
DBNotFoundError DB_NOTFOUND(同时继承自KeyError)
DBOldVersionError DB_OLD_VERSION
DBRunRecoveryError DB_RUNRECOVERY
DBVerifyBadError DB_VERIFY_BAD
DBNoServerError DB_NOSERVER
DBNoServerHomeError DB_NOSERVER_HOME
DBNoServerIDError DB_NOSERVER_ID
DBInvalidArgError EINVAL
DBAccessError EACCESS
DBNoSpaceError ENOSPC
DBNoMemoryError ENOMEM
DBAgainError EAGAIN
DBBusyError EBUSY
DBFileExistsError EEXIST
DBNoSuchFileError ENOENT
DBPermissionsError EPERM

4 其他包模块

  • dbshelve.py :使用Python标准实现的shelve方式存取对象,同时也提供一些高层方法隐藏bdb底层细节。
  • dbtables.py :这个模块是由Gregory Smith实现的一个简单的表格结构。
  • dbutils.py :一个简单的DB接口提供字典的操作方法。
  • dbobj.py :包含DB和DBEnv的子类。
  • dbrecio.py :包含DBRecIO类,允许像读写文件一样存取DB的记录。

5 测试

一个完整的测试套件测试了所有类型的功能,现在使用了 PyUnit 进行自动化测试,已经包含了超过150个测试用例了。

6 参考

查看C API的 在线文档 ,或 这份文档的本地副本 了解更多功能的细节。Python方法的名称与C API中的相似。