11.6. sqlite3 – интерфейс DB-API 2.0 для баз данных SQLite¶sqlite3 – DB-API 2.0 interface for SQLite databases

11.6.1. Функции и константы модуля¶Module functions and constants

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, – это только первое слово имени столбца; то есть если в SQL используется что-то вроде 'as "x [datetime]"', то будет извлечено всё до первого пробела: имя столбца будет просто «x».

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

Открывает соединение с файлом базы данных SQLite database. Можно использовать ":memory:", чтобы открыть соединение с базой данных, расположенной в оперативной памяти, а не на диске.

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

По поводу параметра isolation_level обратитесь к свойству Connection.isolation_level объектов Connection.

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

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

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

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

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

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, как в следующем примере:

# Минимальная оболочка SQLite для экспериментов

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, чтобы снова отключить эту возможность.

11.6.2. Объекты соединения¶Connection Objects

class sqlite3.Connection¶

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

isolation_level¶

Получает или устанавливает текущий уровень изоляции. None для режима автокоммита или одно из значений «DEFERRED», «IMMEDIATE» или «EXCLUSIVE». Подробное объяснение приведено в разделе Управление транзакциями.

in_transaction¶

True, если транзакция активна (есть незафиксированные изменения), и False в противном случае. Атрибут только для чтения.

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

cursor([cursorClass])¶

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

commit()¶

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

rollback()¶

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

close()¶

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

execute(sql[, parameters])¶

Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод execute этого курсора с переданными параметрами.

executemany(sql[, parameters])¶

Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод executemany этого курсора с переданными параметрами.

executescript(sql_script)¶

Это нестандартный сокращённый метод, который создаёт промежуточный объект курсора вызовом метода cursor, а затем вызывает метод executescript этого курсора с переданными параметрами.

create_function(name, num_params, func)¶

Создаёт пользовательскую функцию, которую затем можно использовать в SQL-выражениях под именем name. num_params – количество параметров, принимаемых функцией, а 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])

create_aggregate(name, num_params, aggregate_class)¶

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

Класс агрегатной функции должен реализовывать метод step, который принимает количество параметров num_params, и метод 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])

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 с параметром callable равным 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.

enable_load_extension(enabled)¶

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

Загружаемые расширения по умолчанию отключены. См. [1].

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

import sqlite3

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

# включить загрузку расширений
con.enable_load_extension(True)

# Загрузить расширение полнотекстового поиска.
con.execute("select load_extension('./fts3.so')")

# в качестве альтернативы можно загрузить расширение с помощью вызова API:
# con.load_extension("./fts3.so")

# снова отключить загрузку расширения
con.enable_load_extension(False)

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

load_extension(path)¶

Эта процедура загружает расширение 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"])

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

text_factory¶

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

Из соображений эффективности существует также способ возвращать объекты str только для не-ASCII данных, а в остальных случаях – bytes. Чтобы активировать его, установите этот атрибут в sqlite3.OptimizedUnicode.

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

См. следующий пример для иллюстрации:

import sqlite3

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

AUSTRIA = "\xd6sterreich"

# по умолчанию строки возвращаются как Unicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# но можно заставить sqlite3 всегда возвращать байтовые строки...
con.text_factory = bytes
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is bytes
# байтовые строки будут в кодировке UTF-8, если только в базу не попал мусор
# база данных...
assert row[0] == AUSTRIA.encode("utf-8")

# можно также реализовать собственный text_factory...
# здесь реализован такой, который добавляет "foo" ко всем строкам
con.text_factory = lambda x: x.decode("utf-8") + "foo"
cur.execute("select ?", ("bar",))
row = cur.fetchone()
assert row[0] == "barfoo"

total_changes¶

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

iterdump¶

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

Пример:

# Преобразовать файл existing_db.db в SQL-дамп dump.sql
import sqlite3, os

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

11.6.3. Объекты Cursor¶Cursor Objects

class sqlite3.Cursor¶

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

execute(sql[, parameters])¶

Выполняет 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

# Это стиль qmark:
cur.execute("insert into people values (?, ?)", (who, age))

# А это именованный стиль:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

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

executemany(sql, seq_of_parameters)¶

Выполняет SQL-команду для всех последовательностей параметров или отображений, найденных в последовательности sql. Модуль 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),) # это кортеж из одного элемента

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())

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

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())

executescript(sql_script)¶

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

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

Пример:

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
    );
    """)

fetchone()¶

Извлекает следующую строку результирующего набора запроса, возвращая одну последовательность или None, когда данные закончились.

fetchmany(size=cursor.arraysize)¶

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

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

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

fetchall()¶

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

rowcount¶

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

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

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

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

lastrowid¶

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

description¶

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

Он также устанавливается для инструкций SELECT, даже если не найдено ни одной строки.

11.6.4. Объекты Row¶Row Objects

class sqlite3.Row¶

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

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

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

keys()¶

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

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

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

11.6.5. SQLite и типы Python¶SQLite and Python types

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

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])

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])

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])

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

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)

# Зарегистрировать адаптер
sqlite3.register_adapter(Point, adapt_point)

# Зарегистрировать конвертер
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Использование объявленных типов
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) Использование имён столбцов
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()

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]))

11.6.6. Управление транзакциями¶Controlling Transactions

По умолчанию модуль sqlite3 неявно открывает транзакции перед инструкциями языка модификации данных (DML) (т.е. INSERT/UPDATE/DELETE/REPLACE) и неявно фиксирует транзакции перед любой другой инструкцией (кроме SELECT и вышеупомянутых).

Поэтому если вы находитесь внутри транзакции и выполняете команду наподобие CREATE TABLE ..., VACUUM, PRAGMA, модуль sqlite3 перед выполнением этой команды неявно выполнит фиксацию. На то есть две причины. Первая – некоторые из этих команд не работают внутри транзакций. Другая причина в том, что sqlite3 необходимо отслеживать состояние транзакции (активна она или нет). Текущее состояние транзакции доступно через атрибут Connection.in_transaction объекта соединения.

Можно управлять тем, какие именно операторы BEGIN sqlite3 выполняет неявно (или не выполнять их вовсе) с помощью параметра isolation_level вызова connect(), или с помощью свойства isolation_level соединений.

Если нужен режим автокоммита, установите isolation_level в None.

В противном случае оставьте его по умолчанию, что приведет к простой инструкции “BEGIN”, или установите одно из поддерживаемых уровней изоляции SQLite: “DEFERRED”, “IMMEDIATE” или “EXCLUSIVE”.

11.6.7. Эффективное использование sqlite3¶Using sqlite3 efficiently

import sqlite3

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

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

# Создать таблицу
con.execute("create table person(firstname, lastname)")

# Заполнить таблицу
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Выводит содержимое таблицы.
for row in con.execute("select firstname, lastname from person"):
    print(row)

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

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"]

import sqlite3

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

# При успешном выполнении con.commit() вызывается автоматически.
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() вызывается после завершения блока with с исключением,
# исключение все равно возбуждается, и его необходимо перехватить
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

11.6.8. Типичные проблемы¶Common issues