SqlAlchemy 2.0 中文文档（五十）（1）-阿里云开发者社区

原文：docs.sqlalchemy.org/en/20/contents.html

SQLite

原文：docs.sqlalchemy.org/en/20/dialects/sqlite.html

对 SQLite 数据库的支持。

以下表格总结了数据库发布版本的当前支持水平。

支持的 SQLite 版本

支持类型	版本
CI 中完全测试过	3.36.0
普通支持	3.12+
尽力而为	3.7.16+

DBAPI 支持

可用以下方言/DBAPI 选项。有关连接信息，请参阅各个 DBAPI 部分。

pysqlite
aiosqlite
pysqlcipher

日期和时间类型

SQLite 没有内置的 DATE、TIME 或 DATETIME 类型，而 pysqlite 也没有提供将值在 Python datetime 对象和 SQLite 支持的格式之间转换的开箱即用功能。当使用 SQLite 时，SQLAlchemy 自己的 DateTime 和相关类型提供日期格式化和解析功能。实现类是 DATETIME、DATE 和 TIME。这些类型将日期和时间表示为 ISO 格式的字符串，也很好地支持排序。对于这些函数，不依赖于典型的“libc”内部，因此完全支持历史日期。

确保文本亲和性

这些类型的 DDL 渲染是标准的 DATE、TIME 和 DATETIME 指示符。然而，这些类型也可以应用自定义存储格式。当检测到存储格式不包含字母字符时，这些类型的 DDL 被渲染为 DATE_CHAR、TIME_CHAR 和 DATETIME_CHAR，以便列继续具有文本亲和性。

另请参阅

类型亲和性 - SQLite 文档中的说明 ## SQLite 自增行为

SQLite 的自动增量背景资料位于：sqlite.org/autoinc.html

关键概念：

SQLite 对于任何非复合主键列都有一个隐式的“自动增量”功能，只要使用“INTEGER PRIMARY KEY”类型 + 主键明确创建该列即可。
SQLite 还有一个显式的“AUTOINCREMENT”关键字，它与隐式自增功能不等同；不推荐一般使用此关键字。除非使用了特殊的 SQLite 特定指令（见下文），否则 SQLAlchemy 不会渲染此关键字。但仍然要求列的类型命名为“INTEGER”。

使用 AUTOINCREMENT 关键字

要在渲染 DDL 时特别呈现主键列上的 AUTOINCREMENT 关键字，将标志sqlite_autoincrement=True添加到 Table 构造中：

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 中，当发出初始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 编译时覆盖其 DDL 名称为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 不适用于高并发写入。数据库本身作为文件，在事务中的写操作期间完全被锁定，这意味着在此期间仅有一个“连接”（实际上是一个文件句柄）对数据库具有独占访问权限 - 在此期间所有其他“连接”将被阻塞。

Python DBAPI 规范还要求连接模型始终处于事务中；没有connection.begin()方法，只有connection.commit()和connection.rollback()，在其上立即开始新事务。这似乎意味着 SQLite 驱动理论上只允许在任何时候对特定数据库文件进行单个文件句柄的操作；然而，SQLite 本身以及 pysqlite 驱动中有几个因素显著放宽了这一限制。

但是，无论使用何种锁定模式，一旦启动事务并且至少发出了 DML（例如 INSERT、UPDATE、DELETE），SQLite 将始终锁定数据库文件，并且这将至少在其他事务试图发出 DML 时阻止其他事务。默认情况下，此阻塞的时间非常短，然后会超时并显示错误。

当与 SQLAlchemy ORM 结合使用时，此行为变得更加关键。SQLAlchemy 的 Session 对象默认在事务中运行，并且使用其自动刷新模式，可能会在任何 SELECT 语句之前发出 DML。这可能会导致 SQLite 数据库比预期更快地锁定。可以在某种程度上操纵 SQLite 和 pysqlite 驱动程序的锁定模式，但应注意，要在 SQLite 中实现高度的写并发是一场失败的战斗。

有关 SQLite 按设计缺乏写并发的更多信息，请参阅页面底部的在其他关系数据库管理系统可能更适合的情况下 - 高并发。

以下各小节介绍了受 SQLite 文件型架构影响的区域，并在使用 pysqlite 驱动程序时通常需要解决方法才能正常工作。## 事务隔离级别 / 自动提交

SQLite 以非标准方式支持“事务隔离”，沿着两个轴。一个是 PRAGMA read_uncommitted 指令。此设置可以在 SQLite 的默认模式 SERIALIZABLE 隔离和通常称为 READ UNCOMMITTED 的 “脏读” 隔离模式之间切换。

SQLAlchemy 使用 create_engine.isolation_level 参数的 PRAGMA 语句绑定到此。当与 SQLite 结合使用时，此参数的有效值是 "SERIALIZABLE" 和 "READ UNCOMMITTED"，分别对应值 0 和 1。SQLite 默认为 SERIALIZABLE，但其行为受 pysqlite 驱动程序的默认行为影响。

当使用 pysqlite 驱动程序时，还可以使用 "AUTOCOMMIT" 隔离级别，这将通过 DBAPI 连接上的 .isolation_level 属性来更改 pysqlite 连接，并在设置的持续时间内将其设置为 None。

新版本 1.3.16 中：在使用 pysqlite / sqlite3 SQLite 驱动程序时添加了对 SQLite AUTOCOMMIT 隔离级别的支持。

影响 SQLite 事务性锁定的另一个轴是使用的 BEGIN 语句的性质。三种变体是“deferred”、“immediate” 和 “exclusive”，如 BEGIN TRANSACTION 中所述。直接的 BEGIN 语句使用“deferred”模式，在第一次读取或写入操作之前不会锁定数据库文件，并且在第一次写入操作之前会保持对其他事务的读取访问打开。但是，关键要注意的是 pysqlite 驱动程序通过甚至不发出 BEGIN来干扰此行为。

警告

SQLite 的事务范围受到 pysqlite 驱动程序中未解决的问题的影响，该驱动程序将 BEGIN 语句推迟到比通常更大的程度。请参阅 Serializable isolation / Savepoints / Transactional DDL 或 Serializable isolation / Savepoints / Transactional DDL (asyncio version) 部分，了解解决此行为的技术。

另请参见

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

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 的支持

SAVEPOINT 支持

SQLite 支持 SAVEPOINT，仅在事务开始后才起作用。SQLAlchemy 的 SAVEPOINT 支持可使用 Core 级别的 Connection.begin_nested() 方法和 ORM 级别的 Session.begin_nested() 方法。但是，除非采取解决方法，否则在 pysqlite 中根本无法使用 SAVEPOINT。

警告

SQLite 的 SAVEPOINT 功能受到 pysqlite 和 aiosqlite 驱动程序中未解决的问题的影响，这些驱动程序将 BEGIN 语句推迟到比通常更大的程度。请参阅 Serializable isolation / Savepoints / Transactional DDL 和 Serializable isolation / Savepoints / Transactional DDL (asyncio version) 部分，了解解决此行为的技术。

事务性 DDL

SQLite 数据库也支持事务性 DDL。在这种情况下，pysqlite 驱动程序不仅未能启动事务，还在检测到 DDL 时结束了任何现有事务，因此需要解决方法。

警告

SQLite 的事务 DDL 受到 pysqlite 驱动程序中未解决的问题的影响，该驱动程序在遇到 DDL 时未发出 BEGIN 并且还强制执行 COMMIT 以取消任何事务。请参阅 Serializable isolation / Savepoints / Transactional DDL 部分以了解解决此行为的技巧。

外键支持

SQLite 在发出 CREATE 语句创建表时支持 FOREIGN KEY 语法，但默认情况下这些约束对表的操作没有任何影响。

在 SQLite 上进行约束检查有三个前提条件：

必须使用至少版本 3.6.19 的 SQLite。
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 外键支持 - SQLite 网站上的链接。

Events - SQLAlchemy 事件 API。

通过 ALTER 创建/删除外键约束 - 关于 SQLAlchemy 处理的更多信息

相互依赖的外键约束。## 用于约束的 ON CONFLICT 支持

另请参阅

本节描述了 SQLite 中在 CREATE TABLE 语句内部发生的 “ON CONFLICT” 的 DDL 版本。有关作用于 INSERT 语句的 “ON CONFLICT”，请参阅 INSERT…ON CONFLICT (Upsert)。

SQLite 支持一个名为 ON CONFLICT 的非标准 DDL 子句，可应用于主键、唯一、检查和非空约束。在 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 中的一个。例如，要添加指定 IGNORE 算法的唯一约束：

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标志将唯一约束添加到单个列时，也可以将sqlite_on_conflict_unique参数添加到Column中，该参数将添加到 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 算法以满足非空约束，使用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 短语：

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 单独呈现主键约束，因此冲突解析算法应用于约束本身：

CREATE TABLE some_table (
    id INTEGER NOT NULL,
    PRIMARY KEY (id) ON CONFLICT FAIL
)
```## INSERT…ON CONFLICT（Upsert）
另请参见
本节描述了 SQLite 中“ON CONFLICT”的 DML 版本，它出现在 INSERT 语句中。有关应用于 CREATE TABLE 语句的“ON CONFLICT”，请参见 ON CONFLICT 支持约束。
从版本 3.24.0 开始，SQLite 支持通过 `INSERT` 语句的 `ON CONFLICT` 子句将行“upsert”（更新或插入）到表中。只有候选行不违反任何唯一约束或主键约束时，才会插入候选行。在唯一约束违反的情况下，可以发生二次操作，可以是“DO UPDATE”，表示目标行中的数据应该更新，也可以是“DO NOTHING”，表示要默默跳过此行。
冲突是使用现有唯一约束和索引的列确定的。这些约束通过说明组成索引的列和条件来识别。
SQLAlchemy 通过 SQLite 特定的 `insert()` 函数提供了 `ON CONFLICT` 支持，该函数提供了生成方法 `Insert.on_conflict_do_update()` 和 `Insert.on_conflict_do_nothing()`：
```py
>>> 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)
INSERT  INTO  my_table  (id,  data)  VALUES  (?,  ?)
ON  CONFLICT  (id)  DO  UPDATE  SET  data  =  ?
>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
...     index_elements=['id']
... )
>>> print(do_nothing_stmt)
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)
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_ 参数指定。该参数接受一个字典，其中包含更新的直接值：

>>> 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)
INSERT  INTO  my_table  (id,  data)  VALUES  (?,  ?)
ON  CONFLICT  (id)  DO  UPDATE  SET  data  =  ?

警告

Insert.on_conflict_do_update() 方法不会考虑 Python 端的默认 UPDATE 值或生成函数，例如使用 Column.onupdate 指定的值。这些值不会在 ON CONFLICT 类型的 UPDATE 中执行，除非它们在 Insert.on_conflict_do_update.set_ 字典中手动指定。

使用排除的 INSERT 值进行更新

要引用提议的插入行，Insert.excluded 这个特殊别名可作为 Insert 对象的属性使用；这个对象在列上创建一个“excluded.” 前缀，通知 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)
INSERT  INTO  my_table  (id,  data,  author)  VALUES  (?,  ?,  ?)
ON  CONFLICT  (id)  DO  UPDATE  SET  data  =  ?,  author  =  excluded.author

SqlAlchemy 2.0 中文文档（五十）（2）https://developer.aliyun.com/article/1563128

SqlAlchemy 2.0 中文文档（五十）（1）

SQLite

DBAPI 支持

日期和时间类型

确保文本亲和性

使用 AUTOINCREMENT 关键字

允许自动增量行为的 SQLAlchemy 类型不仅限于 Integer/INTEGER

INSERT/UPDATE/DELETE…RETURNING

SAVEPOINT 支持

事务性 DDL

外键支持

指定目标

SET 子句

使用排除的 INSERT 值进行更新

热门文章

最新文章

相关电子书