对象关系教程¶

SQLAlchemy ORM提供了将用户定义的Python类和数据库表以及这些类（对象）的实例与其对应表中的行相关联的方法。它包括一个透明地同步对象及其相关行之间状态的所有更改的系统，称为工作单元，以及用于根据用户定义的类及其定义的表达式表达数据库查询的系统彼此之间的关系。

ORM与构建ORM的SQLAlchemy表达式语言形成鲜明对比。鉴于在SQL表达式语言教程中引入的SQL表达式语言提供了直接表示关系数据库的原始结构的系统，而没有意见，ORM呈现高级别和抽象的使用模式，是表达式语言的应用使用的示例。

虽然ORM和表达式语言的使用模式之间存在重叠，但它们的相似之处却比最初出现时更为肤浅。从用户定义的域模型的角度来看，数据的结构和内容是透明持久化的，并从其底层存储模型刷新。另一种方法从文字模式和SQL表达式表达式的角度来看，它们被显式地组合成数据库单独消费的消息。

可以仅使用对象关系映射器来构建成功的应用。在高级情况下，使用ORM构建的应用程序可能会在需要特定数据库交互的某些区域中直接偶尔使用表达式语言。

The following tutorial is in doctest format, meaning each >>> line represents something you can type at a Python command prompt, and the following text represents the expected return value.

版本检查¶

快速检查以确认我们至少处于SQLAlchemy的版本1.1：

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
1.1.0

连接¶ T0>

对于本教程，我们将使用一个仅内存的SQLite数据库。要连接，请使用create_engine()：

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

echo标志是设置SQLAlchemy日志记录的快捷方式，它是通过Python的标准日志模块完成的。启用它，我们将看到生成的所有SQL。如果您正在学习本教程并希望产生更少的输出，请将其设置为False。本教程将把SQL格式化为一个弹出窗口，所以它不会妨碍我们；只需点击“SQL”链接即可查看正在生成的内容。

create_engine()的返回值是Engine的一个实例，它表示数据库的核心接口，通过处理方言数据库和DBAPI的使用细节。在这种情况下，SQLite方言将向Python内置的sqlite3模块解释指令。

第一次调用Engine.execute()或Engine.connect()的方法时，Engine才会建立一个真实的DBAPI 连接到数据库，然后用于发出SQL。使用ORM时，我们通常不会直接使用Engine；相反，它在后台被ORM使用，我们将很快看到。

也可以看看

数据库网址 - 包括连接到多种数据库的create_engine()示例，其中包含指向更多信息的链接。

声明映射¶

当使用ORM时，配置过程首先描述我们将要处理的数据库表，然后定义我们用来映射到那些表的类。在现代SQLAlchemy中，这两个任务通常使用称为Declarative方法一起执行，这允许我们创建包含指令的类来描述它们将被映射到的实际数据库表。

使用Declarative方法定义的映射类依据一个基类，这个基类是维系类和数据表关系的目录 - 我们说说的Declarative base class。在一个普通的模块入口中，应用通常只需要有一个base的实例。我们使用declarative_base()函数创建基类，如下所示：

>>> 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)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % (
...                             self.name, self.fullname, self.password)

使用Declarative的类至少需要一个__ tablename __属性和至少一个Column，它是主键[1]的一部分。SQLAlchemy从不对类引用的表本身做任何假设，包括它没有内置的名称，数据类型或约束的约定。但这并不意味着需要样板；而是鼓励使用辅助函数和mixin类创建自己的自动约定，这在Mixin和Custom Base Classes中有详细描述。

构造类时，Declarative会使用称为描述符的特殊Python访问器替换所有Column对象；这是一个称为instrumentation的过程。“instrumented”映射类将为我们提供在SQL上下文中引用我们的表的方法，以及从数据库中持久化和加载列的值。

除了映射过程对我们的类做的外，类另外主要是一个普通的Python类，我们可以定义任何数量的普通属性和我们的应用程序所需的方法。

[1] T0>

有关为什么需要主键的信息，请参阅如何映射没有主键的表？。

构造模式¶

使用通过声明式系统构建的User类，我们定义了有关表的信息，称为表元数据。SQLAlchemy用于表示特定表的此信息的对象称为表对象，这里Declarative已经为我们做了一个。我们可以通过检查__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('password', String(), table=<users>), schema=None)

当我们声明我们的类时，Declarative使用Python元类来完成类声明完成后的额外活动；在这个阶段，然后根据我们的规范创建一个Table对象，并通过构造一个Mapper对象将其与类关联。这个对象是我们通常不需要直接处理的幕后对象（虽然它可以在我们需要时提供关于我们映射的大量信息）。

Table对象是一个更大集合的成员，名为MetaData。当使用Declarative时，这个对象可以使用我们的声明基类的.metadata属性。

The MetaData is a registry which includes the ability to emit a limited set of schema generation commands to the database. 由于我们的SQLite数据库实际上并不存在users表，我们可以使用MetaData向所有尚未存在的表发出CREATE TABLE语句到数据库。下面，我们调用MetaData.create_all()方法，传入我们的Engine作为数据库连接的来源。我们将看到首先发出特殊命令来检查users表的存在，然后是实际的CREATE TABLE 声明：

>>> Base.metadata.create_all(engine)
SELECT ...
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上，这是一个有效的数据类型，但是在其他情况下，它是不允许的。因此，如果在其中一个数据库上运行本教程，并且希望使用SQLAlchemy发出CREATE TABLE，则可以为String类型提供“length”，如下所示：

Column(String(50))

String上的长度字段以及Integer，Numeric等可用的类似精度/缩放字段。除了创建表格时，不会被SQLAlchemy引用。

此外，Firebird和Oracle需要序列来生成新的主键标识符，并且SQLAlchemy不会在未经指示的情况下生成或采用这些标识符。为此，您可以使用Sequence结构：

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

因此，通过我们的声明性映射生成的完整，万无一失的Table是：

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

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

我们单独包含这个更详细的表定义，以突出显示主要针对Python内使用的最小构造与将用于在具有更严格要求的特定后端组上发出CREATE TABLE语句的构造之间的差异。

创建映射类的实例¶

完成映射后，我们现在创建并检查User对象：

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

尽管我们没有在构造函数中指定它，但当我们访问它时，id属性仍会产生一个None值（与Python通常提升AttributeError为未定义的属性）。SQLAlchemy的instrumentation通常在首次访问时为列映射属性生成此默认值。对于那些我们实际赋予值的属性，工具系统正在跟踪这些赋值，以便在最终的INSERT语句中使用，以便发送到数据库。

创建会话¶

我们现在准备开始与数据库会话了。ORM通过Session与数据库建立连接的。当我们首次启动应用程序时，与我们的create_engine()语句相同，我们定义一个Session类，作为新的Session 对象：

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

在您的应用程序在定义模块级对象时尚未具有Engine的情况下，只需按如下设置：

>>> Session = sessionmaker()

稍后，当您使用create_engine()创建引擎时，请使用configure()将其连接到Session

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

此定制的Session类将创建绑定到我们的数据库的新Session对象。调用sessionmaker时也可以定义其他事务特征；这些在后面的章节中描述。然后，每当需要与数据库进行对话时，只需要实例化一个Session：

>>> session = Session()

上述Session与我们启用SQLite的Engine相关联，但尚未打开任何连接。当它第一次使用时，它从由Engine维护的连接池中检索连接，并保持到它，直到我们提交所有更改和/或关闭会话对象。

添加和更新对象¶

要保留我们的User对象，我们add()到我们的Session

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

此时，我们说这个实例正在等待；尚未发出SQL，并且对象尚未由数据库中的行表示。只要需要，Session将发布SQL来坚持Ed tt4> 琼斯 冲洗 T6>。如果我们在数据库中查询Ed Jones，所有待处理的信息将首先被刷新，然后立即发出查询。

例如，下面我们创建一个新的Query对象，它加载User的实例。我们“过滤”ed的name属性，并指示我们只想获取完整列表中的第一个结果。返回一个与我们添加的相同的User实例：

sql>>>our_user=session.query(User).filter_by(name='ed').first()# doctest:+NORMALIZE_WHITESPACEBEGIN (implicit)
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 ? OFFSET ?
('ed', 1, 0)
>>>our_user<User(name='ed',fullname='Ed Jones',password='edspassword')>

事实上，Session已经识别出返回的行是与其内部对象映射中已经表示的相同的行，因此我们实际上得到了与我们刚才添加的相同的实例：

>>> ed_user is our_user
True

这里工作的ORM概念被称为身份映射并且确保在Session内的特定行上的所有操作对相同的数据集进行操作。一旦具有特定主键的对象出现在Session中，那么会话上的所有SQL查询将始终为该特定主键返回相同的Python对象；如果试图在会话中放置具有相同主键的第二个已经持久化的对象，它也将引发错误。

我们可以使用add_all()一次添加更多User对象：

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', password='foobar'),
...     User(name='mary', fullname='Mary Contrary', password='xxg527'),
...     User(name='fred', fullname='Fred Flinstone', password='blah')])

此外，我们认为Ed的密码不是太安全，所以让我们改变它：

>>> ed_user.password = 'f8s7ccs'

Session正在关注。例如，它知道Ed Jones已被修改：

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

并且三个新的User对象正在等待：

>>> session.new  # doctest: +SKIP
IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,
<User(name='mary', fullname='Mary Contrary', password='xxg527')>,
<User(name='fred', fullname='Fred Flinstone', password='blah')>])

我们告诉session，我们要发布对数据库的所有剩余更改，并提交已经在进行中的事务。我们通过commit()来实现。session发出用于“ed”上的密码更改的UPDATE语句，以及三个新的User语句INSERT我们添加的对象：

sql>>>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')
COMMIT

commit()刷新数据库中剩余的任何更改，并提交事务。session引用的连接资源现在返回到连接池。此会话的后续操作将发生在新事务中，这将在第一次需要时再次重新获取连接资源。

如果我们看一下Ed的id属性，它早先是None，它现在有一个值：

sql>>>ed_user.id# doctest: +NORMALIZE_WHITESPACEBEGIN (implicit)
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

在会话在数据库中插入新行后，所有新生成的标识符和数据库生成的默认值立即或通过首次加载访问在实例上可用。在这种情况下，整个行在访问时重新加载，因为在我们发出commit()之后开始一个新事务。SQLAlchemy默认情况下刷新第一次在新事务中访问之前的事务的数据，以便最近的状态可用。重新加载的级别是可以配置的，如使用session中所述。

会话对象状态

由于我们的用户对象从会话之外移动到没有主键的会话内部，为了实际插入，它在三暂停，等待和持久性中的四个可用的“对象状态”。意识到这些状态，它们的含义总是一个好主意 - 请务必阅读Quickie简介到对象状态以便快速了解。

回滚¶

由于Session在一个事务中工作，因此我们可以回滚更改。让我们做将被还原的两个更改； ed_user的用户名设置为Edwardo：

>>> ed_user.name = 'Edwardo'

我们添加另一个错误的用户fake_user：

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

查询会话，我们可以看到它们被刷入当前事务：

sql>>>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(name='Edwardo',fullname='Ed Jones',password='f8s7ccs')>,<User(name='fakeuser',fullname='Invalid',password='12345')>]

回滚，我们可以看到ed_user的名称已回到ed，而且fake_user已被踢出会话：

sql>>>session.rollback()ROLLBACK
sql>>>ed_user.nameBEGIN (implicit)
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_userinsessionFalse

发出SELECT说明对数据库所做的更改：

sql>>>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(name='ed',fullname='Ed Jones',password='f8s7ccs')>]

查询¶

使用Session上的query()方法创建Query对象。此函数采用可变数量的参数，参数可以是类或者类的描述的集合。下面，我们指示加载User实例的查询。下面是一个迭代输出User类的例子：

sql>>>forinstanceinsession.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
()
edEdJoneswendyWendyWilliamsmaryMaryContraryfredFredFlinstone

Query也接受ORM描述作为参数。任何时候，多个类实体或基于列的实体表达都可以作为query()函数的参数，返回类型为元组：

sql>>>forname,fullnameinsession.query(User.name,User.fullname):...print(name,fullname)SELECT users.name AS users_name,
        users.fullname AS users_fullname
FROM users
()
edEdJoneswendyWendyWilliamsmaryMaryContraryfredFredFlinstone

Query返回的元组是命名为tuples，由KeyedTuple类提供，可以像普通Python对象一样对待。名称与属性的属性名称以及类的类名相同：

sql>>>forrowinsession.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(name='ed',fullname='Ed Jones',password='f8s7ccs')>ed<User(name='wendy',fullname='Wendy Williams',password='foobar')>wendy<User(name='mary',fullname='Mary Contrary',password='xxg527')>mary<User(name='fred',fullname='Fred Flinstone',password='blah')>fred

您可以使用label()结构控制单个列表达式的名称，该结构可从任何ColumnElement - 来源对象，以及任何映射到实体表的列元素（例如User.name）：

sql>>>forrowinsession.query(User.name.label('name_label')).all():...print(row.name_label)SELECT users.name AS name_label
FROM users
()
edwendymaryfred

假设在对query()的调用中存在多个实体，可以使用aliased()来控制完整实体例如User：

>>>fromsqlalchemy.ormimportaliased>>>user_alias=aliased(User,name='user_alias')sql>>>forrowinsession.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
()
<User(name='ed',fullname='Ed Jones',password='f8s7ccs')><User(name='wendy',fullname='Wendy Williams',password='foobar')><User(name='mary',fullname='Mary Contrary',password='xxg527')><User(name='fred',fullname='Fred Flinstone',password='blah')>

使用Query的基本操作包括发出LIMIT和OFFSET，最方便地使用Python数组分片，通常与ORDER BY结合使用：

sql>>>foruinsession.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 ? OFFSET ?
(2, 1)
<User(name='wendy',fullname='Wendy Williams',password='foobar')><User(name='mary',fullname='Mary Contrary',password='xxg527')>

过滤结果，可通过使用filter_by()（使用关键字参数）完成：

sql>>>forname,insession.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运算符：

sql>>>forname,insession.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连接标准：

sql>>>foruserinsession.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(name='ed',fullname='Ed Jones',password='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']))

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

注意
确保使用and_()而不是 Python and运算符！

OR

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

注意
请确保使用or_()而不是 Python or运算符！

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

注意
match()使用数据库特定的MATCH或CONTAINS函数；其行为将随后端而变化，并且在某些后端（如SQLite）上不可用。

返回列表和标量¶

Query上的多个方法会立即发出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(name='ed',fullname='Ed Jones',password='f8s7ccs')>,<User(name='fred',fullname='Fred Flinstone',password='blah')>]

first()应用一个限制，并将第一个结果作为标量返回：

sql>>>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 ? OFFSET ?
('%ed', 1, 0)
<User(name='ed',fullname='Ed Jones',password='f8s7ccs')>

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()方法非常适用于希望处理“没有找到项目”和“找到多个项目”的系统；例如Web服务返回，当没有找到结果时，它可能想要引发“404未找到”，但是在找到多个结果时引发应用程序错误。
one_or_none()就像one()，除了如果没有找到结果，它不会引发错误；它只返回None。像one()，如果找到多个结果，它会引发错误。

scalar()调用one()方法，在one()成功的基础上返回该行的第一列：

>>>query=session.query(User.id).filter(User.name=='ed').\
...order_by(User.id)sql>>>query.scalar()SELECT users.id AS users_id
FROM users
WHERE users.name = ? ORDER BY users.id
('ed',)
1

使用文本SQL ¶

文本字符串可以通过Query灵活使用，通过text()构造指定字符串的使用，这种方法可以用在很多方法中。例如，filter()和order_by()：

>>>fromsqlalchemyimporttextsql>>>foruserinsession.query(User).\
...filter(text("id<224")).\
...order_by(text("id")).all():...print(user.name)SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE id<224 ORDER BY id
()
edwendymaryfred

绑定参数可以使用基于字符串的SQL指定，使用冒号。使用params()方法指定数值：

sql>>>session.query(User).filter(text("id<:value and name=:name")).\
...params(value=224,name='fred').order_by(User.id).one()SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE id<? and name=? ORDER BY users.id
(224, 'fred')
<User(name='fred',fullname='Fred Flinstone',password='blah')>

要使用一个完整的SQL语句，可以将表示完整语句的text()结构传递给from_statement()。如果没有其他说明符，字符串SQL中的列将根据名称与模型列匹配，如下所示，我们只使用星号来表示加载所有列：

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

名称匹配列适用于简单情况，但在处理包含重复列名的复杂语句时或使用不易与特定名称匹配的匿名ORM构造时，可能会变得不方便。另外，在处理结果行时，我们可能会发现映射列中存在打字行为。对于这些情况，text()结构允许我们将其文本SQL链接到Core或ORM映射的列表达式；我们可以通过将列表达式作为位置参数传递到TextClause.columns()方法来实现：

>>>stmt=text("SELECT name, id, fullname, password "..."FROM users where name=:name")>>>stmt=stmt.columns(User.name,User.id,User.fullname,User.password)sql>>>session.query(User).from_statement(stmt).params(name='ed').all()SELECT name, id, fullname, password FROM users where name=?
('ed',)
[<User(name='ed',fullname='Ed Jones',password='f8s7ccs')>]

版本1.1中的新功能： TextClause.columns()方法现在接受与明文SQL结果集位置匹配的列表达式，匹配或甚至在SQL语句中是唯一的。

When selecting from a text() construct, the Query may still specify what columns and entities are to be returned; instead of query(User) we can also ask for the columns individually, as in any other case:

>>>stmt=text("SELECT name, id FROM users where name=:name")>>>stmt=stmt.columns(User.name,User.id)sql>>>session.query(User.id,User.name).\
...from_statement(stmt).params(name='ed').all()SELECT name, id FROM users where name=?
('ed',)
[(1,u'ed')]

也可以看看

Using Textual SQL - 从纯核查询的角度解释text()结构。

计数¶ T0>

Query包含一个用于计数的简便方法，称为count()：

sql>>>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.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)
2

使用count()方法来确定SQL语句将返回多少行。查看上面生成的SQL，SQLAlchemy总是将我们正在查询的任何东西放到子查询中，然后对其进行计数。在某些情况下，这可以简化为SELECT count（*） FROM 表 t0>，但现代版本的SQLAlchemy不会尝试猜测这是否合适，因为可以使用更明确的方法发出确切的SQL。

For situations where the “thing to be counted” needs to be indicated specifically, we can specify the “count” function directly using the expression func.count(), available from the func construct. 下面我们用它来返回每个不同用户名的计数：

>>>fromsqlalchemyimportfuncsql>>>session.query(func.count(User.name),User.name).group_by(User.name).all()SELECT count(users.name) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
()
[(1,u'ed'),(1,u'fred'),(1,u'mary'),(1,u'wendy')]

要实现我们简单的SELECT count（*） FROM 表它作为：

sql>>>session.query(func.count('*')).select_from(User).scalar()SELECT count(?) AS count_1
FROM users
('*',)
4

如果我们直接使用User主键表示计数，则可以删除select_from()的用法：

sql>>>session.query(func.count(User.id)).scalar()SELECT count(users.id) AS count_1
FROM users
()
4

建立关系¶

我们考虑如何映射和查询与User相关的第二张表。系统中的用户可以存储与其用户名相关联的任意数量的电子邮件地址。这意味着用户到存储电子邮件地址的新表(addresses)的一对多的关联。使用声明式，我们定义了这个表及其映射类Address：

>>> 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的指令，表示该列中的值应该是受制于在User表中的值。这是关系数据库的一个核心功能，并且是一种“粘合剂”，它可以转换一系列未连接的表，以获得丰富的重叠关系。上面的ForeignKey表示addresses.user_id列中的值应该被限制为users.id列中的值，即其主键。

第二个指令称为relationship()，告诉ORM：Address类本身应该链接到User类，使用属性Address.userrelationship()使用两个表之间的外键关系来确定这个链接的性质，确定Address.user将many to one在属性User.addresses下的User映射类上放置了一个额外的relationship()指令。In both relationship() directives, the parameter relationship.back_populates is assigned to refer to the complementary attribute names; by doing so, each relationship() can make intelligent decision about the same relationship as expressed in reverse; on one side, Address.user refers to a User instance, and on the other side, User.addresses refers to a list of Address instances.

注意

relationship.back_populates参数是称为relationship.backref的非常常见的SQLAlchemy功能的更新版本。relationship.backref参数没有消失，并且始终保持可用！relationship.back_populates是同样的事情，除了稍微冗长一些并且更容易操作。有关整个主题的概述，请参阅Linking Relationships with Backref部分。

多对一关系的反面总是one to many。relationship()配置的完整目录位于Basic Relationship Patterns。

两个互补关系Address.user和User.addresses被称为bidirectional relationship，并且是SQLAlchemy ORM的一个关键特性。Linking Relationships with Backref部分详细讨论了“backref”功能。

假设Declarative系统正在使用，可以使用字符串指定涉及远程类的relationship()的参数。一旦所有映射完成后，这些字符串将被评估为Python表达式，以便产生实际的参数，在上面的例子中是User类。在评估过程中允许的名称除其他外包括所有已根据声明的基础创建的类的名称。

有关参数样式的更多详细信息，请参阅relationship()的文档字符串。

你知道吗？

大多数（尽管不是全部）关系数据库中的FOREIGN KEY约束只能链接到主键列或具有UNIQUE约束的列。
引用多列主键并且自身具有多列的FOREIGN KEY约束被称为“组合外键”。它也可以引用这些列的一个子集。
FOREIGN KEY列可以自动更新自己，以响应引用的列或行的更改。这被称为CASCADE 引用操作，并且是关系数据库的内置函数。
FOREIGN KEY可以引用它自己的表。这被称为“自我参照”外键。
在外键 - 维基百科中阅读有关外键的更多信息。

我们需要在数据库中创建addresses表，因此我们将从我们的元数据中发出另一个CREATE，这将跳过已经创建的表：

sql>>>Base.metadata.create_all(engine)PRAGMA...
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

用连接查询¶

既然我们有两个表，我们可以展示Query的更多特性，特别是如何创建同时处理两个表的查询。SQL JOIN上的维基百科页面提供了一个很好的介绍连接技术的方法，其中几个我们将在这里进行说明。

To construct a simple implicit join between User and Address, we can use Query.filter() to equate their related columns together. 下面我们使用这个方法一次加载User和Address实体：

sql>>>foru,ainsession.query(User,Address).\
...filter(User.id==Address.user_id).\
...filter(Address.email_address=='jack@google.com').\
...all():...print(u)...print(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(name='jack',fullname='Jack Bean',password='gjffdd')><Address(email_address='jack@google.com')>

另一方面，实际的SQL JOIN语法最容易使用Query.join()方法实现：

sql>>>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(name='jack',fullname='Jack Bean',password='gjffdd')>]

Query.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

正如你所期望的那样，使用outerjoin()函数对“外部”连接使用相同的想法：

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

join()的参考文档包含此方法接受的调用样式的详细信息和示例； join()是任何SQL流利应用程序使用中心的重要方法。

如果有多个实体，Query选择什么？

The Query.join() method will typically join from the leftmost item in the list of entities, when the ON clause is omitted, or if the ON clause is a plain SQL expression. 要控制JOIN列表中的第一个实体，请使用Query.select_from()方法：

query = Session.query(User, Address).select_from(Address).join(User)

使用别名¶

When querying across multiple tables, if the same table needs to be referenced more than once, SQL typically requires that the table be aliased with another name, so that it can be distinguished against other occurrences of that table. Query使用aliased结构最明确地支持这一点。下面我们加入Address实体两次，以找到同时拥有两个不同电子邮件地址的用户：

>>>fromsqlalchemy.ormimportaliased>>>adalias1=aliased(Address)>>>adalias2=aliased(Address)sql>>>forusername,email1,email2in \
...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)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')
jackjack@google.comj25@yahoo.com

使用子查询¶

Query适用于生成可用作子查询的语句。假设我们想要加载User对象以及每个用户拥有多少个Address记录的计数。像这样生成SQL的最佳方法是获取按用户标识分组的地址数，并将JOIN添加到父级。在这种情况下，我们使用LEFT OUTER JOIN，以便我们返回那些没有任何地址的用户的行，例如：

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，我们从内到外构建一个类似于此的语句。statement访问器返回一个表达由特定Query生成的语句的SQL表达式 - 这是一个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表达式结构，表示嵌入别名中的SELECT语句它实际上是query.statement.alias()的缩写）。

一旦我们有了我们的声明，它就像一个Table结构，比如我们在本教程开始时为users创建的结构。语句中的列可以通过名为c的属性进行访问：

sql>>>foru,countinsession.query(User,stmt.c.address_count).\
...outerjoin(stmt,User.id==stmt.c.user_id).order_by(User.id):...print(u,count)SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password,
        anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN
    (SELECT addresses.user_id AS user_id, count(?) AS address_count
    FROM addresses GROUP BY addresses.user_id) AS anon_1
    ON users.id = anon_1.user_id
ORDER BY users.id
('*',)
<User(name='ed',fullname='Ed Jones',password='f8s7ccs')>None<User(name='wendy',fullname='Wendy Williams',password='foobar')>None<User(name='mary',fullname='Mary Contrary',password='xxg527')>None<User(name='fred',fullname='Fred Flinstone',password='blah')>None<User(name='jack',fullname='Jack Bean',password='gjffdd')>2

从子查询中选择实体¶

上面，我们刚刚选择了一个包含子查询列的结果。如果我们希望我们的子查询映射到实体呢？为此，我们使用aliased()将映射类的“别名”关联到子查询：

sql>>>stmt=session.query(Address).\
...filter(Address.email_address!='j25@yahoo.com').\
...subquery()>>>adalias=aliased(Address,stmt)>>>foruser,addressinsession.query(User,adalias).\
...join(adalias,User.addresses):...print(user)...print(address)SELECT users.id AS users_id,
            users.name AS users_name,
            users.fullname AS users_fullname,
            users.password AS users_password,
            anon_1.id AS anon_1_id,
            anon_1.email_address AS anon_1_email_address,
            anon_1.user_id AS anon_1_user_id
FROM users JOIN
    (SELECT addresses.id AS id,
            addresses.email_address AS email_address,
            addresses.user_id AS user_id
    FROM addresses
    WHERE addresses.email_address != ?) AS anon_1
    ON users.id = anon_1.user_id
('j25@yahoo.com',)
<User(name='jack',fullname='Jack Bean',password='gjffdd')><Address(email_address='jack@google.com')>

使用EXISTS ¶

SQL中的EXISTS关键字是一个布尔运算符，如果给定表达式包含任何行，则返回True。它可以在许多场景中用于代替连接，并且对于查找在相关表中没有相应行的行也很有用。

有一个明确的EXISTS构造，看起来像这样：

>>>fromsqlalchemy.sqlimportexists>>>stmt=exists().where(Address.user_id==User.id)sql>>>forname,insession.query(User.name).filter(stmt):...print(name)SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
()
jack

Query具有几个自动使用EXISTS的操作符。以上，语句可以使用any()沿着User.addresses关系表示：

sql>>>forname,insession.query(User.name).\
...filter(User.addresses.any()):...print(name)SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
()
jack

any() takes criterion as well, to limit the rows matched:

sql>>>forname,insession.query(User.name).\
...filter(User.addresses.any(Address.email_address.like('%google%'))):...print(name)SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id AND addresses.email_address LIKE ?)
('%google%',)
jack

has() is the same operator as any() for many-to-one relationships (note the ~ operator here too, which means “NOT”):

sql>>>session.query(Address).\
...filter(~Address.user.has(User.name=='jack')).all()SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE NOT (EXISTS (SELECT 1
FROM users
WHERE users.id = addresses.user_id AND users.name = ?))
('jack',)
[]

常见关系运算符¶

以下是所有基于关系的运营商 - 每个运营商都链接到其API文档，其中包含使用和行为的全部详细信息：

__eq__()（多对一“等于”比较）：
```
query.filter(Address.user == someuser)
```
__ne__()（多对一“不等于”比较）：
```
query.filter(Address.user != someuser)
```
IS NULL（多对一比较，也使用__eq__()）：
```
query.filter(Address.user == None)
```

contains()（用于一对多集合）：

query.filter(User.addresses.contains(someaddress))

any()（用于集合）：

query.filter(User.addresses.any(Address.email_address == 'bar'))

# also takes keyword arguments:
query.filter(User.addresses.any(email_address='bar'))

has()（用于标量引用）：

query.filter(Address.user.has(name='ed'))

Query.with_parent() (used for any relationship):

session.query(Address).with_parent(someuser, 'addresses')

急于加载¶

回想一下，当我们访问User的User.addresses集合并发射了SQL时，我们演示了一个lazy loading操作。如果你想减少查询的数量（在很多情况下是显着的），我们可以在查询操作中应用一个预先加载。SQLAlchemy提供了三种热切加载类型，其中两种是自动加载的，另一种是自定义标准。通常通过称为查询选项的函数调用所有这三个函数，这些函数为Query提供了有关如何通过Query.options()方法。

子查询Load ¶

在这种情况下，我们希望指出User.addresses应该急切加载。加载一组对象及其相关集合的好选择是orm.subqueryload()选项，该选项发出第二个SELECT语句，该语句完全加载与刚加载的结果相关的集合。名称“subquery”源自直接通过Query构造的SELECT语句被重用，并作为子查询嵌入到SELECT中以对照相关表。这有点复杂但很容易使用：

>>>fromsqlalchemy.ormimportsubqueryloadsql>>>jack=session.query(User).\
...options(subqueryload(User.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
FROM users
WHERE users.name = ?
('jack',)
SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id,
        anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
    FROM users WHERE users.name = ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id
('jack',)
>>>jack<User(name='jack',fullname='Jack Bean',password='gjffdd')>>>>jack.addresses[<Address(email_address='jack@google.com')>,<Address(email_address='j25@yahoo.com')>]

注意

subqueryload() when used in conjunction with limiting such as Query.first(), Query.limit() or Query.offset() should also include Query.order_by() on a unique column in order to ensure correct results. 请参阅The Importance of Ordering。

加入Load ¶

另一个自动加载加载函数更为人熟知，称为orm.joinedload()。这种加载方式会发出一个JOIN，默认情况下是一个LEFT OUTER JOIN，这样就可以在一个步骤中加载前导对象以及相关的对象或集合。我们举例说明以这种方式加载相同的addresses集合 - 请注意，即使jack上的User.addresses集合现在实际上已被填充，无论如何查询都会发出额外的连接：

>>>fromsqlalchemy.ormimportjoinedloadsql>>>jack=session.query(User).\
...options(joinedload(User.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(name='jack',fullname='Jack Bean',password='gjffdd')>>>>jack.addresses[<Address(email_address='jack@google.com')>,<Address(email_address='j25@yahoo.com')>]

请注意，即使OUTER JOIN导致了两行，我们仍然只返回一个User的实例。这是因为Query将基于对象标识的“uniquing”策略应用于返回的实体。这特别适用于可以应用加入的加载加载而不影响查询结果。

虽然joinedload()已经存在了很长时间，但是subqueryload()是一种更新的渴望加载形式。subqueryload()倾向于更加适合加载相关集合，而joinedload()倾向于更适合于多对一关系，因为只有一个对于领导和相关对象都加载行。

joinedload()不能代替join()

由joinedload()创建的连接是匿名化的，因此它不会影响查询结果。Query.order_by()或Query.filter()调用不能引用这些别名表 - 构建所谓的“用户空间”联接使用Query.join()。其基本原理是，joinedload()仅适用于影响相关对象或集合作为优化细节加载的方式 - 可以添加或删除它，而不会影响实际结果。有关如何使用这些内容的详细说明，请参阅The Zen of Eager Loading部分。

显式加入+ Eagerload ¶

第三种急切加载方式是当我们为了定位主要行而显式构建一个JOIN时，并且想额外将额外表应用到主要对象上的相关对象或集合。此功能通过orm.contains_eager()函数提供，通常用于在需要过滤同一对象的查询上预加载多对一对象。下面我们将说明如何加载Address行以及相关的User对象，在名为“jack”的User上进行过滤并使用orm.contains_eager()将“user”列应用于Address.user属性：

>>>fromsqlalchemy.ormimportcontains_eagersql>>>jacks_addresses=session.query(Address).\
...join(Address.user).\
...filter(User.name=='jack').\
...options(contains_eager(Address.user)).\
...all()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',)

>>>jacks_addresses[<Address(email_address='jack@google.com')>,<Address(email_address='j25@yahoo.com')>]>>>jacks_addresses[0].user<User(name='jack',fullname='Jack Bean',password='gjffdd')>

有关预加载的更多信息，包括如何默认配置各种加载形式，请参阅Relationship Loading Techniques部分。

删除¶ T0>

让我们试着删除jack，看看结果如何。我们将在会话中标记为已删除，然后我们将发出一个count查询以查看没有行保留：

>>>session.delete(jack)sql>>>session.query(User).filter_by(name='jack').count()UPDATE addresses SET user_id=? WHERE addresses.id = ?
((None, 1), (None, 2))
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (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 = ?) AS anon_1
('jack',)
0

到现在为止还挺好。杰克的Address对象怎么样？

sql>>>session.query(Address).filter(...Address.email_address.in_(['jack@google.com','j25@yahoo.com'])...).count()SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
2

呃哦，他们还在！分析刷新SQL，我们可以看到每个地址的user_id列设置为NULL，但行未被删除。SQLAlchemy不会假设删除级联，你必须告诉它这样做。

配置删除/删除 - 孤立级联¶

我们将在User.addresses关系上配置级联选项来更改行为。尽管SQLAlchemy允许您在任何时间点添加新的属性和关系到映射，但在这种情况下，需要删除现有的关系，因此我们需要完全拆除映射并重新开始 - 我们将关闭Session

>>> session.close()
ROLLBACK

并使用新的declarative_base()：

>>> Base = declarative_base()

接下来，我们将声明User类，并在包含级联配置的addresses关系中添加（我们将离开构造函数）：

>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     addresses = relationship("Address", back_populates='user',
...                     cascade="all, delete, delete-orphan")
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % (
...                                self.name, self.fullname, self.password)

然后我们重新创建Address，注意在这种情况下，我们已经通过User类创建了Address.user关系：

>>> 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

现在当我们加载用户jack（下面用get()，通过主键加载），从相应的addresses集合中删除一个地址将导致Address被删除：

# load Jack by primary keysql>>>jack=session.query(User).get(5)BEGIN (implicit)
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 = ?
(5,)
# remove one Address (lazy load fires off)sql>>>deljack.addresses[1]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
(5,)
# only one address remainssql>>>session.query(Address).filter(...Address.email_address.in_(['jack@google.com','j25@yahoo.com'])...).count()DELETE FROM addresses WHERE addresses.id = ?
(2,)
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
1

删除Jack将删除Jack和与用户关联的其余Address：

>>>session.delete(jack)sql>>>session.query(User).filter_by(name='jack').count()DELETE FROM addresses WHERE addresses.id = ?
(1,)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (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 = ?) AS anon_1
('jack',)
0sql>>>session.query(Address).filter(...Address.email_address.in_(['jack@google.com','j25@yahoo.com'])...).count()SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')
0

更多关于瀑布

有关级联配置的更多详细信息，请参见Cascades。级联功能还可以与关系数据库的ON DELETE CASCADE功能顺利集成。有关详细信息，请参见Using Passive Deletes。

建立多对多的关系¶

我们正在进入奖金轮，但让我们展示一个多对多的关系。我们也会潜入一些其他功能，只是为了参观。我们将使我们的应用程序成为博客应用程序，用户可以在其中编写BlogPost项目，其中包含与Keyword项目相关联的项目。

对于普通的多对多，我们需要创建一个未映射的Table结构来充当关联表。这看起来像下面这样：

>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table('post_keywords', Base.metadata,
...     Column('post_id', ForeignKey('posts.id'), primary_key=True),
...     Column('keyword_id', ForeignKey('keywords.id'), primary_key=True)
... )

上面，我们可以看到直接声明一个Table与声明一个映射类有点不同。Table是一个构造函数，所以每个Column参数都以逗号分隔。Column对象也显式给出其名称，而不是从已分配的属性名称中获取。

接下来，我们使用互补的relationship()结构定义BlogPost和Keyword，每个结构都引用post_keywords表作为关联表：

>>> class BlogPost(Base):
...     __tablename__ = 'posts'
...
...     id = Column(Integer, primary_key=True)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     headline = Column(String(255), nullable=False)
...     body = Column(Text)
...
...     # many to many BlogPost<->Keyword
...     keywords = relationship('Keyword',
...                             secondary=post_keywords,
...                             back_populates='posts')
...
...     def __init__(self, headline, body, author):
...         self.author = author
...         self.headline = headline
...         self.body = body
...
...     def __repr__(self):
...         return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)


>>> class Keyword(Base):
...     __tablename__ = 'keywords'
...
...     id = Column(Integer, primary_key=True)
...     keyword = Column(String(50), nullable=False, unique=True)
...     posts = relationship('BlogPost',
...                          secondary=post_keywords,
...                          back_populates='keywords')
...
...     def __init__(self, keyword):
...         self.keyword = keyword

注意

上面的类声明说明了显式的__init__()方法。请记住，使用声明时，它是可选的！

Above, the many-to-many relationship is BlogPost.keywords. 多对多关系的定义特征是引用代表关联表的Table对象的secondary关键字参数。This table only contains columns which reference the two sides of the relationship; if it has any other columns, such as its own primary key, or foreign keys to other tables, SQLAlchemy requires a different usage pattern called the “association object”, described at Association Object.

我们还希望我们的BlogPost类具有author字段。我们会将此添加为另一种双向关系，除了我们将要面对的一个问题是单个用户可能拥有大量博客帖子。当我们访问User.posts时，我们希望能够进一步过滤结果，以免加载整个集合。为此，我们使用一个名为lazy='dynamic'的relationship()接受的设置，该设置在属性上配置了一个备用加载器策略

>>> BlogPost.author = relationship(User, back_populates="posts")
>>> User.posts = relationship(BlogPost, back_populates="author", lazy="dynamic")

创建新表格：

sql>>>Base.metadata.create_all(engine)PRAGMA...
CREATE TABLE keywords (
    id INTEGER NOT NULL,
    keyword VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (keyword)
)
()
COMMIT
CREATE TABLE posts (
    id INTEGER NOT NULL,
    user_id INTEGER,
    headline VARCHAR(255) NOT NULL,
    body TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT
CREATE TABLE post_keywords (
    post_id INTEGER NOT NULL,
    keyword_id INTEGER NOT NULL,
    PRIMARY KEY (post_id, keyword_id),
    FOREIGN KEY(post_id) REFERENCES posts (id),
    FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)
()
COMMIT

用法与我们所做的并无太大差别。让我们给温蒂一些博客文章：

sql>>>wendy=session.query(User).\
...filter_by(name='wendy').\
...one()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 = ?
('wendy',)
>>>post=BlogPost("Wendy's Blog Post","This is a test",wendy)>>>session.add(post)

我们将关键字唯一地存储在数据库中，但我们知道我们还没有，所以我们可以创建它们：

>>> post.keywords.append(Keyword('wendy'))
>>> post.keywords.append(Keyword('firstpost'))

现在我们可以使用关键字'firstpost'查找所有博客文章。我们将使用any运算符来定位“任何关键字具有关键字字符串”firstpost“的博客帖子：

sql>>>session.query(BlogPost).\
...filter(BlogPost.keywords.any(keyword='firstpost')).\
...all()INSERT INTO keywords (keyword) VALUES (?)
('wendy',)
INSERT INTO keywords (keyword) VALUES (?)
('firstpost',)
INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
(2, "Wendy's Blog Post", 'This is a test')
INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
(...)
SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?)
('firstpost',)
[BlogPost("Wendy's Blog Post",'This is a test',<User(name='wendy',fullname='Wendy Williams',password='foobar')>)]

如果我们想查找由用户wendy拥有的帖子，我们可以告诉查询将其缩小为User对象作为父级：

sql>>>session.query(BlogPost).\
...filter(BlogPost.author==wendy).\
...filter(BlogPost.keywords.any(keyword='firstpost')).\
...all()SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?))
(2, 'firstpost')
[BlogPost("Wendy's Blog Post",'This is a test',<User(name='wendy',fullname='Wendy Williams',password='foobar')>)]

或者我们可以使用Wendy自己的posts关系，这是一个“动态”关系，从这里直接查询：

sql>>>wendy.posts.\
...filter(BlogPost.keywords.any(keyword='firstpost')).\
...all()SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?))
(2, 'firstpost')
[BlogPost("Wendy's Blog Post",'This is a test',<User(name='wendy',fullname='Wendy Williams',password='foobar')>)]

进一步参考¶

查询参考：query_api_toplevel

映射器参考：Mapper Configuration

关系参考：Relationship Configuration

会话参考：Using the Session

SQLAlchemy 1.1文档

SQLAlchemy 1.1文档

SQLAlchemy ORM

项目版本

对象关系教程¶

版本检查¶

连接¶ T0>

声明映射¶

构造模式¶

创建映射类的实例¶

创建会话¶

添加和更新对象¶

回滚¶

查询¶

常用过滤器操作符¶

返回列表和标量¶

使用文本SQL ¶

计数¶ T0>

建立关系¶

用连接查询¶

使用别名¶

使用子查询¶

从子查询中选择实体¶

使用EXISTS ¶

常见关系运算符¶

急于加载¶

子查询Load ¶

加入Load ¶

显式加入+ Eagerload ¶

删除¶ T0>

配置删除/删除 - 孤立级联¶

建立多对多的关系¶

进一步参考¶

SQLAlchemy 1.1文档

SQLAlchemy 1.1文档

SQLAlchemy ORM

项目版本

对象关系教程¶

版本检查¶

连接¶ T0>

声明映射¶

构造模式¶

创建映射类的实例¶

创建会话¶

添加和更新对象¶

回滚¶

查询¶

常用过滤器操作符¶

返回列表和标量¶

使用文本SQL ¶

计数¶ T0>

建立关系¶

使用相关对象¶

用连接查询¶

使用别名¶

使用子查询¶

从子查询中选择实体¶

使用EXISTS ¶

常见关系运算符¶

急于加载¶

子查询Load ¶

加入Load ¶

显式加入+ Eagerload ¶

删除¶ T0>

配置删除/删除 - 孤立级联¶

建立多对多的关系¶

进一步参考¶