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

26.8.2. Получение и добавление записи

Когда мы запрашиваем название аккаунта Твиттера, то, если аккаунт существует, нужно найти значение его id. Если такого аккаунта в таблице People еще нет, нужно его добавить и получить значение id для добавленной строки.

Это очень часто встречающийся фрагмент кода, например, он дважды использован в приведенной выше программе. Код демонстрирует, как находить id для аккаунта друга, когда мы извлекаем текстовое значение узла screen_name, подчиненного узлу user в XML-документе.

Поскольку со временем вероятность того, что аккаунт уже занесен в базу данных, возрастает, мы сначала проверяем, содержится ли соответствующая запись в таблице People, используя оператор SELECT. Если код внутри блока try выполняется нормально^{2Как правило, если предложение начинается со слов "если всё выполняется нормально", то обычно код требует включения его внутрь блока 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, сгенерированное базой данных для созданной строки.

26.8.3. Хранение ссылок на друзей

Когда нам уже известны значения ключей пользователя Твиттера и его друга, указанного в 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.

26.9. Три вида ключей

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

В общем случае имеется три вида ключей, используемых в моделях баз данных.

• Логический ключ (logical key) берется из "реальной жизни" и может использоваться при поиске строки. В нашем примере он содержится в поле "name". Это экранное имя (screen name) пользователя Твиттера, и мы действительно несколько раз ищем строку пользователя по этому имени в нашей программе. Чаще всего следует использовать ограничение UNIQUE (уникальный) для логического ключа. Поскольку логический ключ используется для поиска нужной строки, производимого извне, вряд ли имеет смысл хранить в таблице несколько строк с одним и тем же значением ключа.
• Первичный ключ (primary key) – это целое число, автоматически назначенное базой данных для данной строки. Вне программы оно обычно не имеет никакого смысла и используется только для того, чтобы связывать между собой строки из разных таблиц. Если мы хотим найти строку в таблице, то поиск по первичному ключу – обычно самый быстрый из всех возможных. Поскольку первичные ключи являются целыми числами, они требуют минимальной памяти для хранения и могут сравниваться и сортироваться очень быстро. В нашей модели первичный ключ содержится в поле "id".
• Внешний ключ (foreign key) – это обычно число, указывающее на первичный ключ строки из другой таблицы. Примером внешнего ключа в нашей модели данных является содержимое поля "from_id". Мы придерживаемся следующего соглашения об именах ключей: поле, содержащее первичный ключ, всегда имеет имя "id"; поле, содержащее внешний ключ, образуется путем добавления к "id" спереди некоторого префикса и символа подчеркивания, оно имеет вид "prefix_id".