12.6. `sqlite3` - SQLite数据库的DB-API 2.0接口¶

源代码： Lib / sqlite3 /

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

sqlite3模块由Gerhard Häring编写。它提供了一个SQL接口，该接口和PEP 249描述的DB-API 2.0规范兼容。

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

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

也可以使用特殊名称:memory:以在 RAM（内存）中创建数据库。

一旦有了一个Connection，就可以创建Cursor对象并调用其execute()方法来执行 SQL 命令：

c = conn.cursor()

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

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

# Save (commit) the changes
conn.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.
conn.close()

保存过的数据是持久的并在以后的会话中可用：

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

通常的SQL操作需要使用Python变量中的值。你不应该使用Python的字符串操作来组合查询，因为这样做是不安全的；它使你的程序容易受到SQL注入攻击（参见https://xkcd.com/327/，这是关于可能出错的幽默例子）。

相反，应该使用 DB API 参数替代。放置?作为占位符，只要您想要使用值，然后提供一个元组值作为光标的execute()方法的第二个参数。（其他的数据库模块可能使用不同的占位符，如%s或:1。)举个例子：

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

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

为了取回iterator语句的执行结果，你可以把游标cursor 当做是一个迭代器，通过调用游标的fetchone()方法来获取单行结果；或者通过调用fetchall()方法获取结果集列表。

此示例使用迭代器形式：

>>> for row in c.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)

请参见

https://github.com/ghaering/pysqlite: pysqlite网页 - sqlite3是在名称“pysqlite”外部开发的。
https://www.sqlite.org: SQLite网页；该文档描述了支持的SQL方言的语法和可用的数据类型。
http://www.w3schools.com/sql/: 学习SQL语法的教程，参考和示例。
PEP 249 - 数据库API规范2.0: PEP由Marc-AndréLemburg编写。

12.6.1. Module functions and constants¶

sqlite3.version¶: 该模块的字符串形式的版本号。这不是 SQLite 库的版本。

sqlite3.version_info¶: 该模块的整数元组形式的版本号。这不是 SQLite 库的版本。

sqlite3.sqlite_version¶: 运行期SQLite库的版本号，字符串形式。

sqlite3.sqlite_version_info¶: 运行期SQLite库的版本号，整数元组形式。

sqlite3.PARSE_DECLTYPES¶

该常量用于connect()函数的detect_types参数。

设置它使得sqlite3模块解析每个返回列的声明的类型。它将解析出声明的类型的第一个单词，比如，"integer primary key"，它将解析出"integer"，而"number(10)"，它将解析出"number"。然后对于那列，它将查询转换器字典并对类型使用对应注册的转换器函数。

sqlite3.PARSE_COLNAMES¶

该常量用于connect()函数的detect_types参数。

设置它使得SQLite接口解析每个返回列的列名。它将查找[mytype]形式的字符串，然后决定'mytype'是列的类型。将会尝试在转换器字典中找到对应于'mytype'的转换器，然后将转换器函数应用于返回的值。Cursor.description中找到的的列名只是列名的第一个单词，即，如果SQL中有类似'as "x [datetime]"'的成分，那么第一个单词将会被解析成列名，直到有空格为止：列名只是简单的"x"。

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

打开到SQLite数据库文件database的连接。可以使用":memory:"打开到内存的数据库的连接。

当多个连接访问数据库，其中一个进程修改了数据库，SQLite数据库会锁定，直到事务被提交。timeout参数指明为了得到锁，连接最多等待多久，如果等待超时则抛出异常。超时参数的默认值是 5.0 （5 秒）。

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

SQLite 原生只支持文本、整数、实数，BLOB 和 NULL类型。如果您想要使用其他类型，必须自己添加对它们的支持。detect_types参数和使用由register_converter()函数注册的自定义转换器使得这很容易。

detect_types默认为 0 （即关闭，没有类型检测），你可以将它设置为PARSE_DECLTYPES和PARSE_COLNAMES的任意组合以打开类型检测。

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

默认情况下， sqlite3模块使用Connection类以调用connect。然而，可以继承Connection类，通过将子类提供给factory参数，使得connect()使用你的子类。

有关详细信息，请参阅该手册的SQLite and Python types的章节。

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

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

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

有关此功能的详细信息（包括已识别选项的列表），请参见SQLite URI文档。

在版本3.4中已更改：添加了uri参数。

sqlite3.register_converter(typename, callable)¶: 注册可调用对象用来将来自数据库的bytestring转换成为自定义的Python类型。对数据库所有有typename类型的值调用该可调用对象。参见connect()函数的detect_types参数以了解类型检测是如何工作的。请注意typename的大小写和查询中类型的名称必须匹配！

sqlite3.register_adapter(type, callable)¶: 注册可调用对象用来将自定义的 Python 类型type转换为 SQLite 的支持的类型。可调用可调用接受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来再次禁用该功能。

12.6.2. Connection Objects¶

class sqlite3.Connection¶

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

isolation_level¶: 获取或设置当前隔离级别。None为自动提交模式；或者为"DEFERRED"、"IMMEDIATE"或"EXCLUSIVE"之一。参见Controlling Transactions章节以得到更详细的解释。

in_transaction¶: True如果交易处于活动状态（有未提交的更改），则False。只读属性。
版本3.2中的新功能。

cursor([cursorClass])¶: 游标方法接受单个可选参数cursorClass。如果提供，这必须是一个扩展sqlite3.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)¶

创建一个自定义的函数，随后可以在SQL语句中以函数名name来调用它。num_params是函数接受的参数数量（如果num_params为-1，函数可以取任意数量的参数），func一个被称为SQL函数的Python可调用。

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

示例：

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

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

create_collation(name, callable)¶

用指定的name和callable创建一个排序规则。将会传两个字符串参数给可调用对象。如果第一个比第二个小，返回-1；如果它们相等，返回0；如果第一个比第二个大，返回1。请注意它控制排序（SQL里的ORDER BY），所以不会影响其它的SQL操作。

请注意可调用对象将会以Python bytestring的方式得到它的参数，一般为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参数调用该方法。

set_trace_callback(trace_callback)¶

要为SQLite后端实际执行的每个SQL语句调用寄存器trace_callback。

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

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

版本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 laoding 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)

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

如果返回元组不够，并且您希望基于名称访问列，则应考虑将row_factory设置为高度优化的sqlite3.Row类型。Row提供了基于索引的方式来访问列，也提供了基于大小写无关的名字的方式来访问列，且几乎没有内存的额外开销。它一般都会比你自定义的基于字典的方案甚至基于db_row的方案要好。

text_factory¶

使用此属性可以控制对于TEXT数据类型，何种对象将会返回。默认情况下，此属性设置为str，而sqlite3模块将返回TEXT的Unicode对象。如果要返回bytestrings，可以将其设置为bytes。

也可以将其设置为任意的其它的可调用对象，该对象接收单一的bytestring参数，返回结果对象。

请参见下面的演示例子代码：

import sqlite3

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

AUSTRIA = "\xd6sterreich"

# by default, rows are returned as Unicode
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"

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)

12.6.3. Cursor Objects¶

class sqlite3.Cursor¶

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

execute(sql[, parameters])¶

执行一个 SQL 语句。SQL语句可以参数化（即使用占位符而不是SQL字面量）。sqlite3模块支持两种类型的占位符：问号标记（qmark 型）和命名占位符（命名样式）。

这里是两种风格的示例：

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print(cur.fetchone())

execute()只执行单个 SQL 语句。如果您尝试使用它执行多个语句，它将引发sqlite3.Warning。如果想要在一个调用中执行多个 SQL 语句，请使用executescript() 。

executemany(sql, seq_of_parameters)¶

对在序列seq_of_parameters中找到的所有参数序列或映射执行SQL命令。除了序列，sqlite3模块也允许使用iterator yield 参数。

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

这里是使用一种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())

executescript(sql_script)¶

这是一次执行多个 SQL 语句的非标准的便捷方法。它先发出COMMIT语句，然后执行作为参数的 SQL 脚本。

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

fetchone()¶: 获取查询结果集的下一行，返回单一序列，如果没有数据可用，返回None。

fetchmany(size=cursor.arraysize)¶

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

由size参数指定每次调用需要获取的行数。如果它不给，游标的 arraysize 确定要读取的行数。该方法设法获取尽可能多的，由size参数所示数目的行。如果这不可行（由于指定数目的行不可用），可能会返回较少行数。

请注意关于size参数有性能上的考量。为获得最佳性能，通常最好使用 arraysize 属性。如果使用size参数，最好每次调用fetchmany()都用相同的值。

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

close()¶

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

光标将从这一点向前不可用；如果使用光标进行任何操作，则会出现ProgrammingError异常。

rowcount¶

虽然sql3模块的curcor类实现了该属性，怪异的是数据库引擎自己支持如何决定"受影响行"/"被选择行"。

对于executemany()语句，修改总数被累加到rowcount。

根据Python DB API 规范的要求，rowcount属性是-1，如果在游标上没有执行executeXX()，或者接口无法判断上次操作的rowcount。这包括SELECT语句，因为我们无法确定查询所产生的行数，直到所有的行被获取为止。

SQLite 3.6.5之前，如果无条件的DELETE FROM talbe，rowcount为 0。

lastrowid¶: 此只读属性提供最后被修改行的 rowid 。仅当您使用execute()方法发出INSERT语句时，才会设置此选项。对于非INSERT的其它操作或调用executemany()， lastrowid设置为None。

description¶

此只读属性提供最后一个查询的列名称。为了保持与 Python DB API 兼容，它对每一个列返回一个 7 元组，每个元组的后面的6个元素都是None。

对于没有匹配到任何一行的SELECT语句该属性也被设置。

connection¶

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

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

12.6.4. Row Objects¶

class sqlite3.Row¶

Row实例作为Connection对象的高度优化的row_factory。在大多数功能中它试图模仿一个元组。

它支持通过列名和索引的映射访问，迭代，表现，相等性测试和len()。

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

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

在版本3.5中已更改：添加了切片支持。

假定初始化一个前述例子中的表：

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

现在我们插入Row：

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.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

12.6.5. SQLite and Python types¶

12.6.5.1. Introduction¶

SQLite 天然支持以下类型：NULL、INTEGER、REAL、TEXT、 BLOB。

因此下述Python类型可以没有任何问题地直接发送给SQLite。

Python类型	SQLite类型
`None`	`NULL`
`int`	`INTEGER`
`float`	`REAL`
`str`	`TEXT`
`bytes`	`BLOB`

默认情况下SQLite类型将这样转换成Python类型：

SQLite类型	Python类型
`NULL`	`None`
`INTEGER`	`int`
`REAL`	`float`
`TEXT`	默认情况下取决于`text_factory`，`str`
`BLOB`	`bytes`

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

12.6.5.2. Using adapters to store additional Python types in SQLite databases¶

如之前所述，SQLite 天然只支持有限的类型。要使用其他Python类型与SQLite，您必须适应它们为sqlite3模块支持的SQLite类型之一：NoneType，int，float，str，bytes。

有两种方法，可以使sqlite3模块适配一个Python类型到一个支持的类型。

12.6.5.2.1. Letting your object adapt itself¶

这是一种好方法，如果您自己编写类。让我们假设您有一个像这样的类：

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

想要在单一的 SQLite 列中存储点。首先得选一个支持的类型，它可以用来表示点。假定使用 str ，并用分号来分隔坐标。需要给类加一个__conform__(self, protocl) ，该方法必须返回转换后的值。参数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])

12.6.5.2.2. Registering an adapter callable¶

另一种可能性就是创建一个函数，它用来将类型转成字符串表现形式，然后用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])

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

12.6.5.3. Converting SQLite values to custom Python types¶

编写适配器允许将自定义Python类型转成SQLite类型。但是要真的有用，我们必须也提供SQLite类型转成Python类型。

进入转换器。

让我们回到Point类。在SQLite中以字符串的形式存储分号分隔的x、y坐标。

首先定义一个转换器函数，它接收字符串参数，并从中构造一个Point对象。

注

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

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

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

Implicitly via the declared type隐式地通过声明的类型
Explicitly via the column name 明确地通过列名

这两种方法在Module functions and constants章节中都有描述，具体在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()

12.6.5.4. Default adapters and converters¶

在datetime模块中有对date和datetime类型的默认的适配器。它们将ISO dates／ISO timestamps发送给SQLite。

默认的转换器以"date"为名注册给datetime.date，以"timestamp"为名注册给datetime.datetime。

这样，在大多数情况下可以在Python中使用date／timestamp而不需要额外的动作。适配器的格式兼容于SQLite实际上的date/time函数。

下面的示例演示这点。

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

如果存储在SQLite中的时间戳的小数部分大于6个数字，它的值将由时间戳转换器截断至微秒的精度。

12.6.6. Controlling Transactions¶

默认情况下，sqlite3模块在数据修改语言（DML）语句之前隐式打开事务。(DML:INSERT / UPDATE / DELETE / REPLACE）（即除SELECT之外的任何东西或上述内容）。

所以如果在事务中发出CREATE TABLE ...、VACUUM、PRAGMA这样的命令， sqlite3模块将在执行这些命令之前隐式提交事务。这样做有两个理由。首先这些命令在事务中不起作用。另一个原因就是sqlite3需要跟踪事务状态（激活还是非激活）。当前事务状态通过连接对象的Connection.in_transaction属性显示。

通过connect()调用的isolation_level参数或者连接的isolation_level属性，可以控制sqlite3隐式的执行哪种BEGIN语句（或者完全不执行）。

如果需要自动提交模式／autocommint mode，将isolation_level设置为None。

其他情况下，保留其默认值，这将产生一个简单的"BEGIN"语句；或者将其设置成SQLite支持的隔离级别：“DEFERRED”、“IMMEDIATE”、或者“EXCLUSIVE”。

12.6.7. Using `sqlite3` efficiently¶

12.6.7.1. Using shortcut methods¶

使用Connection对象的非标准的executescript()、 execute()和executemany()方法，代码会更简洁，因为不用显式的创建Cursor对象（一般不必要）。这些快捷方法会隐式创建并返回Cursor对象。这样，可以直接在Connection对象上只通过一个调用就可以执行SELECT语句并遍历结果。

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

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

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print(row)

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

12.6.7.2. Accessing columns by name instead of by index¶

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

12.6.7.3. Using the connection as a context manager¶

连接对象可以用作上下文管理器来自动提交或回滚事务。在异常的情况下，事务被回滚；否则，交易被提交：

import sqlite3

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

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# 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 person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

12.6.8. Common issues¶

12.6.8.1. Multithreading¶

老版SQLite线程间共享连接时有问题。这就是为什么 Python 模块不允许线程间共享连接和游标。如果你仍然试图这样做，则会在运行时异常。

唯一的例外就是调用interrupt()方法，它只在从不同线程中调用时有意义。

脚注

[1]	（1，2）默认情况下，sqlite3模块不支持可加载扩展，因为一些平台（特别是Mac OS X）没有此功能的库。要获得可加载的扩展支持，必须传递-enable-loadable-sqlite-extensions以进行配置。

12.6. `sqlite3` - SQLite数据库的DB-API 2.0接口¶

12.6.1. Module functions and constants¶

12.6.2. Connection Objects¶

12.6.3. Cursor Objects¶

12.6.4. Row Objects¶

12.6.5. SQLite and Python types¶

12.6.5.1. Introduction¶

12.6.5.2. Using adapters to store additional Python types in SQLite databases¶

12.6.5.2.1. Letting your object adapt itself¶

12.6.5.2.2. Registering an adapter callable¶

12.6.5.3. Converting SQLite values to custom Python types¶

12.6.5.4. Default adapters and converters¶

12.6.6. Controlling Transactions¶

12.6.7. Using `sqlite3` efficiently¶

12.6.7.1. Using shortcut methods¶

12.6.7.2. Accessing columns by name instead of by index¶

12.6.7.3. Using the connection as a context manager¶

12.6.8. Common issues¶

12.6.8.1. Multithreading¶

目录

上一主题

下一主题

当前页

12.6. sqlite3 - SQLite数据库的DB-API 2.0接口¶

12.6.1. Module functions and constants¶

12.6.2. Connection Objects¶

12.6.3. Cursor Objects¶

12.6.4. Row Objects¶

12.6.5. SQLite and Python types¶

12.6.5.1. Introduction¶

12.6.5.2. Using adapters to store additional Python types in SQLite databases¶

12.6.5.2.1. Letting your object adapt itself¶

12.6.5.2.2. Registering an adapter callable¶

12.6.5.3. Converting SQLite values to custom Python types¶

12.6.5.4. Default adapters and converters¶

12.6.6. Controlling Transactions¶

12.6.7. Using sqlite3 efficiently¶

12.6.7.1. Using shortcut methods¶

12.6.7.2. Accessing columns by name instead of by index¶

12.6.7.3. Using the connection as a context manager¶

12.6.8. Common issues¶

12.6.8.1. Multithreading¶

12.6. `sqlite3` - SQLite数据库的DB-API 2.0接口¶

12.6.7. Using `sqlite3` efficiently¶