- 概述
SQLAlchemy是一套用于python访问数据库的较为成熟的解决方案，其包含的功能可单独使用，也可组合使用。其中最主要的组件描述如下（箭头代表组件之间的基本依赖关系）：
SQLAlchemy中最重要的两个部分当属Object Relational Mapper(ORM) 和SQL expression_r Language。SQL expression_r_rs可独立于ORM使用，当然在使用ORM的时候, SQL expression_r_r language仍然保留部分用于面向对象配置和查询的公共接口函数。
![[程序]sqlalchemy文档资料翻译（一）](http://www.sqlalchemy.org/docs/_images/sqla_arch_small.jpg "[程序]sqlalchemy文档资料翻译（一）")
- 教程
Object Relational Tutorial - 本教程主要描述SQLAlchemy的最主要特性ORM，如果你仅仅是想了解一些高层的SQL功能以及python对象的管理，那么推荐此教程。
SQL expression_r_r_r_r Language Tutorial - 本教程是SQLAlchemy的核心。SQL expression_r_r_r_r_r_r_r Language是独立于ORM包的一套自成体系的工具集，它可以用来创建易于使用的SQL表达式，该SQL表达式在程序上易于创建、修改和执行，返回游标类型的结果集。SQL expression_r_r_r_r_r_r_r Language相较于ORM更加轻量级并且更适合于灵活的SQL操作，同时它也经常出现在ORM的公共接口中，因此对于ORM的高级用户来说也需要掌握此语言。
- 主要文档
Mapper Configuration - ORM模式和技术简介。
Using the Session - SQLAlchemy中Session对象的详细介绍文档。
Database Engines - 介绍SQLAlchemy中的数据库连接工具。
Database Meta Data - 关于使用MetaData和Table对象对schema进行管理的一切，如在应用程序中访问数据库中的schemas，创建和删除tables, constraints, defaults, sequences, indexes。
Connection Pooling - SQLAlchemy连接池相关的更多细节。
Column and Data Types - SQLAlchemy中的数据类型及相关方法，以及如何创建自己的数据类型。
sqlalchemy.ext - SQLAlchemy插件相关
- 代码示例
一些代码示例，参考Examples。
- API参考
所有SQLAlchemy API参考API Reference。
- 安装SQLAlchemy
建议先安装setuptools，安装完成后在命令行输入如下命令：
# easy_install SQLAlchemy第二种安装方式，则需要首先下载SQLAlchemy发布版本，将其解压后在命令行输入：
# python setup.py install
- 安装Database API
SQLAlchemy设计用于和DB-API协作处理数据库，目前支持的数据库包括Supported Databases。
- 版本检查
比较快捷的检查当前SQLAlchemy是否为最新版本的方式如下：
>>> import sqlalchemy
>>> sqlalchemy.__version__
0.6.0
本教程中我们将仅使用SQLite内存数据库。连接数据库我们采用create_engine()方法：
>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite:///:memory:", echo = True)
echo标识用于设置通过python标准日志模块完成的SQLAlchemy日志系统。当开启日志功能，我们就可以看到所有的SQL生成代码。如果你已经熟悉了该教程并希望少一些输出信息，那么可以将其设置为False。本教程会将生成的SQL代码标识在代码中。
接下来我们将告知SQLAlchemy我们需要创建的表。我们会从一个单一的表users开始，该表用于记录使用我们系统的用户信息（假设我们的系统是一个web应用）。我们约定我们的数据表集中在MetaData对象中，通过Table对象来创建，方式和SQL语言的CREATE TABLE类似：
>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey >>> metadata = MetaData() >>> users_table = Table('users', metadata, ... Column('id', Integer, primary_key=True), ... Column('name', String), ... Column('fullname', String), ... Column('password', String) ... )
Database Meta Data 涉及到如何定义 Table 对象的所有内容，以及如何从一个已知的数据库中获取相关数据表定义（被誉为自省）。
接下来，我们就可以通过调用 create_all() 命令发出来自元数据的 CREATE TABLE 命令并将其传递给指向我们数据库的 engine 实例。该方法在调用的时候会先检查已经存在的表结构，因此重复多次的调用该方法是安全的。
>>> metadata.create_all(engine)
SQL代码: PRAGMA table_info("users") () CREATE TABLE users ( id INTEGER NOT NULL, name VARCHAR, fullname VARCHAR, password VARCHAR, PRIMARY KEY (id)) () COMMIT
注意： 熟悉 CREATE TABLE命令的用户可能会注意到此处的 VARCHAR 并没有指定长度，在 SQLite 和 Postgresql中，这是一种合法的数据类型，但是在其他数据库中，这是不允许的。因此如果是在其他数据中运行本示例，你还需要为 String 类型指定长度： ... Column('name', String(50)) String 的长度以及其他可控精度的字段名如：Integer, Numeric等等，在 SQLAlchemy 中仅在创建表的时候使用。 另外，对于 Firebird 和 Oracle 等需要通过序列化方式来创建新主键标识符的数据库，SQLAlchemy 并不会自动为其生成。对于这样的情况，你必须指定 Sequence 生成： >>> from sqlalchemy import Sequence ... Column('id', Integer, Sequence('user_id_seq'), primary_key = True) 一个完整的 Table 实例如下： >>> users_table = Table('users', metadata, ... Column('id', Integer, Sequence('user_id_seq'), primary_key = True), ... Column('name', String(50)), ... Column('fullname', String(50)), ... Column('password', String(12)))
- 定义一个需要映射的 Python 类
Table 对象负责定义和数据库相关的信息，但其并不负责我们程序使用的对象的定义及操作，SQLAlchemy将这视为另一件事。为了对应我们的 users 表，我们先要创建一个基础的 User 类。这仅仅需要从 python 的基础类中继承：
>>> class User(object): ... def __init__(self, name, fullname, password): ... self.name = name ... self.fullname = fullname ... self.password = password ... ... def __repr__(self): ... return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)为了方便类中拥有 __init__() 和 __repr__() 函数。这些函数当然也是可选的，也可以是其他形式的。SQLAlchemy 并不会直接调用 __init__() 函数。
- 设置映射
当拥有了 users_table 和 User 类后，我们现在需要将其映射到一起。SQLAlchemy ORM 包隆重登场。我们将使用 mapper 方法来创建 users_table 和 User之间的映射：
>>> from sqlalchemy.orm import mapper >>> mapper(User, users_table) <Mapper at 0x...; User>mapper() 方法将会创建一个新的 Mapper 对象，该对象会和我们的类关联。现在我们来创建并检查一个 User 对象：
>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'__init__() 方法中并未指定的属性 id 也会存在，因为 users_table 对象中拥有 id 列。默认情况下，mapper 会为 Table 中的所有出现的列创建类属性。这些类属性以python描述的形式存在，并为映射类定义相关的仪器。这些仪器的功能相当丰富，包括跟踪变化以及必要时自动从数据库读取新数据的能力。由于我们还没有通知 SQLAlchemy 去将 Ed Jones 这条记录持久化到数据库中，因此他的 id 仍然是 None 。当我们在稍后持久化本对象后，该属性将会自动被一个新生成的值刷新。
- 在 Declaratively 中一次性完成创建 Table, Class 和 Mapper
我们之前介绍的配置方法包含 Table，用户定义的类，以及调用映射 mapper()。这演示了经典的 SQLAlchemy 用法，该用法以最大的灵活性为重。但是大部分的应用不需要这个级别的灵活性，此时 SQLAlchemy 提供了一种叫做 declarative 的替代的快速配置方案。对于大部分的应用程序来说，这样的配置方式已经足够了。我们之前介绍的例子用该方法描述如下：
>>> from sqlalchemy.ext.declarative import declarative_base >>> Base = declarative_base() >>> class User(Base): ... __tablename__ = 'users' ... ... id = Column(Integer, primary_key=True) ... name = Column(String) ... fullname = Column(String) ... password = Column(String) ... ... def __init__(self, name, fullname, password): ... self.name = name ... self.fullname = fullname ... self.password = password ... ... def __repr__(self): ... return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)如上所述，declarative_base() 函数负责定义一个名为 Base 的新类，该类是我们使用的所有拥有 ORM 功能的类的基类。注意此处我们在定义 Column 对象时并没有指定 "name" 字段，因为该字段可以从给定的属性名推断得出。
在 User 中通过 declarative_base() 创建的底层 Table 对象可以通过 __table__ 属性访问：
>>> users_table = User.__table__
其中 MetaData 对象也是可以访问的：
>>> metadata = Base.metadata
关于 declarative 的详细文档可以在 API Reference 关于 declarative 的章节中找到。
如今 SQLAlchemy 通过第三方库 Elixir 提供另一种 declarative 方法。这是一种拥有更强功能的配置构架产品，他包含很多更高级的内置映射配置。和 declarative 一样，一旦定义了类及映射， ORM 用法和经典的 SQLAlchemy 配置一致。
我们现在开始讨论数据库。ORM 通过 Session 来操纵数据库。当我们第一次创建应用，在 create_engine() 命令同级，我们定义一个 Session 类，该类是提供 Session 对象的工厂：
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)在那些还没有指定 Engine 的应用中，我们可以采用如下的方式来声明：
>>> Session = sessionmaker()
稍后，当你通过 create_engine() 创建了你的 engine 后，我们可以使用 configure() 来将其与 Session 对象关联：
>>> Session.configure(bind=engine) # once engine is available
定制 Session 类将创建新的 Session 对象来绑定到我们的数据库。其他事务特性也可在 sessionmaker() 中定义，这些我们将在后续的章节中讲解。一旦你需要和数据库发生交互，你需要实例化 Session：
>>> session = Session()
上述的 Session 与我们的 SQLite 引擎关联，但此时并不开启任何连接。当其第一次被使用的时候，他将从由引擎维护的连接池中检索出可用连接，并一直使用该连接知道提交所有的改变或关闭 session 对象。
为了持久化我们的 User 对象，我们通过 add() 方法将其添加到 Session 中：
>>> ed_user = User('ed', 'Ed Jones', 'edspassword') >>> session.add(ed_user)此时，该实例处于待处理状态，并不会生成任何的 SQL 代码。 Session 会在需要的时候通过一种名为 flush 的方法通过生成 SQL 语句来持久化该对象。如果此时我们在数据库中查询 Ed Jones，所有待处理信息将会首先被刷新，之后再生成查询。
例如，接下来我们将创建一个读取 User 实例的查询。我们通过 name 属性为 ed 的来过滤，并返回结果集中第一个对象。此时会返回一个等同于我们之前添加的 User 实例：
>>> our_user = session.query(User).filter_by(name='ed').first()
BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')
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 = ?
LIMIT 1 OFFSET 0
('ed',)>>> our_user
<User('ed','Ed Jones', 'edspassword')>实际上，Session 确认查询返回的对象和其内部对象映射中的某对象一致，因此我们实际查询得到的对象和我们刚才添加的完全一致：
>>> ed_user is our_user
True此处用到的被称为恒等映射的 ORM 机制确保了所有作用于数据库中的操作同时也会在 Session 中对应的数据中产生影响。一旦拥有特定主键的对象出现在 Session 中，所有在该 Session 上的对于该主键的 SQL 查询将会返回同一个 Python 对象；当我们此时尝试在该 Session 中添加一个已经持久化的同主键的第二个对象时，将会产生一个error。
我们可以通过使用 add_all() 方法来创建更多的 User 对象：
>>> session.add_all([
... User('wendy', 'Wendy Williams', 'foobar'),
... User('mary', 'Mary Contrary', 'xxg527'),
... User('fred', 'Fred Flinstone', 'blah')])同时， Ed 由于觉得自己的密码不够安全，因此需要重新设置其密码：
>>> ed_user.password = 'f8s7ccs'
Session 会关注到这一切。例如，他知道 Ed Jones 已经被改变了：
>>> session.dirty
IdentitySet([<User('ed','Ed Jones', 'f8s7ccs')>])而且此时还有三个新的 User 对象未处理：
>>> session.new
IdentitySet([<User('wendy','Wendy Williams', 'foobar')>,
<User('mary','Mary Contrary', 'xxg527')>,
<User('fred','Fred Flinstone', 'blah')>])我们可以告知 Session 我们现在需要将这些改变写入数据库，并且提交这些事务，事务将会贯穿我们的始终。我们通过 commit() 命令来完成：
>>> session.commit()
UPDATE users SET password=? WHERE users.id = ?
('f8s7ccs', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('wendy', 'Wendy Williams', 'foobar')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('mary', 'Mary Contrary', 'xxg527')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fred', 'Fred Flinstone', 'blah')
COMMITcommit() 负责将那些改变保存到数据库，并提交这些事务。此时由 session 引用的连接资源会被归还给连接池。该 session 后续的操作将会发生在另一个新的事务中，该事务会在需要时重新申请新的连接资源。
如果我们此时查看 Ed 的 id 属性，他现在将会拥有一个值，而之前这个值为 None：
>>> ed_user.id
BEGIN
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.id = ?
(1,)1
当 Session 将新的数据插入到数据库之后，此本实例中，所有新生成的标识和数据库生成的默认值均可以使用，不管是立即访问到还是通过载入访问的方式。在本实例中，由于这是一个 commit() 后的操作，整个数据将被重新载入。SQLAlchemy 默认情况下在一个新的事务第一次访问数据的时候会重新刷新数据，以保证数据的最新状态被访问到。重载数据的配置方式将在后续的 Sessions 章节中详细讲解。
由于 Session 工作在事务的基础上，因此我们可以回滚我们的改变。让我们创建两个稍后需要撤销的操作；ed_user 的名称变更为 Edwardo：
>>> ed_user.name = 'Edwardo'
接下来我们添加一个错误的用户， fake_user：
>>> fake_user = User('fakeuser', 'Invalid', '12345')
>>> session.add(fake_user)现在查看 session，我们可以看到他们已经刷新到当前事务中了：
>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
UPDATE users SET name=? WHERE users.id = ?
('Edwardo', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fakeuser', 'Invalid', '12345')
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 IN (?, ?)
('Edwardo', 'fakeuser')[<User('Edwardo','Ed Jones', 'f8s7ccs')>, <User('fakeuser','Invalid', '12345')>]
回滚之，我们可以看到 ed_user 的名称重新变更为 ed， fake_user 则被删除了：
>>> session.rollback()
ROLLBACK
>>> ed_user.name
BEGIN
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.id = ?
(1,)u'ed'
>>> fake_user in session
False我们通过下面的 SELECT 来描述数据库的变更：
>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
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 IN (?, ?)
('ed', 'fakeuser')[<User('ed','Ed Jones', 'f8s7ccs')>]
Query 通过在 Session 中使用 query() 方法来创建。该方法可以使用若干参数，这些参数可以是类的组合和类成员变量。接下来，我们会展示一个用于载入 User 实例的 Query。我们通过一个迭代器返回 User 对象：>>> for instance in session . query ( User ) . order_by ( User . id ):
... print instance . name , instance . fullnameed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred FlinstoneQuery 同样也接受 ORM 中的成员变量作为参数。一旦多个类实体或者基于字段的实体作为 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
Query 此时返回元组，该数据结构可以作为 python 的基本数据结构来处理。此时的 name 属性和字段名一致，类名则代表一个类：
>>> 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 ()
<User('ed','Ed Jones', 'f8s7ccs')> ed <User('wendy','Wendy Williams', 'foobar')> wendy <User('mary','Mary Contrary', 'xxg527')> mary <User('fred','Fred Flinstone', 'blah')> fred
你可以通过 label() 来构造字段的别名， aliased() 则用于构造类的别名：
>>> from sqlalchemy.orm import aliased >>> user_alias = aliased(User, name='user_alias') >>> for row in session.query(user_alias, user_alias.name.label('name_label')).all(): ... print row.user_alias, row.name_label
SELECT users_1.id AS users_1_id, users_1.name AS users_1_name, users_1.fullname AS users_1_fullname, users_1.password AS users_1_password, users_1.name AS name_label FROM users AS users_1 ()
<User('ed','Ed Jones', 'f8s7ccs')> ed <User('wendy','Wendy Williams', 'foobar')> wendy <User('mary','Mary Contrary', 'xxg527')> mary <User('fred','Fred Flinstone', 'blah')> fred
Query 中的基本操作通常包含 LIMIT 和 OFFSET，他们都可以很方便的使用 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.fullname AS users_fullname, users.password AS users_password FROM users ORDER BY users.id LIMIT 2 OFFSET 1 ()
<User('wendy','Wendy Williams', 'foobar')> <User('mary','Mary Contrary', 'xxg527')>
结果的过滤还可以通过在 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',)
ed
另外使用 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',)
ed
Query 对象是全生成型的，这意味着大部分返回 Query 对象的调用都可以添加更多的判定标准。例如，要查询一个名为“ed”切全名为“Ed Jones”，我们可以调用 filter() 两次，通过 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')
<User('ed','Ed Jones', 'f8s7ccs')>
常用过滤操作
此处列举了一系列用于 filter() 中的常用操作：
equals:
query.filter(User.name == 'ed')not equals:
query.filter(User.name != 'ed')LIKE:
query.filter(User.name.like('%ed%'))IN:
query.filter(User.name.in_(['ed', 'wendy', 'jack'])) # 可以和 query 对象协同工作： query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))NOT IN:
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))IS NULL:
filter(User.name == None)IS NOT NULL:
filter(User.name != None)AND:
from sqlalchemy import and_ filter(and_(User.name == 'ed', User.fullname == 'Ed Jones')) # 或者连续调用 filter()/filter_by() 两次 filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')OR:
from sqlalchemy import or_ filter(or_(User.name == 'ed', User.name == 'wendy'))match:
query.filter(User.name.match('wendy'))match 的参数内容由数据库后台指定。
返回列表和标量
Query 中的 all()，one()，和 first() 方法会立即生成 SQL 语句并返回非迭代器值。其中 all() 返回一个链表：
>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
sql>>> query.all()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 LIKE ? ORDER BY users.id
('%ed',)[<User('ed','Ed Jones', 'f8s7ccs')>, <User('fred','Fred Flinstone', 'blah')>]
first() 则用于限制为一个并返回第一个元素：
>>> query.first()
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 LIKE ? ORDER BY users.id
LIMIT 1 OFFSET 0
('%ed',)<User('ed','Ed Jones', 'f8s7ccs')>
one() 用于获取所有元素，如果没有获得结果或者返回了多个结果，则会产生一个 error：
>>> from sqlalchemy.orm.exc import MultipleResultsFound
>>> try:
... user = query.one()
... except MultipleResultsFound, e:
... print eSELECT 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 LIKE ? ORDER BY users.id
('%ed',)Multiple rows were found for one()
>>> from sqlalchemy.orm.exc import NoResultFound >>> try: ... user = query.filter(User.id == 99).one() ... except NoResultFound, e: ... print e No row was found for one()
使用文本 SQL
文本字符串可以在 Query 中灵活的使用。大部分的方法接受字符串参数除了 SQLAlchemy 。例如 filter() 和 order_by()：>>> for user in session.query(User).filter("id<224").order_by("id").all(): ... print user.name ed wendy mary fred在基于字符串的 SQL 语句中绑定参数可以通过冒号来指定，通过 params() 方法来为这些参数指定值：>>> session.query(User).filter("id<:value and name=:name").\ ... params(value=224, name='fred').order_by(User.id).one()通过 from_statement() 来使用一句完整的基于字符串的命令；需要确保的是描述中的字段名存在于映射类中（在下面的例子中我们使用 * 号）：>>> session.query(User).from_statement("SELECT * FROM users where name=:name").params(name='ed').all()[<User('ed','Ed Jones', 'f8s7ccs')>]你也可以通过 from_statement() 做到真正的原态，此时你需要使用字符串名来指明你需要的字段：>>> session.query("id", "name", "thenumber12").from_statement("SELECT id, name, 12 as thenumber12 FROM users where name=:name").params(name='ed').all() [(1, u'ed', 12)
计数
Query 提供一种非常便利的方法 count() 来计数：
>>> session.query(User).filter(User.name.like('%ed')).count() 2count() 方法用于确定 SQL 命令将要返回多少行。该方法主要目的是返回一个简单实体（此例中为 User）的计数。对于比较复杂的一组字段或实体的计数需要更加明确的定义，此时 count() 并不合适。下面的实例中，一个单字段的查询结果被正确的返回：
>>> session.query(User.id, User.name).filter(User.name.like('%ed')).count() 2但是如果你仔细分析生成的 SQL 就会发现， SQLAlchemy 认为我们使用的是单字段表达式并包装了我们在子查询中所做的一切，以确保能够返回行的数量。这样的保守行为并不是我们此时所需要的，在我们想要统计每个名字的数量等其他情况下也是如此：
>>> session.query(User.name).group_by(User.name).count() 4其实我们并不希望得到 4 这个数，我们需要返回更详尽的信息。因此如果你需要统计一些详细的查询结果，将 func.count() 方法作为一个字段表达式即可：
>>> from sqlalchemy import func>>> session.query(func.count(User.name), User.name).group_by(User.name).all()
()[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]现在我们开始考虑处理另一份表。我们系统中的用户们也可以存储任意数量的关联到其用户名的电子邮件地址。这意味着一个基本的一对多的映射，从 users_table 到一个用于电子邮件地址的一张名为 addresses 新表。通过 declarative，我们使用映射类 Address 来定义该表：
>>> from sqlalchemy import ForeignKey >>> from sqlalchemy.orm import relationship, backref >>> class Address(Base): ... __tablename__ = 'addresses' ... id = Column(Integer, primary_key=True) ... email_address = Column(String, nullable=False) ... user_id = Column(Integer, ForeignKey('users.id')) ... ... user = relationship(User, backref=backref('addresses', order_by=id)) ... ... def __init__(self, email_address): ... self.email_address = email_address ... ... def __repr__(self): ... return "<Address('%s')>" % self.email_address
上述类介绍了一种用于引用 users 表的外键约束。对于 SQLAlchemy 来说在数据库级定义了两张表之间的联系。User 和 Address 之间的关系通过使用 relationship() 方法来分别定义，通过该方法可以将 user 属性放置到 Address 类中，也可以将一组 addresses 集合放置到 User 类中。这种关系又被称为一种双向关系。由于外键的作用，从 Address 到 User 是一种多对一的关系，而从 User 到 Address 则是一种一对多的关系。SQLAlchemy 会在外键的基础上自动识别上述关系。
注意：
relationship() 方法曾被叫做 relation() 方法，该名称适用于0.6beta2之前的版本，包括0.5和0.4系列版本。SQLAlchemy 0.6beta2 之后才启用 relationship() 。在可预见的将来，relation() 将一直保留以保证兼容性。
relationship() 方法非常的灵活，在 User 类中也可以非常容易的使用：
class User(Base): # .... addresses = relationship(Address, order_by=Address.id, backref="user")
我们也可以不用指定逆指向，此时只在一个类中指定 relationship()。也可以通过定义两个独立的relationship() 来表示相互联系，此种方式通常在一对多和多对一的情况下是安全的，在多对多的情况下并不安全。
使用 declarative 扩展，relationship() 为我们提供一种使用字符串来获得目标类变量的选择，在此例中目标类还没有被定义。这只会在和 declarative 协同时有效：
class User(Base): .... addresses = relationship("Address", order_by="Address.id", backref="user")
在没有使用 declarative 的情况下，我们通常在目标类及 Table 已经定义好后才会定义我们的 mapper()，此时该表达式就么有必要了。
我们还是会需要在数据库中创建 addresses 表，因此我们还需要在 metadata 中发起新的 CREATE 操作，该操作会自动掠过已经创建的表：
>>> metadata.create_all(engine)
PRAGMA table_info("users") () PRAGMA table_info("addresses") () CREATE TABLE addresses ( id INTEGER NOT NULL, email_address VARCHAR NOT NULL, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES users (id) ) () COMMIT
现在当我们创建一个 User 对象，将会生成一个空的 addresses 集。像集合和 map 这样的各种各样的数据类型都有可能在此用到（详细参考 Alternate Collection Implementations），但通常来说该数据集返回 python 链表。
>>> jack = User('jack', 'Jack Bean', 'gjffdd') >>> jack.addresses []
我们现在可以随意的为 User 对象添加 Address 对象。此处，我们直接为其赋值一个链表：
>>> jack.addresses = [Address(email_address='jack@google.com'),Address(email_address='j25@yahoo.com')]
当使用了双向链接，在一方添加元素时另一方会自动获得该链接。这是 backref 关键字的作用，他负责在内存而非通过 SQL 维护链接关系：
>>> jack.addresses[1] <Address('j25@yahoo.com')> >>> jack.addresses[1].user <User('jack','Jack Bean', 'gjffdd')>
此时我们添加并提交 Jack Bean 到数据库中。jack 和他的 addresses 表中的 Address members 在本次会话中通过一种被称为 cascading（级联） 的方法一起提交：
>>> session.add(jack) >>> session.commit()
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?) ('jack', 'Jack Bean', 'gjffdd') INSERT INTO addresses (email_address, user_id) VALUES (?, ?) ('jack@google.com', 5) INSERT INTO addresses (email_address, user_id) VALUES (?, ?) ('j25@yahoo.com', 5) COMMIT
此时查询 Jack 的信息，我们只获得 Jack，并没有生成 SQL 去获取 Jack 的 addresses：
>>> jack = session.query(User).filter_by(name='jack').one() >>> jack <User('jack','Jack Bean', 'gjffdd')>接下来我们来查询 addresses，看看 SQL 语句：
>>> jack.addresses
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]
当我们访问 addresses 集合时，才生成SQL语句。这是一个延迟加载关系的实例。 addresses 集合此时就像对待一个原始列表一样载入之。
如果想减少查询的次数（戏剧的是，大多数情况均是如此），我们可以在查询操作时通过使用 joinedload() 函数立即检索。该函数是一项用于指定查询如何载入的额外选项，此例中，我们希望 addresses 立即载入。SQLAlchemy 此时创建一个用于联接 users 和 addresses 表的外联接，并立即载入他们，并利用 addresses集合填充每一个 User 对象：
>>> from sqlalchemy.orm import joinedload
>>> jack = session.query(User).\
... options(joinedload('addresses')).\
... filter_by(name='jack').one()SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password, addresses_1.id AS addresses_1_id, addresses_1.email_address
AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ORDER BY addresses_1.id
('jack',)>>> jack
<User('jack','Jack Bean', 'gjffdd')>
>>> jack.addresses
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]在使用 joinedload() 函数创建联接时，我们还可以用多种方式明确的使用联接。例如，为了在 User 和Address 之间创建内联接，我们仅需要使用 filter() 他们之间的关联列即可。接下来我们使用该方法来一起载入 User 和 Address 实体：
>>> for u, a in session.query(User, Address).filter(User.id==Address.user_id).\ ... filter(Address.email_address=='jack@google.com').all(): ... print u, a
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id FROM users, addresses WHERE users.id = addresses.user_id AND addresses.email_address = ? ('jack@google.com',)
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>
或者我们可以使用真正的 JOIN 来创建，最通常的用法是使用 join() 方法：
>>> session.query(User).join(Address).\ ... filter(Address.email_address=='jack@google.com').all()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id WHERE addresses.email_address = ? ('jack@google.com',)
[<User('jack','Jack Bean', 'gjffdd')>]
由于他们之间只有一个外键，因此 join() 知道如何去联接 User 和 Address。如果没有任何外键或者存在多个外键，使用如下的方式会使 join() 工作的更好：
query.join((Address, User.id==Address.user_id)) # explicit condition (note the tuple) query.join(User.addresses) # specify relationship from left to right query.join((Address, User.addresses)) # same, with explicit target query.join('addresses') # same, using a string
注意当 join() 以明确的对象以及 ON 语句来调用时，我们使用元组参数。这也是为什么多重联接也能正确工作的原因，参考：
session.query(Foo).join( Foo.bars, (Bat, bar.bats), (Widget, Bat.widget_id==Widget.id) )
以上查询会生成类似于如下形式的 SQL 语句： foo JOIN bars ON <onclause> JOIN bats ON <onclause>JOIN widgets ON <onclause>.
join() 函数也可以作为一个独立的函数使用，该函数是一个由 SQL expression language 提供的同名的启用 ORM 的版本。该函数接受两至三个参数（左边，右边，可选的 ON 语句）并通过 select_from() 方法被应用于联接中以指定明确的 FROM 语句：
>>> from sqlalchemy.orm import join >>> session.query(User).\ ... select_from(join(User, Address, User.addresses)).\ ... filter(Address.email_address=='jack@google.com').all()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id WHERE addresses.email_address = ? ('jack@google.com',)
[<User('jack','Jack Bean', 'gjffdd')>]
使用 join() 立即查询集合和属性
joinedload() 方法提供的立即查询功能以及 join() 的联合构造能力可以通过使用 contains_eager() 选项合并在一起。这是基于已经联接至某些相关实体的查询的典型用法（多对一的情况下屡见不鲜），此时我们更倾向于相关实体一步到位的载入到结果对象，而不需要浪费额外的查询，也不需要通过joinedload() 方法自动联接。
>>> from sqlalchemy.orm import contains_eager >>> for address in session.query(Address).\ ... join(Address.user).\ ... filter(User.name=='jack').\ ... options(contains_eager(Address.user)): ... print address, address.user
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id FROM addresses JOIN users ON users.id = addresses.user_id WHERE users.name = ? ('jack',)
<Address('jack@google.com')> <User('jack','Jack Bean', 'gjffdd')> <Address('j25@yahoo.com')> <User('jack','Jack Bean', 'gjffdd')>
需要注意的是上面的 join 同样也用于将 Address 限制在与 User 对象名为“jack”相关行上。通过内联接的方式填充 Address.user 属性是非常安全的。无论如何，当在联接上进行过滤相当于在一个集合的特定元素上进行过滤，通过 contains_eager() 方式来填充相关集合可能仅仅只填充部分，因为集合自身已经经过了过滤。
使用别名
当使用多表查询，如果同一张表需要被多次访问， SQL 通常需要该表申明一个别名，以区别之。 Query通过使用 aliased 支持该功能。接下来我们会两次联接 Address 实体，以查询同时拥有两个不同 email 地址的用户：
>>> from sqlalchemy.orm import aliased >>> adalias1 = aliased(Address) >>> adalias2 = aliased(Address) >>> for username, email1, email2 in \ ... session.query(User.name, adalias1.email_address, adalias2.email_address).\ ... join((adalias1, User.addresses), (adalias2, User.addresses)).\ ... filter(adalias1.email_address=='jack@google.com').\ ... filter(adalias2.email_address=='j25@yahoo.com'): ... print username, email1, email2
SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address, addresses_2.email_address AS addresses_2_email_address FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id WHERE addresses_1.email_address = ? AND addresses_2.email_address = ? ('jack@google.com', 'j25@yahoo.com')
jack jack@google.com j25@yahoo.com
使用子查询
Query 还适用于生成用于子查询的语句。假设我们希望根据其拥有的 Address 记录数量来载入 User 对象。此问题最佳的生成 SQL 方法是将 user 通过 addresses 数量对 ids 进行分组，并将其与父属进行联接。在本例中，我们使用左外联接以确保即便没有任何地址的用户也能被正确返回：
SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN (SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count ON users.id=adr_count.user_id
通过使用 Query，我们从内到外创建了一个这样的语句。该语句返回一个用于特殊 Query 的 SQL 表达式 - 参考 SQL Expression Language Tutorial：
>>> from sqlalchemy.sql import func >>> stmt = session.query(Address.user_id,func.count('*').label('address_count')).group_by(Address.user_id).subquery()
The func keyword generates SQL functions, and the subquery() method on Query produces a SQL expression construct representing a SELECT statement embedded within an alias (it’s actually shorthand for query.statement.alias()).
Once we have our statement, it behaves like a Table construct, such as the one we created for users at the start of this tutorial. The columns on the statement are accessible through an attribute called c:
>>> for u, count in session.query(User, stmt.c.address_count).\ ... outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): ... print u, count <User('ed','Ed Jones', 'f8s7ccs')> None <User('wendy','Wendy Williams', 'foobar')> None <User('mary','Mary Contrary', 'xxg527')> None <User('fred','Fred Flinstone', 'blah')> None <User('jack','Jack Bean', 'gjffdd')> 2Selecting Entities from Subqueries
Above, we just selected a result that included a column from a subquery. What if we wanted our subquery to map to an entity ? For this we use aliased() to associate an “alias” of a mapped class to a subquery:
>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery() >>> adalias = aliased(Address, stmt) >>> for user, address in session.query(User, adalias).join((adalias, User.addresses)): ... print user, address <User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>Using EXISTS
The EXISTS keyword in SQL is a boolean operator which returns True if the given expression contains any rows. It may be used in many scenarios in place of joins, and is also useful for locating rows which do not have a corresponding row in a related table.
There is an explicit EXISTS construct, which looks like this:
>>> from sqlalchemy.sql import exists >>> stmt = exists().where(Address.user_id==User.id) >>> for name, in session.query(User.name).filter(stmt): ... print name jackThe Query features several operators which make usage of EXISTS automatically. Above, the statement can be expressed along the User.addresses relationship using any():
>>> for name, in session.query(User.name).filter(User.addresses.any()): ... print name jackany() takes criterion as well, to limit the rows matched:
>>> for name, in session.query(User.name).\ ... filter(User.addresses.any(Address.email_address.like('%google%'))): ... print name jackhas() is the same operator as any() for many-to-one relationships (note the ~ operator here too, which means “NOT”):
>>> session.query(Address).filter(~Address.user.has(User.name=='jack')).all() []
2342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
