`sqlite3.md`

1070 строк · 60.8 КБ · обычная страница · сырой текст · скачать
1> **Источник:** https://python-all.ru/3.0/library/sqlite3.html2>3> «Документация Python на русском» – неофициальный перевод официальной документации Python: версии от 2.6 до 3.16, полнотекстовый поиск, английский оригинал рядом с переводом. Эта Markdown-версия страницы предназначена для работы с LLM: вставьте её в ChatGPT, Claude или Cursor.45---67# `sqlite3` – интерфейс DB-API 2.0 для баз данных SQLite89SQLite – это библиотека на C, которая предоставляет легковесную дисковую базу данных, не требующую отдельного серверного процесса, и позволяет обращаться к базе данных с помощью нестандартного варианта языка SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также можно создать прототип приложения на SQLite, а затем перенести код на более крупную базу данных, например PostgreSQL или Oracle.1011pysqlite был написан Gerhard Häring и предоставляет SQL-интерфейс, совместимый со спецификацией DB-API 2.0, описанной в [**PEP 249**](https://python-all.ru/3.0/library/sqlite3.html).1213Чтобы использовать модуль, сначала необходимо создать объект [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection), представляющий базу данных. Здесь данные будут храниться в файле `/tmp/example`:1415```python16conn = sqlite3.connect('/tmp/example')17```1819Можно также указать специальное имя `:memory:`, чтобы создать базу данных в оперативной памяти.2021Когда у вас есть объект [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection), можно создать объект [`Cursor`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor) и вызывать его метод [`execute()`](https://python-all.ru/3.0/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.0/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.0/glossary.html#term-iterator), вызывать его метод [`fetchone()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.fetchone) для получения одной подходящей строки, либо вызвать [`fetchall()`](https://python-all.ru/3.0/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(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)74(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)75(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)76(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)77>>>78```7980> **См. также**81>82> **[http://www.pysqlite.org](https://python-all.ru/3.0/library/sqlite3.html)**83>84> Веб-страница pysqlite.85>86> **[http://www.sqlite.org](https://python-all.ru/3.0/library/sqlite3.html)**87>88> Веб-страница SQLite; документация описывает синтаксис и доступные типы данных для поддерживаемого диалекта SQL.89>90> **[**PEP 249**](https://python-all.ru/3.0/library/sqlite3.html) – спецификация API баз данных 2.0**91>92> PEP написан Марком-Андре Лембургом.9394## Функции и константы модуля9596#### `[sqlite3.PARSE_DECLTYPES]sqlite3.PARSE_DECLTYPES`9798Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.connect).99100При установке этого флага модуль `sqlite3` анализирует объявленный тип для каждого возвращаемого столбца. Он извлекает первое слово объявленного типа, например, для «integer primary key» извлекается «integer», а для «number(10)» – «number». Затем для этого столбца модуль обращается к словарю преобразователей и использует зарегистрированную для этого типа функцию преобразования.101102#### `[sqlite3.PARSE_COLNAMES]sqlite3.PARSE_COLNAMES`103104Эта константа предназначена для использования с параметром *detect\_types* функции [`connect()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.connect).105106Если установить этот флаг, интерфейс SQLite будет анализировать имя столбца для каждого возвращаемого столбца. Он будет искать строку вида \[mytype\] в имени столбца и затем считать, что «mytype» – это тип столбца. Далее будет выполнена попытка найти запись «mytype» в словаре конвертеров и используется найденная функция преобразования для возврата значения. Имя столбца, полученное из [`Cursor.description`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.description), – это только первое слово имени столбца; то есть если в SQL используется что-то вроде `'as "x [datetime]"'`, то будет извлечено всё до первого пробела: имя столбца будет просто «x».107108#### `[sqlite3.connect]sqlite3.connect(database[, timeout, isolation_level, detect_types, factory])`109110Открывает соединение с файлом базы данных SQLite *database*. Можно использовать `":memory:"`, чтобы открыть соединение с базой данных, расположенной в оперативной памяти, а не на диске.111112Когда к базе данных обращаются несколько соединений и один из процессов изменяет базу данных, база данных SQLite блокируется до завершения этой транзакции. Параметр *timeout* задаёт, как долго соединение должно ждать снятия блокировки, прежде чем возникнет исключение. Значение по умолчанию для параметра timeout – 5.0 (пять секунд).113114По поводу параметра *isolation\_level* обратитесь к свойству [`Connection.isolation_level`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.isolation_level) объектов [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection).115116SQLite изначально поддерживает только типы TEXT, INTEGER, FLOAT, BLOB и NULL. Если вы хотите использовать другие типы, вам необходимо добавить поддержку для них самостоятельно. Параметр *detect\_types* и использование пользовательских **преобразователей**, зарегистрированных с помощью функции модульного уровня [`register_converter()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.register_converter), позволяют легко это сделать.117118Параметр *detect\_types* по умолчанию равен 0 (т.е. выключен, определение типов отключено); его можно установить в любую комбинацию [`PARSE_DECLTYPES`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.PARSE_COLNAMES) для включения определения типов.119120По умолчанию модуль `sqlite3` использует свой класс [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection) для вызова connect(). Однако можно создать подкласс [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection) и заставить [`connect()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.connect) использовать ваш класс, передав его в параметре *factory*.121122Подробности см. в разделе [*Типы SQLite и Python*](https://python-all.ru/3.0/library/sqlite3.html#sqlite3-types) данного руководства.123124Модуль `sqlite3` внутри использует кеш запросов, чтобы избежать накладных расходов на разбор SQL. Если требуется явно указать количество кешируемых для соединения запросов, можно задать параметр *cached\_statements*. Текущая реализация по умолчанию кеширует 100 запросов.125126#### `[sqlite3.register_converter]sqlite3.register_converter(typename, callable)`127128Регистрирует вызываемый объект для преобразования байтовой строки из базы данных в пользовательский тип Python. Этот вызываемый объект будет вызываться для всех значений базы данных, имеющих тип129130*typename*131132. О том, как работает определение типа, см. параметр133134*detect\_types*135136функции137138[`connect()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.connect)139140. Обратите внимание, что регистр символов в141142*typename*143144и имени типа в запросе должны совпадать!145146#### `[sqlite3.register_adapter]sqlite3.register_adapter(type, callable)`147148Регистрирует вызываемый объект для преобразования пользовательского типа Python149150*type*151152в один из поддерживаемых типов SQLite. Вызываемый объект153154*callable*155156принимает единственный параметр – значение Python – и должен возвращать значение одного из следующих типов: int, float, str, bytes (в кодировке UTF-8) или buffer.157158#### `[sqlite3.complete_statement]sqlite3.complete_statement(sql)`159160Возвращает [`True`](https://python-all.ru/3.0/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]sqlite3.enable_callback_tracebacks(flag)`198199По умолчанию никакие трассировки в пользовательских функциях, агрегатах, преобразователях, колбэках авторизатора и т.д. Чтобы их отладить, можно вызвать эту функцию, передав200201*flag*202203равным True. После этого трассировки от колбэков будут выводиться в204205`sys.stderr`206207. Используйте208209[`False`](https://python-all.ru/3.0/library/constants.html#False)210211, чтобы снова отключить эту возможность.212213## Объекты подключения214215#### `[sqlite3.Connection]class sqlite3.Connection`216217Соединение с базой данных SQLite обладает следующими атрибутами и методами:218219#### `[sqlite3.Connection.isolation_level]Connection.isolation_level`220221Получает или устанавливает текущий уровень изоляции.222223[`None`](https://python-all.ru/3.0/library/constants.html#None)224225для режима автокоммита или одно из значений «DEFERRED», «IMMEDIATE» или «EXCLUSIVE». Подробное объяснение приведено в разделе226227[*Управление транзакциями*](https://python-all.ru/3.0/library/sqlite3.html#sqlite3-controlling-transactions)228229.230231#### `[sqlite3.Connection.cursor]Connection.cursor([cursorClass])`232233Метод cursor принимает один необязательный параметр234235*cursorClass*236237. Если он указан, это должен быть пользовательский класс курсора, наследующий от238239[`sqlite3.Cursor`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor)240241.242243#### `[sqlite3.Connection.commit]Connection.commit()`244245Этот метод фиксирует текущую транзакцию. Если его не вызвать, все изменения, сделанные после последнего вызова246247`commit()`248249, не будут видны из других подключений к базе данных. Если вы не видите записанные данные, проверьте, не забыли ли вызвать этот метод.250251#### `[sqlite3.Connection.rollback]Connection.rollback()`252253Этот метод откатывает все изменения в базе данных, сделанные после последнего вызова254255[`commit()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.commit)256257.258259#### `[sqlite3.Connection.close]Connection.close()`260261Этот метод закрывает подключение к базе данных. Обратите внимание, что он не вызывает автоматически262263[`commit()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.commit)264265. Если просто закрыть подключение без предварительного вызова266267[`commit()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.commit)268269, изменения будут потеряны!270271#### `[sqlite3.Connection.execute]Connection.execute(sql[, parameters])`272273Это нестандартное сокращение, которое создаёт промежуточный объект курсора путём вызова метода cursor, а затем вызывает метод274275[`execute()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.execute)276277курсора с переданными параметрами.278279#### `[sqlite3.Connection.executemany]Connection.executemany(sql[, parameters])`280281Это нестандартное сокращение, которое создаёт промежуточный объект курсора путём вызова метода cursor, а затем вызывает метод282283[`executemany()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.executemany)284285курсора с переданными параметрами.286287#### `[sqlite3.Connection.executescript]Connection.executescript(sql_script)`288289Это нестандартное сокращение, которое создаёт промежуточный объект курсора путём вызова метода cursor, а затем вызывает метод290291[`executescript()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.executescript)292293курсора с переданными параметрами.294295#### `[sqlite3.Connection.create_function]Connection.create_function(name, num_params, func)`296297Создаёт пользовательскую функцию, которую затем можно использовать в SQL-выражениях под именем *name*. *num\_params* – количество параметров, принимаемых функцией, а *func* – вызываемый объект Python, который будет вызываться как SQL-функция.298299Функция может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float, buffer и 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#### `[sqlite3.Connection.create_aggregate]Connection.create_aggregate(name, num_params, aggregate_class)`318319Создаёт пользовательскую агрегатную функцию.320321Класс агрегатной функции должен реализовывать метод `step`, который принимает количество параметров *num\_params*, и метод `finalize`, который возвращает конечный результат агрегации.322323Метод `finalize` может возвращать любой из типов, поддерживаемых SQLite: bytes, str, int, float, buffer и 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#### `[sqlite3.Connection.create_collation]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#### `[sqlite3.Connection.interrupt]Connection.interrupt()`388389Этот метод можно вызвать из другого потока, чтобы прервать любые запросы, которые могут выполняться в подключении. Запрос будет прерван, а вызывающий код получит исключение.390391#### `[sqlite3.Connection.set_authorizer]Connection.set_authorizer(authorizer_callback)`392393Эта процедура регистрирует колбэк. Колбэк вызывается при каждой попытке доступа к столбцу таблицы в базе данных. Колбэк должен вернуть `SQLITE_OK`, если доступ разрешён, `SQLITE_DENY`, если весь SQL-запрос следует прервать с ошибкой, и `SQLITE_IGNORE`, если столбец следует рассматривать как NULL. Эти константы доступны в модуле `sqlite3`.394395Первый аргумент колбэка указывает, какой тип операции нужно авторизовать. Второй и третий аргументы будут аргументами или [`None`](https://python-all.ru/3.0/library/constants.html#None) в зависимости от первого аргумента. Четвёртый аргумент – это имя базы данных (например, «main», «temp»), если применимо. Пятый аргумент – это имя самого внутреннего триггера или представления, ответственного за попытку доступа, или [`None`](https://python-all.ru/3.0/library/constants.html#None), если эта попытка доступа происходит напрямую из входного SQL-кода.396397Обратитесь к документации SQLite за возможными значениями первого аргумента и значением второго и третьего аргументов в зависимости от первого. Все необходимые константы доступны в модуле `sqlite3`.398399#### `[sqlite3.Connection.set_progress_handler]Connection.set_progress_handler(handler, n)`400401Эта процедура регистрирует колбэк. Колбэк вызывается каждые *n* инструкций виртуальной машины SQLite. Это полезно, если требуется получать вызовы от SQLite во время длительных операций, например для обновления графического интерфейса.402403Чтобы удалить ранее установленный обработчик хода выполнения, вызовите метод, передав [`None`](https://python-all.ru/3.0/library/constants.html#None) в качестве *handler*.404405#### `[sqlite3.Connection.row_factory]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.0/library/sqlite3.html#sqlite3.Connection.row_factory) на высокооптимизированный тип [`sqlite3.Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row). [`Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row) предоставляет как доступ по индексу, так и регистронезависимый доступ по имени к столбцам с почти нулевыми накладными расходами памяти. Вероятно, это будет лучше, чем собственное решение на основе словаря или даже решения на основе db\_row.428429#### `[sqlite3.Connection.text_factory]Connection.text_factory`430431Используя этот атрибут, можно управлять тем, какие объекты возвращаются для типа данных `TEXT` . По умолчанию этот атрибут равен [`str`](https://python-all.ru/3.0/library/functions.html#str), и модуль `sqlite3` будет возвращать объекты Unicode для `TEXT`. Если вместо этого нужно возвращать байтовые строки, можно установить его в [`bytes`](https://python-all.ru/3.0/library/functions.html#bytes).432433Из соображений эффективности существует также способ возвращать объекты [`str`](https://python-all.ru/3.0/library/functions.html#str) только для не-ASCII данных, а в остальных случаях – [`bytes`](https://python-all.ru/3.0/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# но мы можем заставить pysqlite всегда возвращать байтовые строки ...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" + "\xe4\xf6\xfc".encode("latin1"),))469row = cur.fetchone()470assert type(row[0]) == str471472# pysqlite предлагает встроенную оптимизированную text_factory, которая возвращает байтовую строку473# объекты, если данные только в ASCII, и в противном случае возвращает объекты Unicode474con.text_factory = sqlite3.OptimizedUnicode475cur.execute("select ?", (AUSTRIA,))476row = cur.fetchone()477assert type(row[0]) == str478479cur.execute("select ?", ("Germany",))480row = cur.fetchone()481assert type(row[0]) == str482```483484#### `[sqlite3.Connection.total_changes]Connection.total_changes`485486Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия подключения к базе данных.487488#### `[sqlite3.Connection.iterdump]Connection.iterdump`489490Возвращает итератор для дампа базы данных в формате SQL-текста. Полезно при сохранении базы данных в памяти для последующего восстановления. Эта функция предоставляет те же возможности, что и команда `.dump` в оболочке **sqlite3**.491492Пример:493494```python495# Преобразовать файл existing_db.db в SQL-дамп dump.sql496import sqlite3, os497498con = sqlite3.connect('existing_db.db')499with open('dump.sql', 'w') as f:500    for line in con.iterdump():501        f.write('%s\n' % line)502```503504## Объекты курсора505506#### `[sqlite3.Cursor]class sqlite3.Cursor`507508Курсор базы данных SQLite имеет следующие атрибуты и методы:509510#### `[sqlite3.Cursor.execute]Cursor.execute(sql[, parameters])`511512Выполняет SQL-оператор. SQL-оператор может быть параметризован (т.е. использовать плейсхолдеры вместо литералов SQL). Модуль `sqlite3` поддерживает два вида плейсхолдеров: вопросительные знаки (стиль qmark) и именованные плейсхолдеры (именованный стиль).513514Этот пример показывает, как использовать параметры со стилем qmark:515516```python517import sqlite3518519con = sqlite3.connect("mydb")520521cur = con.cursor()522523who = "Yeltsin"524age = 72525526cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))527print(cur.fetchone())528```529530Этот пример показывает, как использовать именованный стиль:531532```python533import sqlite3534535con = sqlite3.connect("mydb")536537cur = con.cursor()538539who = "Yeltsin"540age = 72541542cur.execute("select name_last, age from people where name_last=:who and age=:age",543    {"who": who, "age": age})544print(cur.fetchone())545```546547[`execute()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.execute) выполнит только один SQL-оператор. При попытке выполнить с его помощью более одного оператора будет выдано предупреждение. Используйте [`executescript()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.executescript), если требуется выполнить несколько операторов SQL одним вызовом.548549#### `[sqlite3.Cursor.executemany]Cursor.executemany(sql, seq_of_parameters)`550551Выполняет SQL-команду для всех последовательностей параметров или отображений, найденных в последовательности *sql*. Модуль `sqlite3` также позволяет использовать [*итератор*](https://python-all.ru/3.0/glossary.html#term-iterator), возвращающий параметры, вместо последовательности.552553```python554import sqlite3555556class IterChars:557    def __init__(self):558        self.count = ord('a')559560    def __iter__(self):561        return self562563    def __next__(self):564        if self.count > ord('z'):565            raise StopIteration566        self.count += 1567        return (chr(self.count - 1),) # this is a 1-tuple568569con = sqlite3.connect(":memory:")570cur = con.cursor()571cur.execute("create table characters(c)")572573theIter = IterChars()574cur.executemany("insert into characters(c) values (?)", theIter)575576cur.execute("select c from characters")577print(cur.fetchall())578```579580Вот более короткий пример с использованием [*генератора*](https://python-all.ru/3.0/glossary.html#term-generator):581582```python583import sqlite3584585def char_generator():586    import string587    for c in string.letters[:26]:588        yield (c,)589590con = sqlite3.connect(":memory:")591cur = con.cursor()592cur.execute("create table characters(c)")593594cur.executemany("insert into characters(c) values (?)", char_generator())595596cur.execute("select c from characters")597print(cur.fetchall())598```599600#### `[sqlite3.Cursor.executescript]Cursor.executescript(sql_script)`601602Это нестандартный удобный метод для выполнения нескольких SQL-операторов за один раз. Сначала он выдаёт оператор `COMMIT`, а затем выполняет SQL-скрипт, полученный в качестве параметра.603604*sql\_script* может быть экземпляром [`str`](https://python-all.ru/3.0/library/functions.html#str) или [`bytes`](https://python-all.ru/3.0/library/functions.html#bytes).605606Пример:607608```python609import sqlite3610611con = sqlite3.connect(":memory:")612cur = con.cursor()613cur.executescript("""614    create table person(615        firstname,616        lastname,617        age618    );619620    create table book(621        title,622        author,623        published624    );625626    insert into book(title, author, published)627    values (628        'Dirk Gently''s Holistic Detective Agency',629        'Douglas Adams',630        1987631    );632    """)633```634635#### `[sqlite3.Cursor.fetchone]Cursor.fetchone()`636637Извлекает следующую строку результирующего набора запроса, возвращая одну последовательность или638639[`None`](https://python-all.ru/3.0/library/constants.html#None)640641, когда данные закончились.642643#### `[sqlite3.Cursor.fetchmany]Cursor.fetchmany([size=cursor.arraysize])`644645Извлекает следующий набор строк результата запроса, возвращая список. Пустой список возвращается, когда строк больше нет.646647Количество строк, извлекаемых за один вызов, задаётся параметром *size*. Если он не указан, количество строк определяется атрибутом arraysize курсора. Метод должен пытаться извлечь столько строк, сколько указано параметром size. Если это невозможно из-за того, что указанное количество строк недоступно, может быть возвращено меньше строк.648649Обратите внимание на вопросы производительности, связанные с параметром *size*. Для оптимальной производительности обычно лучше использовать атрибут arraysize. Если используется параметр *size*, то желательно, чтобы его значение оставалось неизменным от одного вызова [`fetchmany()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.fetchmany) к следующему.650651#### `[sqlite3.Cursor.fetchall]Cursor.fetchall()`652653Извлекает все (оставшиеся) строки результата запроса, возвращая список. Обратите внимание, что атрибут arraysize курсора может влиять на производительность этой операции. Пустой список возвращается, если строк нет.654655#### `[sqlite3.Cursor.rowcount]Cursor.rowcount`656657Хотя класс [`Cursor`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor) модуля `sqlite3` реализует этот атрибут, собственная поддержка определения «затронутых строк»/«выбранных строк» в движке базы данных работает причудливо.658659Для операторов `DELETE` SQLite сообщает [`rowcount`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.rowcount) как 0, если выполняется `DELETE FROM table` без каких-либо условий.660661Для инструкций [`executemany()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.executemany) количество изменений суммируется в [`rowcount`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.rowcount).662663В соответствии с требованиями спецификации Python DB API, атрибут [`rowcount`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.rowcount) «равен -1, если для курсора не выполнялось `executeXX()` или количество строк последней операции не может быть определено интерфейсом».664665Это включает операторы `SELECT`, поскольку нельзя определить количество строк, возвращённых запросом, пока не будут получены все строки.666667#### `[sqlite3.Cursor.lastrowid]Cursor.lastrowid`668669Этот доступный только для чтения атрибут возвращает rowid последней изменённой строки. Он устанавливается только в том случае, если с помощью метода670671[`execute()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.execute)672673был выполнен оператор674675`INSERT`676677. Для операций, отличных от678679`INSERT`680681, или при вызове682683[`executemany()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.executemany)684685, значение686687[`lastrowid`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.lastrowid)688689устанавливается равным690691[`None`](https://python-all.ru/3.0/library/constants.html#None)692693.694695#### `[sqlite3.Cursor.description]Cursor.description`696697Этот атрибут только для чтения возвращает имена столбцов последнего запроса. Для совместимости с Python DB API он возвращает для каждого столбца кортеж из 7 элементов, где последние шесть элементов каждого кортежа равны [`None`](https://python-all.ru/3.0/library/constants.html#None).698699Он также устанавливается для инструкций `SELECT`, даже если не найдено ни одной строки.700701## Объекты строк702703#### `[sqlite3.Row]class sqlite3.Row`704705Экземпляр [`Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row) служит высокооптимизированной [`row_factory`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection.row_factory) для объектов [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection). По большинству своих характеристик он имитирует кортеж.706707Он поддерживает доступ по имени столбца и индексу (как отображение), итерацию, представление, проверку на равенство и [`len()`](https://python-all.ru/3.0/library/functions.html#len).708709Если у двух объектов [`Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row) одинаковые столбцы и их элементы равны, то эти объекты считаются равными.710711#### `[sqlite3.Row.keys]keys()`712713Этот метод возвращает кортеж имён столбцов. Сразу после выполнения запроса он становится первым элементом каждого кортежа в714715[`Cursor.description`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor.description)716717.718719Предположим, мы инициализируем таблицу, как в приведённом выше примере:720721```python722conn = sqlite3.connect(":memory:")723c = conn.cursor()724c.execute('''create table stocks725(date text, trans text, symbol text,726 qty real, price real)''')727c.execute("""insert into stocks728          values ('2006-01-05','BUY','RHAT',100,35.14)""")729conn.commit()730c.close()731```732733Теперь подключаем [`Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row):734735```python736>>> conn.row_factory = sqlite3.Row737>>> c = conn.cursor()738>>> c.execute('select * from stocks')739<sqlite3.Cursor object at 0x7f4e7dd8fa80>740>>> r = c.fetchone()741>>> type(r)742<type 'sqlite3.Row'>743>>> r744(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001)745>>> len(r)7465747>>> r[2]748u'RHAT'749>>> r.keys()750['date', 'trans', 'symbol', 'qty', 'price']751>>> r['qty']752100.0753>>> for member in r: print member754...7552006-01-05756BUY757RHAT758100.075935.14760```761762## Типы SQLite и Python763764### Введение765766SQLite изначально поддерживает следующие типы: `NULL`, `INTEGER`, `REAL`, `TEXT`, `BLOB`.767768Следующие типы Python можно передавать в SQLite без каких-либо проблем:769770| Тип Python | Тип SQLite |771| --- | --- |772| [`None`](https://python-all.ru/3.0/library/constants.html#None) | `NULL` |773| [`int`](https://python-all.ru/3.0/library/functions.html#int) | `INTEGER` |774| [`float`](https://python-all.ru/3.0/library/functions.html#float) | `REAL` |775| [`bytes`](https://python-all.ru/3.0/library/functions.html#bytes) (UTF-8) | `TEXT` |776| [`str`](https://python-all.ru/3.0/library/functions.html#str) | `TEXT` |777| `buffer` | `BLOB` |778779Так по умолчанию типы SQLite преобразуются в типы Python:780781| Тип SQLite | Тип Python |782| --- | --- |783| `NULL` | [`None`](https://python-all.ru/3.0/library/constants.html#None) |784| `INTEGER` | :class\`int\` |785| `REAL` | [`float`](https://python-all.ru/3.0/library/functions.html#float) |786| `TEXT` | зависит от text\_factory, по умолчанию str |787| `BLOB` | buffer |788789Система типов модуля `sqlite3` расширяема двумя способами: можно хранить дополнительные типы Python в базе данных SQLite с помощью адаптации объектов, а также можно настроить модуль `sqlite3` на преобразование типов SQLite в разные типы Python с помощью конвертеров.790791### Использование адаптеров для хранения дополнительных типов Python в базах данных SQLite792793Как было описано ранее, SQLite изначально поддерживает только ограниченный набор типов. Чтобы использовать другие типы Python с SQLite, необходимо **адаптировать** их к одному из поддерживаемых модулем sqlite3 типов для SQLite: NoneType, int, float, str, bytes, buffer.794795Модуль `sqlite3` использует адаптацию объектов Python, как описано в [**PEP 246**](https://python-all.ru/3.0/library/sqlite3.html). Используемый протокол – `PrepareProtocol`.796797Есть два способа настроить модуль `sqlite3` на адаптацию пользовательского типа Python к одному из поддерживаемых.798799#### Адаптация объекта самого себя800801Это хороший подход, если вы пишете класс сами. Предположим, у вас есть такой класс:802803```python804class Point(object):805    def __init__(self, x, y):806        self.x, self.y = x, y807```808809Теперь необходимо сохранить точку в одном столбце SQLite. Сначала нужно выбрать один из поддерживаемых типов для представления точки. Используем str и разделим координаты точкой с запятой. Затем классу нужно дать метод `__conform__(self, протокол)`, который должен возвращать преобразованное значение. Параметр *протокол* будет `PrepareProtocol`.810811```python812import sqlite3813814class Point(object):815    def __init__(self, x, y):816        self.x, self.y = x, y817818    def __conform__(self, protocol):819        if protocol is sqlite3.PrepareProtocol:820            return "%f;%f" % (self.x, self.y)821822con = sqlite3.connect(":memory:")823cur = con.cursor()824825p = Point(4.0, -3.2)826cur.execute("select ?", (p,))827print(cur.fetchone()[0])828```829830#### Регистрация вызываемого адаптера831832Другая возможность – создать функцию, которая преобразует тип в строковое представление, и зарегистрировать её с помощью [`register_adapter()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.register_adapter).833834```python835import sqlite3836837class Point(object):838    def __init__(self, x, y):839        self.x, self.y = x, y840841def adapt_point(point):842    return "%f;%f" % (point.x, point.y)843844sqlite3.register_adapter(Point, adapt_point)845846con = sqlite3.connect(":memory:")847cur = con.cursor()848849p = Point(4.0, -3.2)850cur.execute("select ?", (p,))851print(cur.fetchone()[0])852```853854Модуль `sqlite3` имеет два адаптера по умолчанию для встроенных типов Python [`datetime.date`](https://python-all.ru/3.0/library/datetime.html#datetime.date) и [`datetime.datetime`](https://python-all.ru/3.0/library/datetime.html#datetime.datetime). Теперь предположим, что мы хотим хранить объекты [`datetime.datetime`](https://python-all.ru/3.0/library/datetime.html#datetime.datetime) не в представлении ISO, а в виде метки времени Unix.855856```python857import sqlite3858import datetime, time859860def adapt_datetime(ts):861    return time.mktime(ts.timetuple())862863sqlite3.register_adapter(datetime.datetime, adapt_datetime)864865con = sqlite3.connect(":memory:")866cur = con.cursor()867868now = datetime.datetime.now()869cur.execute("select ?", (now,))870print(cur.fetchone()[0])871```872873### Преобразование значений SQLite в пользовательские типы Python874875Написание адаптера позволяет отправлять пользовательские типы Python в SQLite. Но чтобы сделать это действительно полезным, нужно обеспечить обратное преобразование из SQLite в Python.876877Вступают конвертеры.878879Вернёмся к классу `Point`. Мы сохранили координаты x и y, разделённые точкой с запятой, в виде строк в SQLite.880881Сначала определим функцию-конвертер, которая принимает строку в качестве параметра и создаёт объект `Point` из неё.882883> **Примечание**884>885> Функции преобразования **всегда** вызываются со строкой, независимо от того, под каким tипом данных значение было отправлено в SQLite.886887```python888def convert_point(s):889    x, y = map(float, s.split(";"))890    return Point(x, y)891```892893Теперь нужно сообщить модулю `sqlite3`, что выбираемые из базы данных данные на самом деле являются точкой. Есть два способа это сделать:894895- Неявно, через объявленный тип896- Явно, по имени колонки897898Оба способа описаны в разделе [*Функции и константы модуля*](https://python-all.ru/3.0/library/sqlite3.html#sqlite3-module-contents), в записях для констант [`PARSE_DECLTYPES`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.PARSE_DECLTYPES) и [`PARSE_COLNAMES`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.PARSE_COLNAMES).899900Следующий пример иллюстрирует оба подхода.901902```python903import sqlite3904905class Point(object):906    def __init__(self, x, y):907        self.x, self.y = x, y908909    def __repr__(self):910        return "(%f;%f)" % (self.x, self.y)911912def adapt_point(point):913    return "%f;%f" % (point.x, point.y)914915def convert_point(s):916    x, y = list(map(float, s.split(";")))917    return Point(x, y)918919# Register the adapter920sqlite3.register_adapter(Point, adapt_point)921922# Register the converter923sqlite3.register_converter("point", convert_point)924925p = Point(4.0, -3.2)926927#########################928# 1) Using declared types929con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)930cur = con.cursor()931cur.execute("create table test(p point)")932933cur.execute("insert into test(p) values (?)", (p,))934cur.execute("select p from test")935print("with declared types:", cur.fetchone()[0])936cur.close()937con.close()938939#######################940# 1) Using column names941con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)942cur = con.cursor()943cur.execute("create table test(p)")944945cur.execute("insert into test(p) values (?)", (p,))946cur.execute('select p as "p [point]" from test')947print("with column names:", cur.fetchone()[0])948cur.close()949con.close()950```951952### Адаптеры и преобразователи по умолчанию953954В модуле datetime есть адаптеры по умолчанию для типов date и datetime. Они отправляются в SQLite как даты/временные метки в формате ISO.955956Конвертеры по умолчанию зарегистрированы под именем “date” для [`datetime.date`](https://python-all.ru/3.0/library/datetime.html#datetime.date) и под именем “timestamp” для [`datetime.datetime`](https://python-all.ru/3.0/library/datetime.html#datetime.datetime).957958Таким образом, в большинстве случаев можно использовать даты и временные метки из Python без лишних усилий. Формат адаптеров также совместим с экспериментальными функциями даты/времени SQLite.959960Следующий пример демонстрирует это.961962```python963import sqlite3964import datetime965966con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)967cur = con.cursor()968cur.execute("create table test(d date, ts timestamp)")969970today = datetime.date.today()971now = datetime.datetime.now()972973cur.execute("insert into test(d, ts) values (?, ?)", (today, now))974cur.execute("select d, ts from test")975row = cur.fetchone()976print(today, "=>", row[0], type(row[0]))977print(now, "=>", row[1], type(row[1]))978979cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')980row = cur.fetchone()981print("current_date", row[0], type(row[0]))982print("current_timestamp", row[1], type(row[1]))983```984985## Управление транзакциями986987По умолчанию модуль `sqlite3` открывает транзакции неявно перед оператором языка манипуляции данными (DML) (т. е. `INSERT`/`UPDATE`/`DELETE`/`REPLACE`) и фиксирует транзакции неявно перед оператором, не являющимся DML или запросом (т. е. всем, кроме `SELECT` или упомянутых ранее).988989Таким образом, если выполняется команда внутри транзакции, например `CREATE TABLE ...`, `VACUUM`, `PRAGMA`, то модуль `sqlite3` неявно выполнит commit перед выполнением этой команды. Это делается по двум причинам. Во-первых, некоторые из этих команд не работают внутри транзакций. Во-вторых, pysqlite необходимо отслеживать состояние транзакции (активна транзакция или нет).990991Можно управлять тем, какой вид операторов `BEGIN` pysqlite выполняет неявно (или не выполняет вовсе) с помощью параметра *isolation\_level* в вызове [`connect()`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.connect) или через свойство `isolation_level` соединений.992993Если нужен **режим автокоммита**, установите `isolation_level` в None.994995В противном случае оставьте его по умолчанию, что приведет к простой инструкции “BEGIN”, или установите одно из поддерживаемых уровней изоляции SQLite: “DEFERRED”, “IMMEDIATE” или “EXCLUSIVE”.996997## Эффективное использование pysqlite998999### Использование сокращенных методов10001001Используя нестандартные методы `execute()`, `executemany()` и `executescript()` объекта [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection), код можно написать более сжато, поскольку не нужно явно создавать (часто избыточные) объекты [`Cursor`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor). Вместо этого объекты [`Cursor`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Cursor) создаются неявно, и эти сокращённые методы возвращают объекты курсоров. Таким образом, можно выполнить оператор `SELECT` и итерироваться по нему напрямую, используя всего один вызов объекта [`Connection`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Connection).10021003```python1004import sqlite310051006persons = [1007    ("Hugo", "Boss"),1008    ("Calvin", "Klein")1009    ]10101011con = sqlite3.connect(":memory:")10121013# Создать таблицу1014con.execute("create table person(firstname, lastname)")10151016# Заполнить таблицу1017con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)10181019# Выводит содержимое таблицы.1020for row in con.execute("select firstname, lastname from person"):1021    print(row)10221023# Используется фиктивное условие WHERE, чтобы SQLite не применил сокращённое удаление таблицы.1024print("I just deleted", con.execute("delete from person where 1=1").rowcount, "rows")1025```10261027### Доступ к столбцам по имени вместо индекса10281029Одна из полезных возможностей модуля `sqlite3` – встроенный класс [`sqlite3.Row`](https://python-all.ru/3.0/library/sqlite3.html#sqlite3.Row), предназначенный для использования в качестве фабрики строк.10301031Строки, обёрнутые этим классом, можно получать как по индексу (как кортежи), так и по имени без учёта регистра:10321033```python1034import sqlite310351036con = sqlite3.connect("mydb")1037con.row_factory = sqlite3.Row10381039cur = con.cursor()1040cur.execute("select name_last, age from people")1041for row in cur:1042    assert row[0] == row["name_last"]1043    assert row["name_last"] == row["nAmE_lAsT"]1044    assert row[1] == row["age"]1045    assert row[1] == row["AgE"]1046```10471048### Использование соединения как менеджера контекста10491050Объекты соединения можно использовать как менеджеры контекста, которые автоматически фиксируют или откатывают транзакции. В случае исключения транзакция откатывается; в противном случае транзакция фиксируется:10511052```python1053import sqlite310541055con = sqlite3.connect(":memory:")1056con.execute("create table person (id integer primary key, firstname varchar unique)")10571058# При успешном выполнении con.commit() вызывается автоматически.1059with con:1060    con.execute("insert into person(firstname) values (?)", ("Joe",))10611062# con.rollback() вызывается после завершения блока with с исключением,1063# исключение всё равно возбуждается и должно быть перехвачено1064try:1065    with con:1066        con.execute("insert into person(firstname) values (?)", ("Joe",))1067except sqlite3.IntegrityError:1068    print("couldn't add Joe twice")1069```1070