SQLite¶
Support for the SQLite database.
The following table summarizes current support levels for database release versions.
Support type |
Versions |
---|---|
3.21, 3.28+ |
|
3.12+ |
|
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», которыми 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 |
---|---|
Представьте объект даты Python в SQLite с помощью строки. |
|
Представьте объект Python datetime в SQLite с помощью строки. |
|
Тип SQLite JSON. |
|
Представьте объект времени 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
Классная подпись
класс
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, значение PythonNone
продолжает ссылаться на SQL null, а не на JSON NULL. ФлагJSON.none_as_null
явно указывает на постоянство значения в операторе INSERT или UPDATE. ЗначениеJSON.NULL
следует использовать для SQL-выражений, которые хотят сравнить с JSON null. … см. также::JSON.NULL
-
method
- 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-специфического варианта |
Специфическая для 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
для этих строк).
-
attribute
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>]
Водитель¶
Текущая логика выбора диалекта такова:
Если в параметре
create_engine.module
указан модуль DBAPI, то используется этот модуль.В противном случае для Python 3 выберите https://pypi.org/project/sqlcipher3/.
Если нет доступа, перейдите на https://pypi.org/project/pysqlcipher3/
Для Python 2 используется https://pypi.org/project/pysqlcipher/.
Предупреждение
Драйверы 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
может использоваться для предотвращения длительного удержания незашифрованных соединений открытыми, за счет более медленного времени запуска новых соединений.