SQLite

Support for the SQLite database.

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

日期和时间类型

sqlite没有内置的日期、时间或日期时间类型，并且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 _ sqlite.DATETIME, DATE 和 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有一个隐式的“自动递增”特性，它适用于使用“整数主键”为类型+主键专门创建的任何非组合主键列。

• sqlite还具有一个显式的“autoincrement”关键字，即 not 相当于隐式自动增量功能；建议不要将此关键字用于一般用途。除非使用特殊的sqlite特定指令，否则sqlAlchemy不会呈现此关键字（请参见下文）。但是，它仍然要求列的类型名为“integer”。

使用autoincrement关键字

要在呈现DDL时具体呈现主键列上的autoincrement关键字，请添加标志 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 autoincrement功能都要求列类型的名称正好是字符串“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)
)

参见

TypeEngine.with_variant()

自定义SQL构造和编译扩展

Datatypes In SQLite Version 3

数据库锁定行为/并发性

SQLite不是为高级别的写并发而设计的。数据库本身是一个文件，在事务中的写操作期间完全被锁定，这意味着在此期间只有一个“连接”（实际上是一个文件句柄）对数据库具有独占访问权限-在此期间所有其他“连接”都将被阻止。

python dbapi规范还调用始终在事务中的连接模型；没有 connection.begin() 方法，仅 connection.commit() 和 connection.rollback() 立即开始新的交易。这似乎意味着，理论上，sqlite驱动程序在任何时候都只允许对特定数据库文件使用一个filehandle；但是，sqlite本身以及pysqlite驱动程序中都存在许多因素，这些因素大大放宽了这一限制。

但是，无论使用何种锁定模式，一旦启动了一个事务，并且至少发出了DML（例如插入、更新、删除），sqlite仍将始终锁定数据库文件，并且这将至少在其他事务也尝试发出DML时阻止其他事务。默认情况下，此块上的时间长度在出错超时之前非常短。

当与SQLAlchemy ORM结合使用时，此行为变得更加重要。SQLAlchemy Session 默认情况下，对象在事务中运行，并且其自动刷新模型可以在任何select语句之前发出dml。这可能导致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使用 create_engine.isolation_level 参数 create_engine() . 与sqlite一起使用时，此参数的有效值为 "SERIALIZABLE" 和 "READ UNCOMMITTED" 分别对应于0和1的值。sqlite默认为 SERIALIZABLE 但是，它的行为受pysqlite驱动程序的默认行为的影响。

当使用pysqlite驱动程序时 "AUTOCOMMIT" 隔离级别也可用，这将使用 .isolation_level 属性，并在设置期间将其设置为“无”。

1.3.16 新版功能: 添加了在使用pysqlite/sqlite3sqlite驱动程序时对SQLite自动提交隔离级别的支持。

影响SQLite事务锁定的另一个轴是通过 BEGIN 使用的语句。这三种类型是“延期”、“立即”和“独占”，如 BEGIN TRANSACTION 。直道 BEGIN 语句使用“延迟”模式，在这种模式下，数据库文件在第一次读或写操作之前不会被锁定，而读访问在第一次写操作之前对其他事务保持开放。但同样需要注意的是，pysqlite驱动程序通过以下方式干扰此行为 甚至连发射都没有开始 直到第一次写入操作为止。

警告

sqlite的事务范围受pysqlite驱动程序中未解决的问题的影响，该驱动程序将begin语句延迟到比通常可行的更大的程度。见剖面图 可序列化的隔离/保存点/事务性DDL 对于解决这种行为的技术。

参见

设置事务隔离级别，包括DBAPI Autocommit

保存点支持

sqlite支持保存点，该保存点只在事务开始时起作用。SQLAlchemy的保存点支持可以使用 Connection.begin_nested() 核心层面的方法，以及 Session.begin_nested() 在ORM级别。但是，保存点在pysqlite中根本不起作用，除非采取了解决方法。

警告

sqlite的保存点功能受pysqlite驱动程序中未解决的问题的影响，该驱动程序将begin语句延迟到比通常可行的更大的程度。见剖面图 可序列化的隔离/保存点/事务性DDL 对于解决这种行为的技术。

事务DDL

sqlite数据库支持事务性 DDL 也。在这种情况下，pysqlite驱动程序不仅无法启动事务，而且在检测到DDL时，它还会终止任何现有的事务，因此需要解决方法。

警告

sqlite的事务性ddl受pysqlite驱动程序中未解决的问题的影响，该驱动程序无法发出begin，并且在遇到ddl时强制提交以取消任何事务。见剖面图 可序列化的隔离/保存点/事务性DDL 对于解决这种行为的技术。

外键支持

当为表发出CREATE语句时，SQLite支持外键语法，但是默认情况下，这些约束对表的操作没有影响。

对sqlite的约束检查有三个先决条件：

• 必须至少使用sqlite的3.6.19版本

• 必须编译sqlite库 没有 已启用sqlite_omit_foreign_键或sqlite_omit_触发器符号。

• 这个 PRAGMA foreign_keys = ON 语句必须在所有连接上发出，然后才能使用--包括对 MetaData.create_all() 。

SQL炼金术允许 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处理设备的更多信息

相互依赖的外键约束。

论约束的冲突支持

参见

本节介绍 DDL SQLite的“ON CONFLICT”版本，它发生在CREATE TABLE语句中。有关应用于INSERT语句的“ON CONFLICT”，请参阅 冲突时插入（向上插入） .

SQLite支持一个称为ON CONFLICT的非标准DDL子句，它可以应用于主键、unique、check和not 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之一。例如，要添加指定忽略算法的唯一约束，请执行以下操作：

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

要对非空约束应用失败算法， 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')
)

这将在冲突短语上以内联方式呈现列：

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
)

冲突时插入（向上插入）

参见

本节介绍 DML SQLite的“ON CONFLICT”版本，它发生在INSERT语句中。有关应用于CREATE TABLE语句的“ON CONFLICT”，请参阅 论约束的冲突支持 .

从版本3.24.0开始，SQLite支持通过 ON CONFLICT 本条例草案的第(一)款 INSERT 声明。只有当候选行不违反任何UNIQUE或PRIMARY KEY约束时，才会插入该行。在违反唯一约束的情况下，可能会发生次要操作，可以是“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)
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() 方法做 not 考虑Python端的默认更新值或生成函数，例如使用 Column.onupdate . 除非在 Insert.on_conflict_do_update.set_ 字典。

使用排除的插入值更新

为了引用建议的插入行，特殊别名 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

附加WHERE标准

这个 Insert.on_conflict_do_update() 方法还接受使用 Insert.on_conflict_do_update.where 参数，该参数将限制接收更新的行：

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

如果 DO NOTHING 在未指定任何列或约束的情况下使用，则对于发生的任何唯一冲突，它将跳过插入操作：

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

类型反射

sqlite类型不同于大多数其他数据库后端，因为类型的字符串名称通常不会以一对一的方式与“类型”对应。相反，sqlite根据类型的字符串匹配模式将每列的类型行为链接到五个所谓的“类型关联”中的一个。

SQLAlChemy的反射进程在检查类型时，使用一个简单的查找表将返回的关键字链接到提供的SQLAlChemy类型。与所有其他方言一样，此查找表也存在于SQLite方言中。但是，SQLite方言有一个不同的“后备”例程，用于在查找映射中找不到特定的类型名称；相反，它实现了位于https://www.sqlite.org/datatype3.html第2.1节的SQLite“类型关联”方案。

所提供的类型映射将直接从与以下类型匹配的确切字符串名称进行关联：

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

当类型名称与上述类型之一不匹配时，将使用“类型关联”查找：

• 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 如果类型名称包含字符串，则返回 REAL ， FLOA 或 DOUB .

• 否则， NUMERIC 使用类型。

0.9.3 新版功能: 反射列时支持SQLite类型关联规则。

部分指标

部分索引，例如使用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

0.9.9 新版功能.

点式列名

使用其中显式包含句点的表或列名称是 未推荐的 . 对于关系数据库来说，这通常是一个坏主意，因为点是一个语法上有意义的字符，所以sqlite驱动程序直到版本 3.10.0 sqlite有一个bug，它要求sqlAlchemy在结果集中过滤掉这些点。

在 1.1 版更改: 从3.10.0版的sqlite开始，以下sqlite问题已得到解决。SQL炼金术自 1.1 根据此版本的检测自动禁用其内部解决方案。

完全不属于sqlAlchemy的bug可以通过以下方式进行说明：

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

在上面的地方，驱动程序错误地报告列的名称，包括表的名称，这与不存在联合时完全不一致。

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

注意上面，尽管sqlacalchemy过滤掉了这些点， 两个名称仍然可以寻址 ：：

>>> 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 执行选项，注意 使用联合的核心查询和ORM查询可能无法正常工作 .

SQLite特定表选项

SQLite方言与 Table 构建：

• WITHOUT ROWID ：：

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

参见

SQLite CREATE TABLE options

sqlite数据类型

与所有的sqlAlchemy方言一样，已知对sqlite有效的所有大写类型都可以从顶级方言导入，无论它们是否源自 sqlalchemy.types 或者来自当地方言：

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

class sqlalchemy.dialects.sqlite.DATETIME(*args, **kwargs)

Object Name	Description
insert(table[, values, inline, bind, ...], **dialect_kw)	构建一个 Insert 对象。
Insert	INSERT的SQLite特定实现。

function sqlalchemy.dialects.sqlite.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

通过pysqlite驱动程序支持sqlite数据库。请注意，pysqlite与python发行版中包含的sqlite3模块是相同的驱动程序。

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

驱动程序

这个 sqlite3 pythondbapi是所有现代Python版本的标准；对于cPython和Pypy，不需要额外的安装。

连接字符串

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

driver://user:pass@host/database

这意味着要使用的实际文件名以 正确的 第三个斜线。因此，连接到相对文件路径的方式如下：

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

绝对路径（以斜线开头）表示您需要 four 斜线：

# 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 这样做的优点是可以传递额外的驱动程序级参数，包括“只读”等选项。在现代Python3版本中，Python sqlite3驱动程序支持此模式。SQLAlChemy pysqlite驱动程序通过在URL查询字符串中指定“uri=true”来支持这种使用模式。SQLite级别的“URI”作为SQLAlChemy url的“数据库”部分保存(即，跟在劈开后面)：

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

注解

“uri=true”参数必须出现在 查询字符串 URL的。如果它只存在于 create_engine.connect_args 参数字典。

该逻辑通过分离属于pythonsqlite3驱动程序的参数与属于SQLite URI的参数，协调SQLAlchemy的查询字符串和SQLite查询字符串的同时存在。这是通过使用驱动程序的Python端可以接受的固定参数列表来实现的。例如，要包含一个URL，该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/sqlite3dbapi将作为以下参数传递：

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

关于将来添加到Python或本机驱动程序的参数。添加到SQLite URI方案中的新参数名称应自动由该方案容纳。添加到Python驱动程序端的新参数名可以通过在 create_engine.connect_args 字典，直到SQLAlchemy添加了方言支持。对于本机SQLite驱动程序添加一个与现有已知Python驱动程序参数之一重叠的新参数名（例如“timeout”可能是不太可能的情况），SQLAlchemy的方言需要对URL方案进行调整以继续支持这一点。

就像所有SQLAlchemy方言的情况一样，可以绕过整个“URL”过程 create_engine() 通过使用 create_engine.creator 参数，该参数允许自定义可调用项直接创建PythonSQLite3驱动程序级连接。

1.3.9 新版功能.

参见

Uniform Resource Identifiers -在sqlite文档中

正则表达式支持

1.4 新版功能.

支持 ColumnOperators.regexp_match() 运算符是使用Python的 re.search 功能。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 regular expressions 有关详细信息。

参见

Python regular expressions: Python正则表达式语法的文档。

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

pysqlite驱动程序包括sqlite3.parse_decltypes和sqlite3.parse_colnames选项，这些选项具有显式转换为“日期”或“时间戳”的任何列或表达式的效果，将转换为python日期或日期时间对象。pysqlite方言提供的日期和日期时间类型当前与这些选项不兼容，因为它们呈现的是包括微秒在内的ISO日期/日期时间，而pysqlite的驱动程序不提供微秒。此外，SQLAlchemy此时不会自动呈现独立函数“current_timestamp”和“current_date”以本机返回日期时间/日期类型所需的“cast”语法。不幸的是，pysqlite没有在 cursor.description ，使SQLAlchemy无法在不进行昂贵的每行类型检查的情况下即时检测这些类型。

请记住，不建议使用pysqlite的解析选项，也不应使用该选项。如果在create_engine（）上配置“native_datetime=true”，则可以强制使用parse_decltypes:

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

启用此标志后，日期和时间戳类型（但请注意，不是日期时间或时间类型…还不清楚？）不会执行任何绑定参数或结果处理。执行“func.current_date（）”将返回字符串。func.current_timestamp（）“注册为在sqlAlchemy中返回日期时间类型，因此此函数仍接收sqlAlchemy级别的结果处理。

线程/池行为

pysqlite的默认行为是禁止在多个线程中使用单个连接。这最初是为了与在各种情况下不支持多线程操作的旧版本的sqlite一起使用。特别是，旧的sqlite版本不允许 :memory: 在任何情况下都要在多个线程中使用的数据库。

pysqlite不包括现在未记录的标志，即 check_same_thread 这将禁用此检查，但是请注意，在多个线程中并发使用pysqlite连接仍然不安全。特别是，任何语句执行调用都需要在外部进行互斥，因为pysqlite不提供错误消息的线程安全传播。所以即使当 :memory: 在现代的sqlite中，数据库可以在线程之间共享，而pysqlite没有提供足够的线程安全性来保证这种使用的价值。

sqlAlchemy设置池以使用pysqlite的默认行为：

• 当A :memory: 指定了sqlite数据库，默认情况下方言将使用 SingletonThreadPool . 此池为每个线程维护一个连接，以便当前线程内对引擎的所有访问都使用相同的连接 :memory: 数据库-其他线程将访问 :memory: 数据库。

• 当指定了基于文件的数据库时，方言将使用 NullPool 作为联系的来源。此池关闭并丢弃立即返回池的连接。基于sqlite文件的连接开销非常低，因此不需要池。该方案还防止在不同的线程中再次使用连接，并且最好使用sqlite的粗粒度文件锁定。

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

使用A :memory: 数据库在多线程方案中，同一个连接对象必须在线程之间共享，因为该数据库只存在于该连接的范围内。这个 StaticPool 实现将在全球保持单一连接，并且 check_same_thread 标志可以作为 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 应该针对要使用的线程数进行配置；超过该数目，连接将以非确定性方式关闭。

统一码

pysqlite驱动程序只返回python unicode 结果集中的对象，而不是纯字符串，并且 unicode 在所有情况下，绑定参数值中的对象。不管使用的是什么sqlachemy字符串类型，基于字符串的结果值都将由python unicode 在Python 2中。这个 Unicode 但是，类型仍应用于指示那些需要Unicode的列，因此无意中传递的非“unicode”值将发出警告。如果传递包含非ASCII字符的非“unicode”字符串，则pysqlite将发出错误。

在python3中处理混合字符串/二进制列

SQLite数据库是弱类型的，因此在使用二进制值时也是可能的，在Python3中，二进制值表示为 b'some string' ，一个特定的SQLite数据库可以在不同的行中包含数据值，其中一些数据值将作为 b'' Python将以字符串的形式返回，其他字符串将作为SQLG返回。 '' 价值观。如果SQLAlchemy LargeBinary 但是，如果某个特定的SQLite数据库包含直接使用Pysqlite驱动程序插入的数据，或者在使用SQLAlchemy时，数据类型的使用是一致的 String 后来改为 LargeBinary ，由于SQLAlchemy的 LargeBinary 数据类型不处理字符串，因此无法对字符串格式的值进行“编码”。

要处理在同一列中包含混合字符串/二进制数据的SQLite表，请使用一个自定义类型，该类型将分别检查每一行：

# note this is Python 3 only

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功能（如可序列化隔离、事务性DDL和保存点支持）是不起作用的，为了使用这些功能，必须采取解决方法。

问题实质上是，驱动程序试图再次猜测用户的意图，未能启动事务，有时过早地结束事务，以尽量减少sqlite数据库的文件锁定行为，即使sqlite本身对只读活动使用“共享”锁。

默认情况下，SQLAlchemy选择不更改此行为，因为这是Pysqlite驱动程序的长期预期行为；如果并且当Pysqlite驱动程序尝试修复这些问题时，这将更像是SQLAlchemy的默认驱动程序。

好消息是，通过一些事件，我们可以通过完全禁用pysqlite的特性并发出begin-self来完全实现事务支持。这是通过两个事件监听器实现的：

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连接并禁用了任何事务集成。然后，在sqlacalchemy知道事务范围即将开始的时候，我们发出 "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错误跟踪器

硅铝榴石

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

DBAPI

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

连接

Connect String:

sqlite+aiosqlite:///file_path

aiosqlite方言提供了对运行在pysqlite之上的SQLAlChemy异步接口的支持。

aiosqlite是一个围绕pysqlite的包装器，它为每个连接使用一个后台线程。它实际上不使用非阻塞IO，因为SQLite数据库不是基于套接字的。不过，它确实提供了一个可工作的异步接口，这对测试和原型制作很有用。

使用特殊的异步中介层，aiosqlite方言可用作 SQLAlchemy asyncio 扩展包。

此方言通常应仅与 create_async_engine() 引擎创建功能：：

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

URL将所有参数传递给 pysqlite 驱动程序，因此所有连接参数都与 镁尖晶石 。

密码子

通过pysqlcipher驱动程序支持SQLite数据库。Dialect用于支持使用SQLCipher后端的DBAPI。

连接

Connect String:

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

驱动程序

当前方言选择逻辑为：

• 如果 create_engine.module 参数提供DBAPI模块，则使用该模块。

• 否则，对于Python3，请选择https://pypi.org/project/sqlcipher3/

• 如果不可用，请回退到https://pypi.org/project/pysqlcipher3/

• 对于Python2，使用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命令，该命令允许设置密码和其他加密参数，从而允许对数据库文件进行加密。

连接字符串

连接字符串的格式与 pysqlite 驱动程序，除了“密码”字段现在被接受，它应该包含一个密码短语：

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

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

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

如https://www.zettic.net/sqlcipher/sqlcipher-api/所述，sqlcipher支持的其他与加密相关的pragma的选择可以在查询字符串中传递，并将导致为每个新连接调用该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字符串中传递的与加密相关的编译指示，这些编译指示被静默忽略。如果加密选项不匹配，则在打开由以前的sqlalChemy版本保存的文件时，这可能会导致错误。

汇集行为

驱动程序更改pysqlite的默认池行为，如中所述。 线程/池行为 . 据观察，pysqlicpher驱动程序在连接上比pysqlite驱动程序慢得多，这很可能是由于加密开销，因此这里的方言默认使用 SingletonThreadPool 实现，而不是 NullPool pysqlite使用的池。和往常一样，池实现是完全可配置的，使用 create_engine.poolclass 参数； StaticPool 可能更适合单线程使用，或 NullPool 可以用来防止未加密的连接长时间保持打开状态，代价是新连接的启动时间变慢。