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)

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

12.6.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(), вызывает метод cursor’s execute() с заданными параметрами и возвращает курсор.

executemany(sql[, parameters])

Это нестандартное сокращение, которое создаёт объект курсора, вызывая метод cursor(), вызывает метод cursor’s executemany() с заданными параметрами и возвращает курсор.

executescript(sql_script)

Это нестандартное сокращение, которое создаёт объект курсора, вызывая метод cursor(), вызывает метод cursor’s 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.

Возвращение ненулевого значения из функции обработчика завершит выполнение текущего запроса и вызовет исключение 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)

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)

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

class sqlite3.Cursor

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

execute(sql[, parameters])

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

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

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() не была выполнена с курсором, или количество строк последней операции не определяется интерфейсом. Это включает SELECT запросы, поскольку мы не можем определить количество строк, возвращаемых запросом, пока все строки не будут извлечены.

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

lastrowid

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

Если INSERT или REPLACE запрос не выполнился, возвращается ID последней успешно добавленной строки.

Изменено в версии 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

12.6.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

12.6.5. Исключения

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.

12.6.6. Типы SQLite и Python

12.6.6.1. Введение

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

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

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

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

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

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

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

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

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

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

12.6.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 цифр, её значение будет усечено до микросекундной точности преобразователем временных меток.

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

Базовая библиотека 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 инструкции в вашем коде.

12.6.8. Использование sqlite3 эффективно

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

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

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

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

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

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

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

Примечания

1(1,2)

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