SQL 表达式语言教程（1.x API）

关于此文档

本教程介绍已使用多年的著名SQLAlchemy核心API。在SQLAlchemy 1.4中，有两种不同的核心使用风格 1.x style 和 2.0 style ，后者主要在如何控制事务方面进行了一些调整，并缩小了SQL语句构造的执行模式。

计划是在SQLAlchemy 2.0中，在1.4系列中持续的一个弃用阶段之后，1.x风格核心使用的那些元素将被删除。对于ORM的使用，一些1.x样式的元素仍然可用；请参见 迁移到Alchemy 完整概述的文档。

这里的教程适用于那些想了解SQLAlchemy Core多年来如何使用的用户，特别是那些使用现有应用程序或1.x风格的相关学习材料的用户。

有关从新的1.4/2.0角度介绍SQLAlchemy核心的信息，请参见 SQLAlchemy 1.4/2.0教程 .

参见

迁移到Alchemy

SQLAlchemy 1.4/2.0教程

SQLAlchemy表达式语言提供了一个使用Python构造表示关系数据库结构和表达式的系统。这些构造被建模为尽可能接近底层数据库的构造，同时提供了对数据库后端之间的各种实现差异的少量抽象。虽然构造试图用一致的结构表示后端之间的等价概念，但它们并不隐藏后端特定子集所特有的有用概念。因此，表达式语言提供了一种编写与后端无关的SQL表达式的方法，但不尝试强制使用与后端无关的表达式。

表达式语言与对象关系映射器形成了对比，对象关系映射器是一种独特的API，它构建在表达式语言之上。鉴于ORM在 对象关系教程（1.x API） ，提出了一种高层次、抽象的使用模式，它本身就是表达式语言应用的一个例子，表达式语言提出了一个直接表示关系数据库原始结构的系统，而没有意见。

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

成功的应用程序可以只用表达式语言来构造，但是应用程序需要定义自己的系统，将应用程序概念转换为单个数据库消息和单个数据库结果集。或者，在高级场景中，使用ORM构建的应用程序可能会在某些需要特定数据库交互的区域中偶尔直接使用表达式语言。

以下教程采用doctest格式，即 >>> 行表示可以在python命令提示下键入的内容，下面的文本表示预期的返回值。本教程没有先决条件。

版本检查

快速检查以确认我们至少在 版本1.4 SQLAlchemy：

>>> import sqlalchemy
>>> sqlalchemy.__version__  
1.4.0

连接

对于本教程，我们将使用一个只在内存中的sqlite数据库。这是一种简单的测试方法，无需在任何地方定义实际的数据库。连接我们使用的 create_engine() ：

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

这个 echo 标志是设置sqlachemy日志的快捷方式，它是通过python的标准实现的。 logging 模块。启用它后，我们将看到生成的所有SQL。如果您正在学习本教程并希望生成的输出更少，请将其设置为 False . 本教程将在弹出窗口后面格式化SQL，这样它就不会妨碍我们的工作；只需单击“SQL”链接查看正在生成的内容。

的返回值 create_engine() 是的实例 Engine 它表示到数据库的核心接口，通过 dialect 处理数据库的详细信息和 DBAPI 在使用中。在这种情况下，sqlite方言将解释python内置的指令 sqlite3 模块。

第一次像这样的方法 Engine.execute() 或 Engine.connect() 被称为 Engine 建立一个真实的 DBAPI 连接到数据库，该数据库随后用于发出SQL。

参见

数据库URL -包括以下示例 create_engine() 通过指向更多信息的链接连接到几种数据库。

定义和创建表

在大多数情况下，SQL表达式语言根据表列构造其表达式。在SQLAlchemy中，列通常由一个名为 Column 以及在所有情况下 Column 与 Table . 收藏 Table 对象及其关联的子对象称为 数据库元数据 . 在本教程中，我们将明确列出 Table 但请注意，sa还可以“导入”整个 Table 从现有数据库自动生成的对象（此过程称为 桌子反射 ）

我们在一个名为 MetaData ，使用 Table 构造，类似于常规的SQL创建表语句。我们将创建两个表，其中一个表表示应用程序中的“用户”，另一个表表示“用户”表中每行的零个或多个“电子邮件地址”：

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> metadata_obj = MetaData()
>>> users = Table('users', metadata_obj,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
... )

>>> addresses = Table('addresses', metadata_obj,
...   Column('id', Integer, primary_key=True),
...   Column('user_id', None, ForeignKey('users.id')),
...   Column('email_address', String, nullable=False)
...  )

关于如何定义 Table 对象，以及如何从现有数据库自动创建它们，如中所述。 用元数据描述数据库 .

接下来，告诉 MetaData 实际上，我们希望在sqlite数据库中为real创建所选的表，我们使用 create_all() 通过它 engine 指向数据库的实例。这将在创建前检查每个表的存在性，因此可以安全地多次调用：

sql>>> metadata_obj.create_all(engine)
BEGIN...
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    PRIMARY KEY (id)
)
[...] ()
CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
)
[...] ()
COMMIT

注解

熟悉create table语法的用户可能会注意到varchar列的生成没有长度；在sqlite和postgresql上，这是一个有效的数据类型，但在其他数据类型上，这是不允许的。因此，如果在其中一个数据库上运行本教程，并且您希望使用sqlachemy来发布create table，那么可以为 String 类型如下：

Column('name', String(50))

上的长度字段 String 以及类似的精度/比例字段 Integer ， Numeric 除创建表时外，SQLAlchemy不引用等。

此外，Firebird和Oracle需要序列来生成新的主键标识符，而sqlAlchemy在没有得到指示的情况下不会生成或假定这些标识符。为此，您使用 Sequence 结构：

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

一个完整的，万无一失的 Table 因此：：

users = Table('users', metadata_obj,
   Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
   Column('name', String(50)),
   Column('fullname', String(50)),
   Column('nickname', String(50))
)

我们包括这个更详细的 Table 单独构造以突出主要面向Python使用的最小构造与将用于在具有更严格要求的特定后端集上发出create table语句的构造之间的区别。

插入表达式

我们将创建的第一个SQL表达式是 Insert 表示insert语句的。这通常是相对于其目标表创建的：

>>> ins = users.insert()

若要查看此构造生成的SQL示例，请使用 str() 功能：

>>> str(ins)
'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'

注意上面的insert语句命名了 users 表。这可以通过使用 values() 方法，它显式地建立insert的values子句：

>>> ins = users.insert().values(name='jack', fullname='Jack Jones')
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'

上面，而 values 方法将values子句限制为两列，即我们放入的实际数据 values 没有呈现到字符串中；相反，我们得到了命名的绑定参数。事实证明，我们的数据 is 存储在我们的 Insert 构造，但它通常只在实际执行语句时出现；由于数据由文本值组成，因此SQLAlchemy会自动为它们生成绑定参数。我们现在可以通过查看语句的编译形式来查看这些数据：

>>> ins.compile().params  
{'fullname': 'Jack Jones', 'name': 'jack'}

执行

一个有趣的部分 Insert 正在执行它。这是使用数据库连接执行的，它由 Connection 对象。要获取连接，我们将使用 Engine.connect() 方法：

>>> conn = engine.connect()
>>> conn
<sqlalchemy.engine.base.Connection object at 0x...>

这个 Connection 对象表示活动签出的DBAPI连接资源。让我们用我们的 Insert 对象并查看发生的情况：

>>> result = conn.execute(ins)
INSERT INTO users (name, fullname) VALUES (?, ?)
[...] ('jack', 'Jack Jones')
COMMIT

所以insert语句现在被发布到数据库中。尽管我们在输出中得到了位置“qmark”绑定参数而不是“命名”绑定参数。怎么会？因为当执行时， Connection 使用SQLite 方言 帮助生成语句；当我们使用 str() 函数，语句不知道此方言，并返回使用命名参数的默认值。我们可以手动查看如下：

>>> ins.bind = engine
>>> str(ins)
'INSERT INTO users (name, fullname) VALUES (?, ?)'

那呢？ result 我们调用时得到的变量 execute() ？作为一种炼金术 Connection 对象引用DBAPI连接，结果称为 CursorResult 对象，类似于DBAPI光标对象。在插入的情况下，我们可以从中获取重要的信息，例如使用 CursorResult.inserted_primary_key ：

>>> result.inserted_primary_key
(1,)

价值 1 是由sqlite自动生成的，但这只是因为我们没有指定 id 我们的专栏 Insert 语句；否则，将使用显式值。在这两种情况下，SQLAlchemy总是知道如何获取新生成的主键值，即使在不同的数据库中生成它们的方法不同；每个数据库的 Dialect 知道确定正确值（或值）所需的具体步骤；注意 CursorResult.inserted_primary_key 返回一个列表，以便它支持复合主键）。这里的方法范围从使用 cursor.lastrowid ，从特定于数据库的函数中选择，使用 INSERT..RETURNING 语法；这都是透明的。

执行多个语句

上面的插入示例有意地略作说明了表达式语言构造的一些不同行为。通常情况下， Insert 语句通常根据发送到 execute() 方法对 Connection 这样就不需要使用 values 关键字与 Insert . 让我们创建一个通用 Insert 再次声明并以“正常”方式使用：

>>> ins = users.insert()
>>> conn.execute(ins, {"id": 2, "name":"wendy", "fullname": "Wendy Williams"})
INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
[...] (2, 'wendy', 'Wendy Williams')
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

上面，因为我们在 execute() 方法，编译的 Insert 包括所有三列。这个 Insert 语句在执行时根据指定的参数编译；如果指定的参数较少，则 Insert 它的values子句中的条目将更少。

使用dbapi发布许多插入 executemany() 方法，我们可以发送一个字典列表，每个字典包含一组不同的要插入的参数，就像我们在这里添加一些电子邮件地址一样：

>>> conn.execute(addresses.insert(), [
...    {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
...    {'user_id': 1, 'email_address' : 'jack@msn.com'},
...    {'user_id': 2, 'email_address' : 'www@www.org'},
...    {'user_id': 2, 'email_address' : 'wendy@aol.com'},
... ])
INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
[...] ((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

上面，我们再次依赖于sqlite为每个 addresses 行。

当执行多组参数时，每个字典必须具有 same 一组键；也就是说，某些字典中的键不能少于其他字典中的键。这是因为 Insert 语句是根据 第一 字典在列表中，并且假定所有后续参数字典都与该语句兼容。

“ExecuteMany”类型的调用可用于 insert() ， update() 和 delete() 构造。

选择

我们从插入开始，只是为了让我们的测试数据库中有一些数据。数据中更有趣的部分是选择它！稍后我们将介绍更新和删除语句。用于生成select语句的主要构造是 select() 功能：

>>> from sqlalchemy.sql import select
>>> s = select(users)
>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()

上面，我们发布了一个基本的 select() 呼叫，放置 users 表在select的columns子句中，然后执行。SQLAlchemy扩展了 users 表转换为它的每个列的集合，并且还为我们生成了一个FROM子句。

在 1.4 版更改: 这个 select() 构造现在以位置方式接受列参数，如 select(*args) . 以前的风格 select() 现在不推荐接受列元素列表。看到了吗 select（），case（）现在接受位置表达式 .

返回的结果是 CursorResult 对象，其行为与DBAPI光标非常类似，包括 fetchone() 和 fetchall() . 这些方法返回行对象，这些对象通过 Row 班级。可以直接迭代result对象，以便提供 Row 物体：

>>> for row in result:
...     print(row)
(1, u'jack', u'Jack Jones')
(2, u'wendy', u'Wendy Williams')

上面，我们可以看到 Row 生成类似元组的简单结果。在Python中，获取行时访问这些元组值的最规范方法是通过tuple赋值：

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> for id, name, fullname in result:
...     print("name:", name, "; fullname: ", fullname)
name: jack ; fullname:  Jack Jones
name: wendy ; fullname:  Wendy Williams

这个 Row 对象的行为实际上类似于名为tuple的Python，因此我们也可以使用attribute access从行本身访问这些属性：

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> for row in result:
...     print("name:", row.name, "; fullname: ", row.fullname)
name: jack ; fullname:  Jack Jones
name: wendy ; fullname:  Wendy Williams

若要使用字符串通过名称访问列，无论是以编程方式生成的列，还是包含非ascii字符，则 Row._mapping 视图可用于提供类似字典的访问：

sql>>> result = conn.execute(s)
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
>>> row = result.fetchone()
>>> print("name:", row._mapping['name'], "; fullname:", row._mapping['fullname'])
name: jack ; fullname: Jack Jones

1.4 版后已移除: 在1.4之前的SQLAlchemy版本中，上面的访问使用 Row._mapping 将针对row对象本身继续，即：

row = result.fetchone()
name, fullname = row["name"], row["fullname"]

此模式现在已弃用，并将在SQLAlchemy 2.0中删除，以便 Row 对象现在的行为可能完全像一个名为tuple的Python。

在 1.4 版更改: 补充 Row._mapping 提供类似字典的访问 Row ，取代对 Row 直接对象。

作为 Row 是元组，也可以使用序列（即整数或切片）访问：

>>> row = result.fetchone()
>>> print("name:", row[1], "; fullname:", row[2])
name: wendy ; fullname: Wendy Williams

更专门的列访问方法是使用直接对应于特定列的SQL构造作为映射键；在本例中，这意味着我们将使用 Column 对象在我们的SELECT中直接作为键与 Row._mapping 收藏：

sql>>> for row in conn.execute(s):
...     print("name:", row._mapping[users.c.name], "; fullname:", row._mapping[users.c.fullname])
SELECT users.id, users.name, users.fullname
FROM users
[...] ()
name: jack ; fullname: Jack Jones
name: wendy ; fullname: Wendy Williams

这个 CursorResult 对象具有关闭底层DBAPI的“自动关闭”行为 cursor 获取所有挂起的结果行时。如果 CursorResult 在发生此类自动关闭之前将被丢弃，可以使用 CursorResult.close() 方法：

>>> result.close()

选择特定列

如果我们想更仔细地控制select的columns子句中的列，我们引用 Column 来自我们的对象 Table . 这些可用作 c 的属性 Table 对象：

>>> s = select(users.c.name, users.c.fullname)
sql>>> result = conn.execute(s)
SELECT users.name, users.fullname
FROM users
[...] ()
>>> for row in result:
...     print(row)
(u'jack', u'Jack Jones')
(u'wendy', u'Wendy Williams')

让我们来观察一下关于FROM子句的有趣之处。虽然生成的语句包含两个不同的部分，“select columns”部分和“from table”部分，但是 select() 构造只有包含列的列表。这是怎么工作的？让我们试试 two 我们的桌子 select() 声明：

sql>>> for row in conn.execute(select(users, addresses)):
...     print(row)
SELECT users.id, users.name, users.fullname, addresses.id AS id_1, addresses.user_id, addresses.email_address
FROM users, addresses
[...] ()
(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')
(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')
(1, u'jack', u'Jack Jones', 3, 2, u'www@www.org')
(1, u'jack', u'Jack Jones', 4, 2, u'wendy@aol.com')
(2, u'wendy', u'Wendy Williams', 1, 1, u'jack@yahoo.com')
(2, u'wendy', u'Wendy Williams', 2, 1, u'jack@msn.com')
(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

它放置 both 表到FROM子句中。但同时，它也造成了真正的混乱。熟悉SQL连接的人知道这是 笛卡尔积 ；每行 users 表是根据 addresses 表。因此，为了使这句话保持清醒，我们需要一个WHERE子句。我们用 Select.where() ：

>>> s = select(users, addresses).where(users.c.id == addresses.c.user_id)
sql>>> for row in conn.execute(s):
...     print(row)
SELECT users.id, users.name, users.fullname, addresses.id AS id_1,
   addresses.user_id, addresses.email_address
FROM users, addresses
WHERE users.id = addresses.user_id
[...] ()
(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')
(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')
(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')

所以看起来好多了，我们在 select() 它的作用是增加 WHERE users.id = addresses.user_id 我们的声明，我们的结果被管理下来，以便 users 和 addresses 行是有意义的。但让我们看看这个表达？它只在两个不同的 Column 物体。很明显有什么事发生了。说 1 == 1 生产 True 和 1 == 2 生产 False ，不是WHERE子句。所以让我们看看这个表达式在做什么：

>>> users.c.id == addresses.c.user_id
<sqlalchemy.sql.elements.BinaryExpression object at 0x...>

哇，惊喜！这不是一个 True 也没有 False . 那是什么？

>>> str(users.c.id == addresses.c.user_id)
'users.id = addresses.user_id'

如你所见， == 运算符正在生成一个非常类似于 Insert 和 select() 由于python的 __eq__() 内置；你呼叫 str() 它产生SQL。到目前为止，我们所处理的一切最终都是同一类型的对象。sqlachemy terms所有这些表达式的基类 ColumnElement .

算子

既然我们偶然发现了sqlacalchemy的运算符范式，那么让我们来看看它的一些功能。我们已经看到了如何将两列相等：

>>> print(users.c.id == addresses.c.user_id)
users.id = addresses.user_id

如果我们使用一个文本值（一个文本含义，而不是一个sqlachemy子句对象），我们得到一个绑定参数：

>>> print(users.c.id == 7)
users.id = :id_1

这个 7 文字嵌入结果 ColumnElement 我们可以用同样的方法 Insert 要查看的对象：

>>> (users.c.id == 7).compile().params
{u'id_1': 7}

事实证明，大多数python操作符在这里生成一个SQL表达式，如equals、not equals等：

>>> print(users.c.id != 7)
users.id != :id_1

>>> # None converts to IS NULL
>>> print(users.c.name == None)
users.name IS NULL

>>> # reverse works too
>>> print('fred' > users.c.name)
users.name < :name_1

如果将两个整型列加在一起，则得到一个加法表达式：

>>> print(users.c.id + addresses.c.id)
users.id + addresses.id

有趣的是， Column 很重要！如果我们使用 + 有两个基于字符串的列（回想一下，我们把类型 Integer 和 String 关于我们 Column 对象），我们得到不同的结果：

>>> print(users.c.name + users.c.fullname)
users.name || users.fullname

哪里 || 是大多数数据库上使用的字符串连接运算符。但不是所有人。MySQL用户，不要害怕：

>>> print((users.c.name + users.c.fullname).
...      compile(bind=create_engine('mysql://'))) 
concat(users.name, users.fullname)

上面说明了为 Engine 连接到一个MySQL数据库； || 操作符现在编译为mysql concat() 功能。

如果遇到一个真正不可用的操作员，则可以使用 Operators.op() 方法；这将生成所需的任何运算符：

>>> print(users.c.name.op('tiddlywinks')('foo'))
users.name tiddlywinks :name_1

此函数还可用于显式地生成位运算符。例如：：

somecolumn.op('&')(0xff)

是中的值的位与 somecolumn .

使用时 Operators.op() ，表达式的返回类型可能很重要，尤其是在将作为结果列发送的表达式中使用运算符时。对于这种情况，如果不是通常期望的类型，请确保使用 type_coerce() ：：

from sqlalchemy import type_coerce
expr = type_coerce(somecolumn.op('-%>')('foo'), MySpecialType())
stmt = select(expr)

对于布尔运算符，使用 Operators.bool_op() 方法，它将确保将表达式的返回类型作为布尔值处理：：

somecolumn.bool_op('-->')('some value')

常用运算符

下面是核心表达式语言和ORM中使用的一些最常见的运算符的简要说明。在这里，我们看到在使用 Select.where() 方法，但也可以用于其他场景。

所有列类对象共有的所有列级操作的列表位于 ColumnOperators .

• ColumnOperators.__eq__() ：：

  statement.where(users.c.name == 'ed')

• ColumnOperators.__ne__() ：：

  statement.where(users.c.name != 'ed')

• ColumnOperators.like() ：：

  statement.where(users.c.name.like('%ed%'))

注解

ColumnOperators.like() 呈现like运算符，它在某些后端不区分大小写，在其他后端区分大小写。对于保证不区分大小写的比较，请使用 ColumnOperators.ilike() .

• ColumnOperators.ilike() （不区分大小写，例如）：：

  statement.where(users.c.name.ilike('%ed%'))

注解

大多数后端不直接支持iLike。对于那些 ColumnOperators.ilike() 运算符呈现一个表达式，该表达式与应用于每个操作数的下SQL函数组合在一起。

• ColumnOperators.in_() ：：

  statement.where(users.c.name.in_(['ed', 'wendy', 'jack']))
  
  # works with Select objects too:
  statement.where.filter(users.c.name.in_(
      select(users.c.name).where(users.c.name.like('%ed%'))
  ))
  
  # use tuple_() for composite (multi-column) queries
  from sqlalchemy import tuple_
  statement.where(
      tuple_(users.c.name, users.c.nickname).\
      in_([('ed', 'edsnickname'), ('wendy', 'windy')])
  )

• ColumnOperators.not_in() ：：

  statement.where(~users.c.name.in_(['ed', 'wendy', 'jack']))

• ColumnOperators.is_() ：：

  statement.where(users.c. == None)
  
  # alternatively, if pep8/linters are a concern
  statement.where(users.c.name.is_(None))

• ColumnOperators.is_not() ：：

  statement.where(users.c.name != None)
  
  # alternatively, if pep8/linters are a concern
  statement.where(users.c.name.is_not(None))

• AND ：：

  # use and_()
  from sqlalchemy import and_
  statement.where(and_(users.c.name == 'ed', users.c.fullname == 'Ed Jones'))
  
  # or send multiple expressions to .where()
  statement.where(users.c.name == 'ed', users.c.fullname == 'Ed Jones')
  
  # or chain multiple where() calls
  statement.where(users.c.name == 'ed').where(users.c.fullname == 'Ed Jones')

注解

确保使用 and_() 和 not Python and 接线员！

• OR ：：

  from sqlalchemy import or_
  statement.where(or_(users.c.name == 'ed', users.c.name == 'wendy'))

注解

确保使用 or_() 和 not Python or 接线员！

• ColumnOperators.match() ：：

  statement.where(users.c.name.match('wendy'))

注解

ColumnOperators.match() 使用特定于数据库的 MATCH 或 CONTAINS 函数；其行为因后端而异，在某些后端（如sqlite）上不可用。

操作员自定义

同时 Operators.op() 该核心支持操作员系统在类型级别的基本定制和扩展，便于快速地访问定制操作员。可以按类型修改现有运算符的行为，并且可以定义新的操作，这些操作可用于属于该特定类型的所有列表达式。见剖面图 重新定义和创建新的运算符 以获取描述。

连词

我们想炫耀一下我们内部的一些运营商 select() 构造。但是我们需要把它们放在一起，所以我们先介绍一些连词。连词是把事物组合在一起的一些小词，比如and和or。我们也会发现不是。 and_() ， or_() 和 not_() 可以从sqlacalchemy提供的相应函数中工作（注意，我们还引入了 ColumnOperators.like() ）：

>>> from sqlalchemy.sql import and_, or_, not_
>>> print(and_(
...         users.c.name.like('j%'),
...         users.c.id == addresses.c.user_id,
...         or_(
...              addresses.c.email_address == 'wendy@aol.com',
...              addresses.c.email_address == 'jack@yahoo.com'
...         ),
...         not_(users.c.id > 5)
...       )
...  )
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1
   OR addresses.email_address = :email_address_2)
AND users.id <= :id_1

您也可以使用重新设置的按位“与”、“或”和“非”运算符，尽管由于python运算符的优先级，您必须注意括号：

>>> print(users.c.name.like('j%') & (users.c.id == addresses.c.user_id) &
...     (
...       (addresses.c.email_address == 'wendy@aol.com') | \
...       (addresses.c.email_address == 'jack@yahoo.com')
...     ) \
...     & ~(users.c.id>5)
... )
users.name LIKE :name_1 AND users.id = addresses.user_id AND
(addresses.email_address = :email_address_1
    OR addresses.email_address = :email_address_2)
AND users.id <= :id_1

因此，利用所有这些词汇，让我们选择所有在AOL或MSN拥有电子邮件地址的用户，他们的名称以字母“m”和“z”开头，我们还将生成一个包含他们的全名和电子邮件地址的列。我们将在此语句中添加两个新构造， ColumnOperators.between() 和 ColumnElement.label() . ColumnOperators.between() 生成一个between子句，和 ColumnElement.label() 在列表达式中使用 AS 关键字；建议从不具有名称的表达式中进行选择：

>>> s = select((users.c.fullname +
...               ", " + addresses.c.email_address).
...                label('title')).\
...        where(
...           and_(
...               users.c.id == addresses.c.user_id,
...               users.c.name.between('m', 'z'),
...               or_(
...                  addresses.c.email_address.like('%@aol.com'),
...                  addresses.c.email_address.like('%@msn.com')
...               )
...           )
...        )
>>> conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', 'm', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

SQLAlchemy再一次为我们的语句计算出了FROM子句。实际上，它将基于所有其他位来确定FROM子句；columns子句、where子句，以及一些我们还没有涉及的其他元素，包括order by、group by和have。

使用的快捷方式 and_() 将多个链条连在一起 Select.where() 条款。上述内容也可以写成：

>>> s = select((users.c.fullname +
...               ", " + addresses.c.email_address).
...                label('title')).\
...        where(users.c.id == addresses.c.user_id).\
...        where(users.c.name.between('m', 'z')).\
...        where(
...               or_(
...                  addresses.c.email_address.like('%@aol.com'),
...                  addresses.c.email_address.like('%@msn.com')
...               )
...        )
>>> conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', 'm', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

我们可以建立一个 select() 通过连续的方法调用构造 method chaining .

使用文本SQL

最后一个例子真的变成了一小部分。从我们所理解的文本SQL表达式转换为以编程风格将组件组合在一起的python结构可能很困难。这就是为什么SQLAlchemy只允许您使用字符串的原因，在这些情况下，SQL已经是已知的，并且不需要语句支持动态特性。这个 text() construct用于组成一个文本语句，该语句被传递到数据库，但基本上没有更改。下面，我们创建一个 text() 对象并执行它：

>>> from sqlalchemy.sql import text
>>> s = text(
...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...         "FROM users, addresses "
...         "WHERE users.id = addresses.user_id "
...         "AND users.name BETWEEN :x AND :y "
...         "AND (addresses.email_address LIKE :e1 "
...             "OR addresses.email_address LIKE :e2)")
>>> conn.execute(s, {"x":"m", "y":"z", "e1":"%@aol.com", "e2":"%@msn.com"}).fetchall()
SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] ('m', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

上面，我们可以看到绑定参数是在 text() 使用命名的冒号格式；无论数据库后端如何，此格式都是一致的。为了发送参数的值，我们将它们传递到 Connection.execute() 方法作为附加参数。

指定绑定参数行为

这个 text() 构造支持使用 TextClause.bindparams() 方法：

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")

参数也可以显式类型化：：

stmt = stmt.bindparams(bindparam("x", type_=String), bindparam("y", type_=String))
result = conn.execute(stmt, {"x": "m", "y": "z"})

当类型需要Python端或数据类型提供的特殊SQL端处理时，需要为绑定参数键入。

参见

TextClause.bindparams() -完整方法说明

指定结果列行为

我们还可以使用 TextClause.columns() 方法；此方法可用于根据名称指定返回类型：

stmt = stmt.columns(id=Integer, name=String)

或者可以按位置传递完整的列表达式，可以是类型化的，也可以是非类型化的。在这种情况下，最好在文本SQL中显式列出列，因为列表达式与SQL的关联将按位置进行：

stmt = text("SELECT id, name FROM users")
stmt = stmt.columns(users.c.id, users.c.name)

当我们呼叫 TextClause.columns() 方法，我们得到一个 TextAsFrom 对象，它支持 TextAsFrom.c 以及其他“可选”操作：

j = stmt.join(addresses, stmt.c.id == addresses.c.user_id)

new_stmt = select(stmt.c.id, addresses.c.id).\
    select_from(j).where(stmt.c.name == 'x')

位置形式 TextClause.columns() 在将文本SQL与现有的核心或ORM模型关联时特别有用，因为我们可以直接使用列表达式，而不必担心与文本SQL中的结果列名称发生名称冲突或其他问题：

>>> stmt = text("SELECT users.id, addresses.id, users.id, "
...     "users.name, addresses.email_address AS email "
...     "FROM users JOIN addresses ON users.id=addresses.user_id "
...     "WHERE users.id = 1").columns(
...        users.c.id,
...        addresses.c.id,
...        addresses.c.user_id,
...        users.c.name,
...        addresses.c.email_address
...     )
>>> result = conn.execute(stmt)
SELECT users.id, addresses.id, users.id, users.name,
    addresses.email_address AS email
FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
[...] ()

上面，结果中有三列被命名为“id”，但是由于我们已经将这些列与列表达式按位置关联，所以当使用实际的列对象作为键提取结果列时，名称不是问题。取走 email_address 列应为：

>>> row = result.fetchone()
>>> row._mapping[addresses.c.email_address]
'jack@yahoo.com'

另一方面，如果我们使用一个字符串列键，通常的基于名称的匹配规则仍然适用，我们将得到一个不明确的列错误 id 价值：

>>> row._mapping["id"]
Traceback (most recent call last):
...
InvalidRequestError: Ambiguous column name 'id' in result set column descriptions

需要注意的是，当从结果集中访问列时， Column 对象可能看起来不寻常，实际上它是ORM使用的唯一系统，它透明地出现在 Query 对象；这样， TextClause.columns() 方法通常非常适用于要在ORM上下文中使用的文本语句。例子在 使用文本SQL 说明了一个简单用法。

1.1 新版功能: 这个 TextClause.columns() 方法现在接受将按位置与纯文本SQL结果集匹配的列表达式，从而在将表元数据或ORM模型与文本SQL匹配时，不需要在SQL语句中匹配列名，甚至不需要在SQL语句中具有唯一性。

参见

TextClause.columns() -完整方法说明

使用文本SQL - integrating ORM-level queries with text()

在更大的语句中使用text（）片段

text() 还可以用于生成SQL片段，这些片段可以在 select() 对象，接受 text() 对象作为其大多数生成器函数的参数。下面，我们结合了 text() 在一个 select() 对象。这个 select() 构造提供语句的“几何体”，以及 text() 构造提供此表单中的文本内容。我们可以建立一个声明，而无需参考任何预先确定的 Table 元数据：

>>> s = select(
...        text("users.fullname || ', ' || addresses.email_address AS title")
...     ).\
...         where(
...             and_(
...                 text("users.id = addresses.user_id"),
...                 text("users.name BETWEEN 'm' AND 'z'"),
...                 text(
...                     "(addresses.email_address LIKE :x "
...                     "OR addresses.email_address LIKE :y)")
...             )
...         ).select_from(text('users, addresses'))
>>> conn.execute(s, {"x": "%@aol.com", "y": "%@msn.com"}).fetchall()
SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] ('%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

而当 text() 的列列表中可以使用 select() 对象时，它在组成生成的SELECT时有一些限制，因为它不会在 SelectBase.selected_columns 集合，并将从 .c 子查询的集合。下一节将介绍 literal_column() 构造，它是将单个列名表示为SQL片段的更好选择。

使用更具体的文本 table() ， literal_column() 和 column()

我们也可以通过使用 column() ， literal_column() 和 table() 对于我们声明中的一些关键元素。使用这些构造，我们可以获得比使用 text() 直接地，因为它们向核心提供了关于如何使用它们存储的字符串的更多信息，但是仍然不需要进行完整的处理。 Table 基于元数据。下面，我们还指定了 String 两个键的数据类型 literal_column() 对象，以便使用特定于字符串的连接运算符。我们也使用 literal_column() 为了使用表限定表达式，例如 users.fullname ，将按原样呈现；使用 column() 表示可以引用的单个列名称：

>>> from sqlalchemy import select, and_, text, String
>>> from sqlalchemy.sql import table, literal_column
>>> s = select(
...    literal_column("users.fullname", String) +
...    ', ' +
...    literal_column("addresses.email_address").label("title")
... ).\
...    where(
...        and_(
...            literal_column("users.id") == literal_column("addresses.user_id"),
...            text("users.name BETWEEN 'm' AND 'z'"),
...            text(
...                "(addresses.email_address LIKE :x OR "
...                "addresses.email_address LIKE :y)")
...        )
...    ).select_from(table('users')).select_from(table('addresses'))

>>> conn.execute(s, {"x":"%@aol.com", "y":"%@msn.com"}).fetchall()
SELECT users.fullname || ? || addresses.email_address AS anon_1
FROM users, addresses
WHERE users.id = addresses.user_id
AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
[...] (', ', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, wendy@aol.com',)]

按标签排序或分组

有时我们希望使用字符串作为快捷方式的一个地方是，当我们的语句在诸如“order by”或“group by”子句之类的地方有一些要引用的带标签的列元素时；其他候选者包括“over”或“distinct”子句中的字段。如果我们有这样的标签 select() 构造，我们可以通过将字符串直接传递到 select.order_by() 或 select.group_by() 等等。这将引用命名标签，并防止表达式被呈现两次。解析为列的标签名称将完全呈现：

>>> from sqlalchemy import func
>>> stmt = select(
...         addresses.c.user_id,
...         func.count(addresses.c.id).label('num_addresses')).\
...         group_by("user_id").order_by("user_id", "num_addresses")

sql>>> conn.execute(stmt).fetchall()
SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses
[...] ()
[(1, 2), (2, 2)]

我们可以使用修饰符，比如 asc() 或 desc() 通过传递字符串名称：

>>> from sqlalchemy import func, desc
>>> stmt = select(
...         addresses.c.user_id,
...         func.count(addresses.c.id).label('num_addresses')).\
...         group_by("user_id").order_by("user_id", desc("num_addresses"))

sql>>> conn.execute(stmt).fetchall()
SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses DESC
[...] ()
[(1, 2), (2, 2)]

请注意，这里的字符串功能非常适合我们使用 ColumnElement.label() 方法创建一个特别命名的标签。在其他情况下，我们总是希望引用 ColumnElement 对象，这样表达式系统就可以为渲染做出最有效的选择。下面，我们将演示如何使用 ColumnElement 当我们希望按出现多次的列名排序时，可以消除歧义：

>>> u1a, u1b = users.alias(), users.alias()
>>> stmt = select(u1a, u1b).\
...             where(u1a.c.name > u1b.c.name).\
...             order_by(u1a.c.name)  # using "name" here would be ambiguous

sql>>> conn.execute(stmt).fetchall()
SELECT users_1.id, users_1.name, users_1.fullname, users_2.id AS id_1,
users_2.name AS name_1, users_2.fullname AS fullname_1
FROM users AS users_1, users AS users_2
WHERE users_1.name > users_2.name ORDER BY users_1.name
[...] ()
[(2, u'wendy', u'Wendy Williams', 1, u'jack', u'Jack Jones')]

使用别名和子查询

SQL中的别名对应于表或select语句的“重命名”版本，该版本在您说“select.”时出现。从sometable作为someothername”。这个 AS 为表创建新名称。别名是一个键构造，因为它们允许任何表或子查询被唯一的名称引用。对于表，这允许同一个表在FROM子句中多次命名。在select语句的情况下，它为由语句表示的列提供父名称，允许它们相对于此名称被引用。

在SQL炼金术中，任何 Table 或其他 FromClause 可以使用 FromClause.alias() 方法，生成 Alias 构造。 Alias 是一个 FromClause 对象，该对象引用 Column 对象通过ITS FromClause.c 集合，并且可以在任何后续SELECT语句的FROM子句中使用，方法是在该语句的列或WHERE子句中引用其列元素，或者直接或在联接中通过FROM子句中的显式放置。

例如，假设我们知道我们的用户 jack 有两个特定的电子邮件地址。我们如何根据这两个地址的组合来定位杰克？为了实现这一点，我们将使用 addresses 表，每个地址一次。我们创造两个 Alias 构造 addresses ，然后在 select() 构建：

>>> a1 = addresses.alias()
>>> a2 = addresses.alias()
>>> s = select(users).\
...        where(and_(
...            users.c.id == a1.c.user_id,
...            users.c.id == a2.c.user_id,
...            a1.c.email_address == 'jack@msn.com',
...            a2.c.email_address == 'jack@yahoo.com'
...        ))
>>> conn.execute(s).fetchall()
SELECT users.id, users.name, users.fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id
    AND users.id = addresses_2.user_id
    AND addresses_1.email_address = ?
    AND addresses_2.email_address = ?
[...] ('jack@msn.com', 'jack@yahoo.com')
[(1, u'jack', u'Jack Jones')]

请注意 Alias 构造生成了名称 addresses_1 和 addresses_2 在最终的SQL结果中。这些名称的生成由语句中构造的位置决定。如果我们只使用第二个 a2 别名，名字会显示为 addresses_1 . 名字的产生也是 确定性的 ，表示每次为特定方言呈现时，相同的sqlAlchemy语句构造将生成相同的SQL字符串。

因为在外部，我们使用 Alias 构造自己，我们不需要关心生成的名称。但是，为了调试的目的，可以通过将字符串名称传递给 FromClause.alias() 方法：

>>> a1 = addresses.alias('a1')

选择从扩展到 SelectBase 可以使用 SelectBase.subquery() 方法，生成 Subquery 构造；为了便于使用，还有一个 SelectBase.alias() 与同义的方法 SelectBase.subquery() .像 Alias ， Subquery 也是一个 FromClause 对象，它可以是任何封闭选择的一部分，使用与 Alias .

我们可以自己加入 users 桌子回到 select() 我们创造了 Subquery 整个陈述：

>>> address_subq = s.subquery()
>>> s = select(users.c.name).where(users.c.id == address_subq.c.id)
>>> conn.execute(s).fetchall()
SELECT users.name
FROM users,
    (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
        FROM users, addresses AS addresses_1, addresses AS addresses_2
        WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id
        AND addresses_1.email_address = ?
        AND addresses_2.email_address = ?) AS anon_1
WHERE users.id = anon_1.id
[...] ('jack@msn.com', 'jack@yahoo.com')
[(u'jack',)]

在 1.4 版更改: 增加了 Subquery 对象，并在From子句的“别名”和Select的命名子查询之间创建了更多的分隔。参见 select语句不再被隐式地视为FROM子句 .

使用连接

我们已经完成了构建任何select表达式的一半。选择的下一个基石是联接表达式。我们已经在示例中进行了连接，只需在columns子句或的where子句中放置两个表。 select() 构建。但是，如果我们想要创建一个真正的“join”或“outerjoin”构造，我们使用 FromClause.join() 和 FromClause.outerjoin() 方法，通常从联接中的左表访问：

>>> print(users.join(addresses))
users JOIN addresses ON users.id = addresses.user_id

警报阅读器将看到更多的惊喜；SQLAlchemy知道如何连接这两个表！联接的条件（如它所称）是根据 ForeignKey 我们放置在 addresses 本教程开头的表格方式。已经 join() 构造看起来是联接表的更好方法。

当然，您可以加入任何您想要的表达式，例如，如果我们想要加入所有在其电子邮件地址中使用与其用户名相同名称的用户：

>>> print(users.join(addresses,
...                 addresses.c.email_address.like(users.c.name + '%')
...             )
...  )
users JOIN addresses ON addresses.email_address LIKE users.name || :name_1

当我们创建一个 select() 构造时，sqlAlchemy查看我们提到的表，然后将它们放在语句的FROM子句中。然而，当我们使用join时，我们知道我们需要什么from子句，所以这里我们使用 Select.select_from() 方法：

>>> s = select(users.c.fullname).select_from(
...    users.join(addresses,
...             addresses.c.email_address.like(users.c.name + '%'))
...    )
sql>>> conn.execute(s).fetchall()
SELECT users.fullname
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
[...] ('%',)
[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]

这个 FromClause.outerjoin() 方法创建 LEFT OUTER JOIN 构造，使用方法与 FromClause.join() ：

>>> s = select(users.c.fullname).select_from(users.outerjoin(addresses))
>>> print(s)
SELECT users.fullname
    FROM users
    LEFT OUTER JOIN addresses ON users.id = addresses.user_id

这就是输出 outerjoin() 生成，除非，当然，在版本9之前，您一直在使用Oracle，并且您已经设置了引擎（将使用 OracleDialect ）要使用Oracle特定的SQL：

>>> from sqlalchemy.dialects.oracle import dialect as OracleDialect
>>> print(s.compile(dialect=OracleDialect(use_ansi=False)))
SELECT users.fullname
FROM users, addresses
WHERE users.id = addresses.user_id(+)

如果你不知道SQL是什么意思，别担心！甲骨文DBA的秘密部落不希望他们的黑魔法被发现；）。

参见

join()

outerjoin()

Join

通用表表达式（CTE）

现在每个主要数据库都支持通用表表达式，包括现代MySQL、MariaDB、SQLite、PostgreSQL、Oracle和mssqlserver。SQLAlchemy通过 CTE 对象，通常使用 Select.cte() A方法 Select 构建：

>>> users_cte = select(users.c.id, users.c.name).where(users.c.name == 'wendy').cte()
>>> stmt = select(addresses).where(addresses.c.user_id == users_cte.c.id).order_by(addresses.c.id)
>>> conn.execute(stmt).fetchall()
WITH anon_1 AS
(SELECT users.id AS id, users.name AS name
FROM users
WHERE users.name = ?)
 SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses, anon_1
WHERE addresses.user_id = anon_1.id ORDER BY addresses.id
[...] ('wendy',)
[(3, 2, 'www@www.org'), (4, 2, 'wendy@aol.com')]

CTE构造是一种很好的方法，可以提供一个语义上类似于使用子查询的行源，但其格式要简单得多，其中行源被整齐地隐藏在查询的顶部，在那里它可以在主语句中的任何位置引用，就像常规表一样。

当我们构造一个 CTE 对象，我们像使用语句中的任何其他表一样使用它。然而，它并没有作为子查询添加到FROM子句中，而是排在最前面，这有一个额外的好处，即不会引起意外的笛卡尔积。

当CTE的递归格式可用时 Select.cte.recursive 参数。我们通常需要一个递归的别名来连接我们自己。这种行动的一般形式是把原来的中央选举委员会联合起来反对自己。注意到我们的示例表并不适合使用此功能生成实际有用的查询，此表单如下所示：

>>> users_cte = select(users.c.id, users.c.name).cte(recursive=True)
>>> users_recursive = users_cte.alias()
>>> users_cte = users_cte.union(select(users.c.id, users.c.name).where(users.c.id > users_recursive.c.id))
>>> stmt = select(addresses).where(addresses.c.user_id == users_cte.c.id).order_by(addresses.c.id)
>>> conn.execute(stmt).fetchall()
WITH RECURSIVE anon_1(id, name) AS
(SELECT users.id AS id, users.name AS name
FROM users UNION SELECT users.id AS id, users.name AS name
FROM users, anon_1 AS anon_2
WHERE users.id > anon_2.id)
 SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses, anon_1
WHERE addresses.user_id = anon_1.id ORDER BY addresses.id
[...] ()
[(1, 1, 'jack@yahoo.com'), (2, 1, 'jack@msn.com'), (3, 2, 'www@www.org'), (4, 2, 'wendy@aol.com')]

其他一切

介绍了创建SQL表达式的概念。剩下的是相同主题的更多变体。现在我们将列出我们需要知道的其他重要事项。

绑定参数对象

在所有这些示例中，只要出现文本表达式，sqlAlchemy就忙于创建绑定参数。还可以使用自己的名称指定自己的绑定参数，并重复使用同一语句。这个 bindparam() 构造用于生成具有给定名称的绑定参数。虽然sqlAlchemy始终引用API端按名称绑定的参数，但数据库方言在执行时转换为适当的命名或位置样式，如此处所示，它转换为sqlite的位置样式：

>>> from sqlalchemy.sql import bindparam
>>> s = users.select().where(users.c.name == bindparam('username'))
sql>>> conn.execute(s, {"username": "wendy"}).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name = ?
[...] ('wendy',)
[(2, u'wendy', u'Wendy Williams')]

另一个重要方面 bindparam() 它可以被分配一个类型。绑定参数的类型将决定其在表达式中的行为，以及在发送到数据库之前如何处理绑定到该参数的数据：

>>> s = users.select().where(users.c.name.like(bindparam('username', type_=String) + text("'%'")))
sql>>> conn.execute(s, {"username": "wendy"}).fetchall()
SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name LIKE ? || '%'
[...] ('wendy',)
[(2, u'wendy', u'Wendy Williams')]

bindparam() 相同名称的构造也可以多次使用，其中在执行参数中只需要一个命名值：

>>> s = select(users, addresses).\
...     where(
...        or_(
...          users.c.name.like(
...                 bindparam('name', type_=String) + text("'%'")),
...          addresses.c.email_address.like(
...                 bindparam('name', type_=String) + text("'@%'"))
...        )
...     ).\
...     select_from(users.outerjoin(addresses)).\
...     order_by(addresses.c.id)
sql>>> conn.execute(s, {"name": "jack"}).fetchall()
SELECT users.id, users.name, users.fullname, addresses.id AS id_1,
    addresses.user_id, addresses.email_address
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
ORDER BY addresses.id
[...] ('jack', 'jack')
[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]

参见

bindparam()

功能

SQL函数是使用 func 关键字，它使用属性访问生成函数：

>>> from sqlalchemy.sql import func
>>> print(func.now())
now()

>>> print(func.concat('x', 'y'))
concat(:concat_1, :concat_2)

“生成”是指 any SQL函数是根据您选择的单词创建的：

>>> print(func.xyz_my_goofy_function())
xyz_my_goofy_function()

某些函数名由SQLAlchemy知道，允许应用特殊的行为规则。例如，有些函数是“ansi”函数，这意味着它们不会在后面添加括号，例如当前时间戳：

>>> print(func.current_timestamp())
CURRENT_TIMESTAMP

与任何其他列表达式一样，函数都有一个类型，该类型指示表达式的类型以及SQLAlchemy将如何解释从该表达式返回的结果列。用于派生自的任意函数名的默认类型 func 只是一个“null”数据类型。但是，为了使函数生成的列表达式具有特定于类型的运算符行为以及结果集行为（如日期和数字强制），可能需要显式指定类型：

stmt = select(func.date(some_table.c.date_string, type_=Date))

函数通常用于select语句的columns子句中，也可以标记为给定的类型。建议标记一个函数，以便根据字符串名称在结果行中确定结果的目标，并在需要进行结果集处理时（如Unicode转换和日期转换）为其指定类型。下面，我们使用结果函数 scalar() 只需读取第一行的第一列，然后关闭结果；在这种情况下，标签即使存在也不重要：

>>> conn.execute(
...     select(
...            func.max(addresses.c.email_address, type_=String).
...                label('maxemail')
...           )
...     ).scalar()
SELECT max(addresses.email_address) AS maxemail
FROM addresses
[...] ()
u'www@www.org'

PostgreSQL和Oracle等支持返回整个结果集的函数的数据库可以组装成可选的单元，这些单元可以在语句中使用。例如，数据库函数 calculate() 它接受参数 x 和 y ，并返回三列 q ， z 和 r ，我们可以使用“lexical”列对象以及绑定参数进行构造：

>>> from sqlalchemy.sql import column
>>> calculate = select(column('q'), column('z'), column('r')).\
...        select_from(
...             func.calculate(
...                    bindparam('x'),
...                    bindparam('y')
...                )
...             )
>>> calc = calculate.alias()
>>> print(select(users).where(users.c.id > calc.c.z))
SELECT users.id, users.name, users.fullname
FROM users, (SELECT q, z, r
FROM calculate(:x, :y)) AS anon_1
WHERE users.id > anon_1.z

如果我们想使用 calculate 语句两次使用不同的绑定参数， unique_params() 函数将为我们创建副本，并将绑定参数标记为“唯一”，以便隔离冲突的名称。请注意，我们还为可选对象制作了两个单独的别名：

>>> calc1 = calculate.alias('c1').unique_params(x=17, y=45)
>>> calc2 = calculate.alias('c2').unique_params(x=5, y=12)
>>> s = select(users).\
...         where(users.c.id.between(calc1.c.z, calc2.c.z))
>>> print(s)
SELECT users.id, users.name, users.fullname
FROM users,
    (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,
    (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2
WHERE users.id BETWEEN c1.z AND c2.z

>>> s.compile().params 
{u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17}

参见

func

窗口功能

任何 FunctionElement ，包括生成的函数 func ，可以转换为“window函数”，即over子句，使用 FunctionElement.over() 方法：

>>> s = select(
...         users.c.id,
...         func.row_number().over(order_by=users.c.name)
...     )
>>> print(s)
SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1
FROM users

FunctionElement.over() 还支持使用 over.rows 或 over.range 参数：：

>>> s = select(
...         users.c.id,
...         func.row_number().over(
...                 order_by=users.c.name,
...                 rows=(-2, None))
...     )
>>> print(s)
SELECT users.id, row_number() OVER
(ORDER BY users.name ROWS BETWEEN :param_1 PRECEDING AND UNBOUNDED FOLLOWING) AS anon_1
FROM users

over.rows 和 over.range 每个接受一个两元组，其中包含范围的负整数和正整数的组合，零表示“当前行”和 None 表示“无边界”。示例见 over() 更多细节。

1.1 新版功能: 支持窗口功能的“行”和“范围”规范

参见

over()

FunctionElement.over()

数据强制转换和类型强制

在SQL中，我们通常需要显式地指示元素的数据类型，或者需要在SQL语句中在一个数据类型和另一个数据类型之间进行转换。CAST SQL函数执行此操作。在SQL炼金术中， cast() 函数呈现SQL CAST关键字。它接受列表达式和数据类型对象作为参数：

>>> from sqlalchemy import cast
>>> s = select(cast(users.c.id, String))
>>> conn.execute(s).fetchall()
SELECT CAST(users.id AS VARCHAR) AS id
FROM users
[...] ()
[('1',), ('2',)]

这个 cast() 函数不仅在数据类型之间转换时使用，而且在数据库需要知道某些特定值应被视为表达式中特定数据类型的情况下也使用。

这个 cast() 函数还告诉SQLAlchemy本身表达式也应被视为特定类型。表达式的数据类型直接影响Python运算符对该对象的行为，例如 + 运算符可以指示整数加法或字符串串联，它还影响文本python值在传递到数据库之前的转换或处理方式，以及该表达式的结果值应如何转换或处理。

有时，出于上述所有原因，需要让SQLAlchemy知道表达式的数据类型，但不要在SQL端呈现转换表达式本身，因为它可能会干扰已经不使用它的SQL操作。对于这个相当常见的用例，还有另一个函数 type_coerce() 这与 cast() ，因为它将python表达式设置为具有特定的SQL数据库类型，但不呈现 CAST 数据库端的关键字或数据类型。 type_coerce() 在处理 JSON 数据类型，它通常与不同平台上面向字符串的数据类型有着复杂的关系，甚至可能不是显式的数据类型，比如在SQLite和MariaDB上。下面，我们使用 type_coerce() 要将python结构作为json字符串传递到mysql的一个json函数中，请执行以下操作：

>>> import json
>>> from sqlalchemy import JSON
>>> from sqlalchemy import type_coerce
>>> from sqlalchemy.dialects import mysql
>>> s = select(
... type_coerce(
...        {'some_key': {'foo': 'bar'}}, JSON
...    )['some_key']
... )
>>> print(s.compile(dialect=mysql.dialect()))
SELECT JSON_EXTRACT(%s, %s) AS anon_1

上面，mysql的 JSON_EXTRACT 调用了SQL函数，因为我们使用了 type_coerce() 表示我们的python字典应该被视为 JSON . Python __getitem__ 运算符， ['some_key'] 在这种情况下，结果变为可用，并允许 JSON_EXTRACT 路径表达式（未显示，但在本例中，它最终将 '$."some_key"' ）待渲染。

联合和其他集合操作

联合有两种形式，联合和联合所有，可通过模块级功能获得。 union() 和 union_all() ：

>>> from sqlalchemy.sql import union
>>> u = union(
...     addresses.select().
...             where(addresses.c.email_address == 'foo@bar.com'),
...    addresses.select().
...             where(addresses.c.email_address.like('%@yahoo.com')),
... ).order_by(addresses.c.email_address)

sql>>> conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = ?
UNION
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ? ORDER BY email_address
[...] ('foo@bar.com', '%@yahoo.com')
[(1, 1, u'jack@yahoo.com')]

尽管并非所有数据库都支持，但也可以使用 intersect() ， intersect_all() ， except_() 和 except_all() ：

>>> from sqlalchemy.sql import except_
>>> u = except_(
...    addresses.select().
...             where(addresses.c.email_address.like('%@%.com')),
...    addresses.select().
...             where(addresses.c.email_address.like('%@msn.com'))
... )

sql>>> conn.execute(u).fetchall()
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
[...] ('%@%.com', '%@msn.com')
[(1, 1, u'jack@yahoo.com'), (4, 2, u'wendy@aol.com')]

所谓的“复合”可选项的一个常见问题是，它们用括号嵌套。尤其是sqlite不喜欢以括号开头的语句。因此，在“化合物”中嵌套“化合物”时，通常需要应用 .subquery().select() 到最外层化合物的第一个元素，如果该元素也是化合物。例如，要在“except”中嵌套“union”和“select”，sqlite将希望将“union”声明为子查询：

>>> u = except_(
...    union(
...         addresses.select().
...             where(addresses.c.email_address.like('%@yahoo.com')),
...         addresses.select().
...             where(addresses.c.email_address.like('%@msn.com'))
...     ).subquery().select(),   # apply subquery here
...    addresses.select().where(addresses.c.email_address.like('%@msn.com'))
... )
sql>>> conn.execute(u).fetchall()
SELECT anon_1.id, anon_1.user_id, anon_1.email_address
FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
    addresses.email_address AS email_address
    FROM addresses
    WHERE addresses.email_address LIKE ?
    UNION
    SELECT addresses.id AS id,
        addresses.user_id AS user_id,
        addresses.email_address AS email_address
    FROM addresses
    WHERE addresses.email_address LIKE ?) AS anon_1
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
[...] ('%@yahoo.com', '%@msn.com', '%@msn.com')
[(1, 1, u'jack@yahoo.com')]

参见

union()

union_all()

intersect()

intersect_all()

except_()

except_all()

订购工会

联合和其他集合构造在排序结果时有一个特殊的情况。由于union由几个select语句组成，要对整个结果进行排序，通常要求order by子句引用列名，而不是特定的表。和前面的例子一样，我们使用 .order_by(addresses.c.email_address) 但sqlAlchemy不使用表名呈现了顺序。将order by应用于union的一种通用方法也指 CompoundSelect.selected_columns 集合以访问与从第一个select中选择的列同义的列表达式；sqlAlchemy编译器将确保呈现这些表达式时不使用表名：：

>>> u = union(
...     addresses.select().
...             where(addresses.c.email_address == 'foo@bar.com'),
...    addresses.select().
...             where(addresses.c.email_address.like('%@yahoo.com')),
... )
>>> u = u.order_by(u.selected_columns.email_address)
>>> print(u)
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = :email_address_1
UNION SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE :email_address_2 ORDER BY email_address

标量选择

标量选择是只返回一行一列的选择。然后它可以用作列表达式。标量选择通常是 correlated subquery ，它依赖于所附的select语句以获取至少一个FROM子句。

这个 select() 可以通过调用 SelectBase.scalar_subquery() 或 SelectBase.label() 方法：

>>> subq = select(func.count(addresses.c.id)).\
...             where(users.c.id == addresses.c.user_id).\
...             scalar_subquery()

上面的结构现在是 ScalarSelect 对象，它是原始对象周围的适配器 Select 对象；它参与 ColumnElement 表达式构造的族。我们可以将此构造与另一列中的任何其他列相同 select() ：

>>> conn.execute(select(users.c.name, subq)).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS anon_1
FROM users
[...] ()
[(u'jack', 2), (u'wendy', 2)]

要将非匿名列名应用于标量select，我们使用 SelectBase.label() 而是：

>>> subq = select(func.count(addresses.c.id)).\
...             where(users.c.id == addresses.c.user_id).\
...             label("address_count")
>>> conn.execute(select(users.c.name, subq)).fetchall()
SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS address_count
FROM users
[...] ()
[(u'jack', 2), (u'wendy', 2)]

参见

Select.scalar_subquery()

Select.label()

关联子查询

在示例中 标量选择 ，每个嵌入select的from子句不包含 users 表在其FROM子句中。这是因为SQLAlchemy自动 correlates 从对象嵌入到封闭查询的对象（如果存在），并且如果内部select语句仍然有自己的至少一个FROM子句。例如：

>>> stmt = select(addresses.c.user_id).\
...             where(addresses.c.user_id == users.c.id).\
...             where(addresses.c.email_address == 'jack@yahoo.com')
>>> enclosing_stmt = select(users.c.name).\
...             where(users.c.id == stmt.scalar_subquery())
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name
FROM users
WHERE users.id = (SELECT addresses.user_id
    FROM addresses
    WHERE addresses.user_id = users.id
    AND addresses.email_address = ?)
[...] ('jack@yahoo.com',)
[(u'jack',)]

自相关通常会达到预期的效果，但是它也可以被控制。例如，如果我们希望一个语句只与 addresses 桌子，但不是 users 表，即使两者都存在于封闭的select中，我们也使用 Select.correlate() 指定那些来自可能相关的子句的方法：

>>> stmt = select(users.c.id).\
...             where(users.c.id == addresses.c.user_id).\
...             where(users.c.name == 'jack').\
...             correlate(addresses)
>>> enclosing_stmt = select(
...         users.c.name, addresses.c.email_address).\
...     select_from(users.join(addresses)).\
...     where(users.c.id == stmt.scalar_subquery())
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name, addresses.email_address
 FROM users JOIN addresses ON users.id = addresses.user_id
 WHERE users.id = (SELECT users.id
 FROM users
 WHERE users.id = addresses.user_id AND users.name = ?)
 [...] ('jack',)
 
[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]

为了完全禁用语句的关联，我们可以通过 None 作为论据：

>>> stmt = select(users.c.id).\
...             where(users.c.name == 'wendy').\
...             correlate(None)
>>> enclosing_stmt = select(users.c.name).\
...     where(users.c.id == stmt.scalar_subquery())
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name
 FROM users
 WHERE users.id = (SELECT users.id
  FROM users
  WHERE users.name = ?)
[...] ('wendy',)
[(u'wendy',)]

我们还可以通过排除来控制相关性，使用 Select.correlate_except() 方法。例如，我们可以为 users 通过告诉表关联除 users ：

>>> stmt = select(users.c.id).\
...             where(users.c.id == addresses.c.user_id).\
...             where(users.c.name == 'jack').\
...             correlate_except(users)
>>> enclosing_stmt = select(
...         users.c.name, addresses.c.email_address).\
...     select_from(users.join(addresses)).\
...     where(users.c.id == stmt.scalar_subquery())
>>> conn.execute(enclosing_stmt).fetchall()
SELECT users.name, addresses.email_address
 FROM users JOIN addresses ON users.id = addresses.user_id
 WHERE users.id = (SELECT users.id
 FROM users
 WHERE users.id = addresses.user_id AND users.name = ?)
 [...] ('jack',)
 
[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]

横向相关

横向相关是SQL相关的一个特殊子类，它允许可选单元引用单个FROM子句中的另一个可选单元。这是一个非常特殊的用例，虽然它是SQL标准的一部分，但已知只有最新版本的PostgreSQL才支持它。

通常，如果select语句引用 table1 JOIN (some SELECT) AS subquery 在FROM子句中，右侧的子查询不能从左侧引用“table1”表达式；相关性只能引用完全包含此选择的另一个select的一部分表。横向关键字允许我们改变这种行为，允许如下表达式：

SELECT people.people_id, people.age, people.name
FROM people JOIN LATERAL (SELECT books.book_id AS book_id
FROM books WHERE books.owner_id = people.people_id)
AS book_subq ON true

在上面的位置，联接的右侧包含一个子查询，它不仅引用“books”表，还引用与联接左侧相关的“people”表。sqlacalchemy core使用 Select.lateral() 方法如下：

>>> from sqlalchemy import table, column, select, true
>>> people = table('people', column('people_id'), column('age'), column('name'))
>>> books = table('books', column('book_id'), column('owner_id'))
>>> subq = select(books.c.book_id).\
...      where(books.c.owner_id == people.c.people_id).lateral("book_subq")
>>> print(select(people).select_from(people.join(subq, true())))
SELECT people.people_id, people.age, people.name
FROM people JOIN LATERAL (SELECT books.book_id AS book_id
FROM books WHERE books.owner_id = people.people_id)
AS book_subq ON true

上面，我们可以看到 Select.lateral() 方法的作用与 Select.alias() 方法，包括可以指定可选名称。然而，构造是 Lateral 构造而不是 Alias 它提供了横向关键字以及允许从封闭语句的FROM子句内部进行关联的特殊说明。

这个 Select.lateral() 方法与 Select.correlate() 和 Select.correlate_except() 方法，但相关规则也适用于封闭语句的FROM子句中存在的任何其他表。默认情况下，与这些表的关联是“自动的”，如果将表指定为 Select.correlate() ，并且对于除指定给 Select.correlate_except() .

1.1 新版功能: 支持横向关键字和横向相关性。

参见

Lateral

Select.lateral()

排序、分组、限制、偏移…

排序是通过将列表达式传递给 SelectBase.order_by() 方法：

>>> stmt = select(users.c.name).order_by(users.c.name)
>>> conn.execute(stmt).fetchall()
SELECT users.name
FROM users ORDER BY users.name
[...] ()
[(u'jack',), (u'wendy',)]

升序或降序可以使用 ColumnElement.asc() 和 ColumnElement.desc() 修饰语：

>>> stmt = select(users.c.name).order_by(users.c.name.desc())
>>> conn.execute(stmt).fetchall()
SELECT users.name
FROM users ORDER BY users.name DESC
[...] ()
[(u'wendy',), (u'jack',)]

分组是指group by子句，通常与聚合函数一起使用，以建立要聚合的行组。这是通过 SelectBase.group_by() 方法：

>>> stmt = select(users.c.name, func.count(addresses.c.id)).\
...             select_from(users.join(addresses)).\
...             group_by(users.c.name)
>>> conn.execute(stmt).fetchall()
SELECT users.name, count(addresses.id) AS count_1
FROM users JOIN addresses
    ON users.id = addresses.user_id
GROUP BY users.name
[...] ()
[(u'jack', 2), (u'wendy', 2)]

另请参阅 按标签排序或分组 用于按字符串列名称排序或分组的重要技术。

在应用group by之后，可以使用having对聚合值的结果进行筛选。它可以通过 Select.having() 方法：

>>> stmt = select(users.c.name, func.count(addresses.c.id)).\
...             select_from(users.join(addresses)).\
...             group_by(users.c.name).\
...             having(func.length(users.c.name) > 4)
>>> conn.execute(stmt).fetchall()
SELECT users.name, count(addresses.id) AS count_1
FROM users JOIN addresses
    ON users.id = addresses.user_id
GROUP BY users.name
HAVING length(users.name) > ?
[...] (4,)
[(u'wendy', 2)]

处理组合select语句中重复项的一个常见系统是distinct修饰符。可以使用 Select.distinct() 方法：

>>> stmt = select(users.c.name).\
...             where(addresses.c.email_address.
...                    contains(users.c.name)).\
...             distinct()
>>> conn.execute(stmt).fetchall()
SELECT DISTINCT users.name
FROM users, addresses
WHERE (addresses.email_address LIKE '%' || users.name || '%')
[...] ()
[(u'jack',), (u'wendy',)]

大多数数据库后端支持一个限制返回行数的系统，大多数后端还具有在给定“偏移量”之后开始返回行的方法。虽然postgresql、mysql、sqlite等常见的后端支持限制和偏移关键字，但其他后端需要引用“window函数”和row id等更为深奥的特性，才能达到同样的效果。这个 Select.limit() 和 Select.offset() 方法提供了对当前后端方法的简单抽象：

>>> stmt = select(users.c.name, addresses.c.email_address).\
...             select_from(users.join(addresses)).\
...             limit(1).offset(1)
>>> conn.execute(stmt).fetchall()
SELECT users.name, addresses.email_address
FROM users JOIN addresses ON users.id = addresses.user_id
 LIMIT ? OFFSET ?
[...] (1, 1)
[(u'jack', u'jack@msn.com')]

插入、更新和删除

我们见过 TableClause.insert() 在本教程前面演示。在哪里？ TableClause.insert() 生成insert， TableClause.update() 方法生成更新。这两个构造都具有一个方法 ValuesBase.values() 它指定语句的值或set子句。

这个 ValuesBase.values() 方法将任何列表达式作为值容纳：

>>> stmt = users.update().\
...             values(fullname="Fullname: " + users.c.name)
>>> conn.execute(stmt)
UPDATE users SET fullname=(? || users.name)
[...] ('Fullname: ',)
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

使用时 TableClause.insert() 或 TableClause.update() 在“执行多个”上下文中，我们可能还需要指定命名绑定参数，我们可以在参数列表中引用这些参数。这两个构造将自动为发送到的词典中传递的任何列名称生成绑定的占位符。 Connection.execute() 在执行时。但是，如果我们希望将显式目标命名参数与组合表达式一起使用，则需要使用 bindparam() 构建。使用时 bindparam() 具有 TableClause.insert() 或 TableClause.update() ，表列本身的名称是为“自动”生成绑定名称而保留的。我们可以将隐式可用绑定名和显式命名参数的用法结合起来，如下例所示：

>>> stmt = users.insert().\
...         values(name=bindparam('_name') + " .. name")
>>> conn.execute(stmt, [
...        {'id':4, '_name':'name1'},
...        {'id':5, '_name':'name2'},
...        {'id':6, '_name':'name3'},
...     ])
INSERT INTO users (id, name) VALUES (?, (? || ?))
[...] ((4, 'name1', ' .. name'), (5, 'name2', ' .. name'), (6, 'name3', ' .. name'))
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

更新语句是使用 TableClause.update() 构建。这与insert非常相似，除了可以指定附加的where子句之外：

>>> stmt = users.update().\
...             where(users.c.name == 'jack').\
...             values(name='ed')

>>> conn.execute(stmt)
UPDATE users SET name=? WHERE users.name = ?
[...] ('ed', 'jack')
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

使用时 TableClause.update() 在“ExecuteMany”上下文中，我们可能还希望在WHERE子句中使用显式命名的绑定参数。再一次， bindparam() 是否使用构造来实现：

>>> stmt = users.update().\
...             where(users.c.name == bindparam('oldname')).\
...             values(name=bindparam('newname'))
>>> conn.execute(stmt, [
...     {'oldname':'jack', 'newname':'ed'},
...     {'oldname':'wendy', 'newname':'mary'},
...     {'oldname':'jim', 'newname':'jake'},
...     ])
UPDATE users SET name=? WHERE users.name = ?
[...] (('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

相关更新

关联更新允许您使用来自另一个表或同一个表的选择更新表；SELECT语句作为标量子查询传递，使用 Select.scalar_subquery() ：

>>> stmt = select(addresses.c.email_address).\
...             where(addresses.c.user_id == users.c.id).\
...             limit(1)
>>> conn.execute(users.update().values(fullname=stmt.scalar_subquery()))
UPDATE users SET fullname=(SELECT addresses.email_address
    FROM addresses
    WHERE addresses.user_id = users.id
    LIMIT ? OFFSET ?)
[...] (1, 0)
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

多个表更新

PostgreSQL、Microsoft SQL Server和MySQL都支持引用多个表的更新语句。对于pg和mssql，这是“更新自”语法，一次更新一个表，但可以在附加的“自”子句中引用附加表，然后可以在where子句中直接引用该附加表。在MySQL中，可以将多个表嵌入一个用逗号分隔的UPDATE语句中。圣卢西亚 update() 构造通过在WHERE子句中指定多个表隐式支持这两种模式：

stmt = users.update().\
        values(name='ed wood').\
        where(users.c.id == addresses.c.id).\
        where(addresses.c.email_address.startswith('ed%'))
conn.execute(stmt)

上述语句产生的SQL将呈现为：

UPDATE users SET name=:name FROM addresses
WHERE users.id = addresses.id AND
addresses.email_address LIKE :email_address_1 || '%'

使用mysql时，可以使用传递给 Update.values() ：：

stmt = users.update().\
        values({
            users.c.name:'ed wood',
            addresses.c.email_address:'ed.wood@foo.com'
        }).\
        where(users.c.id == addresses.c.id).\
        where(addresses.c.email_address.startswith('ed%'))

在set子句中显式引用表：

UPDATE users, addresses SET addresses.email_address=%s,
        users.name=%s WHERE users.id = addresses.id
        AND addresses.email_address LIKE concat(%s, '%')

当构造用于不支持的数据库时，编译器将 NotImplementedError . 为了方便起见，当一个语句被打印为一个没有方言规范的字符串时，将调用“字符串SQL”编译器，它提供构造的非工作SQL表示。

参数顺序更新

的默认行为 update() 在呈现集合子句时构造是使用原始列中给定的列顺序呈现它们 Table 对象。这是一个重要的行为，因为它意味着每次呈现具有特定列的特定更新语句时都将呈现相同的结果，这对依赖于语句形式的查询缓存系统（客户端或服务器端）有影响。因为参数本身被传递到 Update.values() 方法作为python字典键，没有其他固定顺序可用。

但是，在某些情况下，UPDATE语句的SET子句中呈现的参数顺序可能需要显式声明。这方面的主要示例是使用MySQL并根据其他列值的更新提供列值。以下语句的最终结果：

UPDATE some_table SET x = y + 10, y = 20

将产生与以下结果不同的结果：

UPDATE some_table SET y = 20, x = y + 10

这是因为在MySQL中，单独的set子句是以每值为基础进行完全计算的，而不是以每行为基础进行完全计算的，并且在计算每个set子句时，行中嵌入的值都在变化。

为了适应这个特定的用例， update.ordered_values() 方法。使用此方法时，我们提供 series of 2-tuples 作为方法的参数：

stmt = some_table.update().\
    ordered_values((some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10))

2元组的系列基本上与Python字典的结构相同，只是它显式地建议了特定的顺序。使用上面的形式，我们确信“y”列的SET子句将首先呈现，然后是“x”列的SET子句。

在 1.4 版更改: 增加了 Update.ordered_values() 方法取代 update.preserve_parameter_order 将在SQLAlchemy 2.0中删除的标志。

参见

在重复密钥更新时插入…（向上插入） -MySQL的背景 ON DUPLICATE KEY UPDATE 以及如何支持参数排序。

删除

最后，删除。使用 TableClause.delete() 构建：

>>> conn.execute(addresses.delete())
DELETE FROM addresses
[...] ()
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

>>> conn.execute(users.delete().where(users.c.name > 'm'))
DELETE FROM users WHERE users.name > ?
[...] ('m',)
COMMIT
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x...>

多个表删除

1.2 新版功能.

PostgreSQL、Microsoft SQL Server和MySQL都支持引用WHERE条件中多个表的DELETE语句。对于pg和mysql，这是“delete-using”语法，对于SQL Server，这是一个“delete-from”，表示多个表。圣卢西亚 delete() 构造通过在WHERE子句中指定多个表隐式支持这两种模式：

stmt = users.delete().\
        where(users.c.id == addresses.c.id).\
        where(addresses.c.email_address.startswith('ed%'))
conn.execute(stmt)

在PostgreSQL后端，上述语句生成的SQL将呈现为：

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

当构造用于不支持的数据库时，编译器将 NotImplementedError . 为了方便起见，当一个语句被打印为一个没有方言规范的字符串时，将调用“字符串SQL”编译器，它提供构造的非工作SQL表示。

匹配行计数

两个 TableClause.update() 和 TableClause.delete() 与 匹配行计数 . 这是一个数字，指示由WHERE子句匹配的行数。注意，通过“匹配”，这包括没有实际发生更新的行。该值可用为 CursorResult.rowcount ：

>>> result = conn.execute(users.delete())
DELETE FROM users
[...] ()
COMMIT
>>> result.rowcount
1

进一步参考

表达式语言引用： SQL语句和表达式API

数据库元数据引用： 用元数据描述数据库

发动机参考： 引擎配置

连接参考： 使用引擎和接头

类型引用： 列和数据类型