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


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

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

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

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

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

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

c = conn.cursor()

# Создать таблицу
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()

# Мы также можем закрыть соединение, если мы завершили с ним.
# Просто убедитесь, что любые изменения были зафиксированы, иначе они будут потеряны.
conn.close()

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

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

Обычно ваши операции SQL должны использовать значения из переменных Python. Вы не должны собирать свой запрос, используя строковые операции Python, потому что это небезопасно; это делает вашу программу уязвимой для атаки SQL-инъекций (см. юмористический пример того, что может пойти не так).

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

# Никогда не делайте этого — небезопасно!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Вместо этого сделайте
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Большой пример, вставляющий много записей за раз
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)

См.также

Домашняя страница sqlite
Веб-страница SQLite; документация рассказывает о синтаксисе и доступных типах данных для поддерживаемого диалекта SQL.
Синтаксис SQL
Учебник, справочник и примеры для изучения синтаксиса SQL.
PEP 249 — Спецификация API базы данных 2.0
PEP, написанный Марк-Андре Лембург.

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

sqlite3.version

Номер версии модуля в виде строки. Не версия библиотеки SQLite.

sqlite3.version_info

Номер версии модуля в виде набора целых чисел. Не версия библиотеки SQLite.

sqlite3.sqlite_version

Номер версии библиотеки SQLite времени выполнения в виде строки.

sqlite3.sqlite_version_info

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

sqlite3.PARSE_DECLTYPES

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

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

sqlite3.PARSE_COLNAMES

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

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

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

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

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, чтобы включить определение типа. Из-за поведения SQLite типы не могут быть обнаружены для сгенерированных полей (например, max(data)), даже если установлен параметр detect_types. В таком случае возвращается тип str.

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

По умолчанию модуль sqlite3 использует свой класс Connection для вызова соединения. Однако вы можете создать подкласс класса 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.

Вызывает событие аудита sqlite3.connect с аргументом database.

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

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

sqlite3.register_converter(typename, callable)

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

sqlite3.register_adapter(type, callable)

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

sqlite3.complete_statement(sql)

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

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

# A minimal SQLite shell for experiments

import sqlite3

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

buffer = ""

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

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

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

con.close()
sqlite3.enable_callback_tracebacks(flag)

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

Объекты Connection

class sqlite3.Connection

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

isolation_level

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

in_transaction

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

Добавлено в версии 3.2.

cursor(factory=Cursor)

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

commit()

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

rollback()

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

close()

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

execute(sql[, parameters])

Нестандартный ярлык, создающий объект курсора, вызывая метод cursor(), вызывает метод курсора execute() с заданным parameters и возвращает курсор.

executemany(sql[, parameters])

Нестандартный ярлык, создающий объект курсора, вызывая метод cursor(), вызывает метод курсора executemany() с заданным parameters и возвращает курсор.

executescript(sql_script)

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

create_function(name, num_params, func, *, deterministic=False)

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

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

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

Пример:

import sqlite3
import hashlib

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

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

con.close()
create_aggregate(name, num_params, aggregate_class)

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

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

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

Пример:

import sqlite3

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

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

    def finalize(self):
        return self.count

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

con.close()
create_collation(name, callable)

Создаёт сопоставление с указанными 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» и т. д.), если применимо. 5-й аргумент — это имя самого внутреннего триггера или представления, отвечающего за попытку доступа, или None, если эта попытка доступа осуществляется непосредственно из входного кода SQL.

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

set_progress_handler(handler, n)

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

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

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

set_trace_callback(trace_callback)

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

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

Передача None как trace_callback отключит обратный вызов трассировки.

Добавлено в версии 3.3.

enable_load_extension(enabled)

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

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

Добавлено в версии 3.2.

import sqlite3

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

# enable extension loading
con.enable_load_extension(True)

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

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

# disable extension loading again
con.enable_load_extension(False)

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

con.close()
load_extension(path)

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

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

Добавлено в версии 3.2.

row_factory

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

Пример:

import sqlite3

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

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

con.close()

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

text_factory

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

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

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

import sqlite3

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

AUSTRIA = "\xd6sterreich"

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

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

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

con.close()
total_changes

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

iterdump()

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

Пример:

# Преобразование файла exists_db.db в файл дампа SQL dump.sql
import sqlite3

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)
con.close()
backup(target, *, pages=-1, progress=None, name="main", sleep=0.250)

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

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

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

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

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

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

import sqlite3

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

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

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

import sqlite3

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

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

Добавлено в версии 3.7.

Объекты Cursor

class sqlite3.Cursor

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

execute(sql[, parameters])

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

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

import sqlite3

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

who = "Yeltsin"
age = 72

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

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

print(cur.fetchone())

con.close()

execute() выполнит только один оператор SQL. Если вы попытаетесь выполнить с ним более одного оператора, он вызовет ошибку Warning. Используйте executescript(), если вы хотите выполнить несколько операторов SQL одним вызовом.

executemany(sql, seq_of_parameters)

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

import sqlite3

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

    def __iter__(self):
        return self

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

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

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

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

con.close()

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

import sqlite3
import string

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

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

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

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

con.close()
executescript(sql_script)

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

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

Пример:

import sqlite3

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

    create table book(
        title,
        author,
        published
    );

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

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

fetchmany(size=cursor.arraysize)

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

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

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

fetchall()

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

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

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

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

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

arraysize

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

description

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

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

connection

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

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

Row объекты

class sqlite3.Row

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

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

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

keys()

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

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

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

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

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

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

Исключения

exception sqlite3.Warning

Подкласс Exception.

exception sqlite3.Error

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

exception sqlite3.DatabaseError

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

exception sqlite3.IntegrityError

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

exception sqlite3.ProgrammingError

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

exception sqlite3.OperationalError

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

exception sqlite3.NotSupportedError

Исключение вызывается в случае использования метода или API базы данных, которые не поддерживаются базой данных. Например, вызов метода rollback() для соединения, которое не поддерживает транзакции или транзакции отключены. Подкласс DatabaseError.

Типы SQLite и Python

Введение

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

Таким образом, следующие типы Python могут отправляться в SQLite без каких- либо проблем:

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

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

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

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

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

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

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

Разрешите вашему объекту адаптироваться

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

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

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

import sqlite3

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

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

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

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

con.close()

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

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

import sqlite3

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

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

sqlite3.register_adapter(Point, adapt_point)

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

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

con.close()

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

import sqlite3
import datetime
import time

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

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

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

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

con.close()

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

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

Наберите преобразователи.

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

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

Примечание

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

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

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

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

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

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

import sqlite3

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

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

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

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

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

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

p = Point(4.0, -3.2)

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

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

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

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

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

В модуле 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]))

con.close()

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

Контроль транзакций

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

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

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

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

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

Изменено в версии 3.6: sqlite3 используется для неявной фиксации открытой транзакции перед операторами DDL. Это больше не так.

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

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

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

import sqlite3

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

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

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

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

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

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

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

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

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

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

import sqlite3

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

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

con.close()

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

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

import sqlite3

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

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

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

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

Сноски

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