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

Source code: Lib/sqlite3/

---

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. SQLite是一个C库，它提供了一个轻量级的基于磁盘的数据库，不需要单独的服务器进程，并允许使用SQL查询语言的非标准变体访问数据库。Some applications can use SQLite for internal data storage. 一些应用程序可以使用SQLite进行内部数据存储。It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.还可以使用SQLite对应用程序进行原型化，然后将代码移植到更大的数据库，如PostgreSQL或Oracle。

The sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.sqlite3模块由Gerhard Hring编写。它提供了符合PEP 249描述的DB-API 2.0规范的SQL接口，并且需要SQLite 3.7.15或更新版本。

To use the module, start by creating a Connection object that represents the database. 要使用该模块，首先创建一个表示数据库的Connection对象。Here the data will be stored in the example.db file:在此，数据将存储在example.db文件中：

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

The special path name :memory: can be provided to create a temporary database in RAM.可以提供特殊路径名:memory:来在RAM中创建临时数据库。

Once a Connection has been established, create a Cursor object and call its execute() method to perform SQL commands:建立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()

The saved data is persistent: it can be reloaded in a subsequent session even after restarting the Python interpreter:保存的数据是持久的：即使在重新启动Python解释器后，也可以在后续会话中重新加载：

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

To retrieve data after executing a SELECT statement, either treat the cursor as an iterator, call the cursor’s fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.要在执行SELECT语句后检索数据，可以将游标视为迭代器，调用游标的fetchone()方法来检索单个匹配行，或者调用fetchall()来获取匹配行的列表。

This example uses the iterator form:本例使用迭代器形式：

>>> 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 operations usually need to use values from Python variables. SQL操作通常需要使用Python变量中的值。However, beware of using Python’s string operations to assemble queries, as they are vulnerable to SQL injection attacks (see the xkcd webcomic for a humorous example of what can go wrong):但是，请注意使用Python的字符串操作来组装查询，因为它们容易受到SQL注入攻击（请参阅xkcd webcomic，了解可能出错的幽默示例）：

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

Instead, use the DB-API’s parameter substitution. 相反，使用DB-API的参数替换。To insert a variable into a query string, use a placeholder in the string, and substitute the actual values into the query by providing them as a tuple of values to the second argument of the cursor’s execute() method. 要将变量插入查询字符串，请在字符串中使用占位符，并通过将实际值作为值的tuple提供给游标的execute()方法的第二个参数来替换查询中的实际值。An SQL statement may use one of two kinds of placeholders: question marks (qmark style) or named placeholders (named style). SQL语句可以使用两种占位符之一：问号（qmark样式）或命名占位符（命名样式）。For the qmark style, parameters must be a sequence. 对于qmark样式，parameters必须是序列。For the named style, it can be either a sequence or dict instance. 对于命名样式，它可以是序列或dict实例。The length of the sequence must match the number of placeholders, or a ProgrammingError is raised. 序列的长度必须与占位符的数量匹配，否则会引发ProgrammingError。If a dict is given, it must contain keys for all named parameters. 如果给定dict，则它必须包含所有命名参数的键。Any extra items are ignored. Here’s an example of both styles:忽略任何额外项目。以下是两种风格的示例：

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

See also

https://www.sqlite.org

The SQLite web page; the documentation describes the syntax and the available data types for the supported SQL dialect.SQLite网页；文档描述了支持的SQL方言的语法和可用数据类型。

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

Tutorial, reference and examples for learning SQL syntax.学习SQL语法的教程、参考和示例。

PEP 249 - Database API Specification 2.0数据库API规范2.0

PEP written by Marc-André Lemburg.政治公众人物由马克·安德尔·伦伯格撰写。

Module functions and constants模块函数和常数

sqlite3.apilevel

String constant stating the supported DB-API level. 表示支持的DB-API级别的字符串常量。Required by the DB-API. DB-API要求。Hard-coded to "2.0".硬编码为"2.0"。

sqlite3.paramstyle

String constant stating the type of parameter marker formatting expected by the sqlite3 module. 字符串常量，说明sqlite3模块所需的参数标记格式的类型。Required by the DB-API. Hard-coded to "qmark".DB-API要求。硬编码为"qmark"。

Note

The sqlite3 module supports both qmark and numeric DB-API parameter styles, because that is what the underlying SQLite library supports. sqlite3模块支持qmark和numericDB-API参数样式，因为这是基础SQLite库所支持的。However, the DB-API does not allow multiple values for the paramstyle attribute.但是，DB-API不允许paramstyle属性具有多个值。

sqlite3.version

The version number of this module, as a string. 此模块的版本号，作为字符串。This is not the version of the SQLite library.这不是SQLite库的版本。

sqlite3.version_info

The version number of this module, as a tuple of integers. 此模块的版本号，作为整数元组。This is not the version of the SQLite library.这不是SQLite库的版本。

sqlite3.sqlite_version

The version number of the run-time SQLite library, as a string.运行时SQLite库的版本号，以字符串形式表示。

sqlite3.sqlite_version_info

The version number of the run-time SQLite library, as a tuple of integers.运行时SQLite库的版本号，作为整数元组。

sqlite3.threadsafety

Integer constant required by the DB-API, stating the level of thread safety the sqlite3 module supports. DB-API所需的整数常量，表示sqlite3模块支持的线程安全级别。Currently hard-coded to 1, meaning “Threads may share the module, but not connections.” However, this may not always be true. 当前硬编码为1，表示“线程可以共享模块，但不能共享连接”然而，这可能并不总是正确的。You can check the underlying SQLite library’s compile-time threaded mode using the following query:您可以使用以下查询检查底层SQLite库的编译时线程模式：

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

Note that the SQLITE_THREADSAFE levels do not match the DB-API 2.0 threadsafety levels.请注意，SQLITE_THREADSAFE级别与DB-API 2.0threadsafety级别不匹配。

sqlite3.PARSE_DECLTYPES

This constant is meant to be used with the detect_types parameter of the connect() function.此常量用于connect()函数的detect_types参数。

Setting it makes the sqlite3 module parse the declared type for each column it returns. 设置它使sqlite3模块为其返回的每个列解析声明的类型。It will parse out the first word of the declared type, i. e. for “integer primary key”, it will parse out “integer”, or for “number(10)” it will parse out “number”. 它将解析出声明类型的第一个字，即对于“整数主键”，它将解析出“整数”，或者对于“数字（10）”，它将解析出“数字”。Then for that column, it will look into the converters dictionary and use the converter function registered for that type there.然后，对于该列，它将查看转换器字典，并使用为该类型注册的转换器函数。

sqlite3.PARSE_COLNAMES

This constant is meant to be used with the detect_types parameter of the connect() function.此常量用于connect()函数的detect_types参数。

Setting this makes the SQLite interface parse the column name for each column it returns. 设置此选项后，SQLite接口将解析它返回的每个列的列名。It will look for a string formed [mytype] in there, and then decide that ‘mytype’ is the type of the column. 它将在其中查找形成[mytype]的字符串，然后确定“mytype”是列的类型。It will try to find an entry of ‘mytype’ in the converters dictionary and then use the converter function found there to return the value. 它将尝试在转换器字典中查找“mytype”条目，然后使用在其中找到的转换器函数返回值。The column name found in Cursor.description does not include the type, i. e. if you use something like 'as "Expiration date [datetime]"' in your SQL, then we will parse out everything until the first '[' for the column name and strip the preceding space: the column name would simply be “Expiration date”.Cursor.description中的列名不包含类型，即如果在SQL中使用类似'as "Expiration date [datetime]"'，那么我们将解析所有内容，直到列名的第一个'['，并去掉前面的空格：列名将只是“Expiration date”。

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

Opens a connection to the SQLite database file database. 打开到SQLite数据库文件database的连接。By default returns a Connection object, unless a custom factory is given.默认情况下，除非给定自定义工厂，否则返回Connection对象。

database is a path-like object giving the pathname (absolute or relative to the current working directory) of the database file to be opened. 是一个类似路径的对象，提供要打开的数据库文件的路径名（绝对或相对于当前工作目录）。You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk.您可以使用":memory:"打开与驻留在RAM中而不是磁盘上的数据库的数据库连接。

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. 当通过多个连接访问一个数据库，并且其中一个进程修改了该数据库时，SQLite数据库将被锁定，直到提交该事务。The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. timeout参数指定连接在引发异常之前应等待锁消失的时间。The default for the timeout parameter is 5.0 (five seconds).超时参数的默认值为5.0（五秒）。

For the isolation_level parameter, please see the isolation_level property of Connection objects.有关isolation_level参数，请参阅Connection对象的isolation_level属性。

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. SQLite本机仅支持类型TEXT、INTEGER、REAL、BLOB和NULL。If you want to use other types you must add support for them yourself. 如果您想使用其他类型，您必须自己添加对它们的支持。The detect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.detect_types参数和使用在模块级register_converter()函数中注册的自定义转换器，使您可以轻松做到这一点。

detect_types defaults to 0 (i. e. off, no type detection), you can set it to any combination of PARSE_DECLTYPES and PARSE_COLNAMES to turn type detection on. 默认值为0（即关闭，无类型检测），您可以将其设置为PARSE_DECLTYPES和PARSE_COLNAMES的任意组合以打开类型检测。Due to SQLite behaviour, types can’t be detected for generated fields (for example max(data)), even when detect_types parameter is set. 由于SQLite行为，即使设置了detect_types参数，也无法检测生成字段的类型（例如max(data)）。In such case, the returned type is str.在这种情况下，返回的类型是str。

By default, check_same_thread is True and only the creating thread may use the connection. 默认情况下，check_same_thread为True，只有创建线程可以使用该连接。If set False, the returned connection may be shared across multiple threads. 如果设置为False，则返回的连接可能会在多个线程之间共享。When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.当使用具有相同连接的多个线程时，用户应序列化写入操作，以避免数据损坏。

By default, the sqlite3 module uses its Connection class for the connect call. 默认情况下，sqlite3模块使用其Connection类进行连接调用。You can, however, subclass the Connection class and make connect() use your class instead by providing your class for the factory parameter.但是，您可以将Connection类子类化，并通过为factory参数提供类来让connect()使用您的类。

Consult the section SQLite and Python types of this manual for details.有关详细信息，请参阅本手册的SQLite和Python类型部分。

The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. sqlite3模块在内部使用语句缓存来避免SQL解析开销。If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. 如果要显式设置为连接缓存的语句数，可以设置cached_statements参数。The currently implemented default is to cache 100 statements.当前实现的默认值是缓存100条语句。

If uri is True, database is interpreted as a URI with a file path and an optional query string. 若uri为True，则数据库被解释为具有文件路径和可选查询字符串的URI。The scheme part must be "file:". 方案部分必须是"file:"。The path can be a relative or absolute file path. 路径可以是相对或绝对文件路径。The query string allows us to pass parameters to SQLite. 查询字符串允许我们将参数传递给SQLite。Some useful URI tricks include:一些有用的URI技巧包括：

# Open a database in read-only mode.
con = sqlite3.connect("file:template.db?mode=ro", uri=True)
# Don't implicitly create a new database file if it does not already exist.
# Will raise sqlite3.OperationalError if unable to open a database file.
con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)

# Create a shared named in-memory database.
con1 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
con2 = sqlite3.connect("file:mem1?mode=memory&cache=shared", uri=True)
con1.executescript("create table t(t); insert into t values(28);")
rows = con2.execute("select * from t").fetchall()

More information about this feature, including a list of recognized parameters, can be found in the SQLite URI documentation.有关此功能的更多信息，包括已识别参数的列表，可以在SQLite URI文档中找到。

Raises an auditing event sqlite3.connect with argument database.使用参数database引发审核事件sqlite3.connect。

Raises an auditing event sqlite3.connect/handle with argument connection_handle.使用参数connection_handle引发审核事件sqlite3.connect/handle。

Changed in version 3.4:版本3.4中更改： Added the uri parameter.添加了uri参数。

Changed in version 3.7:版本3.7中更改： database can now also be a path-like object, not only a string.database现在也可以是类似路径的对象，而不仅仅是字符串。

Changed in version 3.10:版本3.10中更改： Added the sqlite3.connect/handle auditing event.添加了sqlite3.connect/handle审核事件。

sqlite3.register_converter(typename, callable)

Registers a callable to convert a bytestring from the database into a custom Python type. 注册一个可调用函数，将数据库中的ByTestString转换为自定义Python类型。The callable will be invoked for all database values that are of the type typename. 将为类型为typename的所有数据库值调用可调用项。Confer the parameter detect_types of the connect() function for how the type detection works. 为connect()函数的参数detect_types提供类型检测的工作方式。Note that typename and the name of the type in your query are matched in case-insensitive manner.请注意，查询中的typename和类型名是以不区分大小写的方式匹配的。

sqlite3.register_adapter(type, callable)

Registers a callable to convert the custom Python type type into one of SQLite’s supported types. 注册一个可调用函数，将自定义Python类型type转换为SQLite支持的类型之一。The callable callable accepts as single parameter the Python value, and must return a value of the following types: int, float, str or bytes.可调用callable接受Python值作为单个参数，并且必须返回以下类型的值：int、float、str或bytes。

sqlite3.complete_statement(sql)

Returns True if the string sql contains one or more complete SQL statements terminated by semicolons. 如果字符串sql包含一个或多个以分号结尾的完整sql语句，则返回True。It does not verify that the SQL is syntactically correct, only that there are no unclosed string literals and the statement is terminated by a semicolon.它不会验证SQL的语法是否正确，只是没有未闭合的字符串文字，并且语句以分号结束。

This can be used to build a shell for SQLite, as in the following example:这可以用于为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)

By default you will not get any tracebacks in user-defined functions, aggregates, converters, authorizer callbacks etc. 默认情况下，您不会在用户定义函数、聚合、转换器、授权程序回调等中获得任何回溯。If you want to debug them, you can call this function with flag set to True. 如果要调试它们，可以使用设置为True的flag调用此函数。Afterwards, you will get tracebacks from callbacks on sys.stderr. 然后，您将从sys.stderr上的回调中获得回溯。Use False to disable the feature again.使用False再次禁用该功能。

Connection Objects对象

classsqlite3.Connection

An SQLite database connection has the following attributes and methods:SQLite数据库连接具有以下属性和方法：

isolation_level

Get or set the current default isolation level. 获取或设置当前默认隔离级别。None for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. None用于自动提交模式或“DEFERRED”、“IMMEDIATE”或“EXCLUSIVE”之一。See section Controlling Transactions for a more detailed explanation.有关更详细的说明，请参阅控制交易一节。

in_transaction

True if a transaction is active (there are uncommitted changes), False otherwise. 如果事务处于活动状态（有未提交的更改），则为True，否则为False。Read-only attribute.只读属性。

New in version 3.2.版本3.2中新增。

cursor(factory=Cursor)

The cursor method accepts a single optional parameter factory. 游标方法接受单个可选参数factory。If supplied, this must be a callable returning an instance of Cursor or its subclasses.如果提供，这必须是一个可调用的，返回Cursor或其子类的实例。

commit()

This method commits the current transaction. 此方法提交当前事务。If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. 如果不调用此方法，则自上次调用commit()以来所做的任何操作都无法从其他数据库连接中看到。If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.如果您想知道为什么没有看到写入数据库的数据，请检查您是否忘记调用此方法。

rollback()

This method rolls back any changes to the database since the last call to commit().此方法回滚自上次调用commit()以来对数据库的任何更改。

close()

This closes the database connection. 这将关闭数据库连接。Note that this does not automatically call commit(). 请注意，这不会自动调用commit()。If you just close your database connection without calling commit() first, your changes will be lost!如果只关闭数据库连接而不首先调用commit()，则更改将丢失！

execute(sql[, parameters])

Create a new Cursor object and call execute() on it with the given sql and parameters. 创建一个新的Cursor对象，并使用给定的sql和parameters对其调用execute()。Return the new cursor object.返回新的Cursor对象。

executemany(sql[, parameters])

Create a new Cursor object and call executemany() on it with the given sql and parameters. 创建一个新的Cursor对象，并使用给定的sql和parameters对其调用executemany()。Return the new cursor object.返回新的Cursor对象。

executescript(sql_script)

Create a new Cursor object and call executescript() on it with the given sql_script. 创建一个新的Cursor对象，并使用给定的sql_script对其调用executescript()。Return the new cursor object.返回新的Cursor对象。

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

Creates a user-defined function that you can later use from within SQL statements under the function name name. 创建一个用户定义函数，以后可以从函数名name下的SQL语句中使用该函数。num_params is the number of parameters the function accepts (if num_params is -1, the function may take any number of arguments), and func is a Python callable that is called as the SQL function. num_params是函数接受的参数数（如果num_pparams为-1，则函数可以接受任意数量的参数），func是一个Python可调用函数，称为SQL函数。If deterministic is true, the created function is marked as deterministic, which allows SQLite to perform additional optimizations. 如果deterministic为true，则创建的函数被标记为确定性，这允许SQLite执行其他优化。This flag is supported by SQLite 3.8.3 or higher, NotSupportedError will be raised if used with older versions.SQLite 3.8.3或更高版本支持此标志，如果与旧版本一起使用，将引发NotSupportedError。

The function can return any of the types supported by SQLite: bytes, str, int, float and None.该函数可以返回SQLite支持的任何类型：bytes、str、int、float和None。

Changed in version 3.8:版本3.8中更改： The deterministic parameter was added.添加了deterministic参数。

Example:示例：

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)

Creates a user-defined aggregate function.创建用户定义的聚合函数。

The aggregate class must implement a step method, which accepts the number of parameters num_params (if num_params is -1, the function may take any number of arguments), and a finalize method which will return the final result of the aggregate.聚合类必须实现一个step方法，该方法接受参数num_params的数量（如果num_pparams为-1，则函数可以接受任意数量的参数），以及一个finalize方法，该法将返回聚合的最终结果。

The finalize method can return any of the types supported by SQLite: bytes, str, int, float and None.finalize方法可以返回SQLite支持的任何类型：bytes、str、int、float和None。

Example:示例：

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)

Creates a collation with the specified name and callable. 创建具有指定name和callable的排序规则。The callable will be passed two string arguments. 可调用函数将传递两个字符串参数。It should return -1 if the first is ordered lower than the second, 0 if they are ordered equal and 1 if the first is ordered higher than the second. 如果第一个的顺序低于第二个，则返回-1；如果它们的顺序相等，则返回0；如果第一个顺序高于第二个顺序，则返回1。Note that this controls sorting (ORDER BY in SQL) so your comparisons don’t affect other SQL operations.请注意，这控制排序（SQL中的ORDER BY），因此比较不会影响其他SQL操作。

Note that the callable will get its parameters as Python bytestrings, which will normally be encoded in UTF-8.请注意，可调用函数将其参数作为Python字节字符串，通常用UTF-8编码。

The following example shows a custom collation that sorts “the wrong way”:以下示例显示了一个“错误”排序的自定义排序规则：

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

To remove a collation, call create_collation with None as callable:要删除排序规则，请将create_collation与None一起调用为可调用：

con.create_collation("reverse", None)

interrupt()

You can call this method from a different thread to abort any queries that might be executing on the connection. 您可以从其他线程调用此方法，以中止可能正在连接上执行的任何查询。The query will then abort and the caller will get an exception.然后查询将中止，调用者将得到一个异常。

set_authorizer(authorizer_callback)

This routine registers a callback. 此例程注册回调。The callback is invoked for each attempt to access a column of a table in the database. 每次尝试访问数据库中表的列时都会调用回调。The callback should return SQLITE_OK if access is allowed, SQLITE_DENY if the entire SQL statement should be aborted with an error and SQLITE_IGNORE if the column should be treated as a NULL value. 如果允许访问，回调应返回SQLITE_OK；如果整个SQL语句因错误而中止，则返回SQLITE_DENY；如果列应被视为NULL值，则返回SQL ITE_IGNORE。These constants are available in the sqlite3 module.sqlite3模块中提供了这些常量。

The first argument to the callback signifies what kind of operation is to be authorized. 回调的第一个参数表示要授权的操作类型。The second and third argument will be arguments or None depending on the first argument. 第二个和第三个参数将是arguments或None，具体取决于第一个参数。The 4th argument is the name of the database (“main”, “temp”, etc.) if applicable. 第四个参数是数据库的名称（“main”、“temp”等）（如果适用）。The 5th argument is the name of the inner-most trigger or view that is responsible for the access attempt or None if this access attempt is directly from input SQL code.第5个参数是负责访问尝试的最内部触发器或视图的名称，如果此访问尝试直接来自输入SQL代码，则为None。

Please consult the SQLite documentation about the possible values for the first argument and the meaning of the second and third argument depending on the first one. 请参考SQLite文档，了解第一个参数的可能值以及第二个和第三个参数的含义（取决于第一个参数）。All necessary constants are available in the sqlite3 module.sqlite3模块中提供了所有必需的常量。

set_progress_handler(handler, n)

This routine registers a callback. 此例程注册回调。The callback is invoked for every n instructions of the SQLite virtual machine. 对SQLite虚拟机的每n条指令调用回调。This is useful if you want to get called from SQLite during long-running operations, for example to update a GUI.如果您希望在长时间运行的操作期间从SQLite调用，例如更新GUI，这将非常有用。

If you want to clear any previously installed progress handler, call the method with None for handler.如果要清除以前安装的任何进度处理程序，请使用None为handler调用方法。

Returning a non-zero value from the handler function will terminate the currently executing query and cause it to raise an OperationalError exception.从处理程序函数返回非零值将终止当前执行的查询，并导致它引发OperationalError异常。

set_trace_callback(trace_callback)

Registers trace_callback to be called for each SQL statement that is actually executed by the SQLite backend.注册要为SQLite后端实际执行的每个SQL语句调用的trace_callback。

The only argument passed to the callback is the statement (as str) that is being executed. 传递给回调的唯一参数是正在执行的语句（str）。The return value of the callback is ignored. 回调的返回值被忽略。Note that the backend does not only run statements passed to the Cursor.execute() methods. 请注意，后端不仅运行传递给Cursor.execute()方法的语句。Other sources include the transaction management of the sqlite3 module and the execution of triggers defined in the current database.其他来源包括sqlite3模块的事务管理和当前数据库中定义的触发器的执行。

Passing None as trace_callback will disable the trace callback.将None作为trace_callback传递将禁用跟踪回调。

Note

Exceptions raised in the trace callback are not propagated. 不会传播跟踪回调中引发的异常。As a development and debugging aid, use enable_callback_tracebacks() to enable printing tracebacks from exceptions raised in the trace callback.作为开发和调试的辅助工具，使用enable_callback_tracebacks()可以从跟踪回调中引发的异常打印回溯。

New in version 3.3.版本3.3中新增。

enable_load_extension(enabled)

This routine allows/disallows the SQLite engine to load SQLite extensions from shared libraries. 此例程允许/禁止SQLite引擎从共享库加载SQLite扩展。SQLite extensions can define new functions, aggregates or whole new virtual table implementations. SQLite扩展可以定义新的函数、聚合或全新的虚拟表实现。One well-known extension is the fulltext-search extension distributed with SQLite.一个著名的扩展是SQLite发布的全文搜索扩展。

Loadable extensions are disabled by default. 默认情况下禁用可加载扩展。See 1.请参阅1。

Raises an auditing event sqlite3.enable_load_extension with arguments connection, enabled.使用参数connection、enabled引发审计事件sqlite3.enable_load_extension。

New in version 3.2.版本3.2中新增。

Changed in version 3.10:版本3.10中更改： Added the sqlite3.enable_load_extension auditing event.添加了sqlite3.enable_load_extension审核事件。

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)

This routine loads an SQLite extension from a shared library. 此例程从共享库加载SQLite扩展。You have to enable extension loading with enable_load_extension() before you can use this routine.在使用此例程之前，必须使用enable_load_extension()启用扩展加载。

Loadable extensions are disabled by default. 默认情况下禁用可加载扩展。See 1.请参阅1。

Raises an auditing event sqlite3.load_extension with arguments connection, path.使用connection、path参数引发审核事件sqlite3.load_extension。

New in version 3.2.版本3.2中新增。

Changed in version 3.10:版本3.10中更改： Added the sqlite3.load_extension auditing event.添加了sqlite3.load_extension审核事件。

row_factory

You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row. 您可以将此属性更改为可调用属性，该属性接受光标和原始行作为元组，并将返回实际结果行。This way, you can implement more advanced ways of returning results, such as returning an object that can also access columns by name.通过这种方式，您可以实现更高级的返回结果的方法，例如返回也可以按名称访问列的对象。

Example:示例：

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

If returning a tuple doesn’t suffice and you want name-based access to columns, you should consider setting row_factory to the highly-optimized sqlite3.Row type. 如果返回元组还不够，并且您希望基于名称访问列，则应考虑将row_factory设置为高度优化的sqlite3.Row类型。Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead. 提供基于索引和不区分大小写的基于名称的列访问，几乎没有内存开销。It will probably be better than your own custom dictionary-based approach or even a db_row based solution.它可能比您自己的基于词典的自定义方法甚至基于db_row的解决方案更好。

text_factory

Using this attribute you can control what objects are returned for the TEXT data type. 使用此属性，可以控制为TEXT数据类型返回哪些对象。By default, this attribute is set to str and the sqlite3 module will return str objects for TEXT. 默认情况下，该属性设置为str，sqlite3模块将为TEXT返回str对象。If you want to return bytes instead, you can set it to bytes.如果要改为返回字节，可以将其设置为bytes。

You can also set it to any other callable that accepts a single bytestring parameter and returns the resulting object.您还可以将其设置为接受单个字节字符串参数并返回结果对象的任何其他可调用项。

See the following example code for illustration:请参见以下示例代码以进行说明：

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

Returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.返回自数据库连接打开以来已修改、插入或删除的数据库行总数。

iterdump()

Returns an iterator to dump the database in an SQL text format. 返回一个迭代器以SQL文本格式转储数据库。Useful when saving an in-memory database for later restoration. 在保存内存中的数据库以便以后恢复时非常有用。This function provides the same capabilities as the .dump command in the sqlite3 shell.此函数提供的功能与sqlite3 shell中的.dump命令相同。

Example:示例：

# 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.25)

This method makes a backup of an SQLite database even while it’s being accessed by other clients, or concurrently by the same connection. 此方法可以备份SQLite数据库，即使其他客户机正在访问它，或者通过同一连接同时访问它。The copy will be written into the mandatory argument target, that must be another Connection instance.副本将写入强制参数target，该target必须是另一个Connection实例。

By default, or when pages is either 0 or a negative integer, the entire database is copied in a single step; otherwise the method performs a loop copying up to pages pages at a time.默认情况下，或者当pages为0或负整数时，只需一步即可复制整个数据库；否则，该方法执行循环复制，每次最多复制个页面。

If progress is specified, it must either be None or a callable object that will be executed at each iteration with three integer arguments, respectively the status of the last iteration, the remaining number of pages still to be copied and the total number of pages.如果指定了progress，则必须是None或一个可调用对象，该对象将在每次迭代时使用三个整数参数执行，分别是最后一次迭代的status、仍要复制的剩余页数remaining和总页数total。

The name argument specifies the database name that will be copied: it must be a string containing either "main", the default, to indicate the main database, "temp" to indicate the temporary database or the name specified after the AS keyword in an ATTACH DATABASE statement for an attached database.name参数指定要复制的数据库名称：它必须是一个字符串，其中包含默认值“"main"，表示主数据库；"temp"，表示临时数据库；或者在ATTACH DATABASE语句中，在附加数据库的AS关键字后指定名称。

The sleep argument specifies the number of seconds to sleep by between successive attempts to backup remaining pages, can be specified either as an integer or a floating point value.sleep参数指定在连续尝试备份剩余页面之间休眠的秒数，可以指定为整数或浮点值。

Example 1, copy an existing database into another:示例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()

Example 2, copy an existing database into a transient copy:示例2，将现有数据库复制到临时副本中：

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

New in version 3.7.版本3.7中新增。

Cursor Objects对象

classsqlite3.Cursor

A Cursor instance has the following attributes and methods.Cursor实例具有以下属性和方法。

execute(sql[, parameters])

Executes an SQL statement. 执行SQL语句。Values may be bound to the statement using placeholders.可以使用占位符将值绑定到语句。

execute() will only execute a single SQL statement. 将只执行单个SQL语句。If you try to execute more than one statement with it, it will raise a Warning. 如果您试图用它执行多条语句，它将引发Warning。Use executescript() if you want to execute multiple SQL statements with one call.如果要用一个调用执行多个SQL语句，请使用executescript()。

executemany(sql, seq_of_parameters)

Executes a parameterized SQL command against all parameter sequences or mappings found in the sequence seq_of_parameters. 对序列seq_of_parameters中找到的所有参数序列或映射执行参数化SQL命令。The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.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()

Here’s a shorter example using a generator:下面是一个使用生成器的简短示例：

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)

This is a nonstandard convenience method for executing multiple SQL statements at once. 这是一种非标准的方便方法，用于一次执行多个SQL语句。It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. 它首先发出COMMIT语句，然后执行作为参数获取的SQL脚本。This method disregards isolation_level; any transaction control must be added to sql_script.此方法忽略isolation_level；任何事务控制都必须添加到sql_script中。

sql_script can be an instance of str.可以是str的实例。

Example:示例：

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

Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.获取查询结果集的下一行，返回单个序列；如果没有更多数据可用，则返回None。

fetchmany(size=cursor.arraysize)

Fetches the next set of rows of a query result, returning a list. 获取查询结果的下一组行，并返回列表。An empty list is returned when no more rows are available.当没有更多行可用时，将返回空列表。

The number of rows to fetch per call is specified by the size parameter. 每次调用要提取的行数由size参数指定。If it is not given, the cursor’s arraysize determines the number of rows to be fetched. 如果没有给出，游标的arraysize将确定要提取的行数。The method should try to fetch as many rows as indicated by the size parameter. 该方法应尝试获取大小参数指示的尽可能多的行。If this is not possible due to the specified number of rows not being available, fewer rows may be returned.如果由于指定的行数不可用而无法执行此操作，则可能会返回较少的行。

Note there are performance considerations involved with the size parameter. 请注意，size参数涉及性能方面的考虑。For optimal performance, it is usually best to use the arraysize attribute. 为了获得最佳性能，通常最好使用arraysize属性。If the size parameter is used, then it is best for it to retain the same value from one fetchmany() call to the next.如果使用了size参数，那么它最好在一次fetchmany()调用到下一次调用时保留相同的值。

fetchall()

Fetches all (remaining) rows of a query result, returning a list. 获取查询结果的所有（剩余）行，并返回列表。Note that the cursor’s arraysize attribute can affect the performance of this operation. 请注意，游标的arraysize属性可能会影响此操作的性能。An empty list is returned when no rows are available.当没有可用行时，将返回空列表。

close()

Close the cursor now (rather than whenever __del__ is called).立即关闭光标（而不是每次调用__del__时）。

The cursor will be unusable from this point forward; a ProgrammingError exception will be raised if any operation is attempted with the cursor.此时光标将无法使用；如果使用光标尝试任何操作，将引发ProgrammingError异常。

setinputsizes(sizes)

Required by the DB-API. DB-API要求。Does nothing in sqlite3.在sqlite3中不执行任何操作。

setoutputsize(size[, column])

Required by the DB-API. DB-API要求。Does nothing in sqlite3.在sqlite3中不执行任何操作。

rowcount

Although the Cursor class of the sqlite3 module implements this attribute, the database engine’s own support for the determination of “rows affected”/”rows selected” is quirky.尽管sqlite3模块的Cursor类实现了此属性，但数据库引擎本身对确定“受影响的行”/“选定的行”的支持很奇怪。

For executemany() statements, the number of modifications are summed up into rowcount.对于executemany()语句，修改次数总计为行数。

As required by the Python DB API Spec, the rowcount attribute “is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. 根据Python DB API Spec的要求，rowcount属性“在游标上未执行executeXX()或接口无法确定最后一个操作的rowcoount时为-1”。This includes SELECT statements because we cannot determine the number of rows a query produced until all rows were fetched.这包括SELECT语句，因为在获取所有行之前，我们无法确定查询生成的行数。

lastrowid

This read-only attribute provides the row id of the last inserted row. 此只读属性提供最后插入的行的行id。It is only updated after successful INSERT or REPLACE statements using the execute() method. 它仅在使用execute()方法成功执行INSERT或REPLACE语句后更新。For other statements, after executemany() or executescript(), or if the insertion failed, the value of lastrowid is left unchanged. 对于其他语句，在executemany()或executescript()之后，或者如果插入失败，lastrowid的值保持不变。The initial value of lastrowid is None.lastrowid的初始值为None。

Note

Inserts into WITHOUT ROWID tables are not recorded.不记录WITHOUT ROWID表格中的插入内容。

Changed in version 3.6:版本3.6中更改： Added support for the REPLACE statement.添加了对REPLACE语句的支持。

arraysize

Read/write attribute that controls the number of rows returned by fetchmany(). 读/写属性，用于控制fetchmany()返回的行数。The default value is 1 which means a single row would be fetched per call.默认值为1，这意味着每次调用都会提取一行。

description

This read-only attribute provides the column names of the last query. 此只读属性提供最后一个查询的列名。To remain compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are None.为了与Python DB API保持兼容，它为每个元组的最后六项为None的列返回一个7元组。

It is set for SELECT statements without any matching rows as well.它是为没有任何匹配行的SELECT语句设置的。

connection

This read-only attribute provides the SQLite database Connection used by the Cursor object. 此只读属性提供Cursor对象使用的SQLite数据库Connection。A Cursor object created by calling con.cursor() will have a connection attribute that refers to con:通过调用con.cursor()创建的Cursor对象将具有引用con的connection属性：

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

Row Objects对象

classsqlite3.Row

A Row instance serves as a highly optimized row_factory for Connection objects. len()实例用作Connection对象的高度优化的row_factory。It tries to mimic a tuple in most of its features.它试图在大多数功能中模拟元组。

It supports mapping access by column name and index, iteration, representation, equality testing and len().它支持按列名和索引、迭代、表示、相等测试和len()进行映射访问。

If two Row objects have exactly the same columns and their members are equal, they compare equal.如果两个Row对象具有完全相同的列并且其成员相等，则它们比较起来相等。

keys()

This method returns a list of column names. 此方法返回列名列表。Immediately after a query, it is the first member of each tuple in Cursor.description.在查询之后，它是Cursor.description中每个元组的第一个成员。

Changed in version 3.5:版本3.5中更改： Added support of slicing.添加了切片支持。

Let’s assume we initialize a table as in the example given above:假设我们按照上面给出的示例初始化一个表：

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

Now we plug Row in:现在我们插入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

Exceptions异常

exceptionsqlite3.Warning

A subclass of Exception.Exception的子类。

exceptionsqlite3.Error

The base class of the other exceptions in this module. 此模块中其他异常的基类。It is a subclass of Exception.它是Exception的子类。

exceptionsqlite3.DatabaseError

Exception raised for errors that are related to the database.针对与数据库相关的错误引发异常。

exceptionsqlite3.IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. 当数据库的关系完整性受到影响时引发异常，例如，外键检查失败。It is a subclass of DatabaseError.它是DatabaseError的子类。

exceptionsqlite3.ProgrammingError

Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. 由于编程错误而引发的异常，例如表未找到或已存在、SQL语句中的语法错误、指定的参数数目错误等。It is a subclass of DatabaseError.它是DatabaseError的子类。

exceptionsqlite3.OperationalError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, etc. 针对与数据库操作相关且不一定受程序员控制的错误引发的异常，例如发生意外断开连接、找不到数据源名称、无法处理事务等。It is a subclass of DatabaseError.它是DatabaseError的子类。

exceptionsqlite3.NotSupportedError

Exception raised in case a method or database API was used which is not supported by the database, e.g. calling the rollback() method on a connection that does not support transaction or has transactions turned off. 在使用数据库不支持的方法或数据库API时引发异常，例如，在不支持事务或已关闭事务的连接上调用rollback()方法。It is a subclass of DatabaseError.它是DatabaseError的子类。

SQLite and Python typesSQLite和Python类型

Introduction介绍

SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB.SQLite本身支持以下类型：NULL、INTEGER、REAL、TEXT、BLOB。

The following Python types can thus be sent to SQLite without any problem:因此，以下Python类型可以毫无问题地发送到SQLite：

Python type	SQLite type
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

This is how SQLite types are converted to Python types by default:默认情况下，SQLite类型转换为Python类型的方式如下：

SQLite type	Python type
NULL	None
INTEGER	int
REAL	float
TEXT	depends on text_factory, str by default
BLOB	bytes

The type system of the sqlite3 module is extensible in two ways: you can store additional Python types in an SQLite database via object adaptation, and you can let the sqlite3 module convert SQLite types to different Python types via converters.sqlite3模块的类型系统可以通过两种方式进行扩展：您可以通过对象自适应将其他Python类型存储在SQLite数据库中，您可以让sqlite3模板通过转换器将SQLite类型转换为不同的Pythons类型。

Using adapters to store additional Python types in SQLite databases使用适配器在SQLite数据库中存储其他Python类型

As described before, SQLite supports only a limited set of types natively. 如前所述，SQLite本机仅支持有限的一组类型。To use other Python types with SQLite, you must adapt them to one of the sqlite3 module’s supported types for SQLite: one of NoneType, int, float, str, bytes.要在SQLite中使用其他Python类型，必须将它们调整为sqlite3模块支持的SQLite类型之一：NoneType、int、float、str、bytes。

There are two ways to enable the sqlite3 module to adapt a custom Python type to one of the supported ones.有两种方法可以使sqlite3模块将自定义Python类型调整为支持的类型之一。

Letting your object adapt itself让对象自适应

This is a good approach if you write the class yourself. 如果你自己编写这个类，这是一个很好的方法。Let’s suppose you have a class like this:假设你有一个这样的类：

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

Now you want to store the point in a single SQLite column. 现在，您需要将点存储在单个SQLite列中。First you’ll have to choose one of the supported types to be used for representing the point. 首先，您必须选择一种支持的类型来表示点。Let’s just use str and separate the coordinates using a semicolon. 让我们使用str并用分号分隔坐标。Then you need to give your class a method __conform__(self, protocol) which must return the converted value. 然后您需要给类一个方法__conform__(self, protocol)，它必须返回转换后的值。The parameter protocol will be PrepareProtocol.参数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()

Registering an adapter callable注册可调用适配器

The other possibility is to create a function that converts the type to the string representation and register the function with register_adapter().另一种可能性是创建一个将类型转换为字符串表示形式的函数，并使用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()

The sqlite3 module has two default adapters for Python’s built-in datetime.date and datetime.datetime types. sqlite3模块有两个默认适配器，用于Python内置的datetime.date和datetime.datetime类型。Now let’s suppose we want to store datetime.datetime objects not in ISO representation, but as a Unix timestamp.现在假设我们不想以ISO表示存储datetime.datetime对象，而是作为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()

Converting SQLite values to custom Python types将SQLite值转换为自定义Python类型

Writing an adapter lets you send custom Python types to SQLite. 编写适配器可以将自定义Python类型发送到SQLite。But to make it really useful we need to make the Python to SQLite to Python roundtrip work.但为了使它真正有用，我们需要使Python-to-SQLite-to-Python往返工作。

Enter converters.输入转换器。

Let’s go back to the Point class. 让我们回到Point类。We stored the x and y coordinates separated via semicolons as strings in SQLite.我们将以分号分隔的x和y坐标作为字符串存储在SQLite中。

First, we’ll define a converter function that accepts the string as a parameter and constructs a Point object from it.首先，我们将定义一个转换器函数，该函数接受字符串作为参数，并从中构造Point对象。

Note

Converter functions always get called with a bytes object, no matter under which data type you sent the value to SQLite.无论您将值发送到SQLite的数据类型是什么，转换器函数始终是使用bytes对象来调用。

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

Now you need to make the sqlite3 module know that what you select from the database is actually a point. 现在，您需要让sqlite3模块知道您从数据库中选择的实际上是一个点。There are two ways of doing this:有两种方法可以做到这一点：

• Implicitly via the declared type通过声明的类型隐式地

• Explicitly via the column name通过列名显式地

Both ways are described in section Module functions and constants, in the entries for the constants PARSE_DECLTYPES and PARSE_COLNAMES.这两种方法都在模块函数和常量一节中描述，在常量PARSE_DECLTYPES和PARSE_COLNAMES的条目中。

The following example illustrates both approaches.以下示例说明了这两种方法。

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

Default adapters and converters默认适配器和转换器

There are default adapters for the date and datetime types in the datetime module. datetime模块中有日期和日期时间类型的默认适配器。They will be sent as ISO dates/ISO timestamps to SQLite.它们将作为ISO日期/ISO时间戳发送到SQLite。

The default converters are registered under the name “date” for datetime.date and under the name “timestamp” for datetime.datetime.默认转换器以datetime.date的“date”名称注册，datetime.datetime的“timestamp”名称注册。

This way, you can use date/timestamps from Python without any additional fiddling in most cases. 这样，在大多数情况下，您可以使用Python中的日期/时间戳，而无需进行任何额外的修改。The format of the adapters is also compatible with the experimental SQLite date/time functions.适配器的格式也与实验性SQLite日期/时间函数兼容。

The following example demonstrates this.下面的示例演示了这一点。

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

If a timestamp stored in SQLite has a fractional part longer than 6 numbers, its value will be truncated to microsecond precision by the timestamp converter.如果存储在SQLite中的时间戳的小数部分长于6个数字，则时间戳转换器会将其值截断为微秒精度。

Note

The default “timestamp” converter ignores UTC offsets in the database and always returns a naive datetime.datetime object. 默认的“timestamp”转换器忽略数据库中的UTC偏移量，并始终返回一个原始的datetime.datetime对象。To preserve UTC offsets in timestamps, either leave converters disabled, or register an offset-aware converter with register_converter().要在时间戳中保留UTC偏移量，请禁用转换器，或使用register_converter()注册可识别偏移量的转换器。

Controlling Transactions控制交易

The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not.默认情况下，底层sqlite3库以autocommit模式运行，但默认情况下Python sqlite3模块不会。

autocommit mode means that statements that modify the database take effect immediately. 模式意味着修改数据库的语句立即生效。A BEGIN or SAVEPOINT statement disables autocommit mode, and a COMMIT, a ROLLBACK, or a RELEASE that ends the outermost transaction, turns autocommit mode back on.BEGIN或SAVEPOINT语句禁用autocommit模式，而结束最外层事务的COMMIT、ROLLBACK或RELEASE会重新启用autocommit模式。

The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).默认情况下，Python sqlite3模块在数据修改语言（DML）语句（即INSERT/UPDATE/DELETE/REPACE）之前隐式发出BEGIN语句。

You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. 您可以通过connect()调用的isolation_level参数或connections的isolatation_levels属性控制sqlite3隐式执行哪种BEGIN语句。If you specify no isolation_level, a plain BEGIN is used, which is equivalent to specifying DEFERRED. 如果未指定isolation_level，则使用普通BEGIN，这相当于指定DEFERRED。Other possible values are IMMEDIATE and EXCLUSIVE.其他可能的值包括IMMEDIATE和EXCLUSIVE。

You can disable the sqlite3 module’s implicit transaction management by setting isolation_level to None. 通过将isolation_level设置为None，可以禁用sqlite3模块的隐式事务管理。This will leave the underlying sqlite3 library operating in autocommit mode. 这将使底层sqlite3库在autocommit模式下运行。You can then completely control the transaction state by explicitly issuing BEGIN, ROLLBACK, SAVEPOINT, and RELEASE statements in your code.然后，您可以通过在代码中显式发出BEGIN、ROLLBACK、SAVEPOINT和RELEASE语句来完全控制事务状态。

Note that executescript() disregards isolation_level; any transaction control must be added explicitly.请注意，executescript()忽略了isolation_level；任何事务控制都必须显式添加。

Changed in version 3.6:版本3.6中更改： sqlite3 used to implicitly commit an open transaction before DDL statements. 用于在DDL语句之前隐式提交打开的事务。This is no longer the case.现在已经不是这样了。

Using sqlite3 efficiently高效使用sqlite3

Using shortcut methods使用快捷方式

Using the nonstandard execute(), executemany() and executescript() methods of the Connection object, your code can be written more concisely because you don’t have to create the (often superfluous) Cursor objects explicitly. 使用Connection对象的非标准execute()、executemany()和executescript()方法，可以更简洁地编写代码，因为您不必显式创建（通常是多余的）Cursor对象。Instead, the Cursor objects are created implicitly and these shortcut methods return the cursor objects. 相反，Cursor对象是隐式创建的，这些快捷方式方法返回游标对象。This way, you can execute a SELECT statement and iterate over it directly using only a single call on the Connection object.这样，您就可以执行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()

Accessing columns by name instead of by index按名称而不是按索引访问列

One useful feature of the sqlite3 module is the built-in sqlite3.Row class designed to be used as a row factory.sqlite3模块的一个有用特性是内置的sqlite3.Row类，它被设计为用作行工厂。

Rows wrapped with this class can be accessed both by index (like tuples) and case-insensitively by name:用此类包装的行既可以通过索引（如元组）访问，也可以通过名称不区分大小写访问：

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

Using the connection as a context manager将连接用作上下文管理器

Connection objects can be used as context managers that automatically commit or rollback transactions. 连接对象可以用作上下文管理器，自动提交或回滚事务。In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:如果出现异常，事务将回滚；否则，将提交事务：

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

Footnotes

1(1,2)

The sqlite3 module is not built with loadable extension support by default, because some platforms (notably macOS) have SQLite libraries which are compiled without this feature. 默认情况下，sqlite3模块不支持可加载扩展，因为某些平台（尤其是macOS）具有SQLite库，而这些库在编译时没有此功能。To get loadable extension support, you must pass the --enable-loadable-sqlite-extensions option to configure.要获得可加载扩展支持，必须传递--enable-loadable-sqlite-extensions扩展选项进行配置。