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

Исходный код: Lib/sqlite3/

SQLite — это библиотека C, предоставляющая лёгкую базу данных на диске, которая не требует отдельного процесса сервера и позволяет обращаться к базе данных с помощью нестандартного варианта языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создание прототипа приложения с использованием SQLite, а затем перенести код на более крупную базу данных, например, PostgreSQL или Oracle.

Модуль sqlite3 был написан Герхардом Хэрингом. Он предоставляет SQL-интерфейс, совместимый со спецификацией DB-API 2.0, описанной в PEP 249.

Для использования модуля необходимо сначала создать объект Connection, представляющий базу данных. Здесь данные будут храниться в файле example.db:

import sqlite3
conn = sqlite3.connect('example.db')

Также можно использовать специальное имя :memory: для создания базы данных в оперативной памяти.

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

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

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

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

Обычно для SQL-операций потребуется использовать значения из переменных Python. Не следует собирать запрос с помощью строковых операций Python, так как это небезопасно; это делает программу уязвимой к атаке SQL-инъекции (см. https://xkcd.com/327/ для юмористического примера того, что может пойти не так).

Вместо этого используйте параметризацию подстановки DB-API. Вставьте ? в качестве заглушки, где нужно использовать значение, а затем передайте кортеж значений в качестве второго аргумента методу курсора execute(). (Другие модули баз данных могут использовать другой маркер подстановки, например, %s или :1.) Например:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

Для извлечения данных после выполнения оператора SELECT можно либо обращаться к курсору как к итератору, либо вызвать метод курсора fetchone() для извлечения одной строки или вызвать fetchall() для получения списка соответствий.

В этом примере используется форма итератора:

>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

См. также

https://github.com/ghaering/pysqlite: Веб-страница pysqlite — sqlite3 разработан отдельно под названием «pysqlite».
https://www.sqlite.org: Веб-страница SQLite; документация описывает синтаксис и доступные типы данных для поддерживаемого диалекта SQL.
https://www.w3schools.com/sql/: Учебник, справочник и примеры для изучения синтаксиса SQL.
PEP 249 — Спецификация API баз данных 2.0: PEP написан Марк-Андре Лембюргом.

Функции и константы модуля

sqlite3.version: Версия этого модуля в виде строки. Это не версия библиотеки SQLite.

sqlite3.version_info: Версия этого модуля в виде кортежа целых чисел. Это не версия библиотеки SQLite.

sqlite3.sqlite_version: Версия библиотеки SQLite времени выполнения в виде строки.

sqlite3.sqlite_version_info: Версия библиотеки SQLite времени выполнения в виде кортежа целых чисел.

sqlite3.PARSE_DECLTYPES

Эта константа предназначена для использования в параметре detect_types функции connect().

Установив её, модуль sqlite3 будет анализировать объявленный тип для каждого возвращаемого столбца. Он будет анализировать первое слово объявленного типа, например, для «integer primary key» — «integer», или для «number(10)» — «number». Затем для этого столбца он будет искать в словаре преобразователей функцию преобразования, зарегистрированную для этого типа.

sqlite3.PARSE_COLNAMES

Эта константа предназначена для использования в параметре detect_types функции connect().

Установив её, интерфейс SQLite будет анализировать имя столбца для каждого возвращаемого столбца. Он будет искать строку вида [mytype], и затем решать, что «mytype» — это тип столбца. Он будет пытаться найти запись «mytype» в словаре преобразователей и затем использовать найденную функцию преобразования для возвращения значения. Имя столбца, найденное в Cursor.description, не включает тип, т. е. если вы используете что-то вроде 'as "Expiration date [datetime]"' в своём SQL, то мы будем анализировать всё до первого '[' для имени столбца и удалять предшествующий пробел: имя столбца будет просто «Дата истечения срока действия».

END_OF_DOCUMENT_MARKER ```

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

Открывает соединение с файлом базы данных SQLite database. По умолчанию возвращает объект Connection, если не указана настраиваемая функция factory.

database — объект пути, задающий имя файла базы данных (абсолютное или относительное к текущей рабочей директории), который требуется открыть. Можно использовать ":memory:" для открытия подключения к базе данных, которая хранится в оперативной памяти, а не на диске.

Когда к базе данных обращаются несколько соединений, и один из процессов модифицирует базу данных, база данных SQLite блокируется до тех пор, пока эта транзакция не будет подтверждена. Параметр timeout указывает, сколько времени соединение должно ждать, пока блокировка не будет снята, прежде чем сгенерировать исключение. Значение параметра timeout по умолчанию равно 5.0 (пять секунд).

Для параметра isolation_level см. свойство isolation_level объектов Connection.

SQLite изначально поддерживает только типы TEXT, INTEGER, REAL, BLOB и NULL. Если вы хотите использовать другие типы, вы должны добавить поддержку для них самостоятельно. Параметр detect_types и использование настраиваемых преобразователей, зарегистрированных с помощью функции модульного уровня register_converter(), позволяют легко это сделать.

Параметр detect_types по умолчанию равен 0 (т. е. выключен, нет распознавания типов), его можно установить в любое сочетание PARSE_DECLTYPES и PARSE_COLNAMES для включения распознавания типов.

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

По умолчанию, модуль sqlite3 использует свой класс Connection для вызова connect. Однако вы можете создать подкласс класса Connection и заставить connect() использовать ваш класс вместо него, предоставив ваш класс в качестве параметра factory.

Подробные сведения см. в разделе Типы SQLite и Python данного руководства.

Модуль sqlite3 внутренне использует кэш инструкций, чтобы избежать избыточных расходов на синтаксический анализ SQL. Если вы хотите явно задать количество инструкций, кэшируемых для подключения, вы можете установить параметр cached_statements. Текущее значение по умолчанию — 100 инструкций.

Если uri равно True, database интерпретируется как URI. Это позволяет вам указывать параметры. Например, чтобы открыть базу данных в режиме только для чтения, можно использовать:

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

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

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

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

sqlite3.register_converter(typename, callable): Регистрирует вызываемую функцию для преобразования байтовой строки из базы данных в настраиваемый тип Python. Вызываемая функция будет вызываться для всех значений базы данных, тип которых соответствует typename. См. параметр detect_types функции connect() для получения информации о работе распознавания типов. Обратите внимание, что typename и имя типа в вашем запросе сопоставляются без учета регистра.

sqlite3.register_adapter(type, callable): Регистрирует вызываемую функцию для преобразования настраиваемого типа Python type в один из поддерживаемых SQLite типов. Вызываемая функция callable принимает в качестве единственного параметра значение Python и должна возвращать значение одного из следующих типов: int, float, str или bytes.

sqlite3.complete_statement(sql)

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

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

# A minimal SQLite shell for experiments

import sqlite3

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

buffer = ""

print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")

while True:
    line = input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print(cur.fetchall())
        except sqlite3.Error as e:
            print("An error occurred:", e.args[0])
        buffer = ""

con.close()

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

Объекты подключения

class sqlite3.Connection

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

isolation_level: Получение или установка текущего уровня изоляции по умолчанию. None для режима автозаписи или один из “DEFERRED”, “IMMEDIATE” или “EXCLUSIVE”. Более подробное объяснение см. в разделе Управление транзакциями.

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

Новое в версии 3.2.

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

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

rollback(): Этот метод отменяет любые изменения в базе данных с момента последнего вызова commit().

close(): Это закрывает подключение к базе данных. Обратите внимание, что это не вызывает автоматически commit(). Если вы просто закроете подключение к базе данных, не вызвав commit() предварительно, ваши изменения будут потеряны!

execute(sql[, parameters]): Это нестандартное сокращение, которое создает объект курсора, вызывая метод cursor(), вызывает метод курсора execute() с заданными параметрами и возвращает курсор.

executemany(sql[, parameters]): Это нестандартное сокращение, которое создает объект курсора, вызывая метод cursor(), вызывает метод курсора executemany() с заданными параметрами и возвращает курсор.

executescript(sql_script): Это нестандартное сокращение, которое создает объект курсора, вызывая метод cursor(), вызывает метод курсора executescript() с заданным sql_script и возвращает курсор.

create_function(name, num_params, func)

Создает пользовательскую функцию, которую вы можете использовать в дальнейшем из SQL-запросов под именем name. num_params — количество параметров, которые принимает функция (если num_params равно -1, функция может принимать любое количество аргументов), а func — вызываемый объект Python, который используется в качестве SQL-функции.

Функция может возвращать любой тип, поддерживаемый SQLite: bytes, str, int, float и None.

Пример:

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",))
print(cur.fetchone()[0])

con.close()

create_aggregate(name, num_params, aggregate_class)

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

Класс агрегата должен реализовывать метод step, который принимает количество параметров num_params (если num_params равно -1, функция может принимать любое количество аргументов) и метод finalize, который вернет окончательный результат агрегирования.

Метод finalize может возвращать любой тип, поддерживаемый SQLite: bytes, str, int, float и None.

Пример:

import sqlite3

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

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

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print(cur.fetchone()[0])

con.close()

create_collation(name, callable)

Создает сортировку (collation) с указанным именем name и вызываемым объектом callable. Вызываемый объект будет получать два строковых аргумента. Он должен возвращать -1, если первый аргумент меньше второго, 0, если они равны, и 1, если первый аргумент больше второго. Обратите внимание, что это управляет сортировкой (ORDER BY в SQL), поэтому ваши сравнения не влияют на другие SQL-операции.

Обратите внимание, что вызываемый объект получит свои параметры в виде байтовых строк Python, которые обычно закодированы в UTF-8.

Следующий пример демонстрирует пользовательскую сортировку, которая сортирует «не так»:

import sqlite3

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

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

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print(row)
con.close()

Для удаления сортировки вызовите create_collation с None в качестве вызываемого объекта:

con.create_collation("reverse", None)

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

set_authorizer(authorizer_callback)

Эта процедура регистрирует обратный вызов. Обратный вызов вызывается для каждой попытки доступа к столбцу таблицы в базе данных. Обратный вызов должен вернуть SQLITE_OK если доступ разрешен, SQLITE_DENY если весь SQL-запрос должен быть прерван с ошибкой и SQLITE_IGNORE если столбец должен обрабатываться как значение NULL. Эти константы доступны в модуле sqlite3.

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

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

set_progress_handler(handler, n)

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

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

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

set_trace_callback(trace_callback)

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

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

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

Новое в версии 3.3.

enable_load_extension(enabled)

Эта процедура позволяет/запрещает SQLite-движку загружать SQLite-расширения из общих библиотек. SQLite-расширения могут определять новые функции, агрегаты или даже целые новые реализации виртуальных таблиц. Одно из известных расширений — расширение полнотекстового поиска, поставляемое с SQLite.

Загрузка расширений по умолчанию отключена. См. 1.

Новое в версии 3.2.

import sqlite3

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

# enable extension loading
con.enable_load_extension(True)

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

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

# disable extension loading again
con.enable_load_extension(False)

# example from SQLite wiki
con.execute("create virtual table recipe using fts3(name, ingredients)")
con.executescript("""
    insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');
    insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');
    insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');
    insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');
    """)
for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):
    print(row)

con.close()

load_extension(path)

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

Загрузка расширений по умолчанию отключена. См. 1.

Новое в версии 3.2.

row_factory

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

Пример:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

con.close()

Если возвращение кортежа недостаточно, и вам нужен доступ к столбцам по имени, вы можете установить row_factory на высокооптимизированный тип sqlite3.Row. Row обеспечивает доступ к столбцам как по индексу, так и по имени в регистронезависимом формате, практически без дополнительной нагрузки на память. Он, вероятно, будет лучше, чем ваш собственный подход на основе словаря или даже решение с db_row.

text_factory

Используя этот атрибут, вы можете контролировать, какие объекты возвращаются для типа данных TEXT. По умолчанию этот атрибут установлен на str, и модуль sqlite3 будет возвращать объекты Unicode для TEXT. Если вы хотите возвращать байтовые строки вместо этого, вы можете установить его на bytes.

Вы также можете установить его на любой другой вызываемый объект, принимающий единственный байтовый параметр и возвращающий результирующий объект.

Иллюстративный пример кода см. ниже:

import sqlite3

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

AUSTRIA = "\xd6sterreich"

# by default, rows are returned as Unicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# but we can make sqlite3 always return bytestrings ...
con.text_factory = bytes
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is bytes
# the bytestrings will be encoded in UTF-8, unless you stored garbage in the
# database ...
assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...
# here we implement one that appends "foo" to all strings
con.text_factory = lambda x: x.decode("utf-8") + "foo"
cur.execute("select ?", ("bar",))
row = cur.fetchone()
assert row[0] == "barfoo"

con.close()

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

iterdump()

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

Пример:

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3

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

backup(target, *, pages=0, progress=None, name="main", sleep=0.250)

Этот метод создает резервную копию базы данных SQLite, даже когда к ней обращаются другие клиенты или одновременно с тем же подключением. Копия будет записана в обязательный аргумент target, который должен быть другим экземпляром Connection.

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

Если указан progress, он должен быть None или вызываемым объектом, который будет выполняться на каждой итерации с тремя целочисленными аргументами, соответственно: status последней итерации, remaining количество оставшихся страниц и total общее количество страниц.

Аргумент name указывает имя базы данных, которое будет скопировано: он должен быть строкой, содержащей либо "main", по умолчанию, для обозначения основной базы данных, "temp" для обозначения временной базы данных или имя, указанное после ключевого слова AS в операторе ATTACH DATABASE для присоединённой базы данных.

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

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

import sqlite3

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

con = sqlite3.connect('existing_db.db')
bck = sqlite3.connect('backup.db')
with bck:
    con.backup(bck, pages=1, progress=progress)
bck.close()
con.close()

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

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)

Доступность: SQLite 3.6.11 или выше

Новое в версии 3.7.

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

class sqlite3.Cursor

У экземпляра Cursor есть следующие атрибуты и методы.

execute(sql[, parameters])

Выполняет SQL-запрос. SQL-запрос может быть параметризован (т. е. использовать подстановки вместо SQL-литералов). Модуль sqlite3 поддерживает два типа подстановок: вопросительные знаки (стиль qmark) и именованные подстановки (именованный стиль).

Вот примеры обоих стилей:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

con.close()

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

executemany(sql, seq_of_parameters)

Выполняет SQL-команду для всех последовательностей параметров или отображений, найденных в последовательности seq_of_parameters. Модуль sqlite3 также позволяет использовать итератор, возвращающий параметры вместо последовательности.

import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

Вот более короткий пример с использованием генератора:

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

executescript(sql_script)

Это нестандартный удобный метод для выполнения нескольких SQL-запросов сразу. Сначала он выдает COMMIT запрос, затем выполняет SQL-скрипт, который он получает в качестве параметра.

sql_script может быть экземпляром str.

Пример:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)
con.close()

fetchone(): Возвращает следующую строку набора результатов запроса в виде последовательности или None, если больше данных недоступно.

fetchmany(size=cursor.arraysize)

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

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

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

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

close()

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

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

rowcount

Хотя класс Cursor модуля sqlite3 реализует этот атрибут, поддержка определения «затронутых строк»/«выбранных строк» в самом движке базы данных специфична.

Для операторов executemany(), количество изменений суммируется в rowcount.

В соответствии со спецификацией Python DB API, атрибут rowcount равен -1, если с курсором не производились операции или количество строк последней операции не может быть определено интерфейсом. Это включает SELECT запросы, потому что мы не можем определить количество строк, возвращаемых запросом, пока не получим все строки.

В версиях SQLite до 3.6.5, rowcount устанавливается в 0, если вы используете DELETE FROM table без условия.

lastrowid

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

Если запрос INSERT или REPLACE не смог вставить предыдущую успешную строку, возвращается идентификатор строки.

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

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

description

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

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

connection

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

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

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

class sqlite3.Row

Экземпляр Row служит высокооптимизированной фабрикой строк row_factory для объектов Connection. Он пытается имитировать кортеж по большей части своих функций.

Он поддерживает доступ по имени и индексу столбца, итерацию, представление, проверку на равенство и функцию len().

Если два объекта Row имеют точно такие же столбцы и их члены равны, они считаются равными.

keys(): Этот метод возвращает список имён столбцов. Сразу после запроса это первый член каждого кортежа в Cursor.description.

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

Предположим, что мы инициализируем таблицу, как в приведённом выше примере:

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()

Теперь мы подключаем Row:

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

Исключения

exception sqlite3.Warning: Подкласс Exception.

exception sqlite3.Error: Базовый класс других исключений в этом модуле. Это подкласс Exception.

exception sqlite3.DatabaseError: Исключение, поднимаемое для ошибок, связанных с базой данных.

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

exception sqlite3.ProgrammingError: Исключение, возникающее при программистских ошибках, например, при отсутствии таблицы или ее существовании, синтаксической ошибке в SQL-запросе, неправильном количестве указанных параметров и т. д. Это подкласс DatabaseError.

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

exception sqlite3.NotSupportedError: Исключение, возникающее, если используется метод или API базы данных, который не поддерживается базой данных, например, вызов метода rollback() для подключения, не поддерживающего транзакции или имеющего отключенные транзакции. Это подкласс DatabaseError.

SQLite и типы Python

Введение

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

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

Тип Python	Тип SQLite
`None`	`NULL`
`int`	`INTEGER`
`float`	`REAL`
`str`	`TEXT`
`bytes`	`BLOB`

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

Тип SQLite	Тип Python
`NULL`	`None`
`INTEGER`	`int`
`REAL`	`float`
`TEXT`	зависит от `text_factory`, по умолчанию `str`
`BLOB`	`bytes`

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

Использование адаптеров для хранения дополнительных типов Python в базах данных SQLite

Как описано ранее, SQLite изначально поддерживает только ограниченный набор типов. Чтобы использовать другие типы Python с SQLite, вы должны адаптировать их к одному из поддерживаемых модулем sqlite3 типов для SQLite: NoneType, int, float, str, bytes.

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

Адаптация объекта

Это хороший подход, если вы сами пишете класс. Предположим, у вас есть класс:

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

Теперь вы хотите сохранить точку в одном столбце SQLite. Сначала вам нужно выбрать один из поддерживаемых типов для представления точки. Используем просто str и разделим координаты точкой с запятой. Затем нужно добавить в ваш класс метод __conform__(self, protocol), который должен вернуть преобразованное значение. Параметр protocol будет PrepareProtocol.

import sqlite3

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

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

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

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

Регистрация вызываемого адаптера

Другая возможность — создать функцию, которая преобразует тип в строковое представление, и зарегистрировать функцию с помощью register_adapter().

import sqlite3

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

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

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

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

Модуль sqlite3 имеет два адаптера по умолчанию для встроенных типов Python datetime.date и datetime.datetime. Теперь предположим, что мы хотим хранить объекты datetime.datetime не в формате ISO, а как метку времени Unix.

import sqlite3
import datetime
import time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

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

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print(cur.fetchone()[0])

con.close()

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

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

Вступают в игру преобразователи.

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

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

Примечание

Функции преобразователей всегда вызываются с объектом bytes, независимо от типа данных, который вы отправили в SQLite.

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

Теперь нужно сообщить модулю sqlite3, что то, что вы выбираете из базы данных, фактически является точкой. Существует два способа сделать это:

Неявным образом через объявленный тип
Явным образом через имя столбца

Оба способа описаны в разделе Функции и константы модуля, в записях для констант PARSE_DECLTYPES и PARSE_COLNAMES.

Следующий пример иллюстрирует оба подхода.

import sqlite3

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

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return ("%f;%f" % (point.x, point.y)).encode('ascii')

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

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

Адаптеры и преобразователи по умолчанию

Существуют адаптеры по умолчанию для типов date и datetime в модуле datetime. Они будут отправлены в SQLite как даты/временные метки ISO.

Адаптеры по умолчанию зарегистрированы под именем «date» для datetime.date и под именем «timestamp» для datetime.datetime.

Таким образом, вы можете использовать даты/временные метки из Python без дополнительных ухищрений в большинстве случаев. Формат адаптеров также совместим с экспериментальными функциями SQLite для дат и времени.

Следующий пример демонстрирует это.

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))

con.close()

Если временная метка, хранящаяся в SQLite, имеет дробную часть длиннее 6 цифр, ее значение будет усечено до микросекундной точности преобразователем временных меток.

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

Базовая библиотека sqlite3 по умолчанию работает в режиме autocommit, но модуль Python sqlite3 — нет.

Режим autocommit означает, что операторы, изменяющие базу данных, вступают в силу немедленно. Оператор BEGIN или SAVEPOINT отключает режим autocommit, а операторы COMMIT, ROLLBACK или RELEASE, завершающие внешнюю транзакцию, снова включают режим autocommit.

Модуль Python sqlite3 по умолчанию неявно выполняет оператор BEGIN перед оператором языка манипулирования данными (DML) (например, INSERT, UPDATE, DELETE или REPLACE).

Вы можете контролировать, какие типы BEGIN инструкций sqlite3 неявно выполняются через параметр isolation_level в вызове connect(), или через свойство isolation_level соединений. Если вы не указываете isolation_level, используется обычный BEGIN, что эквивалентно указанию DEFERRED. Другие возможные значения — IMMEDIATE и EXCLUSIVE.

Вы можете отключить неявное управление транзакциями модуля sqlite3, установив isolation_level в None. Это позволит базовой библиотеке sqlite3 работать в режиме autocommit. Затем вы сможете полностью контролировать состояние транзакции, явно выполняя BEGIN, ROLLBACK, SAVEPOINT, и RELEASE инструкции в вашем коде.

Изменено в версии 3.6: sqlite3 неявно подтверждал открытую транзакцию перед операторами DDL. Сейчас это не так.

Эффективное использование sqlite3

Использование сокращенных методов

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

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

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

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print(row)

print("I just deleted", con.execute("delete from person").rowcount, "rows")

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

Доступ к столбцам по имени вместо индекса

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

Строки, обернутые этим классом, могут быть доступны как по индексу (как кортежи), так и по имени, не чувствительно к регистру:

import sqlite3

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

cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
    assert row[0] == row["name"]
    assert row["name"] == row["nAmE"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

con.close()

Использование соединения как контекстного менеджера

Объекты соединения могут использоваться как контекстные менеджеры, которые автоматически подтверждают или отменяют транзакции. В случае возникновения исключения транзакция отменяется; в противном случае — подтверждается:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

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

Общие проблемы

Многопоточность

В более старых версиях SQLite были проблемы с совместным использованием соединений между потоками. Именно поэтому модуль Python запрещает совместное использование соединений и курсоров между потоками. Если вы всё равно попытаетесь это сделать, во время выполнения будет получено исключение.

Исключением является вызов метода interrupt(), который имеет смысл вызывать только из другого потока.

Примечания

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