Использование баз данных и языка структурированных запросов (SQL)

26.5. Обзор Языка структурированных запросов (SQL)

До сих пор мы использовали Язык структурированных запросов (Structured Query Language) в примерах программ на Питоне и изучили многие базовые 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.

Обратите внимание, что, в отличие от Питона, в SQL-условии WHERE мы используем одинарный, а не двойной знак равенства при проверке на равенство. В условии WHERE можно также указывать другие логические операции, используя знаки сравнения <, >, <=, >=, !=, а также ключевые слова AND, OR и круглые скобки для построения сложных логических выражений. Можно также отсортировать возвращенные строки по одному из полей:

SELECT title,plays FROM Tracks ORDER BY title

Чтобы удалить строки, нужно указать условие WHERE в SQL-операторе DELETE. Условие WHERE определяет, какие именно строки необходимо удалить:

DELETE FROM Tracks WHERE title = 'My Way'

Можно обновить столбец или несколько столбцов внутри одной или более строки, используя оператор UPDATE языка SQL:

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

В команде UPDATE сначала указывается таблица, затем после ключевого слова SET – список полей и их новых значений, и далее после ключевого слова WHERE следует необязательное условие, задающее выбор строк, которые должны быть обновлены. Один оператор UPDATE меняет сразу все строки, которые отвечают критерию выбора, указанному в WHERE, либо, если WHERE не используется, то обновляются вообще все строки в таблице.

Эти четыре основные команды (INSERT, SELECT, UPDATE и DELETE) позволяют выполнять четыре главные операции, необходимые для создания данных и работы с ними.

26.6. Создание пауков Твиттера с использованием базы данных

В этом разделе мы создадим простую программу-паука, которая пройдет по всем учетным записям Твиттера и создаст по ним базу данных. Замечание: будьте осторожны, запуская эту программу! Не следует извлекать чересчур много данных или запускать программу на слишком долгое время, что может повлечь закрытие вашего аккаунта.

Одна из проблем, с которой мы сталкиваемся, когда создаем программу-паука – нужно иметь возможность в любой момент остановить ее и вновь запустить, это может повторяться многократно и при этом не должны теряться полученные ранее данные.

Не хотелось бы каждый раз вновь запускать процесс получения данных с самого начала, поэтому мы сохраняем данные сразу по их получении, таким образом, программа при перезапуске начинает работать с того места, где она была прервана.

Мы начинаем с какого-то пользователя Твиттера, извлекая список его друзей и их статусов, перебираем элементы этого списка в цикле и добавляем каждого из друзей в базу данных, чтобы в будущем извлечь и списки их друзей. После того, как мы обработали друзей одного человека, мы проверяем нашу базу данных и извлекаем из Твиттера друзей какого-нибудь человека, ранее занесенного в базу.

Это повторяется снова и снова, каждый раз мы находим в базе человека, которого еще "не посетили" в Твиттере, извлекаем список его друзей и добавляем в базу тех из них, кто ранее еще не был в нее занесен, чтобы в будущем посетить их тоже. По ходу дела мы отслеживаем также, сколько раз конкретный человек встретился в списках друзей, чтобы определить степень его "популярности".

Сохраняя в базе данных список известных учетных записей, а также для каждой записи информацию о том, был ли для нее извлечен список друзей или еще нет, плюс данные о том, насколько популярна эта учетная запись, мы получаем возможность остановить нашу программу и вновь запустить ее столько раз, сколько нам захочется.

Эта программа довольно сложная. Она основана на упражнении, приведенном ранее в этой книге, в котором мы используем API Твиттера. Вот исходный код нашего Твиттер-паука:

import sqlite3
import urllib
import xml.etree.ElementTree as ET
TWITTER_URL = 'http://api.twitter.com/l/statuses/friends/ACCT.xml'
conn = sqlite3.connect('twdata.db')
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 = TWITTER_URL.replace('ACCT', acct)
print 'Retrieving', url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute('UPDATE Twitter SET retrieved=1 WHERE name = ?', (acct, ) )
countnew = 0
countold = 0
for user in tree.findall('user'):
friend = user.find('screen_name').text
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()
  

Наша база данных хранится в файле twdata.db, там содержится одна таблица с именем Twitter, содержащая три столбца: текстовый столбец "name" для имени аккаунта; целочисленный столбец "retrieved", содержащий единицу для тех аккаунтов, список друзей который уже был извлечен, либо ноль в противном случае; и целочисленный столбец "friends", содержащий количество записей, которые "подружились" с данным аккаунтом.

В основном цикле нашей программы запрашивается название Твиттер-аккаунта или слово "quit" для завершения программы. Если вводится название аккаунта Твиттера, мы извлекаем список его друзей и их статусы и добавляем каждого друга в базу данных, если он еще туда не внесен. Если он уже содержится в базе, то мы увеличиваем число его друзей на единицу.

Если пользователь просто нажал клавишу "Enter", то программа ищет в базе данных следующий аккаунт Твиттера, для которого список друзей еще не был получен, извлекает список его друзей, добавляет их в базу либо обновляет строку в базе, увеличивая счетчик друзей.

Как только мы получаем список друзей и их статусов, мы перебираем в цикле все элементы с тегом "user" полученного XML-документа и для каждого из них извлекаем текстовое значение подчиненного элемента "screen_name". Затем мы используем оператор SELECT для проверки, была ли запись с именем, содержащимся в "screen_name", ранее уже добавлена в базу, и для получения числа ее друзей (столбец "friends"), если запись уже была добавлена.

countnew = 0
countold = 0
for user in tree.findall('user'):
friend = user.find('screen_name').text
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 к переменной cur, но, поскольку мы ограничили количество извлеченных строк единицей (LIMIT 1), можно использовать метод fetchone() ("выбрать один") для извлечения единственной строки, полученной в результате операции SELECT.

Поскольку метод fetchone() возвращает строку в виде кортежа (даже в том случае, когда строка содержит только одно поле), мы берем первое значение из кортежа, используя индексатор [0], и помещаем текущее значение счетчика друзей в переменную count.

Если выбор был успешным, то мы выполняем SQL-команду UPDATE с условием WHERE, чтобы увеличить на единицу значение в столбце "friends" той записи, которая соответствует аккаунту друга. Отметим, что в SQL-команде используются два подстановочных символа – вопросительные знаки, которые заменяются на реальные значения, передаваемые в виде двухэлементного кортежа в качестве второго параметра метода execute().

Если исполнение кода внутри блока try приводит к неудаче, то это происходит скорее всего потому, что в базе нет записей, подходящих под условие "WHERE name = ?" оператора SELECT. Поэтому в блоке except, обрабатывающем ошибочную ситуацию, мы используем SQL-команду INSERT, добавляя имя друга (полученное как screen_name) в таблицу с указанием, что список его друзей еще не извлечен (поле "retrieved" нулевое) и число друзей (поле "friends") равно нулю.

Запустив программу в первый раз и введя название учетной записи Твиттера, получим:

Enter a Twitter account, or quit: drchuck
Retrieving http://api.twitter.com/l/statuses/friends/drchuck.xml
New accounts= 100 revisited= 0
Enter a Twitter account, or quit: quit
  

Поскольку мы запускаем программу в первый раз, база данных отсутствует, поэтому мы создаем ее в файле twdata.db и добавляем в нее таблицу с именем Twitter. Затем мы извлекаем нескольких друзей и помещаем всех их в базу данных, поскольку она изначально пуста.

Здесь мы хотели бы написать простую программу, распечатывающую текущее состояние базы, чтобы посмотреть содержимое нашего файла twdata.db:

import sqlite3
conn = sqlite3.connect('twdata.db')
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)
...
100 rows.
  

Для каждого имени аккаунта (полученного как XML-элемент screen_name) печатается одна строка, в которой указано, что мы еще не получили список друзей для данного имени (второй элемент 0) и что у имени есть 1 друг (третий элемент).

В данный момент содержание нашей базы отражает извлечение списка друзей для нашего первого аккаунта (drchuck). Мы можем снова запустить нашу программу, указав ей извлечь друзей первого "необработанного" аккаунта в базе простым нажатием клавиши "Enter" вместо ввода имени Твиттер-аккаунта:

Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/l/statuses/friends/opencontent.xml
New accounts= 98 revisited= 2
Enter a Twitter account, or quit:
Retrieving http://api.twitter.com/l/statuses/friends/lhawthorn.xml
New accounts= 97 revisited= 3
Enter a Twitter account, or quit: quit
  

Поскольку мы нажали "Enter" (т.е. не ввели название Твиттер-аккаунта), выполняется следующий фрагмент кода:

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) пользователя, у которого признак того, что мы извлекли его друзей (поле "retrieved"), все еще равен нулю. Также мы используем блок try/except и фрагмент fetchone()[0] внутри try для извлечения значения элемента screen_name из полученных данных; при ошибке печатается сообщение о том, что в базе уже нет необработанных записей. Если мы успешно получили имя еще необработанного аккаунта, мы извлекаем из Твиттера его данные следующим образом:

url = TWITTER_URL.replace('ACCT', acct)
print 'Retrieving', url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute('UPDATE Twitter SET retrieved=1 WHERE name = ?', (acct, ) )
  

Получив успешно данные, мы выполняем SQL-команду UPDATE, чтобы заменить для обработанной записи значение поля "retrieved" на единицу – это означает, что мы уже извлекли список друзей данного аккаунта. Таким способом мы предотвращаем повторное извлечение из Твиттера уже обработанных данных, что позволяет каждый раз продвигаться вперед в Твиттере по сети друзей.

Если мы запустим программу и нажмем Enter дважды, чтобы извлечь друзей следующей необработанной записи, а затем распечатаем содержимое базы, то получим следующий вывод:

(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)
...
295 rows.
  

Как мы видим, содержимое базы правильно отражает тот факт, что мы обработали аккаунты opencontent и lhawthorn. Отметим также, что аккаунты cnxorg и kthanos имеют двух друзей. На данный момент мы получили из сети друзей трех человек (drchuck, opencontent и lhawthorn), при этом наша таблица содержит 295 строчек (293 необработанных).

Каждый раз, когда мы запускаем программу, она находит следующий необработанный аккаунт (например, в нашем случае это steve_coppin), извлекает по сети его друзей, отмечает его как обработанный и для каждого друга аккаунта steve_coppin либо добавляет его в базу, либо увеличивает счетчик его друзей, если аккаунт друга уже содержится в базе.

Поскольку данные программы сохраняются в базе данных на диске, работа паука может быть приостановлена и возобновлена многократно без потери данных. Замечание. Прежде чем завершить эту тему, еще раз предупреждаем, что с программой-пауком Твиттера нужно быть осторожным. Не следует извлекать из сети слишком много данных или запускать программу на большое время – это может привести к потере доступа к Твиттеру.