SQLite¶

Support for the SQLite database.

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

**Supported SQLite versions**¶
Support type	Versions
Supported version	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.

pysqlite
aiosqlite
pysqlcipher

日期和时间类型¶

Date and Time Types

中文

SQLite 不具备内建的 DATE、TIME 或 DATETIME 类型，pysqlite 也不提供开箱即用的功能来在 Python 的 datetime 对象与 SQLite 支持的格式之间进行转换。 SQLAlchemy 提供的 DateTime 及相关类型在使用 SQLite 时，提供了日期格式化与解析的功能。其具体实现类为 DATETIME、DATE 与 TIME。这些类型会将日期和时间表示为 ISO 格式的字符串，这种格式在排序时也表现良好。此机制不依赖于传统的 “libc” 内部函数，因此历史日期也能被完全支持。

英文

SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does not provide out of the box functionality for translating values between 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. These types represent dates and times as ISO formatted strings, which also nicely support ordering. There’s no reliance on typical “libc” internals for these functions so historical dates are fully supported.

确保文本关联性¶

Ensuring Text affinity

中文

这些类型在生成 DDL 时会使用标准的 DATE、TIME 和 DATETIME 类型标识。然而，也可以为这些类型应用自定义的存储格式。当检测到存储格式中不含有字母字符时，这些类型的 DDL 会被渲染为 DATE_CHAR、TIME_CHAR 和 DATETIME_CHAR，以确保列依然具有文本关联性（textual affinity）。

参见

类型关联性（Type Affinity） - SQLite 文档中的相关说明

英文

The DDL rendered for these types is the standard DATE, TIME and DATETIME indicators. However, custom storage formats can also be applied to these types. When the storage format is detected as containing no alpha characters, the DDL for these types is rendered as DATE_CHAR, TIME_CHAR, and DATETIME_CHAR, so that the column continues to have textual affinity.

参见

Type Affinity - in the SQLite documentation

SQLite 自动递增行为¶

SQLite Auto Incrementing Behavior

中文

关于 SQLite 自增主键的背景资料参见： https://sqlite.org/autoinc.html

关键概念：

SQLite 拥有一种隐式的 “自动增长” 特性，当某个非复合主键列被定义为 “INTEGER PRIMARY KEY” 时，该特性即被启用。
SQLite 同时也支持显式的 “AUTOINCREMENT” 关键字，但该关键字 并不等同于 上述隐式自动增长机制。此关键字通常 不推荐使用 。除非使用了 SQLite 特定的参数，SQLAlchemy 默认不会生成该关键字（见下文）。但无论如何，若要使用该关键字，仍然要求列的数据类型名称必须是 “INTEGER”。

英文

Background on SQLite’s autoincrement is at: https://sqlite.org/autoinc.html

Key concepts:

SQLite has an implicit “auto increment” feature that takes place for any non-composite primary-key column that is specifically created using “INTEGER PRIMARY KEY” for the type + primary key.
SQLite also has an explicit “AUTOINCREMENT” keyword, that is not equivalent to the implicit autoincrement feature; this keyword is not recommended for general use. SQLAlchemy does not render this keyword unless a special SQLite-specific directive is used (see below). However, it still requires that the column’s type is named “INTEGER”.

使用 AUTOINCREMENT 关键字¶

Using the AUTOINCREMENT Keyword

中文

若要在生成 DDL 时在主键列上显式添加 AUTOINCREMENT 关键字，可以在 Table 构造中添加 sqlite_autoincrement=True 标志:

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

英文

To specifically render the AUTOINCREMENT keyword on the primary key column when rendering DDL, add the flag sqlite_autoincrement=True to the Table construct:

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

允许 SQLAlchemy 中除 Integer/INTEGER 之外的其他类型的自动递增行为¶

Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER

中文

SQLite 的类型模型基于命名约定。这意味着，任何包含 "INT" 子字符串的类型名称都将被认为具有 “integer 关联性”。例如，类型 "BIGINT"、"SPECIAL_INT"，甚至 "XYZINTQPR"，在 SQLite 中都会被视为 “integer” 关联性类型。但需特别注意： 无论使用显式还是隐式的 autoincrement 特性，SQLite 都要求列类型的名称必须 严格为 "INTEGER"。因此，如果应用中主键使用了 BigInteger 类型，在 SQLite 中，为了启用自增功能，该类型在生成 CREATE TABLE 语句时必须渲染为 "INTEGER"。

一种实现方式是仅在 SQLite 上使用 Integer，并借助 TypeEngine.with_variant():

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

另一种方式是自定义一个继承自 BigInteger 的子类，当其针对 SQLite 编译时将类型名称重写为 INTEGER:

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)
)

参见

TypeEngine.with_variant()

自定义 SQL 构造和编译扩展

SQLite 3 中的数据类型

英文

SQLite’s typing model is based on naming conventions. Among other things, this means that any type name which contains the substring "INT" will be determined to be of “integer affinity”. A type named "BIGINT", "SPECIAL_INT" or even "XYZINTQPR", will be considered by SQLite to be of “integer” affinity. However, the SQLite autoincrement feature, whether implicitly or explicitly enabled, requires that the name of the column’s type is exactly the string “INTEGER”. Therefore, if an application uses a type like BigInteger for a primary key, on SQLite this type will need to be rendered as the name "INTEGER" when emitting the initial CREATE TABLE statement in order for the autoincrement behavior to be available.

One approach to achieve this is to use Integer on SQLite only using TypeEngine.with_variant():

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

Another is to use a subclass of BigInteger that overrides its DDL name to be INTEGER when compiled against 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)
)

参见

TypeEngine.with_variant()

自定义 SQL 构造和编译扩展

Datatypes In SQLite Version 3

数据库锁定行为/并发性¶

Database Locking Behavior / Concurrency

中文

SQLite 并非为高并发写操作而设计。由于数据库本质上是一个文件，在事务中的写操作会将该文件完全锁定，意味着在写期间，只有一个 “连接”（本质上是一个文件句柄）拥有对数据库的独占访问权限 —— 所有其他“连接”在此期间都会被阻塞。

Python 的 DBAPI 规范也定义了一种始终处于事务中的连接模型；它没有 connection.begin() 方法，只有 connection.commit() 与 connection.rollback()，调用这些方法后应立即开始一个新事务。这似乎暗示 SQLite 驱动理论上在任意时间只允许一个文件句柄访问某个数据库文件；但实际上，在 SQLite 自身以及 pysqlite 驱动的多方面因素影响下，这一限制已大大放宽。

然而，无论采用哪种锁定模式，一旦事务开始并至少执行了一条 DML 语句（如 INSERT、UPDATE、DELETE），SQLite 仍会锁定整个数据库文件，此时其他事务在试图执行 DML 时也会被阻塞。默认情况下，该阻塞时间非常短，超时后会报错。

在配合 SQLAlchemy ORM 使用时，这一行为尤为关键。 SQLAlchemy 的 Session 对象默认运行在一个事务中，且在启用了自动刷新（autoflush）模式时，可能在任意 SELECT 语句之前就会发出 DML。这可能会导致数据库比预期更快地进入锁定状态。虽然可以在一定程度上调整 SQLite 及其 pysqlite 驱动的锁定模式，但需注意的是，在 SQLite 中尝试实现高写入并发本就是一场败战。

如需了解更多关于 SQLite 在设计上不具备写入并发的资料，请参阅官方文档末尾部分：哪些场景下应考虑其他关系型数据库 —— 高并发需求

以下各小节将介绍 SQLite 的文件架构所影响的领域，这些领域在使用 pysqlite 驱动时通常需要特殊处理或绕过方案。

英文

SQLite is not designed for a high level of write concurrency. The database itself, being a file, is locked completely during write operations within transactions, meaning exactly one “connection” (in reality a file handle) has exclusive access to the database during this period - all other “connections” will be blocked during this time.

The Python DBAPI specification also calls for a connection model that is always in a transaction; there is no connection.begin() method, only connection.commit() and connection.rollback(), upon which a new transaction is to be begun immediately. This may seem to imply that the SQLite driver would in theory allow only a single filehandle on a particular database file at any time; however, there are several factors both within SQLite itself as well as within the pysqlite driver which loosen this restriction significantly.

However, no matter what locking modes are used, SQLite will still always lock the database file once a transaction is started and DML (e.g. INSERT, UPDATE, DELETE) has at least been emitted, and this will block other transactions at least at the point that they also attempt to emit DML. By default, the length of time on this block is very short before it times out with an error.

This behavior becomes more critical when used in conjunction with the SQLAlchemy ORM. SQLAlchemy’s Session object by default runs within a transaction, and with its autoflush model, may emit DML preceding any SELECT statement. This may lead to a SQLite database that locks more quickly than is expected. The locking mode of SQLite and the pysqlite driver can be manipulated to some degree, however it should be noted that achieving a high degree of write-concurrency with SQLite is a losing battle.

For more information on SQLite’s lack of write concurrency by design, please see Situations Where Another RDBMS May Work Better - High Concurrency near the bottom of the page.

The following subsections introduce areas that are impacted by SQLite’s file-based architecture and additionally will usually require workarounds to work when using the pysqlite driver.

事务隔离级别/自动提交¶

Transaction Isolation Level / Autocommit

中文

SQLite 以一种非标准的方式支持“事务隔离”，主要体现在两个方面。其一是 PRAGMA read_uncommitted 指令。此设置可在 SQLite 的默认 SERIALIZABLE 隔离级别与一种通常称为 READ UNCOMMITTED 的“脏读”隔离模式之间切换。

SQLAlchemy 通过 create_engine() 的 create_engine.isolation_level 参数与该 PRAGMA 指令集成。与 SQLite 一起使用时，该参数的有效值为 "SERIALIZABLE" 与 "READ UNCOMMITTED"，它们分别对应的 SQLite PRAGMA 值为 0 与 1。SQLite 默认值为 SERIALIZABLE，但其行为也受 pysqlite 驱动器默认行为的影响。

在使用 pysqlite 驱动时，还可用 "AUTOCOMMIT" 隔离级别，它会通过 DBAPI 连接的 .isolation_level 属性将其设置为 None，从而修改 pysqlite 的连接行为，在该设置期间启用自动提交。

SQLite 的事务锁定行为受 BEGIN 语句的形式影响。该语句有三种模式：“deferred”、“immediate” 与 “exclusive”，详见 BEGIN TRANSACTION。简单的 BEGIN 语句使用的是 “deferred” 模式，在此模式下，直到第一次读写操作执行前，数据库文件不会被锁定；读操作在第一次写入前对其他事务仍是开放的。但需要特别注意的是，pysqlite 驱动会干扰此行为，它 甚至不会在第一次写入前发出 BEGIN 。

警告

SQLite 的事务范围受到 pysqlite 驱动中未解决问题的影响，后者将 BEGIN 语句推迟到了不可接受的程度。请参阅可序列化隔离/保存点/事务性 DDL 或可序列化隔离/保存点/事务 DDL（asyncio版本）部分，了解应对该行为的技巧。

参见

设置事务隔离级别（包括 DBAPI 自动提交）

英文

SQLite supports “transaction isolation” in a non-standard way, along two axes. One is that of the PRAGMA read_uncommitted instruction. This setting can essentially switch SQLite between its default mode of SERIALIZABLE isolation, and a “dirty read” isolation mode normally referred to as READ UNCOMMITTED.

SQLAlchemy ties into this PRAGMA statement using the create_engine.isolation_level parameter of create_engine(). Valid values for this parameter when used with SQLite are "SERIALIZABLE" and "READ UNCOMMITTED" corresponding to a value of 0 and 1, respectively. SQLite defaults to SERIALIZABLE, however its behavior is impacted by the pysqlite driver’s default behavior.

When using the pysqlite driver, the "AUTOCOMMIT" isolation level is also available, which will alter the pysqlite connection using the .isolation_level attribute on the DBAPI connection and set it to None for the duration of the setting.

The other axis along which SQLite’s transactional locking is impacted is via the nature of the BEGIN statement used. The three varieties are “deferred”, “immediate”, and “exclusive”, as described at BEGIN TRANSACTION. A straight BEGIN statement uses the “deferred” mode, where the database file is not locked until the first read or write operation, and read access remains open to other transactions until the first write operation. But again, it is critical to note that the pysqlite driver interferes with this behavior by not even emitting BEGIN until the first write operation.

警告

SQLite’s transactional scope is impacted by unresolved issues in the pysqlite driver, which defers BEGIN statements to a greater degree than is often feasible. See the section 可序列化隔离/保存点/事务性 DDL or 可序列化隔离/保存点/事务 DDL（asyncio版本） for techniques to work around this behavior.

参见

设置事务隔离级别（包括 DBAPI 自动提交）

INSERT/UPDATE/DELETE…RETURNING¶

INSERT/UPDATE/DELETE…RETURNING

中文

SQLite 方言支持 SQLite 3.35 引入的 INSERT|UPDATE|DELETE..RETURNING 语法。在某些场景下，可能会自动使用 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 的支持

英文

The SQLite dialect supports SQLite 3.35’s INSERT|UPDATE|DELETE..RETURNING syntax. INSERT..RETURNING may be used automatically in some cases in order to fetch newly generated identifiers in place of the traditional approach of using cursor.lastrowid, however cursor.lastrowid is currently still preferred for simple single-statement cases for its better performance.

To specify an explicit RETURNING clause, use the _UpdateBase.returning() method on a per-statement basis:

# 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 版本加入: Added support for SQLite RETURNING

SAVEPOINT 支持¶

SAVEPOINT Support

中文

SQLite 支持 SAVEPOINT（保存点），但仅在事务已开始后才生效。 SQLAlchemy 通过 Core 层的 Connection.begin_nested() 方法以及 ORM 层的 Session.begin_nested() 方法提供保存点支持。然而，除非使用某些变通方案，否则在使用 pysqlite 驱动时 SAVEPOINT 将无法正常工作。

警告

SQLite 的 SAVEPOINT 功能受 pysqlite 和 aiosqlite 驱动中的未解决问题影响，它们将 BEGIN 语句的触发推迟到了通常无法接受的程度。请参阅可序列化隔离/保存点/事务性 DDL 与可序列化隔离/保存点/事务 DDL（asyncio版本），了解应对技巧。

英文

SQLite supports SAVEPOINTs, which only function once a transaction is begun. SQLAlchemy’s SAVEPOINT support is available using the Connection.begin_nested() method at the Core level, and Session.begin_nested() at the ORM level. However, SAVEPOINTs won’t work at all with pysqlite unless workarounds are taken.

警告

SQLite’s SAVEPOINT feature is impacted by unresolved issues in the pysqlite and aiosqlite drivers, which defer BEGIN statements to a greater degree than is often feasible. See the sections 可序列化隔离/保存点/事务性 DDL and 可序列化隔离/保存点/事务 DDL（asyncio版本） for techniques to work around this behavior.

事务性 DDL¶

Transactional DDL

中文

SQLite 数据库支持事务性的 DDL 操作。在这种情况下，pysqlite 驱动不仅未能自动开启事务，还在检测到 DDL 时结束了任何已有事务，因此也需要特殊处理。

警告

SQLite 的事务性 DDL 操作受到 pysqlite 驱动中未解决问题的影响，它既不发出 BEGIN，也会在遇到 DDL 时强制 COMMIT，取消已有事务。请参阅可序列化隔离/保存点/事务性 DDL，了解应对技巧。

英文

The SQLite database supports transactional DDL as well. In this case, the pysqlite driver is not only failing to start transactions, it also is ending any existing transaction when DDL is detected, so again, workarounds are required.

警告

SQLite’s transactional DDL is impacted by unresolved issues in the pysqlite driver, which fails to emit BEGIN and additionally forces a COMMIT to cancel any transaction when DDL is encountered. See the section 可序列化隔离/保存点/事务性 DDL for techniques to work around this behavior.

外键支持¶

Foreign Key Support

中文

SQLite 支持在表的 CREATE 语句中使用 FOREIGN KEY 语法，但默认情况下，这些约束 对表的行为没有实际影响。

要使 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 官方网站上的相关说明。

事件 - SQLAlchemy 的事件 API。

通过 ALTER 创建/删除外键约束 - 了解 SQLAlchemy 如何处理相互依赖的外键约束的更多信息。

英文

SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, however by default these constraints have no effect on the operation of the table.

Constraint checking on SQLite has three prerequisites:

At least version 3.6.19 of SQLite must be in use
The SQLite library must be compiled without the SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER symbols enabled.
The PRAGMA foreign_keys = ON statement must be emitted on all connections before use – including the initial call to MetaData.create_all().

SQLAlchemy allows for the PRAGMA statement to be emitted automatically for new connections through the usage of events:

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

警告

When SQLite foreign keys are enabled, it is not possible to emit CREATE or DROP statements for tables that contain mutually-dependent foreign key constraints; to emit the DDL for these tables requires that ALTER TABLE be used to create or drop these constraints separately, for which SQLite has no support.

参见

SQLite Foreign Key Support - on the SQLite web site.

事件 - SQLAlchemy event API.

通过 ALTER 创建/删除外键约束 - more information on SQLAlchemy’s facilities for handling: mutually-dependent foreign key constraints.

ON CONFLICT 约束支持¶

ON CONFLICT support for constraints

中文

参见

本节描述了 SQLite 中出现在 CREATE TABLE 语句中的 DDL 版本的 “ON CONFLICT”。关于适用于 INSERT 语句的 “ON CONFLICT”，请参阅 INSERT…ON CONFLICT（更新插入）。

SQLite 支持一种非标准的 DDL 子句 ON CONFLICT，可应用于主键、唯一性、检查以及非空约束。在 DDL 中，该子句可以出现在 “CONSTRAINT” 子句中，或直接出现在列定义内，具体取决于目标约束的位置。要在 DDL 中渲染该子句，可以在 PrimaryKeyConstraint、UniqueConstraint、CheckConstraint 对象中指定扩展参数 sqlite_on_conflict 并传入冲突解决算法的字符串形式。在 Column 对象中，则可以使用 sqlite_on_conflict_not_null、sqlite_on_conflict_primary_key、sqlite_on_conflict_unique 等参数，分别对应三种列级约束类型。

参见

ON CONFLICT - SQLite 官方文档

sqlite_on_conflict 参数接受一个字符串，表示选择的冲突解决算法，其值可以是 SQLite 支持的 ROLLBACK、ABORT、FAIL、IGNORE 或 REPLACE。例如，添加一个指定使用 IGNORE 算法的 UNIQUE 约束:

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

上述代码生成的 CREATE TABLE DDL 如下：

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 参数指定冲突策略，该参数将会添加到生成的 UNIQUE 约束中:

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

生成的 SQL 如下：

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

要为 NOT NULL 约束指定 FAIL 算法，可使用 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"
    ),
)

生成的列定义中会包含 inline 的 ON CONFLICT 子句：

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

类似地，若要为 inline 主键指定冲突算法，可使用 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
)

英文

参见

This section describes the DDL version of “ON CONFLICT” for SQLite, which occurs within a CREATE TABLE statement. For “ON CONFLICT” as applied to an INSERT statement, see INSERT…ON CONFLICT（更新插入）.

SQLite supports a non-standard DDL clause known as ON CONFLICT which can be applied to primary key, unique, check, and not null constraints. In DDL, it is rendered either within the “CONSTRAINT” clause or within the column definition itself depending on the location of the target constraint. To render this clause within DDL, the extension parameter sqlite_on_conflict can be specified with a string conflict resolution algorithm within the PrimaryKeyConstraint, UniqueConstraint, CheckConstraint objects. Within the Column object, there are individual parameters sqlite_on_conflict_not_null, sqlite_on_conflict_primary_key, sqlite_on_conflict_unique which each correspond to the three types of relevant constraint types that can be indicated from a Column object.

参见

ON CONFLICT - in the SQLite documentation

The sqlite_on_conflict parameters accept a string argument which is just the resolution name to be chosen, which on SQLite can be one of ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. For example, to add a UNIQUE constraint that specifies the IGNORE algorithm:

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

The above renders CREATE TABLE DDL as:

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

When using the Column.unique flag to add a UNIQUE constraint to a single column, the sqlite_on_conflict_unique parameter can be added to the Column as well, which will be added to the UNIQUE constraint in the DDL:

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

rendering:

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

To apply the FAIL algorithm for a NOT NULL constraint, sqlite_on_conflict_not_null is used:

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

this renders the column inline ON CONFLICT phrase:

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

Similarly, for an inline primary key, use sqlite_on_conflict_primary_key:

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

SQLAlchemy renders the PRIMARY KEY constraint separately, so the conflict resolution algorithm is applied to the constraint itself:

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

类型反射¶

Type Reflection

中文

SQLite 的类型系统不同于大多数其他数据库后端，其类型字符串名称通常并不一一对应于某种具体的“类型”。相反，SQLite 使用所谓的五种“类型亲和性”（type affinities）机制，通过字符串匹配方式为每一列确定类型行为。

SQLAlchemy 在执行类型反射时，会使用一个简单的查找表将返回的关键字映射为 SQLAlchemy 提供的类型。这个查找表在 SQLite 方言中与其他方言类似。但是，SQLite 方言在找不到特定类型名的匹配项时，使用一种不同的“回退”策略：它实现了 SQLite 在官方文档 https://www.sqlite.org/datatype3.html 第 2.1 节中描述的“类型亲和性”规则。

此查找表会直接将以下类型名与对应的 SQLAlchemy 类型进行一一匹配：

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

当类型名称未匹配上述任何项时，将采用“类型亲和性”规则进行推断：

若类型名包含字符串 INT，则返回 INTEGER
若类型名包含字符串 CHAR、CLOB 或 TEXT，则返回 TEXT
若类型名包含字符串 BLOB，则返回 NullType
若类型名包含字符串 REAL、FLOA 或 DOUB，则返回 REAL
否则，使用 :class:`*

英文

SQLite types are unlike those of most other database backends, in that the string name of the type usually does not correspond to a “type” in a one-to-one fashion. Instead, SQLite links per-column typing behavior to one of five so-called “type affinities” based on a string matching pattern for the type.

SQLAlchemy’s reflection process, when inspecting types, uses a simple lookup table to link the keywords returned to provided SQLAlchemy types. This lookup table is present within the SQLite dialect as it is for all other dialects. However, the SQLite dialect has a different “fallback” routine for when a particular type name is not located in the lookup map; it instead implements the SQLite “type affinity” scheme located at https://www.sqlite.org/datatype3.html section 2.1.

The provided typemap will make direct associations from an exact string name match for the following types:

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

When a type name does not match one of the above types, the “type affinity” lookup is used instead:

INTEGER is returned if the type name includes the string INT
TEXT is returned if the type name includes the string CHAR, CLOB or TEXT
NullType is returned if the type name includes the string BLOB
REAL is returned if the type name includes the string REAL, FLOA or DOUB.
Otherwise, the NUMERIC type is used.

部分索引¶

Partial Indexes

中文

可以通过 DDL 系统中的 sqlite_where 参数来定义一个 部分索引 （例如带有 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

英文

A partial index, e.g. one which uses a WHERE clause, can be specified with the DDL system using the argument 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),
)

The index will be rendered at create time as:

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

点分列名¶

Dotted Column Names

中文

不推荐 使用显式包含句点（.）的表名或列名。尽管在关系型数据库中这是一个通用的糟糕做法，因为点号是一个具有语法意义的字符，但在 SQLite 驱动中，直到 3.10.0 版本之前存在一个 bug，使得 SQLAlchemy 必须在结果集中过滤掉这些点号。

这个完全由 SQLite 引擎外部引起的 bug 可以如下重现:

import sqlite3

assert sqlite3.sqlite_version_info < (
    3,
    10,
    0,
), "bug 在该版本中已修复"

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 依赖于列名在与原始语句匹配时的可预测性，因此 SQLite 方言别无选择，只能过滤掉这些点号:

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 所采取的回避策略仅影响公共 API 中的 CursorResult.keys() 与 Row.keys()。在某些特定场景中，若应用确实必须使用包含点号的列名，并且需要通过 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}
)

使用引擎级别的执行选项时，请注意： 基于 Core 或 ORM 的 UNION 查询可能无法正常工作。

英文

Using table or column names that explicitly have periods in them is not recommended. While this is generally a bad idea for relational databases in general, as the dot is a syntactically significant character, the SQLite driver up until version 3.10.0 of SQLite has a bug which requires that SQLAlchemy filter out these dots in result sets.

The bug, entirely outside of SQLAlchemy, can be illustrated thusly:

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
]

The second assertion fails:

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

Where above, the driver incorrectly reports the names of the columns including the name of the table, which is entirely inconsistent vs. when the UNION is not present.

SQLAlchemy relies upon column names being predictable in how they match to the original statement, so the SQLAlchemy dialect has no choice but to filter these out:

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

Note that above, even though SQLAlchemy filters out the dots, both names are still addressable:

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

Therefore, the workaround applied by SQLAlchemy only impacts CursorResult.keys() and Row.keys() in the public API. In the very specific case where an application is forced to use column names that contain dots, and the functionality of CursorResult.keys() and Row.keys() is required to return these dotted names unmodified, the sqlite_raw_colnames execution option may be provided, either on a per-Connection basis:

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

or on a per-Engine basis:

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

When using the per-Engine execution option, note that Core and ORM queries that use UNION may not function properly.

SQLite 特定表选项¶

SQLite-specific table options

中文

SQLite 方言支持与 Table 构造配合使用的 CREATE TABLE 选项如下：

WITHOUT ROWID:

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

STRICT:

Table("some_table", metadata, ..., sqlite_strict=True)

在 2.0.37 版本加入.

参见

SQLite CREATE TABLE options

英文

One option for CREATE TABLE is supported directly by the SQLite dialect in conjunction with the Table construct:

WITHOUT ROWID:

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

STRICT:

Table("some_table", metadata, ..., sqlite_strict=True)

在 2.0.37 版本加入.

参见

SQLite CREATE TABLE options

反射内部模式表¶

Reflecting internal schema tables

中文

返回表名列表的反射方法会排除所谓的 “SQLite 内部模式对象（internal schema object）”，SQLite 认为这些是名称以 sqlite_ 为前缀的对象。例如使用 AUTOINCREMENT 列参数时自动生成的 sqlite_sequence 表。若希望返回这些对象，可以向 MetaData.reflect() 或 Inspector.get_table_names() 等方法传递参数 sqlite_include_internal=True。

在 2.0 版本加入: 增加了 sqlite_include_internal=True 参数。此前，SQLAlchemy 反射方法并不会忽略这些表。

备注

参数 sqlite_include_internal 并不作用于存在于 sqlite_master 等模式中的 “系统” 表。

参见

SQLite Internal Schema Objects - SQLite 官方文档中的介绍。

英文

Reflection methods that return lists of tables will omit so-called “SQLite internal schema object” names, which are considered by SQLite as any object name that is prefixed with sqlite_. An example of such an object is the sqlite_sequence table that’s generated when the AUTOINCREMENT column parameter is used. In order to return these objects, the parameter sqlite_include_internal=True may be passed to methods such as MetaData.reflect() or Inspector.get_table_names().

在 2.0 版本加入: Added the sqlite_include_internal=True parameter. Previously, these tables were not ignored by SQLAlchemy reflection methods.

备注

The sqlite_include_internal parameter does not refer to the “system” tables that are present in schemas such as sqlite_master.

参见

SQLite Internal Schema Objects - in the SQLite documentation.

SQLite 数据类型¶

SQLite Data Types

中文

与所有SQLAlchemy方言一样，所有已知对SQLite有效的UPPERCASE类型都可以从顶级方言中导入，无论它们是来自 sqlalchemy.types 还是来自本地方言:

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

英文

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with SQLite are importable from the top level dialect, whether they originate from sqlalchemy.types or from the local dialect:

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

Object Name	Description
DATE	Represent a Python date object in SQLite using a string.
DATETIME	Represent a Python datetime object in SQLite using a string.
JSON	SQLite JSON type.
TIME	Represent a Python time object in SQLite using a string.

class sqlalchemy.dialects.sqlite.DATETIME¶

Represent a Python datetime object in SQLite using a string.

The default string storage format is:

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

e.g.:

2021-03-15 12:05:57.105542

The incoming storage format is by default parsed using the Python datetime.fromisoformat() function.

在 2.0 版本发生变更: datetime.fromisoformat() is used for default datetime string parsing.

The storage format can be customized to some degree using the storage_format and regexp parameters, such as:

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+)",
)

参数:

truncate_microseconds¶ – when True microseconds will be truncated from the datetime. Can’t be specified together with storage_format or regexp.
storage_format¶ – format string which will be applied to the dict with keys year, month, day, hour, minute, second, and microsecond.
regexp¶ – regular expression which will be applied to incoming result rows, replacing the use of datetime.fromisoformat() to parse incoming strings. If the regexp contains named groups, the resulting match dict is applied to the Python datetime() constructor as keyword arguments. Otherwise, if positional groups are used, the datetime() constructor is called with positional arguments via *map(int, match_obj.groups(0)).

Class signature

class sqlalchemy.dialects.sqlite.DATETIME (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.DateTime)

class sqlalchemy.dialects.sqlite.DATE¶

Represent a Python date object in SQLite using a string.

The default string storage format is:

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

e.g.:

2011-03-15

The incoming storage format is by default parsed using the Python date.fromisoformat() function.

在 2.0 版本发生变更: date.fromisoformat() is used for default date string parsing.

The storage format can be customized to some degree using the storage_format and regexp parameters, such as:

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¶ – format string which will be applied to the dict with keys year, month, and day.
regexp¶ – regular expression which will be applied to incoming result rows, replacing the use of date.fromisoformat() to parse incoming strings. If the regexp contains named groups, the resulting match dict is applied to the Python date() constructor as keyword arguments. Otherwise, if positional groups are used, the date() constructor is called with positional arguments via *map(int, match_obj.groups(0)).

Class signature

class sqlalchemy.dialects.sqlite.DATE (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.Date)

class sqlalchemy.dialects.sqlite.JSON¶

SQLite JSON type.

SQLite supports JSON as of version 3.9 through its JSON1 extension. Note that JSON1 is a loadable extension and as such may not be available, or may require run-time loading.

JSON is used automatically whenever the base JSON datatype is used against a SQLite backend.

参见

JSON - main documentation for the generic cross-platform JSON datatype.

The JSON type supports persistence of JSON values as well as the core index operations provided by JSON datatype, by adapting the operations to render the JSON_EXTRACT function wrapped in the JSON_QUOTE function at the database level. Extracted values are quoted in order to ensure that the results are always JSON string values.

Members

__init__()

Class signature

class sqlalchemy.dialects.sqlite.JSON (sqlalchemy.types.JSON)

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

inherited from the sqlalchemy.types.JSON.__init__ method of JSON

Construct a JSON type.

参数:

none_as_null=False¶ –

if True, persist the value None as a SQL NULL value, not the JSON encoding of null. Note that when this flag is False, the null() construct can still be used to persist a NULL value, which may be passed directly as a parameter value that is specially interpreted by the JSON type as SQL NULL:

from sqlalchemy import null

conn.execute(table.insert(), {"data": null()})

备注

JSON.none_as_null does not apply to the values passed to Column.default and Column.server_default; a value of None passed for these parameters means “no default present”.

Additionally, when used in SQL comparison expressions, the Python value None continues to refer to SQL null, and not JSON NULL. The JSON.none_as_null flag refers explicitly to the persistence of the value within an INSERT or UPDATE statement. The JSON.NULL value should be used for SQL expressions that wish to compare to JSON null.

参见

JSON.NULL

class sqlalchemy.dialects.sqlite.TIME¶

Represent a Python time object in SQLite using a string.

The default string storage format is:

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

e.g.:

12:05:57.10558

The incoming storage format is by default parsed using the Python time.fromisoformat() function.

在 2.0 版本发生变更: time.fromisoformat() is used for default time string parsing.

The storage format can be customized to some degree using the storage_format and regexp parameters, such as:

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+))?"),
)

参数:

truncate_microseconds¶ – when True microseconds will be truncated from the time. Can’t be specified together with storage_format or regexp.
storage_format¶ – format string which will be applied to the dict with keys hour, minute, second, and microsecond.
regexp¶ – regular expression which will be applied to incoming result rows, replacing the use of datetime.fromisoformat() to parse incoming strings. If the regexp contains named groups, the resulting match dict is applied to the Python time() constructor as keyword arguments. Otherwise, if positional groups are used, the time() constructor is called with positional arguments via *map(int, match_obj.groups(0)).

Class signature

class sqlalchemy.dialects.sqlite.TIME (sqlalchemy.dialects.sqlite.base._DateTimeMixin, sqlalchemy.types.Time)

SQLite DML 构造¶

SQLite DML Constructs

Object Name	Description
insert(table)	Construct a sqlite-specific variant `Insert` construct.
Insert	SQLite-specific implementation of INSERT.

function sqlalchemy.dialects.sqlite.insert(table: _DMLTableArgument) → Insert¶

Construct a sqlite-specific variant Insert construct.

The sqlalchemy.dialects.sqlite.insert() function creates a sqlalchemy.dialects.sqlite.Insert. This class is based on the dialect-agnostic Insert construct which may be constructed using the insert() function in SQLAlchemy Core.

The Insert construct includes additional methods Insert.on_conflict_do_update(), Insert.on_conflict_do_nothing().

class sqlalchemy.dialects.sqlite.Insert¶

SQLite-specific implementation of INSERT.

Adds methods for SQLite-specific syntaxes such as ON CONFLICT.

The Insert object is created using the sqlalchemy.dialects.sqlite.insert() function.

在 1.4 版本加入.

参见

INSERT…ON CONFLICT（更新插入）

Members

excluded, inherit_cache, on_conflict_do_nothing(), on_conflict_do_update()

Class signature

class sqlalchemy.dialects.sqlite.Insert (sqlalchemy.sql.expression.Insert)

attribute sqlalchemy.dialects.sqlite.Insert.excluded¶

Provide the excluded namespace for an ON CONFLICT statement

SQLite’s ON CONFLICT clause allows reference to the row that would be inserted, known as excluded. This attribute provides all columns in this row to be referenceable.

小技巧

The Insert.excluded attribute is an instance of ColumnCollection, which provides an interface the same as that of the Table.c collection described at 访问表和列. With this collection, ordinary names are accessible like attributes (e.g. stmt.excluded.some_column), but special names and dictionary method names should be accessed using indexed access, such as stmt.excluded["column name"] or stmt.excluded["values"]. See the docstring for ColumnCollection for further examples.

attribute sqlalchemy.dialects.sqlite.Insert.inherit_cache: bool | None = True¶

Indicate if this HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

参见

为自定义构造启用缓存支持 - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

method sqlalchemy.dialects.sqlite.Insert.on_conflict_do_nothing(index_elements: Iterable[Column[Any] | str | DDLConstraintColumnRole] | None = None, index_where: WhereHavingRole | None = None) → Self¶

Specifies a DO NOTHING action for ON CONFLICT clause.

参数:

index_elements¶ – A sequence consisting of string column names, Column objects, or other column expression objects that will be used to infer a target index or unique constraint.
index_where¶ – Additional WHERE criterion that can be used to infer a conditional target index.

Specifies a DO UPDATE SET action for ON CONFLICT clause.

参数:

index_elements¶ – A sequence consisting of string column names, Column objects, or other column expression objects that will be used to infer a target index or unique constraint.
index_where¶ – Additional WHERE criterion that can be used to infer a conditional target index.
set_¶ –
A dictionary or other mapping object where the keys are either names of columns in the target table, or Column objects or other ORM-mapped columns matching that of the target table, and expressions or literals as values, specifying the SET actions to take.

在 1.4 版本加入: The Insert.on_conflict_do_update.set_ parameter supports Column objects from the target Table as keys.

警告

This dictionary does not take into account Python-specified default UPDATE values or generation functions, e.g. those specified using Column.onupdate. These values will not be exercised for an ON CONFLICT style of UPDATE, unless they are manually specified in the Insert.on_conflict_do_update.set_ dictionary.
where¶ – Optional argument. An expression object representing a WHERE clause that restricts the rows affected by DO UPDATE SET. Rows not meeting the WHERE condition will not be updated (effectively a DO NOTHING for those rows).

Aiosqlite¶

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 方言为运行在 pysqlite 之上的 SQLAlchemy asyncio 接口提供支持。

aiosqlite 是 pysqlite 的一个封装器，它为每个连接使用一个后台线程。由于 SQLite 数据库并不是基于套接字的，它并不真正使用非阻塞 IO。然而，它确实提供了一个可用的 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 相同。

英文

The aiosqlite dialect provides support for the SQLAlchemy asyncio interface running on top of pysqlite.

aiosqlite is a wrapper around pysqlite that uses a background thread for each connection. It does not actually use non-blocking IO, as SQLite databases are not socket-based. However it does provide a working asyncio interface that’s useful for testing and prototyping purposes.

Using a special asyncio mediation layer, the aiosqlite dialect is usable as the backend for the SQLAlchemy asyncio extension package.

This dialect should normally be used only with the create_async_engine() engine creation function:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("sqlite+aiosqlite:///filename")

The URL passes through all arguments to the pysqlite driver, so all connection arguments are the same as they are for that of Pysqlite.

用户定义的函数¶

User-Defined Functions

中文

aiosqlite 扩展了 pysqlite，以支持 async，因此我们可以在 Python 中创建自定义函数（UDF），并像在 SQLite 查询中使用它们一样使用，详见用户定义函数。

英文

aiosqlite extends pysqlite to support async, so we can create our own user-defined functions (UDFs) in Python and use them directly in SQLite queries as described here: 用户定义函数.

可序列化隔离/保存点/事务 DDL（asyncio版本）¶

Serializable isolation / Savepoints / Transactional DDL (asyncio version)

中文

与 pysqlite 类似，aiosqlite 也不支持 SAVEPOINT 功能。

解决方案与可序列化隔离/保存点/事务性 DDL 类似，可通过异步事件监听器实现:

from sqlalchemy import create_engine, event
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("sqlite+aiosqlite:///myfile.db")


@event.listens_for(engine.sync_engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # 完全禁用 aiosqlite 对 BEGIN 语句的自动发出行为。
    # 同时也防止其在任何 DDL 前发出 COMMIT。
    dbapi_connection.isolation_level = None


@event.listens_for(engine.sync_engine, "begin")
def do_begin(conn):
    # 手动发出 BEGIN
    conn.exec_driver_sql("BEGIN")

警告

使用上述方案时，不建议在 SQLite 驱动中使用 Connection 与 create_engine() 的 Connection.execution_options.isolation_level 设置，因为该函数也会修改 .isolation_level 设置，可能导致行为不一致。

英文

Similarly to pysqlite, aiosqlite does not support SAVEPOINT feature.

The solution is similar to 可序列化隔离/保存点/事务性 DDL. This is achieved by the event listeners in async:

from sqlalchemy import create_engine, event
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("sqlite+aiosqlite:///myfile.db")


@event.listens_for(engine.sync_engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable aiosqlite'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.sync_engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.exec_driver_sql("BEGIN")

警告

When using the above recipe, it is advised to not use the Connection.execution_options.isolation_level setting on Connection and create_engine() with the SQLite driver, as this function necessarily will also alter the “.isolation_level” setting.

连接池行为¶

Pooling Behavior

中文

SQLAlchemy 的 aiosqlite DBAPI 根据请求的 SQLite 数据库类型，以不同的方式建立连接池：

当指定为 :memory: SQLite 数据库时，方言默认使用 StaticPool。此连接池维护一个单一连接，使得所有访问该 engine 的操作都使用相同的 :memory: 数据库。
当指定为基于文件的数据库时，方言将使用 AsyncAdaptedQueuePool 作为连接来源。

在 2.0.38 版本发生变更: SQLite 文件数据库引擎现在默认使用 AsyncAdaptedQueuePool。之前默认使用的是 NullPool。若需使用 NullPool，可通过 create_engine.poolclass 参数指定。

英文

The SQLAlchemy aiosqlite DBAPI establishes the connection pool differently based on the kind of SQLite database that’s requested:

When a :memory: SQLite database is specified, the dialect by default will use StaticPool. This pool maintains a single connection, so that all access to the engine use the same :memory: database.
When a file-based database is specified, the dialect will use AsyncAdaptedQueuePool as the source of connections.

在 2.0.38 版本发生变更: SQLite file database engines now use AsyncAdaptedQueuePool by default. Previously, NullPool were used. The NullPool class may be used by specifying it via the create_engine.poolclass parameter.

Pysqlcipher¶

Support for the SQLite database via the pysqlcipher driver.

Dialect for support of DBAPIs that make use of the SQLCipher backend.

Connecting¶

Connect String:

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

驱动¶

Driver

中文

当前方言的选择逻辑如下：

如果通过 create_engine.module 参数提供了一个 DBAPI 模块，则使用该模块。
否则，在 Python 3 中优先选择 https://pypi.org/project/sqlcipher3/
若该模块不可用，则回退到 https://pypi.org/project/pysqlcipher3/
对于 Python 2，则使用 https://pypi.org/project/pysqlcipher/

警告

pysqlcipher3 和 pysqlcipher DBAPI 驱动已经不再维护；截至目前，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 命令，允许设置密码短语及其他加密参数，从而实现数据库文件的加密。

英文

Current dialect selection logic is:

If the create_engine.module parameter supplies a DBAPI module, that module is used.
Otherwise for Python 3, choose https://pypi.org/project/sqlcipher3/
If not available, fall back to https://pypi.org/project/pysqlcipher3/
For Python 2, https://pypi.org/project/pysqlcipher/ is used.

警告

The pysqlcipher3 and pysqlcipher DBAPI drivers are no longer maintained; the sqlcipher3 driver as of this writing appears to be current. For future compatibility, any pysqlcipher-compatible DBAPI may be used as follows:

import sqlcipher_compatible_driver

from sqlalchemy import create_engine

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

These drivers make use of the SQLCipher engine. This system essentially introduces new PRAGMA commands to SQLite which allows the setting of a passphrase and other encryption parameters, allowing the database file to be encrypted.

连接字符串¶

Connect Strings

中文

连接字符串的格式与 pysqlite 驱动完全一致，唯一区别是现在接受一个 “password” 字段，用于包含加密所需的密码短语:

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

若需指定绝对路径，数据库名应以两个斜杠开头:

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

SQLCipher 所支持的一系列附加加密相关的 PRAGMA（详见 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 中传入的加密相关 PRAGMA 参数，它们会被悄然忽略。这可能导致无法打开由旧版 SQLAlchemy 创建的数据库文件，如果加密选项不匹配则可能报错。

英文

The format of the connect string is in every way the same as that of the pysqlite driver, except that the “password” field is now accepted, which should contain a passphrase:

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

For an absolute file path, two leading slashes should be used for the database name:

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

A selection of additional encryption-related pragmas supported by SQLCipher as documented at https://www.zetetic.net/sqlcipher/sqlcipher-api/ can be passed in the query string, and will result in that PRAGMA being called for each new connection. Currently, cipher, kdf_iter cipher_page_size and cipher_use_hmac are supported:

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

警告

Previous versions of sqlalchemy did not take into consideration the encryption-related pragmas passed in the url string, that were silently ignored. This may cause errors when opening files saved by a previous sqlalchemy version if the encryption options do not match.

连接池行为¶

Pooling Behavior

中文

该驱动会修改 pysqlite 默认的连接池行为，如线程/池行为所述。观察发现 pysqlcipher 驱动的连接性能明显低于 pysqlite，很可能是由于加密开销的缘故，因此此方言默认使用 SingletonThreadPool 而非 pysqlite 默认的 NullPool。如同以往，连接池的实现方式完全可以通过 create_engine.poolclass 参数进行配置；对于单线程使用场景， StaticPool 可能更合适；若希望防止长时间保持未加密的连接，可以选择 NullPool，但代价是每次新建连接的启动时间更长。

英文

The driver makes a change to the default pool behavior of pysqlite as described in 线程/池行为. The pysqlcipher driver has been observed to be significantly slower on connection than the pysqlite driver, most likely due to the encryption overhead, so the dialect here defaults to using the SingletonThreadPool implementation, instead of the NullPool pool used by pysqlite. As always, the pool implementation is entirely configurable using the create_engine.poolclass parameter; the StaticPool may be more feasible for single-threaded use, or NullPool may be used to prevent unencrypted connections from being held open for long periods of time, at the expense of slower startup time for new connections.

SQLite¶

DBAPI Support¶

日期和时间类型¶

确保文本关联性¶

SQLite 自动递增行为¶

使用 AUTOINCREMENT 关键字¶

允许 SQLAlchemy 中除 Integer/INTEGER 之外的其他类型的自动递增行为¶

数据库锁定行为/并发性¶

事务隔离级别/自动提交¶

INSERT/UPDATE/DELETE…RETURNING¶

SAVEPOINT 支持¶

事务性 DDL¶

外键支持¶

ON CONFLICT 约束支持¶

INSERT…ON CONFLICT（更新插入）¶

指定目标¶

SET 子句¶

使用排除的 INSERT 值进行更新¶

其他 WHERE 条件¶

使用 DO NOTHING 跳过行¶

类型反射¶

部分索引¶

点分列名¶

SQLite 特定表选项¶

反射内部模式表¶

SQLite 数据类型¶

SQLite DML 构造¶

Pysqlite¶

DBAPI¶

Connecting¶

驱动¶

连接字符串¶

URI 连接¶

正则表达式支持¶

与 SQLite3 原生日期和日期时间类型的兼容性¶

线程/池行为¶

禁用文件数据库的连接池¶

在多线程中使用内存数据库¶

在 SQLite 中使用临时表¶

处理混合字符串/二进制列¶

可序列化隔离/保存点/事务性 DDL¶

用户定义函数¶

Aiosqlite¶

DBAPI¶

Connecting¶

用户定义的函数¶

可序列化隔离/保存点/事务 DDL（asyncio版本）¶

连接池行为¶

Pysqlcipher¶

Connecting¶

驱动¶

连接字符串¶

连接池行为¶