Программный интерфейс SQLite

Общая структура модуля sqlite3

В стандартный дистрибутив языка Python в качестве одного из средств долгосрочного сохранения информации входят как библиотека SQLite, так и модуль sqlite3 для работы с ней.

Импортируется модуль стандартно:

import sqlite3

Далее мы будем для удобства считать, что он импортирован командой:

import sqlite3 as db

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

connection = db.connect("foo.db")

Результат работы этой функции (который мы в дальнейшем будем называть соединением) используется в основном для двух вещей:

  • для создания курсора
  • для подтверждения изменений, произведённых с таблицами базы

Основная сущность для работы с базой данный — курсор. Он создаётся методом cursor соединения с базой:

cursor = connection.cursor()

У него есть три наиболее важных метода:

  • метод execute для исполнения одного запроса, сформированного по шаблону
  • метод executemany для исполнения серии запросов, сформированных по одному шаблону
  • метод executescript для исполнения программы на SQL

Если запрос изменяет содержимое какой-либо таблицы (например, этот запрос из серии INSERT, UPDATE, DELETE), изменения нужно подтвердить вызовом метода commit соединения с базой. В противном случае эти изменения не будут сохранены в базу (хотя будут видны дальнейшим запросам, сделанным в рамках текущего соединения).

Если запрос выдаёт какой-то результат, то этот результат можно получить методом fetchall курсора (есть ещё более низкоуровневые fetch и fetchmany, но их мы оставим за рамками обсуждения).

Приведём типичный сценарий работы с базой данных:

cursor.executescript("""
-- тройные кавычки в питоне используются для многострочных текстов

CREATE TABLE squares(arg,val); 
CREATE TABLE people(id INTEGER PRIMARY KEY, name);
""")
## отметим, что CREATE TABLE не требует подтверждения при помощи commit

cursor.execute("INSERT INTO people VALUES(NULL, 'Вася')")
cursor.execute("INSERT INTO people VALUES(NULL, 'Петя')")
cursor.execute("INSERT INTO people VALUES(NULL, 'Даша')")

for i in range(100):
    cursor.execute("INSERT INTO squares VALUES(?,?)", [i, i**2])
    ## это -- пример шаблонного запроса; шаблоны мы обсудим далее

connection.commit()  ## ещё раз отметим, что commit -- метод _не курсора_, а соединения

Использование шаблонных запросов

Если запрос генерируется из каких-то данных, вычисляемых программой, настоятельно не рекомендуется формировать этот запрос при помощи стандартной текстовой конкатенации: в случае, если эти данные получаются откуда-то извне программы (а это — наиболее типичный источник данных), злонамеренным образом сформированные данные могут дать их автору доступ к базе данных (см. ссылку).

Чтобы сделать из запроса шаблон, нужно те места, в которые требуется вставить данные, пометить знаком вопроса (поставленным вместо этих данных). Далее в функцию execute вторым входом нужно подставить массив значений — по одному на каждый знак вопроса в шаблоне.

Есть ещё метод executemany, который работает примерно так:

## код, эквивалентный вызову cursor.executemany(request, data)

for datum in data:
    cursor.execute(request, datum)

Получение результатов запроса

Результат последнего выполненного запроса можно получить от курсора безаргументным методом fetchall:

cursor.execute("SELECT * FROM foo WHERE bar = baz")
results = cursor.fetchall()

Важный момент состоит в том, что fetchall выдаёт результат ровно один раз на запрос. Поэтому, если не сохранить этот результат (связав его с переменной или положив в какую-нибудь структуру данных), он потеряется.

Автоматическое закрытие соединения

Вообще говоря, для типичной программы не характерно создание большого количества различных одновременно действующих соединений с базами данных. Тем не менее, иногда это может получиться случайно: например, в результате многократного вызова функции, которая открывает соединение с базой и что-то с этой базой делает, оставляя после себя соединение открытым. Хотя стандартная реализация языка Python на данный момент использует механизм сборки мусора, срабатывающий в тот момент, когда исчезает последняя ссылка на объект (такой механизм гарантирует, что соединение будет закрыто после завершения работы функции, которая создала это соединение, но не сохранила его никуда), альтернативные реализации могут использовать (и используют) другие механизмы сборки мусора.

Просто вызвать метод close соединения обычно недостаточно: никто не гарантирует, что до его вызова не произойдёт какая-нибудь ошибка (например, ошибка доступа к базе данных), которая выбросит из функции до того момента, как close окажется вызванным. С такой проблемой можно справиться, используя конструкцию try...finally, но она сильно загромождает код.

К счастью, в Python есть (позаимствованная из LISP) конструкция with...as:

with db.connect("foo.db") as connection:
    # блок кода

Эта конструкция гарантирует, что соединение будет закрыто при выходе из неё (даже при аварийном).

Некоторое количество упражнений

  1. Напишите программу, которая принимает на вход имя файла с базой данных, удаляет в ней таблицу foo, если такая там была, и создаёт там таблицу foo(bar,baz). Запрещается пользоваться исключениями или же SQL-командами CREATE TABLE IF NOT EXISTS или DROP TABLE IF EXISTS (не все версии sqlite3 их поддерживают). Узнать наличие таблицы можно в специальной служебной таблице sqlite_master. Записи, соответствующие таблицам базы данных, в ней имеют значение 'table' в колонке type и имя таблицы в колонке name.

  2. Напишите программу, которая принимает на вход имя файла с базой данных, создаёт там таблицу squares(n,val) и заполняет парами (x,x**2) для всех x от 1 до 10000000 (10 миллионов). В следующих трёх заданиях работа происходит с созданной в этом задании базой. Программа в них тоже должна принимать на вход только имя файла с базой данных.

  3. Напечатайте результаты и время выполнения запроса SELECT n FROM squares WHERE val > 100000 AND val < 105000. Время выполнения можно измерить функцией timeit модуля timeit (прочитайте документацию, не забудьте указать number=1 при вызове, измерять нужно полное время общения с БД, начиная от её открытия и заканчивая печатью результатов).

  4. Выполните SQL-команду CREATE INDEX squares_val_index ON squares(val). Повторите измерение из прошлого задания. Обратите внимание на то, что индекс значительно убыстряет поиск значений в колонке. При этом большое количество индексов для одной и той же таблицы замедляют добавление в неё записей. Поэтому иногда рекомендуется перед массовым добавлением записей в таблицу сбросить все индексы, а потом создать их заново.

  5. Удалите все записи из таблицы квадратов, а затем измерьте время их добавления при наличии индекса на колонке val и при его отсутствии.

  6. Напишите программу, которая принимает на вход два имени файлов с базами данных (эти имена разделены переходом на следующую строчку). Программа должна скопировать все таблицы первой базы во вторую базу. Наличие таблиц во второй базе перед копированием проверять не нужно! Команду, которая использовалась для создания таблицы, можно узнать в колонке sql таблицы sqlite_master.