Модуль sqlite3 языка Python. Общее представление

Сами по себе СУБД редко используются для работы с базами данных. В том смысле, что в реальных проектах связки БД + СУБД бывает недостаточно. Обычно с СУБД работают через какой-либо язык программирования. Это позволяет более гибко принимать запросы, обрабатывать ответы перед передачей их куда-либо далее. Ведь у императивного, а не декларативного как SQL, языка программирования средств для работы с данными больше, да и логика богаче.

При этом неизбежна определенная специфика, накладываемая языком программирования и особенностями его работы с СУБД. Есть команды на подключение к БД, использование объекта "курсора", выполнение SQL-запросов, сохранение изменений в БД и др.

Инструменты для работы с конкретной СУБД не являются базовыми командами и объектами самого языка. Обычно они подключаются через импорт модуля или библиотеки. Так модуль sqlite3 входит в установочный пакет языка Python, является компонентом стандартной библиотеки и не требует отдельной загрузки и установки. Однако его все равно надо импортировать.

Библиотеку SQLite также не требуется устанавливать отдельно. Она есть в установочном пакете Python. Непосредственно модуль sqlite3 – это API к СУБД SQLite. Своего рода адаптер, который переводит команды, написанные на Питоне, в команды, которые понимает SQLite. Как и наоборот, доставляет ответы от SQLite в python-программу.

Модуль sqlite3 содержит много классов, функций и констант. Их перечень можно посмотреть с помощью функции dir.

Рассмотрим ознакомительный скрипт на Python, который создает две таблицы базы данных, заполняет одну из них и выполняет запросы на получение из нее данных:

import os
import sqlite3

db_name = 'site.db'
db_exists = os.path.exists(db_name)

db = sqlite3.connect(db_name)
c = db.cursor()
print(type(db), type(c))
c.execute('PRAGMA foreign_keys = ON')

if not db_exists:
    c.execute('''CREATE TABLE sections (
                            _id INTEGER PRIMARY KEY,
                            name TEXT)''')

    c.execute('''CREATE TABLE pages (
                            _id INTEGER PRIMARY KEY AUTOINCREMENT,
                            title TEXT,
                            url TEXT NOT NULL,
                            theme INTEGER NOT NULL,
                            num INTEGER NOT NULL DEFAULT 100,
                            FOREIGN KEY (theme) REFERENCES sections(_id))''')
    themes = ((1, 'Information'), (2, 'Digital Systems'),(3, 'Boolean Algebra'))
    c.executemany('''INSERT INTO sections VALUES (?, ?)''', themes)
    c.execute('''INSERT INTO sections VALUES (4, ?)''', ('Algorithm',))
    db.commit()

c.execute('SELECT * FROM sections')
print(c.fetchone(), '!')
for i in c:
    print(i)

c.execute('SELECT * FROM sections')
print(c.fetchall())

c.execute('SELECT * FROM sections')
print(c.fetchmany(2))
print(c.fetchmany(3))
db.close()

Вызов функции connect приводит к созданию объекта-экземпляра от класса Connection. Этот объект обеспечивает связь с файлом базы данных, представляет конкретную БД в программе. Если файла БД по указанному адресу не существует, то он будет создан.

После того как экземпляр Connection создан, чтобы выполнять SQL-команды, надо создать еще один объект, но теперь уже от класса Cursor. Делается это с помощью метода cursor() экземпляра Connection. В скрипте мы выводим на экран типы переменных db и c для демонстрации их классов:

<class 'sqlite3.Connection'> <class 'sqlite3.Cursor'>

Каждый из этих классов предоставляет свой набор методов для работы с базой данных.

Так SQL-команды выполняются с помощью метода execute() экземпляра Cursor и некоторых других. Длинные запросы удобно разбить на несколько строк, используя тройные кавычки.

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

В конце SQL-запросов, передаваемых в execute(), точка с запятой не ставится.

Метод execute() возвращает тот же экземпляр Cursor, на который вызывается. Однако в этом экземпляре может так или иначе содержаться результат выполнения SQL-запроса, если таковой предполагался.

Заполнять таблицы можно тоже с помощью execute(). Однако, если требуется вставить несколько записей, лучше воспользоваться методом executemany().

Мы создаем список из кортежей. Каждый кортеж – это отдельная запись таблицы. Метод executemany() выполняет SQL-команду по отношению к каждому элементу списка. При этом данные из кортежа подставляются вместо знаков вопроса. Такая подстановка работает и через execute().

То, что подставляется, должно быть кортежем. Через знаки вопроса кортеж как бы распаковывается.

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

У объекта-курсора есть методы fetchone, fetchmany и fetchall, которые позволяют извлекать из него данные, если sql-запрос предполагал их передачу. По-сути они наделяют курсор свойствами объекта-итератора (такой имеет метод __next__).

В примере выше первый вызов fetchone() приведет к выводу первой записи. Далее в цикле из курсора будут извлечены последующие:

(1, 'Information') !
(2, 'Digital Systems')
(3, 'Boolean Algebra')
(4, 'Algorithm')

После того как итератор опустошен, необходимо снова вызвать execute(). Метод fetchall() возвращает список сразу всех записей:

[(1, 'Information'), (2, 'Digital Systems'), (3, 'Boolean Algebra'), (4, 'Algorithm')]

Метод fetchmany() позволяет указать необходимое количество записей, которые необходимо извлечь за раз:

[(1, 'Information'), (2, 'Digital Systems')]
[(3, 'Boolean Algebra'), (4, 'Algorithm')]

Для того чтобы корректно завершить работу с базой данных, надо применить изменения (выполнить транзакцию) и разорвать соединение. Обратите внимание, это делается по отношению к экземпляру Connection, а не Cursor.

Закрытие без commit() приведет к потере изменений, сделанных за сессию. Если нужно откатить, а не применить, изменения текущей сессии, используется метод rollback().

Отметим, что методы execute и подобные есть также у экземпляра Connection. Однако при вызове на него, например, execute, каждый раз будет возвращаться новый экземпляр Cursor.