我们周围到处都是数据，数据的存储、更新以及报表制作是每个应用程序应该具备的关键功能。无论开发的是 Web 应用程序、桌面应用程序还是其他应用程序，都需要确保可以快速、安全地获取数据。如今，关系型数据库仍然是存放数据最常用的方式之一。

SQL 是一种用来查询和操作数据库数据的强大语言，但有时我们很难将其与所开发的应用程序集成在一起。使用 Python 做开发时，你可能使用字符串创建过在 ODBC 接口上运行的查询，也可能用过 DB API。虽然这些方法可以有效地处理数据，但安全性令人担忧，而且也很难对数据库做出调整。

本文探讨一个非常强大又相当灵活的 Python 库，它在关系型数据库和传统编程之间架起了一座桥梁，它就是 SQLAlchemy。

SQLAlchemy入门

SQLAlchemy 库是 Mike Bayer 在 2005 年创建的，现在大大小小很多公司都在使用它。事实上，许多公司都把 SQLAlchemy 看作在 Python 中使用关系型数据库的标准方式。

SQLAlchemy 允许我们使用原始 SQL 执行查询，更鼓励我们使用其提供的高级方法来查询和更新数据库，它提供的方法用起来很友好，而且具有鲜明的 Python 风格。SQLAlchemy 还提供了许多很棒的工具，通过这些工具，可以轻松地将应用程序中的类和对象同数据库表映射起来，然后“忘掉它”，或者不断回到模型调优性能上。

SQLAlchemy 库主要用于与各种数据库交互，可使用一种类似于 Python 类和语句的方式创建数据模型和查询。它可用于连接大多数常见的数据库，比如 Postgres、MySQL、SQLite、Oracle 等。SQLAlchemy 还提供了一种为其他关系型数据库添加支持的方式。Amazon Redshift（使用 PostgreSQL 自定义方言）就是 SQLAlchemy 社区添加的数据库支持的一个很好的例子。

为什么使用SQLAlchemy

使用 SQLAlchemy 的首要原因是，它将你的代码从底层数据库及其相关的 SQL 特性中抽象出来。SQLAlchemy 使用功能强大的常见语句和类型，确保其 SQL 语句为每种数据库和供应商正确、高效地构建出来，而无须你考虑这些。同时，这使得将逻辑从 Oracle 迁移到 PostgreSQL 或从应用程序数据库迁移到数据仓库变得很容易。它还有助于确保数据在提交到数据库之前得到“净化”并正确地进行了转义。这可以避免一些常见的问题，比如 SQL 注入攻击。

SQLAlchemy 提供了两种主要的使用模式——SQL 表达式语言（通常称为 Core）和 ORM，这为我们使用 SQLAlchemy 提供了很大的灵活性。这两种模式可以单独使用，也可以一起使用，具体用法取决于个人喜好以及应用程序的需求。

SQLAlchemy Core和SQL表达式语言

SQL 表达式语言允许我们以 Python 方式使用常见的 SQL 语句和表达式，它是对标准 SQL 语言的简单抽象。SQL 表达式语言关注的是实际的数据库模式，它在经过标准化之后为大量后端数据库提供了一种一致性的语言。SQL 表达式语言也是 SQLAlchemy ORM 的基础。

ORM

SQLAlchemy ORM 类似于你在其他语言中遇到的对象关系映射（ORM）。它关注应用程序的领域模型，并利用工作单元模式来维护对象状态。它还在 SQL 表达式语言之上做了进一步的抽象，使用户能够以惯用的方式工作。你可以组合或搭配使用 ORM 与 SQL 表达式语言，从而创建出功能更为强大的应用程序。ORM 中用到了一个声明式系统，该系统类似于许多其他 ORM 中使用的 Active-Record 系统，比如 Ruby on Rails 中使用的那个。

虽然 ORM 极其有用，但你必须记住，类的关联方式和底层数据库关系的工作方式是有区别的。之后会更加全面地探讨这些方式如何影响你的实现。

选择SQLAlchemy Core还是SQLAlchemy ORM

在使用 SQLAlchemy 构建应用程序之前，你要决定主要使用 ORM 还是 Core。选择使用 Core 还是 ORM 作为应用程序的主要数据访问层，通常取决于多个因素和个人偏好。

Core 和 ORM 使用的语法略有不同，但它们之间最大的区别是把数据看作模式还是业务对象。SQLAlchemy Core 的视图以模式为中心，与传统 SQL 一样，它关注的是表、键和索引结构。SQLAlchemy Core 在数据仓库、报表、分析和其他场景中（在这些场景中，严格控制对未建模的数据进行查询或操作将非常有用）可以大放异彩。强大的数据库连接池和结果集优化非常适合处理大量数据，甚至适合处理多个数据库的数据。

但是，如果你主要关注的是领域驱动设计，那么选用 ORM 会更好，它会帮你把元数据和业务对象中的底层模式和结构封装起来。这种封装使得和数据库的交互变得更加容易，就像在使用普通的 Python 代码一样。大多数常见的应用程序都可以通过这种方式进行建模。ORM 还可以有效地把领域驱动设计注入到遗留的应用程序或者到处是原始 SQL 语句的应用程序中。微服务也能从对底层数据库的抽象中获益，它使得开发人员可以只关注正在实现的流程。

但是，由于 ORM 构建在 SQLAlchemy Core 之上，所以你也可以借助 ORM 使用 Oracle 数据仓库和 Amazon Redshift 这样的服务，就像和 MySQL 交互一样。因此，ORM 很适合用来合并业务对象和仓库数据。

下面列出了几种应用场景，了解它们有助于我们在 Core 和 ORM 中做出最佳选择。

• 虽然你使用的框架中已经内置了 ORM，但你希望添加更强大的报表功能，请选用 Core。
• 如果你想在一个以模式为中心的视图中查看数据（用法类似于 SQL），请使用 Core。
• 如果你的数据不需要业务对象，请使用 Core。
• 如果你要把数据看作业务对象，请使用 ORM。
• 如果你想快速创建原型，请使用 ORM。
• 如果你需要同时使用业务对象和其他与问题域无关的数据，请组合使用 Core 和 ORM。

现在我们已经了解了 SQLAlchemy 的结构以及 Core 和 ORM 之间的区别，接下来就可以开始安装并使用 SQLAlchemy 来连接数据库了。

安装具体事项可参见《SQLAlchemy：Python数据库实战（第2版）》图灵社区试读。这是地址：https://www.ituring.com.cn/book/tupubarticle/27494。

接下来我们了解一下如何使用 SQLAlchemy Core 为应用程序提供数据库服务。

SQLAlchemy Core 允许我们以 Python 方式使用 SQL 命令和数据结构，即所谓的 SQL 表达式语言。SQLAlchemy Core 既可以与 Django 或 SQLAlchemy ORM 一起使用，也可以作为独立的解决方案使用。

我们要做的第一件事是定义数据表存储什么样的数据、数据之间如何相互关联，以及数据上的约束。

为了访问底层数据库，SQLAlchemy 需要用某种东西来代表数据库中的数据表。为此，可以使用下面三种方法中的一种：

• 使用用户定义的 Table 对象
• 使用代表数据表的声明式类
• 从数据库中推断

我们重点讲第一种方法，因为它正是 SQLAlchemy Core 所采用的方式。Table 对象包含一系列带有类型的列和属性，它们与一个常见的元数据容器相关联。下面我们来看模式定义，首先来看看 SQLAlchemy 中有哪些类型可用来构建表格。

类型

在 SQLAlchemy 中有四种类型可用：

• 通用类型
• SQL 标准类型
• 厂商自定义类型
• 用户定义类型

SQLAlchemy 定义了大量通用类型，它们是从各后端数据库所支持的实际 SQL 类型中抽象出来的。这些类型在 sqlalchemy.types 模块中都可用，为方便起见，它们在 sqlalchemy 模块中也可用。接下来看看这些通用类型为什么有用。

Boolean 通用类型一般使用 BOOLEAN SQL 类型，在 Python 端处理成 true 或 false。但是，它在不支持 BOOLEAN 类型的后端数据库上使用 SMALLINT。幸运的是 SQLAlchemy 隐藏了这个小细节，你可以坚信，你创建的所有查询或语句都能针对该类型的字段正确操作，而不管你使用的是哪种类型的数据库。你只需要在 Python 代码中处理 true 或 false 即可。在数据库迁移或分割后端系统（其中数据仓库是一种数据库类型，事务性系统是另外一种数据库类型）时，这种行为使得通用类型非常强大、有用。通用类型在 Python 和 SQL 中分别对应的类型如表 1-1 所示。

表1-1：通用类型及对应关系

学习这些通用类型很重要，因为你会经常使用和定义它们。

除了表 1-1 中列出的通用类型外，还可以使用 SQL 标准类型和厂商自定义类型。当通用类型因其类型或现有模式中指定的特定类型而无法在数据库模式中按照需要使用时，我们通常会使用这两种类型。CHAR 和 NVARCHAR 类型就是很好的例子，它们都要求使用正确的SQL 类型而不仅仅是通用类型。如果我们使用的是在使用 SQLAlchemy 之前就已经定义好的数据库模式，那么就要准确地匹配类型。请务必记住，SQL 标准类型的行为和可用性因数据库而异。SQL 标准类型在 sqlalchemy.types 模块中是可用的。为了将 SQL 标准类型和通用类型区分开，标准类型全部采用大写字母。

厂商自定义类型和 SQL 标准类型一样有用，但是它们只适用于特定的后端数据库。可以通过所选方言的文档或 SQLALchemy 站点确定有哪些类型可用。它们在 sqlalchemy.dialects 模块中都是可用的，并且每种数据库方言都有若干子模块。同样，这些类型采用的全是大写字母，以便同通用类型区分开。有时，我们可能想使用 PostgreSQL 强大的JSON 字段，为此可以使用下面的语句来实现：

from sqlalchemy.dialects.postgresql import JSON

现在，我们可以定义 JSON 字段了。以后，在我们的应用程序中，PostgreSQL 专用的许多JSON 函数都会用到它，比如 array_to_json 函数。

你还可以自定义类型，以便用你选择的方式存储数据。例如，在把字符放入数据库记录时，将其放在存储在 VARCHAR 列中的文本的前面，并在从记录中获取这个字段时去掉它们。在处理现有系统仍然使用的遗留数据时，这可能很有用，因为这种类型的前缀在新应用程序中可能没用或者并不重要。

我们已经学习了用于创建数据表的四种变体类型，接下来看看数据库结构是如何通过元数据结合在一起的。

元数据

元数据用来把数据库结构结合在一起，以便在 SQLAlchemy 中快速访问它。一般可以把元数据看作一种 Table 对象目录，其中包含与引擎和连接有关的信息。这些表可以通过字典MetaData.tables 来访问。读操作是线程安全的，但是表的创建并不是完全线程安全的。在把对象绑定到元数据之前，需要先导入并初始化元数据。接下来，初始化 MetaData 对象的一个实例，在后面的其余示例中我们会用它来保存信息目录：

from sqlalchemy import MetaData
metadata = MetaData()

到这里，我们已经有了用来保存数据库结构的方法，接下来开始定义表。

表

在 SQLAlchemy Core 中，我们通过调用 Table 构造函数来初始化 Table 对象。我们要在构造函数中提供 MetaData 对象（元数据）和表名，任何其他参数都被认为是列对象。此外，还有一些额外的关键字参数，它们用来开启相关特性，相关内容稍后讲解。列对象对应于数据表中的各个字段。列是通过调用 Column() 函数创建的，我们需要为这个函数提供列名、类型，以及其他表示 SQL 结构和约束的参数。在后面其余部分中，我们将创建一组表，并在第一部分中使用。在示例 1-1 中，我们创建了一个表，用于为线上 cookie 配送服务存储 cookie 库存。

示例 1-1　实例化 Table 对象和列

from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True), ➊
Column('cookie_name', String(50), index=True), ➋
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)) ➌
)

➊ 请注意我们把此列标记为表的主键的方式。稍后会详细介绍。

➋ 创建 cookie 名称索引，以加快该列的查询速度。

➌ 这个列包含多个参数，有长度和精度，比如 11.2，其中长度最多为 11 位数字，精度为两位小数。

在深入了解表之前，需要先了解表的基本构造块：列。

列

列用来定义数据表中的字段，它们提供了通过关键字参数定义其他约束的主要方法。不同类型的列的主要参数是不一样的。例如，String 类型的列的主要参数是长度，而带有小数部分的数字有精度和长度。其他大部分类型没有主要参数。

有时你会看到有些 String 列没有长度这个主要参数。这种行为并没有被普遍支持，例如，MySQL 和其他几个数据库后端就不允许这样做。

除此之外，列还有其他一些关键字参数，这些参数有助于进一步塑造它们的行为。可以把列标记为必需，或者强制它们是唯一的。还可以为列设置默认的初始值，并在记录更新时更改列值。一个常见的用例是那些用来指示何时为日志或审计的目的创建或更新记录的字段。下面看一下示例 1-2 中的这些关键字参数。

示例 1-2　另一个带有更多列选项的表

from datetime import datetime
from sqlalchemy import DateTime
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('username', String(15), nullable=False, unique=True), ➊
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now), ➋
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now) ➌
)

➊ 我们让这个列是必需的（nullable=False），而且值唯一。

➋ 如果未指定日期，就把当前时间设置为列的默认值。

➌ 通过使用 onupdate，使得每次更新记录时，都把当前时间设置给当前列。

你可能注意到了，我们在设置 default 和 onupdate 时使用的是 datetime.now， 而 没 有 调 用 datetime.now() 函 数。如果调用这个函数，它就会把default 设置为表首次实例化的时间。通过使用 datetime.now，可以得到实例化和更新每个记录的时间。

我们一直使用列关键字参数来定义表结构和约束，但是，你也可以在 Column 对象之外声明它们。当使用一个现有数据库时，这一点非常重要，因为你必须告诉 SQLAlchemy 数据库中的模式、结构和约束。例如，如果数据库中的已有索引与 SQLAlchemy 使用的默认索引命名方案不匹配，那么你必须手动定义该索引。下面两节内容将演示如何做到这一点。

键和约束

键和约束用来确保我们的数据在存储到数据库之前满足某些要求。可以在基本的SQLAlchemy 模块中找到代表键和约束的对象，其中三个常见的对象导入如下：

from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint

最常见的键类型是主键。主键用作数据库表中每个记录的唯一标识符，用于确保不同表中两个相关数据之间的关系正确。就像你在示例 1-1 和示例 1-2 中看到的那样，只需使用 primary_key 这个关键字参数，就可以让一个列成为主键。还可以通过在多个列上将primary_key 设置为 True 来定义复合主键。本质上，键会被视为一个元组，其中标记为键的列将按照它们在表中定义的顺序出现。主键也可以在表构造函数的列之后定义，如下面的代码片段所示。可以通过添加多个由逗号分隔的列来创建复合键。如果想像示例 1-2 那样显式地定义键，它看起来会像下面这样：

PrimaryKeyConstraint('user_id', name='user_pk')

另一个常见的约束是唯一性约束，它用来确保给定字段中不存在重复的两个值。例如，就我们的线上 cookie 配送服务来说，我们希望确保每个客户都有一个唯一的用户名来登录我们的系统。我们还可以为列分配唯一约束，就像前面在 username 列中所做的那样。当然，你也可以手动定义约束，如下所示：

UniqueConstraint('username', name='uix_username')

示例 1-2 中没有检查约束（check constraint）类型。这种类型的约束用于确保列数据和一组由用户定义的标准相匹配。在下面的示例中，我们会确保 unit_cost 永远不会小于 0.00，因为每个 cookie 的制作都要花费一定的成本（从经济学角度来说，就是没有免费的 cookie ！）：

CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')

除了键和约束之外，我们可能还希望提高某些字段的查找效率。这就是索引的作用。

索引

索引用来加快字段值的查找速度。在示例 1-1 中，我们在 cookie_name 列上创建了一个索引，因为我们知道以后会经常通过它来进行搜索。在索引创建好之后，你会拥有一个名为ix_cookies_cookie_name 的索引。还可以使用显式构造类型来定义索引。你可以指定多个列，各列之间用逗号分隔。你还可以添加 unique=True 这个关键字参数，指定索引也必须唯一。当显式地创建索引时，它们会在列之后传递给 Table 构造函数。示例 1-1 中的索引也可以显式地创建：

from sqlalchemy import Index
Index('ix_cookies_cookie_name', 'cookie_name')

我们还可以创建函数索引，函数索引因所使用的后端数据库而略有不同。这允许你为经常需要基于某些不寻常的上下文进行查询的情况创建索引。例如，如果我们想通过 cookieSKU 和名称进行选择，比如 SKU0001 Chocolate Chip，该怎么办？可以定义下面这样的索引来优化查找：

Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name))

接下来该深入研究关系型数据库最重要的部分了：表关系以及如何定义它们。

关联关系和外键约束

现在我们已经有了一个表，其中的列拥有正确的约束和索引。接下来看看如何在表之间创建关系。我们需要一种跟踪订单的方法，里面有表示每种 cookie 和订购量的行项目。为了帮助理解这些表之间的关系，请参阅图 1-1。

图 1-1：关系可视化

示例 1-3 给出了实现关系的一种方法，即在 line_items 表的 order_id 列上添加外键，通过 ForeignKeyConstraint 来定义两个表之间的关系。示例中，许多行项目都可以出现在单个订单中。但是，如果深入研究 line_items 表，你会发现我们还通过 cookie_id 这个外键在 line_items 表与 cookies 表之间建立了关系。这是因为 line_items 实际是 orders 表和cookies 表之间的一个关联表，其中包含一些额外的数据。关联表用于支持两个其他表之间的多对多关系。表上的单个外键（ForeignKey）通常表示一对多的关系，但是，如果一个表上存在多个外键关系，那么它很可能就是关联表。

示例 1-3　定义更多表

from sqlalchemy import ForeignKey
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id')), ➊
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)

➊ 请注意，在这个列上，我们使用的是一个字符串，而不是对列的实际引用。

使用字符串而非实际列，这允许我们跨多个模块分离表定义，而且不必担心表的加载顺序。这是因为 SQLAlchemy 只会在第一次访问表名和列时对该字符串执行解析。如果在ForeignKey 定义中使用了硬引用（hardreference），比如 cookies.c.cookie_id，那它会在模块初始化期间执行解析，并且有可能失败，这取决于表的加载顺序。

你还可以显式地定义一个 ForeignKeyConstraint。外键约束在试图匹配现有数据库模式，以便与 SQLAlchemy 一起使用时会很有用。这与以前创建键、约束和索引以匹配名称模式等的工作方式相同。在表定义中定义外键约束之前，需要先从 sqlalchemy 模块导入ForeignKeyConstraint。下面的代码演示了如何为 line_items 和 orders 表之间的 order_id字段创建 ForeignKeyConstraint：

ForeignKeyConstraint(['order_id'], ['orders.order_id'])

到目前为止，我们一直以 SQLAlchemy 能够理解的方式定义表。如果你的数据库已经存在，并且已经构建好了模式，那接下来你可以着手编写查询了。但是，如果你需要创建完整的模式或添加一个表，就需要知道如何持久化表以实现永久性存储。

表的持久化

事实上，所有表和模式定义都与 metadata 的实例有关。要想将模式持久化到数据库中，只需调用 metadata 实例的 create_all() 方法，并提供创建表的引擎即可：

metadata.create_all(engine)

默认情况下，create_all 不会尝试重新创建数据库中已经存在的表，并且它可以安全地运行多次。使用 Alembic 这样的数据库迁移工具来处理对现有表或额外模式所做的更改，要比直接在应用程序代码中进行编码更改更明智（第 11 章会详细地讲解相关内容）。现在我们已经对数据库中的表做了持久化，接下来看看示例 1-4，里面是我们在本文中处理的表的完整代码。

示例 1-4　内存中完整的 SQLite 代码

from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey, create_engine)
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2))
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('customer_number', Integer(), autoincrement=True),
Column('username', String(15), nullable=False, unique=True),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id'))
Column('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine)

总结

在本文中，我们学习了 SQLAlchemy 如何将元数据用作目录来存储表模式和其他数据，以及如何定义一个拥有多个列和约束的表。我们了解了约束的类型，以及如何在列对象之外显式地构造它们，以匹配现有的模式或命名方案。然后讨论了如何为审计设置默认值和onupdate 值。最后学习了如何把模式持久化或保存到数据库中以供重用。

——本文节选自《SQLAlchemy：Python数据库实战（第2版）》

SQLAlchemy 功能强大并且十分灵活，但也有点令人望而生畏。SQLAlchemy 教程只讲了这个优秀库中的一小部分内容。另外，尽管在线文档非常多，但大都不适合用来学习 SQLAlchemy，而是更适合用作参考资料。本书既是一本 SQLAlchemy 学习指南，也是一本方便的参考手册，每当在工作中碰到问题，你都可以拿来翻一翻，相信你能很快地从中找到答案。

本书重点讲解 SQLAlchemy 1.0 版本，但所讲的大部分内容同样适用于之前的多个版本。对于 0.8 之后的版本，本书示例只需做小幅调整即可运行，并且大部分示例都要求 SQLAlchemy 版本号不低于 0.5。

本书主要分为三个部分：SQLAlchemy Core、SQLAlchemy ORM 和 Alembic。其中，前两个部分相互对照。我们特意在每个部分使用相同的示例，以方便你比较 SQLAlchemy 的两种主要用法。阅读本书时，你既可以通读 SQLAlchemy Core 和 SQLAlchemy ORM 这两部分内容，也可以只阅读适合你当前需要的那部分。

更多图书咨询请点击：

https://www.ituring.com.cn/book/1986