SQLite

Support for the SQLite database.

The following table summarizes current support levels for database release versions.

Поддерживаемые версии SQLite.

Support type

Versions

Fully tested in CI

3.21, 3.28+

Normal support

3.12+

Best effort

3.7.16+

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

Типы даты и времени

SQLite не имеет встроенных типов DATE, TIME или DATETIME, а pysqlite не предоставляет встроенной функциональности для перевода значений между Python datetime objects and a SQLite-supported format. SQLAlchemy’s own DateTime and related types provide date formatting and parsing functionality when SQLite is used. The implementation classes are DATETIME, DATE and TIME. Эти типы представляют даты и время как строки в формате ISO, которые также поддерживают упорядочивание. Эти функции не зависят от типичных внутренних функций «libc», поэтому исторические даты полностью поддерживаются.

Обеспечение близости к тексту

В DDL для этих типов используются стандартные показатели DATE, TIME и DATETIME. Однако к этим типам можно применять и пользовательские форматы хранения. Когда формат хранения определяется как не содержащий буквенных символов, DDL для этих типов отображается как DATE_CHAR, TIME_CHAR и DATETIME_CHAR, так что колонка продолжает иметь текстовое родство.

См.также

Type Affinity - в документации SQLite

Поведение автоинкрементов в SQLite

Справочная информация об автоинкременте в SQLite находится по адресу: https://sqlite.org/autoinc.html.

Ключевые понятия:

  • SQLite имеет неявную функцию «автоматического инкремента», которая имеет место для любого некомпозитного столбца с первичным ключом, который специально создан с использованием «INTEGER PRIMARY KEY» для типа + первичный ключ.

  • SQLite также имеет явное ключевое слово «AUTOINCREMENT», которое не эквивалентно неявной функции автоинкремента; это ключевое слово не рекомендуется для общего использования. SQLAlchemy не отображает это ключевое слово, если не используется специальная директива, специфичная для SQLite (см. ниже). Однако при этом все равно требуется, чтобы тип столбца был назван «INTEGER».

Использование ключевого слова AUTOINCREMENT

Чтобы специально отобразить ключевое слово AUTOINCREMENT для столбца первичного ключа при создании DDL, добавьте флаг sqlite_autoincrement=True в конструкцию таблицы:

Table('sometable', metadata,
        Column('id', Integer, primary_key=True),
        sqlite_autoincrement=True)

Разрешение автоинкрементного поведения типов SQLAlchemy, отличных от Integer/INTEGER

Модель типизации SQLite основана на соглашениях об именовании. Среди прочего, это означает, что любое имя типа, содержащее подстроку "INT", будет определено как «целочисленное». Тип с именем "BIGINT", "SPECIAL_INT" или даже "XYZINTQPR" будет рассматриваться SQLite как «целочисленный». Однако функция автоинкремента SQLite, включенная неявно или явно, требует, чтобы имя типа столбца было именно строкой «INTEGER «. Поэтому, если приложение использует для первичного ключа тип типа BigInteger, на SQLite этот тип должен быть отображен как имя "INTEGER" при выдаче начального оператора CREATE TABLE, чтобы поведение автоинкремента было доступно.

Один из подходов для достижения этого - использовать Integer на SQLite только с использованием TypeEngine.with_variant():

table = Table(
    "my_table", metadata,
    Column("id", BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
)

Другой вариант - использовать подкласс BigInteger, который переопределяет его DDL-имя на INTEGER при компиляции с SQLite:

from sqlalchemy import BigInteger
from sqlalchemy.ext.compiler import compiles

class SLBigInteger(BigInteger):
    pass

@compiles(SLBigInteger, 'sqlite')
def bi_c(element, compiler, **kw):
    return "INTEGER"

@compiles(SLBigInteger)
def bi_c(element, compiler, **kw):
    return compiler.visit_BIGINT(element, **kw)


table = Table(
    "my_table", metadata,
    Column("id", SLBigInteger(), primary_key=True)
)

Поведение блокировки базы данных / параллелизм

SQLite не рассчитан на высокий уровень параллелизма при записи. Сама база данных, будучи файлом, полностью блокируется во время операций записи в рамках транзакций, что означает, что только одно «соединение» (в действительности - дескриптор файла) имеет эксклюзивный доступ к базе данных в этот период - все остальные «соединения» будут заблокированы в это время.

Спецификация Python DBAPI также требует модели соединения, которое всегда находится в транзакции; здесь нет метода connection.begin(), только connection.commit() и connection.rollback(), после чего немедленно начинается новая транзакция. Может показаться, что это означает, что драйвер SQLite теоретически допускает только один файловый хэндл на конкретный файл базы данных в любое время; однако есть несколько факторов как в самом SQLite, так и в драйвере pysqlite, которые значительно ослабляют это ограничение.

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

Это поведение становится более критичным при использовании в сочетании с SQLAlchemy ORM. Объект SQLAlchemy Session по умолчанию работает внутри транзакции, и благодаря своей модели autoflush может испускать DML, предшествующий любому оператору SELECT. Это может привести к тому, что база данных SQLite будет блокироваться быстрее, чем ожидается. Режимом блокировки SQLite и драйвера pysqlite можно в некоторой степени управлять, однако следует отметить, что достижение высокой степени параллелизма записи в SQLite - это проигрышная битва.

Для получения дополнительной информации об отсутствии в SQLite параллелизма при записи по замыслу, пожалуйста, смотрите Situations Where Another RDBMS May Work Better - High Concurrency внизу страницы.

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

Уровень изоляции транзакций / автокоммит

SQLite поддерживает «изоляцию транзакций» нестандартным образом, по двум направлениям. Первая - это инструкция PRAGMA read_uncommitted. Этот параметр может переключать SQLite между стандартным режимом изоляции SERIALIZABLE и режимом изоляции «грязного чтения», обычно называемым READ UNCOMMITTED.

SQLAlchemy связывается с этим оператором PRAGMA с помощью параметра create_engine.isolation_level create_engine(). Допустимыми значениями этого параметра при использовании с SQLite являются "SERIALIZABLE" и "READ UNCOMMITTED", что соответствует значению 0 и 1, соответственно. По умолчанию SQLite имеет значение SERIALIZABLE, однако на его поведение влияет поведение по умолчанию драйвера pysqlite.

При использовании драйвера pysqlite также доступен уровень изоляции "AUTOCOMMIT", который изменит соединение pysqlite с помощью атрибута .isolation_level на соединении DBAPI и установит его в None на время настройки.

Добавлено в версии 1.3.16: добавлена поддержка уровня изоляции SQLite AUTOCOMMIT при использовании драйвера SQLite pysqlite / sqlite3.

Другая ось, на которую влияет транзакционная блокировка SQLite, - это характер используемого оператора BEGIN. Есть три разновидности: «отложенная», «немедленная» и «эксклюзивная», как описано в BEGIN TRANSACTION. Прямой оператор BEGIN использует «отложенный» режим, при котором файл базы данных не блокируется до первой операции чтения или записи, а доступ на чтение остается открытым для других транзакций до первой операции записи. Но опять же, важно отметить, что драйвер pysqlite вмешивается в это поведение, не выдавая даже BEGIN до первой операции записи.

Предупреждение

На транзакционную область SQLite влияют нерешенные проблемы в драйвере pysqlite, который откладывает выполнение операторов BEGIN в большей степени, чем это часто возможно. Методы обхода этого поведения описаны в разделе Сериализуемая изоляция / Точки сохранения / Транзакционный DDL.

ВСТАВКА/ОБНОВЛЕНИЕ/УДАЛЕНИЕ… ВОЗВРАЩЕНИЕ

Диалект SQLite поддерживает синтаксис INSERT|UPDATE|DELETE..RETURNING в SQLite 3.35. В некоторых случаях INSERT..RETURNING может использоваться автоматически для получения вновь созданных идентификаторов вместо традиционного подхода использования cursor.lastrowid, однако cursor.lastrowid в настоящее время все еще предпочтительнее для простых случаев с одним выражением из-за его лучшей производительности.

Чтобы указать явное предложение RETURNING, используйте метод _UpdateBase.returning() на основе каждого предложения:

# INSERT..RETURNING
result = connection.execute(
    table.insert().
    values(name='foo').
    returning(table.c.col1, table.c.col2)
)
print(result.all())

# UPDATE..RETURNING
result = connection.execute(
    table.update().
    where(table.c.name=='foo').
    values(name='bar').
    returning(table.c.col1, table.c.col2)
)
print(result.all())

# DELETE..RETURNING
result = connection.execute(
    table.delete().
    where(table.c.name=='foo').
    returning(table.c.col1, table.c.col2)
)
print(result.all())

Добавлено в версии 2.0: Добавлена поддержка SQLite RETURNING

Поддержка SAVEPOINT

SQLite поддерживает SAVEPOINTs, которые работают только после начала транзакции. Поддержка SAVEPOINT в SQLAlchemy доступна с помощью метода Connection.begin_nested() на уровне Core и Session.begin_nested() на уровне ORM. Однако SAVEPOINT вообще не будут работать с pysqlite, если не предпринять обходных путей.

Предупреждение

На функцию SAVEPOINT в SQLite влияют нерешенные проблемы в драйвере pysqlite, который откладывает выполнение операторов BEGIN в большей степени, чем это часто возможно. О методах обхода этого поведения читайте в разделе Сериализуемая изоляция / Точки сохранения / Транзакционный DDL.

Транзакционный DDL

База данных SQLite также поддерживает транзакции DDL. В этом случае драйвер pysqlite не только не запускает транзакции, но и завершает все существующие транзакции при обнаружении DDL, поэтому снова требуются обходные пути.

Предупреждение

На транзакционный DDL в SQLite влияют нерешенные проблемы в драйвере pysqlite, который не может выдать BEGIN и дополнительно заставляет COMMIT отменить любую транзакцию при встрече с DDL. Методы обхода этого поведения см. в разделе Сериализуемая изоляция / Точки сохранения / Транзакционный DDL.

Поддержка иностранных ключей

SQLite поддерживает синтаксис FOREIGN KEY при создании операторов CREATE для таблиц, однако по умолчанию эти ограничения не влияют на работу таблицы.

Проверка ограничений в SQLite имеет три предпосылки:

  • Должна использоваться версия SQLite не менее 3.6.19

  • Библиотека SQLite должна быть скомпилирована без включенных символов SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER.

  • Оператор PRAGMA foreign_keys = ON должен выдаваться на всех соединениях перед использованием - включая начальный вызов MetaData.create_all().

SQLAlchemy позволяет автоматически создавать оператор PRAGMA для новых соединений с помощью событий:

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

Предупреждение

Когда внешние ключи в SQLite включены, невозможно создавать операторы CREATE или DROP для таблиц, которые содержат взаимозависимые ограничения внешних ключей; чтобы создать DDL для этих таблиц, необходимо использовать ALTER TABLE, чтобы создать или отменить эти ограничения отдельно, что SQLite не поддерживает.

См.также

SQLite Foreign Key Support - на веб-сайте SQLite.

События - API событий SQLAlchemy.

Создание/удаление ограничений внешних ключей с помощью ALTER - дополнительная информация о средствах SQLAlchemy для обработки

взаимозависимые ограничения внешнего ключа.

ПО КОНФЛИКТУ поддержка ограничений

См.также

В этом разделе описывается DDL версия «ON CONFLICT» для SQLite, которая возникает внутри оператора CREATE TABLE. О том, как «ON CONFLICT» применяется к оператору INSERT, см. раздел INSERT…ON CONFLICT (Upsert).

SQLite поддерживает нестандартное предложение DDL, известное как ON CONFLICT, которое может применяться к ограничениям первичного ключа, уникальности, проверки и не null. В DDL он отображается либо в предложении «CONSTRAINT», либо в самом определении столбца, в зависимости от расположения целевого ограничения. Для отображения этого условия в DDL, параметр расширения sqlite_on_conflict может быть указан с алгоритмом разрешения конфликта строк в объектах PrimaryKeyConstraint, UniqueConstraint, CheckConstraint. Внутри объекта Column имеются отдельные параметры sqlite_on_conflict_not_null, sqlite_on_conflict_primary_key, sqlite_on_conflict_unique, которые соответствуют трем типам соответствующих ограничений, которые могут быть указаны из объекта Column.

См.также

ON CONFLICT - в документации SQLite

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

Параметры sqlite_on_conflict принимают строковый аргумент, который является просто именем разрешения, которое нужно выбрать, которое в SQLite может быть одним из ROLLBACK, ABORT, FAIL, IGNORE и REPLACE. Например, чтобы добавить ограничение UNIQUE, определяющее алгоритм IGNORE:

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', Integer),
    UniqueConstraint('id', 'data', sqlite_on_conflict='IGNORE')
)

Вышеприведенный DDL CREATE TABLE выглядит следующим образом:

CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER,
    PRIMARY KEY (id),
    UNIQUE (id, data) ON CONFLICT IGNORE
)

При использовании флага Column.unique для добавления ограничения UNIQUE к одному столбцу, к параметру sqlite_on_conflict_unique можно добавить и Column, который будет добавлен к ограничению UNIQUE в DDL:

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', Integer, unique=True,
           sqlite_on_conflict_unique='IGNORE')
)

оказание услуг:

CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER,
    PRIMARY KEY (id),
    UNIQUE (data) ON CONFLICT IGNORE
)

Чтобы применить алгоритм FAIL для ограничения NOT NULL, используется sqlite_on_conflict_not_null:

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', Integer, nullable=False,
           sqlite_on_conflict_not_null='FAIL')
)

это отображает колонку в строке ON CONFLICT phrase:

CREATE TABLE some_table (
    id INTEGER NOT NULL,
    data INTEGER NOT NULL ON CONFLICT FAIL,
    PRIMARY KEY (id)
)

Аналогично, для встроенного первичного ключа используйте sqlite_on_conflict_primary_key:

some_table = Table(
    'some_table', metadata,
    Column('id', Integer, primary_key=True,
           sqlite_on_conflict_primary_key='FAIL')
)

SQLAlchemy отображает ограничение PRIMARY KEY отдельно, поэтому алгоритм разрешения конфликтов применяется к самому ограничению:

CREATE TABLE some_table (
    id INTEGER NOT NULL,
    PRIMARY KEY (id) ON CONFLICT FAIL
)

INSERT…ON CONFLICT (Upsert)

См.также

В этом разделе описывается DML версия «ON CONFLICT» для SQLite, которая возникает внутри оператора INSERT. О том, как «ON CONFLICT» применяется к оператору CREATE TABLE, смотрите ПО КОНФЛИКТУ поддержка ограничений.

Начиная с версии 3.24.0, SQLite поддерживает «апсерт» (обновление или вставку) строк в таблицу с помощью пункта ON CONFLICT оператора INSERT. Строка-кандидат будет вставлена только в том случае, если она не нарушает никаких ограничений уникальности или первичного ключа. В случае нарушения уникального ограничения может произойти вторичное действие, которое может быть либо «DO UPDATE», что указывает на то, что данные в целевой строке должны быть обновлены, либо «DO NOTHING», что означает молчаливый пропуск этой строки.

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

SQLAlchemy обеспечивает поддержку ON CONFLICT через специфическую для SQLite функцию insert(), которая предоставляет генеративные методы Insert.on_conflict_do_update() и Insert.on_conflict_do_nothing():

>>> from sqlalchemy.dialects.sqlite import insert

>>> insert_stmt = insert(my_table).values(
...     id='some_existing_id',
...     data='inserted value')

>>> do_update_stmt = insert_stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value')
... )

>>> print(do_update_stmt)
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?{stop}

>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
...     index_elements=['id']
... )

>>> print(do_nothing_stmt)
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO NOTHING

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

См.также

Upsert - в документации SQLite.

Указание цели

Оба метода определяют «цель» конфликта с помощью графового умозаключения:

  • Аргумент Insert.on_conflict_do_update.index_elements задает последовательность, содержащую строковые имена столбцов, объекты Column, и/или элементы SQL выражения, которые будут идентифицировать уникальный индекс или уникальное ограничение.

  • При использовании Insert.on_conflict_do_update.index_elements для вывода индекса, частичный индекс может быть выведен путем указания параметра Insert.on_conflict_do_update.index_where:

    >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
    
    >>> do_update_stmt = stmt.on_conflict_do_update(
    ...     index_elements=[my_table.c.user_email],
    ...     index_where=my_table.c.user_email.like('%@gmail.com'),
    ...     set_=dict(data=stmt.excluded.data)
    ...     )
    
    >>> print(do_update_stmt)
    {printsql}INSERT INTO my_table (data, user_email) VALUES (?, ?)
    ON CONFLICT (user_email)
    WHERE user_email LIKE '%@gmail.com'
    DO UPDATE SET data = excluded.data

Оговорка SET

ON CONFLICT...DO UPDATE используется для выполнения обновления уже существующего ряда, используя любую комбинацию новых значений, а также значений из предлагаемой вставки. Эти значения задаются с помощью параметра Insert.on_conflict_do_update.set_. Этот параметр принимает словарь, состоящий из прямых значений для UPDATE:

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')

>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value')
... )

>>> print(do_update_stmt)
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?

Предупреждение

Метод Insert.on_conflict_do_update() не принимает во внимание значения UPDATE по умолчанию на стороне Python или функции генерации, например, указанные с помощью Column.onupdate. Эти значения не будут использоваться для UPDATE в стиле ON CONFLICT, если они не указаны вручную в словаре Insert.on_conflict_do_update.set_.

Обновление с использованием исключенных значений INSERT

Для того чтобы ссылаться на предполагаемую строку вставки, специальный псевдоним Insert.excluded доступен как атрибут объекта Insert; этот объект создает префикс «исключено.» на столбце, который сообщает DO UPDATE обновить строку значением, которое было бы вставлено, если бы ограничение не сработало:

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )

>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author)
... )

>>> print(do_update_stmt)
{printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author

Дополнительные критерии ГДЕ

Метод Insert.on_conflict_do_update() также принимает предложение WHERE с помощью параметра Insert.on_conflict_do_update.where, который ограничивает строки, получающие UPDATE:

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )

>>> on_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author),
...     where=(my_table.c.status == 2)
... )
>>> print(on_update_stmt)
{printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
WHERE my_table.status = ?

Скакалка с «НЕЛЬЗЯ

ON CONFLICT можно использовать для того, чтобы полностью пропустить вставку строки, если возникает конфликт с уникальным ограничением; ниже это показано на примере метода Insert.on_conflict_do_nothing():

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
>>> print(stmt)
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING

Если DO NOTHING используется без указания столбцов или ограничений, это приводит к пропуску INSERT для любого уникального нарушения, которое имеет место:

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING

Тип Отражение

Типы SQLite отличаются от типов большинства других бэкендов баз данных тем, что строковое имя типа обычно не соответствует «типу» один к одному. Вместо этого SQLite связывает поведение типа в каждом столбце с одним из пяти так называемых «сродств типов» на основе шаблона соответствия строки типу.

Процесс отражения SQLAlchemy при проверке типов использует простую таблицу поиска, чтобы связать возвращаемые ключевые слова с предоставляемыми типами SQLAlchemy. Эта таблица поиска присутствует в диалекте SQLite, как и во всех других диалектах. Однако диалект SQLite имеет другую «запасную» процедуру для случаев, когда имя конкретного типа не находится в карте поиска; вместо этого он реализует схему «сродства типов» SQLite, описанную в разделе 2.1 https://www.sqlite.org/datatype3.html.

Предоставленная карта типов будет создавать прямые ассоциации на основе точного совпадения строкового имени для следующих типов:

BIGINT, BLOB, BOOLEAN, BOOLEAN, CHAR, DATE, DATETIME, FLOAT, DECIMAL, FLOAT, INTEGER, INTEGER, NUMERIC, REAL, SMALLINT, TEXT, TIME, TIMESTAMP, VARCHAR, NVARCHAR, NCHAR

Если имя типа не соответствует ни одному из вышеперечисленных типов, вместо него используется поиск «сродства типов»:

  • INTEGER возвращается, если имя типа включает строку INT.

  • TEXT возвращается, если имя типа включает строку CHAR, CLOB или TEXT.

  • NullType возвращается, если имя типа включает строку BLOB.

  • REAL возвращается, если имя типа включает строку REAL, FLOA или DOUB.

  • В противном случае используется тип NUMERIC.

Частичные индексы

Частичный индекс, например, использующий предложение WHERE, можно задать в системе DDL с помощью аргумента sqlite_where:

tbl = Table('testtbl', m, Column('data', Integer))
idx = Index('test_idx1', tbl.c.data,
            sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))

Индекс будет отображаться во время создания как:

CREATE INDEX test_idx1 ON testtbl (data)
WHERE data > 5 AND data < 10

Имена столбцов с точками

Использование имен таблиц или столбцов, в которых явно присутствуют точки, не рекомендуется. Хотя в целом это плохая идея для реляционных баз данных, поскольку точка является синтаксически значимым символом, драйвер SQLite до версии 3.10.0 SQLite имеет ошибку, которая требует, чтобы SQLAlchemy отфильтровывала эти точки в наборах результатов.

Ошибка, совершенно не относящаяся к SQLAlchemy, может быть проиллюстрирована следующим образом:

import sqlite3

assert sqlite3.sqlite_version_info < (3, 10, 0), "bug is fixed in this version"

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("create table x (a integer, b integer)")
cursor.execute("insert into x (a, b) values (1, 1)")
cursor.execute("insert into x (a, b) values (2, 2)")

cursor.execute("select x.a, x.b from x")
assert [c[0] for c in cursor.description] == ['a', 'b']

cursor.execute('''
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
''')
assert [c[0] for c in cursor.description] == ['a', 'b'], \
    [c[0] for c in cursor.description]

Второе утверждение не работает:

Traceback (most recent call last):
  File "test.py", line 19, in <module>
    [c[0] for c in cursor.description]
AssertionError: ['x.a', 'x.b']

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

SQLAlchemy полагается на то, что имена столбцов предсказуемы в том, как они соответствуют исходному утверждению, поэтому у диалекта SQLAlchemy нет другого выбора, кроме как отфильтровать их:

from sqlalchemy import create_engine

eng = create_engine("sqlite://")
conn = eng.connect()

conn.exec_driver_sql("create table x (a integer, b integer)")
conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
conn.exec_driver_sql("insert into x (a, b) values (2, 2)")

result = conn.exec_driver_sql("select x.a, x.b from x")
assert result.keys() == ["a", "b"]

result = conn.exec_driver_sql('''
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
''')
assert result.keys() == ["a", "b"]

Обратите внимание, что, несмотря на то, что SQLAlchemy отфильтровывает точки, обои имена по-прежнему адресуемы:

>>> row = result.first()
>>> row["a"]
1
>>> row["x.a"]
1
>>> row["b"]
1
>>> row["x.b"]
1

Поэтому обходной путь, применяемый SQLAlchemy, влияет только на CursorResult.keys() и Row.keys() в публичном API. В очень специфическом случае, когда приложение вынуждено использовать имена столбцов, содержащие точки, и функциональность CursorResult.keys() и Row.keys() должна возвращать эти точечные имена без изменений, может быть предоставлена опция выполнения sqlite_raw_colnames, либо на основе каждого из Connection:

result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql('''
    select x.a, x.b from x where a=1
    union
    select x.a, x.b from x where a=2
''')
assert result.keys() == ["x.a", "x.b"]

или на пер-Engine основе:

engine = create_engine("sqlite://", execution_options={"sqlite_raw_colnames": True})

При использовании опции выполнения per-Engine обратите внимание, что Запросы Core и ORM, использующие UNION, могут работать некорректно.

Параметры таблиц, специфичные для SQLite

Один из вариантов CREATE TABLE поддерживается непосредственно диалектом SQLite в сочетании с конструкцией Table:

  • WITHOUT ROWID:

    Table("some_table", metadata, ..., sqlite_with_rowid=False)

См.также

SQLite CREATE TABLE options

Отражение таблиц внутренней схемы

Методы отражения, возвращающие списки таблиц, опускают имена так называемых «внутренних объектов схемы SQLite», которыми SQLite называет любое имя объекта с префиксом sqlite_. Примером такого объекта является таблица sqlite_sequence, которая создается при использовании параметра столбца AUTOINCREMENT. Чтобы вернуть эти объекты, параметр sqlite_include_internal=True может быть передан таким методам, как MetaData.reflect() или Inspector.get_table_names().

Добавлено в версии 2.0: Добавлен параметр sqlite_include_internal=True. Ранее эти таблицы не игнорировались методами отражения SQLAlchemy.

Примечание

Параметр sqlite_include_internal не относится к «системным» таблицам, которые присутствуют в схемах типа sqlite_master.

См.также

SQLite Internal Schema Objects - в документации SQLite.

Типы данных SQLite

Как и во всех диалектах SQLAlchemy, все типы UPPERCASE, которые, как известно, действительны в SQLite, импортируются из диалекта верхнего уровня, независимо от того, происходят ли они из sqlalchemy.types или из локального диалекта:

from sqlalchemy.dialects.sqlite import (
    BLOB,
    BOOLEAN,
    CHAR,
    DATE,
    DATETIME,
    DECIMAL,
    FLOAT,
    INTEGER,
    NUMERIC,
    JSON,
    SMALLINT,
    TEXT,
    TIME,
    TIMESTAMP,
    VARCHAR,
)
Object Name Description

DATE

Представьте объект даты Python в SQLite с помощью строки.

DATETIME

Представьте объект Python datetime в SQLite с помощью строки.

JSON

Тип SQLite JSON.

TIME

Представьте объект времени Python в SQLite с помощью строки.

class sqlalchemy.dialects.sqlite.DATETIME

Представьте объект Python datetime в SQLite с помощью строки.

По умолчанию формат хранения строк следующий:

"%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"

например:

2021-03-15 12:05:57.105542

Формат входящего хранилища по умолчанию разбирается с помощью функции Python datetime.fromisoformat().

Изменено в версии 2.0: datetime.fromisoformat() используется для разбора строки datetime по умолчанию.

Формат хранения может быть в некоторой степени настроен с помощью параметров storage_format и regexp, например:

import re
from sqlalchemy.dialects.sqlite import DATETIME

dt = DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d "
                             "%(hour)02d:%(minute)02d:%(second)02d",
              regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)"
)
Параметры:
  • storage_format – строка формата, которая будет применена к дикте с ключами year, month, day, hour, minute, second и microsecond.

  • regexp – регулярное выражение, которое будет применяться к входящим строкам результатов, заменяя использование datetime.fromisoformat() для разбора входящих строк. Если regexp содержит именованные группы, то полученный дикт совпадений применяется к конструктору Python datetime() в качестве аргументов ключевых слов. В противном случае, если используются позиционные группы, конструктор datetime() вызывается с позиционными аргументами через *map(int, match_obj.groups(0)).

Классная подпись

класс sqlalchemy.dialects.sqlite.DATETIME (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.DateTime)

class sqlalchemy.dialects.sqlite.DATE

Представьте объект даты Python в SQLite с помощью строки.

По умолчанию формат хранения строк следующий:

"%(year)04d-%(month)02d-%(day)02d"

например:

2011-03-15

Формат входящего хранилища по умолчанию разбирается с помощью функции Python date.fromisoformat().

Изменено в версии 2.0: date.fromisoformat() используется для разбора строки даты по умолчанию.

Формат хранения может быть в некоторой степени настроен с помощью параметров storage_format и regexp, например:

import re
from sqlalchemy.dialects.sqlite import DATE

d = DATE(
        storage_format="%(month)02d/%(day)02d/%(year)04d",
        regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)")
    )
Параметры:
  • storage_format – строка формата, которая будет применена к дикте с ключами year, month и day.

  • regexp – регулярное выражение, которое будет применяться к входящим строкам результатов, заменяя использование date.fromisoformat() для разбора входящих строк. Если regexp содержит именованные группы, то полученный дикт совпадений применяется к конструктору Python date() в качестве аргументов ключевых слов. В противном случае, если используются позиционные группы, конструктор date() вызывается с позиционными аргументами через *map(int, match_obj.groups(0)).

Классная подпись

класс sqlalchemy.dialects.sqlite.DATE (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.Date)

class sqlalchemy.dialects.sqlite.JSON

Тип SQLite JSON.

SQLite поддерживает JSON начиная с версии 3.9 через расширение JSON1. Обратите внимание, что JSON1 - это loadable extension и как таковой может быть недоступен или может потребовать загрузки во время выполнения.

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

См.также

JSON - основная документация по общему кроссплатформенному типу данных JSON.

Тип JSON поддерживает сохранение значений JSON, а также основные индексные операции, предоставляемые типом данных JSON, адаптируя операции для отображения функции JSON_EXTRACT, обернутой в функцию JSON_QUOTE на уровне базы данных. Извлеченные значения заключаются в кавычки, чтобы гарантировать, что результаты всегда являются строковыми значениями JSON.

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

Members

__init__()

Классная подпись

класс sqlalchemy.dialects.sqlite.JSON (sqlalchemy.types.JSON)

method sqlalchemy.dialects.sqlite.JSON.__init__(none_as_null: bool = False)

наследуется от sqlalchemy.types.JSON.__init__ метода JSON

Сконструируйте тип JSON.

Параметры:

none_as_null=False – если True, сохранять значение None как значение SQL NULL, а не JSON-кодировку null. Обратите внимание, что когда этот флаг равен False, конструкция null() все еще может быть использована для сохранения значения NULL, которое может быть передано непосредственно как значение параметра, которое специально интерпретируется типом JSON как SQL NULL:: from sqlalchemy import null conn.execute(table.insert(), {«data»: null()}) … note:: JSON.none_as_null не применяется к значениям, переданным в Column.default и Column.server_default; значение None, переданное для этих параметров, означает «по умолчанию отсутствует». Кроме того, при использовании в выражениях сравнения SQL, значение Python None продолжает ссылаться на SQL null, а не на JSON NULL. Флаг JSON.none_as_null явно указывает на постоянство значения в операторе INSERT или UPDATE. Значение JSON.NULL следует использовать для SQL-выражений, которые хотят сравнить с JSON null. … см. также:: JSON.NULL

class sqlalchemy.dialects.sqlite.TIME

Представьте объект времени Python в SQLite с помощью строки.

По умолчанию формат хранения строк следующий:

"%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"

например:

12:05:57.10558

Формат входящего хранилища по умолчанию разбирается с помощью функции Python time.fromisoformat().

Изменено в версии 2.0: time.fromisoformat() используется для разбора строки времени по умолчанию.

Формат хранения может быть в некоторой степени настроен с помощью параметров storage_format и regexp, например:

import re
from sqlalchemy.dialects.sqlite import TIME

t = TIME(storage_format="%(hour)02d-%(minute)02d-"
                        "%(second)02d-%(microsecond)06d",
         regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
)
Параметры:
  • storage_format – строка формата, которая будет применена к дикте с ключами hour, minute, second и microsecond.

  • regexp – регулярное выражение, которое будет применяться к входящим строкам результатов, заменяя использование datetime.fromisoformat() для разбора входящих строк. Если regexp содержит именованные группы, то полученный дикт совпадений применяется к конструктору Python time() в качестве аргументов ключевых слов. В противном случае, если используются позиционные группы, конструктор time() вызывается с позиционными аргументами через *map(int, match_obj.groups(0)).

Классная подпись

класс sqlalchemy.dialects.sqlite.TIME (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.Time)

Конструкции SQLite DML

Object Name Description

insert(table)

Создайте конструкцию sqlite-специфического варианта Insert.

Insert

Специфическая для SQLite реализация INSERT.

function sqlalchemy.dialects.sqlite.insert(table)

Создайте конструкцию sqlite-специфического варианта Insert.

Функция sqlalchemy.dialects.sqlite.insert() создает sqlalchemy.dialects.sqlite.Insert. Этот класс основан на диалектно-агностической конструкции Insert, которая может быть построена с помощью функции insert() в SQLAlchemy Core.

Конструкция Insert включает дополнительные методы Insert.on_conflict_do_update(), Insert.on_conflict_do_nothing().

class sqlalchemy.dialects.sqlite.Insert

Специфическая для SQLite реализация INSERT.

Добавляет методы для специфических для SQLite синтаксисов, таких как ON CONFLICT.

Объект Insert создается с помощью функции sqlalchemy.dialects.sqlite.insert().

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

Классная подпись

класс sqlalchemy.dialects.sqlite.Insert (sqlalchemy.sql.expression.Insert)

attribute sqlalchemy.dialects.sqlite.Insert.excluded

Предоставьте пространство имен excluded для оператора ON CONFLICT

Пункт ON CONFLICT в SQLite позволяет ссылаться на строку, которая будет вставлена, известную как excluded. Этот атрибут обеспечивает возможность ссылки на все столбцы в этой строке.

Совет

Атрибут Insert.excluded является экземпляром ColumnCollection, который предоставляет интерфейс, аналогичный интерфейсу коллекции Table.c, описанной в Доступ к таблицам и столбцам. В этой коллекции обычные имена доступны как атрибуты (например, stmt.excluded.some_column), но к специальным именам и именам методов словаря следует обращаться с помощью индексного доступа, например, stmt.excluded["column name"] или stmt.excluded["values"]. Дополнительные примеры см. в строке документации для ColumnCollection.

attribute sqlalchemy.dialects.sqlite.Insert.inherit_cache: Optional[bool] = False

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

По умолчанию атрибут имеет значение None, что указывает на то, что конструкция еще не приняла во внимание целесообразность ее участия в кэшировании; функционально это эквивалентно установке значения False, за исключением того, что при этом выдается предупреждение.

Этот флаг может быть установлен в True на определенном классе, если SQL, соответствующий объекту, не изменяется на основе атрибутов, локальных для этого класса, а не его суперкласса.

См.также

Включение поддержки кэширования для пользовательских конструкций - общие направляющие для установки атрибута HasCacheKey.inherit_cache для сторонних или определенных пользователем конструкций SQL.

method sqlalchemy.dialects.sqlite.Insert.on_conflict_do_nothing(index_elements=None, index_where=None) Self

Указывает действие DO NOTHING для пункта ON CONFLICT.

Параметры:
  • index_elements – Последовательность, состоящая из строковых имен столбцов, объектов Column или других объектов выражения столбцов, которые будут использоваться для вывода целевого индекса или уникального ограничения.

  • index_where – Дополнительный критерий WHERE, который может быть использован для вывода условного целевого индекса.

method sqlalchemy.dialects.sqlite.Insert.on_conflict_do_update(index_elements=None, index_where=None, set_=None, where=None) Self

Указывает действие DO UPDATE SET для условия ON CONFLICT.

Параметры:
  • index_elements – Последовательность, состоящая из строковых имен столбцов, объектов Column или других объектов выражения столбцов, которые будут использоваться для вывода целевого индекса или уникального ограничения.

  • index_where – Дополнительный критерий WHERE, который может быть использован для вывода условного целевого индекса.

  • set_ – Словарь или другой объект отображения, где ключами являются либо имена столбцов в целевой таблице, либо Column объекты или другие ORM-сопоставленные столбцы, совпадающие со столбцами целевой таблицы, а в качестве значений - выражения или литералы, определяющие действия SET, которые необходимо предпринять. … versionadded:: 1.4 Параметр Insert.on_conflict_do_update.set_ поддерживает Column объекты из целевой Table в качестве ключей. … предупреждение:: Этот словарь не учитывает значения UPDATE по умолчанию, определенные Python, или функции генерации, например, указанные с помощью Column.onupdate. Эти значения не будут использоваться для UPDATE в стиле ON CONFLICT, если они не указаны вручную в словаре Insert.on_conflict_do_update.set_.

  • where – Необязательный аргумент. Если присутствует, может быть литеральной строкой SQL или допустимым выражением для предложения WHERE, которое ограничивает строки, затрагиваемые DO UPDATE SET. Строки, не удовлетворяющие условию WHERE, не будут обновлены (эффективно DO NOTHING для этих строк).

Pysqlite

Поддержка базы данных SQLite через драйвер pysqlite. Обратите внимание, что pysqlite - это тот же драйвер, что и модуль sqlite3, включенный в дистрибутив Python.

DBAPI

Documentation and download information (if applicable) for pysqlite is available at: https://docs.python.org/library/sqlite3.html

Подключение

Connect String:

sqlite+pysqlite:///file_path

Водитель

Python DBAPI sqlite3 является стандартным для всех современных версий Python; для cPython и Pypy дополнительная установка не требуется.

Соедините струны

Спецификация файла для базы данных SQLite берется в качестве части URL «database». Обратите внимание, что формат URL SQLAlchemy следующий:

driver://user:pass@host/database

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

# relative path
e = create_engine('sqlite:///path/to/database.db')

Абсолютный путь, который обозначается началом с косой черты, означает, что вам нужно четыре косых черты:

# absolute path
e = create_engine('sqlite:////path/to/database.db')

Чтобы использовать путь Windows, можно использовать обычные спецификации дисков и обратные косые черты. Двойные обратные косые черты, вероятно, необходимы:

# absolute path on Windows
e = create_engine('sqlite:///C:\\path\\to\\database.db')

Идентификатор sqlite :memory: используется по умолчанию, если не указан путь к файлу. Укажите sqlite:// и ничего больше:

# in-memory database
e = create_engine('sqlite://')

Связи URI

Современные версии SQLite поддерживают альтернативную систему подключения с помощью driver level URI, которая имеет то преимущество, что можно передавать дополнительные аргументы на уровне драйвера, включая такие опции, как «только чтение». Драйвер Python sqlite3 поддерживает этот режим в современных версиях Python 3. Драйвер SQLAlchemy pysqlite поддерживает этот режим использования, указывая «uri=true» в строке запроса URL. «URI» на уровне SQLite сохраняется как часть «database» в URL SQLAlchemy (то есть после слэша):

e = create_engine("sqlite:///file:path/to/database?mode=ro&uri=true")

Примечание

Параметр «uri=true» должен присутствовать в строке запроса URL. Если он присутствует только в словаре параметров create_engine.connect_args, он не будет работать так, как ожидается.

Логика примиряет одновременное присутствие строки запроса SQLAlchemy и строки запроса SQLite, разделяя параметры, принадлежащие драйверу Python sqlite3, и параметры, принадлежащие URI SQLite. Это достигается за счет использования фиксированного списка параметров, которые, как известно, принимаются Python-стороной драйвера. Например, чтобы включить URL, который указывает параметры Python sqlite3 «timeout» и «check_same_thread», а также параметры SQLite «mode» и «nolock», все они могут быть переданы вместе в строке запроса:

e = create_engine(
    "sqlite:///file:path/to/database?"
    "check_same_thread=true&timeout=10&mode=ro&nolock=1&uri=true"
)

Выше, для pysqlite / sqlite3 DBAPI будут переданы аргументы в виде:

sqlite3.connect(
    "file:path/to/database?mode=ro&nolock=1",
    check_same_thread=True, timeout=10, uri=True
)

Что касается будущих параметров, добавляемых как в драйверы Python, так и в родной драйвер. новые имена параметров, добавляемые в схему URI SQLite, должны автоматически подстраиваться под эту схему. Новые имена параметров, добавленные в драйвер Python, могут быть учтены путем указания их в словаре create_engine.connect_args, пока SQLAlchemy не добавит поддержку диалекта. Для менее вероятного случая, когда родной драйвер SQLite добавляет новое имя параметра, которое пересекается с одним из существующих, известных параметров драйвера Python (например, «timeout»), диалект SQLAlchemy потребует корректировки схемы URL, чтобы продолжать поддерживать это.

Как и всегда для всех диалектов SQLAlchemy, весь процесс «URL» может быть обойден в create_engine() с помощью параметра create_engine.creator, который позволяет создать пользовательский callable, создающий соединение на уровне драйвера Python sqlite3 напрямую.

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

См.также

Uniform Resource Identifiers - в документации SQLite

Поддержка регулярных выражений

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

Поддержка оператора ColumnOperators.regexp_match() обеспечивается с помощью функции re.search в Python. Сам SQLite не включает рабочий оператор регулярного выражения; вместо этого он включает нереализованный оператор-заполнитель REGEXP, который вызывает определяемую пользователем функцию, которую необходимо предоставить.

Реализация SQLAlchemy использует хук pysqlite create_function следующим образом:

def regexp(a, b):
    return re.search(a, b) is not None

sqlite_connection.create_function(
    "regexp", 2, regexp,
)

В настоящее время нет поддержки флагов регулярного выражения в качестве отдельного аргумента, поскольку они не поддерживаются оператором REGEXP в SQLite, однако они могут быть включены в строку регулярного выражения. Подробности см. в разделе Python regular expressions.

См.также

Python regular expressions: Документация по синтаксису регулярных выражений Python.

Совместимость с «родными» типами даты и времени sqlite3

Драйвер pysqlite включает опции sqlite3.PARSE_DECLTYPES и sqlite3.PARSE_COLNAMES, которые имеют эффект того, что любой столбец или выражение, явно приведенное к значению «дата» или «временная метка», будет преобразовано в объект Python date или datetime. Типы даты и времени, предоставляемые диалектом pysqlite, в настоящее время не совместимы с этими опциями, поскольку они преобразуют дату/время ISO, включая микросекунды, чего не делает драйвер pysqlite. Кроме того, SQLAlchemy в настоящее время не обеспечивает автоматическое отображение синтаксиса «cast», необходимого для того, чтобы отдельно стоящие функции «current_timestamp» и «current_date» могли возвращать типы дата/время нативно. К сожалению, pysqlite не предоставляет стандартные типы DBAPI в cursor.description, в результате чего SQLAlchemy не имеет возможности определить эти типы «на лету» без дорогостоящих проверок типов в каждой строке.

Учитывая, что опция парсинга pysqlite не рекомендуется и не должна быть необходима для использования с SQLAlchemy, использование PARSE_DECLTYPES может быть принудительным, если настроить «native_datetime=True» в create_engine():

engine = create_engine('sqlite://',
    connect_args={'detect_types':
        sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},
    native_datetime=True
)

Если этот флаг установлен, типы DATE и TIMESTAMP (но обратите внимание - не типы DATETIME или TIME… вы еще не запутались?) не будут выполнять никакой обработки параметров привязки или результатов. Выполнение «func.current_date()» вернет строку. «func.current_timestamp()» зарегистрирована как возвращающая тип DATETIME в SQLAlchemy, поэтому эта функция все еще получает обработку результата на уровне SQLAlchemy.

Поведение потоков/пулинга

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

Запрет потока известен как «проверка одного и того же потока» и может контролироваться с помощью параметра sqlite3 check_same_thread, который отключает или включает эту проверку. По умолчанию SQLAlchemy автоматически устанавливает check_same_thread в False всякий раз, когда используется файловая база данных, чтобы установить совместимость с классом пула по умолчанию QueuePool.

SQLAlchemy pysqlite DBAPI устанавливает пул соединений по-разному в зависимости от типа запрашиваемой базы данных SQLite:

  • Если указана база данных SQLite :memory:, диалект по умолчанию будет использовать SingletonThreadPool. Этот пул поддерживает одно соединение на поток, так что все обращения к движку в рамках текущего потока используют одну и ту же базу данных :memory: - другие потоки будут обращаться к другой базе данных :memory:. Параметр check_same_thread по умолчанию имеет значение True.

  • Если указана база данных на основе файлов, диалект будет использовать QueuePool в качестве источника соединений. В то же время, флаг check_same_thread по умолчанию установлен в False, если он не переопределен.

    Изменено в версии 2.0: Движки файловых баз данных SQLite теперь используют QueuePool по умолчанию. Ранее использовались NullPool. Класс NullPool можно использовать, указав его через параметр create_engine.poolclass.

Отключение пула соединений для файловых баз данных

Пулинг можно отключить для базы данных на основе файлов, указав реализацию NullPool для параметра poolclass():

from sqlalchemy import NullPool
engine = create_engine("sqlite:///myfile.db", poolclass=NullPool)

Было замечено, что реализация NullPool несет чрезвычайно малые затраты производительности при повторных проверках из-за отсутствия повторного использования соединений, реализованного в QueuePool. Тем не менее, использование этого класса может быть полезным, если приложение испытывает проблемы с блокировкой файлов.

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

Чтобы использовать базу данных :memory: в многопоточном сценарии, один и тот же объект соединения должен быть общим для всех потоков, поскольку база данных существует только в рамках этого соединения. Реализация StaticPool будет поддерживать одно соединение глобально, а флаг check_same_thread может быть передан Pysqlite как False:

from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite://',
                    connect_args={'check_same_thread':False},
                    poolclass=StaticPool)

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

Использование временных таблиц в SQLite

Из-за того, как SQLite работает с временными таблицами, если вы хотите использовать временную таблицу в файловой базе данных SQLite при нескольких выключениях из пула соединений, например, при использовании ORM Session, где временная таблица должна оставаться после вызова Session.commit() или Session.rollback(), необходимо использовать пул, поддерживающий одно соединение. Используйте SingletonThreadPool, если область видимости нужна только в текущем потоке, или StaticPool, если область видимости нужна в нескольких потоках, для данного случая:

# maintain the same connection per thread
from sqlalchemy.pool import SingletonThreadPool
engine = create_engine('sqlite:///mydb.db',
                    poolclass=SingletonThreadPool)


# maintain the same connection across all threads
from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite:///mydb.db',
                    poolclass=StaticPool)

Обратите внимание, что SingletonThreadPool должен быть настроен на количество потоков, которые будут использоваться; при превышении этого количества соединения будут закрываться недетерминированным образом.

Работа со смешанными строковыми / двоичными столбцами

База данных SQLite слабо типизирована, поэтому при использовании двоичных значений, которые в Python представлены как b'some string', возможно, что конкретная база данных SQLite может иметь значения данных в разных строках, где некоторые из них будут возвращены драйвером Pysqlite как значения b'', а другие будут возвращены как строки Python, например, значения ''. Эта ситуация не возникает, если последовательно используется тип данных SQLAlchemy LargeBinary, однако если в конкретной базе данных SQLite есть данные, которые были вставлены с помощью драйвера Pysqlite напрямую, или при использовании типа SQLAlchemy String, который позже был изменен на LargeBinary, таблица не будет последовательно читаться, поскольку тип данных SQLAlchemy LargeBinary не работает со строками, поэтому у него нет возможности «закодировать» значение в формате строки.

Чтобы справиться с таблицей SQLite, содержащей смешанные строковые / двоичные данные в одном столбце, используйте пользовательский тип, который будет проверять каждую строку отдельно:

from sqlalchemy import String
from sqlalchemy import TypeDecorator

class MixedBinary(TypeDecorator):
    impl = String
    cache_ok = True

    def process_result_value(self, value, dialect):
        if isinstance(value, str):
            value = bytes(value, 'utf-8')
        elif value is not None:
            value = bytes(value)

        return value

Затем используйте приведенный выше тип данных MixedBinary в том месте, где обычно используется LargeBinary.

Сериализуемая изоляция / Точки сохранения / Транзакционный DDL

В разделе Поведение блокировки базы данных / параллелизм мы ссылаемся на ряд проблем драйвера pysqlite, которые препятствуют корректной работе некоторых функций SQLite. Драйвер pysqlite DBAPI имеет несколько давних ошибок, которые влияют на корректность его транзакционного поведения. В режиме работы по умолчанию такие возможности SQLite, как изоляция SERIALIZABLE, транзакционный DDL и поддержка SAVEPOINT, не функционируют, и для того, чтобы использовать эти возможности, необходимо использовать обходные пути.

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

SQLAlchemy предпочитает не изменять это поведение по умолчанию, поскольку это давно ожидаемое поведение драйвера pysqlite; если и когда драйвер pysqlite попытается устранить эти проблемы, это будет скорее драйвером по умолчанию для SQLAlchemy.

Хорошей новостью является то, что с помощью нескольких событий мы можем полностью реализовать поддержку транзакций, полностью отключив функцию pysqlite и выдавая BEGIN самостоятельно. Это достигается с помощью двух слушателей событий:

from sqlalchemy import create_engine, event

engine = create_engine("sqlite:///myfile.db")

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.exec_driver_sql("BEGIN")

Предупреждение

При использовании приведенного выше рецепта рекомендуется не использовать настройку Connection.execution_options.isolation_level на Connection и create_engine() с драйвером SQLite, так как эта функция обязательно также изменит настройку «.isolation_level».

Выше мы перехватываем новое соединение pysqlite и отключаем любую транзакционную интеграцию. Затем, в тот момент, когда SQLAlchemy узнает, что должна начаться транзакция, мы сами испускаем "BEGIN".

Когда мы берем под контроль "BEGIN", мы также можем напрямую управлять режимами блокировки SQLite, представленными в BEGIN TRANSACTION, добавляя нужный режим блокировки в наш "BEGIN":

@event.listens_for(engine, "begin")
def do_begin(conn):
    conn.exec_driver_sql("BEGIN EXCLUSIVE")

См.также

BEGIN TRANSACTION - на сайте SQLite

sqlite3 SELECT does not BEGIN a transaction - на трекере ошибок Python

sqlite3 module breaks transactions and potentially corrupts data - на трекере ошибок Python

Функции, определяемые пользователем

pysqlite поддерживает метод create_function(), который позволяет нам создавать собственные определяемые пользователем функции (UDF) на языке Python и использовать их непосредственно в запросах SQLite. Эти функции регистрируются в определенном DBAPI Connection.

SQLAlchemy использует пул соединений с файловыми базами данных SQLite, поэтому нам необходимо убедиться, что UDF прикрепляется к соединению при его создании. Это достигается с помощью слушателя событий:

from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy import text


def udf():
    return "udf-ok"


engine = create_engine("sqlite:///./db_file")


@event.listens_for(engine, "connect")
def connect(conn, rec):
    conn.create_function("udf", 0, udf)


for i in range(5):
    with engine.connect() as conn:
        print(conn.scalar(text("SELECT UDF()")))

Айосклит

Support for the SQLite database via the aiosqlite driver.

DBAPI

Documentation and download information (if applicable) for aiosqlite is available at: https://pypi.org/project/aiosqlite/

Connecting

Connect String:

sqlite+aiosqlite:///file_path

Диалект aiosqlite обеспечивает поддержку интерфейса SQLAlchemy asyncio, работающего поверх pysqlite.

aiosqlite - это обертка вокруг pysqlite, которая использует фоновый поток для каждого соединения. На самом деле она не использует неблокирующий ввод-вывод, поскольку базы данных SQLite не основаны на сокетах. Однако она предоставляет рабочий интерфейс asyncio, который полезен для тестирования и создания прототипов.

Используя специальный посреднический слой asyncio, диалект aiosqlite можно использовать в качестве бэкенда для пакета расширения SQLAlchemy asyncio.

Обычно этот диалект следует использовать только с функцией создания двигателя create_async_engine():

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("sqlite+aiosqlite:///filename")

URL передает все аргументы драйвера pysqlite, поэтому все аргументы соединения такие же, как и для драйвера Pysqlite.

Функции, определяемые пользователем

aiosqlite расширяет pysqlite для поддержки async, поэтому мы можем создавать собственные пользовательские функции (UDF) на Python и использовать их непосредственно в запросах SQLite, как описано здесь: Функции, определяемые пользователем.

Pysqlcipher

Поддержка базы данных SQLite через драйвер pysqlcipher.Диалект для поддержки DBAPI, использующих бэкенд SQLCipher.

Подключение

Connect String:

sqlite+pysqlcipher://:passphrase@/file_path[?kdf_iter=<iter>]

Водитель

Текущая логика выбора диалекта такова:

Предупреждение

Драйверы DBAPI pysqlcipher3 и pysqlcipher больше не поддерживаются; драйвер sqlcipher3 на момент написания этой статьи является актуальным. Для будущей совместимости можно использовать любой pysqlcipher-совместимый DBAPI следующим образом:

import sqlcipher_compatible_driver

from sqlalchemy import create_engine

e = create_engine(
    "sqlite+pysqlcipher://:password@/dbname.db",
    module=sqlcipher_compatible_driver
)

Эти драйверы используют механизм SQLCipher. Эта система по сути вводит в SQLite новые команды PRAGMA, которые позволяют задать кодовую фразу и другие параметры шифрования, позволяя зашифровать файл базы данных.

Соедините струны

Формат строки подключения во всех отношениях такой же, как и у драйвера pysqlite, за исключением того, что теперь принимается поле «пароль», которое должно содержать кодовую фразу:

e = create_engine('sqlite+pysqlcipher://:testing@/foo.db')

Для абсолютного пути к файлу в имени базы данных следует использовать две ведущие косые черты:

e = create_engine('sqlite+pysqlcipher://:testing@//path/to/foo.db')

В строке запроса может быть передан выбор дополнительных прагм, связанных с шифрованием, поддерживаемых SQLCipher и документированных на сайте https://www.zetetic.net/sqlcipher/sqlcipher-api/, в результате чего эта прагма будет вызываться для каждого нового соединения. В настоящее время поддерживаются cipher, kdf_iter cipher_page_size и cipher_use_hmac:

e = create_engine('sqlite+pysqlcipher://:testing@/foo.db?cipher=aes-256-cfb&kdf_iter=64000')

Предупреждение

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

Поведение при объединении

Драйвер вносит изменение в поведение пула по умолчанию в pysqlite, как описано в Поведение потоков/пулинга. Было замечено, что драйвер pysqlcipher значительно медленнее при подключении, чем драйвер pysqlite, скорее всего, из-за накладных расходов на шифрование, поэтому диалект здесь по умолчанию использует реализацию SingletonThreadPool вместо пула NullPool, используемого pysqlite. Как всегда, реализация пула полностью настраивается с помощью параметра create_engine.poolclass; StaticPool может быть более подходящим для однопоточного использования, или NullPool может использоваться для предотвращения длительного удержания незашифрованных соединений открытыми, за счет более медленного времени запуска новых соединений.

Вернуться на верх