由于简书限制，无法在一篇文章中包含太多文字，所以分成了上下两个部分
引言
SQLAlchemy 的「表达式语言」实际上就是利用 Python 来构建表示关系型数据库结构和表达式的系统。使用起来类似于我们在下层的数据库中直接使用 SQL 表达式一样，但实际上它为不同数据库实现的差异提供了一层抽象。虽说对于不同的数据库几乎可以使用一致的结构来表示等效的概念，但并没有完全隐藏针对部分支持的后端数据库上细微概念的差异。因此，这种「表达式语言」还为我们提供了一种编写后端中立的 SQL 表达式方法，但并没有强制这些表达式就是后端中立的。
这里所讨论的「表达式语言」与 ORM（Object Relational Mapper, 关系对象映射）是对立的，后者是基于该表达式语言构建的另外一套 API。这里所说的 ORM（关系对象指南）是一种更加抽象、更高层次的使用模式，也是使用「表达式语言」的一种示例。毫无疑问，「表达式语言」代表的是能够直接表示关系型数据库最原始的结构的系统。
ORM 和「表达式语言」在使用模式上有重叠的地方，这种相似性可能要比他们第一次出现还要浅显。从用户定义的域模型角度来看，ORM 的就会以透明的方式在底层的存储模型中持久化或者刷新。而从表单和 SQL 表达式的角度来看，「表达式语言」显式地构成了可供数据库独立消费的消息。
一个成功的项目可能仅需要使用「表达式语言」就可以构建，虽然需要自行定义翻译器用于将应用层的概念转换成单独的数据库消息以及将来自独立数据库的结果集转换成应用层对象。当然，在高级场合，一个使用 ORM 构建的应用也可以在需要某种特点的数据库交互时直接使用「表达式语言」。
本指南中的代码是在 Jupyter Notebook 中测试并执行的，源文件请 戳这里（密码 epra） 下载。
检查版本
快速检查下我们是否正确安装了 SQLAlchemy：
import sqlalchemy
print(sqlalchemy.__version__)
1.1.14
连接
下面将会使用一个仅在内存中的 SQLite 数据库。这样我们可以非常简单地进行测试，而不用在别处定义真实的数据库。我们使用 create_engine() 进行连接：
from sqlalchemy import create_engine
engine = create_engine("sqlite:///:memory:", echo=False)
echo 标志是设置 SQLAlchemy 日志输出的快捷方式，日志功能是借助 Python 标准的 logging 模块实现的。当启用 echo 后，我们会看到所有生成的 SQL。如果你不想看到太多生成的 SQL 等消息输出，可以将 echo 设置为 False。
create_engine 返回的是一个 Engine 实例，它代表的是连接数据库的核心接口，它通过使用相应的方言来处理与数据库交互的细节，并使用 DBAPI，从而实现对不同数据库的适配。这个例子中，就会使用 SQLite 方言将指令解释给 Python 内建的 sqlite3 模块。
当第一次调用 Engine.execute() 或 Engine.connect() 方法时，才会真正创建一个到数据库的 DBAPI 连接，然后可以用来发送 SQL。
定义并创建表
多数情况下，「SQL 表达式语言」会基于表列来构建它的表达式。在 SQLAlchemy 中，一列通常使用 Column 对象来表示，并且 Column 对象总是关联到一个 Table 对象中。一组 Table 对象集合以及它们的关联子对象就被称为「数据库元数据（database metadata）」。在本教程中，我们将显式创建几个 Table 对象，但实际上 SA 也可以帮我们从现有的数据库中「导入」完整的 Table 对象集合（这个过程叫做「表映射」）。
我们使用 Table 构造器（代表了 SQL CREATE TABLE 语句）定义所有的表，并放到一个叫做 MetaData 的目录中。我们将会创建两张表，一张是应用中常见的用户表，而另一张表则是用来表示用户表中每个用户所拥有的 0 个或多个邮件地址。
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String)
)
addresses = Table('address', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', None, ForeignKey('user.id')),
Column('email_address', String, nullable=False)
)
使用 MetaData 描述数据库中详细说明了如何定义 Table 对象，以及如何从现有的数据库中自动创建 Table 对象。
接下来，要想告诉 MetaData 将选择的表在 SQLite 数据库中创建出来，就需要调用 create_all() 方法，并向其传递我们之前创建的指向数据库的 engine 实例。这将会在创建每张表之前检查其是否存在，所以即使调用多次也是安全的：
metadata.create_all(engine)
注意
熟悉 CREATE TABLE 语法的用户会发现 VARCHAR 并没有指定长度；在 SQLite 和 PostgreSQL 中，这是合法的数据类型，但其它不是。所以，如果想要在其它数据库上运行本教程中的创建语句，你可能需要给 String 类型提供一个长度，类似于下面这样：
Column('name', String(50))
在 String 中的长度参数，以及在 Integer, Numeric 等类似的精度/尺度参数，除了在创建表之外，都不会被 SQLAlchemy 引用。
此外，Firebird 和 Oracle 需要使用 sequence 生成新的主键标识，如果没有显式告诉 SQLAlchemy，它是不会生成或者假定这些的。对于这种情况，你就要明确使用 Sequence 构造器：
from sqlalchemy import Sequence
Column('id', Integer, Sequence('user_id_seq'), primary_key=True)
因此，一个完整且不会出错的 Table 定义就是这样的：
user = Table('user', 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()
我们可以使用 str() 函数查看具体会生成的 SQL 样例：
str(ins)
'INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname)'
注意到上面的 INSERT 语句选中了 user 表中所有的列。我们可以使用 .values() 方法显式传递需要使用的列，
ins = users.insert().values(name="Jack", fullname="Jack Jones")
str(ins)
'INSERT INTO "user" (name, fullname) VALUES (:name, :fullname)'
我们可以看到，虽然 values 方法将 VALUES 语句限制到了两列，但我们实际传递的数据并没有渲染成字符串；而是得到了命名绑定参数。事实时，我们的数据存储在 Insert 实例中，通常在它被真正执行的时候才会使用。因为数据是由常量组成，SQLAlchemy 会自动为它们生成绑定参数。我们可以通过查看语句编译后的形式看到数据：
ins.compile().params
{'fullname': 'Jack Jones', 'name': 'Jack'}
执行
Insert 最有趣的部分就是执行它。在这份指南中，我们通常首先教你使用最显式的方法来执行 SQL，后面会告诉你怎么使用更「快捷」的方式做到。我们在前面创建的 engine 对象就是数据库连接仓库，它能够向数据库发送 SQL。为了获得一个连接，我们需要使用 connect() 方法：
conn = engine.connect()
conn
<sqlalchemy.engine.base.Connection at 0x10efaa4e0>
Connection 对象代表的是一个激活的 DBAPI 连接资源。让我们传入 Insert 对象看看会发生什么吧：
# INSERT INTO users (name, fullname) VALUES (?, ?) ('jack', 'Jack Jones')
result = conn.execute(ins)
所以，插入语句被发送到了数据库。虽然我们在输出中看到的是占位符 ? 绑定参数，而非命名绑定参数。为什么可以这样？因为在执行时，Connection 使用了 SQLite 方言帮助生成的语句；当我们使用 str() 函数时，语句并不知道会使用何种方言，所以它会使用默认的命名参数。我们可以通过下面的方式观察到：
ins.bind = engine
print(ins)
INSERT INTO user (name, fullname) VALUES (?, ?)
那在我们调用了 execute() 之后，得到的 result 究竟是什么呢？由于 SQLAlchemy 的 Connection 对象引用的是 DBAPI 连接，所以 result 就是所谓的 ResultProxy 对象，也就是类似 DBAPI 游标的对象。以插入为例，我们可以得到从中得到一些重要信息，比如我们可以使用 ResultProxy.inserted_primay_key 获得生成的主键值：
result.inserted_primary_key
[1]
上面的值 1 是由 SQLite 自动生成的，但仅在我们没有在插入语句中指定 id 的情况下才会生成；否则将会使用我们传递的值。无论如何，SQLAlchemy 总会知道如何生成主键值，即便不同数据库生成的方式都不同；每种数据库 Dialect 都知道生成正确值的具体步骤（或者多个值，注意看上面的结果返回的可是一个列表，也就意味着它支持组合主键）。生成的方法可以使用 curosr.lastrowid，或者使用数据库特有的函数，亦或使用 INSEART...RETURNING 语法，不一而足，但这一切都是透明的。
执行多条语句
上面插入语句示例就是 show 了下「表达式语言」的不同行为。常规情况下，Insert 语句通常是和参数一起编译后发送给 Connection.execute() 的，所以没必要在使用 Insert 时再调用 values 了。下面我们来看一个通用的插入语句，并用「正常」的方式操作：
# Executing multiple statements
ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')
<sqlalchemy.engine.result.ResultProxy at 0x10ee5ad68>
如上所示，因为我们在 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'}
])
<sqlalchemy.engine.result.ResultProxy at 0x10efa0160>
上面的用法中，我们依然依赖了 SQLite 自动生成主键值的机制。
在执行多个插入参数集合时，必须要保证它们拥有相同的键，因此你无法让部分字典中的键比别的少。因为 Insert 语句的编译依赖于列表中的第一个字典，并且它会假设所有后续的参数字典都会和当前的插入语句兼容。
executemany 风格的调用方式适用于 insert(), update() 还有 delete() 操作。
选择
为了让我们的测试数据库有一些测试数据，我们最开始学习了插入语句。然而，最有趣的部分却是选择它们！我们将会在后面介绍到 UPDATE 和 DELETE 语句。生成 SELECT 语句主要还是靠 select() 函数：
# Selecting
from sqlalchemy import select
s = select([users])
result = conn.execute(s)
上面的例子中，我们调用了 select() 函数，并将 users 作为待选择的列，最后执行了相应的语句。SQLAlchemy 会展开 users 表，并将它的列放入到一个集合中，同时还会产生一个 FROM 语句。上述返回结果依然是一个 ResultProxy 对象，类似于一个 DBAPI 游标，并包含了 fetchone(), fetchall() 之类的方法。获取行最简单的方式就是遍历它：
for row in result:
print(row)
(1, 'Jack', 'Jack Jones')
(2, 'wendy', 'Wendy Williams')
从上面可以看到，打印出来的行类似于元组。实际上，我们有多种方式来访问每一行中的数据。最常见的就是类似字典的方式访问，也就是直接用列名作为 key 来访问，此外还支持索引和属性的方式：
result = conn.execute(s)
row = result.fetchone()
# Access column by its index/column_name(attribute style or dict style)
print('id:', row[0], '; name:', row['name'], '; fullname:', row.fullname)
id: 1 ; name: Jack ; fullname: Jack Jones
仍然还有未读取行的结果集应当在丢弃前显式关闭。虽说被 ReultPorxy 引用的游标和连接资源会在对象被垃圾回收时自动关闭并返回给连接池，但还是推荐显式地关闭，因为有些数据库 API 对此非常挑剔：
# Better to close the result and return connection to the pool
result.close()
如果我们想详细指定需要选择的列，那么就可以引用表的相应 Column 对象。我们可以通过 table.c.column_name 的方式引用到相应的列：
s = select([users.c.name, users.c.fullname])
result = conn.execute(s)
for row in result:
print(row)
('Jack', 'Jack Jones')
('wendy', 'Wendy Williams')
让我们仔细观察下 FROM 语句有趣的特性。可以看到生成的语句有两个部分，一个是 SELECT 列，另一个是 FROM 表，但我们的 select() 仅仅接收了一个包含列的列表而已。这是如何工作的呢？让我们试着把两个表都放到 select() 语句中看看：
# 'SELECT "user".id, "user".name, "user".fullname,
# address.id, address.user_id, address.email_address
# FROM "user", address'
for row in conn.execute(select([users, addresses])):
print(row)
(1, 'Jack', 'Jack Jones', 1, 1, 'jack@yahoo.com')
(1, 'Jack', 'Jack Jones', 2, 1, 'jack@msn.com')
(1, 'Jack', 'Jack Jones', 3, 2, 'www@www.org')
(1, 'Jack', 'Jack Jones', 4, 2, 'wendy@aol.com')
(2, 'wendy', 'Wendy Williams', 1, 1, 'jack@yahoo.com')
(2, 'wendy', 'Wendy Williams', 2, 1, 'jack@msn.com')
(2, 'wendy', 'Wendy Williams', 3, 2, 'www@www.org')
(2, 'wendy', 'Wendy Williams', 4, 2, 'wendy@aol.com')
可以看到它将两张表都放到了 FROM 语句中了，但这也造成了严重的错乱。熟悉 SQL JOIN 的人都会知道那就是一个「笛卡尔积」；users 表中的每一行都和 addresses 表中的每一行相乘了。因此，需要增加一个 WHERE 子句来解决。我们可以使用 Select.where() 搞定：
s = select([users, addresses]).where(users.c.id == addresses.c.user_id)
for r in conn.execute(s):
print(r)
(1, 'Jack', 'Jack Jones', 1, 1, 'jack@yahoo.com')
(1, 'Jack', 'Jack Jones', 2, 1, 'jack@msn.com')
(2, 'wendy', 'Wendy Williams', 3, 2, 'www@www.org')
(2, 'wendy', 'Wendy Williams', 4, 2, 'wendy@aol.com')
看到没，明显好多了，我们给 select() 增加了一个表达式并生成了 WHERE user.id = address.user_id 子句，这样组合出来的结果就合理了。回过头来看看那个表达式？我们仅仅在两个不同的 Column 对象中使用了一个 Python 等于操作符而已。需要明确解释下这是怎么做到的，按常理来说， 1 == 1 生成 True，1 == 2 生成 False，但也不会是 WHERE 子句啊。好吧，让我们看看那样的表达式究竟会返回什么：
where = users.c.id == addresses.c.user_id
where, str(where)
(<sqlalchemy.sql.elements.BinaryExpression object at 0x10efaa470>,
'"user".id = address.user_id')
惊讶，返回的根本不是 True 或者 False！
可见，== 操作符实际上产生了一个类似于 Insert 和 Select 对象，这得多亏了 Python 的 __eq__ 方法；你可以调用 str() 查看生成的 SQL。至此，我们可以看到我们最终一直在和相同类型的对象打交道。SQLAlchemy 中的这些表达式都继承自 ColumnElement。
操作符
上面我们简单接触下 SQLAlchemy 操作符范式，接下来我们将详细了解它的强大之处。我们已经看到了 == 应用在两个列上的返回：
print(users.c.id == addresses.c.user_id)
"user".id = address.user_id
如果我们使用一个常量，就会得到一个绑定参数：
print(users.c.id == 10)
"user".id = :id_1
常量 7 会被塞到 ColumnElement 中；我们可以利用类似查看 Insert 参数的技巧来看看是不是这样：
(users.c.id == 10).compile().params
{'id_1': 10}
大多数的 Python 操作符也因此都会产生一个 SQL 表达式，例如相等、不等……：
print(users.c.id != 10)
"user".id != :id_1
print(users.c.name == None)
"user".name IS NULL
print('fred' > users.c.name)
"user".name < :name_1
如果我们将两个整数列相加，可以得到一个相加的表达式：
print(users.c.id + addresses.c.id)
"user".id + address.id
有趣的是，Column 的类型非常重要！如果相加的两列是字符串类型的话，我们会得到不同的结果：
print(users.c.name + users.c.fullname)
"user".name || "user".fullname
这里的 || 就是在多数数据库中使用的字符串拼接操作符。但并非所有的情况都是这样。MySQL 就是个例外：
import pymysql
pymysql.install_as_MySQLdb()
print((users.c.name + users.c.fullname).compile(bind=create_engine('mysql://')))
concat(user.name, user.fullname)
上面的例子表明，基于连接到 MySQL 的 Engine 生成的 SQL 就会将 || 操作符替换成 MySQL 的 concat() 函数。
如果你要用的操作符不满足需求，你总是可以使用 Operators.op() 方法造一个出来；它可以生成任何你需要的操作符：
print(users.c.name.op('tiddlywinks')('foo'))
"user".name tiddlywinks :name_1
这个函数也可以用来创造显式的位运算操作符（下面的就是「位与」运算）：
print(users.c.id.op('&')(0xff))
"user".id & :id_1
在使用 Operators.op() 时，表达式的返回类型也许会很重要，尤其是用在发送结果列的表达式中。对于这种情况，如果不是期望的那种类型的话，记得要显式指定类型，使用 type_coerce()：
from sqlalchemy import type_coerce
expr = type_coerce(users.c.name.op('-%>')('foo'), MySpecialType())
stmt = select([expr])
对于布尔类型的操作符，使用 Operators.bool_op() 方法，这会保证返回值类型被当做布尔类型处理：
if sqlalchemy.__version__ >= '1.2':
users.c.id.bool_op('-->')('some value')
连接
我们先来看看 select 构造器中使用的操作符。连接操作就是使用 AND, OR 这种词，然后将一些东西放在一起。我们也会学习到 NOT。and_(), or_() 以及 not_() 都是可以使用的相关函数（下面还包含了 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)
)
)
"user".name LIKE :name_1 AND "user".id = address.user_id AND (address.email_address = :email_address_1 OR address.email_address = :email_address_2) AND "user".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)
)
"user".name LIKE :name_1 AND "user".id = address.user_id AND (address.email_address = :email_address_1 OR address.email_address = :email_address_2) AND "user".id <= :id_1
好吧，学习了上面那些后，让我们来选择所有拥有 AOL 或者 MSN 邮箱，并且他们的名字首字母都在 m 和 z 之间的用户，我们也会生成一个新的列，包含他们名称和邮箱的组合。我们将会新增两种新的构造器 between() 和 label()。beween() 自然是用来生成 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')
)
)
)
print(s)
conn.execute(s).fetchall()
SELECT "user".fullname || :fullname_1 || address.email_address AS title
FROM "user", address
WHERE "user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email_address LIKE :email_address_1 OR address.email_address LIKE :email_address_2)
[('Wendy Williams, wendy@aol.com',)]
SQLAlchemy 再次为我们自动生成了清晰的 FROM 子句。事实上，它会根据所有其它的位来生成 FROM 子句：列子句、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')
)
)
版权声明
- 本文翻译自 SQLAlchemy Document: SQL Expression Language Tutorial，译文仅供学习参考。
- 本文由 Christopher L 发表，采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。请确保你已了解许可协议，并在 转载 时声明。
- 本文固定链接： http://blog.chriscabin.com/?p=1608。
网友评论