11.1 SQL数据库、持久化和对象

当使用SQLite时，会使用一个关系数据库和基于SQL语言的数据访问层。SQL语言是面向对象编程火热时期所遗留的一种语言。SQL语言侧重于面向过程编程，也是阻抗不匹配问题的来源，即关系数据模型与对象数据模型之间的不匹配。在SQL数据库中，我们主要侧重于数据模型的3个层面，如下所示。

• 概念模型：这些实体关系是基于 SQL 模型创建的。在大多数情况下，它们可以映射为Python对象并且与应用中的数据层相对应。这里是使用对象关系映射的地方。
• 逻辑模型：它们就是SQL数据库中的表、行、列。我们会在SQL数据操作语句中来处理这些实体。这个模型之所以会存在，是因为它表达了一种物理模型，它与数据库中的表、行和列有时是不同的。例如，在一个SQL查询所返回的结果中，看起来像是一个表，但可能并没有涉及数据库中平行定义的一些表。
• 物理模型：这些包括文件、块、页、比特和用于物理存储的字节。这些实体由管理级别的SQL语句进行定义。在一些复杂的数据库中，我们可以尝试对数据的物理模型进行操作从而优化性能。然而在SQLite中，几乎不可能完成。

在使用SQL数据库时，在设计时需要做一些抉择。其中最重要的决定也许是如何最大化地解决阻抗不匹配问题，也就是如何解决从旧的SQL数据映射为Python对象模型，有3种常见的策略。

• 完全不考虑Python的映射：这意味着我们不对数据库进行复杂的Python对象的查询，工作范围在一个完全独立的SQL框架中进行，这个框架包括了原子数据元素和函数处理。使用这种方式就不必非常执着于为数据库对象持久化使用面向对象编程进行设计。这样一来，我们只能使用 4 种基本的 SQLite类型：NULL、INTEGER、REAL和TEXT，还有Python中的datetime.date和datetime.datetime。
• 手动映射：在类与SQL逻辑模型的表、列、行和键之间添加一个数据访问层。
• ORM层：下载安装一个ORM层，用于完成在类与SQL逻辑模型的映射。

在接下来的例子中，会对这3种方式逐一进行介绍。在了解从SQL到对象的映射前，我们先看一下SQL逻辑模型的一些细节，在此过程中会使用不映射的方式来完成。

11.1.1 SQL数据模型——行和表

SQL数据模型包括了表的命名和表中列的命名。表包含了多行数据，每个数据行像是一个不可变的namedtuple。大致上来看，表更像是list。

当定义一个SQL数据库时，会定义一些表以及其中的列。当使用一个SQL数据库时，我们会对表中数据行进行操作。对于SQLite来说，SQL只支持少数几种数值类型。SQLite支持NULL、INTEGER、REAL、TEXT和BLOB数据。对应的Python中类型为None、int、float、str和bytes。类似地，当从SQLite数据库取这些类型的数据时，它们会被转化为Python对象。

可以通过为SQLite添加转换函数来对转换过程进行改善。在sqlite3模块中加入了datetime.date和datetime.datetime，因此需要对这种方式的实现进行扩展。我们将在下一节中使用手动映射的方式解决此问题。

SQL语句可以被分为3类：数据定义语言（data definition language，DDL）、数据操纵语言（data manipulation language，DML）和数据控制语言（data control language，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) );

我们创建了4张表来表示博客应用中的Blog和Post对象。有关在SQLite中使用SQL语句的更多信息，可以参见 http://www.sqlite.org/lang.html。若要了解更多有关 SQL 的背景，可以阅读一些书籍，比如 Creating your MySQL Database: Practical Design Tips __and Techniques，它会基于MySQL数据库对SQL语言进行介绍。SQL语言是区分大小写的，而我们比较倾向于将SQL全部大写，以对Python代码进行区分。

BLOG表中定义了一个AUTOINCREMENT的主键，SQLite将完成对主键的赋值，这样在代码中就无需再生成键值了。TITLE列表示一个博客的标题，我们定义为TEXT类型。在一些数据库中，必须提供最大长度，而在SQLite中不需要，避免了长度不一的存储记录造成的混乱。

在POST表中定义了一个主键和日期，标题还有表示文章内容的RST文本。可以注意到，在表定义中我们并没有引用标签，需要回到有关以下SQL数据表的设计模式中。在POST表中包含了一个单独的REFERENCES语句用来表示这是一个引用到自己BLOG的外键。TAG表中，只定义了每个标签文本。

最后，我们为POST和TAG创建了关联表。这个表只有两个外键，它关联了标签和文章，允许一篇文章包含无限数量的标签，以及无限数量的文章可以共享一个标签。这个关联表在SQL设计模式中是很常见的，用于为这类表关系建立联系。在接下来的几节中，我们会看一些其他的SQL设计模式，可以通过执行之前的定义来创建数据库。

import sqlite3
database = sqlite3.connect('p2_c11_blog.db')
database.executescript( sql_ddl )

所有的数据库访问需要一个连接，使用模块中的函数sqlite3.connect()进行创建，将文件名传入数据库连接中。我们将对这个函数的其他参数在接下来的节中进行介绍。

DB-API会假设应用程序进程会连接一个独立的数据库服务进程。而对于SQLite来说，并没有一个独立的进程。然而，为了符合标准，使用了connect()函数。

sql_ddl变量只是一个长的字符串变量，其中包含了4个CREATE TABLE语句。如果没有错误信息，意味着表结构被正确定义了。

Connection.executescript()方法在Python标准库中被描述为nonstandard shortcut。从技术上来看，数据库操作包含了cursor。如下是一个使用的示例。

crsr = database.cursor()
for stmt in sql_ddl.split(";"):
　　crsr.execute(stmt)

因为我们主要使用 SQLite，所以会大量使用 nonstandard shortcuts。若要顺利移植到其他数据库，就需要严格地符合DB-API。我们会在接下来几节的查询中重新介绍游标对象。

11.1.2 使用SQL的DML语句完成CRUD

以下4种典型的CRUD操作直接对应了相应的SQL语句。

• 创建操作通过INSERT语句完成。
• 查询操作通过SELECT语句完成。
• 更新操作通过UPDATE语句来完成，如果数据库支持，还可以使用REPLACE语句来完成。
• 删除操作通过DELETE语句完成。

已经注意到了，有一种文本形式的SQL语法，绑定了变量占位符而非文本值。对于脚本来说，文字形式的SQL语法是可以接受的。然而，由于值总是文本，对于应用编程来说是非常糟糕的。在一个应用中创建文本的SQL语句涉及到大量的字符串操作和明显的安全问题。这里就有一个拼凑SQL文本带来的安全问题：http://xkcd.com/327/，我们会重点介绍一些SQL中的变量绑定。

SQL文本的广泛使用是错误的做法。

Python的DB-API接口，在Python增强建议书（Python Enhancement Proposal，PEP）249条，http://www.python.org/dev/peps/pep-0249/，定义了几种方式来将应用变量绑定到SQL语句中。SQLite中可以使用?完成位置绑定或使用:name完成命名绑定。接下来会对这两种绑定进行一一介绍。

如以下代码段所示，我们使用一个INSTERT语句新建了一个BLOG行。

create_blog= """
INSERT INTO BLOG(TITLE) VALUES(?)
"""
database.execute(create_blog, ("Travel Blog",))

我们创建了一个SQL语句，为BLOG表中的TITLE列使用了一个位置绑定变量?。在将一组值绑定到变量上之后，执行了这条语句。只绑定了一个变量，因此在元组中只有一个值。这条语句一旦执行，在数据库中将插入一行记录。

可以看到，在3个引号内的SQL语句与Python代码很明显地区分开了。在一些应用中，会将SQL保存为一个独立的配置项。从语句名到SQL文本的映射，将SQL分离是最好的选择。例如，可以将 SQL 存在一个 JSON 文件中。这意味着可以使用SQL=json.load ("sql_config.json")来获取所有的SQL语句。然后可以使用SQL["some statement name"]来引用一个特定的SQL语句，可以将SQL控制在Python代码的外部来简化维护成本。

使用DELETE与UPDATE语句时需要指定一个WHERE语句来标识哪一行需要修改或删除。可以使用如下代码来修改一个blog标题。

update_blog="""
UPDATE BLOG SET TITLE=:new_title WHERE TITLE=:old_title
"""
database.execute( "BEGIN" )
database.execute( update_blog,
　　dict(new_title="2013-2014 Travel", old_title="Travel Blog") )
database.commit()

在UPDATE语句中有两个命名的绑定变量:new_title和:old_title。这个事务会将BLOG表中所有包含了旧标题的行更新为新标题。理想情况下，标题是唯一的，而且只有一行被修改。SQL语句的操作对象是行的集合。数据库设计中很重要的一点是要确保行的内容是由集合构成的，建议为每个表创建一个主键。

当执行一个删除操作时，会有两种选择。要么当子记录仍存在时阻止对父记录的删除，要么在删除父记录的同时删除相应的子记录。接下来的例子中会介绍有关对Blog、Post和标签的级联删除。以下是一些DELETE语句。

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 database:
　　　　title= dict(old_title="2013-2014 Travel")
　　　　database.execute( delete_post_tag_by_blog_title, title )
　　　　database.execute( delete_post_by_blog_title, title )
　　　　database.execute( delete_blog_by_title, title )
　　print( "Delete finished normally." )
except Exception as e:
　　print( "Rolled Back due to {0}".format(e) )

我们使用了3步来完成删除操作。首先，根据指定的Blog标题从ASSOC_POST_TAG中删除所有的行。注意它是一个嵌套查询，我们会在下一节中介绍查询。在SQL结构中执行表之间的跳转是一个常见的问题。因此，必须从BLOG-POST关系中先查询POST的ID来得到要删除的行，然后就可以找出与需要删除博客的相关文章了，再根据这些文章的记录从ASSOC_POST_TAG中找出并移除相关的记录。下一步，删除所有与指定博客相关的文章记录。这也涉及一个嵌套查询，用于完成根据标题查询出所有相关博客的ID。最后，删除博客记录。

这是一个显式执行级联删除的例子，操作需要从BLOG表级联操作两个其他的表。将所有的删除放进一个with上下文中，这样它就会当作同一个事务来执行。如果执行失败，它将会回滚已经执行的修改，将数据库恢复为执行之前的状态。

11.1.3 使用SQL中SELECT语句执行查询

有关SELECT语句的内容，可以单独写一本书来介绍。这里我们只关注SELECT语句中最基本的部分。我们的目的是掌握一些基本的SQL语句，能够完成对数据库的查询和存储即可。

在前面的内容里我们提到，从技术上来说，建议在执行SQL语句时使用一个游标。对于DDL和其他DML语句，是否使用游标并不是很重要。我们将显式地创建游标，因为它会大幅度简化SQL编程。

然而，对于查询来说，游标主要用于从数据库中获取数据。根据标题对一个博客进行定位，可以使用如下这样简单的查询语句。

"SELECT * FROM BLOG WHERE TITLE=?"

我们需要获取对象的行结果集。尽管有时只需要返回一行数据，但对SQL而言，一切都是一个集合。大致上来说，每个结果集是由SELECT查询获取的集合，通常是一个包含了行和列的表。它的定义由SELECT语句完成而非任何CREATE TABLE DDL语句。

这样一来，使用SELECT * 意味着有效地避免了对期望的结果列进行枚举。这或许会导致返回结果中包含大量的列，以下是对于使用SQLite常见的优化方案。

query_blog_by_title= """
SELECT * FROM BLOG WHERE TITLE=?
"""
for blog in database.execute( query_blog_by_title, ("2013-2014
Travel",) ):
　　print( blog[0], blog[1] )

在SELECT语句中，*代表了所有的有效列的集合。这种方式对于简单的表来说是非常有用的。

在SELECT语句中，将请求的博客标题绑定在了"?"参数上。execute()函数的执行结果是一个游标对象。游标是可迭代的，它将迭代返回所有的行结果集，这些结果集包含了所有匹配WHERE语句中查询关键字的行。

为了完全遵守Python中DB-API的标准，可以分解为如下几步。

crsr= database.cursor()
crsr.execute( query_blog_by_title, ("2013-2014 Travel",) )
for blog in crsr.fetchall():
　　print( blog[0], blog[1] )

以上演示了如何使用连接创建一个游标对象。然后就可以使用游标对象来执行一个查询语句。一旦完成了查询的执行，就可以获取行结果集。每行表示SELECT语句所返回的其中一个元组值。这样一来，因为SELECT语句为*，这意味着从原来的CREATE TABLE语句返回的列将被使用。

11.1.4 SQL事务和ACID属性

正如我们所看到的，SQL中DML语句对应了CRUD操作。当讨论SQL中事务时，需要介绍INSERT、SELECT、UPDATE和DELETE语句。

SQL的DML语句都工作在一个SQL事务的上下文中。在一个事务中执行的SQL语句是一个工作的逻辑单元，整个事务可以被提交或回滚，整个过程为原子操作。

SQL中的DDL语句（例如，CREATE、DROP）不会在事务中工作。它们会隐式地结束任何之前正在进行中的事务，因为它们改变了数据库的结构。它们是另一类语句，故而不存在事务的概念。

ACID是指原子性（Atomic）、一致性（Consistent）、隔离性（Isolated）和持久性（Durable）。它们是事务的基本属性，其中每个事务中包括了多个数据库操作。更多信息可以阅读第10章“用Shelve保存和获取对象”。

只有在读未提交（read uncommitted）模式下，每个数据库链接所看到的数据版本是一致的，它们只包含已经提交了的事务执行后的结果。未提交的事务对于其他数据库客户端进程来说通常是隐藏的，具有一致性的特性。

一个SQL事务也具有隔离性的特性。SQLite中支持不同的隔离级别，在隔离级别中定义了SQL的DML语句是如何在多个并发的进程中交互的。这点是基于锁的使用以及一个SQL请求的进程是如何基于锁的定义进行延迟的。在Python中，隔离级别是在数据库连接建立时发生的。

每个SQL数据库都有各自对隔离级别和锁的处理方式，没有统一的模型。

对于 SQLite来说，有4种隔离级别用于定义锁以及事务的本质。更多信息可参见http://www.sqlite.org/isolation.html。以下是几种隔离级别。

• isolation_level=None：这是默认的设置，也被称为自动提交（autocommit）模式。在这种模式下，每个SQL语句都会在执行后直接提交到数据库。它破坏了原子性，而有些奇怪的观点则认为，所有的事务都应当只包含一个SQL语句。
• isolation_level='DEFERRED'：在这种模式中，事务中锁的添加越晚越好。例如BEGIN语句，并没有立即获得任何锁。对于其他的读操作（即SELECT语句）可以获得共享锁，写操作将获得保留的锁。然而这样可以最大化并发，但在多个进程中也会产生死锁。
• isolation_level='EXCLUSIVE'：在这种模式中，事务的BEGIN语句会获得一个锁，阻止其他操作的访问。对于一些链接，在一种特殊的读未提交模式中，忽略锁会导致异常。

持久性对于所有已提交的事务都是可以保证的。数据已经写入了数据库文件中。

在SQL中，需要使用BEGIN TRANSACTION和COMMIT TRANSACTION来将括号内的步骤包括在事务中。出现错误时，需要使用ROLLBACK TRANSACTION语句来进行回滚。在Python中的接口简化了这一点。我们可以执行一个BEGIN语句。其他语句由sqlite3. ``Connection对象中的函数来提供，不需要执行SQL语句来终止一个事务，如以下代码所示。

database = sqlite3.connect('p2c11_blog.db', isolation
level='DEFERRED')
try:
　　database.execute( 'BEGIN' )
　　database.execute( "some statement" )
　　database.execute( "another statement" )
　　database.commit()
except Exception as e:
　　database.rollback()
　　raise e

当建立数据库连接时我们将隔离级别设置为 DEFERRED。这意味着我们需要显式地开始和结束每个事务。一个典型的场景是，将相关的DML封装在一个try语句块中，然后在没有错误的情况下提交事务，如果发生错误则回滚事务，可以使用sqlite3.Connection对象作为一个上下文管理器来简化这个过程。

database = sqlite3.connect('p2c11_blog.db', isolation
level='DEFERRED')
with database:
　　database.execute( "some statement" )
　　database.execute( "another statement" )

以上代码与之前的例子是类似的。我们使用了相同的方式打开数据库，然后进入了一个上下文而并没有显式地执行BEGIN语句，上下文对象会替我们完成这件事情。

在with上下文的最后，database.commit()语句会自动提交。当发生异常时，database.rollback()会被执行，然后异常会从with语句中抛出。

11.1.5 设计数据库中的主键和外键

SQL表中并不一定要定义一个主键。然而，表中没有包含主键的设计是非常糟糕的。正如在第10章“用Shelve保存和获取对象”中所看到的，可能会有一个属性（或一些属性的组合）用于定义一个联合主键，也有可能没有任何属性适合定义为主键，那么就必须定义代理主键。

在之前的例子中就使用了代理主键。这可能是最简单的设计方式，因为它对数据的约束是最少的。其中一个约束是主键不能被修改，这是在编程中必须遵守的规则。可在一些情况下——例如，当需要纠正主键值的错误时——就无论如何都要修改主键了。其中一种做法是删除约束后再新建。另一种做法是删除那条错误记录然后再使用正确的键值重新插入。如果存在级联删除，需要在事务中纠正主键的值，这种情况很复杂。使用代理主键可以避免此类问题。

表之间的所有关系都是由主键和外键的引用完成的。对于表关系的设计，有两种非常常用的设计方式。在之前的表设计中已经介绍了。在设计表关系时，有3种设计方法，如下面列表所示。

• 1对多：这种关系体现在一个博客对应了多篇文章。REFERENCES语句演示了在POST表中的很多行将会引用BLOG表中的一行。如果从子对父的引用方向来看，这种关系可以称为多对1。
• 多对多：这种关系体现在多篇文章与多个标签的对应关系上。这将需要一个介于POST和TAG的中间表；中间表有两个（或多个）外键。多对多的中间表也可以包含自己的属性。
• 1对1：这种关系是相对少见的。从技术上来看，它与一对多的关系没有区别；0行或者一行的基数是一种在应用程序中必须进行管理的约束。

在一个数据库设计中，在关系上可能会有这几种约束：关系被描述为可选或必选，在关系上可能会有基数限制。有时，这些可选或者基数限制被概括地描述为“0:m”，意味着“0对多”或“可选的1对多”。可选性和基数约束是应用编程逻辑中的一部分，在SQLite数据库中并没有正式的说法来对其进行陈述。在数据库中，基本表关系可以通过以下一种或两种方式来实现。

• 显式：可以称之为已定义的，因为它们是数据库中DDL定义的一个部分。理想情况下，它们是被数据库服务器强制的，而对关系约束的检测失败可能会导致某种错误。
• 隐式：这些关系只在查询中体现，它们不是DDL中正式的部分。

可以注意到，在表定义中实现了1对多的关系，建立在博客与博客中的多条记录之间。在之前写的多个查询中我们用到了这些关系。