sqlite3 — интерфейс DB-API 2.0 для баз данных SQLite

Функции модуля

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False, *, autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)

Открыть подключение к базе данных SQLite.

Параметры:

• database (объект типа пути) – Путь к файлу базы данных, который нужно открыть. Вы можете передать ":memory:" для создания базы данных SQLite, существующей только в памяти, и открытия подключения к ней.
• timeout (float) – Количество секунд, которое подключение должно ждать, прежде чем вызывать исключение OperationalError, когда таблица заблокирована. Если другое подключение открывает транзакцию для изменения таблицы, эта таблица будет заблокирована до тех пор, пока транзакция не будет подтверждена. По умолчанию пять секунд.
• detect_types (int) – Управление тем, как и будут ли обрабатываться типы данных, не поддерживаемые SQLite напрямую, для их преобразования в типы Python с использованием преобразователей, зарегистрированных в register_converter(). Установите комбинацию (используя |, побитовое ИЛИ) PARSE_DECLTYPES и PARSE_COLNAMES для включения этой возможности. Имена столбцов имеют приоритет над объявленными типами, если установлены оба флага. Типы не могут быть обнаружены для сгенерированных полей (например, max(data)), даже если параметр detect_types установлен; вместо этого будет возвращено str. По умолчанию (0), обнаружение типов отключено.
• isolation_level (str | None) – Управление поведением обработки транзакций. См. Connection.isolation_level и Управление транзакциями через атрибут isolation_level для получения дополнительной информации. Может быть "DEFERRED" (по умолчанию), "EXCLUSIVE" или "IMMEDIATE"; или None для отключения неявного открытия транзакций. Не имеет эффекта, если Connection.autocommit не установлен в значение LEGACY_TRANSACTION_CONTROL (по умолчанию).
• check_same_thread (bool) – Если True (по умолчанию), ProgrammingError будет вызвано, если подключение к базе данных используется потоком, отличным от того, который его создал. Если False, к подключению можно получить доступ из нескольких потоков; операции записи могут потребовать сериализации пользователем для предотвращения повреждения данных. См. threadsafety для получения дополнительной информации.
• factory (Connection) – Пользовательский подкласс Connection для создания подключения, если он не класс по умолчанию Connection.
• cached_statements (int) – Количество инструкций, которые sqlite3 должно кэшировать для этого подключения, чтобы избежать издержек анализа. По умолчанию 128 инструкций.
• uri (bool) – Если установлено значение True, database интерпретируется как URI с путем к файлу и необязательной строкой запроса. Часть схемы должна быть "file:", а путь может быть относительным или абсолютным. Строка запроса позволяет передавать параметры в SQLite, что позволяет использовать различные способы работы с URI SQLite.
• autocommit (bool) – Управление поведением обработки транзакций PEP 249. См. Connection.autocommit и Управление транзакциями через атрибут autocommit для получения дополнительной информации. По умолчанию autocommit сейчас равен LEGACY_TRANSACTION_CONTROL. По умолчанию будет изменено на False в будущей версии Python.

Тип возвращаемого значения:

Connection

Возбуждает событие аудита sqlite3.connect с аргументом database.

Возбуждает событие аудита sqlite3.connect/handle с аргументом connection_handle.

Изменено в версии 3.4: Добавлен параметр uri.

Изменено в версии 3.7: database теперь также может быть объектом типа пути, а не только строкой.

Изменено в версии 3.10: Добавлено событие аудита sqlite3.connect/handle.

Изменено в версии 3.12: Добавлен параметр autocommit.

Изменено в версии 3.13: Позиционное использование параметров timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements и uri устарело. Они станут ключевыми параметрами в Python 3.15.

sqlite3.complete_statement(statement)

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

Например:

>>> sqlite3.complete_statement("SELECT foo FROM bar;")
True
>>> sqlite3.complete_statement("SELECT foo")
False

Эта функция может быть полезна при вводе командной строки, чтобы определить, формирует ли введенный текст полную SQL-инструкцию или требуется дополнительный ввод перед вызовом execute().

См. runsource() в Lib/sqlite3/__main__.py для реального использования.

sqlite3.enable_callback_tracebacks(flag, /)

Включает или отключает отслеживание обратных вызовов. По умолчанию при работе с пользовательскими функциями, агрегатами, преобразователями, обратными вызовами авторизатора и т. д. отслеживания не будет. Если необходимо их отладить, можно вызвать эту функцию со значением flag, равным True. После этого вы получите отслеживание обратных вызовов в sys.stderr. Используйте False для отключения этой возможности.

Примечание

Ошибки в пользовательских функциях обратных вызовов регистрируются как необрабатываемые исключения. Используйте unraisable hook handler для интроспекции неисправного обратного вызова.

sqlite3.register_adapter(type, adapter, /)

Зарегистрируйте адаптер вызываемый объект для адаптации типа Python type к типу SQLite. Адаптер вызывается с объектом Python типа type в качестве единственного аргумента и должен возвращать значение типа, который SQLite поддерживает напрямую.

sqlite3.register_converter(typename, converter, /)

Зарегистрировать вызываемый объект converter для преобразования объектов SQLite типа typename в объект Python определенного типа. Преобразователь вызывается для всех значений SQLite типа typename; ему передается объект bytes, и он должен возвратить объект желаемого типа Python. Обратитесь к параметру detect_types в connect() для получения информации о том, как работает обнаружение типов.

Примечание: typename и имя типа в запросе сопоставляются без учета регистра.

Модульные константы

sqlite3.LEGACY_TRANSACTION_CONTROL

Установите autocommit на эту константу, чтобы выбрать поведение управления транзакциями в старом стиле (до Python 3.12). Дополнительную информацию см. в разделе Управление транзакциями с помощью атрибута isolation_level.

sqlite3.PARSE_COLNAMES

Передайте это значение флага параметру detect_types метода connect(), чтобы найти функцию-конвертер, используя имя типа, разобранное из имени столбца запроса, в качестве ключа словаря конвертеров. Имя типа должно быть заключено в квадратные скобки ([]).

SELECT p as "p [point]" FROM test;  ! will look up converter "point"

Этот флаг можно комбинировать с PARSE_DECLTYPES с помощью оператора | (побитовое ИЛИ).

sqlite3.PARSE_DECLTYPES

Передайте это значение флага параметру detect_types метода connect(), чтобы найти функцию-конвертер, используя объявленные типы для каждого столбца. Типы объявляются при создании таблицы базы данных. sqlite3 будет искать функцию-конвертер, используя первое слово объявленного типа в качестве ключа словаря конвертеров. Например:

CREATE TABLE test(
   i integer primary key,  ! will look up a converter named "integer"
   p point,                ! will look up a converter named "point"
   n number(10)            ! will look up a converter named "number"
 )

Этот флаг можно комбинировать с PARSE_COLNAMES с помощью оператора | (побитовое ИЛИ).

sqlite3.SQLITE_OK

sqlite3.SQLITE_DENY

sqlite3.SQLITE_IGNORE

Флаги, которые должны возвращаться вызываемой функцией authorizer_callback, переданной в Connection.set_authorizer(), для указания того, разрешен ли:

• Доступ (SQLITE_OK),
• SQL-запрос должен быть прерван с ошибкой (SQLITE_DENY)
• Столбец должен обрабатываться как значение NULL (SQLITE_IGNORE)

sqlite3.apilevel

Строковая константа, указывающая поддерживаемый уровень DB-API. Требуется DB-API. Задано значение "2.0".

sqlite3.paramstyle

Строковая константа, указывающая тип форматирования маркеров параметров, ожидаемый модулем sqlite3. Требуется DB-API. Задано значение "qmark".

Примечание

Поддерживается также стиль параметров DB-API named.

sqlite3.sqlite_version

Номер версии исполняемой библиотеки SQLite в виде string.

sqlite3.sqlite_version_info

Номер версии исполняемой библиотеки SQLite в виде tuple целых чисел integers.

sqlite3.threadsafety

Целочисленная константа, необходимая для DB-API 2.0, указывающая уровень безопасности потоков, поддерживаемый модулем sqlite3. Этот атрибут устанавливается на основе параметра режима потоков по умолчанию, с которым скомпилирована подлежащая библиотека SQLite. Режимы потоков SQLite:

1. Однопотоковый: В этом режиме все мьютексы отключены, и SQLite небезопасно использовать в более чем одном потоке одновременно.
2. Многопотоковый: В этом режиме SQLite можно безопасно использовать в нескольких потоках при условии, что ни одна база данных не используется одновременно в двух или более потоках.
3. Сериализованный: В режиме сериализации SQLite можно безопасно использовать в нескольких потоках без ограничений.

Сопоставление режимов потоков SQLite с уровнями безопасности потоков DB-API 2.0:

Режим потоков SQLite	threadsafety	SQLITE_THREADSAFE	Значение DB-API 2.0
однопотоковый	0	0	Потоки не могут совместно использовать модуль
многопотоковый	1	2	Потоки могут совместно использовать модуль, но не соединения
сериализованный	3	1	Потоки могут совместно использовать модуль, соединения и курсоры

Изменено в версии 3.11: Динамически установите threadsafety вместо жёсткого кодирования значения 1.

sqlite3.version

Номер версии этого модуля в виде string. Это не версия библиотеки SQLite.

Устарело начиная с версии 3.12, будет удалено в версии 3.14: Эта константа раньше отражала номер версии пакета pysqlite, сторонней библиотеки, которая раньше передавала изменения в sqlite3. Сегодня она не имеет смысла или практической ценности.

sqlite3.version_info

Номер версии этого модуля в виде tuple целых чисел integers. Это не версия библиотеки SQLite.

Устарело начиная с версии 3.12, будет удалено в версии 3.14: Эта константа раньше отражала номер версии пакета pysqlite, сторонней библиотеки, которая раньше передавала изменения в sqlite3. Сегодня она не имеет смысла или практической ценности.

sqlite3.SQLITE_DBCONFIG_DEFENSIVE

sqlite3.SQLITE_DBCONFIG_DQS_DDL

sqlite3.SQLITE_DBCONFIG_DQS_DML

sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY

sqlite3.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER

sqlite3.SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION

sqlite3.SQLITE_DBCONFIG_ENABLE_QPSG

sqlite3.SQLITE_DBCONFIG_ENABLE_TRIGGER

sqlite3.SQLITE_DBCONFIG_ENABLE_VIEW

sqlite3.SQLITE_DBCONFIG_LEGACY_ALTER_TABLE

sqlite3.SQLITE_DBCONFIG_LEGACY_FILE_FORMAT

sqlite3.SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

sqlite3.SQLITE_DBCONFIG_RESET_DATABASE

sqlite3.SQLITE_DBCONFIG_TRIGGER_EQP

sqlite3.SQLITE_DBCONFIG_TRUSTED_SCHEMA

sqlite3.SQLITE_DBCONFIG_WRITABLE_SCHEMA

Эти константы используются для методов Connection.setconfig() и getconfig().

Доступность этих констант варьируется в зависимости от версии SQLite, с которой был скомпилирован Python.

Добавлена в версии 3.12.

См. также

https://www.sqlite.org/c3ref/c_dbconfig_defensive.html

Документация SQLite: Параметры конфигурации соединения с базой данных

Объекты соединения

class sqlite3.Connection

Каждая открытая база данных SQLite представлена объектом Connection, который создаётся с помощью sqlite3.connect(). Их основное назначение — создание объектов Cursor и Управление транзакциями.

См. также

• Как использовать методы сокращения соединения
• Как использовать менеджер контекста соединения

Изменено в версии 3.13: Выдаётся предупреждение ResourceWarning, если метод close() не вызывается перед удалением объекта Connection.

Соединение с базой данных SQLite имеет следующие атрибуты и методы:

cursor(factory=Cursor)

Создаёт и возвращает объект Cursor. Метод cursor принимает один необязательный параметр factory. Если он указан, он должен быть вызываемым объектом, возвращающим экземпляр Cursor или его подклассов.

blobopen(table, column, row, /, *, readonly=False, name='main')

Открывает обработчик Blob для существующего объекта BLOB.

Параметры:

• table (str) – Имя таблицы, где расположен BLOB.
• column (str) – Имя столбца, где расположен BLOB.
• row (str) – Имя строки, где расположен BLOB.
• readonly (bool) – Устанавливается в значение True, если BLOB нужно открыть без прав записи. По умолчанию False.
• name (str) – Имя базы данных, где расположен BLOB. По умолчанию "main".

Возможные исключения:

OperationalError – При попытке открыть BLOB в таблице WITHOUT ROWID.

Тип возвращаемого значения:

Blob

Примечание

Размер BLOB нельзя изменить с помощью класса Blob. Используйте SQL-функцию zeroblob для создания BLOB с фиксированным размером.

Добавлен в версии 3.11.

commit()

Зафиксировать все ожидающие транзакции в базе данных. Если autocommit имеет значение True, или открытой транзакции нет, этот метод ничего не делает. Если autocommit имеет значение False, при фиксации ожидающей транзакции этот метод неявно открывает новую транзакцию.

rollback()

Отменить все ожидающие транзакции. Если autocommit имеет значение True, или открытой транзакции нет, этот метод ничего не делает. Если autocommit имеет значение False, при отмене ожидающей транзакции этот метод неявно открывает новую транзакцию.

close()

Закрыть соединение с базой данных. Если autocommit имеет значение False, любая ожидающая транзакция неявно отменяется. Если autocommit имеет значение True или LEGACY_TRANSACTION_CONTROL, никакого неявного управления транзакциями не выполняется. Убедитесь, что вы вызвали commit() перед закрытием, чтобы избежать потери несохранённых изменений.

execute(sql, parameters=(), /)

Создаёт новый объект Cursor и вызывает метод execute() на нём с заданным sql и parameters. Возвращает новый объект курсора.

executemany(sql, parameters, /)

Создаёт новый объект Cursor и вызывает метод executemany() на нём с заданным sql и parameters. Возвращает новый объект курсора.

executescript(sql_script, /)

Создаёт новый объект Cursor и вызывает метод executescript() на нём с заданным sql_script. Возвращает новый объект курсора.

create_function(name, narg, func, *, deterministic=False)

Создаёт или удаляет пользовательскую SQL-функцию.

Параметры:

• name (str) – Имя SQL-функции.
• narg (int) – Количество аргументов, которые может принимать SQL-функция. Если -1, она может принимать любое количество аргументов.
• func (callback | None) – Вызываемый объект, который вызывается при вызове SQL-функции. Вызываемый объект должен возвращать тип, напрямую поддерживаемый SQLite. Установите в значение None для удаления существующей SQL-функции.
• deterministic (bool) – Если True, созданная SQL-функция помечается как детерминированная, что позволяет SQLite выполнять дополнительные оптимизации.

Изменено в версии 3.8: Добавлен параметр deterministic.

Пример:

>>> import hashlib
>>> def md5sum(t):
...     return hashlib.md5(t).hexdigest()
>>> con = sqlite3.connect(":memory:")
>>> con.create_function("md5", 1, md5sum)
>>> for row in con.execute("SELECT md5(?)", (b"foo",)):
...     print(row)
('acbd18db4cc2f85cedef654fccc4a4d8',)
>>> con.close()

Изменено в версии 3.13: Передача name, narg и func в качестве именованных аргументов устарела. Эти параметры станут позиционными в Python 3.15.

create_aggregate(name, n_arg, aggregate_class)

Создаёт или удаляет пользовательскую SQL-агрегатную функцию.

Параметры:

• name (str) – Имя SQL-агрегатной функции.
• n_arg (int) – Количество аргументов, которые может принимать SQL-агрегатная функция. Если -1, она может принимать любое количество аргументов.

• aggregate_class (класс | None) –

  Класс должен реализовывать следующие методы:

  • step(): Добавить строку в агрегат.
  • finalize(): Возвратить окончательный результат агрегата в виде типа, напрямую поддерживаемого SQLite.

  Количество аргументов, которые должен принимать метод step(), контролируется параметром n_arg.

  Установите в None для удаления существующей SQL-агрегатной функции.

Пример:

class MySum:
    def __init__(self):
        self.count = 0

    def step(self, value):
        self.count += value

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.execute("CREATE TABLE test(i)")
cur.execute("INSERT INTO test(i) VALUES(1)")
cur.execute("INSERT INTO test(i) VALUES(2)")
cur.execute("SELECT mysum(i) FROM test")
print(cur.fetchone()[0])

con.close()

Изменено в версии 3.13: Передача name, n_arg и aggregate_class в качестве именованных аргументов устарела. Эти параметры станут позиционными в Python 3.15.

create_window_function(name, num_params, aggregate_class, /)

Создать или удалить пользовательскую агрегирующую функцию окна.

Параметры:

• name (str) – Имя SQL агрегирующей функции окна для создания или удаления.
• num_params (int) – Количество аргументов, которые может принимать SQL агрегирующая функция окна. Если -1, она может принимать любое количество аргументов.

• aggregate_class (класс | None) –

  Класс, который должен реализовывать следующие методы:

  • step(): Добавить строку в текущее окно.
  • value(): Вернуть текущее значение агрегата.
  • inverse(): Удалить строку из текущего окна.
  • finalize(): Вернуть конечный результат агрегата как тип, поддерживаемый SQLite.

  Количество аргументов, которые должны принимать методы step() и value(), контролируется параметром num_params.

  Установите в None для удаления существующей SQL агрегирующей функции окна.

Исключения:

NotSupportedError – Если используется с версией SQLite, младше 3.25.0, которая не поддерживает агрегирующие функции окна.

Добавлена в версии 3.11.

Пример:

# Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc
class WindowSumInt:
    def __init__(self):
        self.count = 0

    def step(self, value):
        """Add a row to the current window."""
        self.count += value

    def value(self):
        """Return the current value of the aggregate."""
        return self.count

    def inverse(self, value):
        """Remove a row from the current window."""
        self.count -= value

    def finalize(self):
        """Return the final value of the aggregate.

        Any clean-up actions should be placed here.
        """
        return self.count


con = sqlite3.connect(":memory:")
cur = con.execute("CREATE TABLE test(x, y)")
values = [
    ("a", 4),
    ("b", 5),
    ("c", 3),
    ("d", 8),
    ("e", 1),
]
cur.executemany("INSERT INTO test VALUES(?, ?)", values)
con.create_window_function("sumint", 1, WindowSumInt)
cur.execute("""
    SELECT x, sumint(y) OVER (
        ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sum_y
    FROM test ORDER BY x
""")
print(cur.fetchall())
con.close()

create_collation(name, callable, /)

Создаёт сортировку name, используя функцию сортировки callable. callable получает два аргумента типа string, и должен возвращать integer:

• 1 если первый аргумент больше второго
• -1 если первый аргумент меньше второго
• 0 если аргументы равны

Следующий пример демонстрирует сортировку в обратном порядке:

def collate_reverse(string1, string2):
    if string1 == string2:
        return 0
    elif string1 < string2:
        return 1
    else:
        return -1

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.execute("CREATE TABLE test(x)")
cur.executemany("INSERT INTO test(x) VALUES(?)", [("a",), ("b",)])
cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse")
for row in cur:
    print(row)
con.close()

Удалить функцию сортировки, установив callable в None.

Изменено в версии 3.11: Имя сортировки может содержать любые символы Юникода. Раньше допускались только символы ASCII.

interrupt()

Вызовите этот метод из другого потока, чтобы прервать любые запросы, которые могут выполняться на соединении. Прерванные запросы будут генерировать исключение OperationalError.

set_authorizer(authorizer_callback)

Зарегистрировать функцию authorizer_callback для вызова при каждой попытке доступа к столбцу таблицы в базе данных. Функция должна возвращать одно из значений SQLITE_OK, SQLITE_DENY или SQLITE_IGNORE для указания, как обращаться к столбцу с помощью библиотеки SQLite.

Первый аргумент функции обозначает тип операции. Второй и третий аргументы будут аргументами или None в зависимости от первого аргумента. Четвёртый аргумент — имя базы данных («main», «temp» и т. д.) применимо. Пятый аргумент — имя самого внутреннего триггера или представления, ответственного за попытку доступа, или None если эта попытка доступа происходит непосредственно из входного SQL-кода.

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

Передача None в качестве authorizer_callback отключит авторизатор.

Изменено в версии 3.11: Добавлена возможность отключения авторизатора с помощью None.

Изменено в версии 3.13: Передача authorizer_callback в качестве ключевого аргумента устарела. Параметр станет позиционным только в Python 3.15.

set_progress_handler(progress_handler, n)

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

Если вы хотите очистить любой ранее установленный обработчик прогресса, вызовите метод с None для progress_handler.

Возвращение ненулевого значения из функции-обработчика завершит выполнение текущего запроса и вызовет исключение DatabaseError.

Изменено в версии 3.13: Передача progress_handler в качестве ключевого аргумента устарела. Параметр станет позиционным только в Python 3.15.

set_trace_callback(trace_callback)

Зарегистрировать функцию trace_callback для вызова для каждой SQL-команды, которая фактически выполняется бэкэндом SQLite.

Единственным аргументом, передаваемым в функцию-обработчик, является команда (как str), которая выполняется. Значение возврата функции-обработчика игнорируется. Обратите внимание, что бэкэнд выполняет не только запросы, передаваемые методам Cursor.execute(). Другие источники включают управление транзакциями модуля sqlite3 и выполнение триггеров, определённых в текущей базе данных.

Передача None в качестве trace_callback отключит обработчик трассировки.

Примечание

Исключения, поднятые в обработчике трассировки, не распространяются. Для отладки используйте enable_callback_tracebacks(), чтобы включить вывод отладочной информации об исключениях, поднятых в обработчике трассировки.

Добавлена в версии 3.3.

Изменено в версии 3.13: Передача trace_callback в качестве ключевого аргумента устарела. Параметр станет позиционным только в Python 3.15.

enable_load_extension(enabled, /)

Включает возможность для движка SQLite загружать расширения SQLite из общих библиотек, если enabled True; в противном случае, запрещает загрузку расширений SQLite. Расширения SQLite могут определять новые функции, агрегаты или полностью новые реализации виртуальных таблиц. Одним из известных расширений является расширение полнотекстового поиска, поставляемое вместе с SQLite.

Примечание

Модуль sqlite3 по умолчанию не построен с поддержкой загружаемых расширений, поскольку на некоторых платформах (особенно macOS) библиотеки SQLite скомпилированы без этой функции. Для получения поддержки загружаемых расширений, вы должны передать опцию --enable-loadable-sqlite-extensions в configure.

Вызывает событие аудита sqlite3.enable_load_extension с аргументами connection, enabled.

Добавлена в версии 3.2.

Изменено в версии 3.10: Добавлено событие аудита sqlite3.enable_load_extension.

con.enable_load_extension(True)

# Load the fulltext search extension
con.execute("select load_extension('./fts3.so')")

# alternatively you can load the extension using an API call:
# con.load_extension("./fts3.so")

# disable extension loading again
con.enable_load_extension(False)

# example from SQLite wiki
con.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)")
con.executescript("""
    INSERT INTO recipe (name, ingredients) VALUES('broccoli stew', 'broccoli peppers cheese tomatoes');
    INSERT INTO recipe (name, ingredients) VALUES('pumpkin stew', 'pumpkin onions garlic celery');
    INSERT INTO recipe (name, ingredients) VALUES('broccoli pie', 'broccoli cheese onions flour');
    INSERT INTO recipe (name, ingredients) VALUES('pumpkin pie', 'pumpkin sugar flour butter');
    """)
for row in con.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"):
    print(row)

load_extension(path, /, *, entrypoint=None)

Загрузить расширение SQLite из разделяемой библиотеки. Включите загрузку расширений с помощью enable_load_extension() перед вызовом этого метода.

Параметры:

• path (строка) – Путь к расширению SQLite.
• entrypoint (строка | None) – Имя точки входа. Если None (по умолчанию), SQLite определит имя точки входа; см. документацию SQLite Загрузка расширения для получения подробностей.

Вызывает событие аудита аудита sqlite3.load_extension с аргументами connection, path.

Добавлен в версии 3.2.

Изменено в версии 3.10: Добавлено событие аудита sqlite3.load_extension.

Изменено в версии 3.12: Добавлен параметр entrypoint.

iterdump(*, filter=None)

Возвращает итератор для вывода базы данных в виде исходного кода SQL. Полезно для сохранения базы данных в оперативной памяти для последующего восстановления. Аналогично команде .dump в оболочке sqlite3.

Параметры:

filter (строка | None) – Необязательный шаблон LIKE для объектов базы данных для вывода, например, prefix_%. Если None (по умолчанию), будут включены все объекты базы данных.

Пример:

# Convert file example.db to SQL dump file dump.sql
con = sqlite3.connect('example.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)
con.close()

См. также

Обработка кодировок текста, отличных от UTF-8

Изменено в версии 3.13: Добавлен параметр filter.

backup(target, *, pages=-1, progress=None, name='main', sleep=0.250)

Создать резервную копию базы данных SQLite.

Работает даже если к базе данных обращаются другие клиенты или одновременно одной и той же подключением.

Параметры:

• target (Подключение) – Соединение базы данных для сохранения резервной копии.
• pages (целое число) – Количество страниц для копирования за один раз. Если равно или меньше 0, вся база данных копируется за один шаг. По умолчанию -1.
• progress (обратный вызов | None) – Если задано вызываемая функция, оно вызывается с тремя целочисленными аргументами для каждой итерации резервного копирования: статус последней итерации, оставшееся количество страниц, которые еще предстоит скопировать, и общее количество страниц. По умолчанию None.
• name (строка) – Имя базы данных для резервного копирования. Или "main" (по умолчанию) для основной базы данных, "temp" для временной базы данных или имя настраиваемой базы данных, присоединённой с помощью ATTACH DATABASE SQL-выражения.
• sleep (вещественное число) – Количество секунд, которые нужно подождать между последовательными попытками резервного копирования оставшихся страниц.

Пример 1, скопировать существующую базу данных в другую:

def progress(status, remaining, total):
    print(f'Copied {total-remaining} of {total} pages...')

src = sqlite3.connect('example.db')
dst = sqlite3.connect('backup.db')
with dst:
    src.backup(dst, pages=1, progress=progress)
dst.close()
src.close()

Пример 2, скопировать существующую базу данных во временную копию:

src = sqlite3.connect('example.db')
dst = sqlite3.connect(':memory:')
src.backup(dst)
dst.close()
src.close()

Добавлен в версии 3.7.

См. также

Обработка кодировок текста, отличных от UTF-8

getlimit(category, /)

Получить ограничение выполнения подключения.

Параметры:

category (целое число) – Категория ограничения SQLite для запроса.

Тип возвращаемого значения:

целое число

Возбуждает:

ProgrammingError – Если category не распознан базовой библиотекой SQLite.

Пример, запрос максимальной длины SQL-выражения для Connection con (значение по умолчанию 1000000000):

>>> con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)
1000000000

Добавлен в версии 3.11.

setlimit(category, limit, /)

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

Параметры:

• category (целое число) – Категория ограничения SQLite для установки.
• limit (целое число) – Значение нового ограничения. Если отрицательно, текущее ограничение остается неизменным.

Тип возвращаемого значения:

целое число

Возбуждает:

ProgrammingError – Если category не распознан базовой библиотекой SQLite.

Пример, ограничение количества присоединенных баз данных до 1 для Connection con (значение по умолчанию 10):

>>> con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1)
10
>>> con.getlimit(sqlite3.SQLITE_LIMIT_ATTACHED)
1

Добавлен в версии 3.11.

getconfig(op, /)

Запросить булево значение конфигурации подключения.

Параметры:

op (целое число) – код SQLITE_DBCONFIG.

Тип возвращаемого значения:

логическое значение

Добавлен в версии 3.12.

setconfig(op, enable=True, /)

Установить булево значение конфигурации подключения.

Параметры:

• op (целое число) – код SQLITE_DBCONFIG.
• enable (логическое значение) – True если параметр конфигурации следует включить (по умолчанию); False если его следует выключить.

Добавлен в версии 3.12.

serialize(*, name='main')

Сериализация базы данных в объект bytes. Для обычного файла базы данных на диске сериализация — это просто копия файла на диске. Для базы данных в памяти или «временной» базы данных сериализация — это та же последовательность байтов, которая была бы записана на диск, если бы эта база данных была резервирована на диск.

Параметры:

name (строка) — Имя базы данных для сериализации. По умолчанию "main".

Тип возвращаемого значения:

байты

Примечание

Этот метод доступен только если у базовой библиотеки SQLite есть API сериализации.

Добавлен в версии 3.11.

deserialize(data, /, *, name='main')

Десериализация базы данных serialized в Connection. Этот метод вызывает разрыв соединения базы данных с базой данных name и повторное открытие name в качестве базы данных в памяти, основанной на сериализованных данных в data.

Параметры:

• data (байты) — Сериализованная база данных.
• name (строка) — Имя базы данных для десериализации. По умолчанию "main".

Возможные исключения:

• OperationalError — Если соединение базы данных в настоящее время участвует в транзакции чтения или операции резервного копирования.
• DatabaseError — Если data не содержит действительной базы данных SQLite.
• OverflowError — Если len(data) больше, чем 2**63 - 1.

Примечание

Этот метод доступен только если у базовой библиотеки SQLite есть API десериализации.

Добавлен в версии 3.11.

autocommit

Этот атрибут управляет поведением транзакции, совместимым с PEP 249. Атрибут autocommit имеет три допустимых значения:

• False: Выбор поведения транзакции, совместимого с PEP 249, подразумевая, что sqlite3 гарантирует, что транзакция всегда открыта. Используйте commit() и rollback() для закрытия транзакций.

  Это рекомендуемое значение для autocommit.

• True: Использование режима автоподтверждения SQLite автоподтверждения. commit() и rollback() не имеют эффекта в этом режиме.

• LEGACY_TRANSACTION_CONTROL: Управление транзакциями до Python 3.12 (не совместимое с PEP 249). См. isolation_level для получения дополнительной информации.

  В настоящее время это значение по умолчанию для autocommit.

Изменение autocommit на False откроет новую транзакцию, а изменение на True подтвердит любую ожидающую транзакцию.

См. Управление транзакциями через атрибут autocommit для получения дополнительной информации.

Примечание

Атрибут isolation_level не имеет эффекта, если autocommit равен LEGACY_TRANSACTION_CONTROL.

Добавлен в версии 3.12.

in_transaction

Этот атрибут только для чтения соответствует режиму автоподтверждения SQLite на низком уровне автоподтверждения.

Возвращает True, если активна транзакция (есть несохраненные изменения), False в противном случае.

Добавлен в версии 3.2.

isolation_level

Управляет режимом обработки транзакций sqlite3. Если установлено значение None, транзакции никогда не открываются неявно. Если установлено одно из "DEFERRED", "IMMEDIATE", или "EXCLUSIVE", соответствующие поведению транзакций SQLite SQLite, выполняется управление транзакцией неявно.

Если не переопределяется параметром isolation_level в connect(), значение по умолчанию — "", что является псевдонимом для "DEFERRED".

Примечание

Использование autocommit для управления обработкой транзакций рекомендуется вместо isolation_level. isolation_level не имеет эффекта, если autocommit установлено в LEGACY_TRANSACTION_CONTROL (значение по умолчанию).

row_factory

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

См. Как создать и использовать фабрики строк для получения дополнительной информации.

text_factory

Функция, которая принимает параметр bytes и возвращает текстовое представление. Функция вызывается для значений SQLite с типом данных TEXT. По умолчанию этот атрибут установлен в str.

См. Как обработать кодировки текста, отличные от UTF-8 для получения дополнительной информации.

total_changes

Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия соединения базы данных.

Объекты курсора

Объект Cursor представляет собой курсор базы данных, который используется для выполнения SQL-запросов и управления контекстом операции извлечения. Курсоры создаются с помощью Connection.cursor() или с помощью любого из сокращенных методов соединения.

Объекты курсора являются итераторами, что означает, что если вы execute() запрос SELECT, вы можете просто перебрать курсор, чтобы извлечь результирующие строки:

for row in cur.execute("SELECT t FROM data"):
    print(row)

class sqlite3.Cursor

Объект Cursor имеет следующие атрибуты и методы.

execute(sql, parameters=(), /)

Выполняет единственное SQL-определение, необязательно связывая значения Python с помощью заменителей.

Параметры:

• sql (строка) – Единственное SQL-определение.
• parameters (dict | последовательность) – Значения Python для привязки к заменителям в sql. Список, если используются именованные замены. Последовательность, если используются незамещённые параметры. Смотрите Как использовать замены для привязки значений в SQL-запросах.

Возможные исключения:

Ошибка программирования – Если sql содержит более одного SQL-определения.

Если autocommit равен LEGACY_TRANSACTION_CONTROL, isolation_level не равен None, sql является INSERT, UPDATE, DELETE, или REPLACE оператором, и нет открытой транзакции, перед выполнением sql транзакция неявно открывается.

Устаревшее с версии 3.12, будет удалено в версии 3.14: DeprecationWarning генерируется, если используются именованные замены и parameters является последовательностью вместо dict. Начиная с Python 3.14, вместо этого будет генерироваться ProgrammingError.

Используйте executescript() для выполнения нескольких SQL-определений.

executemany(sql, parameters, /)

Для каждого элемента в parameters, повторяет выполнение параметризованного SQL-определения DML sql.

Использует ту же неявно управляемую транзакциями обработку, что и execute().

Параметры:

• sql (строка) – Одиночное SQL-определение DML.
• parameters (итерируемый объект) – Итерируемый объект параметров для привязки к заменителям в sql. Смотрите Как использовать замены для привязки значений в SQL-запросах.

Возможные исключения:

Ошибка программирования – Если sql содержит более одного SQL-определения или не является оператором DML.

Пример:

rows = [
    ("row1",),
    ("row2",),
]
# cur is an sqlite3.Cursor object
cur.executemany("INSERT INTO data VALUES(?)", rows)

Примечание

Любые результирующие строки отбрасываются, включая DML-определения с CLAUSES RETURNING.

Устаревшее с версии 3.12, будет удалено в версии 3.14: DeprecationWarning генерируется, если используются именованные замены и элементы в parameters являются последовательностями вместо dict. Начиная с Python 3.14, вместо этого будет генерироваться ProgrammingError.

executescript(sql_script, /)

Выполняет SQL-определения в sql_script. Если autocommit равен LEGACY_TRANSACTION_CONTROL и есть ожидающая транзакция, сначала выполняется неявное COMMIT определение. Никакого другого неявного управления транзакциями не выполняется; любое управление транзакциями должно быть добавлено в sql_script.

sql_script должно быть string.

Пример:

# cur is an sqlite3.Cursor object
cur.executescript("""
    BEGIN;
    CREATE TABLE person(firstname, lastname, age);
    CREATE TABLE book(title, author, published);
    CREATE TABLE publisher(name, address);
    COMMIT;
""")

fetchone()

Если row_factory равно None, возвращает следующую строку набора результатов запроса как tuple. В противном случае, передает её в фабрику строк и возвращает её результат. Возвращает None если больше данных недоступно.

fetchmany(size=cursor.arraysize)

Возвращает следующий набор строк результата запроса как list. Возвращает пустой список, если больше строк недоступно.

Количество строк для извлечения за один вызов задаётся параметром size. Если size не указан, arraysize определяет количество извлекаемых строк. Если строк меньше, чем size, возвращается столько строк, сколько доступно.

Обратите внимание на аспекты производительности, связанные с параметром size. Для оптимальной производительности обычно лучше использовать атрибут arraysize. Если параметр size используется, то желательно, чтобы его значение сохранялось от одного вызова fetchmany() к следующему.

fetchall()

Возвращает все (оставшиеся) строки результата запроса как list. Возвращает пустой список, если строки недоступны. Обратите внимание, что атрибут arraysize может влиять на производительность этой операции.

close()

Закрыть курсор сейчас (а не в момент, когда __del__ вызывается).

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

setinputsizes(sizes, /)

Требуется DB-API. Не делает ничего в sqlite3.

setoutputsize(size, column=None, /)

Требуется DB-API. Не делает ничего в sqlite3.

arraysize

Атрибут для чтения/записи, который управляет количеством строк, возвращаемых fetchmany(). Значение по умолчанию равно 1, что означает, что за один вызов будет извлечена одна строка.

connection

Только для чтения атрибут, предоставляющий подключение к базе данных SQLite Connection, принадлежащее курсору. У объекта Cursor, созданного вызовом con.cursor(), будет атрибут connection, который ссылается на con:

>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> cur.connection == con
True
>>> con.close()

description

Только для чтения атрибут, предоставляющий имена столбцов последнего запроса. Для совместимости с Python DB API он возвращает 7-кортеж для каждого столбца, где последние шесть элементов каждого кортежа являются None.

Он устанавливается для SELECT операторов без соответствующих строк.

lastrowid

Только для чтения атрибут, который предоставляет идентификатор строки последней вставленной строки. Он обновляется только после успешных INSERT или REPLACE операторов, использующих метод execute(). Для других операторов, после executemany() или executescript(), или если вставка не удалась, значение lastrowid остается неизменным. Начальное значение lastrowid равно None.

Примечание

Вставки в WITHOUT ROWID таблицы не записываются.

Изменено в версии 3.6: Добавлена поддержка оператора REPLACE.

rowcount

Только для чтения атрибут, который предоставляет количество изменённых строк для INSERT, UPDATE, DELETE, и REPLACE операторов; имеет значение -1 для других операторов, включая запросы CTE. Он обновляется только методами execute() и executemany() после завершения оператора. Это означает, что для обновления rowcount необходимо извлечь все полученные строки.

row_factory

Управление тем, как извлечённая из этого Cursor строка будет представлена. Если None, строка представляется как tuple. Может быть установлено на включённый sqlite3.Row; или на вызываемый объект, который принимает два аргумента, объект Cursor и значения строки, и возвращает пользовательский объект, представляющий строку SQLite.

По умолчанию используется значение, установленное для Connection.row_factory при создании Cursor. Присвоение этому атрибуту не влияет на Connection.row_factory родительского подключения.

См. Как создать и использовать фабрики строк для получения более подробной информации.

Объекты строк

class sqlite3.Row

Экземпляр Row служит высокооптимизированной row_factory для объектов Connection. Он поддерживает итерацию, проверку на равенство, len() и доступ к данным по имени и индексу столбца (как в словаре).

Два объекта Row равны, если у них идентичны имена и значения столбцов.

См. Как создать и использовать фабрики строк для получения более подробной информации.

keys()

Возвращает list имён столбцов в виде strings. Сразу после запроса это первый элемент каждого кортежа в Cursor.description.

Изменено в версии 3.5: Добавлена поддержка срезов.

Объекты BLOB

class sqlite3.Blob

Добавлена в версии 3.11.

Объект Blob — это объект, похожий на файл, который может читать и записывать данные в SQLite BLOB. Используйте len(blob) для получения размера (числа байтов) BLOB. Используйте индексы и срезы для прямого доступа к данным BLOB.

Используйте Blob как менеджер контекста, чтобы гарантировать закрытие дескриптора BLOB после использования.

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE test(blob_col blob)")
con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))")

# Write to our blob, using two write operations:
with con.blobopen("test", "blob_col", 1) as blob:
    blob.write(b"hello, ")
    blob.write(b"world.")
    # Modify the first and last bytes of our blob
    blob[0] = ord("H")
    blob[-1] = ord("!")

# Read the contents of our blob
with con.blobopen("test", "blob_col", 1) as blob:
    greeting = blob.read()

print(greeting)  # outputs "b'Hello, world!'"
con.close()

close()

Закрыть BLOB.

BLOB больше недоступен. Будет возбуждено исключение Error (или подкласс), если любая последующая операция будет предпринята с BLOB.

read(length=-1, /)

Прочитать length байтов данных из BLOB в текущей позиции смещения. Если достигнут конец BLOB, будут возвращены данные до EOF. Если length не указан или отрицателен, read() будет читать до конца BLOB.

write(data, /)

Записать data в BLOB в текущем смещении. Эта функция не может изменить длину BLOB. Запись за пределами конца BLOB вызовет ValueError.

tell()

Возвращает текущую позицию доступа в BLOB.

seek(offset, origin=os.SEEK_SET, /)

Установить текущую позицию доступа в BLOB на offset. Аргумент origin по умолчанию равен os.SEEK_SET (абсолютная позиция в BLOB). Другие значения origin — os.SEEK_CUR (смещение относительно текущей позиции) и os.SEEK_END (смещение относительно конца BLOB).

Объекты PrepareProtocol

class sqlite3.PrepareProtocol

Единственная цель типа PrepareProtocol — действовать как протокол адаптации в стиле PEP 246 для объектов, которые могут адаптироваться к родным типам SQLite.

Исключения

Иерархия исключений определяется DB-API 2.0 (PEP 249).

exception sqlite3.Warning

Это исключение в настоящее время не генерируется модулем sqlite3, но может генерироваться приложениями, использующими sqlite3, например, если пользовательская функция усекает данные при вставке. Warning является подклассом Exception.

exception sqlite3.Error

Базовый класс других исключений в этом модуле. Используйте его для перехвата всех ошибок с помощью одного оператора except. Error является подклассом Exception.

Если исключение возникло внутри библиотеки SQLite, к исключению добавляются следующие два атрибута:

sqlite_errorcode

Числовой код ошибки из SQLite API

Добавлен в версии 3.11.

sqlite_errorname

Символическое имя числового кода ошибки из SQLite API

Добавлен в версии 3.11.

exception sqlite3.InterfaceError

Исключение, генерируемое при неправильном использовании низкоуровневого C API SQLite. Другими словами, если это исключение генерируется, вероятно, это указывает на ошибку в модуле sqlite3. InterfaceError является подклассом Error.

exception sqlite3.DatabaseError

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

exception sqlite3.DataError

Исключение, генерируемое при ошибках, связанных с обработанными данными, таких как числовые значения, выходящие за пределы диапазона, и строки, которые слишком длинные. DataError является подклассом DatabaseError.

exception sqlite3.OperationalError

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

exception sqlite3.IntegrityError

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

exception sqlite3.InternalError

Исключение, генерируемое, когда SQLite обнаруживает внутреннюю ошибку. Если это исключение генерируется, это может указывать на проблему с библиотекой выполнения SQLite. InternalError является подклассом DatabaseError.

exception sqlite3.ProgrammingError

Исключение, генерируемое при ошибках программирования API sqlite3, например, при предоставлении неправильного количества связываний для запроса или попытке выполнить операцию над закрытым Connection. ProgrammingError является подклассом DatabaseError.

exception sqlite3.NotSupportedError

Исключение, генерируемое в случае, если метод или API базы данных не поддерживается основной библиотекой SQLite. Например, установка deterministic в True в create_function(), если основная библиотека SQLite не поддерживает детерминированные функции. NotSupportedError является подклассом DatabaseError.

Типы SQLite и Python

SQLite нативно поддерживает следующие типы: NULL, INTEGER, REAL, TEXT, BLOB.

Следующие типы Python могут быть отправлены в SQLite без проблем:

Вот как типы SQLite преобразуются в типы Python по умолчанию:

Система типов модуля sqlite3 расширяется двумя способами: вы можете хранить дополнительные типы Python в базе данных SQLite с помощью адаптеров объектов, и вы можете позволить модулю sqlite3 преобразовывать типы SQLite в типы Python с помощью конвертеров.

Конвертеры и адаптеры по умолчанию (устарело)

Устаревшие адаптеры и конвертеры по умолчанию включают:

• Адаптер для объектов datetime.date в strings в формате ISO 8601.
• Адаптер для объектов datetime.datetime в строки в формате ISO 8601.
• Конвертер для типов “date”, объявленных в SQLite, в объекты datetime.date.
• Конвертер для типов “timestamp”, объявленных в SQLite, в объекты datetime.datetime. Дробные части будут усечены до 6 цифр (точность в микросекундах).

Командная строка

Модуль sqlite3 можно вызвать как скрипт, используя переключатель интерпретатора -m, чтобы обеспечить простой оболочку SQLite. Подпись аргумента следующая:

python -m sqlite3 [-h] [-v] [filename] [sql]

Для выхода из оболочки введите .quit или нажмите CTRL-D.

-h, --help

Вывести справку CLI.

-v, --version

Вывести версию подлежащей библиотеки SQLite.

Как использовать плейсхолдеры для привязки значений в запросах SQL

Операции SQL обычно требуют использования значений из переменных Python. Однако будьте осторожны при использовании строковых операций Python для сборки запросов, так как они уязвимы для атак SQL-инъекции. Например, злоумышленник может просто закрыть одинарную кавычку и ввести OR TRUE для выбора всех строк:

>>> # Never do this -- insecure!
>>> symbol = input()
' OR TRUE; --
>>> sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
>>> print(sql)
SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
>>> cur.execute(sql)

Вместо этого используйте подстановку параметров DB-API. Для вставки переменной в строку запроса используйте плейсхолдер в строке и подставьте фактические значения в запрос, предоставив их как tuple значений во второй аргумент метода курсора execute().

Запрос SQL может использовать один из двух типов плейсхолдеров: вопросительные знаки (стиль qmark) или именованные плейсхолдеры (именованный стиль). Для стиля qmark параметры должны быть последовательностью длиной, которая должна соответствовать количеству плейсхолдеров, или будет поднята ошибка ProgrammingError. Для именованного стиля параметры должны быть экземпляром dict (или подкласса), который должен содержать ключи для всех именованных параметров; любые дополнительные элементы игнорируются. Вот пример обоих стилей:

con = sqlite3.connect(":memory:")
cur = con.execute("CREATE TABLE lang(name, first_appeared)")

# This is the named style used with executemany():
data = (
    {"name": "C", "year": 1972},
    {"name": "Fortran", "year": 1957},
    {"name": "Python", "year": 1991},
    {"name": "Go", "year": 2009},
)
cur.executemany("INSERT INTO lang VALUES(:name, :year)", data)

# This is the qmark style used in a SELECT query:
params = (1972,)
cur.execute("SELECT * FROM lang WHERE first_appeared = ?", params)
print(cur.fetchall())
con.close()

Как адаптировать пользовательские типы Python к значениям SQLite

SQLite поддерживает только ограниченный набор типов данных в родном виде. Чтобы сохранить пользовательские типы Python в базах данных SQLite, адаптируйте их к одному из типов Python, которые SQLite понимает в родном виде.

Существует два способа адаптации объектов Python к типам SQLite: позволить объекту адаптироваться самому или использовать вызываемый адаптер. Последний будет иметь приоритет перед первым. Для библиотеки, экспортирующей пользовательский тип, может иметь смысл позволить этому типу адаптироваться самому. Как разработчику приложения, может быть разумнее взять прямой контроль, зарегистрировав пользовательские функции адаптеров.

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return f"{self.x};{self.y}"

con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute("SELECT ?", (Point(4.0, -3.2),))
print(cur.fetchone()[0])
con.close()

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

def adapt_point(point):
    return f"{point.x};{point.y}"

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute("SELECT ?", (Point(1.0, 2.5),))
print(cur.fetchone()[0])
con.close()

Как преобразовать значения SQLite в пользовательские типы Python

Написание адаптера позволяет преобразовывать из пользовательских типов Python в значения SQLite. Чтобы преобразовать из значений SQLite в пользовательские типы Python, мы используем конвертеры.

Вернемся к классу Point. Мы сохранили x и y координаты, разделенные точкой с запятой, как строки в SQLite.

Сначала определим функцию преобразования, которая принимает строку в качестве параметра и строит объект Point из неё.

def convert_point(s):
    x, y = map(float, s.split(b";"))
    return Point(x, y)

Теперь нам нужно сказать sqlite3, когда он должен преобразовать данное значение SQLite. Это делается при подключении к базе данных с помощью параметра detect_types connect(). Существует три варианта:

• Неявный: установить detect_types в PARSE_DECLTYPES
• Явный: установить detect_types в PARSE_COLNAMES
• Оба: установить detect_types в sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES. Имена столбцов имеют приоритет над объявленными типами.

Следующий пример иллюстрирует неявный и явный подходы:

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return f"Point({self.x}, {self.y})"

def adapt_point(point):
    return f"{point.x};{point.y}"

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

# Register the adapter and converter
sqlite3.register_adapter(Point, adapt_point)
sqlite3.register_converter("point", convert_point)

# 1) Parse using declared types
p = Point(4.0, -3.2)
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.execute("CREATE TABLE test(p point)")

cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
cur.execute("SELECT p FROM test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

# 2) Parse using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.execute("CREATE TABLE test(p)")

cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
cur.execute('SELECT p AS "p [point]" FROM test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

Рецепты адаптеров и конвертеров

В этом разделе показаны рецепты для распространённых адаптеров и конвертеров.

import datetime
import sqlite3

def adapt_date_iso(val):
    """Adapt datetime.date to ISO 8601 date."""
    return val.isoformat()

def adapt_datetime_iso(val):
    """Adapt datetime.datetime to timezone-naive ISO 8601 date."""
    return val.isoformat()

def adapt_datetime_epoch(val):
    """Adapt datetime.datetime to Unix timestamp."""
    return int(val.timestamp())

sqlite3.register_adapter(datetime.date, adapt_date_iso)
sqlite3.register_adapter(datetime.datetime, adapt_datetime_iso)
sqlite3.register_adapter(datetime.datetime, adapt_datetime_epoch)

def convert_date(val):
    """Convert ISO 8601 date to datetime.date object."""
    return datetime.date.fromisoformat(val.decode())

def convert_datetime(val):
    """Convert ISO 8601 datetime to datetime.datetime object."""
    return datetime.datetime.fromisoformat(val.decode())

def convert_timestamp(val):
    """Convert Unix epoch timestamp to datetime.datetime object."""
    return datetime.datetime.fromtimestamp(int(val))

sqlite3.register_converter("date", convert_date)
sqlite3.register_converter("datetime", convert_datetime)
sqlite3.register_converter("timestamp", convert_timestamp)

Как использовать методы сокращённого подключения

Используя методы execute(), executemany() и executescript() класса Connection, ваш код можно написать более лаконично, так как вам не придётся создавать (часто излишние) объекты Cursor явно. Вместо этого объекты Cursor создаются неявно, и эти сокращённые методы возвращают объекты курсора. Таким образом, вы можете выполнить запрос SELECT и перебрать его напрямую, используя только один вызов объекта Connection.

# Create and fill the table.
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE lang(name, first_appeared)")
data = [
    ("C++", 1985),
    ("Objective-C", 1984),
]
con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)", data)

# Print the table contents
for row in con.execute("SELECT name, first_appeared FROM lang"):
    print(row)

print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows")

# close() is not a shortcut method and it's not called automatically;
# the connection object should be closed manually
con.close()

Как использовать менеджер контекста подключения

Объект Connection может использоваться как менеджер контекста, который автоматически коммитит или отменяет открытые транзакции при выходе из тела менеджера контекста. Если тело инструкции with завершается без исключений, транзакция коммитится. Если этот коммит терпит неудачу, или если тело инструкции with вызывает необработанное исключение, транзакция отменяется. Если autocommit равно False, новая транзакция неявно открывается после коммита или отмены.

Если открытой транзакции нет при выходе из тела инструкции with или если autocommit равно True, менеджер контекста ничего не делает.

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))

# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
try:
    with con:
        con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
except sqlite3.IntegrityError:
    print("couldn't add Python twice")

# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually
con.close()

Как работать с URI SQLite

Некоторые полезные трюки с URI включают:

• Открыть базу данных в режиме только для чтения:

>>> con = sqlite3.connect("file:tutorial.db?mode=ro", uri=True)
>>> con.execute("CREATE TABLE readonly(data)")
Traceback (most recent call last):
OperationalError: attempt to write a readonly database
>>> con.close()

• Не неявно создавать новый файл базы данных, если он ещё не существует; вызовет OperationalError, если не удастся создать новый файл:

>>> con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
Traceback (most recent call last):
OperationalError: unable to open database file

• Создать общую именованную базу данных в памяти:

db = "file:mem1?mode=memory&cache=shared"
con1 = sqlite3.connect(db, uri=True)
con2 = sqlite3.connect(db, uri=True)
with con1:
    con1.execute("CREATE TABLE shared(data)")
    con1.execute("INSERT INTO shared VALUES(28)")
res = con2.execute("SELECT data FROM shared")
assert res.fetchone() == (28,)

con1.close()
con2.close()

Дополнительную информацию об этой функции, включая список параметров, можно найти в документации URI SQLite.

Как создавать и использовать фабрики строк

По умолчанию, sqlite3 представляет каждую строку в виде tuple. Если tuple не соответствует вашим потребностям, вы можете использовать класс sqlite3.Row или пользовательскую фабрику row_factory.

Хотя row_factory существует как атрибут и в Cursor, и в Connection, рекомендуется установить Connection.row_factory, чтобы все курсоры, созданные из соединения, использовали одну и ту же фабрику строк.

Row предоставляет индексированный и регистронезависимый именованный доступ к столбцам с минимальной нагрузкой на память и влиянием на производительность по сравнению с tuple. Чтобы использовать Row в качестве фабрики строк, назначьте её атрибуту row_factory:

>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = sqlite3.Row

Теперь запросы возвращают объекты Row:

>>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius")
>>> row = res.fetchone()
>>> row.keys()
['name', 'radius']
>>> row[0]         # Access by index.
'Earth'
>>> row["name"]    # Access by name.
'Earth'
>>> row["RADIUS"]  # Column names are case-insensitive.
6378
>>> con.close()

Вы можете создать пользовательскую фабрику row_factory, которая возвращает каждую строку в виде dict со значениями, сопоставленными с именами столбцов:

def dict_factory(cursor, row):
    fields = [column[0] for column in cursor.description]
    return {key: value for key, value in zip(fields, row)}

Используя её, запросы теперь возвращают dict вместо tuple:

>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = dict_factory
>>> for row in con.execute("SELECT 1 AS a, 2 AS b"):
...     print(row)
{'a': 1, 'b': 2}
>>> con.close()

Следующая фабрика строк возвращает именованную кортеж:

from collections import namedtuple

def namedtuple_factory(cursor, row):
    fields = [column[0] for column in cursor.description]
    cls = namedtuple("Row", fields)
    return cls._make(row)

namedtuple_factory() можно использовать следующим образом:

>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = namedtuple_factory
>>> cur = con.execute("SELECT 1 AS a, 2 AS b")
>>> row = cur.fetchone()
>>> row
Row(a=1, b=2)
>>> row[0]  # Indexed access.
1
>>> row.b   # Attribute access.
2
>>> con.close()

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

Как обрабатывать кодировки текста, отличные от UTF-8

По умолчанию, sqlite3 использует str для адаптации значений SQLite с типом данных TEXT. Это хорошо работает для текста в кодировке UTF-8, но может не сработать для других кодировок и некорректного UTF-8. Вы можете использовать пользовательскую фабрику text_factory для обработки таких случаев.

Из-за гибкости типов SQLite TEXT часто встречаются табличные столбцы с типом данных, содержащими кодировки, отличные от UTF-8, или даже произвольные данные. Предположим, у нас есть база данных с текстом в кодировке ISO-8859-2 (Latin-2), например, таблица записей чешско-английского словаря. Предположим, у нас есть экземпляр Connection con подключенного к этой базе данных, мы можем декодировать текст в кодировке Latin-2, используя эту фабрику text_factory:

con.text_factory = lambda data: str(data, encoding="latin2")

Для некорректного UTF-8 или произвольных данных в хранящихся в TEXT столбцах таблицы вы можете использовать следующий метод, заимствованный из Руководства по Unicode:

con.text_factory = lambda data: str(data, errors="surrogateescape")

Управление транзакциями

sqlite3 предоставляет несколько способов управления открытием, закрытием и способом работы транзакций базы данных. Управление транзакциями с помощью атрибута autocommit рекомендуется, в то время как Управление транзакциями с помощью атрибута isolation_level сохраняет поведение до Python 3.12.