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

26.7. Основы моделирования данных

Настоящая сила реляционных баз данных проявляется, когда мы создаем несколько таблиц и устанавливаем связи между ними. Принятие решений о том, каким именно образом разделить данные приложения между несколькими таблицами и как установить соотношения между двумя таблицами, называется моделированием данных (data modeling). Документ с дизайном вашего приложения, который показывает таблицы и их связи, называется моделью данных (data model).

Моделирование данных – непростое искусство, в этом разделе мы познакомимся лишь с самыми основами моделирования реляционных данных. Более подробную информацию по этой теме можно найти по адресу http://en.wikipedia.org/wiki/Relational_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, мы моделируем "отношение", при котором один человек следует за некоторым другим, и представляем это отношение парами чисел. Каждая пара а) указывает, что люди связаны между собой и (б) устанавливает направление этой связи.

26.8. Программирование с несколькими таблицами

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

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()
  

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

1. Создание таблиц с первичными ключами и ограничениями.
2. Когда мы имеем логический ключ, определяющий человека (в данном случае это имя аккаунта), нам нужно получить его id (т.е. соответствующий целочисленный ключ). В зависимости от того, занесен ли данный человек в таблицу People или еще нет, нам нужно либо (1) найти человека в таблице и извлечь значение id, либо (2) добавить человека в таблицу People и получить сгенерированное значение id для добавленной строки.
3. Добавление строки в таблицу Follows, устанавливающей соотношения между людьми.

Ниже мы рассмотрим каждый из этих пунктов.

26.8.1. Ограничения в таблицах баз данных

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

cur.execute('''CREATE TABLE IF NOT EXISTS People
(id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Follows
(from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))''')
    

Используя ключевое слово 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.