SQLAlchemy-tutorial

声明映射

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

现在我们有了一个base, 我们可以从它派生任意多的映射类。我们先从一个简单的users表开始。这张表保存了所有使用我们应用程序的用户信息。 User类会映射这个表。在这个类中，我们定义了关于被映射表的细节，包括数据表名，列名等：

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...                             self.name, self.fullname, self.nickname)

一个映射类最少需要一个tablename属性，并且最少有一个是primary key的一部分的Column。 SQLAlchemy不会对一个class指向哪个table做出推测，也没有内建的names，datatypes，或constraints的约定。但这并不意味着需要模板；相反，我们鼓励你去用helper functions & mixin classes写自己的自动化约定。详情参考Mixin and Custom Base Classes。
当创建好类后，Declarative用Python accessors（又叫 descriptors）；这个过程被称作instrumentation. Instrumented mapped class将给我们提供引用数据表和存取数据的能力。

抛开映射过程对映射class的处理，映射class本质上是python class，我们可以自由定义属性和方法。

关于为什么一定要有primary key，请参考How do I map a table that has no primary key?.

Create a Schema

我们用Declarative System创建好了User class，定义了关于table的信息，这些信息被称作table metadata。用来表示某个table的对象是一个Table class的实例，现在Declarative System已经为User class自动创建了这样的对象。我们可以用User.table访问：

>>> User.__table__ 
Table('users', MetaData(bind=None),
            Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('nickname', String(), table=<users>), schema=None)

当我们声明User class时，一旦类的声明完成，Declarative System利用Python metaclass自动完成一些额外行为；在这个时期，会创建一个Table对象，并且给它再创建一个Mapper对象指向User class。 Table对象通常在幕后，我们通常并不需要直接操作它（但是它能在我们需要的时候提供关于映射的信息）。

Table对象是一个更大合集， MetaData的成员。当使用Declarative System的时候， MetaData对象可以通过Base对象的访问：

metadata = Base.metadata

MetaData是一个包含了生成一系列声明式SQL语句的能力的注册表，现在我们的SQLite数据库里还没有users table，我们可以用MetaData来生成CREATE TABLE声明，以生成所有尚不存在的表。如下，我们调用了MetaData.create_all()方法，传入engine作为数据库连接。我们从SQL语句看出，先检查了是否存在users table，发现不存在后，接着执行了CREATE TABLE语句：

>>> Base.metadata.create_all(engine)
SELECT ...
PRAGMA table_info("users")
()
CREATE TABLE users (
    id INTEGER NOT NULL, name VARCHAR,
    fullname VARCHAR,
    nickname VARCHAR,
    PRIMARY KEY (id)
)
()
COMMIT

最简Table信息描述和全描述

熟悉SQL的朋友们会发现，CREATE TABLE没有指定VARCHAR的长度；用SQLite或者PostgreSQL，这是一个有效的数据声明，但是其他数据库不支持，因此，你应该像下面一样指定其长度：

name = Column(String(50))

如同String的长度，类似于Integer， Numeric的精度，除非创建tables的时候，都不会被SQLAlchemy引用。

额外的，Firebird和Oracle需要sequences来生成新的主键， SA不会自动生成，请显示调用Sequence类。

from sqlalchemy import Sequence
Column(Integer, Sequence('user_id_seq'), primary_key=True)

所以一个用Declarative System生成的Table，稳妥的定义方式如下：

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))

    def __repr__(self):
        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
                                self.name, self.fullname, self.nickname)

我们加入了了这个更加冗长的table定义方式，是为了强调最简化定义和详细定义的差异，后者在需要用CREATE TABLE命令的时候是非常必要的。

创建一个Mapped Class的实例

映射已经完成了，我们开始创建一个User对象：

>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> ed_user.name
'ed'
>>> ed_user.nickname
'edsnickname'
>>> str(ed_user.id)
'None'

虽然我们并没有指定id属性，当我们访问ed_user的id属性依然返回了None（不同于python的常规行为，抛出一个AtributeError异常）。当首次访问column-mapped的属性时，SQLAlchemy的instrumentation会产生一个默认值（None for example）。对那些我们指定了值的属性，Instrumentation system会追踪那些被赋予的值，以在最终执行INSERT的时候，保存这些值到数据库。

the init() method

User class有Declarative System自动生成的 __init__()方法，把所有我们映射过的column字段作为关键字参数。如果你想用自己的__init__()方法，可以在类里定义，会把默认提供的覆盖掉。

创建一个会话

我们现在准备好了跟数据库“聊一聊”了。 ORM的操作手柄是Session。当第一次访问这个应用的时候，我们在create_engine()的时候，建立一个Session class, 作为产生新的Session对象的工厂：

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

在你的应用在定义Session的时候，如果没有创建任何Engine对象，那么可以这样：

>>> Session = sessionmaker()

稍后你用create_engine()创建了engine后，用Session类的configure()方法把engine与其关联起来：

>>> Session.configure(bind=engine)  # once engine is available

这样自定义的Session class可以创建新的连接到数据库的Session对象实例。其他的事务性特征在调用sessionmaker()的时候被定义；在之后的章节会介绍这个过程。从此，你想要跟数据库交互的时候，你只需要实例化Session类：

>>> session = Session()

以上的Session与我们SQLite-enabled Engine关联，但是并没有打开任何真正的连接。当我们第一次使用session的时候，它会从Engine维护的一个连接池中取出一个连接，并且保持这个连接直到提交了所有的改动或关闭了session对象（不知道是session可以调用close()还是del session）。

Session生命周期模型

什么时候创建Session很大程度上取决于你要创建一个什么样的应用。记住，Session只是你所有操作的对象的工作空间，某个特定数据库连接的本地内容——如果你把应用程序的线程想像成一个晚宴的客人，Session就是餐桌上的盘子，你操作的objects就是盘子里的食物，而database...大概就是厨房？关于这个话题的更多内容请参考When do I construct a Session, when do I commit it, and when do I close it?.

添加和更新Objects

为了保存User object到数据库，我们用add()方法把它添加到Session：

>>> ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
>>> session.add(ed_user)

在这个时间点，我们称这个ed_user处于pending状态；没有执行任何SQL语句，并且这个对象也没有变成数据库的一行数据。当必要的时候，Session会把这条数据存入数据库，用所谓的flush process来完成。如果我们在数据库里查找Ed Jones，所有的pending信息都会先被flushed，然后再执行query。

例如，我们在前面的基础上，创建一个新的Query，查找User表的name为ed的第一个*对象。返回的User对象就是我们刚才add到session里的ed_user：

>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
<User(name='ed', fullname='Ed Jones', nickname='edsnickname')>

正在使用的ORM概念是所谓的 identity map，确保session中所有对特定row的操作，都在操作同一组数据。一旦一个有primary key的对象出现在session中，所有的查询到这个对象的query都会返回同一个python对象；如果试图在session中放有重复primarykey的对象，会报错。

我们可以一次性添加多个User objects,用add_all()：

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', nickname='windy'),
...     User(name='mary', fullname='Mary Contrary', nickname='mary'),
...     User(name='fred', fullname='Fred Flintstone', nickname='freddy')])

并且，如果我们觉得Ed的nickname不够好，我们可以更改它：

>>> ed_user.nickname = 'eddie'

Session会关注add到它里面的对象的变化，例如， Ed Jones被修改过了：

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', nickname='eddie')>])

并且还有三个新建的User对象处于pending状态：

>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', nickname='windy')>,
<User(name='mary', fullname='Mary Contrary', nickname='mary')>,
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>])

通过调用commit()方法，我们告诉Session我们想把所有的改动同步到数据库去，并且提交一个会话。 Session会对改动产生UPDATE语句，对插入内容产生INSERT语句。

 session.commit()

commit() flushes剩下的改动到数据库，并提交事务。该session占用的数据库连接会归还给连接池。随后在这个session上的操作将会开启一个new 事务，同时需要重新从连接池获取一个连接。

如果我们查看Ed's id属性，之前值为None，但是变了：

>>> ed_user.id 
1

当Session向数据库插入数据后，新生成的标识符（此处为primary key）和列的默认值都将可用(要么是立马可用，要么是首次访问加载)。在上面的例子中，整行数据都重新加载了，因为新的事务在我们commit()之后开始了。SQLAlchemy默认，在首次从新事务中访问对象的时候，刷新之前事务涉及的数据，这样保证能取得最新的数据。重载数据的时机可以配置，参考Using the Session.

Session Object States

User对象从session外，内，再到真正被插入到数据库，实际上这个对象经历了4个abject states中的三个——transient， pending和persistent。了解这些状态非常有必要——确保读过 Quickie Intro to Object States有个大概了解。

回滚（Rooling back）

因为Session是以事务的形式工作的，我们可以回滚改动。我们来做出两处修改；ed_user的胡勇明改成Edwardo：

>>> ed_user.name = 'Edwardo'

然后我们添加一个不正确的user, fake_user:

>>> fake_user = User(name='fakeuser', fullname='Invalid', nickname='12345')
>>> session.add(fake_user)

查询这个会话，我们可以看到这两个操作都已经被flush到当前事务中了：

>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
[<User(name='Edwardo', fullname='Ed Jones', nickname='eddie')>, <User(name='fakeuser', fullname='Invalid', nickname='12345')>]

回滚后，我们可以看到ed_user的名字已经变回ed，并且fake_user已经从会话中被剔除：

>>> session.rollback()
>>> ed_user.name
u'ed'
>>> fake_user in session
False

用如下SELECT来演示到数据库的改动：

>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

查询

一个Query对象是用Session的Query()方法创造的。这个方法需要一个或多个类作为参数。如下，我们告诉Query对象加载User实例。当迭代结果时，将得到User对象数组：

>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

query()方法也接受ORM-instrumented描述符作为参数。只要存在多个参数，返回的将是tuple的数组：

>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

Query返回的tuples都是named tuples，属于 KeyedTuple 类，可以被当作普通的python对象。 name属性是Query参数里的类名或属性名：

>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)
<User(name='ed', fullname='Ed Jones', nickname='eddie')> ed
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> wendy
<User(name='mary', fullname='Mary Contrary', nickname='mary')> mary
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> fred

你可给column指定别的名字，以防有重名的情况：

>>> for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)
ed
wendy
mary
fred

类名的name可以用aliased()来自定义：

>>> 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)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

Query的基础操作支持LIMIT 和 OFFSET，最方便的用法是用python的数组切片形式，通常会和ORDER BY一起使用：

>>> for u in session.query(User).order_by(User.id)[1:3]:
...    print(u)
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>

过滤查询结果用filter_by()，以关键字参数的形式传入条件：

>>> for name, in session.query(User.name).\
...             filter_by(fullname='Ed Jones'):
...    print(name)
ed

也可以用filter()，可以用更灵活的方式表达过滤条件。下例子演示了如何用常规的python运算符表达某一列的特征：

>>> for name, in session.query(User.name).\
...             filter(User.fullname=='Ed Jones'):
...    print(name)
ed

Query对象是可以链式调用的，意味着每次调用都返回一个新的Query对象，并结合了之前的条件。如下例，你可以调用filter()两次，查询条件会被AND结合起来：

>>> for user in session.query(User).\
...          filter(User.name=='ed').\
...          filter(User.fullname=='Ed Jones'):
...    print(user)
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

通用过滤操作符

下面是一些在filter()常用操作符的概要：

equals:

query.filter(User.name == 'ed')

not equals:

query.filter(User.name != 'ed')

LIKE:

query.filter(User.name.like('%ed%'))

Note
ColumnOperators.like() renders the LIKE operator, which is case insensitive on some backends, and case sensitive on others. For guaranteed case-insensitive comparisons, useColumnOperators.ilike().

ILIKE (case-insensitive LIKE):

query.filter(User.name.ilike('%ed%'))

Note
most backends don’t support ILIKE directly. For those, the ColumnOperators.ilike() operator renders an expression combining LIKE with the LOWER SQL function applied to each operand.

-IN:

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%'))
))

NOT IN:

query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

-IS NULL:

query.filter(User.name == None)

# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))

-IS NOT NULL:

query.filter(User.name != None)

# alternatively, if pep8/linters are a concern
query.filter(User.name.isnot(None))

AND:

# use and_()
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))

# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')

# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

Note
Make sure you use and_() and not the Python and operator!

OR:

from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))

Note
Make sure you use or_() and not the Python or operator!

MATCH:

query.filter(User.name.match('wendy'))

Note
match() uses a database-specific MATCH or CONTAINS function; its behavior will vary by backend and is not available on some backends such as SQLite.

Returning Lists and Scalars

一些方法在Query对象上调用了之后会立马执行SQL，并返回结果。以下是一个简单的介绍：

all() returns a list:

>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
>>> query.all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>,
      <User(name='fred', fullname='Fred Flintstone', nickname='freddy')>]

first() applies a limit of one and returns the first result as a scalar:

>>> query.first()
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

one() 获取所有的行，如果结果不是刚好直有一行数据，会抛出异常：

>>> user = query.one()
Traceback (most recent call last):
...
MultipleResultsFound: Multiple rows were found for one()

当一行都没找到的时候：

>>> user = query.filter(User.id == 99).one()
Traceback (most recent call last):
...
NoResultFound: No row was found for one()

one()方法很适合需要区分没找到结果和找到太多结果这两种情况的应用；比如一个RESTful web API，可能需要在没找到结果的时候返回404，在找到很多结果的时候返回500。

one_or_none()类似于one()，区别在于当没找到记录的时候，前者会返回None。
scalar() 调用one() 方法，并返回其结果的第一列数据：

>>> query = session.query(User.id).filter(User.name == 'ed').\
...    order_by(User.id)
>>> query.scalar()
1

使用文本SQL

字面量字符串可以通过text()，作用于Query，大部分的方法如filter(), order_by()都接受text()作为参数：

>>> from sqlalchemy import text
>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)
ed
wendy
mary
fred

绑定的变量可以通过类似字符串模板的方式传入，使用params() method:

>>> session.query(User).filter(text("id<:value and name=:name")).\
...     params(value=224, name='fred').order_by(User.id).one()
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

使用更彻底的字符串声明，可以把text() 作为参数传递给 from_statement()，不需要额外的标识符，就可以判定需要选择哪些列，如下*代表选择所有列：

>>> session.query(User).from_statement(
...                     text("SELECT * FROM users where name=:name")).\
...                     params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

Counting

Query 包含了一个查询数量的方法-count():

>>> session.query(User).filter(User.name.like('%ed')).count()
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.nickname AS users_nickname
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)
2

count()方法确定多少行数据会被返回。从上面的SQL语句可以看出, SQLAlchemy总是把我们的查询放到一个subquery里，然后再统计这个subquery的个数。在某些情况下，这会被简化为SELECT count(*) FROM table,然而最近新版本的SQLAlchemy不会再去推测什么时候简化，as the exact SQL can be emitted using more explicit means.
在需要明确指定count的对象的时候，用func.count()，下面我们用它来得到每个独一的user name的数量：

>>> 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')]

Counting on count()
Query.count()过去是一个非常复杂的函数，因为它尝试去分析是否需要把已有的查询条件放到subquery里，在某些情况下会出错。现在它每次都用subquery，代码长度只有两行并且每次都返回正确的答案。func.count()在你不能接受subquery的时候使用。

为了实现SELECT count(*) FROM table,我们可以这样用：

>>> session.query(func.count('*')).select_from(User).scalar()
4

如果我们直接在count参数里指明类名和列名， select_from()可以去掉：

>>> session.query(func.count(User.id)).scalar()
4

创建关系

让我们考虑一下，怎么映射一张和User有关系的表，并查询它。一个User可以有多个邮件地址，这是一个基本的一对多关系。存储邮件地址的表就叫addresses表。我们像之前那样用declarative的方式定义它的表结构和迎映射类：


>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship

>>> 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", back_populates="addresses")
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

>>> User.addresses = relationship(
...     "Address", order_by=Address.id, back_populates="user")

ForeignKey是对Column的一个指令，意思是Column的值constrained远程的column。这是关系型数据库的核心。addresses.user_id的值只能是users.id的值的一个。也就是主键中的一个。
另一个指令， relationship()，告诉ORMAddress应该和User关联，用Address.user指向User。relationship()用两张表的外键关系来决定他们之间的关系连接，发现Address.user是多对一。有一个额外的relationship()指令在User类上，及User.addresses。在大多数的relationship()指令中， relationship.back_populates参数是用来指定一个与此表关联的表的互补属性名的。这样，每个 relationship()都可以知道关系的两端是什么；一方面来说，Address.user指向User对象，另一方面，User.addresses指向一个Address的数组。

Note
relationship.back_populates和 relationship.backref基本上是一回事儿。前者是新版本特性，后者也会被继续支持，关于二者区别请参考 Linking Relationships with Backref。
多对一的反面永远是一对多。关于relationship()的配置请参考Basic Relationship Patterns。

Address.user和User.addresse的互补关系叫做bidirectional relationship，也是SQLAlchemy ORM的关键特性。 Linking Relationships with Backref详细讨论了"backref"特性。
Arguments to relationship() which concern the remote class can be specified using strings, assuming the Declarative system is in use. Once all mappings are complete, these strings are evaluated as Python expressions in order to produce the actual argument, in the above case the User class. The names which are allowed during this evaluation include, among other things, the names of all classes which have been created in terms of the declared base.

See the docstring for relationship() for more detail on argument style.

Working with Related Objects

现在我们创建一个新User，一个空的addresses合集将会产生。默认这个合集为python list类型。也可以选择sets和dictionaries等类型，参考Customizing Collection Access。

>>> jack = User(name='jack', fullname='Jack Bean', nickname='gjffdd')
>>> jack.addresses
[]

我们可以添加Address对象：

>>> jack.addresses = [
...                 Address(email_address='jack@google.com'),
...                 Address(email_address='j25@yahoo.com')]

当使用双向关系后，在一个方向添加了对象后，在另一个方向也可见了。这个行为是通过，属性改变事件完成的，整个过程，没有用SQL语句：

>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

我们把jack添加到session里，两个Address也自动被加进去了。这个过程叫做cascading：

>>> session.add(jack)
>>> session.commit()

当查询jack的时候，不会有查询jack的addresses的SQL语句：

>>> jack = session.query(User).\
... filter_by(name='jack').one()
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
('jack',)

>>> jack
<User(name='jack', fullname='Jack Bean', nickname='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(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

可以看到SQL执行了查询address的语句，这是一个关于lazy loading的例子，我们会提到几种优化加载relationship的对象(addresses)的方法。

Querying with Joins

现在我们已经有了两个表，我们可以展示Query更多的特性，特别是关于怎么一次性查询两个表。 Wikipedia page on SQL JOIN提供了一个很好的关于join技术的介绍。

创建一个User和Address的简单隐式Join，可以用Query.filter()实现，把他们关联的列相等作为查询条件：

>>> for u, a in session.query(User, Address).\
...                     filter(User.id==Address.user_id).\
...                     filter(Address.email_address=='jack@google.com').\
...                     all():
...     print(u)
...     print(a)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

类似SQL JOIN语法，用Query.join()：

>>> session.query(User).join(Address).\
...         filter(Address.email_address=='jack@google.com').\
...         all()
[<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>]

Query.join()知道怎么自动join User和Address两张表，因为他们之间只有一个外键。如果他们之间没有外键，或者有好几个外键，Query.join()按下列格式之一使用：

query.join(Address, User.id==Address.user_id)    # explicit condition
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

你可能猜到了，对于outer join-outerjoin()，也是一样的：

query.outerjoin(User.addresses)   # LEFT OUTER JOIN

join()的详细介绍在此，很重要的特性，一定要会哦。

如果Query()方法接受好几个实体作为参数，应该选择那个来作为默认的SELECT呢？
答案：如果没有ON语句或者ON语句是字符串形式，那就选择最左边的item。你可以用Query.select_from()方法指定：
query = session.query(User, Address).select_from(Address).join(User)

使用别名

当查询多张表时，如果某张表需要被多次引用，SQL需要那张表有个别名。Query通过aliased概念支持这个特性，如下，我们两次joinAddress表，以获取有某两个email address的用户：

>>> 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).\
...     join(adalias2, User.addresses).\
...     filter(adalias1.email_address=='jack@google.com').\
...     filter(adalias2.email_address=='j25@yahoo.com'):
...     print(username, email1, email2)
jack jack@google.com j25@yahoo.com

使用子查询

Query也可以用来生成子查询。假设我们我们想加载所有的User对象并且包含每个User对象的Address个数。最好的方法是生成一个这样的SQL语句，按照user id分组，获取每个user的addresses数量，并且JOIN到父查询。在这个例子中我们用LEFT OUTER JOIN，来获取没有任何Adress的User：

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，我们由内而外地创造一个这样的SQL。statement accessor返回一个SQL表达式，这个表达式是由一个Query生成的-这也是select()的一个实例。详情参考 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()

func关键字代表了SQL函数，在Query上调用的subquery()方法产生了一条SQL表达式，它代表了一条包含alias（query.statement.alias()的short hand）的SELECT语句。
现在我们拿到了stmt变量，它的行为实际上像一个Table，就像我们一开始创造的users表。可以通过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(name='ed', fullname='Ed Jones', nickname='eddie')> None
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> None
<User(name='mary', fullname='Mary Contrary', nickname='mary')> None
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> None
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')> 2

从子查询选择Entities

刚才我们是从子查询中select了一个count列而已，如果我们要select子查询结果代表的对象实体呢？我们可以用aliased来把一个子查询结果和映射类关联起来：

>>> 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)
...     print(address)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

使用EXISTS

EXISTS关键字在SQL里是一个boolean操作符，当给定的查询不为空，它返回 True 。它可能会在很多join的场景里被用到，用来判定给定的行在其关联的表中是否有对应的行，也十分有用。

这是一个典型的EXISTS应用：

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
>>> for name, in session.query(User.name).filter(stmt):
...     print(name)
jack

Query实现了几个自动使用EXISTS的特性。上面的例子可以用 any()来替代：

>>> for name, in session.query(User.name).\
...         filter(User.addresses.any()):
...     print(name)
jack

any() 也可以接受参数，来筛选匹配项：

>>> for name, in session.query(User.name).\
...     filter(User.addresses.any(Address.email_address.like('%google%'))):
...     print(name)
jack

has() 和any()差不多的，只不过是用在多对一的relationships上。～意思是"NOT":

>>> session.query(Address).\
...         filter(~Address.user.has(User.name=='jack')).all()
[]

Common Relationship Operators

最后编辑于：2019.10.21 10:54:48

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,921评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,635评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,393评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,836评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,833评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,685评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,043评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,694评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,671评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,670评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,779评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,424评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,027评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,984评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,214评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,108评论 2赞 351
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,517评论 2赞 343