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

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

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

Модуль sqlite3 был написан Gerhard Häring. Он предоставляет интерфейс 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)

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

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

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

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

Когда к базе данных обращаются несколько подключений, и один из процессов изменяет базу данных, база данных SQLite блокируется до тех пор, пока эта транзакция не будет подтверждена. Параметр 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.

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

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

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

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

create_collation(name, callable)

Создаёт сортировку с указанным 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.

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

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.

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

Пояснение см. в следующем примере кода:

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"

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)

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

class sqlite3.Cursor

Объект Cursor имеет следующие атрибуты и методы.

execute(sql[, parameters])

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

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

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

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

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

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.

Пример:

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 связаны соображения производительности. Для оптимальной производительности обычно лучше использовать атрибут arraysize. Если параметр size используется, желательно сохранить его значение от одного вызова fetchmany() к другому.

fetchall()

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

close()

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

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

rowcount

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

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

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

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

lastrowid

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

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

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

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

5. Исключение

exception sqlite3.Warning

Подкласс Exception.

exception sqlite3.Error

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

exception sqlite3.DatabaseError

Исключение, генерируемое для ошибок, связанных с базой данных.

exception sqlite3.IntegrityError

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

exception sqlite3.ProgrammingError

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

6. Типы SQLite и Python

6.1. Введение

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

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

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

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

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

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

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

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

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

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

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

Другой вариант — создать функцию, преобразующую тип в строковое представление, и зарегистрировать функцию с помощью 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])

Модуль 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])

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

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

Вводятся преобразователи.

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

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

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

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

Существуют адаптеры по умолчанию для типов date и datetime в модуле datetime. Они будут отправлены в SQLite в формате ISO-дат/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]))

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

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

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

Итак, если вы находитесь внутри транзакции и выполняете команду, например CREATE TABLE ..., VACUUM, PRAGMA, модуль sqlite3 неявно подтвердит (commit) перед выполнением этой команды. Это делается по двум причинам. Во-первых, некоторые из этих команд не работают внутри транзакций. Во-вторых, sqlite3 необходимо отслеживать состояние транзакции (активна транзакция или нет). Текущее состояние транзакции отображается через атрибут Connection.in_transaction объекта соединения.

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

Если вам нужен автоматический режим подтверждения, то установите isolation_level в значение None.

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

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

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

Используя нестандартные методы 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")

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

Полезная особенность модуля 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"]

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

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

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

9. Распространённые проблемы

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

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

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

Примечания