英文版原文地址：docs.sqlalchemy.org/en/latest/orm/tutorial.html
一、基础学习
1. 查看当前sqlalchemy版本
sqlalchemy.__version__2. 连接数据库
from sqlalchemyimport create_engine
engine =create_engine(‘sqlite:///:memory:’, echo=True)功能: 建立与数据库的连接
参数: echo参数为True时，会显示每条执行的SQL语句，可以关闭。
返回值: create_engine()返回一个Engine的实例，并且它表示通过数据库语法处理细节的核心接口，在这种情况下，数据库语法将会被解释称Python的类方法。
3. 建立映射关系
使用ORM, 首先要配置数据库表，然后定义实体类并建立类与表的映射关系。在现版本的sqlalchemy，这两个任务通常一起执行，通过使用Declarative方法，我们可以创建一些包含描述要被映射的实际数据库表的准则的映射类。
sqlalchemy中Declarative baseclas是维系类和数据表关系的基类，使用Declarative方法定义的映射类继承自这个基类。在一个普通的模块入口中，应用通常只需要有一个base的实例。创建过程:
fromsqlalchemy.ext.declarativeimportdeclarative_base
Base =declarative_base()可以依据这个base定义任意数量的映射类。一个简单的user例子：
from sqlalchemyimport Column, Integer, String
classUser(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__属性，一个主键行。
4. 构造模式？
通过Declarative系统，构建了User类，并定义了表的信息(表元数据)。实际上，Declarative生成了一个sqlalchamy用来表示特定表结构的表对象，可以用过__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元类，以便声明完成时会执行一些附加活动；在此阶段，它根据类中给出的规范创建表对象，并通过ORM模型将其与类关联。通常不需要直接处理这个幕后对象，但是需要时可以提供大量的映射信息。
表对象是一个称为元数据的更大集合的成员。使用声明性时，可以使用声明性基类的.元数据属性来使用此对象。
元数据是一个注册表，它能够向数据库发送一组有限模式的生成命令？。由于sqlite数据库本身没有用户需要定义表，可以使用元数据向数据库发送CREATE TABLE语句去创建这些不存在的表。下面把Engine作为连接数据库的接口来调用MetaData.create_all()方法。会发现，首先发出特殊命令来检查用户表是否存在，然后遵循CREATE TABLE语句去创建表:
>>>BASE.metadata.create_all(engine)
SELECT …
PRAGMAtable_info(“users”)
()
CREATE TABLEusers (
id INTEGER NOT NULL, name VARCHAR,
fullname VARCHAR,
password VARCHAR,
PRIMARY KEY (id)
)
()
COMMIT５.最小描述 vs. 全表描述
如果熟悉CREATE TABLE语句语法的话，会发现VARCHAR列没有长度；在SQLite和PostgreSQL数据库，这是有效的数据类型，但在其他数据库，这是不允许的。因此，如果要在其他数据库上运行语句并且希望sqlalchemy去CREATE TABLE，可以向字符串类型提供一个长度:
from sqlalchemy import Sequence
Column(Integer, Sequence(‘user_id_seq’), primary_key=True)此处具体含义？
通过声明性映射系统去生成完整无误的表:
classUser(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)６. 创建映射类的实例
完成映射后创建并检查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属性仍然产生一个值（与Python对于未定义属性的属性错误的常见行为相反）。当第一次访问时，SqalAlcMy的工具通常会为列映射属性生成默认值。对于那些实际分配值的属性，检测系统跟踪那些在最终插入语句中使用的任务，这些语句将被发送到数据库。
７. 创建会话
ORM模型的句柄是数据库的一个绘画。当第一次设置应用程序时，在create_engine()语句的同时，就定义了一个绘画类，它将作为新会话对象的工厂。
>>>from sqlalchemy.orm import sessionmaker
>>>Session = sessionmaker(bind=engine)用程序在定义模块级对象时还没有引擎的话，只需将其设置为：
>>>Session.configure(bind=engine) # 这个定制的会话类将创建绑定到数据库的新会话对象。其他事务特性也可以在调用sessionmaker时定义，这些将在后面的章节中描述。然后，每当需要与数据库进行对话时，实例化会话：
>>> session=Session()虽然上面的Session已经和数据库引擎Engine关联，但是还没有打开任何连接。当它第一次被使用时，就会从Engine维护的一个连接池中检索是否存在连接，如果存在便会保持连接知道我们提交所有更改并且/或者关闭session对象。
８. 添加和更新对象
为了保存User对象，可以将它添加到会话中:
>>>ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>>session.add(ed_user)至此，我们可以认为，新添加的这个对象实例仍在等待中；ed_user对象现在并不代表数据库中的一行数据。直到使用flush进程，Session才会让SQL保持连接。如果查询这条数据的话，所有等待信息会被第一时间刷新，查询结果也会立即发行。
session.commit()通过commit()可以提交所有剩余的更改到数据库。
下面举例说明: 首先创建一个Query查询对象，它装在了User的实例，然后通过name属性进行过滤得到name=ed的实例，并且只取结果集的第一条记录。数据库中符合条件的一个用户实例将被返回：
>>>our_user = session.query(User).filter_by(name=’ed’).first()
>>>our_user
<User (name='ed',fullname='Ed Jones', password='edspassword')>事实上，会话已经确定返回的行与已经在对象内部映射中表示的行相同，因此我们实际上返回了与刚才添加的相同的实例：
>>>ed_user is our_user
True这里使用的ORM概念被称为身份映射，并要确保会话内所有针对特定行的操作都在同一组数据集上完成。在会话中存在特定主键的对象之后，该会话上的所有SQL查询将总是返回与该特定主键相同的Python对象；如果尝试将具有相同逐渐的两个数据放置在同一个Python对象时，会引发错误。
可以通过add_all()方法一次性向User对象添加多条数据
>>>session.add_all([
... User(name='wendy', fullname='WendyWilliams', password='foobar'),
... User(name='mary', fullname='MaryContrary', password='xxg527'),
... User(name='fred', fullname='FredFlinstone', password='blah')])此外，可能觉得Ed的密码不安全，可以改变它:
>>>ed_user.password = 'f8s7ccs'同时，session处于被监控状态，它知道Ed Jones已经被修改:
>>>session.dirty
IdentitySet([<User(name='ed',fullname='Ed Jones', password='f8s7ccs')>])三个新的用户对象悬而未决：
>>>session.new
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')>])当确定要向数据库提交这些修改事务时，需要调用commit()方法。session将发出UPDATE语句在修改密码的同时，也会发出INSERT语句添加三个新的用户对象：
>>>session.commit()commit()将更新的数据刷新到数据库并提交事务后，session引用的连接资源将返回到连接池。此会话的后续操作将发生在新事务中，该新事务将在需要时重新获取连接资源。
如果我们看一下Ed的ID属性，它先前没有，它现在就有了一个值：
>>>ed_user.id
1９. 事务回滚
由于会话在事务中工作，所以我们也可以回滚所做的更改。
>>>ed_user.name = 'Edwardo'
>>>fake_user = User(name='fakeuser', fullname='Invalid', password='12345')
>>>session.add(fake_user)查询会话，我们可以看到它们被冲入当前事务：
>>> session.query( User).filter(User.name.in_(['Edwardo','fakeuser'])).all()
[<User(name='Edwardo',fullname='Ed Jones',password='f8s7ccs')>,<User(name='fakeuser',fullname='Invalid',password='12345')>]回滚，可以看到Edl用户的名字回到ED，并且FAKEYUSER已经被踢出了会话：
>>>session.rollback()
>>>ed_user.name
u'ed'
>>>fake_user in session
False二、查询
1. 基本查询
在session中，query()方法会创建一个Query对象(查询结果集对象)。此函数接收可变数量的参数，这些参数可以是类或符合规则的表达式。下面以加载了User实例的Query对象为例，当在上下文迭代时，返回的是一个包含用户对象的列表:
>>> forinstance in session.query(User).order_by(User.id):
... print(instance.name, instance.fullname)
ed Ed Jones
wendy WendyWilliams
mary MaryContrary
fred FredFlinstoneQuery对象还接收ORM模型的描述符作为参数。当多类实体或基于列的实体被作为Query)函数的参数时，返回结果为一个相关值的元组:
>>> forname, fullname in session.query(User.name,User.fullname):
... print(name, fullname)
ed Ed Jones
wendy WendyWilliams
mary MaryContrary
fred FredFlinstone查询结果集返回值是支持KeyedTupleclass的命名元组，可以被作为像普通的Python对象看待。名称与属性的属性名称相同，类的类名为：
>>> forrow in session.query(User, User.name).all():
... print(row.User, row.name)
<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对象中，也可以映射到任何一个类属性上:
>>> forrow in session.query(User.name.label('name_label')).all():
... print(row.name_label)
ed
wendy
mary
fred假设query()查询结果集中存在多个实体类，则可以通过aliased()方法对整个实体类命名来控制整个实体，以User为例:
>>>from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')
>>> forrow in session.query(user_alias, user_alias.name).all():
... print(row.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，也会把ORDERBY和Python数组切片联合使用:
>>> foru in session.query(User).order_by(User.id)[1:3]:
... print(u)
<User(name='wendy',fullname='Wendy Williams', password='foobar')>
<User(name='mary',fullname='Mary Contrary', password='xxg527')>filter_by()使用关键字参数可以对查询结果进行过滤:
>>> forname, in session.query(User.name).\
... filter_by(fullname='Ed Jones'):
... print(name)
ed或者用filter()更灵活的使用SQL语句构造过滤条件，允许在映射类上使用具有类级属性的常规Python运算符：
>>> forname, in session.query(User.name).\
... filter(User.fullname=='Ed Jones'):
... print(name)
ed查询对象调用大多数新的方法返回值还是查询对象，可以进行进一步的操作。例如，为了用“Ed Jones”的全名查询名为“ED”的用户，可以调用FILTER（）两次：
>>> foruser in session.query(User).\
... filter(User.name=='ed').\
... filter(User.fullname=='Ed Jones'):
... print(user)
<User(name='ed',fullname='Ed Jones', password='f8s7ccs')>1.1 Filter常用的操作符
query.filter(User.name== ’ed’)query.filter(User.name!= ’ed’)like:
query.filter(User.name.like(%ed%’)
说明:ColumnOperators.like()提供LIKE运算符，在有些后端不区分大小写有些则区分。为了保证不区分大小写比较，可以使用ColumnOperators.like()
ilike(case-insensitive like):
query.filter(User.name.ilike(‘%ed%’)说明:大多数后端不直接支持ILIKE。对于这种情况，ColumnOperators.ilike()运算符呈现LOWER SQL 和LIKE函数相结合的结果。？
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)
# 如果关心PEP8/LTENS，或者写成下面的形式
query.filter(User.name.is_(None))query.filter(User.name != None)
# 如果关心PEP8/LTENS，或者写成下面的形式
query.filter(User.name.isnot(None))AND:
# 使用and_()
from sqlalchemy import and_
query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))
# 或者将组合表达式交给 .filter()方法
query.filter(User.name == ‘ed’, User.fullname == ‘Ed Jones’)
# 或者组合调用 filter()/filter_by()方法
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操作符！query.filter(User.name.match(‘wendy’))
注意: match()方法使用了数据库具有的MATCH或CONTAINS函数，它的行为会随后端变化，在一些后端如SQLite不可用。1.2 返回列表和标量
Query的许多方法是直接发送SQL语句并返回加载了数据库查询结果的值。以下是一些简单介绍：
all() 返回一个列表:
>>> query = session.query(User).filter(User.name.like(‘%ed’)).order_by(User.id)
>>> query.all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>,
<User(name='fred', fullname='Fred Flinstone', password='blah')>]first() 应用了一个限制一条记录，并将第一个结果作为标量返回：
>>> query.first()
<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()方法是很有用的，例如REST网络服务，它可能希望在没有找到结果的情况下触发“404 not found”异常，但是当发现多个结果时会引发应用程序错误。
one_or_none()和one()类似
当没有查询结果时one_or_none()只返回None，而不是触发错误。如果匹配到多行，它会触发错误。
scalar() 调用one()方法，在成功的基础上返回查询结果的第一列:
>>>query = session.query(User.id).filter(User.name == 'ed').\
... order_by(User.id)
>>>query.scalar()
11.3 使用文本字符串SQL语句
>>> 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
基于文本字符串的SQL语句可以使用冒号来绑定参数，如果要指定值，使用params()方法：>>>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 Flinstone', password='blah')>如果要使用一个完整的SQL语句，可以把text()构造的文本字符串SQL语句作为参数传入from_statement()方法。在没有其他附加说明的情况下，文本字符串SQL语句会按列名称匹配模型，如下面，只是用“*”就加载了所有列：
>>>session.query(User).from_statement(
… text(“SELECT * FROM users wherename=:name”)).\
… params(name=’ed’).all()
[<User(name='ed',fullname='Ed Jones', password='f8s7ccs')>]基于名称的匹配只适用于简单的情况，对于处理包含重复列名的复杂语句时或使用了匿名ORM结构而不容易匹配到特定名称时会显得比较笨拙。此外，在处理结果记录时可能会发现需要按照映射的列进行归类。对于这些情况，text()结构允许将它的文本化SQL语句连接到核心或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)
>>>session.query(User).from_statement(stmt).params(name=’ed’).all()
[<User(name='ed',fullname='Ed Jones', password='f8s7ccs')>]1.1版本中用法有所更新：TextClause.columns()方法接收字段名表达式，并按照位置与纯文本SQL查询结果集对应，从而消除了在SQL语句中需要按照字段名匹配甚至是字段名唯一匹配。
对于text()方法结构，Query仍然可以指定要返回的实例或者字段；除了query(User)我们也可以单独的请求字段，就像其他的情况一样：
>>>stmt = text("SELECT name, id FROM users where name=:name")
>>>stmt = stmt.columns(User.name, User.id)
>>>session.query(User.id, User.name).\
... from_statement(stmt).params(name='ed').all()
[(1, u'ed')]1.4 计数
count()用来统计查询结果的数量。
>>>session.query(User).filter(User.name.like('%ed')).count()
2
count()方法用于确定SQL语句查询到的记录条数。查看上例中的SQL会发现，SQLAlchemy总是将查询的内容放入子查询中然后对其进行计数。在某些情况下，可以从表中简化SELECT count(*) From table。
对于需要具体指出“要计数的事物”的情况，可以使用func.count()方法来指定“计数”函数。下面我们使用它来返回每个不同用户的计数：
>>>fromsqlalchemy 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')]为了实现表中的简单选择计数（*），我们可以将其应用为：
>>>session.query(func.count(*)).select_from(User).scalar()
4
如果直接使用主键表示计数，select_from()可以被去掉：
>>>session.query(func.count(User.id)).scalar()
4
2. 外键
2.1 建立外键
当存在与User表相关的第二个表时，如何去映射和查询？比如在系统中，Users表存储与用户名相关联的任意数量的电子邮件地址。这意味着从users到addresses存在一对多的关联。来定义这个表及其映射类Address：
>>> fromsqlalchemy 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”)relationship()函数告诉ORM Address类通过Address.user属性与User类链接起来。relationship()函数使用两个表之间的外键关系确定该链接的性质----Address.user 将是多对一的。同时，一条额外的relationship()指令会被用在User类上映射属性User.address。在两条relationship()指令中，relationship.back_populates参数值是互补引用的属性名称；这样，两条relationship()反向的表达了相同的决策；一方面Address.user引用了User实例，另一方面User.addresses引用了Address实例。
注意: 在旧版本中用的是backref参数，新版本用back_populates代替。
这两个互补关系：Address.user和User.addresses被称为双向关系。这是SQLAlchemy ORM的一个非常关键的功能。
假设声明的方法已经开始使用，relationship()中和其他类关联的参数可以通过strings指定。在上文的User类中，一旦所有映射成功，为了产生实际的参数，这些字符串会被当做Python的表达式。下面是一个在User类中创建双向联系的例子：
class User(Base): addresses =relationship("Address",
order_by="Address.id",backref="user")
补充知识:
在大多数的外键约束（尽管不是所有的）关系数据库只能链接到一个主键列，或具有唯一约束的列。
外键约束如果是指向多个列的主键，并且它本身也具有多列，这种被称为“复合外键”。
外键列可以自动更新自己来相应它所引用的行或者列。这被称为级联，是一种建立在关系数据库的功能。
外键可以参考自己的表格。这种被称为“自引”外键。
我们需要在数据库中创建一个addresses表，所以我们会创建另一个元数据，这将会跳过已经创建的表。
2.2 使用外键
当再次创建用户时，将出现一个空白地址集合。各种集合类型（如集合和字典）都是有可能的，默认情况下，集合类型是Python列表。
>>>jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
>>>jack.addresses
[]如果要给用户对象添加地址信息，只需给用户分配一个完整的地址列表即可:
>>>jack.addresses = [
... Address(email_address='jack@google.com'),
... Address(email_address='j25@yahoo.com')]当使用双向关系时，在一个类中添加的元素会自动在另一个类中可见。此行为基于更改事件的属性时发生，它是对Python进行评估，而对SQL并没有做任何更改：
>>>jack.addresses[1]
<Address(email_address='j25@yahoo.com')>
>>>jack.addresses[1].user
<User(name='jack',fullname='Jack Bean',password='gjffdd')>将上例中Jack的地址信息添加并提交到数据库，Jack和Address表中的两个地址信息会一次性的被添加到会话中，该过程实际上是级联操作。
>>>session.add(jack)
>>>session.commit()
如果去查询User表中的Jack，我们只会得到Jack的信息，而不包含地址信息，也就是说以上只是建立了级联关系，并没有对User表进行更改。
>>>jack = session.query(User).\
...filter_by(name='jack').one()
>>>jack
<User(name='jack',fullname='Jack Bean', password='gjffdd')>当查看地址时却又能够得到地址的集合：
>>>jack.addresses
[<Address(email_address='jack@google.com')>,<Address(email_address='j25@yahoo.com')>]
当访问addresses集合时，SQL就像突然被执行。这是一个延迟加载(lazy loading)关系的例子。addresses集合加载完成之后就可以像对待普通列表一样对其进行操作。这种加载方式是可以优化的
3.连接查询
3.1 概述
当有两个表之后，可以显示更多的查询特性，特别是可以对两个表同时进行查询。维基百科上的SQL JOIN 词条对于连接查询技术做了很好的介绍，这里引用其中的几个来说明：
为了在User表和Address表之间构建一个简单的隐式连接，query.filter()会被用来平等的对待两个表的字段：
>>> foru, 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', password='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', password='gjffdd')>]query.join()知道如何将User和Address连接是因为两表之间只有一个外键。如果没有这个外键或有多个外键，下面的形式将会让query.join()使用效果更好：
query.join(Address,User.id==Address.user_id) # 明确条件
query.join(User.addresses) # 从左到右指定关系（左连接）
query.join(Address,User.addresses) # 同样，有明确的目标
query.join('addresses') # 同样，字符串指定明确条件
outerjoin()函数实现外链接使用了同样的思想：
query.outerjoin(User.addresses) # 外链接对于任何SQL-fluent应用程序join()方法是一个极其重要的使用方法。对于连接的使用，可以搜索外连接，内连接，左连接，右连接等概念去了解。
3.2 使用别名
当进行多表查询时，如果需要多次引用同一个表，SQL语句通常要求给该表取一个别名，一遍可以与该表的其他事件相区别。Query也支持使用别名机制。下例中两次使用了Address实体类取查询同时拥有两个不同电子邮箱的用户：
>>>from sqlalchemy.orm import aliased
>>>adalias1 = aliased(Address)
>>>adalias2 = aliased(Address)
>>> forusername, 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)
jackjack@google.com j25@yahoo.com3.3 使用子查询
Query同样适用于需要生成子查询的语句。假设相载入用户对象同时统计每个用户有多少条地址记录，生成这样的SQL的最佳方法是获取由User.ID分组的地址计数并加入到父节点。在这种情况下，可以使用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) ASadr_count
ON users.id=adr_count.user_id使用Query，可以从内部构建像这样的语句。语句访问器能够返回一个SQL语句表达式来描述特定Query生成的描述表，这是一个select()构造的实例：
>>> 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()方法生成一个描述SELECT语句SQL表达式并嵌入了一个别名(这个别名实际上是用来标记query.statement.alias()的返回值)。
拿到这个表达式之后，它实际上返回一个表结构，就像在初始时用户创建的用户表一样。语句得到的列也可通过C属性访问：
>>> foru, 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', 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')> 23.4 从子查询中获取实体
上面只是从子查询的结果中选择了一列进行显示。如果希望子查询映射到一个实体，只需要使用aliased()方法取一个别名来关联这个子查询：
>>>stmt = session.query(Address).\
… filter(Address.email_address !=‘j25@yahoo.com’).\
… subquery()
>>>adalias = aliased(Address, stmt)
>>> foruser, address in session.query(User, adalias).\
… join(adalias, User.addresses):
… print(user)
… print(address)
<User(name='jack',fullname='Jack Bean', password='gjffdd')>
<Address(email_address='jack@google.com')>3.5 使用EXISTS
SQL中的EXISTS关键字是一个布尔型运算符，当给定的表达式包含有结果时返回True。它可以在很多情况下代替连接查询，也可以用于查找相关表中没有相应行的情况。
下面是一个显示的EXISTS结构：
>>>from sqlalchemy.sql import exists
>>>stmt = exists().where(Address.user_id==User.id)
>>> forname, in session.query(User.name).filter(stmt):
… print(name)
jack该查询具有多个运算符表现了EXISTS的使用特点。上文中，也可以把any()与User.address一起使用来表达。
>>> forname, in session.query(User.name).\
… filter(User.address.any()):
… print(name)
jackany()也可以使用参数来限制匹配到的行：
>>> forname, in session.query(User.name).\
... filter(User.addresses.any(Address.email_address.like('%google%'))):
... print(name)
jack当处理多对一关系时，has()运算符与any()起到的作用是相同的（在这里也请注意一下~运算符，意思为“NOT”）：
>>>session.query(Address).\
... filter(~Address.user.has(User.name=='jack')).all()
[]3.6 常用的关系运算符
__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'))
# 也可以接收关键字参数
query.filter(User.addresses.any(email_address='bar'))
has() (用于量值引用):
query.filter(Address.user.has(name='ed'))
Query.with_parent() (可用于所有关系):
session.query(Address).with_parent(someuser, 'addresses')
三、急加载
上述章节中在访问User表的User.addresses记录时提交SQL语句演示了懒惰加载操作。在查询操作中如果想要减少查询的数量（在很多情况下），可以将急加载应用到查询操作中。SQLAlchemy提供了三种类型的急加载方式，其中两种是自动的，第三种涉及到自定义设置。这三种都是通过对查询中调用函数时通过Query.options()方法增加额外属性用以说明希望的加载方式来实现的。
1. SubqueryLoad
如果想要让User.addresses更急切的加载，可以通过orm.subqueryload()选项发出一个Second-SELECT 语句来完全关联这组对象及相关数据集合查询到的结果。通常根据实际情况会给”subquery”取一个别名，以便能够作为子查询嵌入到相关表的SELECT语句中而被重用。听起来有点复杂，但用起来很简单：
>>>from sqlalchemy.orm import subqueryload
>>>jack = session.query(User).\
… options(subqueryload(User.addresses)).\
… filter_by(name=’jack’).one()
>>> jack
<User(name='jack',fullname='Jack Bean', password='gjffdd')>
>>>jack.addresses
[<Address(email_address='jack@google.com')>,Address(email_address='j25@yahoo.com')>]注意：subqueryload()与诸如Query.first(),Query.limit(),Query.offset()等限制类的函数一起使用时，还应该在唯一列上使用Query.order_by()方法以保证能够得到正确结果。
2.Joined Load
另一个自动加载功能是更广为人知的，称为orm.joinedload()。这种加载方式会发出一个连接，默认情况下是一个左外连接(LEFTOUTER JOIN)，以便在一步中加载lead对象和相关对象或集合。以下演示如何以这种方式加载相同的地址集合——请注意，查询将发出额外的连接，但无论如何Jack的User.addresses集合已经被实际获得:
>>>from sqlalchemy.orm import joinedload
>>>jack = session.query(User).\
… options(joinedload(User.addresses)).\
… filter_by(name=’jack’).one()
>>>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 会得到两行数据，但是只返回了一个用户实例。这是因为查询使用了一个基于对象标识符的“uniquing”策略。这是特定的，因此可以在不影响查询结果的情况下应用连接的快速加载。
虽然joinedload()已经存在了很长时间，subqueryload()是一种更新的加载方式。subqueryload()更适合于加载相关联的集合，而joinedload()更适合于多对一的关系，这是由于主表和相关对象或集合都只有一行会被加载。
注意：joinedload()不是join()的替代品。由joinedload()方法创建的连接查询采用的匿名别名这样就不会影响查询结果。而query.order_by()或者query.filter()调用时不能引用由Query.join()方法构建的所谓的“用户空间”表格。其结果是，joinedload()仅适用于影响相关对象或集合是如何作为优化细节加载的-----它可以被添加或移除而不会对实际结果产生影响。
3. ExplicitJoin+Eagerload(显示连接+急加载)
第三种急加载方式是当显式地构造一个连接查询来定位主要记录时，并希望将额外的表附加到主对象上的相关对象或集合上。这个特性是由orm.contains_eager()函数提供的，常用于在查询中需要对多对一关系的对象同时进行预加载和筛选的情况。
>>>from sqlalchemy.orm import contains_eager
>>>jacks_addresses = session.query(Address).\
... join(Address.user).\
... filter(User.name=='jack').\
... options(contains_eager(Address.user)).\
... all()
>>>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')>有关急加载的更多信息，包括默认情况下如何配置各种加载形式，请参阅关系加载技术部分。
四、删除
1. 简单删除
如果要删除Jack，只需要在会话中将标记Jack的对象删除，然后再发出计数查询查看结果：
>>>session.delete(jack)
>>>session.query(User).filter_by(name='jack').count()
0如预期一样，Jack被删除了，但是Jack的Address对象现在什么情况？
>>>session.query(Address).filter(
... Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count()
2地址信息还在！取分析SQL数据，可以看到每条地址记录的user_id字段被设置为NULL，但是记录并没有被删除。sqlalchemy并不认为这是级联删除，必须去告诉它这么做：
2. 配置删除/级联删除
在User.addresses关系中配置cascade选项来改变行为。虽然SQLAlchemy允许在任何时间点向映射添加新的属性和关系，但是需要删除现有关系，因此我需要完全拆掉映射并重新开始-
将关闭会话：
>>>session.close()
ROLLBACK
使用一个新的 declarative_base():
>>>Base = declarative_base()
接下来，声明User类，并配置包含级联动作的addresses属性。
>>> calss 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的关联关系：
>>> classAddress(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=”address”)
…
… def __repr__(self):
… return “<Address(email_address=’%s’>”% self.email_address现在，当再次加载用户Jack(使用get()方法由主键加载)，从相应的地址集合里删除一个地址将导致Address里的该地址被删除：
# load Jack byprimary key
>>>jack = session.query(User).get(5)
# remove oneAddress (lazy load fies off)
>>> deljack.addresses[1]
# only oneaddress remains
>>>session.query(Address).filter(
… Address.email_address.in_([‘jack@google.com’, ‘j25@yahoo.com’])
… ).count()
1删除Jack将删除User里的Jack和Address里剩余的与Jack相关的地址：
>>>session.delete(jack)
>>>session.query(User).filter_by(name='jack').count()
0
>>>session.query(Address).filter(
... Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count()
0五、建立多对多关系
假设要创建一个Blog应用程序，用户可以在里面写入多篇博客，每个博客可以包含多个与其相关的关键字项。
对于一个简单的多对多关系，首先创建一个未映射的表结构作为关联表：
>>>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)
... )从上例可以看出，直接声明一个表与声明一个映射类略有不同。直接声明表是一个构造函数，每个单独的字段参数用逗号分隔。列对象显式的给出它们的名字，而不是从属性名称中指定。
接下来使用互补的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上例中，多对多的关系是在BlogPost.keywords。多对多关系的定义特征是次要关键字参数，它引用表示关联表的表对象。该表仅包含引用关系的两面的列；如果它有其他列，如它自己的主键，或其他表的外键，则SQLAlchemy需要一个不同的使用模式，称为“关联对象”，在关联对象中描述。
同时，BlogPost应该有一个author字段，将其添加为另一个双向关系：一个用户可能有很多BlogPost帖子。当访问User.posts时，如果要进一步过滤结果以便不加载整个集合，可以对relationship()设置关键字参数:lazy=’dynamic’，这样会在该属性上设置一个交替加载的策略：
>>>BlogPost.author = relationship(User, back_populates="posts")
>>>User.posts = relationship(BlogPost, back_populates="author",lazy="dynamic")创建新表:
>>>Base.metadata.create_all(engine)用法上和之前没什么大的不同。下面给Wendy添加一些博客文章：
>>>wendy = session.query(User).\
... filter_by(name='wendy').\
... one()
>>>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”查找所有博客文章，可以得到所有的博客帖子只要它的关键字中含有”firstpost”字符串：
>>>session.query(BlogPost).\
... filter(BlogPost.keywords.any(keyword='firstpost')).\
... all()[BlogPost("Wendy'sBlog Post", 'This is a test', <User(name='wendy', fullname='WendyWilliams', password='foobar')>)]
如果要查找Wendy用户所拥有的posts，可以将查询对象缩小到该用户：
>>> session.query(BlogPost).\
... filter(BlogPost.author==wendy).\
... filter(BlogPost.keywords.any(keyword='firstpost')).\
... all()
[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', password='foobar')>)]或者直接像下面这样查询：
>>>wendy.posts.\
... filter(BlogPost.keywords.any(keyword='firstpost')).\
... all()
[BlogPost("Wendy'sBlog Post", 'This is a test', <User(name='wendy', fullname='WendyWilliams', password='foobar')>)]六、更多参考
Query Reference: query_api_toplevel
MapperReference: MapperConfiguration
RelationshipReference: RelationshipConfiguration
1585
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
