Модуль sqlite3 языка Python

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

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

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

>>> import sqlite3

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

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

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

>>> db = sqlite3.connect('site.sqlite')
>>> type(db)
<class 'sqlite3.Connection'>

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

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

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

>>> cur.execute('''
... CREATE TABLE sections (
... _id INTEGER PRIMARY KEY,
... name TEXT)''')
<sqlite3.Cursor object at 0x7fd0f16fe110>

>>> cur.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))
... ''')
<sqlite3.Cursor object at 0x7fd0f16fe110>

Если нужна поддержка внешнего ключа включим ее:

>>> cur.execute("PRAGMA foreign_keys = ON")
<sqlite3.Cursor object at 0x7fd0f16fe110>

Обратим внимание, что в конце SQL-запросов здесь точка с запитой не ставятся. Также метод возвращает сам объект.

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

>>> themes = [
... (1, 'Information'),
... (2, 'Digital Systems'),
... (3, 'Boolean Algebra')]

>>> cur.executemany('''
... INSERT INTO sections 
... VALUES (?, ?)''', themes)
<sqlite3.Cursor object at 0x7fe7729f8a40>

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

>>> cur.execute('''
... INSERT INTO sections VALUES
... (4, ?)''', ('Algorithm',))
<sqlite3.Cursor object at 0x7fe7729f8a40>

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

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

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

>>> cur.execute("SELECT * FROM sections")
<sqlite3.Cursor object at 0x7fd0f16fe110>
>>> cur.fetchone()
(1, 'Information')
>>> cur.fetchone()
(2, 'Digital Systems')
>>> cur.__next__()
(3, 'Boolean Algebra')

Два других метода:

>>> cur.execute("SELECT * FROM sections")
>>> cur.fetchall()
[(1, 'Information'), (2, 'Digital Systems'), 
(3, 'Boolean Algebra'), (4, 'Algorithm')]
>>> cur.fetchall()
[]
>>> cur.execute("SELECT * FROM sections")
<sqlite3.Cursor object at 0x7fd0f16fe110>
>>> cur.fetchmany(2)
[(1, 'Information'), (2, 'Digital Systems')]
>>> cur.fetchmany(2)
[(3, 'Boolean Algebra'), (4, 'Algorithm')]
>>> cur.fetchmany(2)
[]

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

>>> db.commit()
>>> db.close()

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