在许多应用程序中，我们需要持久化大量不同的对象。我们在第 10 章、序列化和保存-J**子、YAML、Pickle、CSV 和 XML中看到的技术偏向于单个单一单一对象的持久化。有时，我们需要从一个更大的域中持久化单独的对象。例如，我们可能正在保存博客条目、博客帖子、作者和广告；每一个都必须单独处理。
在第 11 章中通过 Shelve存储和检索对象，我们研究了在shelve数据存储中存储不同的 Python 对象。这使我们能够在单个对象的域上实现 CRUD 处理。可以创建、检索、更新或删除每个对象，而无需加载和转储整个文件。
在本章中，我们将介绍如何将 Python 对象映射到关系数据库；特别是与 Python 捆绑的sqlite3数据库。这将是三层架构设计模式的另一个例子。
在这种情况下，SQLite 数据层是比shelve更复杂的数据库。SQLite 可以通过锁定允许并发数据库更新。SQLite 提供了一个基于 SQL 语言的访问层。它通过将 SQL 表保存到文件系统来提供持久化。Web 应用程序就是一个例子，其中使用数据库而不是简单的文件持久化来处理对单个数据池的并发更新。RESTful 数据服务器也经常使用关系数据库来提供对持久对象的访问。
为了实现可扩展性，可以使用独立的数据库服务器进程隔离所有数据库事务。这意味着可以将持久化分配给一个相对安全的主机，与 web 应用程序服务器分开，并位于适当的防火墙后面。例如，MySQL 可以实现为一个独立的服务器进程。
作为 Python 一部分的 SQLite3 数据库不是独立的数据库服务器；它必须嵌入到主机应用程序中。
在本章中，我们将介绍以下主题：
- SQL 数据库、持久化和对象
- 用 SQL 处理应用程序数据
- 将 Python 对象映射到 SQLite BLOB 列
- 手动将 Python 对象映射到数据库行
- 利用指数提高绩效
- 添加 ORM 层
- 查询给定标记的过去
- 利用指数提高绩效
本章的代码文件可在上找到 https://git.io/fj2UK 。
使用 SQLite 时，我们的应用程序使用基于 SQL 语言的隐式访问层。SQL 语言是从面向对象编程非常罕见的时代遗留下来的。它严重偏向于过程编程。关系设计的行和列概念与更复杂的数据对象模型产生了所谓的阻抗失配。在 SQL 数据库中，我们通常关注三层数据建模，如下所示：
- 概念模型：这些是 SQL 模型隐含的实体和关系。这些可能不是表和列，而是表和列的视图。视图可能涉及选择行、投影列或将多个表连接到概念结果中。在大多数情况下，概念模型可以映射到 Python 对象，并且应该与应用层的数据模型层相对应。这就是对象关系映射（ORM层）有用的地方。
- 逻辑模型：这些是 SQL 数据库中出现的表、行和列。我们将在 SQL 数据操作语句中处理这些实体。我们说这些看起来是存在的，因为表和列是由物理模型实现的，该物理模型可能与数据库模式中定义的对象有所不同。例如，SQL 查询的结果看起来像表，但可能不涉及与任何单个已定义表的存储并行的存储。
- 物理模型：持久物理存储的文件、块、页、位、字节。这些实体由管理 SQL 语句定义。在一些更复杂的数据库产品中，我们可以对数据的物理模型进行一些控制，以进一步调整性能。然而，在 SQLite 中，我们几乎无法控制这一点。
在使用 SQL 数据库时，我们面临许多设计决策。也许最重要的是决定如何覆盖阻抗失配。我们如何处理 SQL 遗留数据模型到 Python 对象模型之间的映射？以下是三种常见的策略：
- 到 Python 的最小映射：这意味着我们不会从从数据库检索的行构建 Python 对象。该应用程序将完全在独立原子数据元素和处理函数的 SQL 框架内工作。遵循这种方法往往避免深入强调面向对象编程。这种方法将我们限制为四种基本的 SQLite 类型
NULL、INTEGER、REAL和TEXT，再加上 Python 添加的datetime.date和datetime.datetime。虽然这对于更复杂的应用程序来说可能很困难，但在进行数据库维护和支持时，它适用于某些类型的脚本。 - 手动映射到 Python：我们可以定义一个访问层，在 Python 类定义和表、列、行和键的 SQL 逻辑模型之间进行映射。对于某些特殊目的，这可能是必要的。
- ORM 层：我们可以下载并安装一个 ORM 层来处理 Python 对象和 SQL 逻辑模型之间的映射。有大量的 ORM 选择；我们将把 SQLAlchemy 作为一个代表性的例子。ORM 层通常是最简单和最通用的方法
我们将在下面的示例中查看所有三种选择。在我们研究从 SQL 到对象的映射之前，我们将详细研究 SQL 逻辑模型，并首先介绍无映射、纯 SQL 设计策略。
从概念上讲，SQL 数据模型基于具有命名列的命名表。表定义是列的平面列表，数据没有其他结构。每一行本质上是一个可变的 @dataclass 。我们的想法是将表中的内容想象成单个对象的list。关系模型可以用 Python 类型的提示来描述，就好像它有类似于以下的定义一样：
from dataclasses import dataclass
from typing import Union, Text
import datetime
SQLType = Union[Text, int, float, datetime.datetime, datetime.date, bytes]
@dataclass
class Row:
column_x: SQLType
...
Table = Union[List[Row], Dict[SQLType, Row]] 从类型提示中，我们可以看到数据库Table可以被看作是Row实例的列表，或者是将列映射到Row实例的Dict。Row的定义是 SQLType 列定义的集合。关系模型还有很多内容，但这个小小的概述为我们在 Python 中使用数据库提供了一些建议。SQL 数据库的原子数据类型相对较少。通常，唯一可用的数据结构是Table，它是行的集合。每个Row都是单个列定义的简单列表。Table中的数据可以用作Row对象的简单列表。当我们选择列为键，其余列为值时，我们可以考虑该表的行为类似于字典或从键到行值的映射。我们没有在概念类型定义中添加额外的细节，如多列键和列值的可空性。
更复杂的数据结构是通过一个表中的行包含对另一个表中的行的引用来构建的。这些引用只是围绕着由单独的表共享的公共值构建的。当一个表中的一行键被另一个表中的行引用时，我们已经有效地定义了一个分层的一对多关系；这将实现 Python 嵌套列表或嵌套字典。我们可以使用中间关联表定义多对多关系，该表提供了每个关联表的密钥对列表。可以使用具有唯一主键的表来构建集合，以确保仅将唯一值收集到表中。
当我们定义一个 SQL 数据库时，我们定义一个表及其列的集合。当我们使用 SQL 数据库时，我们操作收集到表中的数据行。
在 SQLite 的例子中，SQL 将处理的数据类型范围很窄。SQLite 处理NULL、INTEGER、REAL、TEXT和BLOB数据。
None、int、float、str和bytes的 Python 类型映射到这些 SQL 类型。类似地，当从 SQLite 数据库获取这些类型的数据时，这些项将转换为 Python 对象。
BLOB类型是二进制大对象，是 SQL 之外定义的类型的字节集合。这可用于将特定于 Python 的数据类型引入 SQL 数据库。SQLite 允许我们添加转换函数，用于将 Python 对象编码和解码为字节。sqlite3模块已经通过这种方式为我们添加了datetime.date和datetime.datetime扩展。我们可以添加更多转换函数以进行更专门的处理。
SQL 语言可以分为三个子语言：数据定义语言（DDL）、数据操作语言（DML）和数据控制语言（DCL）。DDL 用于定义表、表的列和索引。以 DDL 为例，我们可以用以下方式定义一些表：
CREATE TABLE blog(
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT
);
CREATE TABLE post(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date TIMESTAMP,
title TEXT,
rst_text TEXT,
blog_id INTEGER REFERENCES blog(id)
);
CREATE TABLE tag(
id INTEGER PRIMARY KEY AUTOINCREMENT,
phrase TEXT UNIQUE ON CONFLICT FAIL
);
CREATE TABLE assoc_post_tag(
post_id INTEGER REFERENCES post(id),
tag_id INTEGER REFERENCES tag(id)
); 我们创建了四个表来表示微博应用程序的Blog和Post对象。有关 SQLite 处理的 SQL 语言的更多信息，请参见http://www.sqlite.org/lang.html 。对于更广泛的 SQL 背景，如创建 MySQL 数据库：实用设计技巧和技术等书籍将在 MySQL 数据库的上下文中介绍 SQL 语言。SQL 语言不区分大小写。
没有什么好的理由，我们更喜欢看到所有大写的 SQL 关键字，以区别于周围的 Python 代码。
blog表定义了一个带有AUTOINCREMENT选项的主键；这将允许 SQLite 分配键值，使我们不必在代码中生成键值。title栏是博客的标题。我们将其定义为TEXT。在某些数据库产品中，我们必须为字符串提供最大大小。这可以帮助数据库引擎优化存储。因为 SQLite 中不需要这个大小，所以我们将避免混乱。
post 表定义了文章正文的主键以及日期、标题和 RST 文本。请注意，在此表定义中，我们没有引用 post 的标记。我们将返回到以下 SQL 表所需的设计模式。然而，post表确实包含一个正式的REFERENCES子句，向我们表明这是对所属blog的外键引用。
tag表定义了单个标记文本项，仅此而已。SQL 将文本列定义为唯一的。尝试插入副本将导致事务失败。
最后，我们在post和tag之间有一个关联表。此表只有两个外键。它将标签和帖子关联起来，允许每个帖子有无限数量的标签，也允许无限数量的帖子共享一个公共标签。此关联表是处理这种多对多关系的常用 SQL 设计模式。在下一节中，我们将研究其他一些 SQL 设计模式。我们可以执行前面的定义来创建数据库：
import sqlite3
database = sqlite3.connect('p2_c11_blog.db')
database.executescript(sql_ddl) 所有数据库访问都需要使用模块功能sqlite3.connect()创建的连接。我们提供了要分配给数据库的文件名。
Python 数据库模块的 DB-API 标准由 PEP 249 定义，可在上获得 https://www.python.org/dev/peps/pep-0249/ 。本标准假定应用程序进程将连接到一个单独的数据库服务器进程。对于 SQLite，实际上没有单独的过程。然而，使用connect()功能是为了符合标准。上例中的sql_ddl变量只是一个长字符串变量，包含四条CREATE TABLE语句。如果没有错误消息，则表示已经定义了表结构。
Python 标准库中将Connection对象的executescript()方法描述为非标准快捷方式。从技术上讲，数据库操作必须涉及创建一个cursor对象。以下是一种标准化方法：
from contextlib import closing
with closing(database.cursor()) as cursor:
for stmt in sql_ddl.split(";"):
cursor.execute(stmt) 虽然在概念上有帮助，但这并不实际。当注释或文本文本中有";"字符时，它不起作用。最好使用方便的executescript()快捷方式。如果我们担心到其他数据库的可移植性，我们会将重点转移到更严格遵守 Python DB-API 规范上。在查看查询时，我们将在下一节中返回到游标对象的性质。
在下一节中，我们将讨论通过 SQL DML 语句进行 CRUD 处理。
以下四个规范 CRUD 操作直接映射到 SQL 语言语句：
- 创建是通过
INSERT语句完成的。 - 通过
SELECT语句进行检索。 - 更新通过
UPDATE语句完成。一些 SQL 方言还包括一个REPLACE语句。 - 通过
DELETE语句进行删除。
我们必须注意的是，我们经常会在提供所有值的情况下查看文本 SQL 语法。这与使用绑定变量占位符而不是文字值的 SQL 语法不同。文字 SQL 语法对于脚本是可接受的；这对于应用程序编程来说是非常糟糕的。在应用程序中构建文字 SQL 语句涉及无休止的字符串操作和著名的安全问题。参见上的 XKCD 漫画 http://xkcd.com/327/ 针对从用户提供的文本中汇编文字 SQL 的特定安全问题。我们将专门关注带有绑定变量的 SQL。
文字 SQL 广泛用于脚本。使用用户提供的文本构建文字 SQL 是一个错误。
Never build literal SQL DML statements with string manipulation. It is very high risk to attempt to sanitize user-supplied text.
Python DB-API 接口定义了几种将应用程序变量绑定到 SQL 语句中的方法。SQLite 可以将位置绑定与?一起使用，也可以将命名绑定与:name一起使用。我们将向您展示这两种类型的绑定变量。我们使用一个INSERT语句来创建一个新的BLOG行，如下面的代码片段所示：
create_blog = """
INSERT INTO blog(title) VALUES(?)
"""
with closing(database.cursor()) as cursor:
cursor.execute(create_blog, ( "Travel Blog" ,))
database.commit()我们为blog表的title列创建了一条 SQL 语句，其中包含一个位置绑定变量?。游标对象用于在绑定值元组后执行语句。只有一个绑定变量，因此元组中只有一个值。语句执行后，数据库中就有一行了。最后一次提交将使此更改持久化，释放所有持有的锁
我们以三倍引号的长字符串文本将 SQL 语句与周围的 Python 代码清楚地分开。在某些应用程序中，SQL 存储为单独的配置项。保持 SQL 分离最好作为从语句名到 SQL 文本的映射来处理。这可以通过将 SQL 排除在 Python 编程之外来简化应用程序维护。
DELETE和UPDATE语句需要一个WHERE子句来指定哪些行将被更改或删除。要更改博客标题，我们可以执行以下操作：
update_blog = """
UPDATE blog SET title=:new_title WHERE title=:old_title
"""
with closing(database.cursor()) as cursor:
cursor.execute(
update_blog,
dict (
new_title = "2013-2014 Travel" ,
old_title = "Travel Blog"
)
)
database.commit()此处显示的UPDATE语句有两个命名的绑定变量：:new_title和:old_title。此事务将更新blog表中具有给定旧标题的所有行，并将标题设置为新标题。理想情况下，标题是唯一的，并且只触及一行。SQL 操作被定义为对行集进行操作。确保所需的行是集合的内容是数据库设计的问题。因此，建议每个表都有一个唯一的主键。
在实现删除操作时，我们总是有两种选择。我们可以在子项仍然存在时禁止删除父项，也可以级联删除父项以同时删除相关子项。我们将看一看blog、post和tag关联的级联删除。下面是一系列的陈述：
delete_post_tag_by_blog_title = """
DELETE FROM assoc_post_tag
WHERE post_id IN (
SELECT DISTINCT post_id
FROM blog JOIN post ON blog.id = post.blog_id
WHERE blog.title=:old_title)
"""
delete_post_by_blog_title = """
DELETE FROM post WHERE blog_id IN (
SELECT id FROM BLOG WHERE title=:old_title)
"""
delete_blog_by_title ="""
DELETE FROM blog WHERE title=:old_title
"""
try:
with closing(database.cursor()) as cursor:
title = dict(old_title="2013-2014 Travel")
cursor.execute("BEGIN")
cursor.execute(delete_post_tag_by_blog_title, title)
cursor.execute(delete_post_by_blog_title, title)
cursor.execute(delete_blog_by_title, title)
database.commit()
print("Delete finished normally.")
except Exception as ex:
print(f"Rollback due to {ex!r}")
database.rollback()整个博客的DELETE操作级联为三个单独的删除操作。首先，我们根据标题删除了给定blog的assoc_post_tag中的所有行。注意delete_post_tag_by_blog_title值内的嵌套查询；我们将在下一节中查看查询。表之间的导航是 SQL 构造的常见问题。
在这种情况下，我们需要查询 blog-to-post 关系来定位将被删除的post表键；然后，我们可以从assoc_post_tag中删除与要删除的博客相关联的帖子的行。接下来，我们删除了属于某个博客的所有帖子。这也涉及一个嵌套查询，根据标题定位博客的 ID。最后，我们可以删除博客本身。
这是一个显式级联删除设计的示例，其中我们必须将操作从blog表级联到其他两个表。我们将整个 delete 套件包装在一个with上下文中，这样它就可以作为单个事务提交。如果发生故障，它将回滚部分更改，使数据库保持原样。
似乎使用数据库的executescript()方法会使这种操作受益。executescript()的问题是 SQL 语句中的所有值都必须是文字；它不绑定值。对于数据定义，它工作得很好。对于这里显示的数据操作来说，这是一个糟糕的选择，因为博客标题必须绑定到每个语句中。
让我们在下一节讨论 SQLSELECT语句的行查询。
仅就SELECT陈述就有可能写出一本内容丰富的书。我们将跳过SELECT的所有基本功能。我们的目的是覆盖足够的 SQL 来存储和检索数据库中的对象。
在前面的大多数示例中，我们创建了一个游标来执行 SQL 语句。对于 DDL 和其他 DML 语句，光标的存在与否并不重要。一种非标准快捷方式，它消除了游标的显式创建，并大大简化了 SQL 编程。
但是，对于查询，游标对于从数据库检索行是必不可少的。游标对象维护查询状态，以便可以获取多行。当光标关闭时，任何内部缓冲区或锁都会被释放。要按标题定位博客，我们可以从以下简单代码开始：
SELECT * FROM blog WHERE title=?执行 SQL 查询后，我们需要获取结果行对象集合。即使我们期望有一行作为响应，在 SQL 世界中，所有内容都是一个集合。通常，SELECT查询的每个结果集看起来都像一个表，其中包含由SELECT语句定义的行和列。
在这种情况下，使用SELECT *意味着我们避免了枚举预期的结果列。这可能会导致检索大量列。使用显式游标，我们将按如下方式执行此操作：
query_blog_by_title = """
SELECT * FROM blog WHERE title=?
"""
with closing(database.cursor()) as blog_cursor:
blog_cursor.execute(query_blog_by_title, ("2013-2014 Travel",))
for blog in blog_cursor.fetchall():
print("Blog", blog)这遵循前面创建游标并使用绑定值执行 SQL 查询语句的模式。游标的fetchall()方法将检索所有结果行。此查询将处理标题相同的所有博客。
以下是使用 SQLite 快捷方式进行此操作的常见优化：
query_blog_by_title= """
SELECT * FROM blog WHERE title=?
"""
cursor = database.execute(
query_blog_by_title, ("2013-2014 Travel",))
for blog in cursor:
print(blog[0], blog[1]) 我们已经将请求的博客标题绑定到了SELECT语句中的"?"参数。execute()函数的结果将是一个游标对象。当用作 iterable 时，游标将生成结果集中的所有行，并在迭代完成时关闭游标。
这种缩写形式对于查询或简单事务来说很方便。对于更复杂的事务，如级联删除，则需要一个带有commit()的显式游标，以确保事务要么完全执行，要么根本不执行。接下来我们将研究 SQL 事务的语义。
正如我们所看到的，SQLDML 语句映射到 CRUD 操作。在讨论 SQL 事务的特性时，我们将看到INSERT、SELECT、UPDATE和DELETE语句的序列。
酸性质为原子、一致、分离、持久。这些是由多个数据库操作组成的事务的基本特性。更多信息请参见第 11 章通过货架存储和检索对象。
所有 SQL DML 语句都在 SQL 事务的上下文中运行。整个事务必须作为一个整体提交，或者作为一个整体回滚。这通过创建从一个一致状态到下一个一致状态的单个、原子的、不可分割的更改来支持原子性属性。
SQL DDL 语句（即，CREATE、DROP在事务中不起作用。它们隐式地结束任何先前的进程内事务。毕竟，他们正在改变数据库的结构；它们是一种不同的语句，事务概念并不真正适用。每个 SQL 数据库产品在创建表或索引时是否需要提交方面略有不同。发出一个database.commit()不会有伤害；对于某些数据库引擎，它可能是必需的。
除非在特殊的读取未提交模式下工作，否则每个到数据库的连接都会看到数据的一致版本，其中只包含已提交事务的结果。未提交的事务通常对其他数据库客户端进程不可见，支持一致性属性。
SQL 事务还支持隔离属性。SQLite 支持多种不同的隔离级别设置。隔离级别定义了 SQL DML 语句如何在多个并发进程之间交互。这取决于锁的使用方式以及进程的 SQL 请求如何延迟以等待锁。在 Python 中，隔离级别是在连接到数据库时设置的。
每个 SQL 数据库产品都采用不同的隔离级别和锁定方法。没有单一的模式。
对于 SQLite，有四个隔离级别定义事务的锁定和性质。详见http://www.sqlite.org/isolation.html 。
以下项目列表介绍了这些隔离级别：
isolation_level=None：默认为自动提交模式。在这种模式下，每个单独的 SQL 语句在执行时都会提交到数据库。这可以打破复杂事务的原子性。但是，对于数据仓库查询应用程序来说，它是理想的。isolation_level='DEFERRED'：在这种模式下，锁是在交易后期获取的。例如，BEGIN语句不会立即获取任何锁。其他读取操作（例如，SELECT语句）将获取共享锁。写入操作将获取保留锁。虽然这可以最大限度地提高并发性，但也可能导致竞争事务进程之间的死锁。isolation_level='IMMEDIATE'：在此模式下，事务BEGIN语句获取一个锁，该锁阻止所有写入。但是，读取将正常继续。这避免了死锁，在事务可以快速完成时效果良好。isolation_level='EXCLUSIVE'：在此模式下，事务BEGIN语句获取一个锁，该锁阻止除特殊读取未提交模式下的连接之外的所有访问
对于所有提交的事务，耐久性属性都有保证。数据被写入数据库存储器。
SQL 规则要求我们执行BEGIN TRANSACTION和COMMIT TRANSACTION语句以包含一系列步骤。如果发生错误，需要一个ROLLBACK TRANSACTION语句来释放潜在的更改。Python 接口简化了这一点。我们可以执行 SQLBEGIN语句。其他语句作为sqlite3.Connection对象的函数提供；我们不会执行 SQL 语句来结束事务。我们可能会编写类似于以下代码的明确代码：
database = sqlite3.connect('p2_c11_blog.db', isolation_level='DEFERRED')
try:
with closing(database.cursor()) as cursor:
cursor.execute("BEGIN")
# cursor.execute("some statement")
# cursor.execute("another statement")
database.commit()
except Exception as e:
database.rollback()我们在进行数据库连接时选择了一个隔离级别DEFERRED。这就要求我们明确地开始和结束每个事务。一个典型的场景是将相关的 DML 封装在try块中，并在工作正常时提交事务，或者在出现问题时回滚事务。我们可以通过使用sqlite3.Connection对象作为上下文管理器来简化此过程，如下所示：
database = sqlite3.connect('p2_c11_blog.db', isolation_level='DEFERRED')
with database:
database.execute("some statement")
database.execute("another statement") 这与前面的示例类似。我们用同样的方法打开了数据库。我们没有执行一个明确的BEGIN语句，而是进入了一个上下文；上下文为我们执行BEGIN语句。
在with上下文结束时，database.commit()将自动完成。如果发生异常，将执行一个database.rollback()，并且异常将由with语句引发。
这种速记方式在 web 服务器中很有用，因为在 web 服务器中，连接只需要持续一个 web 请求的时间。在执行查询时，隔离级别可以保留为默认值，从而可以非常快速地访问数据
在下一节中，我们将设计主数据库键和外部数据库键。
SQL 表不需要主键。然而，忽略给定表行的主键是一种相当糟糕的设计。正如我们在第 11 章中所述，通过 Shelve存储和检索对象，可能有一个属性（或属性组合）构成一个合适的主键。也完全可能没有属性适合作为主键，我们必须定义代理键。
前面的示例使用 SQLite 创建的代理键。这可能是最简单的设计，因为它对数据施加的约束最少。如果我们不生成代理键，那么主键值就无法更新；这成为应用程序编程必须执行的规则。如果需要更正主键值中的错误，则会创建复杂的边缘情况。一种方法是删除并重新创建约束。另一种方法是删除有故障的行，然后用正确的键重新插入该行。
当存在级联删除时，更正主键所需的事务可能会变得非常复杂。使用代理密钥可以防止此类问题。表之间的所有关系都是通过主键和外键引用完成的。关系有两种非常常见的设计模式。前面的表格向我们展示了这两种主要的设计模式。关系有三种设计模式，如下所示：
- 一对多：多个子对象属于单亲对象。例如，这是单亲博客和多个子博客之间的关系。
REFERENCES子句显示post表中的许多行将引用blog表中的一行。 - 多对多：这种关系存在于多个帖子和多个标签之间。这需要在
post和tag表之间建立一个中间关联表；中间表有两个（或更多）外键关联。多对多关联表也可以有自己的属性。 - 一对一：这种关系是一种不太常见的设计模式。一对多的关系没有技术上的区别。这是关系子端行的基数问题。要强制执行一对一关系，某些处理必须阻止创建其他子级。
在数据库设计中，对关系有几种约束：关系可以描述为可选的或强制性的；这种关系可能有基数限制。有时，这些可选性和基数约束会用简短的描述来概括，例如0:m表示零对多或可选一对多。可选性和基数约束通常是应用程序编程逻辑的重要部分；在 SQLite 数据库中没有正式的方法来声明这些约束。基本表关系可以通过以下任一种或两种方式在数据库中实现：
- 显式：我们可以称之为声明的，因为它们是数据库 DDL 声明的一部分。理想情况下，它们由数据库服务器强制执行，不遵守关系的约束可能会导致某种错误。这些关系也将在查询中重复。
- 隐式：这些关系仅在查询中声明；它们不是 DDL 的正式部分。
请注意，我们的表定义在博客和该博客中的各种条目之间实现了一对多关系。我们在编写的各种查询中使用了这些关系。
在下一节中，我们将看到如何使用 SQL 处理应用程序数据。
前面几节中的示例向我们展示了我们可以称之为过程SQL 处理的内容。我们已经从问题域对象中避开了任何面向对象的设计。我们使用的不是Blog和Post对象，而是 SQLite 可以处理字符串、日期、浮点和整数值的数据元素。我们主要使用过程式编程。
我们可以看到，可以通过一系列查询来定位博客、博客中的所有帖子以及与博客相关的帖子相关联的所有标签。检索完整博客、所有帖子和帖子上的所有标记的处理过程如下所示：
query_blog_by_title = """
SELECT * FROM blog WHERE title=?
"""
query_post_by_blog_id = """
SELECT * FROM post WHERE blog_id=?
"""
query_tag_by_post_id = """
SELECT tag.*
FROM tag
JOIN assoc_post_tag ON tag.id = assoc_post_tag.tag_id
WHERE assoc_post_tag.post_id=?
"""
with closing(database.cursor()) as blog_cursor:
blog_cursor.execute(query_blog_by_title, ("2013-2014 Travel",))
for blog in blog_cursor.fetchall():
print("Blog", blog)
with closing(database.cursor()) as post_cursor:
post_cursor.execute(query_post_by_blog_id, (blog[0],))
for post in post_cursor:
print("Post", post)
with closing(database.cursor()) as tag_cursor:
tag_cursor.execute(query_tag_by_post_id, (post[0],))
for tag in tag_cursor.fetchall():
print("Tag", tag)我们定义了三个 SQL 查询。第一个按标题获取博客。对于每个博客，我们获取了属于该博客的所有帖子。最后，我们获取了与给定帖子关联的所有标记。
第二个查询隐式地重复post表和blog表之间的REFERENCES定义。我们正在查找特定博客家长的子帖子；我们需要在查询期间重复一些表定义。
第三个查询涉及assoc_post_tag表和tag表的行之间的关系联接。JOIN子句概括了表定义中的外键引用。WHERE子句还重复了表定义中的REFERENCES子句。
因为在第三个查询中连接了多个表，所以使用SELECT *将从所有表中生成列。我们只对TAG表的属性感兴趣，所以我们使用SELECT TAG.*只生成所需的列。
这些查询为我们提供了所有单独的位和数据片段。但是，这些查询不会为我们重建 Python 对象。如果我们有更复杂的类定义，我们必须从检索到的单个数据片段构建对象。特别是，如果我们的 Python 类定义有重要的方法函数，那么我们需要更好的 SQL 到 Python 映射来使用更完整的 Python 类定义。
在下一节中，我们将看到如何在纯 SQL 中实现类处理。
让我们看一看Blog类的更复杂的定义。此定义重复于第 10 章、序列化和保存—JSON、YAML、Pickle、CSV 和 XML，以及第 11 章、通过 Shelve存储和检索对象。这一定义如下：
from dataclasses import dataclass, field, asdict
@dataclass
class Blog:
title: str
underline: str = field(init=False)
# Part of the persistence, not essential to the class.
_id: str = field(default="", init=False, compare=False)
def __post_init__(self) -> None:
self.underline = "=" * len(self.title)此数据类提供博客的基本标题属性。它有一个可选属性，该属性将内部数据库 ID 分配给博客条目。
下面是一个用来检索Blog和Post对象的Access类的开始：
class Access:
def open( self , path: Path) -> None :
self .database = sqlite3.connect(path)
self .database.row_factory = sqlite3.Row
def get_blog( self , id: str ) -> Blog:
query_blog = """
SELECT * FROM blog WHERE id=?
"""
row = self .database.execute(query_blog, (id,)).fetchone()
blog = Blog(title=row["TITLE"])
blog._id = row["ID"]
return blog这个Access类有一个从关系数据库中的列创建Blog对象的方法。__post_init__()方法将创建预期的underline属性值。这显示了从关系数据到对象的基本技术。
检索与Blog实例关联的Post实例并不简单。它需要使用access对象从数据库中获取更多行。它不能通过Blog类中单独定义的某种属性直接完成。我们将在查看访问层和对象关系管理（ORM时更深入地了解这一点。
我们可以将 SQL 列映射到类定义，以便从数据库中的数据创建适当的 Python 对象实例。SQLite 包含一个二进制大对象（BLOB数据类型。我们可以对 Python 对象进行 pickle，并将它们存储在 BLOB 列中。我们可以计算 Python 对象的字符串表示（例如，使用 JSON 或 YAML 表示法），也可以使用 SQLite 文本列。
必须谨慎使用此技术，因为它有效地挫败了 SQL 处理。BLOB 列不能用于 SQL DML 操作。我们不能索引它或在 DML 语句的搜索条件中使用它。
SQLite BLOB 映射应该保留给那些对周围 SQL 处理不透明的对象。最常见的例子是媒体对象，如视频、静止图像或声音片段。SQL 偏向于文本和数字字段。它通常不会处理更复杂的对象。
如果我们使用的是财务数据，我们的应用程序应该使用decimal.Decimal值。原因在第 8 章、创造数字中讨论；使用浮动值执行货币计算时将不正确。我们可能希望使用这种数据在 SQL 中进行查询或计算。由于 SQLite 不直接支持decimal.Decimal，我们需要扩展 SQLite 来处理这种类型的值。
这有两个方向：转换和适应。我们需要将Python 数据适配到 SQLite，并且我们需要将SQLite 数据转换回 Python。以下是两个函数及其注册请求：
import decimal
def adapt_currency(value):
return str(value)
sqlite3.register_adapter(decimal.Decimal, adapt_currency)
def convert_currency(bytes):
return decimal.Decimal(bytes.decode())
sqlite3.register_converter("DECIMAL", convert_currency) 我们已经编写了一个adapt_currency()函数，它将decimal.Decimal对象调整为适合数据库的形式。在本例中，我们只做了一个简单的字符串转换。我们已经注册了适配器函数，以便 SQLite 的接口可以使用注册的适配器函数转换类decimal.Decimal的对象。我们还编写了一个convert_currency()函数，将 SQLite 字节对象转换为 Pythondecimal.Decimal对象。我们已经注册了converter函数，因此DECIMAL类型的列将正确地转换为 Python 对象。
一旦定义了适配器和转换器，我们就可以使用DECIMAL作为完全受支持的列类型。为了使其正常工作，我们必须在进行数据库连接时通过设置detect_types=sqlite3.PARSE_DECLTYPES通知 SQLite。以下是使用新列数据类型的表定义：
CREATE TABLE budget(
year INTEGER,
month INTEGER,
category TEXT,
amount DECIMAL
) 与文本字段一样，SQLite 不需要最大大小。其他数据库产品需要一定的大小来优化存储和性能。我们可以这样使用新的列类型定义：
database = sqlite3.connect('p2_c11_blog.db', detect_types=sqlite3.PARSE_DECLTYPES)
database.execute(decimal_ddl)
insert_budget= """
INSERT INTO budget(year, month, category, amount)
VALUES(:year, :month, :category, :amount)
"""
database.execute(insert_budget,
dict(year=2013, month=1, category="fuel", amount=decimal.Decimal('256.78')))
database.execute(insert_budget,
dict(year=2013, month=2, category="fuel", amount=decimal.Decimal('287.65')))
query_budget= """
SELECT * FROM budget
"""
for row in database.execute(query_budget):
print(row) 我们创建了一个数据库连接，该连接要求通过转换器函数映射声明的类型。一旦建立了连接，我们就可以使用新的DECIMAL列类型创建表。
当我们在表中插入行时，我们使用适当的decimal.Decimal对象。当我们从表中获取行时，我们将看到我们从数据库中获得了正确的decimal.Decimal对象。以下是输出：
(2013, 1, 'fuel', Decimal('256.78'))
(2013, 2, 'fuel', Decimal('287.65')) 这表明我们的decimal.Decimal对象已正确存储并从数据库中恢复。我们可以为任何 Python 类编写适配器和转换器。要做到这一点，我们需要发明一种适当的对象字节表示法。由于字符串很容易转换为字节，因此创建和解析字符串通常是最简单的方法。可以使用字符串的encode()方法从字符串创建字节。类似地，可以使用 bytesdecode()方法从字节中恢复字符串。
让我们来看看如何将 Python 对象映射到数据库行。
我们可以将 SQL 行映射到类定义，以便从数据库中的数据创建适当的 Python 对象实例。如果我们对数据库和类定义非常小心，这并不是不可能的复杂。但是，如果我们不小心，我们可以创建 SQL 表示非常复杂的 Python 对象。这种复杂性的一个结果是，在对象行和数据库行之间的映射中涉及大量查询。挑战在于在面向对象设计和 SQL 数据库施加的约束之间取得平衡。
我们必须修改我们的类定义，以便更好地了解 SQL 实现。我们将对第 11 章中所示的Blog和Post类设计进行一些修改，通过 Shelve存储和检索对象。
这里有一个Blog类定义：
@dataclass
class Blog:
title: str
underline: str = field( init = False )
# Part of the persistence, not essential to the class.
_id: str = field( default = "" , init = False , compare = False )
_access: Optional[ref] = field( init = False , repr = False , default = None , compare = False )
def __post_init__( self ) -> None :
self .underline = "=" * len ( self .title)
@property
def entries( self ) -> List[ 'Post' ]:
if self ._access and self ._access():
posts = cast( 'Access' , self ._access()).post_iter( self )
return list (posts)
raise RuntimeError("Can't work with Blog: no associated Access instance")
def by_tag( self ) -> Dict[ str , List[Dict[ str , Any]]]:
if self ._access and self ._access():
return cast( 'Access' , self ._access()).post_by_tag( self )
raise RuntimeError ( "Can't work with Blog: no associated Access instance" )Blog实例的核心元素存在：title和property提供Post条目列表。这与前面章节中显示的功能集完全匹配。
为了处理对象的持久化，可以方便地包含数据库键值。在这个定义中，它是_id字段。此字段不是初始化的一部分，也不是Blog类实例比较的一部分。将计算 underline 属性，如前面的示例所示。
entries属性依赖于可选的_access字段。这提供了对Access类的引用，该类提供数据库连接并处理来自数据库表的 SQL 映射。当检索对象时，Access类注入此值。
Post对象列表需要定义所需的类。Post类定义如下：
@dataclass
class Post:
date: datetime.datetime
title: str
rst_text: str
tags: List[ str ] = field( default_factory = list )
_id: str = field( default = "" , init = False , compare = False )
def append( self , tag):
self .tags.append(tag)字段描述个人Post；这些字段包括日期、标题、RST 表示法中的正文以及用于进一步分类帖子的标签列表。与Blog一样，我们允许数据库_id字段作为对象的一级部分。
一旦我们有了这些类定义，我们就可以编写一个访问层，在这些类的对象和数据库之间移动数据。访问层实现了一个更复杂的版本，将 Python 类转换和调整为数据库中表的行。
让我们看看如何为 SQLite 设计一个访问层。
对于这个小对象模型，我们可以在单个类中实现整个访问层。这个类将包括对每个持久类执行 CRUD 操作的方法
这个例子不会费力地包含完整访问层的所有方法。我们会给你看那些重要的。我们将把它分解成几个部分来处理Blogs、Posts和迭代器。下面是我们访问层的第一部分：
# An access layer to map back and forth between Python objects and SQL rows.
class Access:
get_last_id = """
SELECT last_insert_rowid()
"""
def open( self , path: Path) -> None :
self .database = sqlite3.connect(path)
self .database.row_factory = sqlite3.Row
def get_blog( self , id: str ) -> Blog:
query_blog = """
SELECT * FROM blog WHERE id=?
"""
row = self .database.execute(query_blog, (id,)).fetchone()
blog = Blog( title =row[ "TITLE" ])
blog._id = row[ "ID" ]
blog._access = ref( self )
return blog
def add_blog( self , blog: Blog) -> Blog:
insert_blog = """
INSERT INTO blog(title) VALUES(:title)
"""
self .database.execute(insert_blog, dict ( title =blog.title))
row = self .database.execute( self .get_last_id).fetchone()
blog._id = str (row[ 0 ])
blog._access = ref( self )
return blog除了打开数据库，open()方法将Connection.row_factory设置为使用sqlite3.Row类，而不是简单的元组。Row类允许通过数字索引以及列名进行访问。
get_blog()方法从获取的数据库行构造一个Blog对象。因为我们使用的是sqlite3.Row对象，所以我们可以按名称引用列。这澄清了 SQL 和 Python 类之间的映射。另外两个属性_id和_access必须分别设置；它们是访问层的一部分，而不是问题域的中心。
add_blog()方法根据Blog对象的值在blog表中插入一行。这是一个三步操作。首先，我们创建新行。然后，我们执行 SQL 查询以获取分配给该行的行键。最后，使用分配的数据库密钥和对Access实例的引用更新原始博客实例。
请注意，我们的表定义使用INTEGER PRIMARY KEY AUTOINCREMENT。因此，表的主键是行的_id属性，分配的行键将通过last_insert_rowid()函数可用。这允许我们检索 SQLite 创建的行键；然后，我们可以将其放入 Python 对象中以供将来参考。以下是我们如何从数据库中检索单个Post对象：
def get_post( self , id: str ) -> Post:
query_post = """
SELECT * FROM post WHERE id=?
"""
row = self .database.execute(query_post, (id,)).fetchone()
post = Post(
title =row[ "TITLE" ], date =row[ "DATE" ], rst_text =row[ "RST_TEXT" ]
)
post._id = row[ "ID" ]
# Get tag text, too
query_tags = """
SELECT tag.*
FROM tag JOIN assoc_post_tag ON tag.id = assoc_post_tag.tag_id
WHERE assoc_post_tag.post_id=?
"""
results = self .database.execute(query_tags, (id,))
for tag_id, phrase in results:
post.append(phrase)
return post为了构建Post，我们有两个查询：首先，我们从post表中获取一行来构建Post对象的一部分。这包括将数据库 ID 注入到结果实例中。然后，我们从tag表中获取与这些行连接的关联行。这用于为Post对象构建标记列表。
当我们保存一个Post对象时，它也会有几个部分。必须在POST表中添加一行。此外，需要将行添加到assoc_post_tag表中。如果标签是新的，那么可能需要将一行添加到tag表中。如果标记存在，那么我们只需将 post 与现有标记的键相关联。以下是add_post()方法函数：
def add_post( self , blog: Blog, post: Post) -> Post:
insert_post = """
INSERT INTO post(title, date, rst_text, blog_id) VALUES(:title, :date, :rst_text, :blog_id)
"""
query_tag = """
SELECT * FROM tag WHERE phrase=?
"""
insert_tag = """
INSERT INTO tag(phrase) VALUES(?)
"""
insert_association = """
INSERT INTO assoc_post_tag(post_id, tag_id) VALUES(:post_id, :tag_id)
"""
try :
with closing( self .database.cursor()) as cursor:
cursor.execute(
insert_post,
dict (
title =post.title,
date =post.date,
rst_text =post.rst_text,
blog_id =blog._id,
),
)
row = cursor.execute( self .get_last_id).fetchone()
post._id = str (row[ 0 ])
for tag in post.tags:
tag_row = cursor.execute(query_tag, (tag,)).fetchone()
if tag_row is not None :
tag_id = tag_row[ "ID" ]
else :
cursor.execute(insert_tag, (tag,))
row = cursor.execute( self .get_last_id).fetchone()
tag_id = str (row[ 0 ])
cursor.execute(
insert_association,
dict ( tag_id =tag_id, post_id =post._id)
)
self .database.commit()
except Exception as ex :
self .database.rollback()
raise
return post在数据库中创建完整 post 的过程涉及几个 SQL 步骤。我们已经使用了insert_post语句在post表中创建了行。我们还将使用通用的get_last_id查询返回为新的post行分配的主键。
query_tag语句用于确定数据库中是否存在该标记。如果查询结果不是None，则表示找到了tag行，我们有该行的 ID。否则必须使用insert_tag语句创建一行；get_last_id查询必须用于确定分配的密钥。
通过在assoc_post_tag表中插入行，每个post都与相关标签相关联。insert_association语句创建必要的行。这里有两个迭代器样式的查询来定位Blogs和Posts：
def blog_iter( self ) -> Iterator[Blog]:
query = """
SELECT * FROM blog
"""
results = self .database.execute(query)
for row in results:
blog = Blog( title =row[ "TITLE" ])
blog._id = row[ "ID" ]
blog._access = ref( self )
yield blog
def post_iter( self , blog: Blog) -> Iterator[Post]:
query = """
SELECT id FROM post WHERE blog_id=?
"""
results = self .database.execute(query, (blog._id,))
for row in results:
yield self .get_post(row[ "ID" ])blog_iter()方法函数定位所有BLOG行，并从这些行构建Blog实例。post_iter()方法函数定位与BLOGID 关联的POSTID。POSTID 与get_post()方法一起用于构建Post实例。由于get_post()将对POST表执行另一个查询，因此这两种方法之间可能存在优化。
让我们在下一节中了解如何实现容器关系。
当我们查看第 11 章中的Blog对象通过搁置存储和检索对象时，我们定义了一种by_tag()方法，以生成由相关标记字符串组织的帖子的有用字典表示。该方法有一个类型提示为Dict[str, List[Dict[str, Any]]]的定义。因为它提供了有用的信息来创建带有链接的标记，by_tag()方法是将Blog实例呈现为 RST 或 HTML 的有用部分。此外，entries属性返回附加到Blog的Post实例的完整集合
理想情况下，Blog和Post等应用程序模型类定义与访问层对象完全分离，后者将它们保存在外部存储器中。这两个用例表明模型层对象必须具有对访问层的引用。有几种策略，包括：
-
客户端类使用一个全局
Access对象来执行这些查询操作。这打破了封装的概念：Blog不再是Post条目的容器。相反，Access对象是两者的容器。类定义被简化。所有其他处理都变得更加复杂。 -
在每个
Blog对象中包含对访问层对象的引用，允许客户端类处理不知道访问层的Blog对象。这使得模型层类定义更加复杂。它使客户机的工作稍微简单一些。这种技术的优点是它使模型层对象的行为更像复杂的 Python 对象。复杂对象可能会被迫从数据库中获取子对象，但如果可以透明地执行此操作，则会使整个应用程序稍微简单一些。
作为一个具体的例子，我们将添加一个Blog.by_tag()特性。其思想是返回一个标签字典，并将信息作为一个复杂的字典发布。这需要访问层对象进行大量工作来定位和获取Post实例的字典表示。
从关系列到对象没有简单的映射。因此，Access类必须构建每个对象类。举例来说，获取Blog实例的方法如下：
def get_blog( self , id: str ) -> Blog:
query_blog = """
SELECT * FROM blog WHERE id=?
"""
row = self .database.execute(query_blog, (id,)).fetchone()
blog = Blog( id =row[ "ID" ], title =row[ "TITLE" ])
blog._access = ref( self )
return blog关系查询检索用于重新创建Blog实例的各种属性。除了创建核心字段外，每个Blog对象都有一个可选的_access属性。这在初始化时不提供，也不是Blog对象表示或比较的一部分。该值是对Access类实例的弱引用。此对象将包含执行此工作所需的相当复杂的 SQL 查询。每次检索博客实例时，access 对象都会插入此关联。
关联博客、帖子和标记需要相当复杂的 SQL 查询。下面是遍历关联并定位标记短语和相关 post 标识符所需的SELECT语句：
query_by_tag = """
SELECT tag.phrase, post.id
FROM tag
JOIN assoc_post_tag ON tag.id = assoc_post_tag.tag_id
JOIN post ON post.id = assoc_post_tag.post_id
JOIN blog ON post.blog_id = blog.id
WHERE blog.title=?
""" 此查询的结果集是一个类似表的行序列，具有两个属性：tag.phrase、post.id。SELECT语句定义了blog、post、标记和assoc_post_tag表之间的三个连接操作。这些规则通过每个JOIN规范中的ON条款提供。通过将tag.id列的值与assoc_post_tag.tag_id列的值匹配，将tag表与assoc_post_tag表连接起来。类似地，post表通过匹配post.id列的值与assoc_post_tag.post_id列的值来关联。此外，blog表通过匹配post.blog_id列的值与blog.id列的值来关联
使用此查询的Access类的by_tag()方法如下：
def post_by_tag( self , blog: Blog) -> Dict[ str , List[Dict[ str , Any]]]:
results = self .database.execute(
self.query_by_tag, (blog.title,))
tags: DefaultDict[ str , List[Dict[ str , Any]]] = defaultdict( list )
for phrase, post_id in results.fetchall():
tags[phrase].append(asdict( self .get_post(post_id)))
return tags需要注意的是，这个复杂的 SQL 查询与表定义是分离的。SQL 不是一种面向对象的编程语言。没有整洁的类将数据和处理捆绑在一起。像这样对 SQL 使用过程编程往往会破坏对象模型。
blog类的by_tag()方法使用Access类的这个方法来实际获取各种帖子。然后，客户端应用程序可以执行以下操作：
blog = some_access_object.get_blog(id=1)
tag_link = blog.by_tag() 返回的Blog实例的行为就好像它是一个简单的 Python 类定义，包含Post实例的简单集合。为了实现这一点，我们需要小心地将访问层引用注入到返回的对象中。
在下一节中，我们将看到如何使用索引提高性能。
提高关系数据库（如 SQLite）性能的方法之一是加快连接操作。实现这一点的理想方法是包含足够的索引信息，以避免执行缓慢的搜索操作来查找匹配的行。
当我们定义一个可能在查询中使用的列时，我们应该考虑为该列建立索引。这意味着在表定义中添加更多的 SQLDDL 语句。
索引是一个单独的存储器，但与特定的表和列相关联。SQL 代码如下所示：
CREATE INDEX ix_blog_title ON blog(title); 这将在blog表的title列上创建索引。其他什么都不需要做。SQL 数据库在基于索引列执行查询时将使用索引。创建、更新或删除数据时，索引将自动调整。
索引涉及存储和计算开销。很少使用的索引的创建和维护成本可能非常高，以至于它会成为性能障碍而不是帮助。另一方面，有些指标非常重要，可以显著提高性能。在所有情况下，我们都不能直接控制所使用的数据库算法；我们所能做的最好的事情就是创建索引并衡量绩效的影响。
在某些数据库产品中，将列定义为键可能会自动包括添加索引。在其他情况下，索引的存在迫使列被视为唯一键。这方面的规则通常在数据库的 DDL 部分中非常清楚地说明。例如，SQLite 的文档说明如下：
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database.
它接着列出了两个例外。其中一个是 integer 主键异常，它是我们用来强制数据库为我们创建代理键的设计模式。因此，我们的 integer 主键设计不会创建任何附加索引。
在下一节中，我们将讨论添加 ORM 层。
有相当多的 Python ORM 项目。可在中找到这些文件的列表 https://wiki.python.org/moin/HigherLevelDatabaseProgramming 。
我们将仅选取其中一个作为示例。我们将使用 SQLAlchemy，因为它为我们提供了许多特性，并且相当流行。与许多事情一样，没有最佳；其他 ORM 层有不同的优点和缺点。
由于使用关系数据库支持 web 开发的流行，web 框架通常包括 ORM 层。Django 有自己的 ORM 层，就像web.py一样。在某些情况下，我们可以从更大的框架中挑出 ORMs。但是，使用独立的 ORM 似乎更简单。
SQLAlchemy 的文档、安装指南和代码可在上找到 http://www.sqlalchemy.org 。安装时，如果不需要进行高性能优化，使用--without-cextensions可以简化过程。
需要注意的是，SQLAlchemy 可以用一流的 Python 构造完全替换应用程序的所有 SQL 语句。这有一个深刻的优势，即允许我们用一种语言（Python）编写应用程序，即使第二种语言 SQL 是作为数据访问层的一部分在后台使用的。这可以在开发和调试阶段节省一些复杂性。
然而，这并没有消除理解底层 SQL 数据库约束以及我们的设计必须如何适应这些约束的义务。ORM 层并不能神奇地消除设计方面的考虑。它只是将实现语言从 SQL 更改为 Python。
在下一节中，我们将设计一个 ORM 友好的类。
当使用 ORM 时，我们将从根本上改变设计和实现持久类的方式。我们将扩展类定义的语义，使其具有以下三个不同层次的含义：
- 该类将用于创建 Python 对象。这些对象使用方法函数。
- 该类还将描述一个 SQL 表，ORM 可以使用该类创建用于构建和维护数据库结构的 SQL DDL。属性将映射到 SQL 列。
- 该类还将定义 SQL 表和 Python 类之间的映射。它将成为将 Python 操作转换为 SQL DML 并从 SQL 查询结果构建 Python 对象的工具。
大多数 ORM 设计为使用描述符来正式定义类的属性。我们不只是在__init__()方法中定义属性。有关描述符的更多信息，请参见第 4 章、属性访问、属性和描述符。
SQLAlchemy 要求我们构建一个声明性基类。这个基类为应用程序的类定义提供了一个元类。它还充当我们为数据库定义的元数据的存储库。如果我们遵循默认设置，则很容易将该类称为Base。
以下是可能有用的导入列表：
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Table
from sqlalchemy import (
BigInteger,
Boolean,
Date,
DateTime,
Enum,
Float,
Integer,
Interval,
LargeBinary,
Numeric,
PickleType,
SmallInteger,
String,
Text,
Time,
Unicode,
UnicodeText,
ForeignKey,
)
from sqlalchemy.orm import relationship, backref我们导入了必要的定义来创建一个表的列 column，并创建一个罕见的表，该表没有专门映射到 Python 类Table。我们导入了所有的泛型列类型定义。我们将只使用其中的一些列类型。SQLAlchemy 不仅定义了这些泛型类型；它定义了 SQL 标准类型，还为各种受支持的 SQL 方言定义了特定于供应商的类型。最好坚持使用泛型类型，并允许 SQLAlchemy 在泛型、标准和供应商类型之间映射。
我们还导入了两个助手来定义表之间的关系，relationship和backref。SQLAlchemy 的元类由declarative_base()函数构建，如下所示：
Base = declarative_base() Base对象必须是我们要定义的任何持久类的超类。我们将定义三个映射到 Python 类的表。我们还将定义 SQL 实现多对多关系所需的第四个表。
以下是Blog课程：
class Blog(Base):
__tablename__ = "BLOG"
id = Column(Integer, primary_key = True )
title = Column(String)
def as_dict( self ):
return dict (
title = self .title,
underline = "=" * len ( self .title),
entries =[e.as_dict() for e in self .entries],
)我们的Blog类映射到一个名为"BLOG"的表。我们已经为这个表中需要的两列包含了两个描述符。id列定义为Integer主键。隐式地，这将是一个自动增量字段，以便为我们生成代理键。
标题列定义为通用字符串。我们可以用Text、Unicode甚至UnicodeText来做这个。对于这些不同的类型，底层引擎可能有不同的实现。在我们的例子中，SQLite 将以几乎相同的方式处理所有这些问题。还要注意，SQLite 不需要列长度的上限；其他数据库引擎可能需要对String的大小设置上限。
as_dict()方法函数指的是一个entries集合，该集合显然不是此类中定义的。当我们查看Post类的定义时，我们将看到entries属性是如何构建的。以下是Post类的定义：
class Post(Base):
__tablename__ = "POST"
id = Column(Integer, primary_key = True )
title = Column(String)
date = Column(DateTime)
rst_text = Column(UnicodeText)
blog_id = Column(Integer, ForeignKey( "BLOG.id" ))
blog = relationship( "Blog" , backref = "entries" )
tags = relationship( "Tag" , secondary =assoc_post_tag, backref = "posts" )
def as_dict( self ):
return dict (
title = self .title,
underline = "-" * len ( self .title),
date = self .date,
rst_text = self .rst_text,
tags =[t.phrase for t in self .tags],
)这个类有五个属性、两个关系和一个方法函数。id属性为整数主键；默认情况下，该值将自动递增。title属性是一个简单的字符串。
date属性为DateTime列；rst_text被定义为UnicodeText，以强调我们对该字段中任何 Unicode 字符的期望。
blog_id是包含此帖子的父博客的外键引用。除了外键列定义外，我们还在帖子和父博客之间包含了一个明确的relationship定义。这个relationship定义成为一个属性，我们可以使用它从帖子导航到父博客。
backref选项包括一个将添加到Blog类的向后引用。Blog类中的此引用将是Blog中包含的Posts的集合。backref选项将Blog类中的新属性命名为引用子Posts。
tags属性使用relationship定义；此属性将通过关联表导航，以定位与帖子关联的所有Tag实例。我们将查看下面的关联表。这也使用backref在Tag类中包含一个属性，该类引用Post实例的相关集合。
as_dict()方法利用tags属性定位与该Post关联的所有Tags。以下是Tag类的定义：
class Tag(Base):
__tablename__ = "TAG"
id = Column(Integer, primary_key = True )
phrase = Column(String, unique = True )我们定义了一个主键和一个String属性。我们包含了一个约束，以确保每个标记显式唯一。尝试插入重复项将导致数据库异常。Post类定义中的关系表示将在此类中创建其他属性。
按照 SQL 的要求，我们需要一个关联表，用于标记和帖子之间的多对多关系。此表纯粹是 SQL 中的技术要求，不需要映射到 Python 类：
assoc_post_tag = Table(
"ASSOC_POST_TAG" ,
Base.metadata,
Column( "POST_ID" , Integer, ForeignKey( "POST.id" )),
Column( "TAG_ID" , Integer, ForeignKey( "TAG.id" )),
)我们必须将其显式绑定到Base.metadata集合。该绑定自动成为使用Base作为元类的类的一部分。我们定义了一个包含两个Column实例的表。每个列都是模型中其他表之一的外键。
让我们看看如何使用 ORM 层构建模式。
为了连接到数据库，我们需要创建一个引擎。该引擎的一个用途是使用表声明构建数据库实例。引擎的另一个用途是管理会话中的数据，我们将在后面介绍。下面是一个我们可以用来构建数据库的脚本：
from sqlalchemy import create_engine
engine = create_engine('sqlite:///./p2_c11_blog2.db', echo=True)
Base.metadata.create_all(engine) 当我们创建一个Engine实例时，我们使用一个类似 URL 的字符串来命名供应商产品，并提供创建到该数据库的连接所需的所有附加参数。对于 SQLite，连接是一个文件名。对于其他数据库产品，可能有服务器主机名和身份验证凭据。
一旦我们有了引擎，我们就完成了一些基本的元数据操作。我们已经向您展示了create_all()，它构建了所有的表。我们还可能执行一个drop_all()，它将删除所有表，丢失所有数据。当然，我们也可以创建或删除单个模式项。
如果我们在软件开发期间更改表定义，它不会自动改变 SQL 表定义。我们需要显式地删除并重建表。在某些情况下，我们可能希望保留一些操作数据，这可能会导致从旧表创建和填充新表的复杂操作。
echo=True选项使用生成的 SQL 语句写入日志条目。这有助于确定声明是否完整并创建预期的数据库设计。以下是生成的输出片段：
CREATE TABLE "BLOG" (
id INTEGER NOT NULL,
title VARCHAR,
PRIMARY KEY (id)
)
CREATE TABLE "TAG" (
id INTEGER NOT NULL,
phrase VARCHAR,
PRIMARY KEY (id),
UNIQUE (phrase)
)
CREATE TABLE "POST" (
id INTEGER NOT NULL,
title VARCHAR,
date DATETIME,
rst_text TEXT,
blog_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(blog_id) REFERENCES "BLOG" (id)
)
CREATE TABLE "ASSOC_POST_TAG" (
"POST_ID" INTEGER,
"TAG_ID" INTEGER,
FOREIGN KEY("POST_ID") REFERENCES "POST" (id),
FOREIGN KEY("TAG_ID") REFERENCES "TAG" (id)
)这表明，CREATE TABLE语句是根据我们的类定义创建的。这有助于了解 ORM 定义是如何在数据库中实现的
一旦建立了数据库，我们就可以创建、检索、更新和删除对象。为了处理数据库对象，我们需要创建一个会话作为 ORM 管理对象的缓存。
我们将在下一节中看到如何使用 ORM 层操纵对象。
为了处理对象，我们需要一个会话缓存。这是绑定到引擎上的。我们将向会话缓存添加新对象。我们还将使用会话缓存来查询数据库中的对象。这可以确保所有需要持久化的对象都在缓存中。以下是创建工作会话的方法：
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session() 我们使用 SQLAlchemysessionmaker()函数创建了一个Session类。这是绑定到我们之前创建的数据库引擎的。然后，我们使用Session类来构建一个session对象，我们可以使用它来执行数据操作。通常需要一个会话来处理对象。
通常，我们会在引擎中构建一个sessionmaker类。然后，我们可以使用这个sessionmaker类为应用程序处理构建多个会话。
对于简单对象，我们创建它们并将其加载到会话中，如下代码所示：
blog = Blog(title="Travel 2013")
session.add(blog) 这会将一个新的Blog对象放入名为session的会话中。Blog对象不一定是写入数据库的。我们需要在执行数据库写入之前提交会话。为了满足原子性要求，我们将在提交会话之前完成构建 post。
首先，我们将在数据库中查找Tag实例。如果它们不存在，我们将创建它们。如果它们确实存在，我们将使用在数据库中找到的标记，如下所示：
tags = []
for phrase in "#RedRanger", "#Whitby42", "#ICW":
try:
tag = session.query(Tag).filter(Tag.phrase == phrase).one()
except sqlalchemy.orm.exc.NoResultFound:
tag = Tag(phrase=phrase)
session.add(tag)
tags.append(tag) 我们使用session.query()函数检查给定类的实例。每个filter()函数都会向查询附加一个条件。one()函数确保我们找到了一行。如果引发异常，则表示Tag不存在。我们需要构建一个新的Tag并将其添加到会话中。
一旦我们找到或创建了Tag实例，我们就可以将其附加到名为tags的本地列表中；我们将使用这个Tag实例列表来创建Post对象。以下是我们如何构建Post：
p2 = Post(date=datetime.datetime(2013,11,14,17,25),
title="Hard Aground",
rst_text="""Some embarrassing revelation. Including ☹ and ⚓""",
blog=blog,
tags=tags
)
session.add(p2)
blog.posts = [p2] 这包括对父博客的引用。它还包括我们构建（或在数据库中找到）的Tag实例列表。
Post.blog属性在类定义中定义为关系。当我们分配一个对象时，SQLAlchemy 会提取适当的 ID 值来创建 SQL 数据库用于实现关系的外键引用。
Post.tags属性也被定义为一种关系。Tag对象通过关联表引用。SQLAlchemy 正确地跟踪 ID 值，以便在 SQL 关联表中为我们构建必要的行。
为了将Post与Blog关联，我们将使用Blog.posts属性。这也被定义为一种关系。当我们为这个关系属性分配一个Post对象列表时，ORM 将在每个Post对象中建立适当的外键引用。这是因为我们在定义关系时提供了backref属性。最后，我们对会议的承诺如下：
session.commit() 数据库插入都是在一系列自动生成的 SQL 中处理的。对象仍缓存在会话中。如果我们的应用程序继续使用这个会话实例，那么对象池仍然可用，而不必对数据库执行任何实际查询。
另一方面，如果我们希望绝对确保由其他并发进程编写的任何更新都包含在查询中，那么我们可以为该查询创建一个新的空会话。当我们丢弃会话并使用空会话时，必须从数据库中提取对象以刷新会话。
我们可以编写如下的简单查询来检查并打印所有的Blog对象：
session = Session()
for blog in session.query(Blog):
print("{title}\n{underline}\n".format(**blog.as_dict()))
for p in blog.entries:
print(p.as_dict()) 这将检索所有的Blog实例。Blog.as_dict()方法将检索博客中的所有帖子。Post.as_dict()将检索所有标签。SQL 查询将由 SQLAlchemy 自动生成和执行。
我们没有包括第 10 章、序列化和保存-JSON、YAML、Pickle、CSV 和 XML中的其他基于模板的格式。它不会改变。我们可以通过entries列表从Blog对象导航到Post对象，而无需编写复杂的 SQL 查询。将导航转换为查询是 SQLAlchemy 的工作。使用 Python 迭代器足以让 SQLAlchemy 生成正确的查询来刷新缓存并返回预期的对象。
如果我们为Engine实例定义了echo=True，那么我们将能够看到为检索Blog、Post和Tag实例而执行的 SQL 查询序列。这些信息可以帮助我们了解应用程序在数据库服务器进程上的工作负载。
让我们看看如何查询带有标记的帖子。
关系数据库的一个重要优点是我们能够跟踪对象之间的关系。使用 SQLAlchemy 的查询功能，我们可以跟踪从Tag到Post的关系，并定位所有共享给定Tag的Posts。
查询是会话的一项功能。这意味着已经在会话中的对象不需要从数据库中提取，这可能会节省时间。不在会话中的对象缓存在会话中，以便在提交时处理更新或删除。
要收集具有给定标记的所有帖子，我们需要使用中间关联表以及Post和Tag表。我们将使用会话的查询方法来指定希望返回的对象类型。我们将使用 fluent 接口将各种中间表和我们想要的最终表与选择条件连接起来。下面是它的外观：
session2 = Session()
results = (
session2.query(Post).join(assoc_post_tag).join(Tag).filter(
Tag.phrase == "#Whitby42"
)
)
for post in results:
print (
post.blog.title, post.date,
post.title, [t.phrase for t in post.tags]
)session.query()方法指定了我们想要查看的表。join()方法确定必须匹配的其他表。因为我们在类定义中提供了关系信息，SQLAlchemy 可以计算出使用主键和外键匹配行所需的 SQL 详细信息。最后的filter()方法提供所需行子集的选择标准。以下是生成的 SQL：
SELECT "POST".id AS "POST_id",
"POST".title AS "POST_title",
"POST".date AS "POST_date",
"POST".rst_text AS "POST_rst_text",
"POST".blog_id AS "POST_blog_id"
FROM "POST"
JOIN "ASSOC_POST_TAG" ON "POST".id = "ASSOC_POST_TAG"."POST_ID"
JOIN "TAG" ON "TAG".id = "ASSOC_POST_TAG"."TAG_ID"
WHERE "TAG".phrase = ? Python 版本更容易理解，因为可以省略键匹配的细节。print()函数使用post.blog.title从Post实例导航到关联的 blog，并显示title属性。如果日志位于会话缓存中，则可以快速完成此导航。如果日志不在会话缓存中，它将从数据库中获取。
此导航行为也适用于[t.phrase for t in post.tags]。如果该对象位于会话缓存中，则只需使用它。在这种情况下，收集与 post 关联的Tag对象可能会导致复杂的 SQL 查询，如下所示：
SELECT
"TAG".id AS "TAG_id",
"TAG".phrase AS "TAG_phrase"
FROM "TAG", "ASSOC_POST_TAG"
WHERE ? = "ASSOC_POST_TAG"."POST_ID"
AND "TAG".id = "ASSOC_POST_TAG"."TAG_ID" 在 Python 中，我们只是通过post.tags导航。SQLAlchemy 为我们生成并执行 SQL。
提高关系数据库（如 SQLite）性能的方法之一是加快连接操作。实现这一点的理想方法是包含足够的索引信息，这样就不会执行缓慢的搜索操作来查找匹配的行。
当我们定义一个可能在查询中使用的列时，我们应该考虑为该列建立索引。这是一个使用 SQLAlchemy 的简单过程。我们只是用index=True注释类的属性。
例如，我们可以对Post表进行相当小的更改。我们可以这样做来添加索引：
class Post(Base):
__tablename__ = "POST"
id = Column(Integer, primary_key=True)
title = Column(String, index=True)
date = Column(DateTime, index=True)
blog_id = Column(Integer, ForeignKey('BLOG.id'), index=True) 为标题和日期添加两个索引通常会加快按标题或日期查询帖子的速度。不能保证性能会有所改善。关系数据库性能涉及许多因素。在使用索引和不使用索引的情况下，衡量实际工作负载的性能非常重要。
类似地，通过blog_id添加索引可能会加快Blog和Post表中的行之间的连接操作。数据库引擎也可能使用了一种算法，但该算法无法从该索引的可用性中获益。
索引涉及存储和计算开销。很少使用的索引的创建和维护成本可能非常高，以至于它成为一个问题，而不是一个解决方案。另一方面，有些指标非常重要，可以显著提高性能。在所有情况下，我们都不能直接控制所使用的数据库算法；我们所能做的最好的事情就是创建索引并测量性能影响。
让我们来看看下一节中的模式演化。
在使用 SQL 数据库时，我们必须解决模式演化的问题。我们的对象具有动态和（相对）静态的类定义。我们可以很容易地保持对象的动态状态。我们的类定义了持久数据的模式。ORM 提供了从类到 SQL 实现的映射。
当我们更改类定义时，如何从数据库中获取对象？如果数据库必须更改，我们如何升级 Python 映射并仍然访问数据？一个好的设计通常涉及多种技术的结合。
对 Python 类的方法和属性的更改不会更改到 SQL 行的映射。这些可以称为微小更改，因为数据库中的表仍然与更改的类定义兼容。新的软件版本可以有一个新的次要版本号。
对 Python 类属性的某些更改不一定会更改持久化对象状态。例如，添加索引不会更改基础表。在将数据类型从数据库转换为 Python 对象时，SQL 可以有一定的灵活性。ORM 层也可以增加灵活性。在某些情况下，我们可以对类或数据库进行一些更改，并将其称为小版本更新，因为现有的 SQL 模式仍然可以与新的类定义一起使用
对 SQL 表定义的其他更改需要涉及修改持久化对象。当现有数据库行不再与新的类定义兼容时，这些可以称为主要更改。不应该通过修改原始 Python 类定义来进行此类更改。应该通过定义新子类并提供更新的工厂函数来创建旧类或新类的实例来进行此类更改。
Alembic 等工具（见https://pypi.org/project/alembic/ 和迁移（https://sqlalchemy-migrate.readthedocs.io/en/latest/ 可以帮助管理模式演变。模式迁移步骤的严格历史记录对于正确地从旧数据转换为新数据通常是必不可少的
有两种技术用于将模式从一个版本转换到下一个版本。任何给定的应用程序都将使用这些技术的某些组合，如下所示：
- SQL
ALTER语句就地修改表。对于ALTER可以进行哪些更改，有许多限制和限制。这通常包括一些小的变化。 - 创建新表和删除旧表。通常，SQL 模式更改需要从旧表中的数据获取新版本的表。对于大型数据库，这可能是一个耗时的操作。对于某些类型的结构变化，这是不可避免的。
SQL 数据库架构更改通常涉及运行一次性转换脚本。此脚本将使用旧模式查询现有数据，将其转换为新数据，并使用新模式将新数据插入数据库。当然，在用户的实时操作数据库上运行之前，必须在备份数据库上进行测试。一旦完成了模式更改，就可以安全地忽略旧模式，然后删除旧模式以释放存储空间。
Alembic 等工具需要两个单独的转换脚本。升级脚本将架构向前移动到新状态。降级脚本会将架构移回以前的状态。调试迁移可能涉及升级、查找问题、降级以及使用以前的软件，直到问题得到解决。
我们以三种方式介绍了使用 SQLite 的基础知识：直接、通过访问层和通过 SQLAlchemy ORM。我们必须创建 SQLDDL 语句；我们可以直接在应用程序或访问层中执行此操作。我们还可以使用 SQLAlchemy 类定义构建 DDL。要操作数据，我们将使用 SQL DML 语句；我们可以直接以过程的方式来完成，也可以使用我们自己的访问层或 SQLAlchemy 来创建 SQL。
sqlite3模块的优点之一是，它允许我们保存不同的对象，每个对象都有其独特的更改历史。当使用支持并发写入的数据库时，我们可以让多个进程更新数据，依靠 SQLite 通过其自身的内部锁定来处理并发。
使用关系数据库有许多限制。我们必须考虑如何将对象映射到数据库中的表行，如下所示：
- 我们可以直接使用 SQL，只使用受支持的 SQL 列类型，并且在很大程度上避免使用面向对象的类。
- 我们可以使用扩展 SQLite 的手动映射将对象作为 SQLite BLOB 列处理。
- 我们可以编写自己的访问层，以便在对象和 SQL 行之间进行调整和转换。
- 我们可以使用 ORM 层来实现行到对象的映射。
混合使用 Python 和 SQL 的问题是，可能会有一种推动力，我们可以称之为全唱、全跳、全 SQL解决方案。这里的想法是，关系数据库在某种程度上是理想的平台，而 Python 通过注入不必要的面向对象特性破坏了这一点。
全 SQL、无对象的设计策略有时被认为更适合于某些类型的问题。具体而言，支持者将指出使用 SQLGROUP BY子句汇总大型数据集是 SQL 的理想用途。
Python 的defaultdict和Counter非常有效地实现了这种处理。Python 版本通常非常有效，以至于一个使用defaultdict查询大量行并积累摘要的小型 Python 程序可能比使用GROUP BY执行 SQL 的数据库服务器更快速度。
如有疑问，请测量。当面对 SQL 神奇地比 Python 快的说法时，您应该收集证据。这种数据收集也不局限于一次性的初始技术峰值情况。随着使用的增长和变化，SQL 数据库与 Python 的相对优势也将发生变化。自制的访问层往往非常特定于问题域。这可能具有从行到对象的高性能和相对透明的映射的优点。每次类更改或数据库实现更改时进行维护可能会让人恼火。
一个成熟的 ORM 项目可能需要一些初步的努力来学习 ORM 的特性，但是长期的简化是重要的好处。学习 ORM 层的特性可以包括初始工作和经验教训后的返工。随着应用程序的权衡和考虑变得越来越清楚，对于具有良好对象特性并且仍然适合 SQL 框架的设计的第一次尝试将不得不重新进行。
因为 SQL 依赖于键，所以我们必须注意为各种对象设计和管理键。我们必须设计从一个对象到将用于标识该对象的键的映射。一种选择是定位包含正确主键且无法更改的属性（或属性组合）。另一种选择是生成不能更改的代理密钥；这允许更改所有其他属性。
大多数关系数据库都可以为我们生成代理键。这通常是最好的方法。对于其他唯一属性或候选键属性，我们可以定义 SQL 索引以提高处理性能。
我们还必须考虑对象之间的外键关系。有几种常见的设计模式：一对多、多对一、多对多和可选的一对一。我们需要了解 SQL 如何使用键来实现这些关系，以及 SQL 查询如何用于填充 Python 集合。
由于使用sqlite3时相对复杂，我们的应用软件必须更合理地分层。通常，我们将研究具有以下层的软件体系结构：
- **表示层：**这是一个顶级用户界面，可以是 web 表示，也可以是桌面 GUI。
- **应用层：**这是使应用程序工作的内部服务或控制器。这可以称为处理模型，与逻辑数据模型不同。
- **业务层或问题域模型层：**这些是定义业务域或问题空间的对象。这有时被称为逻辑数据模型。我们研究了如何使用微博和帖子示例对这些对象进行建模。
- **基础设施：**这通常包括几个层次，以及其他交叉关注点，如日志记录、安全性和网络访问。
- **数据访问层：**这些是访问数据对象的协议或方法。它通常是 ORM 层。我们已经看过了炼金术。这方面还有很多其他选择。
- **持久层：**这是在文件存储中看到的物理数据模型。
sqlite3模块实现持久化。当使用诸如 SQLAlchemy 之类的 ORM 层时，我们仅在创建引擎时引用 SQLite。
当查看本章中的sqlite3和第 11 章中的shelve通过 Shelve存储和检索对象时，很明显，掌握面向对象编程涉及到一些更高层次的设计模式。我们不能简单地孤立地设计类，我们需要了解如何将类组织成更大的结构。
在下一章中，我们将介绍如何使用 REST 传输和共享对象。这个设计模式向我们展示了如何管理状态的表示，以及如何将对象状态从一个进程转移到另一个进程。我们将利用许多持久化模块来表示正在传输的对象的状态。
在第 14 章、配置文件和持久化中，我们将查看配置文件。我们将研究几种利用控制应用程序的数据持久表示的方法。