在Python的Django框架上部署ORM库的教程

$ pip install sqlobject
Downloading/unpacking sqlobject
Downloading SQLObject-1.5.1.tar.gz (276kB): 276kB downloaded
Running setup.py egg_info for package sqlobject
 
warning: no files found matching '*.html'
warning: no files found matching '*.css'
warning: no files found matching 'docs/*.html'
warning: no files found matching '*.py' under directory 'tests'
Requirement already satisfied (use --upgrade to upgrade): FormEncode>=1.1.1 in /Users/xiaonuogantan/python2-workspace/lib/python2.7/site-packages (from sqlobject)
Installing collected packages: sqlobject
Running setup.py install for sqlobject
changing mode of build/scripts-2.7/sqlobject-admin from 644 to 755
changing mode of build/scripts-2.7/sqlobject-convertOldURI from 644 to 755
 
warning: no files found matching '*.html'
warning: no files found matching '*.css'
warning: no files found matching 'docs/*.html'
warning: no files found matching '*.py' under directory 'tests'
changing mode of /Users/xiaonuogantan/python2-workspace/bin/sqlobject-admin to 755
changing mode of /Users/xiaonuogantan/python2-workspace/bin/sqlobject-convertOldURI to 755
Successfully installed sqlobject
Cleaning up...
 
> from sqlobject import StringCol, SQLObject, ForeignKey, sqlhub, connectionForURI
> sqlhub.processConnection = connectionForURI('sqlite:/:memory:')
>
> class Person(SQLObject):
... name = StringCol()
...
> class Address(SQLObject):
... address = StringCol()
... person = ForeignKey('Person')
...
> Person.createTable()
[]
> Address.createTable()
[]

> p = Person(name='person')
> a = Address(address='address', person=p)
> p
 
> a
 
<address>

> persons = Person.select(Person.q.name == 'person')
> persons
 
> list(persons)
[]
> p1 = persons[0]
> p1 == p
True
> addresses = Address.select(Address.q.person == p1)
> addresses
 
> list(addresses)
[
 
<address>]
> a1 = addresses[0]
> a1 == a
True
 

> from storm.locals import Int, Reference, Unicode, create_database, Store
>
>
> db = create_database('sqlite:')
> store = Store(db)
>
>
> class Person(object):
... __storm_table__ = 'person'
... id = Int(primary=True)
... name = Unicode()
...
>
> class Address(object):
... __storm_table__ = 'address'
... id = Int(primary=True)
... address = Unicode()
... person_id = Int()
... person = Reference(person_id, Person.id)
...

> store.execute("CREATE TABLE person "
... "(id INTEGER PRIMARY KEY, name VARCHAR)")
 
> store.execute("CREATE TABLE address "
... "(id INTEGER PRIMARY KEY, address VARCHAR, person_id INTEGER, "
... " FOREIGN KEY(person_id) REFERENCES person(id))")
 
> person = Person()
> person.name = u'person'
> print person
 
> print "%r, %r" % (person.id, person.name)
None, u'person' # Notice that person.id is None since the Person instance is not attached to a valid database store yet.
> store.add(person)
 
> print "%r, %r" % (person.id, person.name)
None, u'person' # Since the store hasn't flushed the Person instance into the sqlite database yet, person.id is still None.
> store.flush()
> print "%r, %r" % (person.id, person.name)
1, u'person' # Now the store has flushed the Person instance, we got an id value for person.
> address = Address()
> address.person = person
> address.address = 'address'
> print "%r, %r, %r" % (address.id, address.person, address.address)
None, , 'address'
> address.person == person
True
> store.add(address)
 
> store.flush()
> print "%r, %r, %r" % (address.id, address.person, address.address)
1, , 'address'

> person = store.find(Person, Person.name == u'person').one()
> print "%r, %r" % (person.id, person.name)
1, u'person'
> store.find(Address, Address.person == person).one()
 
> address = store.find(Address, Address.person == person).one()
> print "%r, %r" % (address.id, address.address)
1, u'address'

$ django-admin.py startproject demo
$ cd demo
$ python manage.py syncdb
Creating tables ...
Creating table django_admin_log
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_content_type
Creating table django_session
 
You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now"htmlcode">

# demo/models.py
> from django.db import models
>
>
> class Person(models.Model):
... name = models.TextField()
... class Meta:
... app_label = 'demo'
...
>
> class Address(models.Model):
... address = models.TextField()
... person = models.ForeignKey(Person)
... class Meta:
... app_label = 'demo'
...



上面的代码声明了2个Python 类，Person 和 Address，每一个都映射到数据库表。在执行任意数据库操作代码之前，我们需要先在本地的sqlite数据库创建表。


python manage.py syncdb
Creating tables ...
Creating table demo_person
Creating table demo_address
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)




为了插入一个 person 和一个 address 到数据库，我们实例化相应对象并调用这些对象的save() 方法。

 




> from demo.models import Person, Address
> p = Person(name='person')
> p.save()
> print "%r, %r" % (p.id, p.name)
1, 'person'
> a = Address(person=p, address='address')
> a.save()
> print "%r, %r" % (a.id, a.address)
1, 'address'



为了获得或检索 person 和 address 对象， 我们用model类神奇的对象属性从数据库取得对象。

 




> persons = Person.objects.filter(name='person')
> persons
[]
> p = persons[0]
> print "%r, %r" % (p.id, p.name)
1, u'person'
> addresses = Address.objects.filter(person=p)
> addresses
[
 
<address>]
> a = addresses[0]
> print "%r, %r" % (a.id, a.address)
1, u'address'



peewee
peewee 是一个小的，表达式的 ORM。相比其他的 ORM，peewee 主要专注于极简主义，其API简单，并且其库容易使用和理解。

 




pip install peewee
Downloading/unpacking peewee
Downloading peewee-2.1.7.tar.gz (1.1MB): 1.1MB downloaded
Running setup.py egg_info for package peewee
 
Installing collected packages: peewee
Running setup.py install for peewee
changing mode of build/scripts-2.7/pwiz.py from 644 to 755
 
changing mode of /Users/xiaonuogantan/python2-workspace/bin/pwiz.py to 755
Successfully installed peewee
Cleaning up...



为了创建数据库模型映射，我们实现了一个Person 类 和一个Address类 来映射对应的数据库表。
 




> from peewee import SqliteDatabase, CharField, ForeignKeyField, Model
>
> db = SqliteDatabase(':memory:')
>
> class Person(Model):
... name = CharField()
... class Meta:
... database = db
...
>
> class Address(Model):
... address = CharField()
... person = ForeignKeyField(Person)
... class Meta:
... database = db
...
> Person.create_table()
> Address.create_table()



为了插入对象到数据库，我们实例化对象并调用了它们的save() 方法。从视图的对象创建这点来看，peewee类似于Django。

 




> p = Person(name='person')
> p.save()
> a = Address(address='address', person=p)
> a.save()



为了从数据库获得或检索对象， 我们select 了类各自的对象。
 




> person = Person.select().where(Person.name == 'person').get()
> person
 
> print '%r, %r' % (person.id, person.name)
1, u'person'
> address = Address.select().where(Address.person == person).get()
> print '%r, %r' % (address.id, address.address)
1, u'address'



SQLAlchemy
 SQLAlchemy 是Python编程语言里，一个在MIT许可下发布的开源工具和SQL ORM。它首次发布于2006年二月，由Michael Bayer写的。它提供了 “一个知名企业级的持久化模式的，专为高效率和高性能的数据库访问设计的，改编成一个简单的Python域语言的完整套件”。它采用了数据映射模式（像Java中的Hibernate）而不是Active Record模式（像Ruby on Rails的ORM）。
SQLAlchemy 的工作单元 主要使得 有必要限制所有的数据库操作代码到一个特定的数据库session，在该session中控制每个对象的生命周期 。类似于其他的ORM，我们开始于定义declarative_base()的子类，以映射表到Python类。
 




> from sqlalchemy import Column, String, Integer, ForeignKey
> from sqlalchemy.orm import relationship
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Person(Base):
... __tablename__ = 'person'
... id = Column(Integer, primary_key=True)
... name = Column(String)
...
>
> class Address(Base):
... __tablename__ = 'address'
... id = Column(Integer, primary_key=True)
... address = Column(String)
... person_id = Column(Integer, ForeignKey(Person.id))
... person = relationship(Person)
...



在我们写任何数据库代码前，我们需要为数据库session创建一个数据库引擎。

 




> from sqlalchemy import create_engine
> engine = create_engine('sqlite:///')



一当我们创建了数据库引擎，可以继续创建一个数据库会话，并为所有之前定义的 Person和Address 类创建数据库表。

 




> from sqlalchemy.orm import sessionmaker
> session = sessionmaker()
> session.configure(bind=engine)
> Base.metadata.create_all(engine)



现在，session 对象对象变成了我们工作单元的构造函数，将和所有后续数据库操作代码和对象关联到一个通过调用它的 __init__() 方法构建的数据库session上。

 




> s = session()
> p = Person(name='person')
> s.add(p)
> a = Address(address='address', person=p)
> s.add(a)



为了获得或检索数据库中的对象，我们在数据库session对象上调用 query() 和 filter() 方法。

 




> p = s.query(Person).filter(Person.name == 'person').one()
> p
 
> print "%r, %r" % (p.id, p.name)
1, 'person'
> a = s.query(Address).filter(Address.person == p).one()
> print "%r, %r" % (a.id, a.address)
1, 'address'



请留意到目前为止，我们还没有提交任何对数据库的更改，所以新的person和address对象实际上还没存储在数据库中。 调用 s.commit() 将会提交更改，比如，插入一个新的person和一个新的address到数据库中。

 




> s.commit()
> s.close()



Python ORM 之间对比
对于在文章里提到的每一种 Python ORM ，我们来列一下他们的优缺点：

SQLObject
优点：

  
    采用了易懂的ActiveRecord 模式
  
    一个相对较小的代码库

缺点：

  
    方法和类的命名遵循了Java 的小驼峰风格
  
    不支持数据库session隔离工作单元



Storm
优点：

  
    清爽轻量的API，短学习曲线和长期可维护性
  
    不需要特殊的类构造函数，也没有必要的基类

缺点：

  
    迫使程序员手工写表格创建的DDL语句，而不是从模型类自动派生
  
    Storm的贡献者必须把他们的贡献的版权给Canonical公司



Django's ORM
优点：

  
    易用，学习曲线短
  
    和Django紧密集合，用Django时使用约定俗成的方法去操作数据库

缺点：

  
    不好处理复杂的查询，强制开发者回到原生SQL
  
    紧密和Django集成，使得在Django环境外很难使用



peewee
优点：

  
    Django式的API，使其易用
  
    轻量实现，很容易和任意web框架集成

缺点：

  
    不支持自动化 schema 迁移
  
    多对多查询写起来不直观



SQLAlchemy
优点：

  
    企业级 API，使得代码有健壮性和适应性
  
    灵活的设计，使得能轻松写复杂查询

缺点：

  
    工作单元概念不常见
  
    重量级 API，导致长学习曲线



总结和提示
相比其他的ORM， SQLAlchemy 意味着，无论你何时写SQLAlchemy代码， 都专注于工作单元的前沿概念 。DB Session 的概念可能最初很难理解和正确使用，但是后来你会欣赏这额外的复杂性，这让意外的时序提交相关的数据库bug减少到0。在SQLAlchemy中处理多数据库是棘手的， 因为每个DB session 都限定了一个数据库连接。但是，这种类型的限制实际上是好事， 因为这样强制你绞尽脑汁去想在多个数据库之间的交互， 从而使得数据库交互代码很容易调试。
在未来的文章中，我们将会完整地披露更高阶的SQLAlchemy用例, 真正领会无限强大的API。