sqlite3 — SQLite 数据库的 DB-API 2.0 接口 — Python 文档

sqlite3 — SQLite 数据库的 DB-API 2.0 接口

源代码： :source:`Lib/sqlite3/`

---

SQLite 是一个 C 库，它提供了一个轻量级的基于磁盘的数据库，它不需要单独的服务器进程，并允许使用 SQL 查询语言的非标准变体来访问数据库。 某些应用程序可以使用 SQLite 进行内部数据存储。 也可以使用 SQLite 对应用程序进行原型设计，然后将代码移植到更大的数据库，例如 PostgreSQL 或 Oracle。

sqlite3 模块由 Gerhard Häring 编写。 它提供了符合 PEP 249 描述的 DB-API 2.0 规范的 SQL 接口。

要使用该模块，首先要创建一个表示数据库的 Connection 对象。 这里的数据将存储在 example.db 文件中：

import sqlite3
con = sqlite3.connect('example.db')

可以提供特殊路径名 :memory: 在 RAM 中创建临时数据库。

一旦建立了 Connection，创建一个 Cursor 对象并调用它的 execute() 方法来执行 SQL 命令：

cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

保存的数据是持久的：即使在重新启动 Python 解释器后，它也可以在后续会话中重新加载：

import sqlite3
con = sqlite3.connect('example.db')
cur = con.cursor()

要在执行 SELECT 语句后检索数据，请将游标视为 迭代器 ，调用游标的 fetchone() 方法检索单个匹配行，或调用 fetchall( ) 获取匹配行的列表。

此示例使用迭代器形式：

>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

SQL 操作通常需要使用 Python 变量中的值。 但是，请注意使用 Python 的字符串操作来组合查询，因为它们容易受到 SQL 注入攻击（有关可能出错的幽默示例，请参阅 xkcd webcomic）：

# Never do this -- insecure!
symbol = 'RHAT'
cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

相反，使用 DB-API 的参数替换。 要将变量插入查询字符串，请在字符串中使用占位符，并将实际值作为值的 元组 提供给游标 execute( ) 方法。 SQL 语句可以使用两种占位符之一：问号（qmark 样式）或命名占位符（命名样式）。 对于qmark样式，parameters必须是序列。 对于命名样式，它可以是 sequence 或 dict 实例。 序列 的长度必须与占位符的数量匹配，否则会引发 ProgrammingError。 如果给出了 dict，它必须包含所有命名参数的键。 任何额外的项目都将被忽略。 这是两种样式的示例：

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table lang (name, first_appeared)")

# This is the qmark style:
cur.execute("insert into lang values (?, ?)", ("C", 1972))

# The qmark style used with executemany():
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

# And this is the named style:
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
print(cur.fetchall())

con.close()

也可以看看

https://www.sqlite.org

SQLite 网页； 该文档描述了支持的 SQL 方言的语法和可用数据类型。

https://www.w3schools.com/sql/

学习 SQL 语法的教程、参考和示例。

PEP 249 - 数据库 API 规范 2.0

PEP 由 Marc-André Lemburg 编写。

模块函数和常量

sqlite3.apilevel

字符串常量说明支持的 DB-API 级别。 DB-API 需要。 硬编码为 "2.0"。

sqlite3.paramstyle

字符串常量说明 sqlite3 模块期望的参数标记格式的类型。 DB-API 需要。 硬编码为 "qmark"。

笔记

sqlite3 模块支持 qmark 和 numeric DB-API 参数样式，因为这是底层 SQLite 库支持的。 但是，DB-API 不允许 paramstyle 属性有多个值。

sqlite3.version

此模块的版本号，作为字符串。 这不是 SQLite 库的版本。

sqlite3.version_info

此模块的版本号，作为整数元组。 这不是 SQLite 库的版本。

sqlite3.sqlite_version

运行时 SQLite 库的版本号，作为字符串。

sqlite3.sqlite_version_info

运行时 SQLite 库的版本号，作为整数元组。

sqlite3.threadsafety

DB-API 所需的整数常量，说明 sqlite3 模块支持的线程安全级别。 目前硬编码为 1，意思是 “线程可以共享模块，但不能共享连接。” 然而，这可能并不总是正确的。 您可以使用以下查询检查底层 SQLite 库的编译时线程模式：

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""
    select * from pragma_compile_options
    where compile_options like 'THREADSAFE=%'
""").fetchall()

请注意，SQLITE_THREADSAFE 级别 与 DB-API 2.0 threadsafety 级别不匹配。

sqlite3.PARSE_DECLTYPES

此常量旨在与 connect() 函数的 detect_types 参数一起使用。

设置它会使 sqlite3 模块解析它返回的每一列的声明类型。 它将解析出声明类型的第一个单词，i。 e. 对于“integer primary key”，它会解析出“integer”，或者对于“number(10)”，它将解析出“number”。 然后对于该列，它将查看转换器字典并使用为该类型注册的转换器函数。

sqlite3.PARSE_COLNAMES

此常量旨在与 connect() 函数的 detect_types 参数一起使用。

设置它会使 SQLite 接口解析它返回的每一列的列名。 它会在那里寻找一个形成 [mytype] 的字符串，然后确定 'mytype' 是列的类型。 它将尝试在转换器字典中找到“mytype”条目，然后使用在那里找到的转换器函数返回值。 在 Cursor.description 中找到的列名不包括类型 i。 e. 如果您在 SQL 中使用 'as "Expiration date [datetime]"' 之类的内容，那么我们将解析所有内容，直到列名的第一个 '[' 并去除前面的空格：列名将只是“到期日期” .

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

打开与 SQLite 数据库文件 数据库 的连接。 默认情况下返回 Connection 对象，除非给出自定义 factory。

database 是一个 类似路径的对象 ，给出要打开的数据库文件的路径名（绝对或相对于当前工作目录）。 您可以使用 ":memory:" 打开到驻留在 RAM 中而不是磁盘上的数据库的数据库连接。

当一个数据库被多个连接访问，并且其中一个进程修改了该数据库时，SQLite 数据库将被锁定，直到该事务被提交。 timeout 参数指定连接应该等待锁定消失的时间，直到引发异常。 timeout 参数的默认值为 5.0（五秒）。

对于isolation_level参数，请参见Connection对象的isolation_level属性。

SQLite 本身只支持 TEXT、INTEGER、REAL、BLOB 和 NULL 类型。 如果您想使用其他类型，您必须自己添加对它们的支持。 detect_types 参数和使用模块级 register_converter() 函数注册的自定义 converters 可让您轻松做到这一点。

detect_types 默认为 0 (i. e. off，无类型检测），您可以将其设置为 PARSE_DECLTYPES 和 PARSE_COLNAMES 的任意组合来打开类型检测。 由于 SQLite 行为，即使设置了 detect_types 参数，也无法检测生成字段的类型（例如 max(data)）。 在这种情况下，返回的类型是 str。

默认情况下，check_same_thread 是 True 并且只有创建线程可以使用该连接。 如果设置 False，则返回的连接可能会在多个线程之间共享。 当使用多个线程进行相同的连接写入操作时，用户应该进行序列化以避免数据损坏。

默认情况下，sqlite3 模块使用其 Connection 类进行连接调用。 但是，您可以将 Connection 类设为子类，并通过为 factory 参数提供您的类来使 connect() 使用您的类。

有关详细信息，请参阅本手册的 SQLite 和 Python 类型 部分。

sqlite3 模块内部使用语句缓存来避免 SQL 解析开销。 如果要显式设置为连接缓存的语句数，可以设置 cached_statements 参数。 当前实现的默认值是缓存 100 条语句。

如果 uri 为真，则 database 被解释为一个 URI。 这允许您指定选项。 例如，要以只读模式打开数据库，您可以使用：

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

有关此功能的更多信息，包括已识别选项的列表，可以在 SQLite URI 文档 中找到。

3.4 版更改： 添加 uri 参数。

在 3.7 版更改： database 现在也可以是 类路径对象 ，而不仅仅是字符串。

sqlite3.register_converter(typename, callable)

注册一个可调用对象，将数据库中的字节串转换为自定义 Python 类型。 将为 typename 类型的所有数据库值调用可调用对象。 赋予 connect() 函数的参数 detect_types 以了解类型检测的工作原理。 请注意， typename 和查询中的类型名称以不区分大小写的方式匹配。

sqlite3.register_adapter(type, callable)

注册一个可调用对象以将自定义 Python 类型 type 转换为 SQLite 支持的类型之一。 callable callable 接受 Python 值作为单个参数，并且必须返回以下类型的值：int、float、str 或 bytes。

sqlite3.complete_statement(sql)

如果字符串 sql 包含一个或多个以分号结尾的完整 SQL 语句，则返回 True。 它不验证 SQL 在语法上是否正确，只验证没有未闭合的字符串文字并且语句以分号终止。

这可用于为 SQLite 构建 shell，如下例所示：

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")

while True:
    line = input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print(cur.fetchall())
        except sqlite3.Error as e:
            print("An error occurred:", e.args[0])
        buffer = ""

con.close()

sqlite3.enable_callback_tracebacks(flag)

默认情况下，您不会在用户定义的函数、聚合、转换器、授权方回调等中获得任何回溯。 如果要调试它们，可以将 flag 设置为 True 来调用此函数。 之后，您将从 sys.stderr 上的回调中获得回溯。 使用 False 再次禁用该功能。

连接对象

class sqlite3.Connection

SQLite 数据库连接具有以下属性和方法：

isolation_level

获取或设置当前的默认隔离级别。 None 用于自动提交模式或“DEFERRED”、“IMMEDIATE”或“EXCLUSIVE”之一。 有关更详细的说明，请参阅 控制事务 部分。

in_transaction

True 如果事务处于活动状态（有未提交的更改），否则为 False。 只读属性。

3.2 版中的新功能。

cursor(factory=Cursor)

游标方法接受单个可选参数 factory。 如果提供，这必须是可调用的，返回 Cursor 或其子类的实例。

commit()

此方法提交当前事务。 如果不调用此方法，则自上次调用 commit() 以来所做的任何事情都不会从其他数据库连接中看到。 如果你想知道为什么你看不到你写入数据库的数据，请检查你没有忘记调用这个方法。

rollback()

此方法回滚自上次调用 commit() 以来对数据库的任何更改。

close()

这将关闭数据库连接。 请注意，这不会自动调用 commit()。 如果您只是关闭数据库连接而不先调用 commit()，您的更改将会丢失！

execute(sql[, parameters])

这是一个非标准的快捷方式，它通过调用 cursor() 方法创建一个游标对象，使用给定的 参数 调用游标的 execute() 方法，并返回光标。

executemany(sql[, parameters])

这是一个非标准的快捷方式，它通过调用 cursor() 方法创建一个游标对象，使用给定的 参数 调用游标的 executemany() 方法，并返回光标。

executescript(sql_script)

这是一个非标准的快捷方式，它通过调用 cursor() 方法创建游标对象，使用给定的 sql_script 调用游标的 executescript() 方法，并返回光标。

create_function(name, num_params, func, *, deterministic=False)

创建一个用户定义的函数，您以后可以在函数名 name 下的 SQL 语句中使用该函数。 num_params 是函数接受的参数数量（如果 num_params 是 -1，函数可以接受任意数量的参数），而 func 是一个 Python 可调用的这被称为 SQL 函数。 如果 deterministic 为真，则创建的函数被标记为 deterministic，这允许 SQLite 执行额外的优化。 SQLite 3.8.3 或更高版本支持此标志，如果与旧版本一起使用，将引发 NotSupportedError。

该函数可以返回 SQLite 支持的任何类型：bytes、str、int、float 和 None。

3.8 版更改： 添加了 确定性 参数。

例子：

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",))
print(cur.fetchone()[0])

con.close()

create_aggregate(name, num_params, aggregate_class)

创建用户定义的聚合函数。

聚合类必须实现 step 方法，该方法接受参数数量 num_params（如果 num_params 为 -1，该函数可以采用任意数量的参数），和 finalize 方法将返回聚合的最终结果。

finalize 方法可以返回 SQLite 支持的任何类型：bytes、str、int、float 和 None。

例子：

import sqlite3

class MySum:
    def __init__(self):
        self.count = 0

    def step(self, value):
        self.count += value

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print(cur.fetchone()[0])

con.close()

create_collation(name, callable)

创建具有指定 name 和 callable 的排序规则。 callable 将传递两个字符串参数。 如果第一个排序低于第二个，则返回 -1，如果它们相等则返回 0，如果第一个排序高于第二个则返回 1。 请注意，这控制排序（SQL 中的 ORDER BY），因此您的比较不会影响其他 SQL 操作。

请注意，可调用对象将其参数作为 Python 字节串获取，通常以 UTF-8 编码。

以下示例显示了一个以“错误方式”排序的自定义排序规则：

import sqlite3

def collate_reverse(string1, string2):
    if string1 == string2:
        return 0
    elif string1 < string2:
        return 1
    else:
        return -1

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print(row)
con.close()

要删除排序规则，请使用 None 调用 create_collation 作为可调用：

con.create_collation("reverse", None)

interrupt()

您可以从不同的线程调用此方法以中止可能在连接上执行的任何查询。 然后查询将中止，调用者将收到异常。

set_authorizer(authorizer_callback)

该例程注册一个回调。 每次尝试访问数据库中表的列时都会调用回调。 如果允许访问，回调应返回 SQLITE_OK，如果整个 SQL 语句应因错误而中止，则应返回 SQLITE_DENY，如果列应被视为 NULL 值，则应返回 SQLITE_IGNORE。 这些常量在 sqlite3 模块中可用。

回调的第一个参数表示要授权的操作类型。 第二个和第三个参数将是参数或 None 取决于第一个参数。 如果适用，第四个参数是数据库的名称（“main”、“temp”等）。 第 5 个参数是负责访问尝试的最内部触发器或视图的名称，如果此访问尝试直接来自输入 SQL 代码，则为 None。

请查阅 SQLite 文档，了解第一个参数的可能值以及第二个和第三个参数的含义（取决于第一个参数）。 sqlite3 模块中提供了所有必需的常量。

set_progress_handler(handler, n)

该例程注册一个回调。 为 SQLite 虚拟机的每个 n 条指令调用回调。 如果您想在长时间运行的操作期间从 SQLite 调用，这很有用，例如更新 GUI。

如果要清除任何以前安装的进度处理程序，请使用 None 调用 handler 的方法。

从处理程序函数返回一个非零值将终止当前正在执行的查询并导致它引发 OperationalError 异常。

set_trace_callback(trace_callback)

注册 trace_callback 为 SQLite 后端实际执行的每个 SQL 语句调用。

传递给回调的唯一参数是正在执行的语句（如 str）。 回调的返回值被忽略。 请注意，后端不仅运行传递给 Cursor.execute() 方法的语句。 其他来源包括sqlite3模块的事务管理和当前数据库中定义的触发器的执行。

将 None 作为 trace_callback 传递将禁用跟踪回调。

笔记

跟踪回调中引发的异常不会传播。 作为开发和调试帮助，使用 enable_callback_tracebacks() 启用从跟踪回调中引发的异常打印回溯。

3.3 版中的新功能。

enable_load_extension(enabled)

此例程允许/禁止 SQLite 引擎从共享库加载 SQLite 扩展。 SQLite 扩展可以定义新函数、聚合或全新的虚拟表实现。 一个众所周知的扩展是随 SQLite 分发的全文搜索扩展。

默认情况下禁用可加载扩展。 参见 1。

3.2 版中的新功能。

import sqlite3

con = sqlite3.connect(":memory:")

# enable extension loading
con.enable_load_extension(True)

# Load the fulltext search extension
con.execute("select load_extension('./fts3.so')")

# alternatively you can load the extension using an API call:
# con.load_extension("./fts3.so")

# disable extension loading again
con.enable_load_extension(False)

# example from SQLite wiki
con.execute("create virtual table recipe using fts3(name, ingredients)")
con.executescript("""
    insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');
    insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');
    insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');
    insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');
    """)
for row in con.execute("select rowid, name, ingredients from recipe where name match 'pie'"):
    print(row)

con.close()

load_extension(path)

此例程从共享库加载 SQLite 扩展。 在使用此例程之前，您必须使用 enable_load_extension() 启用扩展加载。

默认情况下禁用可加载扩展。 参见 1。

3.2 版中的新功能。

row_factory

您可以将此属性更改为接受游标和原始行作为元组并返回实际结果行的可调用对象。 通过这种方式，您可以实现更高级的返回结果的方式，例如返回一个也可以按名称访问列的对象。

例子：

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

con.close()

如果返回元组还不够，并且您希望基于名称访问列，则应考虑将 row_factory 设置为高度优化的 sqlite3.Row 类型。 Row 提供对列的基于索引和不区分大小写的基于名称的访问，几乎没有内存开销。 它可能比您自己的基于字典的自定义方法甚至基于 db_row 的解决方案更好。

text_factory

使用此属性，您可以控制为 TEXT 数据类型返回哪些对象。 默认情况下，此属性设置为 str 并且 sqlite3 模块将为 TEXT 返回 str 对象。 如果你想返回 bytes，你可以将它设置为 bytes。

您还可以将其设置为任何其他接受单个字节串参数并返回结果对象的可调用对象。

请参阅以下示例代码以进行说明：

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

AUSTRIA = "Österreich"

# by default, rows are returned as str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# but we can make sqlite3 always return bytestrings ...
con.text_factory = bytes
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is bytes
# the bytestrings will be encoded in UTF-8, unless you stored garbage in the
# database ...
assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...
# here we implement one that appends "foo" to all strings
con.text_factory = lambda x: x.decode("utf-8") + "foo"
cur.execute("select ?", ("bar",))
row = cur.fetchone()
assert row[0] == "barfoo"

con.close()

total_changes

返回自数据库连接打开以来已修改、插入或删除的数据库行总数。

iterdump()

返回一个迭代器以 SQL 文本格式转储数据库。 在保存内存数据库以供以后恢复时很有用。 此函数提供与 sqlite3 shell 中的 .dump 命令相同的功能。

例子：

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)
con.close()

backup(target, *, pages=- 1, progress=None, name='main', sleep=0.250)

即使在其他客户端正在访问 SQLite 数据库或由同一连接同时访问该数据库时，此方法也会对 SQLite 数据库进行备份。 该副本将写入强制参数 target，该参数必须是另一个 Connection 实例。

默认情况下，或者当 pages 为 0 或负整数时，整个数据库将一步复制； 否则该方法执行一次循环复制最多 页 页。

如果指定了 progress，则它必须是 None 或将在每次迭代中执行的可调用对象，并带有三个整数参数，分别为上次迭代的 status ， 剩余 页仍需复印的页数和 总 页数。

name 参数指定将被复制的数据库名称：它必须是一个字符串，其中包含 "main"（默认值，表示主数据库），"temp" 表示主数据库临时数据库或附加数据库的 ATTACH DATABASE 语句中的 AS 关键字后指定的名称。

sleep 参数指定在连续尝试备份剩余页面之间休眠的秒数，可以指定为整数或浮点值。

示例 1，将现有数据库复制到另一个：

import sqlite3

def progress(status, remaining, total):
    print(f'Copied {total-remaining} of {total} pages...')

con = sqlite3.connect('existing_db.db')
bck = sqlite3.connect('backup.db')
with bck:
    con.backup(bck, pages=1, progress=progress)
bck.close()
con.close()

示例 2，将现有数据库复制到临时副本中：

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)

可用性：SQLite 3.6.11 或更高版本

3.7 版中的新功能。

光标对象

class sqlite3.Cursor

Cursor 实例具有以下属性和方法。

execute(sql[, parameters])

执行 SQL 语句。 值可以使用 占位符 绑定到语句。

execute() 只会执行一条 SQL 语句。 如果您尝试使用它执行多个语句，它将引发 Warning。 如果要一次调用执行多个 SQL 语句，请使用 executescript()。

executemany(sql, seq_of_parameters)

针对在序列 seq_of_parameters 中找到的所有参数序列或映射执行 参数化 SQL 命令。 sqlite3 模块还允许使用 迭代器 产生参数而不是序列。

import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def __next__(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

这是一个使用 生成器 的较短示例：

import sqlite3
import string

def char_generator():
    for c in string.ascii_lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")
print(cur.fetchall())

con.close()

executescript(sql_script)

这是一次执行多个 SQL 语句的非标准便捷方法。 它首先发出 COMMIT 语句，然后执行它作为参数获取的 SQL 脚本。 该方法无视isolation_level； 任何事务控制都必须添加到 sql_script。

sql_script 可以是 str 的实例。

例子：

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)
con.close()

fetchone()

获取查询结果集的下一行，返回单个序列，或者在没有更多数据可用时返回 None。

fetchmany(size=cursor.arraysize)

获取查询结果的下一组行，返回一个列表。 当没有更多行可用时，将返回一个空列表。

每次调用要获取的行数由 size 参数指定。 如果没有给出，游标的数组大小决定了要获取的行数。 该方法应尝试获取由 size 参数指示的尽可能多的行。 如果由于指定的行数不可用而无法执行此操作，则可能会返回较少的行。

请注意，size 参数涉及性能注意事项。 为了获得最佳性能，通常最好使用 arraysize 属性。 如果使用 size 参数，那么最好让它从一个 fetchmany() 调用到下一个调用保持相同的值。

fetchall()

获取查询结果的所有（剩余）行，返回一个列表。 请注意，游标的 arraysize 属性会影响此操作的性能。 当没有可用行时，返回一个空列表。

close()

现在关闭光标（而不是在调用 __del__ 时）。

从现在开始，光标将无法使用； 如果尝试对光标进行任何操作，将引发 ProgrammingError 异常。

setinputsizes(sizes)

DB-API 需要。 在 sqlite3 中是空操作。

setoutputsize(size[, column])

DB-API 需要。 在 sqlite3 中是空操作。

rowcount

尽管 sqlite3 模块的 Cursor 类实现了该属性，但数据库引擎自身对确定“受影响的行”/“选定的行”的支持是古怪的。

对于 executemany() 语句，修改次数汇总为 rowcount。

根据 Python DB API 规范的要求，rowcount 属性“为 -1，以防在游标上未执行 executeXX() 或接口无法确定最后一次操作的行数”。 这包括 SELECT 语句，因为在获取所有行之前我们无法确定查询生成的行数。

对于 3.6.5 之前的 SQLite 版本，如果您在没有任何条件的情况下创建 DELETE FROM table，则 rowcount 将设置为 0。

lastrowid

此只读属性提供最后修改行的 rowid。 仅当您使用 execute() 方法发出 INSERT 或 REPLACE 语句时才设置它。 对于除 INSERT 或 REPLACE 以外的操作，或调用 executemany() 时，lastrowid 设置为 None。

如果 INSERT 或 REPLACE 语句未能插入之前成功的 rowid，则返回。

3.6 版更改： 添加了对 REPLACE 语句的支持。

arraysize

控制 fetchmany() 返回的行数的读/写属性。 默认值为 1，这意味着每次调用将获取一行。

description

此只读属性提供最后一个查询的列名。 为了与 Python DB API 保持兼容，它为每列返回一个 7 元组，其中每个元组的最后六项是 None。

它也适用于没有任何匹配行的 SELECT 语句。

connection

此只读属性提供 Cursor 对象使用的 SQLite 数据库 Connection。 通过调用 con.cursor() 创建的 Cursor 对象将具有引用 con 的 connection 属性：

>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> cur.connection == con
True

行对象

class sqlite3.Row

Row 实例用作 Connection 对象的高度优化的 row_factory。 它试图在其大部分功能中模仿元组。

它支持按列名和索引、迭代、表示、相等性测试和 len() 进行映射访问。

如果两个 Row 对象具有完全相同的列并且它们的成员相等，则它们比较相等。

keys()

此方法返回一个列名列表。 在查询之后，它是 Cursor.description 中每个元组的第一个成员。

3.5 版更改： 增加了对切片的支持。

假设我们按照上面给出的示例初始化一个表：

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
cur.execute("""insert into stocks
            values ('2006-01-05','BUY','RHAT',100,35.14)""")
con.commit()
cur.close()

现在我们将 Row 插入：

>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = cur.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

例外

exception sqlite3.Warning

Exception 的子类。

exception sqlite3.Error

此模块中其他异常的基类。 它是 Exception 的子类。

exception sqlite3.DatabaseError

与数据库相关的错误引发的异常。

exception sqlite3.IntegrityError

当数据库的关系完整性受到影响时引发异常，例如 外键检查失败。 它是 DatabaseError 的子类。

exception sqlite3.ProgrammingError

因编程错误引发的异常，例如 表未找到或已存在、SQL 语句中的语法错误、指定的参数数量错误等。 它是 DatabaseError 的子类。

exception sqlite3.OperationalError

与数据库操作相关且不一定受程序员控制的错误引发的异常，例如 发生意外断开、未找到数据源名称、无法处理事务等。 它是 DatabaseError 的子类。

exception sqlite3.NotSupportedError

如果使用了数据库不支持的方法或数据库 API，则会引发异常，例如 在不支持事务或已关闭事务的连接上调用 rollback() 方法。 它是 DatabaseError 的子类。

SQLite 和 Python 类型

介绍

SQLite 本身支持以下类型：NULL、INTEGER、REAL、TEXT、BLOB。

因此，以下 Python 类型可以毫无问题地发送到 SQLite：

蟒蛇型	SQLite 类型
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

这是默认情况下 SQLite 类型转换为 Python 类型的方式：

SQLite 类型	蟒蛇型
NULL	None
INTEGER	int
REAL	float
TEXT	默认取决于 text_factory, str
BLOB	bytes

sqlite3模块的类型系统有两种扩展方式：可以通过对象适配在SQLite数据库中存储额外的Python类型，可以让sqlite3模块将SQLite类型转换为不同的 Python 类型通过转换器。

使用适配器在 SQLite 数据库中存储额外的 Python 类型

如前所述，SQLite 本身只支持有限的一组类型。 要在 SQLite 中使用其他 Python 类型，您必须 使 它们适应 SQLite 的 sqlite3 模块支持的类型之一：NoneType、int、float、str、bytes 之一。

有两种方法可以使 sqlite3 模块使自定义 Python 类型适应受支持的类型之一。

让你的对象适应自己

如果您自己编写类，这是一个很好的方法。 假设你有一个这样的类：

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

现在您想将该点存储在单个 SQLite 列中。 首先，您必须选择一种受支持的类型来表示点。 让我们只使用 str 并使用分号分隔坐标。 然后你需要给你的类一个方法 __conform__(self, protocol) 它必须返回转换后的值。 参数 protocol 将为 PrepareProtocol。

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

注册一个可调用的适配器

另一种可能性是创建一个将类型转换为字符串表示的函数，并使用 register_adapter() 注册该函数。

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

con.close()

sqlite3 模块有两个默认适配器，用于 Python 的内置 datetime.date 和 datetime.datetime 类型。 现在让我们假设我们想要存储 datetime.datetime 对象不是以 ISO 表示形式，而是作为 Unix 时间戳。

import sqlite3
import datetime
import time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite3.connect(":memory:")
cur = con.cursor()

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print(cur.fetchone()[0])

con.close()

将 SQLite 值转换为自定义 Python 类型

编写适配器可让您将自定义 Python 类型发送到 SQLite。 但是为了让它真正有用，我们需要让 Python 到 SQLite 再到 Python 的往返工作。

输入转换器。

让我们回到 Point 类。 我们将通过分号分隔的 x 和 y 坐标存储为 SQLite 中的字符串。

首先，我们将定义一个转换器函数，它接受字符串作为参数并从中构造一个 Point 对象。

笔记

转换器函数 总是 使用 bytes 对象调用，无论您将值发送到 SQLite 的数据类型是什么。

def convert_point(s):
    x, y = map(float, s.split(b";"))
    return Point(x, y)

现在你需要让 sqlite3 模块知道你从数据库中选择的实际上是一个点。 有两种方法可以做到这一点：

• 通过声明的类型隐式
• 显式通过列名

这两种方式都在 模块函数和常量 节中描述，在常量 PARSE_DECLTYPES 和 PARSE_COLNAMES 的条目中。

以下示例说明了这两种方法。

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return ("%f;%f" % (point.x, point.y)).encode('ascii')

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

默认适配器和转换器

datetime 模块中有用于日期和日期时间类型的默认适配器。 它们将作为 ISO 日期/ISO 时间戳发送到 SQLite。

默认转换器在 datetime.date 的名称为“date”，datetime.datetime 的名称为“timestamp”。

这样，在大多数情况下，您可以使用 Python 中的日期/时间戳，而无需任何额外的调整。 适配器的格式也与实验性 SQLite 日期/时间函数兼容。

以下示例演示了这一点。

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))

con.close()

如果存储在 SQLite 中的时间戳的小数部分超过 6 个数字，则时间戳转换器将其值截断为微秒精度。

笔记

默认的“时间戳”转换器忽略数据库中的 UTC 偏移量并始终返回一个简单的 datetime.datetime 对象。 要在时间戳中保留 UTC 偏移量，请禁用转换器，或使用 register_converter() 注册偏移量感知转换器。

控制交易

底层的 sqlite3 库默认以 autocommit 模式运行，但 Python sqlite3 模块默认不运行。

autocommit模式表示修改数据库的语句立即生效。 BEGIN 或 SAVEPOINT 语句禁用 autocommit 模式，COMMIT、ROLLBACK 或 RELEASE结束最外面的事务，重新打开 autocommit 模式。

默认情况下，Python sqlite3 模块在数据修改语言 (DML) 语句之前隐式发出 BEGIN 语句（即 INSERT/UPDATE/DELETE/REPLACE）。

您可以通过 connect() 调用的 isolation_level 参数或通过 isolation_level 连接属性。 如果不指定 isolation_level，则使用普通的 BEGIN，相当于指定 DEFERRED。 其他可能的值为 IMMEDIATE 和 EXCLUSIVE。

您可以通过将 isolation_level 设置为 None 来禁用 sqlite3 模块的隐式事务管理。 这将使底层的 sqlite3 库在 autocommit 模式下运行。 然后，您可以通过在代码中显式发出 BEGIN、ROLLBACK、SAVEPOINT 和 RELEASE 语句来完全控制事务状态。

注意 executescript() 忽略 isolation_level； 必须显式添加任何事务控制。

3.6 版更改： sqlite3 用于在 DDL 语句之前隐式提交打开的事务。 这已不再是这种情况。

有效地使用 sqlite3

使用快捷方式

使用 Connection 对象的非标准 execute()、executemany() 和 executescript() 方法，您的代码可以写得更简洁，因为您不必创建（通常是多余的） Cursor 对象显式。 相反，Cursor 对象是隐式创建的，这些快捷方法返回光标对象。 这样，您可以执行 SELECT 语句并直接使用 Connection 对象上的单个调用对其进行迭代。

import sqlite3

langs = [
    ("C++", 1985),
    ("Objective-C", 1984),
]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table lang(name, first_appeared)")

# Fill the table
con.executemany("insert into lang(name, first_appeared) values (?, ?)", langs)

# Print the table contents
for row in con.execute("select name, first_appeared from lang"):
    print(row)

print("I just deleted", con.execute("delete from lang").rowcount, "rows")

# close is not a shortcut method and it's not called automatically,
# so the connection object should be closed manually
con.close()

按名称而不是按索引访问列

sqlite3 模块的一个有用功能是内置的 sqlite3.Row 类，旨在用作行工厂。

可以通过索引（如元组）和不区分大小写的名称访问用此类包装的行：

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
    assert row[0] == row["name"]
    assert row["name"] == row["nAmE"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

con.close()

使用连接作为上下文管理器

连接对象可用作自动提交或回滚事务的上下文管理器。 发生异常时，事务回滚； 否则，事务被提交：

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table lang (id integer primary key, name varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into lang(name) values (?)", ("Python",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into lang(name) values (?)", ("Python",))
except sqlite3.IntegrityError:
    print("couldn't add Python twice")

# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually
con.close()

脚注

1(1,2)

默认情况下，sqlite3 模块没有使用可加载扩展支持构建，因为某些平台（尤其是 macOS）具有编译时没有此功能的 SQLite 库。 要获得可加载扩展支持，您必须通过 --enable-loadable-sqlite-extensions 进行配置。