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

26.10. Использование команды JOIN для получения данных

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

26.11. Резюме

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

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

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

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

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

26.12. Отладка

Когда вы разрабатываете программу на Питоне, использующую базу данных SQLite, распространенным способом отладки является просмотр содержимого базы с помощью браузера базы данных SQLite ("SQLite Database Browser"). После запуска вашей программы браузер дает возможность быстро проверить, правильно ли работает ваша программа.

Нужно учитывать, что система SQLite предотвращает одновременное изменение одних и тех же данных разными программами. Например, если вы открыли базу данных в браузере, сделали какое-то изменение и всё ещё не нажали клавишу "save" (сохранить), браузер "блокирует" (lock) доступ к файлу базы для любых других программ.

В частности, ваша программа на Питоне не сможет работать с файлом базы, когда он заблокирован. Решение состоит в том, чтобы либо закрыть браузер, либо, используя его меню "File", закрыть базу данных перед тем, как начать опять работать с ней из программы Питона, – это позволит избежать отказов программы из-за блокировки файла базы.