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

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

SQLite DML Constructs

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

构造一个特定于 SQLite 的变体 Insert 构造。

sqlalchemy.dialects.sqlite.insert() 函数创建 sqlalchemy.dialects.sqlite.Insert。该类基于方言无关的 Insert 结构，可以使用 SQLAlchemy Core 中的 insert() 函数构造。

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 版中新增。

另请参阅

INSERT…ON CONFLICT（插入或替换）

成员

excluded、inherit_cache、on_conflict_do_nothing()、on_conflict_do_update()

类签名

class sqlalchemy.dialects.sqlite.Insert（sqlalchemy.sql.expression.Insert）

attribute excluded

为 ON CONFLICT 语句提供 excluded 命名空间。

SQLite 的 ON CONFLICT 子句允许引用将要插入的行，称为 excluded。此属性提供了对此行中的所有列的引用。

提示

Insert.excluded 属性是 ColumnCollection 的一个实例，它提供与访问表和列描述的 Table.c 集合相同的接口。通过这个集合，普通名称可以像属性一样访问（例如 stmt.excluded.some_column），但特殊名称和字典方法名称应使用索引访问，例如 stmt.excluded["column name"] 或 stmt.excluded["values"]。有关更多示例，请参阅 ColumnCollection 的文档字符串。

attribute inherit_cache: bool | None = False

指示此 HasCacheKey 实例是否应使用其直接超类使用的缓存密钥生成方案。

此属性默认为 None，表示构造尚未考虑是否适合参与缓存；这在功能上相当于将值设置为 False，但还会发出警告。

如果与此类本地属性（而不是其超类）无关，则可以在特定类上设置此标志为 True，则与对象对应的 SQL 不会根据这个类的属性而改变。

另请参阅

为自定义结构启用缓存支持 - 设置HasCacheKey.inherit_cache 属性的通用指南，用于第三方或用户定义的 SQL 结构。

method on_conflict_do_nothing(index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None) → Self

指定了 ON CONFLICT 子句的 DO NOTHING 操作。

参数：

• index_elements – 由字符串列名、Column 对象或其他列表达式对象组成的序列，将用于推断目标索引或唯一约束。
  
• index_where – 用于推断条件目标索引的额外 WHERE 条件。
  

method on_conflict_do_update(index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None, set_: _OnConflictSetT = None, where: _OnConflictWhereT = None) → Self

指定了 ON CONFLICT 子句的 DO UPDATE SET 操作。

参数：

• index_elements – 由字符串列名、Column 对象或其他列表达式对象组成的序列，将用于推断目标索引或唯一约束。
  
• index_where – 用于推断条件目标索引的额外 WHERE 条件。
  
• set_ –
  一个字典或其他映射对象，其中键是目标表中的列名称，或者是 Column 对象或其他 ORM 映射的列，匹配目标表的列，值是表达式或文字，指定要采取的 SET 操作。
  从版本 1.4 开始：Insert.on_conflict_do_update.set_ 参数支持目标 Table 中的 Column 对象作为键。
  警告
  此字典不考虑 Python 指定的默认 UPDATE 值或生成函数，例如使用 Column.onupdate 指定的值。除非在 Insert.on_conflict_do_update.set_ 字典中手动指定，否则这些值将不会用于 ON CONFLICT 类型的 UPDATE。
  
• where – 可选参数。如果存在，则可以是一个文字 SQL 字符串或一个可接受的 WHERE 子句表达式，用于限制受 DO UPDATE SET 影响的行。不满足 WHERE 条件的行将不会更新（对于这些行实际上是 DO NOTHING）。
  

Pysqlite

通过 pysqlite 驱动程序支持 SQLite 数据库。

请注意，pysqlite 与 Python 发行版中包含的 sqlite3 模块是相同的驱动程序。

DBAPI

pysqlite 的文档和下载信息（如果适用）可在此处找到：docs.python.org/library/sqlite3.html

连接

连接字符串：

sqlite+pysqlite:///file_path

驱动程序

在所有现代 Python 版本上，sqlite3 Python DBAPI 都是标准的；对于 cPython 和 Pypy，不需要额外安装。

连接字符串

SQLite 数据库的文件规范被视为 URL 的 “数据库” 部分。请注意，SQLAlchemy URL 的格式为：

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://:memory: 的文件名。如果没有文件路径，指定只有 sqlite:// 而没有其他内容：

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

URI 连接

现代版本的 SQLite 支持使用驱动级 URI进行连接的另一种系统，其优势在于可以传递附加的驱动级参数，包括诸如“只读”之类的选项。Python  的 sqlite3 驱动在现代 Python 3 版本下支持此模式。SQLAlchemy 的 pysqlite 驱动通过在 URL  查询字符串中指定“uri=true”来支持此使用模式。SQLite 级别的“URI”被保留为 SQLAlchemy URL  的“database”部分（即在斜杠后面）：

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

注意

“uri=true”参数必须出现在 URL 的查询字符串中。如果仅出现在create_engine.connect_args参数字典中，则目前不会按预期工作。

该逻辑通过分离属于 Python sqlite3 驱动程序和属于 SQLite URI 的参数来协调 SQLAlchemy 查询字符串和  SQLite 查询字符串的同时存在。这是通过使用已知被 Python 驱动程序的固定参数列表来实现的。例如，要包含指示 Python  sqlite3“timeout”和“check_same_thread”参数以及 SQLite“mode”和“nolock”参数的  URL，它们都可以一起传递到查询字符串中：

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 或本机驱动程序的参数。 SQLite URI 方案中添加的新参数名称应该会自动适应此方案。可以通过在create_engine.connect_args字典中指定它们来适应  Python 驱动程序端添加的新参数名称，直到 SQLAlchemy 添加了方言支持为止。对于本机 SQLite  驱动程序添加的新参数名称与现有的已知 Python 驱动程序参数之一（例如“timeout”）重叠的不太可能的情况，SQLAlchemy  的方言将需要调整 URL 方案以继续支持此参数。

对于所有 SQLAlchemy 方言，可以通过create_engine()的create_engine.creator参数绕过整个“URL”过程，该参数允许创建直接创建 Python sqlite3 驱动级连接的自定义可调用函数。

新版本中新增。

另请参见

统一资源标识符 - SQLite 文档中的正则表达式支持 ### 正则表达式支持

新版本中新增。

使用 Python 的 re.search 函数提供了对 ColumnOperators.regexp_match() 运算符的支持。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,
)

目前不支持将正则表达式标志作为单独参数，因为这些标志不受 SQLite 的 REGEXP 运算符支持，但可以在正则表达式字符串内联包含。详见Python 正则表达式。

另请参见

Python 正则表达式：Python 正则表达式语法的文档。

与 sqlite3 “本地”日期和日期时间类型兼容

pysqlite 驱动程序包括 sqlite3.PARSE_DECLTYPES 和 sqlite3.PARSE_COLNAMES  选项，这些选项的效果是任何明确转换为“date”或“timestamp”的列或表达式将转换为 Python 日期或日期时间对象。pysqlite  方言提供的日期和日期时间类型目前与这些选项不兼容，因为它们呈现 ISO 日期/日期时间，包括微秒，而 pysqlite  的驱动程序不包括。此外，SQLAlchemy  目前不会自动呈现“cast”语法，以使自由函数“current_timestamp”和“current_date”返回原生的  datetime/date 类型。不幸的是，pysqlite 不提供 cursor.description 中的标准 DBAPI 类型，使得 SQLAlchemy 无法在不进行昂贵的每行类型检查的情况下动态检测这些类型。

请注意，不推荐使用 pysqlite 的解析选项，也不应该使用 SQLAlchemy，如果配置了 “native_datetime=True” 在 create_engine() 上，可以强制使用 PARSE_DECLTYPES。

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

启用此标志后，DATE 和 TIMESTAMP 类型（但请注意 - 不是 DATETIME 或 TIME  类型…还困惑吗？）将不执行任何绑定参数或结果处理。执行 “func.current_date()” 将返回一个字符串。在 SQLAlchemy  中，“func.current_timestamp()” 被注册为返回 DATETIME 类型，因此此函数仍接收 SQLAlchemy  级别的结果处理。

线程/池行为

默认情况下，sqlite3 DBAPI 禁止在创建它的线程之外的线程中使用特定连接。随着 SQLite 的成熟，它在多线程下的行为已经改进，甚至包括选项，使得内存数据库可以在多个线程中使用。

线程禁止被称为“检查同一线程”，可以使用sqlite3参数check_same_thread进行控制，该参数将禁用或启用此检查。在使用基于文件的数据库时，SQLAlchemy 的默认行为是自动将check_same_thread设置为False，以确立与默认池类QueuePool的兼容性。

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

• 当指定:memory: SQLite 数据库时，默认情况下方言将使用SingletonThreadPool。此池在每个线程中维护单个连接，因此当前线程内对引擎的所有访问都使用相同的:memory:数据库 - 其他线程将访问不同的:memory:数据库。check_same_thread参数默认为True。
  
• 当指定基于文件的数据库时，方言将使用QueuePool作为连接的源。同时，默认情况下将check_same_thread标志设置为False，除非被覆盖。
  自 2.0 版本更改：SQLite 文件数据库引擎现在默认使用QueuePool。以前使用的是NullPool。可以通过create_engine.poolclass参数指定使用NullPool类。
  

禁用文件数据库的连接池

可以通过为poolclass()参数指定NullPool实现来禁用基于文件的数据库的连接池：

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

当使用NullPool实现时，由于QueuePool未实现连接重用，因此对于重复检出，NullPool实现会产生极小的性能开销。然而，如果应用程序遇到文件被锁定的问题，仍然可能有利于使用此类。

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

要在多线程场景中使用 :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 驱动程序直接插入的数据，或者在使用后更改为 LargeBinary 的 SQLAlchemy String 类型时，该表将无法一致地读取，因为 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

然后在通常会使用LargeBinary的地方使用上述MixedBinary数据类型。

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

在数据库锁定行为/并发性部分，我们提到 pysqlite 驱动程序的一系列问题，这些问题阻止 SQLite 的几个功能正常工作。  pysqlite DBAPI 驱动程序有几个长期存在的错误，影响其事务行为的正确性。在其默认操作模式下，SQLite  的功能，如可序列化隔离、事务 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")

警告

在使用上述配方时，建议不要在 SQLite 驱动程序上使用Connection.execution_options.isolation_level设置Connection和create_engine()，因为此函数必然也会改变“.isolation_level”设置。

在上面，我们拦截一个新的 pysqlite 连接并禁用任何事务集成。然后，在 SQLAlchemy 知道事务范围将开始的时候，我们自己发出"BEGIN"。

当我们控制"BEGIN"时，我们还可以直接控制 SQLite 的锁定模式，通过将所需的锁定模式添加到我们的"BEGIN"中引入的开始事务：

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

另请参阅

开始事务 - 在 SQLite 网站上

sqlite3 SELECT 不会 BEGIN 事务 - 在 Python 错误跟踪器上

sqlite3 模块中断事务并可能损坏数据 - 在 Python 错误跟踪器上 ### 用户定义的函数

pysqlite 支持一个 create_function() 方法，允许我们在 Python 中创建自己的用户定义的函数 (UDFs)，并直接在 SQLite 查询中使用它们。这些函数已与特定的 DBAPI 连接注册。

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()")))
```## Aiosqlite
通过 aiosqlite 驱动程序支持 SQLite 数据库。
### DBAPI
aiosqlite 的文档和下载信息（如果适用）可在此处获得：[`pypi.org/project/aiosqlite/`](https://pypi.org/project/aiosqlite/)
### 连接
连接字符串：
```py
sqlite+aiosqlite:///file_path

aiosqlite 方言提供了对运行在 pysqlite 之上的 SQLAlchemy asyncio 接口的支持。

aiosqlite 是对 pysqlite 的封装，每个连接使用一个后台线程。它实际上不使用非阻塞 IO，因为 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 以支持异步，因此我们可以在 Python 中创建自定义用户定义的函数 (UDFs)，并直接在  SQLite 查询中使用它们，如此处所述：用户定义的函数。### Serializable isolation / Savepoints /  Transactional DDL (asyncio 版本)

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

解决方案类似于 Serializable isolation / Savepoints / Transactional DDL。这是通过 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")

警告

在使用以上方案时，建议不要在 SQLite 驱动程序上使用 Connection.execution_options.isolation_level 设置 Connection 和 create_engine() ，因为该函数必然也会改变 “.isolation_level” 设置。## Pysqlcipher

通过 pysqlcipher 驱动程序支持 SQLite 数据库。

为支持利用 SQLCipher 后端的 DBAPI 提供方言。

连接中

连接字符串：

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

驱动程序

当前的方言选择逻辑是：

• 如果 create_engine.module 参数提供了一个 DBAPI 模块，则使用该模块。
  
• 否则对于 Python 3，选择pypi.org/project/sqlcipher3/
  
• 如果不可用，回退到pypi.org/project/pysqlcipher3/
  
• 对于 Python 2，使用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 引擎。该系统基本上引入了新的 PRAGMA 命令到 SQLite，这允许设置密码和其他加密参数，从而允许加密数据库文件。

连接字符串

连接字符串的格式在每个方面与 pysqlite 驱动程序的格式相同，除了现在接受“密码”字段，该字段应包含一个密码：

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

对于绝对文件路径，应该使用两个前导斜杠作为数据库名：

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

可以通过查询字符串传递一系列由 SQLCipher 支持的附加加密相关的 PRAGMA，如 www.zetetic.net/sqlcipher/sqlcipher-api/ 中所述，并且将导致每个新连接调用该 PRAGMA。目前，支持 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，这些 PRAGMA 被悄悄地忽略了。如果加密选项不匹配，这可能会导致打开由之前的 sqlalchemy 版本保存的文件时出错。

池行为

驱动程序对 pysqlite 的默认池行为进行了更改，如线程/池行为中所述。观察到 pysqlcipher 驱动程序连接速度比 pysqlite 驱动程序慢得多，很可能是由于加密开销，因此该方言在这里默认使用SingletonThreadPool实现，而不是 pysqlite 使用的NullPool池。与往常一样，可以使用create_engine.poolclass参数完全配置池实现；StaticPool可能更适合单线程使用，或者可以使用NullPool来防止未加密的连接被长时间保持打开，但新连接的启动时间较慢。

支持 SQLite 数据库。

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

支持的 SQLite 版本

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 文档中的内容

确保文本亲和性

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

参见

类型亲和性 - SQLite 文档中的内容

SQLite 自动增量行为

关于 SQLite 的自动增量的背景信息请参阅：sqlite.org/autoinc.html

关键概念：

• SQLite 具有隐式的“自动增量”功能，适用于任何使用“INTEGER PRIMARY KEY”来明确创建的非复合主键列。
  
• SQLite 还具有显式的 “AUTOINCREMENT” 关键字，这与隐式自动增量功能 不 等同；不建议一般使用这个关键字。SQLAlchemy 不会呈现此关键字，除非使用特殊的特定于 SQLite 的指令（见下文）。但是，它仍然要求列的类型被命名为 “INTEGER”。
  

使用 AUTOINCREMENT 关键字

要在渲染 DDL 时在主键列上具体呈现 AUTOINCREMENT 关键字，请将 sqlite_autoincrement=True 标志添加到 Table 构造函数中：

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

允许除 Integer/INTEGER 之外的 SQLAlchemy 类型具有自动增量行为

SQLite 的类型模型基于命名约定。这意味着包含子字符串 "INT" 的任何类型名称都将被确定为“整数亲和性”。一个名为 "BIGINT"、"SPECIAL_INT" 或甚至 "XYZINTQPR" 的类型都将被 SQLite 视为“整数”亲和性。然而，无论隐式还是显式启用了 SQLite 的自动增量功能，列类型的名称都必须正好是字符串 "INTEGER"。因此，如果应用程序使用 BigInteger 作为主键的类型，在 SQLite 上，当在发出初始的 CREATE TABLE 语句时，这个类型将需要被渲染为名称 "INTEGER"，以便自动增量行为可用。

实现此目标的一种方法是仅在 SQLite 上使用 TypeEngine.with_variant() 使用 Integer:

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 中的数据类型

使用 AUTOINCREMENT 关键字

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

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

允许除 Integer/INTEGER 外的 SQLAlchemy 类型具有自增行为

SQLite 的类型模型基于命名约定。除其他外，这意味着包含子字符串 "INT" 的任何类型名称都将被确定为“整数亲和性”。类型名称为 "BIGINT"、"SPECIAL_INT" 甚至 "XYZINTQPR" 的类型，SQLite 都会将其视为“整数”亲和性。然而，无论是隐式还是显式启用的 SQLite 自增特性，都要求列的类型名称正好是字符串"INTEGER"。因此，如果应用程序使用类似 BigInteger 的类型作为主键，在 SQLite 上，此类型在发出初始的 CREATE TABLE 语句时需要呈现为名称 "INTEGER"，以便自增行为可用。

实现此目标的一种方法是仅在 SQLite 上使用 TypeEngine.with_variant() 使用 Integer:

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 的设计缺乏写并发性的更多信息，请参阅在哪些情况下另一个 RDBMS 可能更适合使用 - 高并发性，页面底部。

以下子节介绍了受 SQLite 的基于文件的架构影响的领域，此外，通常在使用 pysqlite 驱动程序时需要一些解决方法。

事务隔离级别 / 自动提交

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

SQLAlchemy 使用create_engine.isolation_level参数来连接到此 PRAGMA 语句create_engine()。在与 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语句的性质。这三种类型是“延迟”、“立即”和“独占”，如开始事务所述。直接的BEGIN语句使用“延迟”模式，在第一次读取或写入操作之前不锁定数据库文件，并且读取访问在第一次写入操作之前仍然对其他事务开放。但需要再次强调的是，pysqlite 驱动器通过甚至不发出 BEGIN直到第一次写入操作来干扰此行为。

警告

SQLite 的事务范围受到 pysqlite 驱动程序中未解决的问题的影响，该问题将 BEGIN  语句推迟到比通常可行的更大程度。有关解决此行为的技术，请参阅部分可序列化隔离/保存点/事务 DDL 或可序列化隔离/保存点/事务  DDL（asyncio 版本）。

请参阅

设置事务隔离级别，包括 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 的支持

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