16.1.1. 什么是数据库？¶

一个 数据库 是一个用于存储数据的文件。大多数数据库在意义上类似于字典，即它们将键映射到值。最大的区别在于数据库存储在磁盘（或其他永久存储介质）上，因此它在程序结束后仍然存在。由于数据库存储在永久存储介质上，它可以存储比字典多得多的数据，而字典仅限于计算机内存的大小。

与字典类似，数据库软件旨在使大量数据的插入和访问都非常快速。数据库软件通过构建 索引 来保持其性能，当数据被添加到数据库时，允许计算机快速跳转到特定条目。

存在许多用于各种目的的不同数据库系统，包括：Oracle、MySQL、Microsoft SQL Server、PostgreSQL 和 SQLite。我们在本书中专注于 SQLite，因为它是一种非常常见的数据库，并且已经内置于 Python 中。SQLite 被设计为可以 嵌入 到其他应用程序中，以在应用程序内提供数据库支持。例如，Firefox 浏览器也在内部使用 SQLite 数据库，许多其他产品也是如此。

http://sqlite.org/

SQLite 非常适合我们在信息学中看到的一些数据操作问题，例如本章中描述的 Twitter 爬虫应用程序。

16.1.2. 数据库概念¶

当你第一次查看数据库时，它看起来像是一个具有多个工作表的电子表格。数据库中的主要数据结构是：表、行 和 列。

|Relational Databases|

关系数据库

在关系数据库的技术描述中，表、行和列的概念分别更正式地称为 关系、元组 和 属性。我们将在本章中使用这些不太正式的术语。

16.1.3. SQLite 数据库浏览器¶

尽管本章将专注于使用 Python 在 SQLite 数据库文件中处理数据，但许多操作可以通过名为 Database Browser for SQLite 的软件更方便地完成，该软件可从以下地址免费获取：

http://sqlitebrowser.org/

使用浏览器，您可以轻松创建表、插入数据、编辑数据，或在数据库中的数据上运行简单的 SQL 查询。

某种意义上，数据库浏览器在处理文本文件时类似于文本编辑器。当需要对文本文件执行一个或极少几个操作时，你可以直接在文本编辑器中打开它并进行所需修改。当需要对文本文件进行大量修改时，通常你会编写一个简单的 Python 程序。在处理数据库时，你会发现相同的模式。你会在数据库管理器中执行简单操作，而更复杂的操作则最方便在 Python 中完成。

16.1.4. 创建数据库表¶

数据库需要比 Python 列表或字典更明确的定义结构:sup:`1 <https://www.py4e.com/html3/15-database#fn1>`__。

当我们创建数据库 表 时，必须提前告诉数据库表中每个 列 的名称以及我们计划存储在每个 列 中的数据类型。当数据库软件知道每个列中的数据类型时，它可以根据数据类型选择存储和查找数据的最有效方式。

您可以在以下网址查看 SQLite 支持的各类数据类型：

http://www.sqlite.org/datatypes.html

预先为数据定义结构在开始时可能显得不便，但其回报是即使数据库包含大量数据时也能快速访问数据。

创建数据库文件以及在数据库中创建一个名为 Tracks 且包含两列的表的代码如下：

import sqlite3

conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Tracks')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

conn.close()

# Code: http://www.py4e.com/code3/db1.py

connect 操作在当前目录中建立与存储在文件 music.sqlite 中的数据库的“连接”。如果该文件不存在，则将其创建。之所以称之为“连接”，是因为有时数据库存储在与运行应用程序的服务器不同的“数据库服务器”上。在我们的简单示例中，数据库仅仅是与正在运行的 Python 代码位于同一目录中的本地文件。

一个 游标 类似于一个文件句柄，我们可以用它对存储在数据库中的数据执行操作。在文本文件处理中，调用 cursor() 在概念上与调用 open() 非常相似。

数据库游标

一旦我们拥有游标，就可以使用 execute() 方法开始对数据库内容执行命令。

数据库命令用一种特殊语言表达，该语言已在许多不同的数据库供应商之间标准化，以便我们学习一种单一的数据库语言。数据库语言称为 结构化查询语言 或简称 SQL。

http://en.wikipedia.org/wiki/SQL

在我们的示例中，我们在数据库中执行两条 SQL 命令。按照惯例，我们将 SQL 关键字显示为大写，而添加的命令部分（如表和列名）将显示为小写。

第一条 SQL 命令如果存在，则从数据库中删除 Tracks 表。这种模式仅仅是为了允许我们反复运行相同的程序来创建 Tracks 表，而不会导致错误。请注意，DROP TABLE 命令会从数据库中删除表及其所有内容（即，没有“撤销”）。

cur.execute('DROP TABLE IF EXISTS Tracks ')

第二条命令创建一个名为 Tracks 的表，其中包含一个名为 title 的文本列和一个名为 plays 的整数列。

cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

既然我们已经创建了名为 ``Tracks`` 的表，我们就可以使用 SQL ``INSERT`` 操作向该表中插入一些数据。再次，我们首先建立与数据库的连接并获取 ``cursor`` 。然后，我们可以使用游标执行 SQL 命令。

SQL INSERT 命令指明所使用的表，然后通过列出要包含的字段 (title, plays) 定义新行，随后列出要放置在新行中的 VALUES。我们将值指定为问号 (?, ?)，以表明实际值作为元组 ( 'My Way', 15 ) 作为 execute() 调用的第二个参数传入。

import sqlite3

conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()

cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
    ('Thunderstruck', 20))
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
    ('My Way', 15))
conn.commit()

print('Tracks:')
cur.execute('SELECT title, plays FROM Tracks')
for row in cur:
     print(row)

cur.execute('DELETE FROM Tracks WHERE plays < 100')
conn.commit()

cur.close()

# Code: http://www.py4e.com/code3/db2.py

首先，我们向表中 INSERT 两行数据，并使用 commit() 强制将数据写入数据库文件。

表中的行

随后，我们使用 `SELECT` 命令检索刚刚插入到表中的行。在 `SELECT` 命令中，我们指定想要 `(title, plays)` 的列，并指明要从哪个表检索数据。执行 `SELECT` 语句后，游标成为可以在 `for` 语句中遍历的对象。为了提高效率，在执行 `SELECT` 语句时，游标不会从数据库读取所有数据。相反，数据会在我们在 `for` 语句中遍历行时按需读取。

程序的输出如下：

Tracks:
('Thunderstruck', 20)
('My Way', 15)

我们的``for``循环找到两行，每一行都是一个 Python 元组，第一个值为``title``，第二个值为``plays``的数量。

注意：您可能在其他书籍或互联网上看到以``u'``开头的字符串。这是在 Python 2 中的一个指示，表明这些字符串是 Unicode 字符串，能够存储非拉丁字符集。在 Python 3 中，所有字符串默认都是 unicode 字符串。

在程序的最后，我们执行一条 SQL 命令以 DELETE 刚刚创建的行，以便我们可以反复运行该程序。DELETE 命令展示了 WHERE 子句的使用，该子句允许我们表达选择标准，以便我们可以要求数据库仅对匹配该标准的行应用该命令。在本例中，该标准恰好适用于所有行，因此我们清空表格，以便我们可以反复运行该程序。在 DELETE 执行后，我们还调用 commit() 以强制从数据库中删除数据。

16.1.5. 结构化查询语言摘要¶

到目前为止，我们在 Python 示例中一直使用结构化查询语言，并涵盖了 SQL 命令的许多基础知识。在本节中，我们将专门研究 SQL 语言，并概述 SQL 语法。

由于存在众多不同的数据库供应商，结构化查询语言 (SQL) 被标准化，以便我们能够以可移植的方式与来自多个供应商的数据库系统进行通信。

关系数据库由表、行和列组成。列通常具有文本、数值或日期数据等类型。当我们创建表时，我们指定列的名称和类型：

CREATE TABLE Tracks (title TEXT, plays INTEGER)

要向表中插入一行，我们使用 SQL INSERT 命令：

INSERT INTO Tracks (title, plays) VALUES ('My Way', 15)

INSERT 语句指定表名，然后列出您希望在新行中设置的字段/列，接着是关键字 VALUES 以及每个字段对应的值列表。

SQL SELECT 命令用于从数据库中检索行和列。SELECT 语句允许您指定要检索的列，以及一个 WHERE 子句来选择您希望查看的行。它还允许使用可选的 ORDER BY 子句来控制返回行的排序。

SELECT * FROM Tracks WHERE title = 'My Way'

使用 `*` 表示您希望数据库返回匹配 `WHERE` 子句的每一行的所有列。

注意，与 Python 不同，在 SQL WHERE 子句中，我们使用单个等号来表示相等性测试，而不是双等号。WHERE 子句中允许的其他逻辑操作包括 <、>、<=、>=、!=，以及 AND 和 OR，还可以使用括号来构建逻辑表达式。

您可以按如下方式请求返回的行按某个字段排序：

SELECT title,plays FROM Tracks ORDER BY title

要删除一行，需要在 SQL DELETE 语句中使用 WHERE 子句。WHERE 子句用于确定要删除的行：

DELETE FROM Tracks WHERE title = 'My Way'

可以使用 SQL `UPDATE` 语句，如下所示，在表的一个或多个行中对一个或多个列进行 `UPDATE` 操作：

UPDATE Tracks SET plays = 16 WHERE title = 'My Way'

UPDATE 语句指定一个表，然后在 SET 关键字后指定一组要更改的字段和值，然后是一个可选的 WHERE 子句以选择要更新的行。单个 UPDATE 语句将更改所有匹配 WHERE 子句的行。如果未指定 WHERE 子句，则对表中的所有行执行 UPDATE。

这 4 条基本 SQL 命令（INSERT、SELECT、UPDATE 和 DELETE）允许执行创建和维护数据所需的 4 种基本操作。

16.1.6. 使用数据库对 Twitter 进行爬虫¶

在本节中，我们将创建一个简单的爬虫程序，该程序将遍历 Twitter 账户并建立它们的数据库。注意：运行此程序时要非常小心。您不希望拉取太多数据或将程序运行太久长而导致 Twitter 访问被切断。

任何类型的爬虫程序的问题之一是，它需要能够多次停止和重新启动，并且您不希望丢失到目前为止检索到的数据。您不希望总是从最开始重新检索数据，因此我们希望在我们检索数据时将其存储，以便我们的程序可以重新启动并从中断的地方继续。

我们将首先检索某人的 Twitter 好友及其状态，遍历好友列表，并将每位好友添加到数据库中以便将来检索。在处理完某人的 Twitter 好友后，我们在数据库中检查并检索该好友的一位好友。我们反复执行此操作，选择一位“未访问”的人，检索其好友列表，并将我们尚未见过的朋友添加到我们的列表中，以便将来访问。

我们还追踪在数据库中看到特定朋友的次数，以了解他们的“受欢迎程度”。

通过将我们已知的账户列表、是否已检索到该账户以及账户的流行度存储到计算机磁盘上的数据库中，我们可以随意停止并重新启动我们的程序。

该程序略显复杂。它基于本书前面练习中使用的 Twitter API 代码。

这是我们的 Twitter 爬虫应用程序的源代码：

from urllib.request import urlopen
import urllib.error
import twurl
import json
import sqlite3
import ssl

TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json'

conn = sqlite3.connect('spider.sqlite')
cur = conn.cursor()

cur.execute('''
            CREATE TABLE IF NOT EXISTS Twitter
            (name TEXT, retrieved INTEGER, friends INTEGER)''')

# Ignore SSL certificate errors
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

while True:
    acct = input('Enter a Twitter account, or quit: ')
    if (acct == 'quit'): break
    if (len(acct) < 1):
        cur.execute('SELECT name FROM Twitter WHERE retrieved = 0 LIMIT 1')
        try:
            acct = cur.fetchone()[0]
        except:
            print('No unretrieved Twitter accounts found')
            continue

    url = twurl.augment(TWITTER_URL, {'screen_name': acct, 'count': '20'})
    print('Retrieving', url)
    connection = urlopen(url, context=ctx)
    data = connection.read().decode()
    headers = dict(connection.getheaders())

    print('Remaining', headers['x-rate-limit-remaining'])
    js = json.loads(data)
    # Debugging
    # print json.dumps(js, indent=4)

    cur.execute('UPDATE Twitter SET retrieved=1 WHERE name = ?', (acct, ))

    countnew = 0
    countold = 0
    for u in js['users']:
        friend = u['screen_name']
        print(friend)
        cur.execute('SELECT friends FROM Twitter WHERE name = ? LIMIT 1',
                    (friend, ))
        try:
            count = cur.fetchone()[0]
            cur.execute('UPDATE Twitter SET friends = ? WHERE name = ?',
                        (count+1, friend))
            countold = countold + 1
        except:
            cur.execute('''INSERT INTO Twitter (name, retrieved, friends)
                        VALUES (?, 0, 1)''', (friend, ))
            countnew = countnew + 1
    print('New accounts=', countnew, ' revisited=', countold)
    conn.commit()

cur.close()

# Code: http://www.py4e.com/code3/twspider.py

我们的数据库存储在文件 spider.sqlite 中，它包含一个名为 Twitter 的表。Twitter 表中的每一行都有一个列用于存储账户名称、我们是否已获取该账户的朋友列表，以及该账户被“添加为朋友”的次数。

在主循环中，我们提示用户输入 Twitter 账户名称或"quit"以退出程序。如果用户输入了一个 Twitter 账户，我们检索该用户的联系人列表和状态，并将每个联系人添加到数据库中（如果尚未在数据库中）。如果联系人已经在列表中，我们将数据库行中的 friends 字段加 1。

如果用户按下回车键，我们就在数据库中查找下一个尚未检索的 Twitter 账户，检索该账户的朋友和状态，将它们添加到数据库中或更新它们，并增加其``friends``计数。

一旦我们获取了好友和状态的列表，我们就遍历返回的 JSON 中的所有``user``个项，并为每个用户获取``screen_name``。然后我们使用``SELECT``语句来检查是否已经在数据库中存储了该特定的``screen_name``，如果记录存在则获取好友计数（friends）。

countnew = 0
countold = 0
for u in js['users'] :
    friend = u['screen_name']
    print(friend)
    cur.execute('SELECT friends FROM Twitter WHERE name = ? LIMIT 1',
        (friend, ) )
    try:
        count = cur.fetchone()[0]
        cur.execute('UPDATE Twitter SET friends = ? WHERE name = ?',
            (count+1, friend) )
        countold = countold + 1
    except:
        cur.execute('''INSERT INTO Twitter (name, retrieved, friends)
            VALUES ( ?, 0, 1 )''', ( friend, ) )
        countnew = countnew + 1
print('New accounts=',countnew,' revisited=',countold)
conn.commit()

一旦游标执行了``SELECT``语句，我们必须检索行。我们可以使用``for``语句来完成此操作，但由于我们只检索一行（LIMIT 1），因此可以使用``fetchone()``方法来获取``SELECT``操作的结果中的第一行（也是唯一一行）。由于``fetchone()``将行作为 元组 返回（即使只有一个字段），我们使用索引从元组中获取第一个值，将其放入变量``count``中。

如果此次检索成功，我们使用带有 `WHERE` 子句的 SQL `UPDATE` 语句，将匹配朋友账户的行中的 `friends` 列的值加 1。请注意，SQL 中有两个占位符（即问号），而 `execute()` 的第二个参数是一个包含两个元素的元组，用于将值替换到 SQL 中的问号位置。

如果 try 块中的代码失败，那可能是因为没有任何记录匹配 SELECT 语句中的 WHERE name = ? 子句。因此，在 except 块中，我们使用 SQL INSERT 语句将朋友的 screen_name 添加到表中，并标记我们尚未检索 screen_name，同时将朋友计数设置为 1。

因此，当程序首次运行且我们输入一个 Twitter 账户时，程序的运行方式如下：

Enter a Twitter account, or quit: drchuck
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 20  revisited= 0
Enter a Twitter account, or quit: quit

由于这是第一次运行该程序，数据库为空，我们在文件``spider.sqlite``中创建数据库，并向数据库中添加一个名为``Twitter``的表。然后，我们检索一些朋友并将它们全部添加到数据库中，因为数据库是空的。

此时，我们可能希望编写一个简单的数据库导出程序，以查看我们的 spider.sqlite 文件中的内容：

import sqlite3

conn = sqlite3.connect('spider.sqlite')
cur = conn.cursor()
cur.execute('SELECT * FROM Twitter')
count = 0
for row in cur:
    print(row)
    count = count + 1
print(count, 'rows.')
cur.close()

# Code: http://www.py4e.com/code3/twdump.py

该程序简单地打开数据库并选择表中所有行的所有列 Twitter，然后遍历行并打印每一行。

如果我们在上述 Twitter 爬虫首次运行后再次运行此程序，其输出将如下所示：

('opencontent', 0, 1)
('lhawthorn', 0, 1)
('steve_coppin', 0, 1)
('davidkocher', 0, 1)
('hrheingold', 0, 1)
...
20 rows.

我们能看到每一行对应一个 screen_name，表示我们尚未检索到该 screen_name 的数据，且数据库中的每个人都有一位朋友。

现在我们的数据库反映了我们第一个 Twitter 账户（drchuck）的朋友的检索结果。我们可以再次运行该程序，并通过简单地按 Enter 键而不是输入 Twitter 账户来检索下一个“未处理”账户的朋友，如下所示：

Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 18  revisited= 2
Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 17  revisited= 3
Enter a Twitter account, or quit: quit

由于我们按下了回车键（即，我们没有指定一个 Twitter 账户），以下代码被执行：

if ( len(acct) < 1 ) :
    cur.execute('SELECT name FROM Twitter WHERE retrieved = 0 LIMIT 1')
    try:
        acct = cur.fetchone()[0]
    except:
        print('No unretrieved twitter accounts found')
        continue

我们使用 SQL SELECT 语句来获取第一个（LIMIT 1）仍将其“是否已检索该用户”值设置为零的用户姓名。我们还在 try/except 块内使用 fetchone()[0] 模式，要么从检索到的数据中提取 screen_name，要么输出错误消息并循环回到上方。

如果成功获取了未处理的 screen_name，则按如下方式获取其数据：

url=twurl.augment(TWITTER_URL,{'screen_name': acct,'count': '20'})
print('Retrieving', url)
connection = urllib.urlopen(url)
data = connection.read()
js = json.loads(data)

cur.execute('UPDATE Twitter SET retrieved=1 WHERE name = ?',(acct, ))

一旦我们成功检索到数据，我们就使用 UPDATE 语句将 retrieved 列设置为 1，以表明我们已经完成了对该账户朋友的检索。这防止我们反复检索相同的数据，并使我们能够继续向前推进 Twitter 朋友网络。

如果我们运行 friend 程序并按下两次 Enter 键以获取下一个未访问朋友的 friend，然后运行 dumping 程序，它将给出以下输出：

('opencontent', 1, 1)
('lhawthorn', 1, 1)
('steve_coppin', 0, 1)
('davidkocher', 0, 1)
('hrheingold', 0, 1)
...
('cnxorg', 0, 2)
('knoop', 0, 1)
('kthanos', 0, 2)
('LectureTools', 0, 1)
...
55 rows.

我们可以看到，我们已经正确记录了已访问过 lhawthorn 和 opencontent。此外，账户 cnxorg 和 kthanos 已经有两个关注者。由于我们现在已获取了三个人（drchuck、opencontent 和 lhawthorn）的朋友列表，因此我们的表中有 55 行朋友记录待检索。

每次运行程序并按下回车键时，它会选取下一个未访问的账户（例如，下一个账户将是 steve_coppin），检索其好友，将其标记为已检索，并且对于 steve_coppin 的每个好友，要么将其添加到数据库末尾，要么如果该好友已在数据库中，则更新其好友计数。

由于程序的数据全部存储在数据库的磁盘上，因此可以随意暂停和恢复爬虫活动，而不会丢失任何数据。

16.1.7. 基础数据建模¶

关系型数据库的真正力量在于我们创建多个表并在这些表之间建立链接。决定如何将应用程序数据拆分为多个表并建立表之间关系的行为称为 数据建模。展示表及其关系的文档称为 数据模型。

数据建模是一项相对复杂的技能，本节仅介绍关系数据建模的最基本概念。有关数据建模的更多细节，您可以从以下资源开始：

http://en.wikipedia.org/wiki/Relational_model

假设对于我们的 Twitter 爬虫应用程序，我们不仅计算一个人的朋友数量，还想要保留所有传入关系列表，以便找出关注某个特定账户的所有人。

由于每个人可能拥有许多关注他们的账户，我们无法简单地向我们的 Twitter 表中添加单个列。因此，我们创建一个新的表来记录朋友对。以下是创建此类表的简单方法：

CREATE TABLE Pals (from_friend TEXT, to_friend TEXT)

每次遇到一个``drchuck``正在关注的人时，我们都会插入一行如下形式的记录：

INSERT INTO Pals (from_friend,to_friend) VALUES ('drchuck', 'lhawthorn')

在处理来自 drchuck 推特源的 20 位朋友时，我们将插入 20 条记录，其中"drchuck"作为第一个参数，因此我们将在数据库中多次重复该字符串。

这种字符串数据的重复违反了 数据库规范化 的最佳实践之一，该实践基本规定我们不应将相同的字符串数据在数据库中存储超过一次。如果我们需要多次使用该数据，我们为数据创建一个数字 键，并使用此键引用实际数据。

在实际应用中，字符串在磁盘和计算机内存中占用的空间远大于整数，且进行比较和排序所需的处理器时间也更多。如果只有几百条记录，存储空间和处理器时间几乎可以忽略不计。但如果我们的数据库中有百万级用户，且存在一亿条好友链接的可能性，那么尽可能快速地扫描数据就显得尤为重要。

我们将把 Twitter 账户存储在名为 People 的表中，而不是上一示例中使用的 Twitter 表。People 表增加了一列，用于存储与该 Twitter 用户行关联的数值键。SQLite 具有一种功能，可以自动为使用特殊数据类型列（INTEGER PRIMARY KEY）插入到表中的任何行添加键值。

我们可按如下方式创建包含此额外 id 列的 People 表：

CREATE TABLE People
    (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)

注意，我们不再在 People 表的每一行中维护朋友计数。当我们选择 INTEGER PRIMARY KEY 作为 id 列的类型时，我们表示希望 SQLite 管理该列，并为我们插入的每一行自动分配一个唯一的数字键。我们还添加了关键字 UNIQUE，以指示不允许 SQLite 插入两行具有相同 name 值的记录。

现在，我们不再创建上面的表 Pals，而是创建一个名为 Follows 的表，该表包含两个整数列 from_id 和 to_id，并对该表施加一个约束，即 from_id 和 to_id 的 组合 在该表中必须是唯一的（即，我们不能在数据库中插入重复的行）。

CREATE TABLE Follows
    (from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id) )

当我们向表中添加 UNIQUE 子句时，我们是在向数据库传达一组规则，要求我们在尝试插入记录时强制执行这些规则。我们创建这些规则是为了方便我们的程序，正如我们即将看到的。这些规则既防止我们犯错，又使编写部分代码变得更加简单。

本质上，在创建此 Follows 表时，我们是在对一个“关系”进行建模，即一个人“关注”另一个人，并用一对数字来表示：(a) 人们之间存在连接，(b) 关系的方向。

表之间的关系

16.1.8. 使用多张表进行编程¶

我们现在将使用两个表重新编写 Twitter 爬虫程序，如上文所述，使用键和关键引用。以下是该程序新版本的代码：

import urllib.request, urllib.parse, urllib.error
import twurl
import json
import sqlite3
import ssl

TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json'

conn = sqlite3.connect('friends.sqlite')
cur = conn.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS People
            (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Follows
            (from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))''')

# Ignore SSL certificate errors
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

while True:
    acct = input('Enter a Twitter account, or quit: ')
    if (acct == 'quit'): break
    if (len(acct) < 1):
        cur.execute('SELECT id, name FROM People WHERE retrieved=0 LIMIT 1')
        try:
            (id, acct) = cur.fetchone()
        except:
            print('No unretrieved Twitter accounts found')
            continue
    else:
        cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1',
                    (acct, ))
        try:
            id = cur.fetchone()[0]
        except:
            cur.execute('''INSERT OR IGNORE INTO People
                        (name, retrieved) VALUES (?, 0)''', (acct, ))
            conn.commit()
            if cur.rowcount != 1:
                print('Error inserting account:', acct)
                continue
            id = cur.lastrowid

    url = twurl.augment(TWITTER_URL, {'screen_name': acct, 'count': '100'})
    print('Retrieving account', acct)
    try:
        connection = urllib.request.urlopen(url, context=ctx)
    except Exception as err:
        print('Failed to Retrieve', err)
        break

    data = connection.read().decode()
    headers = dict(connection.getheaders())

    print('Remaining', headers['x-rate-limit-remaining'])

    try:
        js = json.loads(data)
    except:
        print('Unable to parse json')
        print(data)
        break

    # Debugging
    # print(json.dumps(js, indent=4))

    if 'users' not in js:
        print('Incorrect JSON received')
        print(json.dumps(js, indent=4))
        continue

    cur.execute('UPDATE People SET retrieved=1 WHERE name = ?', (acct, ))

    countnew = 0
    countold = 0
    for u in js['users']:
        friend = u['screen_name']
        print(friend)
        cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1',
                    (friend, ))
        try:
            friend_id = cur.fetchone()[0]
            countold = countold + 1
        except:
            cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)
                        VALUES (?, 0)''', (friend, ))
            conn.commit()
            if cur.rowcount != 1:
                print('Error inserting account:', friend)
                continue
            friend_id = cur.lastrowid
            countnew = countnew + 1
        cur.execute('''INSERT OR IGNORE INTO Follows (from_id, to_id)
                    VALUES (?, ?)''', (id, friend_id))
    print('New accounts=', countnew, ' revisited=', countold)
    print('Remaining', headers['x-rate-limit-remaining'])
    conn.commit()
cur.close()

# Code: http://www.py4e.com/code3/twfriends.py

该程序开始变得有些复杂，但它说明了在使用键将表连接起来时我们需要使用的模式。基本模式如下：

1. 创建具有键和约束的表。

2. 当我们拥有一个人的逻辑键（即账户名称）且需要该人的``id``值时，取决于该人是否已存在于``People``表中，我们需要：(1) 在``People``表中查找该人并检索该人的``id``值，或 (2) 将该人添加到``People``表中并获取新添加行的``id``值。

3. 插入记录“关注”关系的行。

我们将逐一讨论这些内容。

cur.execute('''CREATE TABLE IF NOT EXISTS People
    (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Follows
    (from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))''')

cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)
    VALUES ( ?, 0)''', ( friend, ) )

cur.execute('''INSERT OR IGNORE INTO Follows
    (from_id, to_id) VALUES (?, ?)''', (id, friend_id) )

friend = u['screen_name']
cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1',
    (friend, ) )
try:
    friend_id = cur.fetchone()[0]
    countold = countold + 1
except:
    cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)
        VALUES ( ?, 0)''', ( friend, ) )
    conn.commit()
    if cur.rowcount != 1 :
        print('Error inserting account:',friend)
        continue
    friend_id = cur.lastrowid
    countnew = countnew + 1

cur.execute('INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)',
    (id, friend_id) )

Enter a Twitter account, or quit:
No unretrieved Twitter accounts found
Enter a Twitter account, or quit: drchuck
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 20  revisited= 0
Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 17  revisited= 3
Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/1.1/friends ...
New accounts= 17  revisited= 3
Enter a Twitter account, or quit: quit

People:
(1, 'drchuck', 1)
(2, 'opencontent', 1)
(3, 'lhawthorn', 1)
(4, 'steve_coppin', 0)
(5, 'davidkocher', 0)
55 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
60 rows.

16.1.9. 三种键¶

现在我们已经开始构建数据模型，将数据放入多个关联表中，并使用 键 关联这些表中的行，我们需要查看一些关于键的术语。在数据库模型中通常有三种键。

• 一个 逻辑键 是“现实世界”中可能用于查找行的键。在我们的示例数据模型中，name 字段是一个逻辑键。它是用户的屏幕名称，我们确实多次使用 name 字段在程序中查找用户的行。你经常会发现，为逻辑键添加 UNIQUE 约束是有意义的。由于逻辑键是我们从外部世界查找行的方式，因此允许表中存在具有相同值的多个行是没有意义的。

• 一个 键 通常是由数据库自动分配的数字。它在程序之外通常没有意义，仅用于将不同表中的行关联在一起。当我们想要在表中查找某一行时，使用键搜索该行通常是找到该行的最快方式。由于键是整数，它们占用的存储空间非常少，并且可以非常快速地进行比较或排序。在我们的数据模型中，id 字段就是一个键的例子。

• 一个 外键 通常是一个数字，它指向不同表中相关行的键。我们数据模型中外键的一个示例是 from_id。

我们采用的命名约定是：主键字段名始终称为 id，而对于任何作为外键的字段名，则在其后追加后缀 _id。

16.1.10. 使用 JOIN 检索数据¶

现在我们已经遵循了数据库规范化规则，并将数据分离到两个表中，这两个表通过主键和外键相互关联，我们需要能够构建一个``SELECT``来跨表重新组合数据。

SQL 使用 JOIN 子句将这些表重新连接。在 JOIN 子句中，您指定用于在表之间重新连接行的字段。

以下是一个带有 JOIN 子句的 SELECT 示例：

SELECT * FROM Follows JOIN People
    ON Follows.from_id = People.id WHERE People.id = 1

JOIN 子句表明我们要选择的字段跨越了 Follows 和 People 这两张表。ON 子句表明了这两张表的连接方式：从 Follows 中取出行，并在 Follows 中的 from_id 字段与 People 表中的 id 值相同时，追加来自 People 的行。

使用 JOIN 连接表

JOIN 的结果是创建超长的“元行”，其中包含来自 People 的字段以及来自 Follows 的匹配字段。当 People 中的 id 字段与 People 中的 from_id 之间存在多个匹配时，JOIN 会为每对匹配的行创建一个元行，并根据需要重复数据。

以下代码演示了在我们多次运行多表 Twitter 爬虫程序（上文）后，数据库中将会拥有的数据。

import sqlite3

conn = sqlite3.connect('friends.sqlite')
cur = conn.cursor()

cur.execute('SELECT * FROM People')
count = 0
print('People:')
for row in cur:
    if count < 5: print(row)
    count = count + 1
print(count, 'rows.')

cur.execute('SELECT * FROM Follows')
count = 0
print('Follows:')
for row in cur:
    if count < 5: print(row)
    count = count + 1
print(count, 'rows.')

cur.execute('''SELECT * FROM Follows JOIN People
            ON Follows.to_id = People.id
            WHERE Follows.from_id = 2''')
count = 0
print('Connections for id=2:')
for row in cur:
    if count < 5: print(row)
    count = count + 1
print(count, 'rows.')

cur.close()

# Code: http://www.py4e.com/code3/twjoin.py

在此程序中，我们首先输出``People``和``Follows``，然后输出表中连接后的数据子集。

这是程序的输出：

python twjoin.py
People:
(1, 'drchuck', 1)
(2, 'opencontent', 1)
(3, 'lhawthorn', 1)
(4, 'steve_coppin', 0)
(5, 'davidkocher', 0)
55 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
60 rows.
Connections for id=2:
(2, 1, 1, 'drchuck', 1)
(2, 28, 28, 'cnxorg', 0)
(2, 30, 30, 'kthanos', 0)
(2, 102, 102, 'SomethingGirl', 0)
(2, 103, 103, 'ja_Pac', 0)
20 rows.

您能看到来自 `People` 和 `Follows` 表的列，且最后一组行是 `SELECT` 与 `JOIN` 子句的结果。

在最后的查询中，我们正在查找与"opencontent"（即``People.id=2``）是朋友关系的账户。

在最后一次选择中的每个“元行”（metarow）中，前两列来自 Follows 表，随后三列（第三至第五列）来自 People 表。您还可以看到，在每一个连接后的“元行”中，第二列（Follows.to_id）与第三列（People.id）相匹配。

16.1.11. 概要¶

本章涵盖了大量内容，以概述在 Python 中使用数据库的基础知识。编写用于将数据存储到数据库的代码比使用 Python 字典或平面文件更为复杂，因此除非您的应用程序真正需要数据库的功能，否则没有理由使用数据库。数据库非常适用的情况包括：(1) 当您的应用程序需要在大型数据集中进行许多小型随机更新时，(2) 当您的数据量如此之大以至于无法放入字典中，且您需要反复查找信息时，或 (3) 当您有一个长运行进程，并希望能够在停止和重新启动后保留从一个运行到下一个运行的数据时。

您可以构建一个简单的单表数据库以满足许多应用需求，但大多数问题将需要多个表以及不同表行之间的链接/关系。当您开始在表之间建立链接时，重要的是要进行一些深思熟虑的设计并遵循数据库规范化规则，以充分利用数据库的功能。由于使用数据库的主要动机是您需要处理大量数据，因此高效地对数据进行建模至关重要，以使您的程序尽可能快地运行。

16.1.12. 调试¶

当你开发一个用于连接 SQLite 数据库的 Python 程序时，一种常见模式是运行 Python 程序并使用 SQLite 数据库浏览器检查结果。该浏览器允许你快速检查程序是否正常工作。

你必须小心，因为 SQLite 会确保两个程序不会同时更改相同的数据。例如，如果你在浏览器中打开数据库并对数据库进行更改，但尚未在浏览器中按下“保存”按钮，浏览器将“锁定”数据库文件并阻止任何其他程序访问该文件。特别是，如果文件被锁定，你的 Python 程序将无法访问该文件。

因此，解决方案是在尝试从 Python 访问数据库之前，确保关闭数据库浏览器或使用 File 菜单在浏览器中关闭数据库，以避免因数据库被锁定而导致 Python 代码失败的问题。

16.1.13. 术语表¶

attribute

元组内的一个值。更常称为“列”或“字段”。

constraint

当我们指示数据库对表中的某个字段或某一行强制执行规则时。一种常见的约束是坚持特定字段中不能有重复值（即所有值必须唯一）。

cursor

游标允许您在数据库中执行 SQL 命令并从数据库检索数据。游标分别类似于用于网络连接和文件的套接字或文件句柄。

database browser

一种软件，允许您直接连接到数据库并直接操作数据库，而无需编写程序。

foreign key

一个数字键，指向另一个表中某一行的主键。外键建立存储在不同表中的行之间的关系。

index

数据库软件作为行维护并插入到表中的附加数据，以使查找非常快速。

logical key

“外部世界”用来查找特定行的键。例如，在用户账户表中，个人的电子邮件地址可能是用户数据的逻辑键的良好候选。

normalization

设计数据模型以便没有数据被复制。我们将每个数据项存储在数据库中的一个位置，并使用外键在其他地方引用它。

primary key

分配给每一行的数字键，用于从另一个表引用表中的一行。通常配置数据库以便在插入行时自动分配主键。

relation

数据库中包含元组和属性的区域。更通常称为“表”。

tuple

数据库表中的单个条目，是一组属性。更通常称为“行”。

1. SQLite 实际上允许在列中存储的数据类型具有一定的灵活性，但本章我们将保持数据类型的严格性，以便这些概念同样适用于 MySQL 等其他数据库系统。↩︎

2. 通常，当句子以“如果一切顺利”开头时，你会发现代码需要使用 try/except。↩︎

如果您在本书中发现错误，欢迎使用 Github 向我发送修正建议。