python - Efficiently updating database using SQLAlchemy ORM - Stack Overflow
to customize your list.
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
J it only takes a minute:
Join the Stack Overflow community to:
Ask programming questions
Answer and help your peers
Get recognized for your expertise
I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.
Say I've got a column 'foo' in my database and I want to increment it.
In straight sqlite, this is easy:
db = sqlite3.connect('mydata.sqlitedb')
cur = db.cursor()
cur.execute('update table stuff set foo = foo + 1')
I figured out the SQLAlchemy SQL-builder equivalent:
engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb')
md = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table('stuff', md, autoload=True)
upd = table.update(values={table.c.foo:table.c.foo+1})
engine.execute(upd)
This is slightly slower, but there's not much in it.
Here's my best guess for a SQLAlchemy ORM approach:
# snip definition of Stuff class made using declarative_base
# snip creation of session object
for c in session.query(Stuff):
c.foo = c.foo + 1
session.flush()
This does the right thing, but it takes just under fifty times as long as the other two approaches.
I presume that's because it has to bring all the data into memory before it can work with it.
Is there any way to generate the efficient SQL using SQLAlchemy's ORM?
Or using any other python ORM?
Or should I just go back to writing the SQL by hand?
19.8k74463
SQLAlchemy's ORM is meant to be used together with the SQL layer, not hide it. But you do have to keep one or two things in mind when using the ORM and plain SQL in the same transaction. Basically, from one side, ORM data modifications will only hit the database when you flush the changes from your session. From the other side, SQL data manipulation statements don't affect the objects that are in your session.
So if you say
for c in session.query(Stuff).all():
c.foo = c.foo+mit()
it will do what it says, go fetch all the objects from the database, modify all the objects and then when it's time to flush the changes to the database, update the rows one by one.
Instead you should do this:
session.execute(update(stuff_table, values={stuff_table.c.foo: stuff_table.c.foo + 1}))
This will execute as one query as you would expect, and because atleast the default session configuration expires all data in the session on commit you don't have any stale data issues.
In the almost-released 0.5 series you could also use this method for updating:
session.query(Stuff).update({Stuff.foo: Stuff.foo + 1})
That will basically run the same SQL statement as the previous snippet, but also select the changed rows and expire any stale data in the session. If you know you aren't using any session data after the update you could also add synchronize_session=False to the update statement and get rid of that select.
session.query(Clients).filter(Clients.id == client_id_list).update({'status': status})
Try this =)
12.8k764111
There are several ways to UPDATE using sqlalchemy
1) for c in session.query(Stuff).all():
c.foo += 1
2) session.query().\
update({"foo": (Stuff.foo + 1)})
3) conn = engine.connect()
stmt = Stuff.update().\
values(Stuff.foo = (Stuff.foo + 1))
conn.execute(stmt)
1,72211722
Withough testing, I'd try:
for c in session.query(Stuff).all():
c.foo = c.foo+mit()
(IIRC, commit() works without flush()).
I've found that at times doing a large query and then iterating in python can be up to 2 orders of magnitude faster than lots of queries.
I assume that iterating over the query object is less efficient than iterating over a list generated by the all() method of the query object.
[Please note comment below - this did not speed things up at all].
20.2k34079
If it is because of the overhead in terms of creating objects, then it probably can't be sped up at all with SA.
If it is because it is loading up related objects, then you might be able to do something with lazy loading.
Are there lots of objects being created due to references?
(IE, getting a Company object also gets all of the related People objects).
20.2k34079
Here's an example of how to solve the same problem without having to map the fields manually:
from sqlalchemy import Column, ForeignKey, Integer, String, Date, DateTime, text, create_engine
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.attributes import InstrumentedAttribute
engine = create_engine('postgres://postgres@localhost:5432/database')
session = sessionmaker()
session.configure(bind=engine)
Base = declarative_base()
class Media(Base):
__tablename__ = 'media'
id = Column(Integer, primary_key=True)
title = Column(String, nullable=False)
slug = Column(String, nullable=False)
type = Column(String, nullable=False)
def update(self):
s = session()
mapped_values = {}
for item in Media.__dict__.iteritems():
field_name = item[0]
field_type = item[1]
is_column = isinstance(field_type, InstrumentedAttribute)
if is_column:
mapped_values[field_name] = getattr(self, field_name)
s.query(Media).filter(Media.id == self.id).update(mapped_values)
s.commit()
So to update a Media instance, you can do something like this:
media = Media(id=123, title="Titular Line", slug="titular-line", type="movie")
media.update()
6,73342538
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled当前位置：&>& >
Python ORM框架SQLAlchemy之数据查询实例
Python ORM框架SQLAlchemy之数据查询实例
发布时间： 15:57:31&&&编辑：
本文介绍了Python ORM框架SQLAlchemy中数据查询的一些实例代码，有需要的朋友参考下。
前期做了充足的准备工作，现在该是关键内容之一查询了，当然前面的文章中或多或少的穿插了些有关查询的东西，比如一个查询(Query)对象就是通过Session会话的query()方法获取的，注意，这个方法的参数数目是可变的，也就是说可以传入任意多的参数数目，参数的类型可以是任意的类组合或者是类的名称，接下来的例子就说明了这一点，让Query对象加载了User实例。
&&& for instance in session.query(User).order_by(User.id):
...&&&& print instance.name, instance.fullname
SELECT users.id AS users_id,
&&&&&&& users.name AS users_name,
&&&&&&& users.fullname AS users_fullname,
&&&&&&& users.password AS users_password
FROM users ORDER BY users.id
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone
当然通过这个例子，得到Query对象返回的是一组可迭代的User实例表，然后通过for in语句访问，比如说这里可以依次输出&用户名&instance.name和&用户全名&instance.fullname。
后面有个.order_by(User.id)，这个和SQL语句一样的，指示结果集按User.id所映射的表列进行排序。
假设仅需要&用户名&和&用户全名&，对于对象实例的其他属性不感兴趣的话，也可以直接查询它们(类的属性名称)，前提是这个类必须是ORM映射的，无论何时，任意数目的类实体或者基于列的实体均可以作为query()方法的参数，当然最终Query对象会返回元组类型。
&&& for name, fullname in session.query(User.name, User.fullname):
...&&&& print name, fullname
SELECT users.name AS users_name,
&&&&&&& users.fullname AS users_fullname
FROM users
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone
返回的元组类型也可以被看作是普通的Python对象，属性名称归属性名称，类型名称归类型名称。
&&& for row in session.query(User, User.name).all():
...&&& print row.User, row.name
SELECT users.id AS users_id,
&&&&&&& users.name AS users_name,
&&&&&&& users.fullname AS users_fullname,
&&&&&&& users.password AS users_password
FROM users
当然，也可以搞点个性化，比如通过label()方法改变单独的列表达式名称，当然这个方法只有在映射到实体表的列元素对象(ColumnElement-derived)中存在(比如 User.name)。
&&& for row in session.query(User.name.label('name_label')).all():
...&&& print(row.name_label)
SELECT users.name AS name_label
FROM users
之前看到查询对象实例必须用到实体类的全名(User)，假设要多次使用这个实体类名作为查询对象(比如表连接操作)query()的参数，则可以给它取个&别名&，然后就可以通过别名来传入参数了。
&&& from sqlalchemy.orm import aliased
&&& user_alias = aliased(User, name='user_alias')
&&& for row in session.query(user_alias, user_alias.name).all():
...&&& print row.user_alias
SELECT user_alias.id AS user_alias_id,
&&&&&&& user_alias.name AS user_alias_name,
&&&&&&& user_alias.fullname AS user_alias_fullname,
&&&&&&& user_alias.password AS user_alias_password
FROM users AS user_alias
学过MySQL等这类的同学可能知道LIMIT和OFFSET这两个SQL操作，这个能够很方便控制记录的数目和位置，常常被用于数据分页操作，当然这类操作SQLAlchemy的Query对象可以通过Python数组分片来实现，这个操作常常和ORDER BY一起使用。
&&& for u in session.query(User).order_by(User.id)[1:3]:
...&&& print u
SELECT users.id AS users_id,
&&&&&&& users.name AS users_name,
&&&&&&& users.fulame AS users_fullname,
&&&&&&& users.password AS users_password
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
假如需要筛选过滤特定结果，则可以使用filter_by()方法，这个方法使用关键词参数：
&&& for name, in session.query(User.name).\
...&&&&&&&&&&&& filter_by(fullname='Ed Jones'):
...&&& print name
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)
或者使用filter()同样能达到目的。
注意，其使用了更加灵活的类似SQL语句的表达式结构，这意味着可以在其内部使用Python自身的操作符，比如比较操作：
&&& for name, in session.query(User.name).\
...&&&&&&&&&&&& filter(User.fullname=='Ed Jones'):
...&&& print name
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)
注意这里的User.fullname=='Ed Jones'，比较操作与Ed Jones相等的才筛选。
当然强大的Query对象有个很有用的特性，那就是它是可以串联的，意味着Query对象的每一步操作将会返回一个Query对象，可以将相同的方法串联到一起形成表达式结构，假如要查询用户名为&ed&并且全名为&Ed Jones&的用户，可以直接串联调用filter()两次，表示SQL语句里的AND连接：
&&& for user in session.query(User).\
...&&&&&&&&& filter(User.name=='ed').\
...&&&&&&&&& filter(User.fullname=='Ed Jones'):
...&&& print user
SELECT users.id AS users_id,
&&&&&&& users.name AS users_name,
&&&&&&& users.fullname AS users_fullname,
&&&&&&& users.password AS users_password
FROM users
WHERE users.name = ? AND users.fullname = ?
('ed', 'Ed Jones')
列举一些使用filter()常见的筛选过滤操作：
query.filter(User.name == 'ed')
query.filter(User.name != 'ed')
query.filter(User.name.like('%ed%'))
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# works with query objects too:
query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
6. IS NULL
filter(User.name == None)
7. IS NOT NULL
filter(User.name != None)
from sqlalchemy import and_
filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# or l filter()/filter_by() multiple times
filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))
query.filter(User.name.match('wendy'))
match()参数内容由数据库后台指定。
(注：原文是&The contents of the match parameter are database backend specific.&)。
本文来自：
本文链接：
相关热词搜索：SQLAlchemy 和其他的 ORM 框架 - Python - 伯乐在线
& SQLAlchemy 和其他的 ORM 框架
Python ORM 概览
作为一个美妙的语言，Python 除了
外还有很多ORM库。在这篇文章里，我们将来看看几个流行的可选
库，以此更好地窥探到Python ORM 境况。通过写一段脚本来读写2个表 ，person 和 address 到一个简单的数据库，我们能更好地理解每个ORM库的优缺点。
是一个介于SQL数据库和Python之间映射对象的Python ORM。得益于其类似于Ruby on Rails的ActiveRecord模式，在编程社区变得越来越流行。首个 SQLObject在2002年十月发布。它遵循LGPL许可。
中，数据库概念是通过与 SLQAlchemy 非常类似的的一种方式映射到Python的，表映射成类，行作为实例而字段作为属性。它同时提供一种基于Python对象的查询语言，这使得
更加抽象， 从而为应用提供了数据库不可知性（译注:应用和数据库分离）。
$ 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...
1234567891011121314151617181920212223
$ pip install sqlobjectDownloading/unpacking sqlobjectDownloading SQLObject-1.5.1.tar.gz (276kB): 276kB downloadedRunning 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: sqlobjectRunning setup.py install for sqlobjectchanging mode of build/scripts-2.7/sqlobject-admin from 644 to 755changing 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 755changing mode of /Users/xiaonuogantan/python2-workspace/bin/sqlobject-convertOldURI to 755Successfully installed sqlobjectCleaning 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()
12345678910111213
&&& 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()
上面的代码创建了2个简单的表：person 和 address 。为了创建和插入记录到这2个表，我们简单实例化一个person 实例和 一个 address 实例：
&&& p = Person(name='person')
&&& a = Address(address='address', person=p)
&&& p = Person(name='person')&&& a = Address(address='address', person=p)&&& p&&&& a&&address&
为了获得或检索新记录， 我们用神奇的 q 对象关联到 Person 和 Address 类：
&&& persons = Person.select(Person.q.name == 'person')
&&& persons
&&& list(persons)
&&& p1 = persons[0]
&&& p1 == p
&&& addresses = Address.select(Address.q.person == p1)
&&& addresses
&&& list(addresses)
&address&]
&&& a1 = addresses[0]
&&& a1 == a
123456789101112131415161718
&&& persons = Person.select(Person.q.name == 'person')&&& persons&&&& list(persons)[]&&& p1 = persons[0]&&& p1 == pTrue&&& addresses = Address.select(Address.q.person == p1)&&& addresses&&&& list(addresses)[&&address&]&&& a1 = addresses[0]&&& a1 == aTrue
是一个介于 单个或多个数据库与Python之间 映射对象的 Python ORM 。为了支持动态存储和取回对象信息，它允许开发者构建跨数据表的复杂查询。它由Ubuntu背后的公司 Canonical公司用Python开发的，用在
和 Landscape 应用中，后来在2007年作为自由软件发布。这个项目在LGPL许可下发布，代码贡献者必须受让版权给Canonical公司。
像 SQLAlchemy 和 SQLObject 那样，
也映射表到类，行到实例和字段到属性。相对另外2个库， Stom中 table class 不需要是框架特定基类 的子类 。在 SQLAlchemy中，每个 table class 是 sqlalchemy.ext.declarative.declarative_bas 的一个子类。 而在SQLOjbect中，每个table class是 的 sqlobject.SQLObject 的子类。
类似于 SQLAlchemy, Storm 的 Store 对象对于后端数据库就像一个代理人， 所有的操作缓存在内存，一当提交方法在store上被调用就提交到数据库。每个 store 持有自己的Python数据库对象映射集合，就像一个 SQLAlchemy session 持有不同的 Python对象集合。
指定版本的 Storm 可以从
下载。在这篇文章里，示例代码是使用 0.20 版本的Storm写的。
&&& 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)
1234567891011121314151617181920
&&& 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)...
上面的代码创建了一个 sqlite 内存数据库，然后用 store 来引用该数据库对象。一个Storm store 类似 SQLAlchemy的 DBSession对象，都管理 附属于其的实例对象 的生命周期。例如，下面的代码创建了一个 person 和 一个 address， 然后通过刷新 store 都插入记录。
&&& 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
&&& store.add(address)
&&& store.flush()
&&& print "%r, %r, %r" % (address.id, address.person, address.address)
1, , 'address'
1234567891011121314151617181920212223242526272829303132
&&& 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 == personTrue&&& store.add(address)&&&& store.flush()&&& print "%r, %r, %r" % (address.id, address.person, address.address)1, , 'address'
为了获得或检索已插的 Person 和 Address 对象, 我们调用 store.find() 来查询：
&&& 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'
&&& 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 的 ORM
是一个免费开源的紧嵌ORM到其系统的web应用框架。在它首次发布后，得益于其易用为Web而备的特点，Django越来越流行。它在2005年七月在BSD许可下发布。因为Django的ORM 是紧嵌到web框架的，所以就算可以也不推荐，在一个独立的非Django的Python项目中使用它的ORM。
Django，一个最流行的Python web框架， 有它独有的 ORM。 相比 SQLAlchemy， Django 的 ORM 更吻合于直接操作SQL对象，操作暴露了简单直接映射数据表和Python类的SQL对象 。
$ django-admin.py startproject 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? (yes/no): no
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)
$ python manage.py shell
1234567891011121314151617181920
$ django-admin.py startproject demo$ cd demo$ python manage.py syncdbCreating tables ...Creating table django_admin_logCreating table auth_permissionCreating table auth_group_permissionsCreating table auth_groupCreating table auth_user_groupsCreating table auth_user_user_permissionsCreating table auth_userCreating table django_content_typeCreating 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? (yes/no): noInstalling custom SQL ...Installing indexes ...Installed 0 object(s) from 0 fixture(s)$ python manage.py shell
因为我们在没有先建立一个项目时不能够执行Django代码，所以我们在前面的shell创建一个Django demo 项目，然后进入Django shell来测试我们写的 ORM 例子。
# 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'
12345678910111213141516
# 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)
python manage.py syncdbCreating tables ...Creating table demo_personCreating table demo_addressInstalling 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'
&&& 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'
1234567891011121314
&&& 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'
是一个小的，表达式的 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...
123456789101112
pip install peeweeDownloading/unpacking peeweeDownloading peewee-2.1.7.tar.gz (1.1MB): 1.1MB downloadedRunning setup.py egg_info for package peewee&Installing collected packages: peeweeRunning setup.py install for peeweechanging mode of build/scripts-2.7/pwiz.py from 644 to 755&changing mode of /Users/xiaonuogantan/python2-workspace/bin/pwiz.py to 755Successfully installed peeweeCleaning 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()
123456789101112131415161718
&&& 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()
&&& 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'
&&& 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
是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)
123456789101112131415161718192021
&&& 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:///')
&&& 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)
&&& 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)
&&& 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()
&&& print "%r, %r" % (p.id, p.name)
1, 'person'
&&& a = s.query(Address).filter(Address.person == p).one()
&&& print "%r, %r"
1, 'address'
&&& 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" 1, 'address'
请留意到目前为止，我们还没有提交任何对数据库的更改，所以新的person和address对象实际上还没存储在数据库中。 调用 s.commit() 将会提交更改，比如，插入一个新的person和一个新的address到数据库中。
&&& s.commit()
&&& s.close()
&&& s.commit()&&& s.close()
Python ORM 之间对比
对于在文章里提到的每一种 Python ORM ，我们来列一下他们的优缺点：
采用了易懂的ActiveRecord 模式
一个相对较小的代码库
方法和类的命名遵循了Java 的小驼峰风格
不支持数据库session隔离工作单元
清爽轻量的API，短学习曲线和长期可维护性
不需要特殊的类构造函数，也没有必要的基类
迫使程序员手工写表格创建的DDL语句，而不是从模型类自动派生
Storm的贡献者必须把他们的贡献的版权给Canonical公司
Django’s ORM
易用，学习曲线短
和Django紧密集合，用Django时使用约定俗成的方法去操作数据库
不好处理复杂的查询，强制开发者回到原生SQL
紧密和Django集成，使得在Django环境外很难使用
Django式的API，使其易用
轻量实现，很容易和任意web框架集成
不支持自动化 schema 迁移
多对多查询写起来不直观
SQLAlchemy
企业级 API，使得代码有健壮性和适应性
灵活的设计，使得能轻松写复杂查询
工作单元概念不常见
重量级 API，导致长学习曲线
总结和提示
相比其他的ORM， SQLAlchemy 意味着，无论你何时写SQLAlchemy代码， 都专注于工作单元的前沿概念 。DB Session 的概念可能最初很难理解和正确使用，但是后来你会欣赏这额外的复杂性，这让意外的时序提交相关的数据库bug减少到0。在SQLAlchemy中处理多数据库是棘手的， 因为每个DB session 都限定了一个数据库连接。但是，这种类型的限制实际上是好事， 因为这样强制你绞尽脑汁去想在多个数据库之间的交互， 从而使得数据库交互代码很容易调试。
在未来的文章中，我们将会完整地披露更高阶的SQLAlchemy用例, 真正领会无限强大的API。
可能感兴趣的话题
关于 Python 频道
Python频道分享 Python 开发技术、相关的行业动态。
新浪微博：
推荐微信号
（加好友请注明来意）
– 好的话题、有启发的回复、值得信赖的圈子
– 分享和发现有价值的内容与观点
– 为IT单身男女服务的征婚传播平台
– 优秀的工具资源导航
– 翻译传播优秀的外文文章
– 国内外的精选文章
– UI,网页，交互和用户体验
– 专注iOS技术分享
– 专注Android技术分享
– JavaScript, HTML5, CSS
– 专注Java技术分享
– 专注Python技术分享
& 2016 伯乐在线
赞助云主机