Презентация для лекции 26.ppt.
База данных – это файл, используемый специально для хранения данных. Большая часть баз данных организована в виде словаря в том смысле, что база данных отображает ключи на их значения. Основное отличие в том, что база данных хранится на диске (или другом постоянном хранителе информации) и поэтому не исчезает после окончания программы. Также, поскольку база данных хранится на постоянном носителе, она способна вместить намного больший объем данных, чем словарь, размер которого ограничивается объемом оперативной памяти компьютера.
Как и в случае словаря, программы работы с базами данных разработаны так, чтобы добавление данных и доступ к ним происходили максимально быстро даже при их огромном объеме. Это достигается с помощью создания индексов, позволяющих компьютеру быстро перейти к конкретному элементу.
Существует множество различных систем для создания баз данных разных типов, включающее Oracle, MySQL, Microsoft SQL Server, PostgreSQL и SQLite. В этой книге мы рассмотрим SQLite, поскольку это очень распространенная база данных, поддержка которой встроена в Питон.
База SQLite специально сконструирована так, чтобы ее можно было включать в другие приложения, обеспечивая в их рамках поддержку баз данных. Например, браузер Firefox использует внутри себя SQLite, как и многие другие программы.
SQLite хорошо подходит для решения разных задач, связанных с манипуляциями данными, например, при создании пауков Твиттера, которых мы опишем в этой главе.
При первом взгляде на базу данных она выглядит как таблица с множеством листов. Первичная структура данных базы использует таблицы, которые состоят из строк и столбцов.
В техническом описании реляционных баз данных вместо не очень строгих понятий таблицы, строки и столбца используются более четко определенные формальные термины: отношение (relation), кортеж (tuple) и атрибут (attribute) соответственно. Мы все же будем пользоваться неформальными терминами в этой главе.
Хотя в данной главе в основном рассматривается работа с базами данных SQLite из программ Питона, многие операции удобнее выполнять с помощью графической программы, которая называется "Браузер базы данных SQLite" (SQLite Database Browser) – это свободная программа, доступная по адресу .
Используя браузер, вы легко можете создавать таблицы, добавлять и редактировать данные и выполнять простые SQL-запросы к базе данных:
Браузер базы данных в некотором смысле аналогичен текстовому редактору, работающему с текстовыми файлами. Когда нужно выполнить одну или несколько операций с текстовым файлом, можно просто открыть его в текстовом редакторе и внести нужные изменения. Но когда подобных изменений очень много, часто проще написать небольшую программу на Питоне. То же самое верно и при работе с базами данных: простые операции можно делать в браузере, но более сложные гораздо удобнее выполнять с помощью программ на Питоне.
Базы данных требуют более четкой структуры, чем списки или словари Питона
Можно посмотреть список различных типов данных, поддерживаемых SQLite, по адресу .
Определение структуры данных на начальном шаге может показаться неудобным, но зато мы получаем в результате быстрый доступ к данным даже в том случае, когда их объем очень большой.
Код для создания файла базы данных и ее таблицы с именем Tracks, которая содержит два столбца, выглядит следующим образом:
import sqlite3
conn = sqlite3.connect('music.db')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks ')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()
Операция connect устанавливает "соединение" с базой данных, которая хранится в файле music.db в текущем каталоге. Если файл не существует, то он будет создан. Слово "соединение" используется потому, что достаточно часто база хранится на сетевом сервере, отличном от того компьютера, на котором работает приложение. Но в нашем простом случае база данных представляет собой локальный файл в том же каталоге, в котором мы выполняем программу Питона. Переменная cur играет роль файлового дескриптора, мы используем ее для операций с содержимым базы данных. Вызов метода cursor() концептуально близок к вызову open(), когда мы работаем с текстовыми файлами.
Как только мы получили дескриптор cur с помощью метода cursor, можно выполнять команды над содержимым базы данных при помощи метода execute(). Эти команды представляют собой специальный язык, который был стандартизован благодаря усилиям многих разработчиков различных систем баз данных – все системы теперь используют единый язык. Он называется "Язык структирированных запросов" (Structured Query Language) или сокращенно SQL.
В нашем примере мы исполняем две SQL-команды в базе данных. По общему соглашению, принято записывать ключевые слова языка SQL прописными буквами, а части команды, которые мы добавляем к ключевым словам (например, имена таблицы и ее столбцов), – строчными буквами. Первая SQL-команда удаляет таблицу с именем Tracks из базы данных, если такая существует. Этот фрагмент кода позволяет многократно выполнять одну и ту же программу, которая каждый раз заново создает таблицу Tracks, избегая ошибок. Отметим, что команда DROP TABLE удаляет из базы данных таблицу со всем ее содержимым без возможности восстановления (отмена операции – "Undo" – не предусмотрена).
cur.execute('DROP TABLE IF EXISTS Tracks ')
Вторая команда создает таблицу с именем Tracks, которая содержит два столбца: в стобец с именем title помещается текстовая информация, в столбец с именем plays – целые числа.
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
Теперь, когда мы создали таблицу с именем Tracks, мы можем поместить данные в эту таблицу с помощью операции INSERT языка SQL. Как и в предыдущем случае, мы начинаем с установки соединения с базой данных и получения курсора (аналога файлового дескриптора). После этого, используя курсор, мы можем выполнять SQL-команды.
Команда SQL INSERT указывает, какую именно таблицу мы используем, затем задает новую строку таблицы, перечисляя поля, которые мы хотим в нее включить (title, plays), и после ключевого слова VALUES – значения, которые мы хотим поместить в новую строку таблицы. Мы можем задать значения с помощью вопросительных знаков (?, ?), чтобы указать, что реальные значения передаются в виде кортежа ( 'My Way', 15 ) в качестве второго параметра метода execute():
import sqlite3
conn = sqlite3.connect('music.db')
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()
Сначала c помощью команды INSERT мы вставляем две строки в нашу таблицу, затем мы используем метод commit() для форсированной записи данных в файл.
Затем мы используем команду SELECT, чтобы получить из таблицы две строки, которые только что были добавлены в нее. В команде SELECT мы указываем, какие столбцы нам нужны (title, plays), а также имя таблицы, из которой мы извлекаем информацию. После выполнения операции SELECT курсор (т.е. переменная cur) позволяет нам перебирать выбранные данные в цикле for. Для эффективности курсор в действительности не читает все данные из базы сразу при выполнении операции SELECT, вместо этого каждая очередная порция данных считывается по отдельности, когда мы перебираем выбранные строки в цикле for.
На выходе программы получаем:
Tracks: (u'Thunderstruck', 20) (u'My Way', 15)
В цикле for найдены две строки, каждая из которых представляет собой кортеж в смысле Питона, его первым элементом является заголовок (музыкального произведения), вторым – число его исполнений. Пусть вас не смущает префикс "u", с которого начинаются заголовки, – это просто указание, что строки представлены в кодировке Unicode, которая дает возможность использовать любые символы, а не только латинские буквы. В самом конце программы мы выполняем SQL-команду DELETE, удаляя только что созданные строки, что позволяет нам исполнять программу снова и снова.
Команда DELETE демонстрирует использование условия WHERE, задающего критерий выбора строк, к которым применяется команда. В данном примере получилось так, что критерий применим ко всем строкам, в результате таблица становится пустой. Благодаря этому программу можно запускать много раз.
После выполнения команды DELETE мы также вызываем метод commit() для форсированного удаления данных из файла базы данных.
До сих пор мы использовали Язык структурированных запросов (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) позволяют выполнять четыре главные операции, необходимые для создания данных и работы с ними.
В этом разделе мы создадим простую программу-паука, которая пройдет по всем учетным записям Твиттера и создаст по ним базу данных. Замечание: будьте осторожны, запуская эту программу! Не следует извлекать чересчур много данных или запускать программу на слишком долгое время, что может повлечь закрытие вашего аккаунта.
Одна из проблем, с которой мы сталкиваемся, когда создаем программу-паука – нужно иметь возможность в любой момент остановить ее и вновь запустить, это может повторяться многократно и при этом не должны теряться полученные ранее данные.
Не хотелось бы каждый раз вновь запускать процесс получения данных с самого начала, поэтому мы сохраняем данные сразу по их получении, таким образом, программа при перезапуске начинает работать с того места, где она была прервана.
Мы начинаем с какого-то пользователя Твиттера, извлекая список его друзей и их статусов, перебираем элементы этого списка в цикле и добавляем каждого из друзей в базу данных, чтобы в будущем извлечь и списки их друзей. После того, как мы обработали друзей одного человека, мы проверяем нашу базу данных и извлекаем из Твиттера друзей какого-нибудь человека, ранее занесенного в базу.
Это повторяется снова и снова, каждый раз мы находим в базе человека, которого еще "не посетили" в Твиттере, извлекаем список его друзей и добавляем в базу тех из них, кто ранее еще не был в нее занесен, чтобы в будущем посетить их тоже. По ходу дела мы отслеживаем также, сколько раз конкретный человек встретился в списках друзей, чтобы определить степень его "популярности".
Сохраняя в базе данных список известных учетных записей, а также для каждой записи информацию о том, был ли для нее извлечен список друзей или еще нет, плюс данные о том, насколько популярна эта учетная запись, мы получаем возможность остановить нашу программу и вновь запустить ее столько раз, сколько нам захочется.
Эта программа довольно сложная. Она основана на упражнении, приведенном ранее в этой книге, в котором мы используем 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 либо добавляет его в базу, либо увеличивает счетчик его друзей, если аккаунт друга уже содержится в базе.
Поскольку данные программы сохраняются в базе данных на диске, работа паука может быть приостановлена и возобновлена многократно без потери данных. Замечание. Прежде чем завершить эту тему, еще раз предупреждаем, что с программой-пауком Твиттера нужно быть осторожным. Не следует извлекать из сети слишком много данных или запускать программу на большое время – это может привести к потере доступа к Твиттеру.
Настоящая сила реляционных баз данных проявляется, когда мы создаем несколько таблиц и устанавливаем связи между ними. Принятие решений о том, каким именно образом разделить данные приложения между несколькими таблицами и как установить соотношения между двумя таблицами, называется моделированием данных (data modeling). Документ с дизайном вашего приложения, который показывает таблицы и их связи, называется моделью данных (data model).
Моделирование данных – непростое искусство, в этом разделе мы познакомимся лишь с самыми основами моделирования реляционных данных. Более подробную информацию по этой теме можно найти по адресу .
Пусть в нашей программе-пауке Твиттера вместо простого подсчета друзей каждого человека мы хотим получить список всех входящих связей, чтобы можно было получить список всех тех, кто дружит с конкретным аккаунтом.
Поскольку каждый пользователь Твиттера может иметь множество аккаунтов, которые с ним дружат, недостаточно просто добавить единственный столбец в нашу таблицу Twitter. Поэтому мы создаем новую таблицу, в которой будут храниться пары друзей. Ниже указан простой способ создания подобной таблицы Pals (англ. "приятели"):
CREATE TABLE Pals (from_friend TEXT, to_friend TEXT)
Каждый раз, когда мы встречаем человека, который является другом пользователя drchuck, мы добавляем в таблицу Pals строку вида:
INSERT INTO Pals (from_friend,to_friend) VALUES ('drchuck', 'lhawthorn')
После обработки 100 друзей аккаунта drchuck мы добавим в таблицу 100 записей, в которых "drchuck" будет первым параметром, что приведет нас к многократному повторению одной и той же строки в базе данных.
Такое повторение строковых данных нарушает хорошую практику нормализации баз данных (database normalization), которая требует, чтобы одна и та же строка не помещалась в базу дважды. Если нам нужно использовать данные более одного раза, то мы создаем целочисленный ключ к реальным данным и ссылаемся на них с помощью этого ключа.
Говоря практически, строка занимает намного больше пространства в памяти или на диске, чем целое число, и требует больше процессорного времени для сравнения и сортировки. Когда мы имеем всего лишь несколько сотен записей, объем памяти и время обработки несущественны. Но если в нашей базе данных миллион человек и, возможно, 100 миллионов ссылок на друзей, скорость сканирования данных становится очень важной.
Мы будем хранить наши Твиттер-аккаунты в таблице с именем People вместо таблицы Twitter из предыдущих примеров. Таблица People имеет дополнительный столбец для хранения целочисленного ключа, соответствующего строке пользователя Твиттера. SQLite дает возможность автоматически добавлять ключевое значение для любой строки, добавляемой в базу данных, используя специальный тип данных в столбце (INTEGER PRIMARY KEY).
Таблица People с дополнительным столбцом, хранящим идентификаторы, создается следующим образом:
CREATE TABLE People (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)
Отметим, что мы больше не поддерживаем счетчик числа друзей для каждой строки в таблице People. Когда мы задали тип столбца "id" как INTEGER PRIMARY KEY, мы указали, что 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, мы моделируем "отношение", при котором один человек следует за некоторым другим, и представляем это отношение парами чисел. Каждая пара а) указывает, что люди связаны между собой и (б) устанавливает направление этой связи.
Теперь мы перепишем нашу программу-паука Твиттера, используя две таблицы, первичные ключи и ссылки между ключами, как было описано выше. Вот код новой версии программы:
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 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 = TWITTER_URL.replace('ACCT', acct)
print 'Retrieving', url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute('UPDATE People 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 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()
Программа становится немного более сложной, но зато она иллюстрирует шаблоны кода, который используется при установлении связей между таблицами с помощью целочисленных ключей. Наиболее важные моменты следующие.
Ниже мы рассмотрим каждый из этих пунктов.
Поскольку мы уже разработали дизайн наших таблиц, мы можем сообщить исполняющей системе базы данных, что нужно установить ряд правил при работе с таблицами. Эти правила предотвращают возможные ошибки и добавление в базу некорректных данных. Вот код для создания таблиц:
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))''')
Используя ключевое слово UNIQUE, мы указываем, что значения в столбце "name" таблицы People должны быть уникальными (не могут повторяться). Точно так же уникальными должны быть и пары чисел в строках таблицы Follows. Это предотвращает такие ошибки, как добавление одного и того же соотношения дважды. Мы можем воспользоваться преимуществами этих ограничений в следующем коде:
cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)''', ( friend, ) )
Мы добавили условие OR IGNORE ("или игнорировать") в оператор INSERT, чтобы указать, что, если выполнение команды INSERT приведет к нарушению правила "поле name должно быть уникальным", исполняющая система должна проигнорировать эту команду.
Мы используем ограничения как страховочную сетку, чтобы случайно не сделать что-нибудь неправильно. Аналогично, следующий код предохраняет нас от двукратного добавления одного и того же соотношения в таблицу Follows:
cur.execute('''INSERT OR IGNORE INTO Follows
(from_id, to_id) VALUES (?, ?)''', (id, friend_id) )
Снова мы указываем исполняющей системе базы данных, что она должна игнорировать наши попытки выполнения команды INSERT, если это приводит к нарушению условия уникальности для строк таблицы Follows.
Когда мы запрашиваем название аккаунта Твиттера, то, если аккаунт существует, нужно найти значение его id. Если такого аккаунта в таблице People еще нет, нужно его добавить и получить значение id для добавленной строки.
Это очень часто встречающийся фрагмент кода, например, он дважды использован в приведенной выше программе. Код демонстрирует, как находить id для аккаунта друга, когда мы извлекаем текстовое значение узла screen_name, подчиненного узлу user в XML-документе.
Поскольку со временем вероятность того, что аккаунт уже занесен в базу данных, возрастает, мы сначала проверяем, содержится ли соответствующая запись в таблице People, используя оператор SELECT. Если код внутри блока try выполняется нормальноtry/except.fetchone(), затем извлекаем первый (и единственный) элемент возвращенного кортежа и записываем его в поле friend_id. Если операция SELECT заканчивается неудачно, то код fetchone()[0] приводит к отказу и управление передается в секцию except.
friend = user.find('screen_name').text
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, чтобы посмотреть, сколько строк обновилось. Поскольку мы делали попытку добавить единственную строку, то, если число обновленных строк отлично от единицы, это свидетельствует об ошибке.
Если команда INSERT завершается успешно, мы можем использовать переменную cur.lastrowid, чтобы получить значение id, сгенерированное базой данных для созданной строки.
Когда нам уже известны значения ключей пользователя Твиттера и его друга, указанного в XML, нетрудно добавить пару чисел в таблицу Follows с помощью следующего кода:
cur.execute('INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)',
(id, friend_id) )
Заметим, что мы поручили самой базе данных следить за тем, чтобы задающая отношение дружбы пара не была добавлена в таблицу дважды – для этого при создании таблицы мы задали ограничение на единственность, а при добавлении использовали вариант OR IGNORE ("или игнорировать") команды INSERT. Вот пример выполнения программы:
Enter a Twitter account, or quit:
No unretrieved Twitter accounts found
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:
Retrieving http://api.twitter.com/l/statuses/friends/opencontent.xml
New accounts= 97 revisited= 3
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
Мы начали с аккаунта 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)
295 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
300 rows.
Можно видеть значения полей id, name, и visited в таблице People, а также пары чисел, задающие отношение дружбы, в таблице Follows. Из таблицы People видно, что мы посетили первых трех человек и что их данные уже получены из Твиттера. Данные в таблице Follows показывают, что пользователи 2-6 являются друзьями пользователя drchuck (его номер 1). Произошло это потому, что первыми были получены и помещены в базу друзья пользователя drchuck.
Если бы мы напечатали больше строк таблицы Follows, то увидели бы также и друзей пользователей с номерами 2 и 3.
Теперь, когда мы начали построение модели данных, помещая данные в несколько таблиц и связывая между собой строки этих таблиц с помощью ключей, нужно ознакомиться с терминологией, относящейся к ключам.
В общем случае имеется три вида ключей, используемых в моделях баз данных.
Теперь, когда мы, следуя правилу о нормализации базы данных, разделили данные на две таблицы, связанные с помощью первичных и внешних ключей, нам нужен способ выбора данных в команде SELECT, который мог бы собрать вместе данные из разных таблиц.
SQL использует условие JOIN для соединения таблиц. В нем указываются поля, которые служат для соединения строк из разных таблиц.
Приведем пример команды SELECT с условием JOIN:
SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2
Условие JOIN указывает, что мы выбираем поля сразу из двух таблиц: Follows и People. Условие ON задает, как именно соединяются две таблицы. Берем строки из таблицы Follows и добавляем в их концы строки из таблицы People, у которых значение поля "id" совпадает со значением поля "from_id" строки из Follows.
Результатом команды JOIN является создание сверхдлинных "мета-строк", которые содержит как поля из таблицы People, так и соответствующие поля из таблицы Follows. Когда есть больше одного совпадения значений полей "id" таблицы People и "from_id" таблицы Follows, команда JOIN создает несколько мета-строк, соответствующих каждой совпадающей паре ключей, дублируя данные при необходимости.
Следующий пример распечатывает данные, которые мы имеем в базе после того, как приведенный выше вариант программы Твиттер-паука, основанный на двух таблицах, был запущен несколько раз.
import sqlite3
conn = sqlite3.connect('twdata.db')
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()
Программа сначала распечатывает содержимое таблиц 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) 295 rows. Follows: (1, 2) (1, 3) (1, 4) (1, 5) (1, 6) 300 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) 100 rows.
Вначале идут данные таблиц People и Follows; последние строки вывода представляют собой результат выполнения команды SELECT с условием JOIN. В ней мы находим аккаунты, которые являются друзьями аккаунта "opencontent" (т.е. People.id=2).
В каждой "мета-строке", возвращенной последней командой SELECT, первые два поля получены из таблицы Follows, за ними следуют поля с третьего по пятое из таблицы People. Можно также заметить, что в каждой объединенной "мета-строке" второе поле (Follows.to_id) соответствует третьему полю (People.id).
В этой главе мы познакомились с основами использования баз данных в программах на Питоне. Это сложнее, чем использовать для хранения данных словари Питона или обычные файлы, поэтому работать с базами данных следует только тогда, когда это действительно необходимо. Базы данных могут оказаться весьма полезными в следующих ситуациях:
Во многих приложениях достаточно простой базы данных с единственной таблицей, но все же большинство реальных задач требуют использования нескольких таблиц и связей между строками разных таблиц.
Когда возникает необходимость вводить связи между таблицами, важно хорошо продумать их дизайн и следовать правилу нормализации баз данных, чтобы обеспечить максимально эффективное использование их возможностей.
Поскольку чаще всего необходимость в базах данных возникает из-за огромного объема данных, с которыми приходится иметь дело, важно построить эффективную модель данных, чтобы программа работала максимально быстро.
Когда вы разрабатываете программу на Питоне, использующую базу данных SQLite, распространенным способом отладки является просмотр содержимого базы с помощью браузера базы данных SQLite ("SQLite Database Browser"). После запуска вашей программы браузер дает возможность быстро проверить, правильно ли работает ваша программа.
Нужно учитывать, что система SQLite предотвращает одновременное изменение одних и тех же данных разными программами. Например, если вы открыли базу данных в браузере, сделали какое-то изменение и всё ещё не нажали клавишу "save" (сохранить), браузер "блокирует" (lock) доступ к файлу базы для любых других программ.
В частности, ваша программа на Питоне не сможет работать с файлом базы, когда он заблокирован. Решение состоит в том, чтобы либо закрыть браузер, либо, используя его меню "File", закрыть базу данных перед тем, как начать опять работать с ней из программы Питона, – это позволит избежать отказов программы из-за блокировки файла базы.
Атрибут (attribute): одно из значений внутри кортежа. Чаще используются термины "столбец" или "поле".
Ограничение (constraint): указание базе данных, что к полю или к строке таблицы применяется некоторое правило. Чаще всего используется ограничение, требующее, чтобы не было дублирования значений в конкретном поле (т.е. все значения должны быть уникальными).
Курсор (cursor): позволяет выполнять SQL-команды над содержимым базы данных и извлекать данные из базы. В применении к базе данных курсор является аналогом файлового дескриптора в случае обычного файла или сокета в случае сети.
Браузер базы данных (database browser): программа, дающая возможность прямого подсоединения к базе данных, просмотра и изменения ее содержимого без необходимости написания программного кода.
Внешний ключ (foreign key): целочисленный ключ, который ссылается на первичный ключ некоторой строки в другой таблице. Внешние ключи устанавливают связи между строками разных таблиц.
Индекс (index): дополнительные данные, которые программное обеспечение баз данных поддерживает при добавлении строк в таблицу; они используются для ускорения поиска.
Логический ключ (logical key): ключ, используемый для поиска конкретной строки из "внешнего мира". Например, в таблице, содержащей учетные записи пользователей, адрес электронной почты человека является хорошим кандидатом на роль логического ключа.
Нормализация (normalization): создание модели данных таким образом, чтобы исключить дублирование данных. Мы храним каждый элемент данных только в одном месте, используя во всех других местах ссылки на него с помощью внешнего ключа.
Первичный ключ (primary key): целочисленный ключ, ассоциированный с каждой строкой таблицы, который используется для ссылки на данную строку из других таблиц. Часто база данных конфигурируется таким образом, чтобы автоматически генерировать первичные ключи при добавлении строк.
Отношение (relation): область внутри базы данных, содержащая кортежи и атрибуты. Чаще используется термин "таблица".
Кортеж (tuple): одна запись в таблице базы данных, представляющая собой набор атрибутов. Чаще используется термин "строка".
Упражнение 26.1.
Получите по сети файл и используйте браузер базы данных SQLite, чтобы узнать, сколько таблиц содержится в базе; определите также для каждой таблицы список ее полей и их типов. Тип одного из полей не был рассмотрен в этой главе. Используйте online-документацию SQLite, чтобы описать, для чего нужен подобный тип данных.
Презентация для лекции 26.ppt.
База данных – это файл, используемый специально для хранения данных. Большая часть баз данных организована в виде словаря в том смысле, что база данных отображает ключи на их значения. Основное отличие в том, что база данных хранится на диске (или другом постоянном хранителе информации) и поэтому не исчезает после окончания программы. Также, поскольку база данных хранится на постоянном носителе, она способна вместить намного больший объем данных, чем словарь, размер которого ограничивается объемом оперативной памяти компьютера.
Как и в случае словаря, программы работы с базами данных разработаны так, чтобы добавление данных и доступ к ним происходили максимально быстро даже при их огромном объеме. Это достигается с помощью создания индексов, позволяющих компьютеру быстро перейти к конкретному элементу.
Существует множество различных систем для создания баз данных разных типов, включающее Oracle, MySQL, Microsoft SQL Server, PostgreSQL и SQLite. В этой книге мы рассмотрим SQLite, поскольку это очень распространенная база данных, поддержка которой встроена в Питон.
База SQLite специально сконструирована так, чтобы ее можно было включать в другие приложения, обеспечивая в их рамках поддержку баз данных. Например, браузер Firefox использует внутри себя SQLite, как и многие другие программы.
SQLite хорошо подходит для решения разных задач, связанных с манипуляциями данными, например, при создании пауков Твиттера, которых мы опишем в этой главе.
При первом взгляде на базу данных она выглядит как таблица с множеством листов. Первичная структура данных базы использует таблицы, которые состоят из строк и столбцов.
В техническом описании реляционных баз данных вместо не очень строгих понятий таблицы, строки и столбца используются более четко определенные формальные термины: отношение (relation), кортеж (tuple) и атрибут (attribute) соответственно. Мы все же будем пользоваться неформальными терминами в этой главе.
Хотя в данной главе в основном рассматривается работа с базами данных SQLite из программ Питона, многие операции удобнее выполнять с помощью графической программы, которая называется "Браузер базы данных SQLite" (SQLite Database Browser) – это свободная программа, доступная по адресу .
Используя браузер, вы легко можете создавать таблицы, добавлять и редактировать данные и выполнять простые SQL-запросы к базе данных:
Браузер базы данных в некотором смысле аналогичен текстовому редактору, работающему с текстовыми файлами. Когда нужно выполнить одну или несколько операций с текстовым файлом, можно просто открыть его в текстовом редакторе и внести нужные изменения. Но когда подобных изменений очень много, часто проще написать небольшую программу на Питоне. То же самое верно и при работе с базами данных: простые операции можно делать в браузере, но более сложные гораздо удобнее выполнять с помощью программ на Питоне.
Базы данных требуют более четкой структуры, чем списки или словари Питона
Можно посмотреть список различных типов данных, поддерживаемых SQLite, по адресу .
Определение структуры данных на начальном шаге может показаться неудобным, но зато мы получаем в результате быстрый доступ к данным даже в том случае, когда их объем очень большой.
Код для создания файла базы данных и ее таблицы с именем Tracks, которая содержит два столбца, выглядит следующим образом:
import sqlite3
conn = sqlite3.connect('music.db')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Tracks ')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
conn.close()
Операция connect устанавливает "соединение" с базой данных, которая хранится в файле music.db в текущем каталоге. Если файл не существует, то он будет создан. Слово "соединение" используется потому, что достаточно часто база хранится на сетевом сервере, отличном от того компьютера, на котором работает приложение. Но в нашем простом случае база данных представляет собой локальный файл в том же каталоге, в котором мы выполняем программу Питона. Переменная cur играет роль файлового дескриптора, мы используем ее для операций с содержимым базы данных. Вызов метода cursor() концептуально близок к вызову open(), когда мы работаем с текстовыми файлами.
Как только мы получили дескриптор cur с помощью метода cursor, можно выполнять команды над содержимым базы данных при помощи метода execute(). Эти команды представляют собой специальный язык, который был стандартизован благодаря усилиям многих разработчиков различных систем баз данных – все системы теперь используют единый язык. Он называется "Язык структирированных запросов" (Structured Query Language) или сокращенно SQL.
В нашем примере мы исполняем две SQL-команды в базе данных. По общему соглашению, принято записывать ключевые слова языка SQL прописными буквами, а части команды, которые мы добавляем к ключевым словам (например, имена таблицы и ее столбцов), – строчными буквами. Первая SQL-команда удаляет таблицу с именем Tracks из базы данных, если такая существует. Этот фрагмент кода позволяет многократно выполнять одну и ту же программу, которая каждый раз заново создает таблицу Tracks, избегая ошибок. Отметим, что команда DROP TABLE удаляет из базы данных таблицу со всем ее содержимым без возможности восстановления (отмена операции – "Undo" – не предусмотрена).
cur.execute('DROP TABLE IF EXISTS Tracks ')
Вторая команда создает таблицу с именем Tracks, которая содержит два столбца: в стобец с именем title помещается текстовая информация, в столбец с именем plays – целые числа.
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
Теперь, когда мы создали таблицу с именем Tracks, мы можем поместить данные в эту таблицу с помощью операции INSERT языка SQL. Как и в предыдущем случае, мы начинаем с установки соединения с базой данных и получения курсора (аналога файлового дескриптора). После этого, используя курсор, мы можем выполнять SQL-команды.
Команда SQL INSERT указывает, какую именно таблицу мы используем, затем задает новую строку таблицы, перечисляя поля, которые мы хотим в нее включить (title, plays), и после ключевого слова VALUES – значения, которые мы хотим поместить в новую строку таблицы. Мы можем задать значения с помощью вопросительных знаков (?, ?), чтобы указать, что реальные значения передаются в виде кортежа ( 'My Way', 15 ) в качестве второго параметра метода execute():
import sqlite3
conn = sqlite3.connect('music.db')
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()
Сначала c помощью команды INSERT мы вставляем две строки в нашу таблицу, затем мы используем метод commit() для форсированной записи данных в файл.
Затем мы используем команду SELECT, чтобы получить из таблицы две строки, которые только что были добавлены в нее. В команде SELECT мы указываем, какие столбцы нам нужны (title, plays), а также имя таблицы, из которой мы извлекаем информацию. После выполнения операции SELECT курсор (т.е. переменная cur) позволяет нам перебирать выбранные данные в цикле for. Для эффективности курсор в действительности не читает все данные из базы сразу при выполнении операции SELECT, вместо этого каждая очередная порция данных считывается по отдельности, когда мы перебираем выбранные строки в цикле for.
На выходе программы получаем:
Tracks: (u'Thunderstruck', 20) (u'My Way', 15)
В цикле for найдены две строки, каждая из которых представляет собой кортеж в смысле Питона, его первым элементом является заголовок (музыкального произведения), вторым – число его исполнений. Пусть вас не смущает префикс "u", с которого начинаются заголовки, – это просто указание, что строки представлены в кодировке Unicode, которая дает возможность использовать любые символы, а не только латинские буквы. В самом конце программы мы выполняем SQL-команду DELETE, удаляя только что созданные строки, что позволяет нам исполнять программу снова и снова.
Команда DELETE демонстрирует использование условия WHERE, задающего критерий выбора строк, к которым применяется команда. В данном примере получилось так, что критерий применим ко всем строкам, в результате таблица становится пустой. Благодаря этому программу можно запускать много раз.
После выполнения команды DELETE мы также вызываем метод commit() для форсированного удаления данных из файла базы данных.
До сих пор мы использовали Язык структурированных запросов (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) позволяют выполнять четыре главные операции, необходимые для создания данных и работы с ними.
В этом разделе мы создадим простую программу-паука, которая пройдет по всем учетным записям Твиттера и создаст по ним базу данных. Замечание: будьте осторожны, запуская эту программу! Не следует извлекать чересчур много данных или запускать программу на слишком долгое время, что может повлечь закрытие вашего аккаунта.
Одна из проблем, с которой мы сталкиваемся, когда создаем программу-паука – нужно иметь возможность в любой момент остановить ее и вновь запустить, это может повторяться многократно и при этом не должны теряться полученные ранее данные.
Не хотелось бы каждый раз вновь запускать процесс получения данных с самого начала, поэтому мы сохраняем данные сразу по их получении, таким образом, программа при перезапуске начинает работать с того места, где она была прервана.
Мы начинаем с какого-то пользователя Твиттера, извлекая список его друзей и их статусов, перебираем элементы этого списка в цикле и добавляем каждого из друзей в базу данных, чтобы в будущем извлечь и списки их друзей. После того, как мы обработали друзей одного человека, мы проверяем нашу базу данных и извлекаем из Твиттера друзей какого-нибудь человека, ранее занесенного в базу.
Это повторяется снова и снова, каждый раз мы находим в базе человека, которого еще "не посетили" в Твиттере, извлекаем список его друзей и добавляем в базу тех из них, кто ранее еще не был в нее занесен, чтобы в будущем посетить их тоже. По ходу дела мы отслеживаем также, сколько раз конкретный человек встретился в списках друзей, чтобы определить степень его "популярности".
Сохраняя в базе данных список известных учетных записей, а также для каждой записи информацию о том, был ли для нее извлечен список друзей или еще нет, плюс данные о том, насколько популярна эта учетная запись, мы получаем возможность остановить нашу программу и вновь запустить ее столько раз, сколько нам захочется.
Эта программа довольно сложная. Она основана на упражнении, приведенном ранее в этой книге, в котором мы используем 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 либо добавляет его в базу, либо увеличивает счетчик его друзей, если аккаунт друга уже содержится в базе.
Поскольку данные программы сохраняются в базе данных на диске, работа паука может быть приостановлена и возобновлена многократно без потери данных. Замечание. Прежде чем завершить эту тему, еще раз предупреждаем, что с программой-пауком Твиттера нужно быть осторожным. Не следует извлекать из сети слишком много данных или запускать программу на большое время – это может привести к потере доступа к Твиттеру.
Настоящая сила реляционных баз данных проявляется, когда мы создаем несколько таблиц и устанавливаем связи между ними. Принятие решений о том, каким именно образом разделить данные приложения между несколькими таблицами и как установить соотношения между двумя таблицами, называется моделированием данных (data modeling). Документ с дизайном вашего приложения, который показывает таблицы и их связи, называется моделью данных (data model).
Моделирование данных – непростое искусство, в этом разделе мы познакомимся лишь с самыми основами моделирования реляционных данных. Более подробную информацию по этой теме можно найти по адресу .
Пусть в нашей программе-пауке Твиттера вместо простого подсчета друзей каждого человека мы хотим получить список всех входящих связей, чтобы можно было получить список всех тех, кто дружит с конкретным аккаунтом.
Поскольку каждый пользователь Твиттера может иметь множество аккаунтов, которые с ним дружат, недостаточно просто добавить единственный столбец в нашу таблицу Twitter. Поэтому мы создаем новую таблицу, в которой будут храниться пары друзей. Ниже указан простой способ создания подобной таблицы Pals (англ. "приятели"):
CREATE TABLE Pals (from_friend TEXT, to_friend TEXT)
Каждый раз, когда мы встречаем человека, который является другом пользователя drchuck, мы добавляем в таблицу Pals строку вида:
INSERT INTO Pals (from_friend,to_friend) VALUES ('drchuck', 'lhawthorn')
После обработки 100 друзей аккаунта drchuck мы добавим в таблицу 100 записей, в которых "drchuck" будет первым параметром, что приведет нас к многократному повторению одной и той же строки в базе данных.
Такое повторение строковых данных нарушает хорошую практику нормализации баз данных (database normalization), которая требует, чтобы одна и та же строка не помещалась в базу дважды. Если нам нужно использовать данные более одного раза, то мы создаем целочисленный ключ к реальным данным и ссылаемся на них с помощью этого ключа.
Говоря практически, строка занимает намного больше пространства в памяти или на диске, чем целое число, и требует больше процессорного времени для сравнения и сортировки. Когда мы имеем всего лишь несколько сотен записей, объем памяти и время обработки несущественны. Но если в нашей базе данных миллион человек и, возможно, 100 миллионов ссылок на друзей, скорость сканирования данных становится очень важной.
Мы будем хранить наши Твиттер-аккаунты в таблице с именем People вместо таблицы Twitter из предыдущих примеров. Таблица People имеет дополнительный столбец для хранения целочисленного ключа, соответствующего строке пользователя Твиттера. SQLite дает возможность автоматически добавлять ключевое значение для любой строки, добавляемой в базу данных, используя специальный тип данных в столбце (INTEGER PRIMARY KEY).
Таблица People с дополнительным столбцом, хранящим идентификаторы, создается следующим образом:
CREATE TABLE People (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)
Отметим, что мы больше не поддерживаем счетчик числа друзей для каждой строки в таблице People. Когда мы задали тип столбца "id" как INTEGER PRIMARY KEY, мы указали, что 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, мы моделируем "отношение", при котором один человек следует за некоторым другим, и представляем это отношение парами чисел. Каждая пара а) указывает, что люди связаны между собой и (б) устанавливает направление этой связи.
Теперь мы перепишем нашу программу-паука Твиттера, используя две таблицы, первичные ключи и ссылки между ключами, как было описано выше. Вот код новой версии программы:
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 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 = TWITTER_URL.replace('ACCT', acct)
print 'Retrieving', url
document = urllib.urlopen (url).read()
tree = ET.fromstring(document)
cur.execute('UPDATE People 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 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()
Программа становится немного более сложной, но зато она иллюстрирует шаблоны кода, который используется при установлении связей между таблицами с помощью целочисленных ключей. Наиболее важные моменты следующие.
Ниже мы рассмотрим каждый из этих пунктов.
Поскольку мы уже разработали дизайн наших таблиц, мы можем сообщить исполняющей системе базы данных, что нужно установить ряд правил при работе с таблицами. Эти правила предотвращают возможные ошибки и добавление в базу некорректных данных. Вот код для создания таблиц:
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))''')
Используя ключевое слово UNIQUE, мы указываем, что значения в столбце "name" таблицы People должны быть уникальными (не могут повторяться). Точно так же уникальными должны быть и пары чисел в строках таблицы Follows. Это предотвращает такие ошибки, как добавление одного и того же соотношения дважды. Мы можем воспользоваться преимуществами этих ограничений в следующем коде:
cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)
VALUES ( ?, 0)''', ( friend, ) )
Мы добавили условие OR IGNORE ("или игнорировать") в оператор INSERT, чтобы указать, что, если выполнение команды INSERT приведет к нарушению правила "поле name должно быть уникальным", исполняющая система должна проигнорировать эту команду.
Мы используем ограничения как страховочную сетку, чтобы случайно не сделать что-нибудь неправильно. Аналогично, следующий код предохраняет нас от двукратного добавления одного и того же соотношения в таблицу Follows:
cur.execute('''INSERT OR IGNORE INTO Follows
(from_id, to_id) VALUES (?, ?)''', (id, friend_id) )
Снова мы указываем исполняющей системе базы данных, что она должна игнорировать наши попытки выполнения команды INSERT, если это приводит к нарушению условия уникальности для строк таблицы Follows.
Когда мы запрашиваем название аккаунта Твиттера, то, если аккаунт существует, нужно найти значение его id. Если такого аккаунта в таблице People еще нет, нужно его добавить и получить значение id для добавленной строки.
Это очень часто встречающийся фрагмент кода, например, он дважды использован в приведенной выше программе. Код демонстрирует, как находить id для аккаунта друга, когда мы извлекаем текстовое значение узла screen_name, подчиненного узлу user в XML-документе.
Поскольку со временем вероятность того, что аккаунт уже занесен в базу данных, возрастает, мы сначала проверяем, содержится ли соответствующая запись в таблице People, используя оператор SELECT. Если код внутри блока try выполняется нормальноtry/except.fetchone(), затем извлекаем первый (и единственный) элемент возвращенного кортежа и записываем его в поле friend_id. Если операция SELECT заканчивается неудачно, то код fetchone()[0] приводит к отказу и управление передается в секцию except.
friend = user.find('screen_name').text
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, чтобы посмотреть, сколько строк обновилось. Поскольку мы делали попытку добавить единственную строку, то, если число обновленных строк отлично от единицы, это свидетельствует об ошибке.
Если команда INSERT завершается успешно, мы можем использовать переменную cur.lastrowid, чтобы получить значение id, сгенерированное базой данных для созданной строки.
Когда нам уже известны значения ключей пользователя Твиттера и его друга, указанного в XML, нетрудно добавить пару чисел в таблицу Follows с помощью следующего кода:
cur.execute('INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)',
(id, friend_id) )
Заметим, что мы поручили самой базе данных следить за тем, чтобы задающая отношение дружбы пара не была добавлена в таблицу дважды – для этого при создании таблицы мы задали ограничение на единственность, а при добавлении использовали вариант OR IGNORE ("или игнорировать") команды INSERT. Вот пример выполнения программы:
Enter a Twitter account, or quit:
No unretrieved Twitter accounts found
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:
Retrieving http://api.twitter.com/l/statuses/friends/opencontent.xml
New accounts= 97 revisited= 3
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
Мы начали с аккаунта 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)
295 rows.
Follows:
(1, 2)
(1, 3)
(1, 4)
(1, 5)
(1, 6)
300 rows.
Можно видеть значения полей id, name, и visited в таблице People, а также пары чисел, задающие отношение дружбы, в таблице Follows. Из таблицы People видно, что мы посетили первых трех человек и что их данные уже получены из Твиттера. Данные в таблице Follows показывают, что пользователи 2-6 являются друзьями пользователя drchuck (его номер 1). Произошло это потому, что первыми были получены и помещены в базу друзья пользователя drchuck.
Если бы мы напечатали больше строк таблицы Follows, то увидели бы также и друзей пользователей с номерами 2 и 3.
Теперь, когда мы начали построение модели данных, помещая данные в несколько таблиц и связывая между собой строки этих таблиц с помощью ключей, нужно ознакомиться с терминологией, относящейся к ключам.
В общем случае имеется три вида ключей, используемых в моделях баз данных.
Теперь, когда мы, следуя правилу о нормализации базы данных, разделили данные на две таблицы, связанные с помощью первичных и внешних ключей, нам нужен способ выбора данных в команде SELECT, который мог бы собрать вместе данные из разных таблиц.
SQL использует условие JOIN для соединения таблиц. В нем указываются поля, которые служат для соединения строк из разных таблиц.
Приведем пример команды SELECT с условием JOIN:
SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2
Условие JOIN указывает, что мы выбираем поля сразу из двух таблиц: Follows и People. Условие ON задает, как именно соединяются две таблицы. Берем строки из таблицы Follows и добавляем в их концы строки из таблицы People, у которых значение поля "id" совпадает со значением поля "from_id" строки из Follows.
Результатом команды JOIN является создание сверхдлинных "мета-строк", которые содержит как поля из таблицы People, так и соответствующие поля из таблицы Follows. Когда есть больше одного совпадения значений полей "id" таблицы People и "from_id" таблицы Follows, команда JOIN создает несколько мета-строк, соответствующих каждой совпадающей паре ключей, дублируя данные при необходимости.
Следующий пример распечатывает данные, которые мы имеем в базе после того, как приведенный выше вариант программы Твиттер-паука, основанный на двух таблицах, был запущен несколько раз.
import sqlite3
conn = sqlite3.connect('twdata.db')
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()
Программа сначала распечатывает содержимое таблиц 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) 295 rows. Follows: (1, 2) (1, 3) (1, 4) (1, 5) (1, 6) 300 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) 100 rows.
Вначале идут данные таблиц People и Follows; последние строки вывода представляют собой результат выполнения команды SELECT с условием JOIN. В ней мы находим аккаунты, которые являются друзьями аккаунта "opencontent" (т.е. People.id=2).
В каждой "мета-строке", возвращенной последней командой SELECT, первые два поля получены из таблицы Follows, за ними следуют поля с третьего по пятое из таблицы People. Можно также заметить, что в каждой объединенной "мета-строке" второе поле (Follows.to_id) соответствует третьему полю (People.id).
В этой главе мы познакомились с основами использования баз данных в программах на Питоне. Это сложнее, чем использовать для хранения данных словари Питона или обычные файлы, поэтому работать с базами данных следует только тогда, когда это действительно необходимо. Базы данных могут оказаться весьма полезными в следующих ситуациях:
Во многих приложениях достаточно простой базы данных с единственной таблицей, но все же большинство реальных задач требуют использования нескольких таблиц и связей между строками разных таблиц.
Когда возникает необходимость вводить связи между таблицами, важно хорошо продумать их дизайн и следовать правилу нормализации баз данных, чтобы обеспечить максимально эффективное использование их возможностей.
Поскольку чаще всего необходимость в базах данных возникает из-за огромного объема данных, с которыми приходится иметь дело, важно построить эффективную модель данных, чтобы программа работала максимально быстро.
Когда вы разрабатываете программу на Питоне, использующую базу данных SQLite, распространенным способом отладки является просмотр содержимого базы с помощью браузера базы данных SQLite ("SQLite Database Browser"). После запуска вашей программы браузер дает возможность быстро проверить, правильно ли работает ваша программа.
Нужно учитывать, что система SQLite предотвращает одновременное изменение одних и тех же данных разными программами. Например, если вы открыли базу данных в браузере, сделали какое-то изменение и всё ещё не нажали клавишу "save" (сохранить), браузер "блокирует" (lock) доступ к файлу базы для любых других программ.
В частности, ваша программа на Питоне не сможет работать с файлом базы, когда он заблокирован. Решение состоит в том, чтобы либо закрыть браузер, либо, используя его меню "File", закрыть базу данных перед тем, как начать опять работать с ней из программы Питона, – это позволит избежать отказов программы из-за блокировки файла базы.
Атрибут (attribute): одно из значений внутри кортежа. Чаще используются термины "столбец" или "поле".
Ограничение (constraint): указание базе данных, что к полю или к строке таблицы применяется некоторое правило. Чаще всего используется ограничение, требующее, чтобы не было дублирования значений в конкретном поле (т.е. все значения должны быть уникальными).
Курсор (cursor): позволяет выполнять SQL-команды над содержимым базы данных и извлекать данные из базы. В применении к базе данных курсор является аналогом файлового дескриптора в случае обычного файла или сокета в случае сети.
Браузер базы данных (database browser): программа, дающая возможность прямого подсоединения к базе данных, просмотра и изменения ее содержимого без необходимости написания программного кода.
Внешний ключ (foreign key): целочисленный ключ, который ссылается на первичный ключ некоторой строки в другой таблице. Внешние ключи устанавливают связи между строками разных таблиц.
Индекс (index): дополнительные данные, которые программное обеспечение баз данных поддерживает при добавлении строк в таблицу; они используются для ускорения поиска.
Логический ключ (logical key): ключ, используемый для поиска конкретной строки из "внешнего мира". Например, в таблице, содержащей учетные записи пользователей, адрес электронной почты человека является хорошим кандидатом на роль логического ключа.
Нормализация (normalization): создание модели данных таким образом, чтобы исключить дублирование данных. Мы храним каждый элемент данных только в одном месте, используя во всех других местах ссылки на него с помощью внешнего ключа.
Первичный ключ (primary key): целочисленный ключ, ассоциированный с каждой строкой таблицы, который используется для ссылки на данную строку из других таблиц. Часто база данных конфигурируется таким образом, чтобы автоматически генерировать первичные ключи при добавлении строк.
Отношение (relation): область внутри базы данных, содержащая кортежи и атрибуты. Чаще используется термин "таблица".
Кортеж (tuple): одна запись в таблице базы данных, представляющая собой набор атрибутов. Чаще используется термин "строка".
Упражнение 26.1.
Получите по сети файл и используйте браузер базы данных SQLite, чтобы узнать, сколько таблиц содержится в базе; определите также для каждой таблицы список ее полей и их типов. Тип одного из полей не был рассмотрен в этой главе. Используйте online-документацию SQLite, чтобы описать, для чего нужен подобный тип данных.
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.