Сообщить об ошибке.

Методы объекта Connection SQLite3.

Объекта Connection можно получить в результате соединение с базой данных SQLite в результате вызова функции sqlite3.connect().

Синтаксис:

import sqlite3

connect = sqlite3.connect(":memory:")
# или
connect = sqlite3.connect("example.sqlite3")

В разделе рассмотрены методы объекта Connection модуля sqlite3 с подробным описанием и примерами.

Содержание.

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

connect.isolation_level:

Атрибут connect.isolation_level возвращает или устанавливает текущий уровень изоляции по умолчанию. None для режима автоматической фиксации autocommit или одного из режимов DEFERRED, IMMEDIATE или EXCLUSIVE.

Смотрите раздел "Управление транзакциями" для более подробного объяснения.

connect.in_transaction:

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

Атрибут только для чтения.

connect.cursor(factory=Cursor):

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

connect.blobopen(table, column, row, /, \*, readonly=False, name="main"):

Метод connect.blobopen() (добавлен в Python 3.11) открывает файлоподобный объект Blob, для существующего SQLite BLOB.

Принимаемые аргументы:

  • table (str) - имя таблицы, в которой находится большой двоичный объект.
  • column (str) - имя столбца, в котором находится большой двоичный объект.
  • row (str) - имя строки, в которой находится большой двоичный объект.
  • readonly=False (bool) - значение True, если большой двоичный объект должен быть открыт без разрешений на запись. По умолчанию имеет значение False.
  • name="main" (str) — имя базы данных, в которой находится большой двоичный объект. По умолчанию main.

При попытке открыть большой двоичный объект в таблице WITHOUT ROWID поднимается исключение sqlite3.OperationalError.

Примечание. Размер большого двоичного объекта нельзя изменить с помощью класса Blob. Используйте функцию SQL zeroblob для создания большого двоичного объекта фиксированного размера.

Новое в Python 3.11

connect.commit():

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

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

connect.rollback():

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

connect.close():

Метод connect.close() закрывает соединение с базой данных.

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

connect.execute(sql[, parameters]):

Нестандартный метод connect.execute(), который создает объект курсора и вызывает его метод cursor.execute() с заданными параметрами и возвращает курсор. Объект курсора создается вызовом метода connect.cursor().

Нестандартный метод выполняет инструкцию SQL. Оператор 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

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

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

print(cur.fetchone())

con.close()

connect.executemany(sql[, seq_of_parameters]):

Нестандартный метод connect.executemany(), который создает объект курсора и вызывает его метод cursor.executemany() с заданными параметрами и возвращает курсор. Объект курсора создается вызовом метода connect.cursor().

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

connect.executescript(sql_script):

Нестандартный метод connect.executescript() , который создает объект курсора и вызывает его метод cursor.executescript() с заданными параметрами и возвращает курсор. Объект курсора создается вызовом метода connect.cursor().

Метод позволяет сделать одновременно выполнение нескольких операторов 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()

connect.create_function(name, num_params, func, *, deterministic=False):

Метод connect.create_function() создает пользовательскую функцию, которую позже можно использовать из операторов SQL под именем функции.

Аргумент num_params - это число параметров, которые принимает функция, если num_params=-1, то функция может принимать любое количество аргументов.

Аргумент func - это вызываемый объект (функция) Python, которая будет вызываться как функция SQL.

Если аргумент deterministic=True, то созданная функция помечается как deterministic, что позволяет SQLite выполнять дополнительную оптимизацию. Этот флаг поддерживается в SQLite 3.8.3 или более поздней версии. При использовании этого флага со старыми версиями будет вызываться исключение NotSupportedError.

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

Пример использования:

import sqlite3
import hashlib

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

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

con.close()

connect.create_aggregate(name, num_params, aggregate_class):

Метод connect.create_aggregate() создает пользовательскую агрегатную функцию.

Совокупный класс должен реализовать метод step, который принимает число параметров num_params и метод finalize, который возвратит конечный результат.

  • Если num_params=-1, то функция может принимать любое количество аргументов.
  • Метод 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()

connect.create_window_function(name, num_params, aggregate_class, /):

Метод connect.create_window_function() (добавлен в Python 3.11) создает или удаляет определяемую пользователем агрегированную оконную функцию.

Принимаемые аргументы:

  • name (str) - имя статистической оконной функции SQL, которую необходимо создать или удалить.
  • num_params (int) - количество аргументов, которые может принять оконная функция SQL. Если -1, то может принимать любое количество аргументов.

  • aggregate_class (class | None) - Класс, который должен реализовать следующие методы:

    • `step(): добавить строку в текущее окно.
    • `value(): возвращает текущее значение агрегата.
    • `inverse(): удалить строку из текущего окна.
    • `finalize(): возвращает окончательный результат агрегата как тип, изначально поддерживаемый SQLite.

    Количество аргументов, которые должны принимать методы .step() и .value(), контролируется параметром num_params.

    Чтобы удалить существующую оконную функцию необходимо установить значение None.

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

Новое в Python 3.11

connect.create_collation(name, callable):

Метод connect.create_collation() создает сопоставление с указанным именем name и возможностью вызова.

Вызываемому объекту callable будут переданы два строковых аргумента. Объект 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()

Чтобы удалить параметры сортировки, вызовите connect.create_collation() с параметром callable=None, при этом аргумент name должен иметь имя удаляемого сопоставления:

con.create_collation("reverse", None)
interrupt()

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

Изменено в Python 3.11: Имя сопоставления collation может содержать любой символ Юникода. Ранее разрешались только символы ASCII.

connect.set_authorizer(authorizer_callback):

Метод connect.set_authorizer() регистрирует обратный вызов authorizer_callback. Этот вызов будет вызывается для каждой попытки доступа к столбцу таблицы в базе данных.

Обратный вызов должен возвращать:

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

Первый аргумент к обратному вызову указывает, какая операция должна быть авторизована. Второй и третий аргументы будут параметрами или None в зависимости от первого аргумента. Четвёртый аргумент - это имя базы данных 'main', 'temp' и т. д., если применимо. Пятый аргумент - это имя самого внутреннего триггера или представления, которое отвечает за попытку доступа или None, если эта попытка доступа осуществляется непосредственно из входного кода SQL.

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

Передача None в качестве authorizer_callback приведет к отключению авторизации.

Изменено в Python 3.11: Добавлена поддержка отключения авторизации с помощью None.

connect.set_progress_handler(handler, n):

Метод connect.set_progress_handler() регистрирует обработчик handler. Обработчик handler вызывается для каждой n инструкций виртуальной машины SQLite.

Такое поседение полезно, если необходимо получить вызов из SQLite во время длительных операций, например для обновления графического интерфейса. Другими словами позволяет сделать индикатор выполнения PROGRESS BAR

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

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

connect.set_trace_callback(trace_callback):

Метод connect.set_trace_callback() регистрирует trace_callback для вызова каждого оператора SQL, который фактически выполняется бакэндом SQLite

Единственный аргумент, передаваемый в trace_callback - это выполняемый оператор в виде строки. Возвращаемое значение обратного вызова игнорируется.

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

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

connect.enable_load_extension(enabled):

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

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

import sqlite3

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

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

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

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

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

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

con.close()

connect.load_extension(path):

Метод connect.load_extension() загружает расширение SQLite из общей библиотеки.

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

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

connect.row_factory:

Можно изменить атрибут connect.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. Этот тип обеспечивает доступ к столбцам как на основе индексов, так и по имени столбца без учета регистра, почти без затрат памяти. Вероятно, это будет лучше, чем собственный подход на основе словаря или даже решение на основе db_row.

connect.text_factory:

Используя атрибут connect.text_factory можно контролировать, какие объекты возвращаются для типа данных TEXT.

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

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

Смотрите следующий пример кода для лучшего понимания:

import sqlite3

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

AUSTRIA = "\xd6sterreich"

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

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

connect.total_changes:

Атрибут connect.total_changes возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия соединения с базой данных.

connect.iterdump():

Метод connect.iterdump() возвращает итератор для выгрузки базы данных в текстовом формате SQL.

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

Пример использования:

# Преобразовать файл existing_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()

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

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

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

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

Аргумент 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()

Пример №1 - скопировать существующую базу данных во временную копию:

# Availability: SQLite 3.6.11 or higher
import sqlite3

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

connect.getlimit(category, /):

Метод connect.getlimit() (добавлен в Python 3.11) получает ограничение для текущего соединения.

Аргумент category - (int) категория ограничения SQLite, подлежащая запросу.

Если категория не распознается базовой библиотекой SQLite, то появляется ошибка sqlite3.ProgrammingError

Например, запросим максимальную длину инструкции SQL для Connection (по умолчанию 1000000000):

>>> con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH)
# 1000000000

Новое в Python 3.11

connect.setlimit(category, limit, /):

Метод connect.setlimit() (добавлен в Python 3.11) устанавливает ограничение для текущего соединения.

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

Аргумент category - (int) категория ограничения SQLite, подлежащая запросу.

Аргумент limit - (int) значение нового лимита. Если отрицательный, то текущий лимит не изменяется.

Например, ограничим количество подключенных баз данных до 1 для Connection (ограничение по умолчанию - 10):

>>> con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1)
# 10
>>> con.getlimit(sqlite3.SQLITE_LIMIT_ATTACHED)
# 1

Новое в Python 3.11

connect.serialize(\*, name="main"):

Метод connect.serialize() (добавлен в Python 3.11) сериализует базу данных в байтовый объект. Для обычного файла базы данных на диске сериализация - это просто копия файла на диске. Для базы данных в памяти или "временной" базы данных сериализация представляет собой ту же последовательность байтов, которая была бы записана на диск, если бы эта база данных была скопирована на диск.

Аргумент name - (str) имя сериализуемой базы данных. По умолчанию 'main'.

Примечание. Этот метод доступен только в том случае, если базовая библиотека SQLite имеет API сериализации.

Новое в Python 3.11

connect.deserialize(data, /, \*, name="main"):

Метод connect.deserialize() (добавлен в Python 3.11) десериализует сериализованную базу данных в Connection. Этот метод приводит к отключению соединения с базой данных с именем name и повторному открытию name в качестве базы данных в памяти на основе сериализации, содержащейся в data.

Аргумент data - (bytes) сериализованная база данных.

Аргумент name - (str) имя базы данных для десериализации. По умолчанию 'main'.

Если соединение с базой данных в данный момент задействовано в транзакции чтения или операции резервного копирования, то возникает ошибка sqlite3.OperationalError.

Если данные не содержат действующую базу данных SQLite sqlite3, то возникает ошибка sqlite3.DatabaseError.

Если len(data) больше 2**63, то возникает ошибка OverflowError.

Примечание. Этот метод доступен только в том случае, если базовая библиотека SQLite имеет API сериализации.

Новое в Python 3.11