第 14 章 数据库与结构化查询语言 SQL
14.1 什么是数据库
数据库是经过组织的、存储数据的文件。从这个意义上讲，大多数数据库的组织方式与字典类似，数据库实现键与值之间的映射。数据库与字典的最大区别在于，数据库存储在磁盘（或其他永久存储器）上，程序运行结束后数据会永久存在。正是由于数据库存储在永久存储上，它能存储的数据远远多于字典。字典受到计算机内存大小的限制。
与字典类似，数据库软件被设计为快速保留插入的与访问的数据，即使是大量数据的情况亦如此。数据库软件通过对添加的数据构建索引来维护性能，让计算机可以快速跳转到特定数据项。
广泛应用的数据库系统包括 Oracle、MySQL、Microsoft SQL Server、 PostgreSQL 和 SQLite。本书关注 SQLite，因为它是一个非常通用的数据库，而且已经内建在 Python。SQLite 被设计为嵌入到其他应用程序，提供应用程序内的数据库支持。例如，Firefox 浏览器把 SQLite 数据库作为内部使用，其他很多产品也这样做。
SQLite 非常适合信息科学中的一些数据处理问题，比如本章介绍的 Twitter 爬虫应用。
14.2 数据库概念
初次接触数据库，可将其视为多个工作表的电子表格。数据库的主要数据结构包括表、行与列。
在关系型数据库中，表、行与列的专业定义为关系、元组与属性。本章将使用非专业化术语。
14.3 SQLite 管理器（Firefox 插件）
本章重点使用 Python 对 SQLite 数据库文件进行操作，许多操作可以用 SQLite 数据库管理器（一个 Firefox 插件）更方便地完成。免费下载地址如下：
https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/
使用 Firefox 浏览器可以在数据库中轻松创建表、插入数据、编辑数据、以及执行简单的 SQL 查询。
从某种意义上讲，在文本文件的处理方面，数据库管理器与文本编辑器类似。如果要对文本文件进行少量修改操作，你可以在文本编辑器中打开它，并根据需要修改。如果要对文本文件进行大量修改时，通常需要编写一个简单的 Python 程序。类似的，数据库同样存在相同的模式。在数据库管理器中执行一些简单操作，在 Python 中可以方便地处理一些复杂操作。
14.4 创建一张数据库的表
与 Python 的列表与字典相比，数据库需要更多的结构定义1 。
创建一个数据库的表，我们必须根据每一列存储的数据情况，预先在数据库中定义表的每一列名称和数据类型。数据库软件知道了每一列的数据类型，根据特定数据类型，它可以选择最有效的数据存储与检索方法。
以下网址介绍了 SQLite 支持的各种数据类型：
http://www.sqlite.org/datatypes.html
一开始就定义好数据结构可能不是很方便，但是这样做的好处是，当数据库包含大量数据可以提供快速的数据访问。
以下代码创建了一个数据库文件和带有两列的 Tracks 表。
import sqlite3
conn = sqlite3.connect('music.sqlite3')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks ')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()
connect 操作建立了与当前目录中 music.sqlite3 数据库文件的“连接”。如果文件不存在，则创建它。之所以称为“连接”，因为数据库有时存储在单独的数据库服务器上，与我们运行的应用程序不在同一个服务器上。在这个简单示例中，数据库作为一个本地文件，与 Python 代码处在同一个目录下。
游标（cursor）类似一个文件句柄，可以对数据库中的数据执行操作。当处理文本文件时，cursor() 的调用与 open() 方法相似。
当有个游标，我们使用 execute() 方法，开始对数据库的内容执行命令。
数据库命令使用专门的语言，在众多数据库厂商中间已经标准化，用户只需学习一种数据库语言即可。数据库语言称为结构化查询语言，简称为 SQL。
http://en.wikipedia.org/wiki/SQL
在这个例子中，我们对数据库执行两条 SQL 命令。按照惯例，我们用大写显示 SQL 关键词，其他部分如表和列名显示为小写。
如果 Track 表已经存在，第一条 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.sqlite3')
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()
首先，我们向表中插入两行，使用 commit() 提交命令将数据写入数据库文件。
然后，我们用 SELECT 命令检索刚插入表中的行。SELECT 命令首先指定（title, plays）列，之后是数据检索的来源表。执行 SELECT 语句后，游标可以让我们用 for 语句进行循环。为了提高效率，当执行 SELECT 语句时，游标并不会从数据库中读取所有数据。相反，数据是在 for 循环时按需读取。
程序运行结果如下：
Tracks:
(u'Thunderstruck', 20)
(u'My Way', 15)
for 循环找到两行，每一行是一个 Python 元组，其中第一个值是歌曲名称（title），第二个值是播放次数（plays）。不用担心，title 字符串以 u’开头。这说明字符串使用 Unicode，即能够存储非拉丁字符集。
在程序末尾，我们执行 SQL 的 DELETE 命令，删除刚才创建的行，以便可以反复运行这个程序。DELETE 命令使用了 WHERE 子句，用来表达一个选择条件，这样 SQL 命令在数据库中只对条件匹配的行进行操作。在本示例中，条件应用于所有行，因此我们可以清空表，反复执行程序。在 DELETE 命令执行后，使用 commit() 提交命令将数据从数据库中删除。
14.5 结构化查询语言 SQL 小结
至此，我们在 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 Tracks SET plays = 16 WHERE title = 'My Way'
UPDATE 语句先指明待更新的表，在 SET 关键词之后设置修改的字段及其取值，然后可以用 WHERE 子句（可选的）选择要更新的行。一个 UPDATE 语句会修改 WHERE 子句匹配到的所有行，若不指定 WHERE 子句，它将更新表中所有的行。
以上是数据创建与维护的四个基本 SQL 命令（INSERT、 SELECT、 UPDATE 和 DELETE）。
14.6 使用数据库爬取 Twitter
在本节中，我们编写一个简单的爬虫程序，通过 Twitter 账号采集数据，然后建立数据库。请注意：谨慎执行这个程序，不要抓取太多数据或长时间执行程序，这样会导致你的 Twitter 账号被封。
任何类型的爬虫程序都面临一个问题，它需要能被停止和重启多次，你也不想丢失已获取到的数据。你不希望总是在一开始重启数据检索，因此把检索到的数据存储起来，让爬虫程序能启动备份，并在它离开的地方继续检索。
我们通过检索一个用户的 Twitter 朋友及他们的状态，循环朋友列表，向数据库添加每个朋友的信息，以备后续检索。当处理了一个用户的 Twitter 朋友，我们登录数据库，检索朋友中的一个。重复这个操作，挑选一个“未访问过的”用户，检索他的用户列表，添加列表中没有的朋友，以备下次访问。
我们也追踪数据库中特定朋友的出现次数，以此查看“人气”情况。
通过存储已知账号的列表，不论是否检索这个账号，数据库中账号的人气情况已经存储在计算机磁盘，这样停止或重启程序多少次都没关系。
这个程序有些复杂，它基于前面的 Twitter API 程序代码。
Twitter 爬虫程序源代码如下：
import urllib
import twurl
import json
import sqlite3
TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json'
conn = sqlite3.connect('spider.sqlite3')
cur = conn.cursor()
cur.execute("'
CREATE TABLE IF NOT EXISTS Twitter
(name TEXT, retrieved INTEGER, friends INTEGER)"')
while True:
acct = raw_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 = urllib.urlopen(url)
data = connection.read()
headers = connection.info().dict
# print 'Remaining', headers['x-rate-limit-remaining']
js = json.loads(data)
# 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()
数据库存在于 spider.sqlite3 文件中，包括一个 Twitter 表。Twitter 表的每一行包括账号名、是否检索过这个账号的朋友以及这个账号被加好友的次数。
在程序的主循环中，提示用户输入一个 Twitter 账号名或退出程序。如果用户输入一个 Twitter 账号，程序就检索朋友列表和用户状态，如果数据库中没有这个朋友，则添加进去。如果该朋友已经存在于列表中，我们对 friends 字段值加一。
当用户按下回车键，在数据库中寻找下一个还未检索过的 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() 以元组返回行，即使仅有一个字段也是如此。我们用[0]取出元组的第一个值，得到变量 count 的当前朋友数。
如果获取成功，我们使用 SQL 的 UPDATE 语句和 WHERE 子句，对匹配到的朋友账号所在行的 friends 列值加一。请注意，SQL 语句中有两个占位符（即问号）， execute() 的第二个参数是两元素元组，其中的值会替换 SQL 的占位符。
如果 try 区块的代码失效，可能是因为 SELECT 语句的 WHERE name = ?子句没有匹配到记录。在 except 区块，我们使用 SQL 的 INSERT 语句，向表中添加朋友的 screen_name，另外一个指示符表示我们还没有获取到 screen_name，这时将朋友数设为 0。
第一次执行程序，输入一个 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.sqlite3，向数据库添加一张 Twitter 表。然后获取一些朋友，将他们的信息存储到之前空的数据库中。
此时，我们想要编写一个简单的数据库导出程序，用来查看 spider.sqlites3 文件：
import sqlite3
conn = sqlite3.connect('spider.sqlite3')
cur = conn.cursor()
cur.execute('SELECT * FROM Twitter')
count = 0
for row in cur :
print row
count = count + 1
print count, 'rows.'
cur.close()
如果再次执行 Twitter 爬虫程序，程序运行结果如下：
(u'opencontent', 0, 1)
(u'lhawthorn', 0, 1)
(u'steve_coppin', 0, 1)
(u'davidkocher', 0, 1)
(u'hrheingold', 0, 1)
...
20 rows.
我们看到每个 screen_name 有一行，没有获取该字段本身的数据，数据库中每人有一个朋友。
现在，在数据库里可以看到第一个 Twitter 账号（drchuck）的朋友已经获取到。我们再次执行这个程序，只需按下回车键，不用再输入 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），但该用户的“是否访问过”字段值还是 0。我们还在 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 程序，按两次回车键，检索下一个未被访问的朋友的朋友，然后执行 dumping 程序，程序输出结果如下：
(u'opencontent', 1, 1)
(u'lhawthorn', 1, 1)
(u'steve_coppin', 0, 1)
(u'davidkocher', 0, 1)
(u'hrheingold', 0, 1)
...
(u'cnxorg', 0, 2)
(u'knoop', 0, 1)
(u'kthanos', 0, 2)
(u'LectureTools', 0, 1)
...
55 rows.
由此可见，我们正确记录了已经访问过的 lhawthorn 和 opencontent 两个账号的信息。另外，cnxorg 和 kthanos 已经有了粉丝。由于已经检索了三个用户（drchuck、opencontent 与 lhawthorn）的朋友，表中已有 55 行。
每次执行程序与按下回车键时，它会选择下一个未访问的账号（这里的下一个账号是 steve_coppin），获取他们的朋友，标记他们，循环 steve_coppin 的每一位朋友，将他们添加到数据库。如果他们已经存在于数据库，更新他们的朋友数。
由于程序的数据全部存储在数据库的磁盘上，爬虫活动可以被任意多次暂停或继续，数据都不会丢失。
14.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 个朋友的 Twitter 消息源（feed），我们插入“drchuck”作为第一参数的 20 条记录，这个字符串在数据库重复出现了多次。
重复的字符串数据破坏了数据库规范化的最佳实践。数据库规范化指相同的字符串数据在数据库只能存在一处。如果需要数据出现多次，要为数据创建一个数字键，通过该键引用实际的数据。
在实际应用中，字符串比整数在计算机磁盘与内存上占用更多空间，处理器也需要更多时间进行比较和排序。如果仅有几百条数据，那么存储与处理器耗时并没什么问题。但当数据库中包括百万用户，以及可能的一亿朋友链接，尽可能快速扫描数据就显得非常重要了。
我们把 Twitter 账号存储在 People 表，而不是之前示例的 Twitter 表。People 表用额外的一列来存储与该 Twitter 用户的行数相关的数值键。SQLite 的 INTEGER PRIMARY KEY 这一特殊的数据列类型能为插入的任一行自动增加键值。
新建 People 表，包括一个额外的 id 列：
CREATE TABLE People
(id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)
请注意，我们不再维护 People 表每一行的朋友数。当选择 INTEGER PRIMARY KEY 作为 id 列的字段类型，这表明我们希望 SQLite 来管理该列，在插入一行时自动赋予唯一的数值键。我们还添加了关键词 UNIQUE，表示不允许 SQLite 为两个行插入相同的值。
与之前创建的 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 表的创建实际是构建了一个“关系”，一个用户是其他人的粉丝，将其表示成一个数值对，代表与他联系的用户，以及关系的方向。
14.8 多表编程
我们使用之前的两个表、主键和键引用来重做 Twitter 爬虫程序。新版本的程序代码如下：
import urllib
import twurl
import json
import sqlite3
TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json'
conn = sqlite3.connect('friends.sqlitesqlite3')
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))"')
while True:
acct = raw_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': '20'} )
print 'Retrieving account', acct
connection = urllib.urlopen(url)
data = connection.read()
headers = connection.info().dict
print 'Remaining', headers['x-rate-limit-remaining']
js = json.loads(data)
# print json.dumps(js, indent=4)
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
conn.commit()
cur.close()
这个程序变得有些复杂了，介绍了通过整数键连接表格的使用模式。基本模式如下：
- 创建带有主键与约束的表。
- 当一个用户（即账号名称）拥有一个逻辑键，我们需要用户的 id 值。根据 People 表中是否有该用户，（1）查找 People 表中的用户，获取该用户的 id 值，或（2）向 People 表添加用户，为新增行添加 id 值。
- 插入一行，表示“粉丝”关系。
以下依次介绍每一个步骤。
14.8.1 数据库表约束
设计表结构时，我们告诉数据库系统强制执行一些规则。这些规则帮助我们避免出错，不要把错误的数据写入表中。创建表的代码如下：
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))"')
我们定义 People 表中的 name 列必须是唯一的（UNIQUE）。同时，定义 Follows 表每一行两个数字的组合必须唯一。这些约束避免了多次添加同一个关系。
以下代码体现了这些约束的优势：
cur.execute("'INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)"', ( friend, ) )
我们在 INSERT 语句中添加 OR IGNORE 子句，这表示如果有一个 INSERT 违反了“name 必须唯一”的规则，那么数据库将忽略这个 INSERT。数据库约束作为一个安全网络，确保我们不会在无意中犯错。
同样地，以下代码确保不会重复添加同一个 Follows 关系。
cur.execute("'INSERT OR IGNORE INTO Follows
(from_id, to_id) VALUES (?, ?)"', (id, friend_id) )
同样地，如果违反了 Follows 行的唯一性约束，只需告诉数据库忽略 INSERT 即可。
14.8.2 检索与插入一条记录
当提示用户输入一个 Twitter 账号，如果账号已存在，我们必须找到它的 id 值。如果 People 表中还没有该账号，我们必须插入一条记录，并得到该插入行的 id 值。
这是一个很常见的模式，在前面的程序中用到过 2 次。当我们从已获取的 Twitter 的 JSON 数据中获取 user 节点的 screen_name，本节代码演示了如何检索一个朋友账号的 id。
随着数据的累积，用户账号可能已经存在于数据库中。我们需要先使用 SELECT 语句，检查 People 表中该账号是或否存在。
如果 try 部分一切进展顺利2 ，我们使用 fetchone() 获取该记录，然后检索返回的元组的第一个（也是唯一）元素，将其存储为 friend_id。
如果 SELECT 执行失败，fetchone()[0]也会失败，然后控制跳转到 except 部分。
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
如果以 except 代码结束，这意味着，没有发现记录，必须插入新行。我们使用 INSERT OR IGNORE 仅是避免出错，然后调用 commit() 来强制数据库对提交进行更新。当写入完成后，我们通过 cur.rowcount 检查有多少行受到影响。由于我们尝试插入一个单行，如果受影响行的数字不是 1，那么将导致错误。
如果 INSERT 执行成功，我们通过 cur.lastrowid 找出数据库为新建行赋予的 id 列值。
14.8.3 存储朋友关系
一旦知道了 JSON 数据中 Twitter 用户与朋友的键值，在 Follows 表中插入两个值就是件简单的事情了，程序代码如下：
cur.execute('INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)',
(id, friend_id) )
请注意，根据表格创建时的唯一性约束，避免了重复插入同一个关系，然后在 INSERT 语句中添加 OR IGNORE。
程序运行结果如下所示：
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
从 drchuck 账号开始，让程序自动获取下两个账号，并添加到数据库。
当程序执行完成后，以下是 People 和 Follows 表的头几行：
People:
(1, u'drchuck', 1)
(2, u'opencontent', 1)
(3, u'lhawthorn', 1)
(4, u'steve_coppin', 0)
(5, u'davidkocher', 0)
55 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
60 rows.
可以看出，People 表中的 id、name 与 visited 字段，关系 Follows 表结尾的数字。在 People 表中，我们看到前三个用户已经被访问过，他们的数据已被获取。Follows 表的数据表明，drchuck（用户 1）是所显示前五行的用户的朋友。这是由于我们获取和存储的第一个数据是 drchuck 的 Twitter 朋友。如果打印出 Follows 表的更多行，就会看到用户 2 和用户 3 的朋友。
14.9 键的三种类型
现在我们已经开始构建数据模型了，将数据放入多个关联表中，使用键来连接这些表中的行。我们需要知道一些有关键的术语。数据库模型中一般存在三种类型的键。
- 逻辑键是“真实世界”中可以检索行的键。在示例数据模型中，name 字段是一个逻辑键。它是用户的屏幕名称，在程序中通过 name 字段多次检索用户的行。你会发现，对一个逻辑键添加 UNIQUE 约束，这是有道理的。由于逻辑键是我们从“外部世界”如何检索表中的一行，允许表中存在相同值的多行没有多大意义。
- 主键通常是由数据库自动赋予的一个数字。它对外部程序而言没有意义，仅用于把来自不同表的行连接在一起。当我们检索表中的行，通常搜索主键是最快的。由于主键是整数值，占用极少的存储空间，能够快速进行比较与排序。在示例数据模型中，id 字段是主键。
- 外键通常是指向不同表中相关行的主键的一个数值。示例数据模型中的外键是 from_id。
我们使用一些命名惯例，比如主键名为 id，那么将_id 后缀添加到对应外键的名称中。
14.10 使用 JSON 获取数据
我们已经了解了数据库规范化原则，将数据分成两个表，通过主键和外键将两个表连接起来，然后通过 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 子句表示，两个表怎样被连接在一起。选取 People 表的行，然后将 Follows 表的 from_id 字段与 People 表的 id 字段值相同的行附加在后面。
连接的结果是创建了一个相当长的“元行”（meta-rows），包括 People 表的字段与 Follows 表中匹配的字段。由于 People 表的 id 字段与 Follows 表中的 from_id 字段之间存在多个匹配，JOIN 会为每一个匹配到的行创建一个元行，根据需要重复数据。
多表数据库驱动的 Twitter 爬虫程序多次执行的代码如下：
import sqlite3
conn = sqlite3.connect('spider.sqlite3')
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.from_id = People.id WHERE People.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()
在这个程序中，我们首先整体导出 People 与 Follows 表，然后导出连接表的数据子集。
程序输出结果如下：
python twjoin.py
People:
(1, u'drchuck', 1)
(2, u'opencontent', 1)
(3, u'lhawthorn', 1)
(4, u'steve_coppin', 0)
(5, u'davidkocher', 0)
55 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
60 rows.
Connections for id=2:
(2, 1, 1, u'drchuck', 1)
(2, 28, 28, u'cnxorg', 0)
(2, 30, 30, u'kthanos', 0)
(2, 102, 102, u'SomethingGirl', 0)
(2, 103, 103, u'ja_Pac', 0)
20 rows.
可以看出，People 与 Follows 表的列和带 JOIN 子句的 SELECT 语句执行后得到的结果行。
最后一次选择中我们找到“opencontent”（即 People.id=2）的朋友账号。
在最后一次选择的每个“元行”中，前两列来自 Follows 表，之后是 People 表 5 个列中的 3 个。连接后的每个元行中第 2 列（Follows.to_id）匹配第 3 列（People.id）。
14.11 小结
本章全面介绍了 Python 中数据库的基本使用方法。与 Python 字典或平面文件相比，编写代码来使用数据库存储数据更加复杂。除非你的应用程序确实需要数据库功能，否则不要轻易使用。数据库的使用优势在于：（1）应用程序需要在大量数据中随机更新一小部分数据；（2）数据量很大，无法用字典来存储，而且需要重复检索信息；（3）在长期运行过程中希望停止或重启，数据可以得以保留，并在下次执行时从中止处继续。
你可以创建单表的简单数据库，以满足多种应用需求。但是，大多数问题都需要多个表与跨表的行之间的链接/关系。为表创建链接时，需要进行周全设计，遵循数据库规范化原则，恰当地运用数据库能力。数据库的使用动机主要需要处理大量数据，有效的数据建模让程序能快速执行，把握住这一点很重要。
14.12 调试
一个常见的 Python 程序开发模式是与 SQLite 数据建立联系，执行 Python 程序，使用 SQLite 数据库浏览器查看结果。这个浏览器可以快速检查程序是否正常执行。
由于 SQLite 在同一时刻会防止两个程序对同一数据的修改。例如，如果在浏览器中打开数据库，修改数据库，在尚未按下保存按钮时，浏览器会锁定数据库文件，以防止其他程序访问该文件。具体而言，如果数据库文件被锁定，你的 Python 程序将不能访问这个文件。
一个解决方法是，在 Python 尝试访问数据库之前，关闭数据库浏览器或使用浏览器的文件菜单来关闭数据库。这样可以避免数据库锁定导致的 Python 代码运行失败问题。
14.13 术语
属性： 元组的一个值。更常见的提法是列或字段。
约束： 告知数据库在表中的字段或行上执行规则。一个常见的约束是保证特定字段上无重复值（即所有值必须唯一）。
游标： 执行数据库的 SQL 命令，并从数据库中获取数据。游标类似于网络连接中的套接字或文件读取的文件句柄。
数据库浏览器： 一种无需编写程序，直接与数据库连接并进行操作的软件。
外键： 指向另一个表中行的主键的数值键。外键建立了不同表中行之间的关系。
索引： 向表中插入行时，数据库软件由于维护需要而产生的额外数据，目的是提高查询速度。
逻辑键： 外部世界用来检索特定行的键。例如，在用户信息表中用户的电子邮件地址是不错的用户数据候选逻辑键。
规范化： 数据模型设计要保证无重复的数据。每个数据项只存储在数据库的一个位置，其他地方用外键来引用。
主键： 每一行指定的数值键，用于当前表中的行与另一个表中的行之间建立引用关系。数据库的默认配置会自动为插入的行赋予主键。
关系： 数据库中包含元组与属性的一块区域。更典型的提法是“表”。
元组： 数据库中表的一个数据条目，包含一组属性。更典型的提法是“行”。
1. 实际上，SQLite 在列中存储的数据类型具备一定灵活性，但本章中严格定义数据类型，这样做让这些概念也能适用于其他数据库系统（如 MySQL）。 ↩
2. 一般来说，以“如果一切顺利”开头的话，你会发现需要使用 try/except。 ↩
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论