SQL表达式语言教程¶

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

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

尽管ORM和表达语言的使用模式有重叠,但相似性比最初可能出现的更为肤浅。一种方法是从用户定义的角度来处理数据的结构和内容。 domain model 它透明地持久化并从其底层存储模型中刷新。另一种方法是从文本模式和SQL表达式表示的角度进行处理,这些表达式显式地组合成数据库单独使用的消息。

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

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

版本检查

快速检查以确认我们至少在 版本1.3 SQLAlchemy:

>>> import sqlalchemy
>>> sqlalchemy.__version__  
1.3.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 以及在所有情况下 ColumnTable . 收藏 Table 对象及其关联的子对象称为 数据库元数据 . 在本教程中,我们将明确列出 Table 但请注意,sa还可以“导入”整个 Table 从现有数据库自动生成的对象(此过程称为 表格反射

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

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

>>> addresses = Table('addresses', metadata,
...   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.create_all(engine)
SE...

注解

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

Column('name', String(50))

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

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

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

一个完整的,万无一失的 Table 因此::

users = Table('users', metadata,
   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 正在执行。在本教程中,我们通常将重点放在执行SQL构造的最显式方法上,稍后将讨论一些“捷径”方法。这个 engine 我们创建的对象是一个数据库连接库,能够向数据库发出SQL。为了获得连接,我们使用 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() ?作为一种SQLAlchemy Connection 对象引用DBAPI连接,结果称为 ResultProxy 对象,类似于DBAPI光标对象。在插入的情况下,我们可以从中获取重要的信息,例如使用 ResultProxy.inserted_primary_key

>>> result.inserted_primary_key
[1]

价值 1 是由sqlite自动生成的,但这只是因为我们没有指定 id 我们的专栏 Insert 语句;否则,将使用显式值。在这两种情况下,SQLAlchemy总是知道如何获取新生成的主键值,即使在不同的数据库中生成它们的方法不同;每个数据库的 Dialect 知道确定正确值(或值)所需的具体步骤;注意 ResultProxy.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.result.ResultProxy 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.result.ResultProxy 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子句。返回的结果再次为 ResultProxy 对象,其行为与DBAPI光标非常类似,包括 fetchone()fetchall() . 从中获取行的最简单方法是迭代:

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

上面,我们看到打印每一行都会产生一个简单的tuple类型的结果。在访问每一行中的数据时,我们有更多的选项。一种非常常见的方法是通过字典访问,使用列的字符串名称:

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

整数索引也可以工作:

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

但另一种方法,其效用将在以后变得明显,是使用 Column 直接作为键的对象:

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

丢弃前,应显式关闭保留挂起行的结果集。当光标和连接资源被 ResultProxy 当对象被垃圾收集时,将分别关闭并返回到连接池,最好将其显式化,因为有些数据库API非常挑剔这样的事情:

>>> result.close()

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

>>> s = select([users.c.name, users.c.fullname])
sql>>> result = conn.execute(s)
>>> 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)
(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)
(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 我们的声明,我们的结果被管理下来,以便 usersaddresses 行是有意义的。但让我们看看这个表达?它只在两个不同的 Column 对象。很明显有什么事发生了。说 1 == 1 生产 True1 == 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'

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

算子

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

>>> 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 很重要!如果我们使用 + 有两个基于字符串的列(回想一下,我们把类型 IntegerString 关于我们 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')

1.2.0b3 新版功能: 增加了 Operators.bool_op() 方法。

操作符自定义

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

连词

我们想炫耀一下我们内部的一些运营商 select() 构造。但是我们需要把它们放在一起,所以我们先介绍一些连词。连词是把事物组合在一起的一些小词,比如and和or。我们也会发现不是。 and_()or_()not_() 可以从SQLAlchemy提供的相应函数中工作(注意,我们还引入了 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”开头,我们还将生成一个包含他们的全名和电子邮件地址的列。我们将在此语句中添加两个新构造, between()label() . between() 生成一个between子句,和 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_() 将多个链条连在一起 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)")
sql>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()
[(u'Wendy Williams, wendy@aol.com',)]

上面,我们可以看到绑定参数是在 text() 使用命名的冒号格式;无论数据库后端如何,此格式都是一致的。为了发送参数的值,我们将它们传递到 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
...     )
sql>>> result = conn.execute(stmt)

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

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

另一方面,如果我们使用一个字符串列键,那么基于名称的匹配的常规规则仍然适用,并且我们会得到一个不明确的列错误。 id 价值:

>>> row["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'))
sql>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall()
[(u'Wendy Williams, wendy@aol.com',)]

在 1.0.0 版更改: 这个 select() 当将字符串SQL片段强制为 text()text() 应明确使用。见 将完整的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'))

sql>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall()
[(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()
[(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()
[(1, 2), (2, 2)]

请注意,这里的字符串功能非常适合我们使用 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()
[(2, u'wendy', u'Wendy Williams', 1, u'jack', u'Jack Jones')]

使用别名

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

在SQLAlchemy中,任何 Tableselect() 构造或其他可选择项可以使用 FromClause.alias() 方法,生成 Alias 构建。例如,假设我们知道我们的用户 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'
...        ))
sql>>> conn.execute(s).fetchall()
[(1, u'jack', u'Jack Jones')]

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

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

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

别名当然可以用于任何可以选择的内容,包括select语句本身。我们可以自己加入 users 表格回到 select() 我们是通过为整个语句创建别名来创建的。这个 correlate(None) 指令是为了避免SQLAlchemy试图“关联”内部 users 外桌:

>>> a1 = s.correlate(None).alias()
>>> s = select([users.c.name]).where(users.c.id == a1.c.id)
sql>>> conn.execute(s).fetchall()
[(u'jack',)]

使用连接

我们已经完成了构建任何select表达式的一半。选择的下一个基石是联接表达式。我们已经在示例中进行了连接,只需在columns子句或的where子句中放置两个表。 select() 构建。但是,如果我们想要创建一个真正的“join”或“outerjoin”构造,我们使用 join()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_from() 方法:

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

这个 outerjoin() 方法创建 LEFT OUTER JOIN 构造,使用方法与 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的秘密部落不希望他们的黑魔法被发现;)。

其他一切

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

绑定参数对象

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

>>> from sqlalchemy.sql import bindparam
>>> s = users.select(users.c.name == bindparam('username'))
sql>>> conn.execute(s, username='wendy').fetchall()
[(2, u'wendy', u'Wendy Williams')]

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

>>> s = users.select(users.c.name.like(bindparam('username', type_=String) + text("'%'")))
sql>>> conn.execute(s, username='wendy').fetchall()
[(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()
[(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

函数通常用于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() 它接受参数 xy ,并返回三列 qzr ,我们可以使用“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() 还支持使用 expression.over.rowsexpression.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

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

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

联合和其他集合操作

联合有两种形式,联合和联合所有,可通过模块级功能获得。 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()
[(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()
[(1, 1, u'jack@yahoo.com'), (4, 2, u'wendy@aol.com')]

所谓的“复合”可选项的一个常见问题是,它们用括号嵌套。尤其是sqlite不喜欢以括号开头的语句。因此,在“化合物”中嵌套“化合物”时,通常需要应用 .alias().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'))
...     ).alias().select(),   # apply subquery here
...    addresses.select(addresses.c.email_address.like('%@msn.com'))
... )
sql>>> conn.execute(u).fetchall()
[(1, 1, u'jack@yahoo.com')]

标量选择

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

这个 select() 可以通过调用 as_scalar()label() 方法:

>>> stmt = select([func.count(addresses.c.id)]).\
...             where(users.c.id == addresses.c.user_id).\
...             as_scalar()

上面的结构现在是 ScalarSelect 对象,并且不再是 FromClause 层次结构;而是在 ColumnElement 表达式构造的族。我们可以将此构造与另一列中的任何其他列相同 select()

>>> conn.execute(select([users.c.name, stmt])).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() 而是:

>>> stmt = select([func.count(addresses.c.id)]).\
...             where(users.c.id == addresses.c.user_id).\
...             label("address_count")
>>> conn.execute(select([users.c.name, stmt])).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的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)
>>> 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中,我们也使用 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)
>>> 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)
>>> 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)
>>> 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”表。SQLAlchemy 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 新版功能: 支持横向关键字和横向相关性。

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

排序是通过将列表达式传递给 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',)]

升序或降序可以使用 asc()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子句,通常与聚合函数一起使用,以建立要聚合的行组。这是通过 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对聚合值的结果进行筛选。它可以通过 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等更为深奥的特性,才能达到同样的效果。这个 limit()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')]

插入、更新和删除

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

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

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

使用时 insert()update() 在“执行多个”上下文中,我们可能还需要指定命名绑定参数,我们可以在参数列表中引用这些参数。这两个构造将自动为发送到的词典中传递的任何列名称生成绑定的占位符。 execute() 在执行时。但是,如果我们希望将显式目标命名参数与组合表达式一起使用,则需要使用 bindparam() 构建。使用时 bindparam() 具有 insert()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.result.ResultProxy object at 0x...>

更新语句是使用 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.result.ResultProxy object at 0x...>

使用时 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.result.ResultProxy object at 0x...>

相关更新

相关更新允许您使用从另一个表或同一个表中选择的内容更新表:

>>> stmt = select([addresses.c.email_address]).\
...             where(addresses.c.user_id == users.c.id).\
...             limit(1)
>>> conn.execute(users.update().values(fullname=stmt))
UPDATE users SET fullname=(SELECT addresses.email_address FROM addresses WHERE addresses.user_id = users.id LIMIT ? OFFSET ?) (1, 0) COMMIT
<sqlalchemy.engine.result.ResultProxy 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子句时,行中嵌入的值都在变化。

为了适应这个特定的用例, preserve_parameter_order 可以使用标志。使用此标志时,我们提供 Python list of 2-tuples 作为 Update.values() 方法:

stmt = some_table.update(preserve_parameter_order=True).\
    values([(some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)])

2个元组的列表与Python字典的结构基本相同,只是顺序不同。使用上面的表单,我们确信“Y”列的set子句将首先呈现,然后是“X”列的set子句。

1.0.10 新版功能: 添加了对更新参数显式排序的支持,使用 preserve_parameter_order 标志。

参见

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

删除

最后,删除。使用 delete() 构建:

>>> conn.execute(addresses.delete())
DELETE FROM addresses () COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x...> >>> conn.execute(users.delete().where(users.c.name > 'm'))
DELETE FROM users WHERE users.name > ? ('m',) COMMIT
<sqlalchemy.engine.result.ResultProxy 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表示。

匹配行计数

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

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

进一步参考

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

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

引擎参考: 引擎配置

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

类型引用: 列和数据类型