Python for Infomatics 第14章 数据库和SQL应用四（译）

14.6 用数据库爬取Twitter

在本节中，我们将创建一个简单的爬虫程序。它将仔细搜索Twitter账号，并建立一个账号数据库。注意：在运行这个程序时要非常小心。如果你抓取太多的数据或者长时间运行这个程序，最终可能造成Twitter账号被关闭。

任何爬虫程序都存在一个问题，即它需要能够关闭和重启很多次数，并且你不想丢失你至今为止获取的数据。你不想每次重启都重头获取所有数据，所以我们要存储已获得的数据，这样我们的程序可以备份，并且从它停止的地方重新开始。

我们将从获取某人的Twitter好友和他们的状态开始，循环搜索好友列表，将获取的每个好友添加到数据库。当我们处理完一个人的Twitter好友，记录到我们的数据库，然后获取每个好友的好友。我们一遍又一遍地这么做，挑选未访问的人，获取他们的好友列表并将我们还未记录的好友添加到我们的列表，以便将来的访问。

我们同时跟踪数据库中一个特定好友出现的次数，从而获取他的受欢迎程度。

通过存储我们的已知账号列表，和是否获取该账号，以及该账号的受欢迎度至计算机的硬盘，我们可以随时停止和重启我们程序。

这个程序有一点复杂。它是基于本书中先前使用Twitter API 练习代码。

我们的Twitter爬虫应用程序的源码如下：

import urllib.request
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 Twiter
(name TEXT, retrieved INTEGER, friends INTERGER)''')

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 = urllib.request.urlopen(url)
    data = connection.read()
    headers = connection.info().dict
    js = json.loads(data)

    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:
            cut.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的表，表中的每一行都有三个列：账号名称(name)，我们是否从这个账号获取过好友(retrieved)，以及这个账号有多少次被加为好友(friends)。

在程序的主循环中，我们提示用户输入Twitter账号名或者输入“quit”退出程序。如果用户输入一个Twitter账号，我们获取这个用户的所有好友清单及其状态，然后把数据库中还未存在的好友添加的数据库中。如果这个好友已经存在，我们在好友数量字段中加1。

如果用户按了回车键，我们在数据库中查找我们还未获取过的下一个Twitter账号，然后获取这个账号的的好友和状态。把他们添加到数据库或者更新他们的好友数量。

一旦我们获取了好友列表和状态，我们遍历返回的JSON中的所有用户项目并获取每个用户的呢称。然后我们用SELECT语句来查看我们是否已经在数据库中保存了这个昵称，如果有的话，是否获取其好友信息。

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()方法返回查询操作结果的第一行（也只有一行）。因为fetcheone()是以元组的方式返回行（即使只有一个字段），我们使用索引[0]来提取元组的第一个值，获取当前好友数量并保存的变量count中。

如果提取成功，我们使用带WHERE子句的SQL UPDATE语句给对应好友账号的好友数量加1.要注意的是在SQL中有两个占位符（？），而且execute()方法的第二个参数是二元元组，其中包含了用于替换SQL中问号的值。

try模块中的代码可能会因为未查询到匹配 WHERE name=? 的记录而失败。所以在except模块中我们使用SQL INSERT语句将好友昵称添加到表中，并指出我们还未获取这个昵称的好友，并且将他的好友数量设置为零。
当这个程序第一次运行时，我们输入一个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.sqlite3文件中到底有什么：

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表中的所有行的列信息，然后循环打印每一行。如果我们在第一执行前面的Twitter爬虫软件后运行这个程序，它的输出如下所示： 

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

我们看到每一行对应一个昵称，这些昵称我们都未进行爬取，并且在数据库中它们都有一个好友。

现在数据库反映出我们第一个Twitter账号（drchuck）的好友爬取情况。我们可以再次运行程序，并且无需输入账号，只要按一下回车，就可让它获取后面未处理的账号信息。程序的运行结果如下：

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 account），程序将执行以下代码：

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 语句来获取数据库中未处理的值为零的第一个用户名。同时用try/except代码块中的fetchone[0]的方式，抽取获取的昵称或者输出错误信息，并再次寻找。

如果我们成功获取了一个未处理的用户昵称，我们通过以下代码获取他们的数据：

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语句语句将是否获取列的值设置为1，指示这个账号我们已经完成获取工作。这样可以防止重复获取，并保持程序前行处理网络上的Twitter 好友。如果我们运行好友程序并输入两次回车获取下一个未访问的好友的好友，然后运行复制程序，它将给出以下输出：

 

(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和opncontent两位好友。同时cnxorg和kthanos已经有了两个追随者。因为现在我们获取了三个人（drchuck,opencontent和lhawthon）的好友，我们表中有55行已获取的好友。
每次我们运行这个程序并按回车，它将挑选下一个未处理的账号（例如 下个账号将会是steve_coppin），获取并标识steve_coppin每个好友，并将他们添加到数据库中，如果他们已经存在，则更新他们的好友数量。
因为这个程序的数据保存在硬盘上的数据库中，所以爬取活动可以被任意中止并重新启动而不会丢失数据。

注：文章原文为Dr. Charles Severance 的 《Python for Informatics》。此节中的代码未改写，并且用2.7版本调测时报认证错误，估计是oauth安全认证的问题。