`sqlite3.md`

1227 строк · 83.9 КБ · обычная страница · сырой текст · скачать
1> **Источник:** https://python-all.ru/3.9/library/sqlite3.html2>3> «Документация Python на русском» – неофициальный перевод официальной документации Python: версии от 2.6 до 3.16, полнотекстовый поиск, английский оригинал рядом с переводом. Эта Markdown-версия страницы предназначена для работы с LLM: вставьте её в ChatGPT, Claude или Cursor.45---67# [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) – интерфейс DB-API 2.0 для баз данных SQLite89**Исходный код:** [Lib/sqlite3/](https://python-all.ru/src/3.9/Lib/sqlite3)1011---1213SQLite – это библиотека на C, которая предоставляет легковесную дисковую базу данных, не требующую отдельного серверного процесса, и позволяет обращаться к базе данных с помощью нестандартного варианта языка SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также можно создать прототип приложения на SQLite, а затем перенести код на более крупную базу данных, например PostgreSQL или Oracle.1415Модуль sqlite3 был написан Герхардом Харингом. Он предоставляет SQL-интерфейс, совместимый со спецификацией DB-API 2.0, описанной в [**PEP 249**](https://python-all.ru/3.9/library/sqlite3.html).1617Чтобы использовать модуль, начните с создания объекта [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection), представляющего базу данных. В данном примере данные будут храниться в файле `example.db`:1819```python20import sqlite321con = sqlite3.connect('example.db')22```2324Специальное имя пути `:memory:` можно использовать для создания временной базы данных в ОЗУ.2526После установки соединения [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection) создайте объект [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) и вызовите его метод [`execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute) для выполнения SQL-команд:2728```python29cur = con.cursor()3031# Создать таблицу32cur.execute('''CREATE TABLE stocks33               (date text, trans text, symbol text, qty real, price real)''')3435# Вставить строку данных36cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")3738# Сохранить (зафиксировать) изменения39con.commit()4041# Также можно закрыть соединение, если работа с ним завершена.42# Все изменения должны быть зафиксированы, иначе они будут потеряны.43con.close()44```4546Сохранённые данные сохраняются: их можно загрузить в последующем сеансе даже после перезапуска интерпретатора Python:4748```python49import sqlite350con = sqlite3.connect('example.db')51cur = con.cursor()52```5354Чтобы извлечь данные после выполнения оператора SELECT, либо используйте курсор как [итератор](https://python-all.ru/3.9/glossary.html#term-iterator), вызовите метод курсора [`fetchone()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.fetchone) для получения одной подходящей строки, либо вызовите [`fetchall()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.fetchall) для получения списка подходящих строк.5556В этом примере используется форма итератора:5758```python59>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):60        print(row)6162('2006-01-05', 'BUY', 'RHAT', 100, 35.14)63('2006-03-28', 'BUY', 'IBM', 1000, 45.0)64('2006-04-06', 'SELL', 'IBM', 500, 53.0)65('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)66```6768Операции SQL обычно требуют использования значений из переменных Python. Однако остерегайтесь использования строковых операций Python для сборки запросов, так как они уязвимы для атак SQL-инъекций (см. [веб-комикс xkcd](https://python-all.ru/3.9/library/sqlite3.html) для юмористического примера того, что может пойти не так):6970```python71# Никогда не делайте этого – это небезопасно!72symbol = 'RHAT'73cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)74```7576Вместо этого используйте подстановку параметров DB-API. Чтобы вставить переменную в строку запроса, используйте заполнитель в строке и подставьте фактические значения в запрос, передав их как [`tuple`](https://python-all.ru/3.9/library/stdtypes.html#tuple) значений во второй аргумент метода [`execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute) курсора. Оператор SQL может использовать один из двух видов заполнителей: вопросительные знаки (стиль qmark) или именованные заполнители (именованный стиль). Для стиля qmark `parameters` должна быть [последовательностью](https://python-all.ru/3.9/glossary.html#term-sequence). Для именованного стиля это может быть либо [последовательность](https://python-all.ru/3.9/glossary.html#term-sequence), либо экземпляр [`dict`](https://python-all.ru/3.9/library/stdtypes.html#dict). Длина [последовательности](https://python-all.ru/3.9/glossary.html#term-sequence) должна совпадать с количеством заполнителей, иначе возникает исключение [`ProgrammingError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.ProgrammingError). Если передана [`dict`](https://python-all.ru/3.9/library/stdtypes.html#dict), она должна содержать ключи для всех именованных параметров. Любые лишние элементы игнорируются. Вот пример обоих стилей:7778```python79import sqlite38081con = sqlite3.connect(":memory:")82cur = con.cursor()83cur.execute("create table lang (name, first_appeared)")8485# Это стиль qmark:86cur.execute("insert into lang values (?, ?)", ("C", 1972))8788# Стиль qmark, используемый с executemany():89lang_list = [90    ("Fortran", 1957),91    ("Python", 1991),92    ("Go", 2009),93]94cur.executemany("insert into lang values (?, ?)", lang_list)9596# А это именованный стиль:97cur.execute("select * from lang where first_appeared=:year", {"year": 1972})98print(cur.fetchall())99100con.close()101```102103> **См. также**104>105> **[https://www.sqlite.org](https://python-all.ru/3.9/library/sqlite3.html)**106>107> Веб-страница SQLite; документация описывает синтаксис и доступные типы данных поддерживаемого диалекта SQL.108>109> **[https://www.w3schools.com/sql/](https://python-all.ru/3.9/library/sqlite3.html)**110>111> Учебные материалы, справочник и примеры для изучения синтаксиса SQL.112>113> **[**PEP 249**](https://python-all.ru/3.9/library/sqlite3.html) – спецификация API баз данных 2.0**114>115> PEP написан Марком-Андре Лембургом.116117## Функции и константы модуля118119#### `sqlite3.apilevel`120121Строковая константа, указывающая поддерживаемый уровень DB-API. Требуется спецификацией DB-API. Жёстко закодирована как `"2.0"`.122123#### `sqlite3.paramstyle`124125Строковая константа, указывающая тип форматирования маркеров параметров, ожидаемый модулем [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3). Требуется спецификацией DB-API. Жёстко закодирована как `"qmark"`.126127> **Примечание**128>129> Модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) поддерживает оба стиля параметров DB-API – `qmark` и `numeric`, поскольку это поддерживает базовая библиотека SQLite. Однако DB-API не допускает множественных значений для атрибута `paramstyle`.130131#### `sqlite3.version`132133Номер версии этого модуля в виде строки. Это не версия библиотеки SQLite.134135#### `sqlite3.version_info`136137Номер версии этого модуля в виде кортежа целых чисел. Это не версия библиотеки SQLite.138139#### `sqlite3.sqlite_version`140141Номер версии библиотеки SQLite времени выполнения в виде строки.142143#### `sqlite3.sqlite_version_info`144145Номер версии библиотеки SQLite времени выполнения в виде кортежа целых чисел.146147#### `sqlite3.threadsafety`148149Целочисленная константа, требуемая DB-API, указывающая уровень потокобезопасности, поддерживаемый модулем [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3). В настоящее время жёстко задана как `1`, что означает *«Потоки могут совместно использовать модуль, но не подключения».* Однако это не всегда верно. Уровень потокобезопасности, заданный при компиляции библиотеки SQLite, можно проверить следующим запросом:150151```python152import sqlite3153con = sqlite3.connect(":memory:")154con.execute("""155    select * from pragma_compile_options156    where compile_options like 'THREADSAFE=%'157""").fetchall()158```159160Обратите внимание, что [уровни SQLITE\_THREADSAFE](https://python-all.ru/3.9/library/sqlite3.html) не соответствуют уровням DB-API 2.0 `threadsafety`.161162#### `sqlite3.PARSE_DECLTYPES`163164Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.connect).165166Установка этого флага заставляет модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) анализировать объявленный тип для каждого возвращаемого столбца. Он выделит первое слово объявленного типа, т. е. для «integer primary key» выделит «integer», а для «number(10)» выделит «number». Затем для этого столбца он заглянет в словарь преобразователей и использует зарегистрированную там функцию преобразования для этого типа.167168#### `sqlite3.PARSE_COLNAMES`169170Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.connect).171172Установка этого флага заставляет интерфейс SQLite анализировать имя столбца для каждого возвращаемого столбца. Он будет искать строку вида \[mytype\] и затем решать, что «mytype» – это тип столбца. Он попытается найти запись для «mytype» в словаре преобразователей и затем использовать найденную функцию преобразования для возврата значения. Имя столбца, найденное в [`Cursor.description`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.description), не включает тип; например, если в SQL используется что-то вроде `'as "Expiration date [datetime]"'`, то будет выделено всё до первого `'['` для имени столбца и удалён предшествующий пробел: имя столбца будет просто «Expiration date».173174#### `sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])`175176Открывает соединение с файлом базы данных SQLite *database*. По умолчанию возвращает объект [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection), если не указана пользовательская *factory*.177178*database* is a [path-like object](https://python-all.ru/3.9/glossary.html#term-path-like-object) giving the pathname (absolute or relative to the current working directory) of the database file to be opened. You can use `":memory:"` to open a database connection to a database that resides in RAM instead of on disk.179180Когда к базе данных обращаются несколько соединений и один из процессов изменяет базу данных, база данных SQLite блокируется до завершения этой транзакции. Параметр *timeout* задаёт, как долго соединение должно ждать снятия блокировки, прежде чем возникнет исключение. Значение по умолчанию для параметра timeout – 5.0 (пять секунд).181182По поводу параметра *isolation\_level* обратитесь к свойству [`isolation_level`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.isolation_level) объектов [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection).183184SQLite изначально поддерживает только типы TEXT, INTEGER, REAL, BLOB и NULL. Если вы хотите использовать другие типы, необходимо добавить поддержку самостоятельно. Параметр *detect\_types* и использование пользовательских **преобразователей**, зарегистрированных с помощью функции модуля [`register_converter()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.register_converter), позволяют легко это сделать.185186*detect\_types* по умолчанию равен 0 (т. е. выключено, определение типов отсутствует); вы можете установить его в любую комбинацию [`PARSE_DECLTYPES`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.PARSE_COLNAMES), чтобы включить определение типов. Из-за поведения SQLite типы не могут быть определены для генерируемых полей (например, `max(data)`), даже если установлен параметр *detect\_types*. В таком случае возвращаемый тип – [`str`](https://python-all.ru/3.9/library/stdtypes.html#str).187188По умолчанию *check\_same\_thread* равен [`True`](https://python-all.ru/3.9/library/constants.html#True), и только создавший поток может использовать соединение. Если установлено [`False`](https://python-all.ru/3.9/library/constants.html#False), возвращаемое соединение может использоваться несколькими потоками. При использовании нескольких потоков с одним соединением операции записи должны быть сериализованы пользователем во избежание повреждения данных.189190По умолчанию модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) использует свой класс [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection) для вызова connect. Однако вы можете создать подкласс класса [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection) и заставить [`connect()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.connect) использовать ваш класс, передав его в параметре *factory*.191192Подробности см. в разделе [Типы SQLite и Python](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-types) данного руководства.193194Модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) внутренне использует кэш операторов для снижения накладных расходов на разбор SQL. Если вы хотите явно задать количество операторов, кэшируемых для соединения, можно установить параметр *cached\_statements*. Текущая реализация по умолчанию кэширует 100 операторов.195196Если *uri* равен [`True`](https://python-all.ru/3.9/library/constants.html#True), *database* интерпретируется как URI с путём к файлу и необязательной строкой запроса. Схема *должна* быть `"file:"`. Путь может быть относительным или абсолютным путём к файлу. Строка запроса позволяет передавать параметры в SQLite. Некоторые полезные приёмы с URI включают:197198```python199# Открыть базу данных в режиме только для чтения.200con = sqlite3.connect("file:template.db?mode=ro", uri=True)201202# Не создавать неявно новый файл базы данных, если его ещё не существует.203# Вызовет sqlite3.OperationalError, если не удастся открыть файл базы данных.204con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)205206# Создать общую именованную базу данных в памяти.207con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)208con2 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)209con1.executescript("create table t(t); insert into t values(28);")210rows = con2.execute("select * from t").fetchall()211```212213Дополнительную информацию об этой функции, включая список распознаваемых параметров, можно найти в [документации SQLite по URI](https://python-all.ru/3.9/library/sqlite3.html).214215Вызывает [событие аудита](https://python-all.ru/3.9/library/sys.html#auditing) `sqlite3.connect` с аргументом `database`.216217Изменено в версии 3.4: Добавлен параметр *uri*.218219Изменено в версии 3.7: *database* теперь также может быть [объектом, подобным пути](https://python-all.ru/3.9/glossary.html#term-path-like-object), а не только строкой.220221#### `sqlite3.register_converter(typename, callable)`222223Регистрирует вызываемый объект для преобразования байтовой строки из базы данных в пользовательский тип Python. Вызываемый объект будет вызываться для всех значений базы данных, имеющих тип *typename*. Смотрите параметр *detect\_types* функции [`connect()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.connect), чтобы узнать, как работает определение типа. Обратите внимание, что *typename* и имя типа в запросе сравниваются без учёта регистра.224225#### `sqlite3.register_adapter(type, callable)`226227Регистрирует вызываемый объект для преобразования пользовательского типа Python *type* в один из поддерживаемых типов SQLite. Вызываемый объект *callable* принимает в качестве единственного параметра значение Python и должен возвращать значение одного из следующих типов: int, float, str или bytes.228229#### `sqlite3.complete_statement(sql)`230231Возвращает [`True`](https://python-all.ru/3.9/library/constants.html#True), если строка *sql* содержит одно или несколько полных SQL-выражений, завершающихся точкой с запятой. При этом не проверяется синтаксическая корректность SQL, только отсутствие незакрытых строковых литералов и завершение выражения точкой с запятой.232233Это можно использовать для создания оболочки для SQLite, как в следующем примере:234235```python236# Минимальная оболочка SQLite для экспериментов237238import sqlite3239240con = sqlite3.connect(":memory:")241con.isolation_level = None242cur = con.cursor()243244buffer = ""245246print("Enter your SQL commands to execute in sqlite3.")247print("Enter a blank line to exit.")248249while True:250    line = input()251    if line == "":252        break253    buffer += line254    if sqlite3.complete_statement(buffer):255        try:256            buffer = buffer.strip()257            cur.execute(buffer)258259            if buffer.lstrip().upper().startswith("SELECT"):260                print(cur.fetchall())261        except sqlite3.Error as e:262            print("An error occurred:", e.args[0])263        buffer = ""264265con.close()266```267268#### `sqlite3.enable_callback_tracebacks(flag)`269270По умолчанию трассировки не отображаются для пользовательских функций, агрегатов, преобразователей, колбэков авторизатора и т.д. Для отладки можно вызвать эту функцию, установив для *flag* значение `True`. После этого трассировки будут отображаться из колбэков на `sys.stderr`. Используйте [`False`](https://python-all.ru/3.9/library/constants.html#False), чтобы снова отключить эту возможность.271272## Объекты подключения273274#### `class sqlite3.Connection`275276Соединение с базой данных SQLite имеет следующие атрибуты и методы:277278#### `isolation_level`279280Получает или устанавливает текущий уровень изоляции по умолчанию. [`None`](https://python-all.ru/3.9/library/constants.html#None) для режима автокоммита или одно из значений «DEFERRED», «IMMEDIATE» или «EXCLUSIVE». Смотрите раздел [Управление транзакциями](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-controlling-transactions) для более подробного объяснения.281282#### `in_transaction`283284[`True`](https://python-all.ru/3.9/library/constants.html#True), если транзакция активна (есть незафиксированные изменения), иначе [`False`](https://python-all.ru/3.9/library/constants.html#False). Атрибут только для чтения.285286Новое в версии 3.2.287288#### `cursor(factory=Cursor)`289290Метод cursor принимает один необязательный параметр *factory*. Если он указан, это должен быть вызываемый объект, возвращающий экземпляр [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) или его подклассов.291292#### `commit()`293294Этот метод фиксирует текущую транзакцию. Если не вызвать этот метод, все изменения, сделанные после последнего вызова `commit()`, не будут видны из других подключений к базе данных. Если вы не видите данные, которые записали в базу, проверьте, не забыли ли вызвать этот метод.295296#### `rollback()`297298Этот метод откатывает все изменения в базе данных, сделанные после последнего вызова [`commit()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.commit).299300#### `close()`301302Это закрывает подключение к базе данных. Обратите внимание, что это не вызывает автоматически [`commit()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.commit). Если просто закрыть подключение к базе данных, не вызвав предварительно [`commit()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.commit), изменения будут потеряны!303304#### `execute(sql[, parameters])`305306Создать новый объект [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) и вызвать для него [`execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute) с заданными *sql* и *parameters*. Вернуть новый объект курсора.307308#### `executemany(sql[, parameters])`309310Создать новый объект [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) и вызвать для него [`executemany()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executemany) с заданными *sql* и *parameters*. Вернуть новый объект курсора.311312#### `executescript(sql_script)`313314Создать новый объект [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) и вызвать для него [`executescript()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executescript) с заданным *sql\_script*. Вернуть новый объект курсора.315316#### `create_function(name, num_params, func, *, deterministic=False)`317318Создаёт пользовательскую функцию, которую впоследствии можно использовать в SQL-выражениях под именем функции *name*. *num\_params* – количество параметров, принимаемых функцией (если *num\_params* равно -1, функция может принимать любое количество аргументов), а *func* – это вызываемый объект Python, который вызывается как SQL-функция. Если *deterministic* истинно, созданная функция помечается как [детерминированная](https://python-all.ru/3.9/library/sqlite3.html), что позволяет SQLite выполнять дополнительные оптимизации. Этот флаг поддерживается в SQLite 3.8.3 и выше; при использовании с более старыми версиями будет вызвано [`NotSupportedError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.NotSupportedError).319320Функция может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float и `None`.321322Изменено в версии 3.8: Добавлен параметр *deterministic*.323324Пример:325326```python327import sqlite3328import hashlib329330def md5sum(t):331    return hashlib.md5(t).hexdigest()332333con = sqlite3.connect(":memory:")334con.create_function("md5", 1, md5sum)335cur = con.cursor()336cur.execute("select md5(?)", (b"foo",))337print(cur.fetchone()[0])338339con.close()340```341342#### `create_aggregate(name, num_params, aggregate_class)`343344Создаёт пользовательскую агрегатную функцию.345346Класс агрегата должен реализовывать метод `step`, который принимает количество параметров *num\_params* (если *num\_params* равно -1, функция может принимать любое количество аргументов), и метод `finalize`, который возвращает окончательный результат агрегата.347348Метод `finalize` может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float и `None`.349350Пример:351352```python353import sqlite3354355class MySum:356    def __init__(self):357        self.count = 0358359    def step(self, value):360        self.count += value361362    def finalize(self):363        return self.count364365con = sqlite3.connect(":memory:")366con.create_aggregate("mysum", 1, MySum)367cur = con.cursor()368cur.execute("create table test(i)")369cur.execute("insert into test(i) values (1)")370cur.execute("insert into test(i) values (2)")371cur.execute("select mysum(i) from test")372print(cur.fetchone()[0])373374con.close()375```376377#### `create_collation(name, callable)`378379Создаёт collation с указанными *name* и *callable*. Вызываемый объект будет получать два строковых аргумента. Он должен возвращать -1, если первый меньше второго, 0, если они равны, и 1, если первый больше второго. Обратите внимание, что это управляет сортировкой (ORDER BY в SQL), поэтому сравнения не влияют на другие операции SQL.380381Обратите внимание, что вызываемый объект будет получать свои параметры в виде байтовых строк Python, которые обычно закодированы в UTF-8.382383Следующий пример показывает пользовательскую коллацию, которая сортирует «в обратном порядке»:384385```python386import sqlite3387388def collate_reverse(string1, string2):389    if string1 == string2:390        return 0391    elif string1 < string2:392        return 1393    else:394        return -1395396con = sqlite3.connect(":memory:")397con.create_collation("reverse", collate_reverse)398399cur = con.cursor()400cur.execute("create table test(x)")401cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])402cur.execute("select x from test order by x collate reverse")403for row in cur:404    print(row)405con.close()406```407408Чтобы удалить коллацию, вызовите `create_collation` с `None` в качестве вызываемого объекта:409410```python411con.create_collation("reverse", None)412```413414#### `interrupt()`415416Этот метод можно вызвать из другого потока, чтобы прервать любые запросы, которые могут выполняться в подключении. Запрос будет прерван, а вызывающий код получит исключение.417418#### `set_authorizer(authorizer_callback)`419420Эта процедура регистрирует колбэк. Колбэк вызывается при каждой попытке доступа к столбцу таблицы в базе данных. Колбэк должен возвращать `SQLITE_OK`, если доступ разрешён, `SQLITE_DENY`, если весь SQL-запрос должен быть прерван с ошибкой, и `SQLITE_IGNORE`, если столбец должен рассматриваться как значение NULL. Эти константы доступны в модуле [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3).421422Первый аргумент колбэка указывает, какой тип операции требуется авторизовать. Второй и третий аргументы будут аргументами или [`None`](https://python-all.ru/3.9/library/constants.html#None) в зависимости от первого аргумента. Четвёртый аргумент – имя базы данных («main», «temp» и т.д.), если применимо. Пятый аргумент – имя самого внутреннего триггера или представления, ответственного за попытку доступа, или [`None`](https://python-all.ru/3.9/library/constants.html#None), если эта попытка доступа происходит непосредственно из входного SQL-кода.423424Обратитесь к документации SQLite за возможными значениями первого аргумента и значением второго и третьего аргументов в зависимости от первого. Все необходимые константы доступны в модуле [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3).425426#### `set_progress_handler(handler, n)`427428Эта процедура регистрирует колбэк. Колбэк вызывается каждые *n* инструкций виртуальной машины SQLite. Это полезно, если требуется получать вызовы от SQLite во время длительных операций, например для обновления графического интерфейса.429430Чтобы очистить ранее установленный обработчик прогресса, вызовите метод с [`None`](https://python-all.ru/3.9/library/constants.html#None) для *handler*.431432Возврат ненулевого значения из функции-обработчика завершит выполняющийся запрос и вызовет исключение [`OperationalError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.OperationalError).433434#### `set_trace_callback(trace_callback)`435436Регистрирует *trace\_callback*, который будет вызываться для каждого SQL-выражения, фактически выполняемого серверной частью SQLite.437438Единственный аргумент, передаваемый колбэку, – это выполняемое выражение (в виде [`str`](https://python-all.ru/3.9/library/stdtypes.html#str)). Возвращаемое значение колбэка игнорируется. Обратите внимание, что серверная часть выполняет не только выражения, переданные методам [`Cursor.execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute). Другие источники включают [управление транзакциями](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-controlling-transactions) модуля sqlite3 и выполнение триггеров, определённых в текущей базе данных.439440Передача [`None`](https://python-all.ru/3.9/library/constants.html#None) в качестве *trace\_callback* отключает трассировочный колбэк.441442> **Примечание**443>444> Исключения, возникающие в колбэке трассировки, не распространяются. В качестве средства отладки и разработки используйте [`enable_callback_tracebacks()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.enable_callback_tracebacks), чтобы включить печать трассировок для исключений, возникающих в колбэке трассировки.445446Новое в версии 3.3.447448#### `enable_load_extension(enabled)`449450Эта процедура разрешает или запрещает механизму SQLite загружать расширения SQLite из разделяемых библиотек. Расширения SQLite могут определять новые функции, агрегатные функции или целые реализации виртуальных таблиц. Одним из известных расширений является расширение полнотекстового поиска, распространяемое с SQLite.451452Загружаемые расширения по умолчанию отключены. См. [1](https://python-all.ru/3.9/library/sqlite3.html#f1).453454Новое в версии 3.2.455456```python457import sqlite3458459con = sqlite3.connect(":memory:")460461# включить загрузку расширений462con.enable_load_extension(True)463464# Загрузить расширение полнотекстового поиска.465con.execute("select load_extension('./fts3.so')")466467# в качестве альтернативы можно загрузить расширение с помощью вызова API:468# con.load_extension("./fts3.so")469470# отключить загрузку расширения снова471con.enable_load_extension(False)472473# пример из вики SQLite474con.execute("create virtual table recipe using fts3(name, ingredients)")475con.executescript("""476    insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');477    insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');478    insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');479    insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');480    """)481for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):482    print(row)483484con.close()485```486487#### `load_extension(path)`488489Эта процедура загружает расширение SQLite из разделяемой библиотеки. Перед использованием этой процедуры необходимо включить загрузку расширений с помощью [`enable_load_extension()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.enable_load_extension).490491Загружаемые расширения по умолчанию отключены. См. [1](https://python-all.ru/3.9/library/sqlite3.html#f1).492493Новое в версии 3.2.494495#### `row_factory`496497Этот атрибут можно заменить на вызываемый объект, который принимает курсор и исходную строку в виде кортежа и возвращает реальную результирующую строку. Таким образом можно реализовать более продвинутые способы возврата результатов, например возврат объекта, который также может обращаться к столбцам по имени.498499Пример:500501```python502import sqlite3503504def dict_factory(cursor, row):505    d = {}506    for idx, col in enumerate(cursor.description):507        d[col[0]] = row[idx]508    return d509510con = sqlite3.connect(":memory:")511con.row_factory = dict_factory512cur = con.cursor()513cur.execute("select 1 as a")514print(cur.fetchone()["a"])515516con.close()517```518519Если возврата кортежа недостаточно и требуется доступ к столбцам по имени, рассмотрите возможность установки [`row_factory`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.row_factory) в высокооптимизированный тип [`sqlite3.Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row). [`Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row) обеспечивает как доступ по индексу, так и регистронезависимый доступ по имени с почти нулевыми накладными расходами памяти. Вероятно, это будет лучше, чем собственное решение на основе словаря или даже на основе db\_row.520521#### `text_factory`522523С помощью этого атрибута можно управлять тем, какие объекты возвращаются для типа данных `TEXT`. По умолчанию этот атрибут установлен в [`str`](https://python-all.ru/3.9/library/stdtypes.html#str), и модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) будет возвращать объекты [`str`](https://python-all.ru/3.9/library/stdtypes.html#str) для `TEXT`. Если требуется возвращать [`bytes`](https://python-all.ru/3.9/library/stdtypes.html#bytes), можно установить его в [`bytes`](https://python-all.ru/3.9/library/stdtypes.html#bytes).524525Также можно установить его в любой другой вызываемый объект, принимающий единственный параметр – байтовую строку и возвращающий результирующий объект.526527См. следующий пример для иллюстрации:528529```python530import sqlite3531532con = sqlite3.connect(":memory:")533cur = con.cursor()534535AUSTRIA = "Österreich"536537# по умолчанию строки возвращаются как str538cur.execute("select ?", (AUSTRIA,))539row = cur.fetchone()540assert row[0] == AUSTRIA541542# но можно заставить sqlite3 всегда возвращать байтовые строки...543con.text_factory = bytes544cur.execute("select ?", (AUSTRIA,))545row = cur.fetchone()546assert type(row[0]) is bytes547# байтовые строки будут в кодировке UTF-8, если только в базу не попал мусор548# база данных...549assert row[0] == AUSTRIA.encode("utf-8")550551# можно также реализовать собственный text_factory...552# здесь реализован такой, который добавляет "foo" ко всем строкам553con.text_factory = lambda x: x.decode("utf-8") + "foo"554cur.execute("select ?", ("bar",))555row = cur.fetchone()556assert row[0] == "barfoo"557558con.close()559```560561#### `total_changes`562563Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия подключения к базе данных.564565#### `iterdump()`566567Возвращает итератор для дампа базы данных в текстовом формате SQL. Полезно при сохранении базы данных в памяти для последующего восстановления. Эта функция предоставляет те же возможности, что и команда `.dump` в оболочке **sqlite3**.568569Пример:570571```python572# Преобразовать файл existing_db.db в SQL-дамп dump.sql573import sqlite3574575con = sqlite3.connect('existing_db.db')576with open('dump.sql', 'w') as f:577    for line in con.iterdump():578        f.write('%s\n' % line)579con.close()580```581582#### `backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)`583584Этот метод создаёт резервную копию базы данных SQLite, даже когда к ней обращаются другие клиенты или одновременно то же самое подключение. Копия будет записана в обязательный аргумент *target*, который должен быть другим экземпляром [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection).585586По умолчанию, или когда *pages* равен `0` или отрицательному целому, вся база данных копируется за один шаг; в противном случае метод выполняет цикл, копируя до *pages* страниц за раз.587588Если указан *progress*, он должен быть либо `None`, либо вызываемым объектом, который будет выполняться на каждой итерации с тремя целочисленными аргументами: *status* последней итерации, *remaining* количество страниц, оставшихся для копирования, и *total* общее количество страниц.589590Аргумент *name* задаёт имя базы данных, которая будет скопирована: это должна быть строка, содержащая либо `"main"` (по умолчанию) для основной базы данных, `"temp"` для временной базы данных, либо имя, указанное после ключевого слова `AS` в выражении `ATTACH DATABASE` для присоединённой базы данных.591592Аргумент *sleep* задаёт количество секунд ожидания между последовательными попытками резервного копирования оставшихся страниц; может быть указан как целое или число с плавающей запятой.593594Пример 1: копирование существующей базы данных в другую:595596```python597import sqlite3598599def progress(status, remaining, total):600    print(f'Copied {total-remaining} of {total} pages...')601602con = sqlite3.connect('existing_db.db')603bck = sqlite3.connect('backup.db')604with bck:605    con.backup(bck, pages=1, progress=progress)606bck.close()607con.close()608```609610Пример 2: копирование существующей базы данных во временную копию:611612```python613import sqlite3614615source = sqlite3.connect('existing_db.db')616dest = sqlite3.connect(':memory:')617source.backup(dest)618```619620Доступность: SQLite 3.6.11 или выше621622Добавлено в версии 3.7.623624## Объекты курсора625626#### `class sqlite3.Cursor`627628Экземпляр [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) имеет следующие атрибуты и методы.629630#### `execute(sql[, parameters])`631632Выполняет SQL-выражение. Значения могут быть привязаны к выражению с помощью [плейсхолдеров](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-placeholders).633634[`execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute) выполняет только одно SQL-выражение. При попытке выполнить с его помощью несколько выражений будет вызвано исключение [`Warning`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Warning). Используйте [`executescript()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executescript), если нужно выполнить несколько SQL-выражений одним вызовом.635636#### `executemany(sql, seq_of_parameters)`637638Выполняет [параметризованную](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-placeholders) SQL-команду для всех последовательностей или отображений параметров, найденных в последовательности *seq\_of\_parameters*. Модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) также позволяет использовать [итератор](https://python-all.ru/3.9/glossary.html#term-iterator), возвращающий параметры, вместо последовательности.639640```python641import sqlite3642643class IterChars:644    def __init__(self):645        self.count = ord('a')646647    def __iter__(self):648        return self649650    def __next__(self):651        if self.count > ord('z'):652            raise StopIteration653        self.count += 1654        return (chr(self.count - 1),) # это кортеж из одного элемента655656con = sqlite3.connect(":memory:")657cur = con.cursor()658cur.execute("create table characters(c)")659660theIter = IterChars()661cur.executemany("insert into characters(c) values (?)", theIter)662663cur.execute("select c from characters")664print(cur.fetchall())665666con.close()667```668669Вот более короткий пример с использованием [генератора](https://python-all.ru/3.9/glossary.html#term-generator):670671```python672import sqlite3673import string674675def char_generator():676    for c in string.ascii_lowercase:677        yield (c,)678679con = sqlite3.connect(":memory:")680cur = con.cursor()681cur.execute("create table characters(c)")682683cur.executemany("insert into characters(c) values (?)", char_generator())684685cur.execute("select c from characters")686print(cur.fetchall())687688con.close()689```690691#### `executescript(sql_script)`692693Это нестандартный удобный метод для выполнения нескольких SQL-выражений за один раз. Сначала он выполняет выражение `COMMIT`, а затем выполняет SQL-скрипт, переданный в качестве параметра. Этот метод игнорирует `isolation_level`; любое управление транзакциями должно быть добавлено в *sql\_script*.694695*sql\_script* может быть экземпляром [`str`](https://python-all.ru/3.9/library/stdtypes.html#str).696697Пример:698699```python700import sqlite3701702con = sqlite3.connect(":memory:")703cur = con.cursor()704cur.executescript("""705    create table person(706        firstname,707        lastname,708        age709    );710711    create table book(712        title,713        author,714        published715    );716717    insert into book(title, author, published)718    values (719        'Dirk Gently''s Holistic Detective Agency',720        'Douglas Adams',721        1987722    );723    """)724con.close()725```726727#### `fetchone()`728729Извлекает следующую строку результата запроса, возвращая одну последовательность или [`None`](https://python-all.ru/3.9/library/constants.html#None), если данные закончились.730731#### `fetchmany(size=cursor.arraysize)`732733Извлекает следующий набор строк результата запроса, возвращая список. Пустой список возвращается, когда строк больше нет.734735Количество строк, извлекаемых за один вызов, задаётся параметром *size*. Если он не указан, количество строк определяется атрибутом arraysize курсора. Метод должен пытаться извлечь столько строк, сколько указано параметром size. Если это невозможно из-за того, что указанное количество строк недоступно, может быть возвращено меньше строк.736737Обратите внимание, что параметр *size* связан с производительностью. Для оптимальной производительности обычно лучше использовать атрибут arraysize. Если используется параметр *size*, рекомендуется сохранять одно и то же значение от одного вызова [`fetchmany()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.fetchmany) к следующему.738739#### `fetchall()`740741Извлекает все (оставшиеся) строки результата запроса, возвращая список. Обратите внимание, что атрибут arraysize курсора может влиять на производительность этой операции. Пустой список возвращается, если строк нет.742743#### `close()`744745Закрывает курсор сейчас (а не при вызове `__del__`).746747Курсор станет непригодным к использованию; будет выброшено исключение [`ProgrammingError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.ProgrammingError) при любой попытке выполнить операцию с курсором.748749#### `setinputsizes(sizes)`750751Требуется DB-API. Ничего не делает в [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3).752753#### `setoutputsize(size[, column])`754755Требуется DB-API. Ничего не делает в [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3).756757#### `rowcount`758759Хотя класс [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) модуля [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) реализует этот атрибут, собственная поддержка определения «затронутых строк»/«выбранных строк» в движке базы данных работает непредсказуемо.760761Для выражений [`executemany()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executemany) количество изменений суммируется в [`rowcount`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.rowcount).762763В соответствии со спецификацией Python DB API, атрибут [`rowcount`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.rowcount) «равен -1, если над курсором не выполнялось `executeXX()` или количество строк последней операции не может быть определено интерфейсом». Это включает выражения `SELECT`, потому что количество строк, полученных запросом, нельзя определить, пока не будут извлечены все строки.764765В SQLite версий до 3.6.5 [`rowcount`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.rowcount) устанавливается в 0, если выполнить `DELETE FROM table` без условия.766767#### `lastrowid`768769Этот атрибут только для чтения содержит идентификатор последней вставленной строки. Он обновляется только после успешного выполнения выражений `INSERT` или `REPLACE` с использованием метода [`execute()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.execute). Для других выражений, после [`executemany()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executemany) или [`executescript()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executescript), или если вставка не удалась, значение `lastrowid` остаётся неизменным. Начальное значение `lastrowid` – [`None`](https://python-all.ru/3.9/library/constants.html#None).770771> **Примечание**772>773> Вставки в таблицы `WITHOUT ROWID` не записываются.774775Изменено в версии 3.6: Добавлена поддержка оператора `REPLACE`.776777#### `arraysize`778779Атрибут чтения/записи, управляющий количеством строк, возвращаемых [`fetchmany()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.fetchmany). Значение по умолчанию – 1, то есть при каждом вызове будет извлечена одна строка.780781#### `description`782783Этот атрибут только для чтения содержит имена столбцов последнего запроса. Для совместимости с Python DB API он возвращает 7-кортеж для каждого столбца, где последние шесть элементов каждого кортежа равны [`None`](https://python-all.ru/3.9/library/constants.html#None).784785Он также устанавливается для операторов `SELECT`, не имеющих подходящих строк.786787#### `connection`788789Этот атрибут только для чтения предоставляет [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection) базы данных SQLite, используемый объектом [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor). Объект [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor), созданный вызовом [`con.cursor()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.cursor), будет иметь атрибут [`connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.connection), ссылающийся на *con*:790791```python792>>> con = sqlite3.connect(":memory:")793>>> cur = con.cursor()794>>> cur.connection == con795True796```797798## Объекты строк799800#### `class sqlite3.Row`801802Экземпляр [`Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row) служит высокооптимизированной [`row_factory`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.row_factory) для объектов [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection). Он старается имитировать кортеж в большинстве своих возможностей.803804Он поддерживает доступ по имени столбца и индексу, итерацию, представление, проверку равенства и [`len()`](https://python-all.ru/3.9/library/functions.html#len).805806Если два объекта [`Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row) имеют одинаковые столбцы и их члены равны, они считаются равными.807808#### `keys()`809810Этот метод возвращает список имён столбцов. Сразу после запроса это первый элемент каждого кортежа в [`Cursor.description`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.description).811812Изменено в версии 3.5: Добавлена поддержка срезов.813814Предположим, мы инициализируем таблицу, как в приведённом выше примере:815816```python817con = sqlite3.connect(":memory:")818cur = con.cursor()819cur.execute('''create table stocks820(date text, trans text, symbol text,821 qty real, price real)''')822cur.execute("""insert into stocks823            values ('2006-01-05','BUY','RHAT',100,35.14)""")824con.commit()825cur.close()826```827828Теперь подключаем [`Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row):829830```python831>>> con.row_factory = sqlite3.Row832>>> cur = con.cursor()833>>> cur.execute('select * from stocks')834<sqlite3.Cursor object at 0x7f4e7dd8fa80>835>>> r = cur.fetchone()836>>> type(r)837<class 'sqlite3.Row'>838>>> tuple(r)839('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)840>>> len(r)8415842>>> r[2]843'RHAT'844>>> r.keys()845['date', 'trans', 'symbol', 'qty', 'price']846>>> r['qty']847100.0848>>> for member in r:849...     print(member)850...8512006-01-05852BUY853RHAT854100.085535.14856```857858## Исключения859860#### `exception sqlite3.Warning`861862Подкласс [`Exception`](https://python-all.ru/3.9/library/exceptions.html#Exception).863864#### `exception sqlite3.Error`865866Базовый класс остальных исключений в этом модуле. Он является подклассом [`Exception`](https://python-all.ru/3.9/library/exceptions.html#Exception).867868#### `exception sqlite3.DatabaseError`869870Исключение, вызываемое для ошибок, связанных с базой данных.871872#### `exception sqlite3.IntegrityError`873874Исключение возникает при нарушении реляционной целостности базы данных, например, при неудачной проверке внешнего ключа. Оно является подклассом [`DatabaseError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.DatabaseError).875876#### `exception sqlite3.ProgrammingError`877878Исключение вызывается при ошибках программирования, например, таблица не найдена или уже существует, синтаксическая ошибка в SQL-запросе, указано неверное количество параметров и т.д. Является подклассом [`DatabaseError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.DatabaseError).879880#### `exception sqlite3.OperationalError`881882Исключение вызывается для ошибок, связанных с работой базы данных и не обязательно находящихся под контролем программиста, например, неожиданное отключение, не найден источник данных, транзакция не могла быть обработана, и т.д. Является подклассом [`DatabaseError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.DatabaseError).883884#### `exception sqlite3.NotSupportedError`885886Исключение вызывается, если используется метод или API базы данных, который не поддерживается базой данных, например, вызов метода [`rollback()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.rollback) для соединения, которое не поддерживает транзакции или имеет транзакции отключены. Является подклассом [`DatabaseError`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.DatabaseError).887888## Типы SQLite и Python889890### Введение891892SQLite изначально поддерживает следующие типы: `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`.893894Следующие типы Python можно передавать в SQLite без каких-либо проблем:895896| Тип Python | Тип SQLite |897| --- | --- |898| [`None`](https://python-all.ru/3.9/library/constants.html#None) | `NULL` |899| [`int`](https://python-all.ru/3.9/library/functions.html#int) | `INTEGER` |900| [`float`](https://python-all.ru/3.9/library/functions.html#float) | `REAL` |901| [`str`](https://python-all.ru/3.9/library/stdtypes.html#str) | `TEXT` |902| [`bytes`](https://python-all.ru/3.9/library/stdtypes.html#bytes) | `BLOB` |903904Так по умолчанию типы SQLite преобразуются в типы Python:905906| Тип SQLite | Тип Python |907| --- | --- |908| `NULL` | [`None`](https://python-all.ru/3.9/library/constants.html#None) |909| `INTEGER` | [`int`](https://python-all.ru/3.9/library/functions.html#int) |910| `REAL` | [`float`](https://python-all.ru/3.9/library/functions.html#float) |911| `TEXT` | зависит от [`text_factory`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection.text_factory), по умолчанию [`str`](https://python-all.ru/3.9/library/stdtypes.html#str) |912| `BLOB` | [`bytes`](https://python-all.ru/3.9/library/stdtypes.html#bytes) |913914Система типов модуля [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) расширяема двумя способами: можно хранить дополнительные типы Python в базе данных SQLite с помощью адаптации объектов, а также можно позволить модулю [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) преобразовывать типы SQLite в различные типы Python с помощью конвертеров.915916### Использование адаптеров для хранения дополнительных типов Python в базах данных SQLite917918Как описывалось ранее, SQLite изначально поддерживает только ограниченный набор типов. Чтобы использовать другие типы Python с SQLite, необходимо **адаптировать** их к одному из поддерживаемых модулем sqlite3 типов для SQLite: NoneType, int, float, str, bytes.919920Существует два способа разрешить модулю [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) адаптировать пользовательский тип Python к одному из поддерживаемых.921922#### Адаптация объекта самого себя923924Это хороший подход, если вы пишете класс сами. Предположим, у вас есть такой класс:925926```python927class Point:928    def __init__(self, x, y):929        self.x, self.y = x, y930```931932Теперь вы хотите сохранить точку в одной колонке SQLite. Сначала нужно выбрать один из поддерживаемых типов для представления точки. Давайте просто используем str и разделим координаты точкой с запятой. Затем нужно добавить в класс метод `__conform__(self, protocol)`, который должен возвращать преобразованное значение. Параметр *протокол* будет равен `PrepareProtocol`.933934```python935import sqlite3936937class Point:938    def __init__(self, x, y):939        self.x, self.y = x, y940941    def __conform__(self, protocol):942        if protocol is sqlite3.PrepareProtocol:943            return "%f;%f" % (self.x, self.y)944945con = sqlite3.connect(":memory:")946cur = con.cursor()947948p = Point(4.0, -3.2)949cur.execute("select ?", (p,))950print(cur.fetchone()[0])951952con.close()953```954955#### Регистрация вызываемого адаптера956957Другой вариант – создать функцию, которая преобразует тип в строковое представление, и зарегистрировать её с помощью [`register_adapter()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.register_adapter).958959```python960import sqlite3961962class Point:963    def __init__(self, x, y):964        self.x, self.y = x, y965966def adapt_point(point):967    return "%f;%f" % (point.x, point.y)968969sqlite3.register_adapter(Point, adapt_point)970971con = sqlite3.connect(":memory:")972cur = con.cursor()973974p = Point(4.0, -3.2)975cur.execute("select ?", (p,))976print(cur.fetchone()[0])977978con.close()979```980981Модуль [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) имеет два адаптера по умолчанию для встроенных типов Python [`datetime.date`](https://python-all.ru/3.9/library/datetime.html#datetime.date) и [`datetime.datetime`](https://python-all.ru/3.9/library/datetime.html#datetime.datetime). Теперь предположим, что мы хотим сохранить объекты [`datetime.datetime`](https://python-all.ru/3.9/library/datetime.html#datetime.datetime) не в формате ISO, а в виде временной метки Unix.982983```python984import sqlite3985import datetime986import time987988def adapt_datetime(ts):989    return time.mktime(ts.timetuple())990991sqlite3.register_adapter(datetime.datetime, adapt_datetime)992993con = sqlite3.connect(":memory:")994cur = con.cursor()995996now = datetime.datetime.now()997cur.execute("select ?", (now,))998print(cur.fetchone()[0])9991000con.close()1001```10021003### Преобразование значений SQLite в пользовательские типы Python10041005Написание адаптера позволяет отправлять пользовательские типы Python в SQLite. Но чтобы сделать это действительно полезным, нужно обеспечить обратное преобразование из SQLite в Python.10061007Вступают конвертеры.10081009Вернемся к классу `Point`. Мы сохраняли координаты x и y, разделенные точкой с запятой, в виде строк в SQLite.10101011Сначала определим функцию-конвертер, которая принимает строку в качестве параметра и создает из нее объект `Point`.10121013> **Примечание**1014>1015> Функции-конвертеры **всегда** вызываются с объектом [`bytes`](https://python-all.ru/3.9/library/stdtypes.html#bytes), независимо от того, с каким типом данных значение было отправлено в SQLite.10161017```python1018def convert_point(s):1019    x, y = map(float, s.split(b";"))1020    return Point(x, y)1021```10221023Теперь нужно сообщить модулю [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3), что выбираемые из базы данные на самом деле являются точкой. Есть два способа сделать это:10241025- Неявно, через объявленный тип1026- Явно, по имени колонки10271028Оба способа описаны в разделе [Функции и константы модуля](https://python-all.ru/3.9/library/sqlite3.html#sqlite3-module-contents), в записях для констант [`PARSE_DECLTYPES`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.PARSE_COLNAMES).10291030Следующий пример иллюстрирует оба подхода.10311032```python1033import sqlite310341035class Point:1036    def __init__(self, x, y):1037        self.x, self.y = x, y10381039    def __repr__(self):1040        return "(%f;%f)" % (self.x, self.y)10411042def adapt_point(point):1043    return ("%f;%f" % (point.x, point.y)).encode('ascii')10441045def convert_point(s):1046    x, y = list(map(float, s.split(b";")))1047    return Point(x, y)10481049# Зарегистрировать адаптер1050sqlite3.register_adapter(Point, adapt_point)10511052# Зарегистрировать конвертер1053sqlite3.register_converter("point", convert_point)10541055p = Point(4.0, -3.2)10561057#########################1058# 1) Использование объявленных типов1059con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)1060cur = con.cursor()1061cur.execute("create table test(p point)")10621063cur.execute("insert into test(p) values (?)", (p,))1064cur.execute("select p from test")1065print("with declared types:", cur.fetchone()[0])1066cur.close()1067con.close()10681069#######################1070# 1) Использование имён столбцов1071con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)1072cur = con.cursor()1073cur.execute("create table test(p)")10741075cur.execute("insert into test(p) values (?)", (p,))1076cur.execute('select p as "p [point]" from test')1077print("with column names:", cur.fetchone()[0])1078cur.close()1079con.close()1080```10811082### Адаптеры и преобразователи по умолчанию10831084В модуле datetime есть адаптеры по умолчанию для типов date и datetime. Они отправляются в SQLite как даты/временные метки в формате ISO.10851086Преобразователи по умолчанию зарегистрированы под именем «date» для [`datetime.date`](https://python-all.ru/3.9/library/datetime.html#datetime.date) и под именем «timestamp» для [`datetime.datetime`](https://python-all.ru/3.9/library/datetime.html#datetime.datetime).10871088Таким образом, в большинстве случаев можно использовать даты и временные метки из Python без лишних усилий. Формат адаптеров также совместим с экспериментальными функциями даты/времени SQLite.10891090Следующий пример демонстрирует это.10911092```python1093import sqlite31094import datetime10951096con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)1097cur = con.cursor()1098cur.execute("create table test(d date, ts timestamp)")10991100today = datetime.date.today()1101now = datetime.datetime.now()11021103cur.execute("insert into test(d, ts) values (?, ?)", (today, now))1104cur.execute("select d, ts from test")1105row = cur.fetchone()1106print(today, "=>", row[0], type(row[0]))1107print(now, "=>", row[1], type(row[1]))11081109cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')1110row = cur.fetchone()1111print("current_date", row[0], type(row[0]))1112print("current_timestamp", row[1], type(row[1]))11131114con.close()1115```11161117Если временная метка, хранящаяся в SQLite, имеет дробную часть длиннее 6 знаков, её значение будет усечено до микросекундной точности преобразователем временных меток.11181119> **Примечание**1120>1121> Конвертер "timestamp" по умолчанию игнорирует смещения UTC в базе данных и всегда возвращает наивный объект [`datetime.datetime`](https://python-all.ru/3.9/library/datetime.html#datetime.datetime). Чтобы сохранить смещения UTC в метках времени, либо оставьте конвертеры отключёнными, либо зарегистрируйте конвертер с учётом смещения через [`register_converter()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.register_converter).11221123## Управление транзакциями11241125Лежащая в основе библиотека `sqlite3` по умолчанию работает в режиме `autocommit`, а модуль Python [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) по умолчанию – нет.11261127Режим `autocommit` означает, что операторы, изменяющие базу данных, вступают в силу немедленно. Оператор `BEGIN` или `SAVEPOINT` отключает режим `autocommit`, а оператор `COMMIT`, `ROLLBACK` или `RELEASE`, завершающий внешнюю транзакцию, снова включает режим `autocommit`.11281129Модуль Python [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) по умолчанию неявно выполняет оператор `BEGIN` перед оператором языка модификации данных (DML) (т.е. `INSERT`/`UPDATE`/`DELETE`/`REPLACE`).11301131Можно управлять тем, какие операторы `BEGIN` [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) выполняет неявно, с помощью параметра *isolation\_level* вызова [`connect()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.connect), или через свойство `isolation_level` соединений. Если не указывать *isolation\_level*, используется простой `BEGIN`, что eквивалентно указанию `DEFERRED`. Другие возможные значения: `IMMEDIATE` и `EXCLUSIVE`.11321133Можно отключить неявное управление транзакциями модуля [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3), установив `isolation_level` в `None`. При этом лежащая в основе библиотека `sqlite3` продолжит работать в режиме `autocommit`. После этого можно полностью управлять состоянием транзакции, явно выполняя операторы `BEGIN`, `ROLLBACK`, `SAVEPOINT` и `RELEASE` в коде.11341135Обратите внимание, что [`executescript()`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor.executescript) игнорирует `isolation_level`; любое управление транзакциями должно быть добавлено явно.11361137Изменено в версии 3.6: [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) ранее неявно фиксировал открытую транзакцию перед DDL операторами. Теперь это не так.11381139## Эффективное использование [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3)11401141### Использование сокращенных методов11421143Используя нестандартные методы `execute()`, `executemany()` и `executescript()` объекта [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection), код можно писать более кратко, поскольку не нужно явно создавать (часто излишние) объекты [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor). Вместо этого объекты [`Cursor`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Cursor) создаются неявно, и эти сокращенные методы возвращают объекты курсора. Таким образом, можно выполнить оператор `SELECT` и итерироваться по нему напрямую, используя всего один вызов объекта [`Connection`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Connection).11441145```python1146import sqlite311471148langs = [1149    ("C++", 1985),1150    ("Objective-C", 1984),1151]11521153con = sqlite3.connect(":memory:")11541155# Создать таблицу1156con.execute("create table lang(name, first_appeared)")11571158# Заполнить таблицу1159con.executemany("insert into lang(name, first_appeared) values (?, ?)", langs)11601161# Выводит содержимое таблицы.1162for row in con.execute("select name, first_appeared from lang"):1163    print(row)11641165print("I just deleted", con.execute("delete from lang").rowcount, "rows")11661167# close не является методом-ярлыком и не вызывается автоматически,1168# поэтому объект соединения следует закрывать вручную.1169con.close()1170```11711172### Доступ к столбцам по имени вместо индекса11731174Одна из полезных возможностей модуля [`sqlite3`](https://python-all.ru/3.9/library/sqlite3.html#module-sqlite3) – встроенный класс [`sqlite3.Row`](https://python-all.ru/3.9/library/sqlite3.html#sqlite3.Row), предназначенный для использования в качестве фабрики строк.11751176Строки, обёрнутые этим классом, можно получать как по индексу (как кортежи), так и по имени без учёта регистра:11771178```python1179import sqlite311801181con = sqlite3.connect(":memory:")1182con.row_factory = sqlite3.Row11831184cur = con.cursor()1185cur.execute("select 'John' as name, 42 as age")1186for row in cur:1187    assert row[0] == row["name"]1188    assert row["name"] == row["nAmE"]1189    assert row[1] == row["age"]1190    assert row[1] == row["AgE"]11911192con.close()1193```11941195### Использование соединения как менеджера контекста11961197Объекты соединения можно использовать как менеджеры контекста, которые автоматически фиксируют или откатывают транзакции. В случае исключения транзакция откатывается; в противном случае транзакция фиксируется:11981199```python1200import sqlite312011202con = sqlite3.connect(":memory:")1203con.execute("create table lang (id integer primary key, name varchar unique)")12041205# При успешном выполнении con.commit() вызывается автоматически.1206with con:1207    con.execute("insert into lang(name) values (?)", ("Python",))12081209# con.rollback() вызывается после завершения блока with с исключением,1210# исключение все равно возбуждается, и его необходимо перехватить1211try:1212    with con:1213        con.execute("insert into lang(name) values (?)", ("Python",))1214except sqlite3.IntegrityError:1215    print("couldn't add Python twice")12161217# Объект Connection, используемый как контекстный менеджер, только фиксирует или откатывает транзакции,1218# поэтому объект соединения следует закрывать вручную.1219con.close()1220```12211222Сноски12231224**1([1](https://python-all.ru/3.9/library/sqlite3.html#id1),[2](https://python-all.ru/3.9/library/sqlite3.html#id2))**12251226Модуль sqlite3 по умолчанию собирается без поддержки загружаемых расширений, поскольку на некоторых платформах (в частности, macOS) библиотеки SQLite скомпилированы без этой возможности. Чтобы получить поддержку загружаемых расширений, необходимо передать `--enable-loadable-sqlite-extensions` в configure.1227