版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

SQL表达式语言教程

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

表达式语言与对象关系映射器(Object Relational Mapper)形成对比,对象关系映射器是一种在表达式语言之上构建的独特的API。而在Object Relational Tutorial中引入的ORM呈现高层次和抽象的使用模式,这本身就是表达式语言的应用用法的一个例子,表达式语言提供了一个表示基元关系数据库的构造直接没有意见。

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

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

以下教程是doctest格式,这意味着每个>>>本教程没有先决条件。

版本检查

A quick check to verify that we are on at least version 1.1 of SQLAlchemy:

>>> import sqlalchemy
>>> sqlalchemy.__version__  # doctest: +SKIP
1.1.0

连接¶ T0>

对于本教程,我们将使用仅在内存中的SQLite数据库。这是测试事情的简单方法,无需在任何地方定义实际的数据库。要连接,我们使用create_engine()

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

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

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

The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the database, which is then used to emit the SQL.

也可以看看

Database Urls - 包含连接到多种数据库的create_engine()的示例,并链接到更多信息。

定义和创建表

SQL表达式语言在大多数情况下针对表列构造表达式。在SQLAlchemy中,列通常由一个名为Column的对象表示,在所有情况下,一个Column都与一个Table关联。A collection of Table objects and their associated child objects is referred to as database metadata. In this tutorial we will explicitly lay out several Table objects, but note that SA can also “import” whole sets of Table objects automatically from an existing database (this process is called table reflection).

我们使用Table结构来定义我们的表,这些表都在名为MetaData的目录中,类似于常规的SQL CREATE TABLE语句。我们将创建两个表,其中一个表示应用程序中的“用户”,另一个表示“users”表中每行的零个或多个“电子邮件地址”:

>>> 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对象,以及如何从现有数据库自动创建它们的描述在Describing Databases with MetaData中描述。

接下来,为了告诉MetaData,我们实际上希望在SQLite数据库中创建真实的表格选择,我们使用create_all(),将engine这将在创建之前检查每个表的存在,因此多次调用是安全的:

sql>>> metadata.create_all(engine)
SE...

注意

熟悉CREATE TABLE语法的用户可能会注意到VARCHAR列的生成没有长度;在SQLite和Postgresql上,这是一个有效的数据类型,但在其他情况下,这是不允许的。因此,如果在其中一个数据库上运行本教程,并且希望使用SQLAlchemy发出CREATE TABLE,则可以为String类型提供“length”,如下所示:

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)

A full, foolproof Table is therefore:

users = Table('users', metadata,
   Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
   Column('name', String(50)),
   Column('fullname', String(50)),
   Column('password', String(12))
)

我们单独包含这个更详细的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中的实际数据没有被渲染到字符串中。相反,我们有命名的绑定参数。事实证明,我们的数据被存储在我们的Insert结构中,但是通常只有当语句被实际执行时才会出现。由于数据由字面值组成,SQLAlchemy自动为它们生成绑定参数。我们现在可以通过查看声明的编译形式来查看这些数据:

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

执行¶ T0>

The interesting part of an Insert is executing it. 在本教程中,我们将主要关注执行SQL构造的最明确的方法,稍后再介绍一些“快捷方式”。我们创建的engine对象是一个能够向数据库发出SQL的数据库连接的存储库。要获取连接,我们使用connect()方法:

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

The Connection object represents an actively checked out DBAPI connection resource. 让我们把Insert对象,看看会发生什么:

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

所以INSERT语句现在被发布到数据库。尽管我们在输出中获得了位置“qmark”绑定参数,而不是“named”绑定参数。怎么来的 ?因为执行时,Connection使用SQLite 方言来帮助生成语句;当我们使用str()函数时,语句不知道这个方言,并且回到使用命名参数的默认值上。我们可以手动查看,如下所示:

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

我们调用execute()时得到的result变量​​呢?由于SQLAlchemy Connection对象引用了一个DBAPI连接,因此称为ResultProxy对象的结果类似于DBAPI游标对象。对于INSERT,我们可以从中得到重要的信息,比如从我们的语句中使用ResultProxy.inserted_primary_key生成的主键值:

>>> result.inserted_primary_key
[1]

SQLite自动生成1的值,但仅仅是因为我们没有在我们的Insert语句中指定id否则,我们明确的价值将被使用。In either case, SQLAlchemy always knows how to get at a newly generated primary key value, even though the method of generating them is different across different databases; each database’s Dialect knows the specific steps needed to determine the correct value (or values; note that ResultProxy.inserted_primary_key returns a list so that it supports composite primary keys). 这里的方法包括使用cursor.lastrowid,从数据库特定的函数中选择,使用INSERT..RETURNING语法;这一切都是透明的。

执行多个语句

我们上面的插入示例被有意识地展示了一些表达式语言结构的各种行为。通常情况下,一个Insert语句通常是针对发送给Connection上的execute()方法的参数进行编译的,所以没有必要在Insert中使用values关键字。让我们再次创建一个通用的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行自动生成主键标识符。

当执行多组参数时,每个字典必须有相同的组键;即在某些字典中你不能拥有比其他字典更少的密钥。这是因为Insert语句是针对列表中的第一个字典进行编译的,假定所有后续的参数字典都与该语句兼容。

The “executemany” style of invocation is available for each of the insert(), update() and delete() constructs.

选择¶ T0>

我们从插入开始,以便我们的测试数据库中有一些数据。数据中更有趣的部分是选择它!稍后我们将介绍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子句。The result returned is again a ResultProxy object, which acts much like a DBAPI cursor, including methods such as fetchone() and fetchall(). 从它获取行的最简单的方法是迭代:

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

上面,我们看到打印每一行产生一个简单的元组结果。我们有更多的选择来访问每一行中的数据。一种非常常见的方式是通过字典访问,使用字符串名称的列:

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

剩余待处理行的结果集应该在丢弃之前显式关闭。While the cursor and connection resources referenced by the ResultProxy will be respectively closed and returned to the connection pool when the object is garbage collected, it’s better to make it explicit as some database APIs are very picky about such things:

>>> result.close()

如果我们希望更仔细地控制放置在select的COLUMNS子句中的列,我们引用来自Table的单个Column对象。它们可以作为Table对象的c属性的命名属性使用:

>>> 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列”部分和“FROM表”部分,但我们的select()结构只包含一个包含列的列表。这个怎么用 ?让我们尝试将两个表放入我们的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')

It placed both tables into the FROM clause. 而且,它真的是一团糟。那些熟悉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')

So that looks a lot better, we added an expression to our select() which had the effect of adding WHERE users.id = addresses.user_id to our statement, and our results were managed down so that the join of users and addresses rows made sense. 但让我们看看这个表达?它只是在两个不同的Column对象之间使用Python相等运算符。应该清楚的是,有些事情已经到来。Saying 1 == 1 produces True, and 1 == 2 produces False, not a WHERE clause. 所以让我们看看这个表达式到底在做什么:

>>> 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。到目前为止,我们可以看到,我们所处理的一切最终都是同一类型的对象。SQLAlchemy将所有这些表达式的基类称为ColumnElement

¶ T0>

由于我们偶然发现了SQLAlchemy的操作符范例,让我们来看看它的一些功能。我们已经看到如何将两列相互等同:

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

如果我们使用字面值(字面意思,而不是SQLAlchemy子句对象),我们得到一个绑定参数:

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

The 7 literal is embedded the resulting ColumnElement; we can use the same trick we did with the Insert object to see it:

>>> (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的类型很重要!如果我们在两个基于字符串的列上使用+(回想一下,我们在Column对象中放置了IntegerString

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

其中||是大多数数据库上使用的字符串连接运算符。但不是全部。MySQL用户,不要害怕:

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

以上说明了连接到MySQL数据库的Engine生成的SQL; ||运算符现在编译为MySQL的concat()函数。

如果遇到真正无法使用的操作符,则始终可以使用ColumnOperators.op()方法;这会产生你需要的任何操作符:

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

这个函数也可以用来使按位运算符明确。例如:

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

somecolumn中的值的按位与。

操作员自定义

While ColumnOperators.op() is handy to get at a custom operator in a hurry, the Core supports fundamental customization and extension of the operator system at the type level. 现有操作符的行为可以在每个类型的基础上进行修改,并且可以定义新的操作,这些操作可用于属于该特定类型的所有列表达式。有关说明,请参阅Redefining and Creating New Operators部分。

连词¶ T0>

我们想在select()结构中展示一些我们的运算符。但是我们需要把它们再加一点,所以我们先来介绍一些连词。连词是像AND和OR这样的小词,把事情放在一起。我们也会碰到NOT。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

你也可以使用重复的按位AND,OR和NOT运算符,但是由于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”之间的一个字母开头,我们还会生成一个包含全名的列他们的电邮地址。()和label()之间的between()between() produces a BETWEEN clause, and label() is used in a column expression to produce labels using the AS keyword; it’s recommended when selecting from expressions that otherwise would not have a name:

>>> 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子句; column子句,where子句,还有一些我们还没有涉及的元素,包括ORDER BY,GROUP BY和HAVING。

使用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()结构用于组成一个大部分没有改变的传递给数据库的文本语句。下面,我们创建一个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", String), bindparam("y", String))
result = conn.execute(stmt, {"x": "m", "y": "z"})

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

也可以看看

TextClause.bindparams() - full method description

指定结果列行为

我们也可以使用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)

When we call the TextClause.columns() method, we get back a TextAsFrom object that supports the full suite of TextAsFrom.c and other “selectable” operations:

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

The positional form of TextClause.columns() is particularly useful when relating textual SQL to existing Core or ORM models, because we can use column expressions directly without worrying about name conflicts or other issues with the result column names in the textual 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

It’s important to note that while accessing columns from a result set using Column objects may seem unusual, it is in fact the only system used by the ORM, which occurs transparently beneath the facade of the Query object; in this way, the TextClause.columns() method is typically very applicable to textual statements to be used in an ORM context. Using Textual SQL的例子说明了一个简单的用法。

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

也可以看看

TextClause.columns() - full method description

Using Textual SQL - 将ORM级查询与text()

在较大的语句中使用text()片段

text()也可用于生成可在select()对象中自由使用的SQL片段,该对象接受text()对象作为其大部分建设者职能的论据。下面我们结合select()对象中text()的用法。The select() construct provides the “geometry” of the statement, and the text() construct provides the textual content within this form. 我们可以建立一个声明,而无需参考任何预先建立的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',)]

Changed in version 1.0.0: The select() construct emits warnings when string SQL fragments are coerced to text(), and text() should be used explicitly. 请参阅Warnings emitted when coercing full SQL fragments into text())作为背景。

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

我们可以通过使用column()literal_column()table()来将我们的结构级别向另一个方向移回我们声明的一些关键要素。Using these constructs, we can get some more expression capabilities than if we used text() directly, as they provide to the Core more information about how the strings they store are to be used, but still without the need to get into full Table based metadata. 在下面,我们还为key literal_column()对象中的两个指定了String数据类型,以便特定于字符串的连接运算符变为可用。We also use literal_column() in order to use table-qualified expressions, e.g. users.fullname, that will be rendered as is; using column() implies an individual column name that may be quoted:

>>> 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”条款中的字段。If we have such a label in our select() construct, we can refer to it directly by passing the string straight into select.order_by() or select.group_by(), among others. 这将引用指定的标签,并防止表达式被渲染两次:

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

sql>>> conn.execute(stmt).fetchall()
[(2, 4)]

我们可以使用asc()desc()等修饰符来传递字符串名称:

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

sql>>> conn.execute(stmt).fetchall()
[(2, 4)]

请注意,这里的字符串特性非常适合于我们已经使用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 .. FROM sometable AS someothername”就会发生这种情况。AS为表格创建一个新名称。别名是一个关键的结构,因为它们允许任何表或子查询被一个唯一的名称引用。在表格的情况下,这允许多次在FROM子句中命名相同的表格。在SELECT语句的情况下,它为由语句表示的列提供父名称,允许它们相对于该名称被引用。

In SQLAlchemy, any Table, select() construct, or other selectable can be turned into an alias using the FromClause.alias() method, which produces a Alias construct. 举个例子,假设我们知道我们的用户jack有两个特定的电子邮件地址。我们如何根据这两个地址的组合来定位插孔?为了达到这个目的,我们使用一个连接到addresses表,每个地址一次。我们根据addresses创建两个Alias结构,然后在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结构在最终的SQL结果中生成名称addresses_1addresses_2这些名字的产生由结构在声明中的位置决定。如果我们仅使用第二个a2别名创建查询,则名称将以addresses_1形式出现。The generation of the names is also deterministic, meaning the same SQLAlchemy statement construct will produce the identical SQL string each time it is rendered for a particular dialect.

由于在外部,我们使用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表达式了。SELECT的下一个基石是JOIN表达式。我们已经在我们的例子中进行了连接,只需将两个表放置在select()结构的columns子句或where子句中即可。但是如果我们想做一个真正的“JOIN”或“OUTERJOIN”构造,我们使用最常见的从左表访问的join()outerjoin()

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

警报读者会看到更多的惊喜; SQLAlchemy想出了如何加入两个表!这个连接的ON条件是根据我们在本教程开头的addresses表的方式放置的ForeignKey对象自动生成的。已经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',)]

The outerjoin() method creates LEFT OUTER JOIN constructs, and is used in the same way as 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是什么意思的话,别担心!Oracle数据库管理员的秘密部落不希望他们发现黑魔法;)。

其他的东西

已经介绍了创建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() constructs of the same name can also be used multiple times, where only a single named value is needed in the execute parameters:

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

功能¶ T0>

SQL函数使用func关键字创建,该关键字使用属性访问生成函数:

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

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

By “generates”, we mean that any SQL function is created based on the word you choose:

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

某些函数名称由SQLAlchemy知道,允许应用特殊的行为规则。一些例如是“ANSI”函数,这意味着它们不会在它们后面添加括号,例如CURRENT_TIMESTAMP:

>>> 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等数据库可以组合成可选择的单元,可以在语句中使用。Such as, a database function calculate() which takes the parameters x and y, and returns three columns which we’d like to name q, z and r, we can construct using “lexical” column objects as well as bind parameters:

>>> 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()函数将为我们创建副本,并将绑定参数标记为“unique”相互冲突的名字是孤立的。注意我们也可以选择两个独立的别名:

>>> 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 # doctest: +SKIP
{u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17}

也可以看看

func

窗口函数

任何FunctionElement,包括由func生成的函数都可以转换成一个“窗口函数”,即一个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() also supports range specifciation using either the expression.over.rows or expression.over.range parameters:

>>> 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.rows and expression.over.range each accept a two-tuple which contains a combination of negative and positive integers for ranges, zero to indicate “CURRENT ROW” and None to indicate “UNBOUNDED”. 有关更多详细信息,请参阅over()上的示例。

版本1.1新增:支持窗口函数的“行”和“范围”规范

联合和其他集合操作

联合有两种风格,UNION和UNION ALL,它们可以通过模块级别的函数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')]

Also available, though not supported on all databases, are intersect(), intersect_all(), except_(), and 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')]

标量选择

标量选择是一个SELECT,它返回一行和一列。它然后可以用作列表达式。A scalar select is often a correlated subquery, which relies upon the enclosing SELECT statement in order to acquire at least one of its FROM clauses.

通过调用as_scalar()label()方法,可以将select()结构修改为列表达式:

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

The above construct is now a ScalarSelect object, and is no longer part of the FromClause hierarchy; it instead is within the ColumnElement family of expression constructs. We can place this construct the same as any other column within another 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)]

要将非匿名列名应用于我们的标量选择,我们使用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)]

相关子查询

请注意,在Scalar Selects的例子中,每个嵌入式选择的FROM子句在其FROM子句中不包含users表。This is because SQLAlchemy automatically correlates embedded FROM objects to that of an enclosing query, if present, and if the inner SELECT statement would still have at least one FROM clause of its own. 例如:

>>> 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()方法来指定那些可能相关的FROM子句:

>>> 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之外的所有FROM子句来为users表写入我们的SELECT:

>>> 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才支持。

Normally, if a SELECT statement refers to table1 JOIN (some SELECT) AS subquery in its FROM clause, the subquery on the right side may not refer to the “table1” expression from the left side; correlation may only refer to a table that is part of another SELECT that entirely encloses this SELECT. LATERAL关键字允许我们将这种行为转化为一个表达式,例如:

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

如上所述,JOIN的右侧包含一个子查询,它不仅指“books”表,还指“JOIN”左侧的“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,它提供了LATERAL关键字以及特殊的指令,以允许在包围语句的FROM子句内进行关联。

The Select.lateral() method interacts normally with the Select.correlate() and Select.correlate_except() methods, except that the correlation rules also apply to any other tables present in the enclosing statement’s FROM clause. 如果表被指定为Select.correlate(),并且除了那些指定给Select.correlate_except()

版本1.1中的新功能:支持LATERAL关键字和横向关联。

排序,分组,限制,偏移量...

通过将列表达式传递给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()方法添加一个简单的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这样的常见后端支持LIMIT和OFFSET关键字,但其他后端需要引用更多深奥的功能,例如“窗口函数”和行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()方法产生UPDATE。这两种结构都有一个名为values()的方法,它指定语句的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()结构。When using bindparam() with insert() or update(), the names of the table’s columns themselves are reserved for the “automatic” generation of bind names. 我们可以结合使用隐式可用的绑定名称和明确命名的参数,如下例所示:

>>> 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语句是使用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...>

在“executemany”上下文中使用update()时,我们也希望在WHERE子句中使用明确的命名绑定参数。Again, bindparam() is the construct used to achieve this:

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

多表更新

0.7.4版本的新功能

Postgresql,Microsoft SQL Server和MySQL后端都支持引用多个表的UPDATE语句。对于PG和MSSQL,这是“UPDATE FROM”语法,它一次更新一个表,但可以在附加的“FROM”子句中引用额外的表,然后可以直接在WHERE子句中引用该子句。在MySQL上,可以将多个表嵌入由逗号分隔的单个UPDATE语句中。通过在WHERE子句中指定多个表,SQLAlchemy update()结构隐式支持这两种模式:

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()的字典形式直接在SET子句中将每个表中的列分配给SET子句:

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

在不支持的数据库上使用这些结构时,SQLAlchemy不会做任何特殊的事情。如果存在多个表,则会默认生成UPDATE FROM语法,如果不支持此语法,则语句将被数据库拒绝。

参数有序更新

呈现SET子句时,update()构造的默认行为是使用原始Table对象中给定的列顺序来呈现它们。这是一个重要的行为,因为它意味着每次渲染具有特定列的特定UPDATE语句都会呈现相同的效果,这对依赖于语句形式的查询缓存系统(客户端或服务器)侧。由于参数本身作为Python字典键传递给Update.values()方法,因此没有其他可用的固定排序。

但是,在某些情况下,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标志。当使用这个标志时,我们为Update.values()方法提供一个2元组的Python列表作为参数。

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标志显式排序UPDATE参数的支持。

删除¶ T0>

最后是删除。这很容易使用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...>

匹配行计数

update()delete()都与匹配的行数关联。这是一个数字,表示WHERE子句匹配的行数。请注意,通过“匹配”,这包括没有更新实际发生的行。该值可用于rowcount

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

进一步参考

表达式语言参考:SQL Statements and Expressions API

数据库元数据参考:Describing Databases with MetaData

引擎参考:Engine Configuration

连接参考:Working with Engines and Connections

类型参考:Column and Data Types