`sqlite3.md`

1083 строк · 61.1 КБ · обычная страница · сырой текст · скачать
1> **Источник:** https://python-all.ru/3.1/library/sqlite3.html2>3> «Документация Python на русском» – неофициальный перевод официальной документации Python: версии от 2.6 до 3.16, полнотекстовый поиск, английский оригинал рядом с переводом. Эта Markdown-версия страницы предназначена для работы с LLM: вставьте её в ChatGPT, Claude или Cursor.45---67# 11.6. `sqlite3` – интерфейс DB-API 2.0 для баз данных SQLite89SQLite – это библиотека на C, которая предоставляет легковесную дисковую базу данных, не требующую отдельного серверного процесса, и позволяет обращаться к базе данных с помощью нестандартного варианта языка SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также можно создать прототип приложения на SQLite, а затем перенести код на более крупную базу данных, например PostgreSQL или Oracle.1011sqlite3 написан Герхардом Херингом и предоставляет SQL-интерфейс, соответствующий спецификации DB-API 2.0, описанной в [**PEP 249**](https://python-all.ru/3.1/library/sqlite3.html).1213Чтобы использовать модуль, сначала необходимо создать объект [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection), представляющий базу данных. Здесь данные будут храниться в файле `/tmp/example`:1415```python16conn = sqlite3.connect('/tmp/example')17```1819Можно также указать специальное имя `:memory:`, чтобы создать базу данных в оперативной памяти.2021Когда у вас есть объект [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection), можно создать объект [`Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor) и вызывать его метод [`execute()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.execute) для выполнения SQL-команд:2223```python24c = conn.cursor()2526# Создать таблицу27c.execute('''create table stocks28(date text, trans text, symbol text,29 qty real, price real)''')3031# Вставить строку данных32c.execute("""insert into stocks33          values ('2006-01-05','BUY','RHAT',100,35.14)""")3435# Сохранить (зафиксировать) изменения36conn.commit()3738# Также можно закрыть курсор, если он больше не нужен.39c.close()40```4142Обычно вашим SQL-операциям потребуется использовать значения из переменных Python. Не следует собирать запрос с помощью строковых операций Python, потому что это небезопасно; это делает вашу программу уязвимой для SQL-инъекций.4344Вместо этого используйте подстановку параметров DB-API. Поставьте `?` в качестве заполнителя там, где нужно использовать значение, а затем передайте кортеж значений вторым аргументом методу [`execute()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.execute) курсора. (В других модулях баз данных может использоваться другой заполнитель, например `%s` или `:1`.) Например:4546```python47# Никогда не делайте этого – это небезопасно!48symbol = 'IBM'49c.execute("... where symbol = '%s'" % symbol)5051# Вместо этого сделайте так52t = (symbol,)53c.execute('select * from stocks where symbol=?', t)5455# Более крупный пример56for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),57          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),58          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),59         ]:60    c.execute('insert into stocks values (?,?,?,?,?)', t)61```6263Чтобы извлечь данные после выполнения оператора SELECT, можно либо рассматривать курсор как [*итератор*](https://python-all.ru/3.1/glossary.html#term-iterator), вызывать его метод [`fetchone()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.fetchone) для получения одной подходящей строки, либо вызвать [`fetchall()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.fetchall) для получения списка всех подходящих строк.6465В этом примере используется форма итератора:6667```python68>>> c = conn.cursor()69>>> c.execute('select * from stocks order by price')70>>> for row in c:71...     print(row)72...73('2006-01-05', 'BUY', 'RHAT', 100, 35.14)74('2006-03-28', 'BUY', 'IBM', 1000, 45.0)75('2006-04-06', 'SELL', 'IBM', 500, 53.0)76('2006-04-05', 'BUY', 'MSOFT', 1000, 72.0)77>>>78```7980> **См. также**81>82> **[http://www.pysqlite.org](https://python-all.ru/3.1/library/sqlite3.html)**83>84> Страница pysqlite – sqlite3 разрабатывается отдельно под названием «pysqlite».85>86> **[http://www.sqlite.org](https://python-all.ru/3.1/library/sqlite3.html)**87>88> Веб-страница SQLite; документация описывает синтаксис и доступные типы данных поддерживаемого диалекта SQL.89>90> **[**PEP 249**](https://python-all.ru/3.1/library/sqlite3.html) – спецификация API баз данных 2.0**91>92> PEP написан Марком-Андре Лембургом.9394## 11.6.1. Функции и константы модуля9596#### `sqlite3.PARSE_DECLTYPES`9798Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.connect).99100При установке этого флага модуль `sqlite3` анализирует объявленный тип для каждого возвращаемого столбца. Он извлекает первое слово объявленного типа, например, для «integer primary key» извлекается «integer», а для «number(10)» – «number». Затем для этого столбца модуль обращается к словарю преобразователей и использует зарегистрированную для этого типа функцию преобразования.101102#### `sqlite3.PARSE_COLNAMES`103104Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.connect).105106Если установить этот флаг, интерфейс SQLite будет анализировать имя столбца для каждого возвращаемого столбца. Он будет искать строку вида \[mytype\] в имени столбца и затем считать, что «mytype» – это тип столбца. Далее будет выполнена попытка найти запись «mytype» в словаре конвертеров и используется найденная функция преобразования для возврата значения. Имя столбца, полученное из [`Cursor.description`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.description), – это только первое слово имени столбца; то есть если в SQL используется что-то вроде `'as "x [datetime]"'`, то будет извлечено всё до первого пробела: имя столбца будет просто «x».107108#### `sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])`109110Открывает соединение с файлом базы данных SQLite *database*. Можно использовать `":memory:"`, чтобы открыть соединение с базой данных, расположенной в оперативной памяти, а не на диске.111112Когда к базе данных обращаются несколько соединений и один из процессов изменяет базу данных, база данных SQLite блокируется до завершения этой транзакции. Параметр *timeout* задаёт, как долго соединение должно ждать снятия блокировки, прежде чем возникнет исключение. Значение по умолчанию для параметра timeout – 5.0 (пять секунд).113114По поводу параметра *isolation\_level* обратитесь к свойству [`Connection.isolation_level`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.isolation_level) объектов [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection).115116SQLite изначально поддерживает только типы TEXT, INTEGER, FLOAT, BLOB и NULL. Если вы хотите использовать другие типы, вам необходимо добавить поддержку для них самостоятельно. Параметр *detect\_types* и использование пользовательских **преобразователей**, зарегистрированных с помощью функции модульного уровня [`register_converter()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.register_converter), позволяют легко это сделать.117118Параметр *detect\_types* по умолчанию равен 0 (т.е. выключен, определение типов отключено); его можно установить в любую комбинацию [`PARSE_DECLTYPES`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.PARSE_COLNAMES) для включения определения типов.119120По умолчанию модуль `sqlite3` использует свой класс [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection) для вызова connect(). Однако можно создать подкласс [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection) и заставить [`connect()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.connect) использовать ваш класс, передав его в параметре *factory*.121122Подробности см. в разделе [*Типы SQLite и Python*](https://python-all.ru/3.1/library/sqlite3.html#sqlite3-types) данного руководства.123124Модуль `sqlite3` внутри использует кеш запросов, чтобы избежать накладных расходов на разбор SQL. Если требуется явно указать количество кешируемых для соединения запросов, можно задать параметр *cached\_statements*. Текущая реализация по умолчанию кеширует 100 запросов.125126#### `sqlite3.register_converter(typename, callable)`127128Регистрирует вызываемый объект для преобразования байтовой строки из базы данных в пользовательский тип Python. Этот вызываемый объект будет вызываться для всех значений базы данных, имеющих тип129130*typename*131132. О том, как работает определение типа, см. параметр133134*detect\_types*135136функции137138[`connect()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.connect)139140. Обратите внимание, что регистр символов в141142*typename*143144и имени типа в запросе должны совпадать!145146#### `sqlite3.register_adapter(type, callable)`147148Регистрирует вызываемый объект для преобразования пользовательского типа Python149150*type*151152в один из поддерживаемых типов SQLite. Вызываемый объект153154*callable*155156принимает в качестве единственного параметра значение Python и должен возвращать значение одного из следующих типов: int, float, str или bytes.157158#### `sqlite3.complete_statement(sql)`159160Возвращает [`True`](https://python-all.ru/3.1/library/constants.html#True), если строка *sql* содержит одно или несколько полных SQL-выражений, завершённых точкой с запятой. При этом не проверяется синтаксическая корректность SQL – только отсутствие незакрытых строковых литералов и наличие точки с запятой в конце каждого выражения.161162Это можно использовать для создания оболочки для SQLite, как в следующем примере:163164```python165# Минимальная оболочка SQLite для экспериментов166167import sqlite3168169con = sqlite3.connect(":memory:")170con.isolation_level = None171cur = con.cursor()172173buffer = ""174175print("Enter your SQL commands to execute in sqlite3.")176print("Enter a blank line to exit.")177178while True:179    line = input()180    if line == "":181        break182    buffer += line183    if sqlite3.complete_statement(buffer):184        try:185            buffer = buffer.strip()186            cur.execute(buffer)187188            if buffer.lstrip().upper().startswith("SELECT"):189                print(cur.fetchall())190        except sqlite3.Error as e:191            print("An error occurred:", e.args[0])192        buffer = ""193194con.close()195```196197#### `sqlite3.enable_callback_tracebacks(flag)`198199По умолчанию никакие трассировки в пользовательских функциях, агрегатах, преобразователях, колбэках авторизатора и т.д. Чтобы их отладить, можно вызвать эту функцию, передав200201*flag*202203равным True. После этого трассировки от колбэков будут выводиться в204205`sys.stderr`206207. Используйте208209[`False`](https://python-all.ru/3.1/library/constants.html#False)210211, чтобы снова отключить эту возможность.212213## 11.6.2. Объекты соединения214215#### `class sqlite3.Connection`216217Соединение с базой данных SQLite обладает следующими атрибутами и методами:218219#### `Connection.isolation_level`220221Получает или устанавливает текущий уровень изоляции.222223[`None`](https://python-all.ru/3.1/library/constants.html#None)224225для режима автокоммита или одно из значений «DEFERRED», «IMMEDIATE» или «EXCLUSIVE». Подробное объяснение приведено в разделе226227[*Управление транзакциями*](https://python-all.ru/3.1/library/sqlite3.html#sqlite3-controlling-transactions)228229.230231#### `Connection.cursor([cursorClass])`232233Метод cursor принимает один необязательный параметр234235*cursorClass*236237. Если он указан, это должен быть пользовательский класс курсора, наследующий от238239[`sqlite3.Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor)240241.242243#### `Connection.commit()`244245Этот метод фиксирует текущую транзакцию. Если его не вызвать, все изменения, сделанные после последнего вызова246247`commit()`248249, не будут видны из других подключений к базе данных. Если вы не видите записанные данные, проверьте, не забыли ли вызвать этот метод.250251#### `Connection.rollback()`252253Этот метод откатывает все изменения в базе данных, сделанные после последнего вызова254255[`commit()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.commit)256257.258259#### `Connection.close()`260261Этот метод закрывает подключение к базе данных. Обратите внимание, что он не вызывает автоматически262263[`commit()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.commit)264265. Если просто закрыть подключение без предварительного вызова266267[`commit()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.commit)268269, изменения будут потеряны!270271#### `Connection.execute(sql[, parameters])`272273Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод274275[`execute`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.execute)276277этого курсора с переданными параметрами.278279#### `Connection.executemany(sql[, parameters])`280281Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод282283[`executemany`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.executemany)284285этого курсора с переданными параметрами.286287#### `Connection.executescript(sql_script)`288289Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод290291[`executescript`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.executescript)292293этого курсора с переданными параметрами.294295#### `Connection.create_function(name, num_params, func)`296297Создаёт пользовательскую функцию, которую затем можно использовать в SQL-выражениях под именем *name*. *num\_params* – количество параметров, принимаемых функцией, а *func* – вызываемый объект Python, который будет вызываться как SQL-функция.298299Функция может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float и None.300301Пример:302303```python304import sqlite3305import hashlib306307def md5sum(t):308    return hashlib.md5(t).hexdigest()309310con = sqlite3.connect(":memory:")311con.create_function("md5", 1, md5sum)312cur = con.cursor()313cur.execute("select md5(?)", ("foo",))314print(cur.fetchone()[0])315```316317#### `Connection.create_aggregate(name, num_params, aggregate_class)`318319Создаёт пользовательскую агрегатную функцию.320321Класс агрегатной функции должен реализовывать метод `step`, который принимает количество параметров *num\_params*, и метод `finalize`, который возвращает конечный результат агрегации.322323Метод `finalize` может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float и None.324325Пример:326327```python328import sqlite3329330class MySum:331    def __init__(self):332        self.count = 0333334    def step(self, value):335        self.count += value336337    def finalize(self):338        return self.count339340con = sqlite3.connect(":memory:")341con.create_aggregate("mysum", 1, MySum)342cur = con.cursor()343cur.execute("create table test(i)")344cur.execute("insert into test(i) values (1)")345cur.execute("insert into test(i) values (2)")346cur.execute("select mysum(i) from test")347print(cur.fetchone()[0])348```349350#### `Connection.create_collation(name, callable)`351352Создаёт collation с указанными *name* и *callable*. Вызываемый объект будет получать два строковых аргумента. Он должен возвращать -1, если первый меньше второго, 0, если они равны, и 1, если первый больше второго. Обратите внимание, что это управляет сортировкой (ORDER BY в SQL), поэтому сравнения не влияют на другие операции SQL.353354Обратите внимание, что вызываемый объект будет получать свои параметры в виде байтовых строк Python, которые обычно закодированы в UTF-8.355356Следующий пример показывает пользовательскую коллацию, которая сортирует «в обратном порядке»:357358```python359import sqlite3360361def collate_reverse(string1, string2):362    if string1 == string2:363        return 0364    elif string1 < string2:365        return 1366    else:367        return -1368369con = sqlite3.connect(":memory:")370con.create_collation("reverse", collate_reverse)371372cur = con.cursor()373cur.execute("create table test(x)")374cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])375cur.execute("select x from test order by x collate reverse")376for row in cur:377    print(row)378con.close()379```380381Чтобы удалить сортировку, вызовите `create_collation` с параметром callable равным None:382383```python384con.create_collation("reverse", None)385```386387#### `Connection.interrupt()`388389Этот метод можно вызвать из другого потока, чтобы прервать любые запросы, которые могут выполняться в подключении. Запрос будет прерван, а вызывающий код получит исключение.390391#### `Connection.set_authorizer(authorizer_callback)`392393Эта процедура регистрирует колбэк. Колбэк вызывается при каждой попытке доступа к столбцу таблицы в базе данных. Колбэк должен вернуть `SQLITE_OK`, если доступ разрешён, `SQLITE_DENY`, если весь SQL-запрос следует прервать с ошибкой, и `SQLITE_IGNORE`, если столбец следует рассматривать как NULL. Эти константы доступны в модуле `sqlite3`.394395Первый аргумент колбэка указывает, какой тип операции нужно авторизовать. Второй и третий аргументы будут аргументами или [`None`](https://python-all.ru/3.1/library/constants.html#None) в зависимости от первого аргумента. Четвёртый аргумент – это имя базы данных (например, «main», «temp»), если применимо. Пятый аргумент – это имя самого внутреннего триггера или представления, ответственного за попытку доступа, или [`None`](https://python-all.ru/3.1/library/constants.html#None), если эта попытка доступа происходит напрямую из входного SQL-кода.396397Обратитесь к документации SQLite за возможными значениями первого аргумента и значением второго и третьего аргументов в зависимости от первого. Все необходимые константы доступны в модуле `sqlite3`.398399#### `Connection.set_progress_handler(handler, n)`400401Эта процедура регистрирует колбэк. Колбэк вызывается каждые *n* инструкций виртуальной машины SQLite. Это полезно, если требуется получать вызовы от SQLite во время длительных операций, например для обновления графического интерфейса.402403Чтобы удалить ранее установленный обработчик хода выполнения, вызовите метод, передав [`None`](https://python-all.ru/3.1/library/constants.html#None) в качестве *handler*.404405#### `Connection.row_factory`406407Этот атрибут можно заменить на вызываемый объект, который принимает курсор и исходную строку в виде кортежа и возвращает реальную результирующую строку. Таким образом можно реализовать более продвинутые способы возврата результатов, например возврат объекта, который также может обращаться к столбцам по имени.408409Пример:410411```python412import sqlite3413414def dict_factory(cursor, row):415    d = {}416    for idx, col in enumerate(cursor.description):417        d[col[0]] = row[idx]418    return d419420con = sqlite3.connect(":memory:")421con.row_factory = dict_factory422cur = con.cursor()423cur.execute("select 1 as a")424print(cur.fetchone()["a"])425```426427Если возврата кортежа недостаточно и требуется доступ к столбцам по имени, рассмотрите возможность установки [`row_factory`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.row_factory) на высокооптимизированный тип [`sqlite3.Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row). [`Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row) предоставляет как доступ по индексу, так и регистронезависимый доступ по имени к столбцам с почти нулевыми накладными расходами памяти. Вероятно, это будет лучше, чем собственное решение на основе словаря или даже решения на основе db\_row.428429#### `Connection.text_factory`430431Используя этот атрибут, можно управлять тем, какие объекты возвращаются для типа данных `TEXT` . По умолчанию этот атрибут равен [`str`](https://python-all.ru/3.1/library/functions.html#str), и модуль `sqlite3` будет возвращать объекты Unicode для `TEXT`. Если вместо этого нужно возвращать байтовые строки, можно установить его в [`bytes`](https://python-all.ru/3.1/library/functions.html#bytes).432433Из соображений эффективности существует также способ возвращать объекты [`str`](https://python-all.ru/3.1/library/functions.html#str) только для не-ASCII данных, а в остальных случаях – [`bytes`](https://python-all.ru/3.1/library/functions.html#bytes). Чтобы активировать его, установите этот атрибут в `sqlite3.OptimizedUnicode`.434435Также можно установить его в любой другой вызываемый объект, принимающий единственный параметр – байтовую строку и возвращающий результирующий объект.436437См. следующий пример для иллюстрации:438439```python440import sqlite3441442con = sqlite3.connect(":memory:")443cur = con.cursor()444445# Создать таблицу446con.execute("create table person(lastname, firstname)")447448AUSTRIA = "\xd6sterreich"449450# по умолчанию строки возвращаются как Unicode451cur.execute("select ?", (AUSTRIA,))452row = cur.fetchone()453assert row[0] == AUSTRIA454455# но можно заставить sqlite3 всегда возвращать байтовые строки...456con.text_factory = str457cur.execute("select ?", (AUSTRIA,))458row = cur.fetchone()459assert type(row[0]) == str460# байтовые строки будут в кодировке UTF-8, если только в базу не попал мусор461# база данных...462assert row[0] == AUSTRIA.encode("utf-8")463464# можно также реализовать собственный text_factory...465# здесь мы реализуем такой, который будет игнорировать символы Unicode, которые не могут быть466# декодированы из UTF-8467con.text_factory = lambda x: str(x, "utf-8", "ignore")468cur.execute("select ?", ("this is latin1 and would normally create errors" +469                         "\xe4\xf6\xfc".encode("latin1"),))470row = cur.fetchone()471assert type(row[0]) == str472473# sqlite3 предлагает встроенную оптимизированную text_factory, которая возвращает байтовые строки474# объекты, если данные только в ASCII, и в противном случае возвращает объекты Unicode475con.text_factory = sqlite3.OptimizedUnicode476cur.execute("select ?", (AUSTRIA,))477row = cur.fetchone()478assert type(row[0]) == str479480cur.execute("select ?", ("Germany",))481row = cur.fetchone()482assert type(row[0]) == str483```484485#### `Connection.total_changes`486487Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия подключения к базе данных.488489#### `Connection.iterdump`490491Возвращает итератор для дампа базы данных в формате SQL-текста. Полезно при сохранении базы данных в памяти для последующего восстановления. Эта функция предоставляет те же возможности, что и команда `.dump` в оболочке **sqlite3**.492493Пример:494495```python496# Преобразовать файл existing_db.db в SQL-дамп dump.sql497import sqlite3, os498499con = sqlite3.connect('existing_db.db')500with open('dump.sql', 'w') as f:501    for line in con.iterdump():502        f.write('%s\n' % line)503```504505## 11.6.3. Объекты Cursor506507#### `class sqlite3.Cursor`508509Экземпляр510511[`Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor)512513имеет следующие атрибуты и методы.514515#### `Cursor.execute(sql[, parameters])`516517Выполняет SQL-оператор. SQL-оператор может быть параметризован (т.е. использовать плейсхолдеры вместо литералов SQL). Модуль `sqlite3` поддерживает два вида плейсхолдеров: вопросительные знаки (стиль qmark) и именованные плейсхолдеры (именованный стиль).518519Этот пример показывает, как использовать параметры со стилем qmark:520521```python522import sqlite3523524con = sqlite3.connect("mydb")525526cur = con.cursor()527528who = "Yeltsin"529age = 72530531cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))532print(cur.fetchone())533```534535Этот пример показывает, как использовать именованный стиль:536537```python538import sqlite3539540con = sqlite3.connect("mydb")541542cur = con.cursor()543544who = "Yeltsin"545age = 72546547cur.execute("select name_last, age from people where name_last=:who and age=:age",548    {"who": who, "age": age})549print(cur.fetchone())550```551552[`execute()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.execute) выполнит только один SQL-оператор. При попытке выполнить с его помощью более одного оператора будет выдано предупреждение. Используйте [`executescript()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.executescript), если требуется выполнить несколько операторов SQL одним вызовом.553554#### `Cursor.executemany(sql, seq_of_parameters)`555556Выполняет SQL-команду для всех последовательностей параметров или отображений, найденных в последовательности *sql*. Модуль `sqlite3` также позволяет использовать [*итератор*](https://python-all.ru/3.1/glossary.html#term-iterator), возвращающий параметры, вместо последовательности.557558```python559import sqlite3560561class IterChars:562    def __init__(self):563        self.count = ord('a')564565    def __iter__(self):566        return self567568    def __next__(self):569        if self.count > ord('z'):570            raise StopIteration571        self.count += 1572        return (chr(self.count - 1),) # это кортеж из одного элемента573574con = sqlite3.connect(":memory:")575cur = con.cursor()576cur.execute("create table characters(c)")577578theIter = IterChars()579cur.executemany("insert into characters(c) values (?)", theIter)580581cur.execute("select c from characters")582print(cur.fetchall())583```584585Вот более короткий пример с использованием [*генератора*](https://python-all.ru/3.1/glossary.html#term-generator):586587```python588import sqlite3589590def char_generator():591    import string592    for c in string.letters[:26]:593        yield (c,)594595con = sqlite3.connect(":memory:")596cur = con.cursor()597cur.execute("create table characters(c)")598599cur.executemany("insert into characters(c) values (?)", char_generator())600601cur.execute("select c from characters")602print(cur.fetchall())603```604605#### `Cursor.executescript(sql_script)`606607Это нестандартный удобный метод для выполнения нескольких SQL-операторов за один раз. Сначала он выдаёт оператор `COMMIT`, а затем выполняет SQL-скрипт, полученный в качестве параметра.608609*sql\_script* может быть экземпляром [`str`](https://python-all.ru/3.1/library/functions.html#str) или [`bytes`](https://python-all.ru/3.1/library/functions.html#bytes).610611Пример:612613```python614import sqlite3615616con = sqlite3.connect(":memory:")617cur = con.cursor()618cur.executescript("""619    create table person(620        firstname,621        lastname,622        age623    );624625    create table book(626        title,627        author,628        published629    );630631    insert into book(title, author, published)632    values (633        'Dirk Gently''s Holistic Detective Agency',634        'Douglas Adams',635        1987636    );637    """)638```639640#### `Cursor.fetchone()`641642Извлекает следующую строку результирующего набора запроса, возвращая одну последовательность или643644[`None`](https://python-all.ru/3.1/library/constants.html#None)645646, когда данные закончились.647648#### `Cursor.fetchmany([size=cursor.arraysize])`649650Извлекает следующий набор строк результата запроса, возвращая список. Пустой список возвращается, когда строк больше нет.651652Количество строк, извлекаемых за один вызов, задаётся параметром *size*. Если он не указан, количество строк определяется атрибутом arraysize курсора. Метод должен пытаться извлечь столько строк, сколько указано параметром size. Если это невозможно из-за того, что указанное количество строк недоступно, может быть возвращено меньше строк.653654Обратите внимание на вопросы производительности, связанные с параметром *size*. Для оптимальной производительности обычно лучше использовать атрибут arraysize. Если используется параметр *size*, то желательно, чтобы его значение оставалось неизменным от одного вызова [`fetchmany()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.fetchmany) к следующему.655656#### `Cursor.fetchall()`657658Извлекает все (оставшиеся) строки результата запроса, возвращая список. Обратите внимание, что атрибут arraysize курсора может влиять на производительность этой операции. Пустой список возвращается, если строк нет.659660#### `Cursor.rowcount`661662Хотя класс [`Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor) модуля `sqlite3` реализует этот атрибут, собственная поддержка определения «затронутых строк»/«выбранных строк» в движке базы данных работает причудливо.663664Для операторов `DELETE` SQLite сообщает [`rowcount`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.rowcount) как 0, если выполняется `DELETE FROM table` без каких-либо условий.665666Для инструкций [`executemany()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.executemany) количество изменений суммируется в [`rowcount`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.rowcount).667668В соответствии с требованиями спецификации Python DB API, атрибут [`rowcount`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.rowcount) «равен -1, если для курсора не выполнялось `executeXX()` или количество строк последней операции не может быть определено интерфейсом».669670Это включает операторы `SELECT`, поскольку нельзя определить количество строк, возвращённых запросом, пока не будут получены все строки.671672#### `Cursor.lastrowid`673674Этот доступный только для чтения атрибут возвращает rowid последней изменённой строки. Он устанавливается только в том случае, если с помощью метода675676[`execute()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.execute)677678был выполнен оператор679680`INSERT`681682. Для операций, отличных от683684`INSERT`685686, или при вызове687688[`executemany()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.executemany)689690, значение691692[`lastrowid`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.lastrowid)693694устанавливается равным695696[`None`](https://python-all.ru/3.1/library/constants.html#None)697698.699700#### `Cursor.description`701702Этот атрибут только для чтения возвращает имена столбцов последнего запроса. Для совместимости с Python DB API он возвращает для каждого столбца кортеж из 7 элементов, где последние шесть элементов каждого кортежа равны [`None`](https://python-all.ru/3.1/library/constants.html#None).703704Он также устанавливается для инструкций `SELECT`, даже если не найдено ни одной строки.705706## 11.6.4. Объекты Row707708#### `class sqlite3.Row`709710Экземпляр [`Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row) служит высокооптимизированной [`row_factory`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.row_factory) для объектов [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection). По большинству своих характеристик он имитирует кортеж.711712Он поддерживает доступ по имени столбца и индексу (как отображение), итерацию, представление, проверку на равенство и [`len()`](https://python-all.ru/3.1/library/functions.html#len).713714Если у двух объектов [`Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row) одинаковые столбцы и их элементы равны, то эти объекты считаются равными.715716#### `keys()`717718Этот метод возвращает кортеж имён столбцов. Сразу после выполнения запроса он становится первым элементом каждого кортежа в719720[`Cursor.description`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor.description)721722.723724Предположим, мы инициализируем таблицу, как в приведённом выше примере:725726```python727conn = sqlite3.connect(":memory:")728c = conn.cursor()729c.execute('''create table stocks730(date text, trans text, symbol text,731 qty real, price real)''')732c.execute("""insert into stocks733          values ('2006-01-05','BUY','RHAT',100,35.14)""")734conn.commit()735c.close()736```737738Теперь подключаем [`Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row):739740```python741>>> conn.row_factory = sqlite3.Row742>>> c = conn.cursor()743>>> c.execute('select * from stocks')744<sqlite3.Cursor object at 0x7f4e7dd8fa80>745>>> r = c.fetchone()746>>> type(r)747<class 'sqlite3.Row'>748>>> tuple(r)749('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)750>>> len(r)7515752>>> r[2]753'RHAT'754>>> r.keys()755['date', 'trans', 'symbol', 'qty', 'price']756>>> r['qty']757100.0758>>> for member in r:759...     print(member)760...7612006-01-05762BUY763RHAT764100.076535.14766```767768## 11.6.5. SQLite и типы Python769770### 11.6.5.1. Введение771772SQLite изначально поддерживает следующие типы: `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`.773774Следующие типы Python можно передавать в SQLite без каких-либо проблем:775776| Тип Python | Тип SQLite |777| --- | --- |778| [`None`](https://python-all.ru/3.1/library/constants.html#None) | `NULL` |779| [`int`](https://python-all.ru/3.1/library/functions.html#int) | `INTEGER` |780| [`float`](https://python-all.ru/3.1/library/functions.html#float) | `REAL` |781| [`str`](https://python-all.ru/3.1/library/functions.html#str) | `TEXT` |782| [`bytes`](https://python-all.ru/3.1/library/functions.html#bytes) | `BLOB` |783784Так по умолчанию типы SQLite преобразуются в типы Python:785786| Тип SQLite | Тип Python |787| --- | --- |788| `NULL` | [`None`](https://python-all.ru/3.1/library/constants.html#None) |789| `INTEGER` | [`int`](https://python-all.ru/3.1/library/functions.html#int) |790| `REAL` | [`float`](https://python-all.ru/3.1/library/functions.html#float) |791| `TEXT` | зависит от text\_factory, по умолчанию str |792| `BLOB` | [`bytes`](https://python-all.ru/3.1/library/functions.html#bytes) |793794Система типов модуля `sqlite3` расширяема двумя способами: можно хранить дополнительные типы Python в базе данных SQLite с помощью адаптации объектов, а также можно настроить модуль `sqlite3` на преобразование типов SQLite в разные типы Python с помощью конвертеров.795796### 11.6.5.2. Использование адаптеров для хранения дополнительных типов Python в базах данных SQLite797798Как описывалось ранее, SQLite изначально поддерживает только ограниченный набор типов. Чтобы использовать другие типы Python с SQLite, необходимо **адаптировать** их к одному из поддерживаемых модулем sqlite3 типов для SQLite: NoneType, int, float, str, bytes.799800Модуль `sqlite3` использует адаптацию объектов Python, как описано в [**PEP 246**](https://python-all.ru/3.1/library/sqlite3.html). Используемый протокол – `PrepareProtocol`.801802Есть два способа настроить модуль `sqlite3` на адаптацию пользовательского типа Python к одному из поддерживаемых.803804#### 11.6.5.2.1. Адаптация объекта самостоятельно805806Это хороший подход, если вы пишете класс сами. Предположим, у вас есть такой класс:807808```python809class Point:810    def __init__(self, x, y):811        self.x, self.y = x, y812```813814Теперь необходимо сохранить точку в одном столбце SQLite. Сначала нужно выбрать один из поддерживаемых типов для представления точки. Используем str и разделим координаты точкой с запятой. Затем классу нужно дать метод `__conform__(self, протокол)`, который должен возвращать преобразованное значение. Параметр *протокол* будет `PrepareProtocol`.815816```python817import sqlite3818819class Point:820    def __init__(self, x, y):821        self.x, self.y = x, y822823    def __conform__(self, protocol):824        if protocol is sqlite3.PrepareProtocol:825            return "%f;%f" % (self.x, self.y)826827con = sqlite3.connect(":memory:")828cur = con.cursor()829830p = Point(4.0, -3.2)831cur.execute("select ?", (p,))832print(cur.fetchone()[0])833```834835#### 11.6.5.2.2. Регистрация вызываемого адаптера836837Другая возможность – создать функцию, которая преобразует тип в строковое представление, и зарегистрировать её с помощью [`register_adapter()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.register_adapter).838839```python840import sqlite3841842class Point:843    def __init__(self, x, y):844        self.x, self.y = x, y845846def adapt_point(point):847    return "%f;%f" % (point.x, point.y)848849sqlite3.register_adapter(Point, adapt_point)850851con = sqlite3.connect(":memory:")852cur = con.cursor()853854p = Point(4.0, -3.2)855cur.execute("select ?", (p,))856print(cur.fetchone()[0])857```858859Модуль `sqlite3` имеет два адаптера по умолчанию для встроенных типов Python [`datetime.date`](https://python-all.ru/3.1/library/datetime.html#datetime.date) и [`datetime.datetime`](https://python-all.ru/3.1/library/datetime.html#datetime.datetime). Теперь предположим, что мы хотим хранить объекты [`datetime.datetime`](https://python-all.ru/3.1/library/datetime.html#datetime.datetime) не в представлении ISO, а в виде метки времени Unix.860861```python862import sqlite3863import datetime, time864865def adapt_datetime(ts):866    return time.mktime(ts.timetuple())867868sqlite3.register_adapter(datetime.datetime, adapt_datetime)869870con = sqlite3.connect(":memory:")871cur = con.cursor()872873now = datetime.datetime.now()874cur.execute("select ?", (now,))875print(cur.fetchone()[0])876```877878### 11.6.5.3. Преобразование значений SQLite в пользовательские типы Python879880Написание адаптера позволяет отправлять пользовательские типы Python в SQLite. Но чтобы сделать это действительно полезным, нужно обеспечить обратное преобразование из SQLite в Python.881882Вступают конвертеры.883884Вернёмся к классу `Point`. Мы сохранили координаты x и y, разделённые точкой с запятой, в виде строк в SQLite.885886Сначала определим функцию-конвертер, которая принимает строку в качестве параметра и создаёт объект `Point` из неё.887888> **Примечание**889>890> Функции преобразования **всегда** вызываются со строкой, независимо от того, под каким tипом данных значение было отправлено в SQLite.891892```python893def convert_point(s):894    x, y = map(float, s.split(";"))895    return Point(x, y)896```897898Теперь нужно сообщить модулю `sqlite3`, что выбираемые из базы данных данные на самом деле являются точкой. Есть два способа это сделать:899900- Неявно, через объявленный тип901- Явно, по имени колонки902903Оба способа описаны в разделе [*Функции и константы модуля*](https://python-all.ru/3.1/library/sqlite3.html#sqlite3-module-contents), в записях для констант [`PARSE_DECLTYPES`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.PARSE_COLNAMES).904905Следующий пример иллюстрирует оба подхода.906907```python908import sqlite3909910class Point:911    def __init__(self, x, y):912        self.x, self.y = x, y913914    def __repr__(self):915        return "(%f;%f)" % (self.x, self.y)916917def adapt_point(point):918    return "%f;%f" % (point.x, point.y)919920def convert_point(s):921    x, y = list(map(float, s.split(";")))922    return Point(x, y)923924# Зарегистрировать адаптер925sqlite3.register_adapter(Point, adapt_point)926927# Зарегистрировать конвертер928sqlite3.register_converter("point", convert_point)929930p = Point(4.0, -3.2)931932#########################933# 1) Использование объявленных типов934con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)935cur = con.cursor()936cur.execute("create table test(p point)")937938cur.execute("insert into test(p) values (?)", (p,))939cur.execute("select p from test")940print("with declared types:", cur.fetchone()[0])941cur.close()942con.close()943944#######################945# 1) Использование имён столбцов946con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)947cur = con.cursor()948cur.execute("create table test(p)")949950cur.execute("insert into test(p) values (?)", (p,))951cur.execute('select p as "p [point]" from test')952print("with column names:", cur.fetchone()[0])953cur.close()954con.close()955```956957### 11.6.5.4. Стандартные адаптеры и преобразователи958959В модуле datetime есть адаптеры по умолчанию для типов date и datetime. Они отправляются в SQLite как даты/временные метки в формате ISO.960961Конвертеры по умолчанию зарегистрированы под именем “date” для [`datetime.date`](https://python-all.ru/3.1/library/datetime.html#datetime.date) и под именем “timestamp” для [`datetime.datetime`](https://python-all.ru/3.1/library/datetime.html#datetime.datetime).962963Таким образом, в большинстве случаев можно использовать даты и временные метки из Python без лишних усилий. Формат адаптеров также совместим с экспериментальными функциями даты/времени SQLite.964965Следующий пример демонстрирует это.966967```python968import sqlite3969import datetime970971con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)972cur = con.cursor()973cur.execute("create table test(d date, ts timestamp)")974975today = datetime.date.today()976now = datetime.datetime.now()977978cur.execute("insert into test(d, ts) values (?, ?)", (today, now))979cur.execute("select d, ts from test")980row = cur.fetchone()981print(today, "=>", row[0], type(row[0]))982print(now, "=>", row[1], type(row[1]))983984cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')985row = cur.fetchone()986print("current_date", row[0], type(row[0]))987print("current_timestamp", row[1], type(row[1]))988```989990## 11.6.6. Управление транзакциями991992По умолчанию модуль `sqlite3` открывает транзакции неявно перед оператором языка манипуляции данными (DML) (т. е. `INSERT`/`UPDATE`/`DELETE`/`REPLACE`) и фиксирует транзакции неявно перед оператором, не являющимся DML или запросом (т. е. всем, кроме `SELECT` или упомянутых ранее).993994Таким образом, если вы находитесь в транзакции и выполняете команду, например, `CREATE TABLE ...`, `VACUUM`, `PRAGMA`, модуль `sqlite3` неявно зафиксирует транзакцию перед выполнением этой команды. Для этого есть две причины. Первая – некоторые из этих команд не работают внутри транзакций. Вторая причина в том, что sqlite3 необходимо отслеживать состояние транзакции (активна транзакция или нет).995996Можно управлять тем, какие именно операторы `BEGIN` sqlite3 выполняет неявно (или не выполнять их вовсе) с помощью параметра *isolation\_level* вызова [`connect()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.connect), или с помощью свойства `isolation_level` соединений.997998Если нужен **режим автокоммита**, установите `isolation_level` в None.9991000В противном случае оставьте его по умолчанию, что приведет к простой инструкции “BEGIN”, или установите одно из поддерживаемых уровней изоляции SQLite: “DEFERRED”, “IMMEDIATE” или “EXCLUSIVE”.10011002## 11.6.7. Эффективное использование `sqlite3`10031004### 11.6.7.1. Использование сокращённых методов10051006Используя нестандартные методы `execute()`, `executemany()` и `executescript()` объекта [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection), код можно написать более сжато, поскольку не нужно явно создавать (часто избыточные) объекты [`Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor). Вместо этого объекты [`Cursor`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Cursor) создаются неявно, и эти сокращённые методы возвращают объекты курсоров. Таким образом, можно выполнить оператор `SELECT` и итерироваться по нему напрямую, используя всего один вызов объекта [`Connection`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection).10071008```python1009import sqlite310101011persons = [1012    ("Hugo", "Boss"),1013    ("Calvin", "Klein")1014    ]10151016con = sqlite3.connect(":memory:")10171018# Создать таблицу1019con.execute("create table person(firstname, lastname)")10201021# Заполнить таблицу1022con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)10231024# Выводит содержимое таблицы.1025for row in con.execute("select firstname, lastname from person"):1026    print(row)10271028# Используется фиктивное условие WHERE, чтобы SQLite не применил сокращённое удаление таблицы.1029print("I just deleted", con.execute("delete from person where 1=1").rowcount, "rows")1030```10311032### 11.6.7.2. Доступ к столбцам по имени вместо индекса10331034Одна из полезных возможностей модуля `sqlite3` – встроенный класс [`sqlite3.Row`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Row), предназначенный для использования в качестве фабрики строк.10351036Строки, обёрнутые этим классом, можно получать как по индексу (как кортежи), так и по имени без учёта регистра:10371038```python1039import sqlite310401041con = sqlite3.connect("mydb")1042con.row_factory = sqlite3.Row10431044cur = con.cursor()1045cur.execute("select name_last, age from people")1046for row in cur:1047    assert row[0] == row["name_last"]1048    assert row["name_last"] == row["nAmE_lAsT"]1049    assert row[1] == row["age"]1050    assert row[1] == row["AgE"]1051```10521053### 11.6.7.3. Использование соединения как менеджера контекста10541055Объекты соединения можно использовать как менеджеры контекста, которые автоматически фиксируют или откатывают транзакции. В случае исключения транзакция откатывается; в противном случае транзакция фиксируется:10561057```python1058import sqlite310591060con = sqlite3.connect(":memory:")1061con.execute("create table person (id integer primary key, firstname varchar unique)")10621063# При успешном выполнении con.commit() вызывается автоматически.1064with con:1065    con.execute("insert into person(firstname) values (?)", ("Joe",))10661067# con.rollback() вызывается после завершения блока with с исключением,1068# исключение всё равно возбуждается и должно быть перехвачено1069try:1070    with con:1071        con.execute("insert into person(firstname) values (?)", ("Joe",))1072except sqlite3.IntegrityError:1073    print("couldn't add Joe twice")1074```10751076## 11.6.8. Типичные проблемы10771078### 11.6.8.1. Многопоточность10791080В старых версиях SQLite были проблемы с совместным использованием соединений между потоками. Именно поэтому модуль Python запрещает совместное использование соединений и курсоров между потоками. Если всё же попытаться это сделать, во время выполнения будет возбуждено исключение.10811082Единственное исключение – вызов метода [`interrupt()`](https://python-all.ru/3.1/library/sqlite3.html#sqlite3.Connection.interrupt), который имеет смысл вызывать только из другого потока.1083