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_DECLTYPESPARSE_COLNAMES的任意组合以打开类型检测。

默认情况下,check_same_threadTrue,只有创建线程可以使用连接。如果设置为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)

用指定的namecallable创建一个排序规则。将会传两个字符串参数给可调用对象。如果第一个比第二个小,返回-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。

如果希望清除之前安装的过程处理器,以Nonehandler参数调用该方法。

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 talberowcount为 0。

lastrowid

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

description

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

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

connection

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

>>> 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 天然支持以下类型:NULLINTEGERREALTEXTBLOB

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

Python类型SQLite类型
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB

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

SQLite类型Python类型
NULLNone
INTEGERint
REALfloat
TEXT默认情况下取决于text_factorystr
BLOBbytes

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) ,该方法必须返回转换后的值。参数protocolPrepareProtocol

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.datedatetime.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_DECLTYPESPARSE_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 ...VACUUMPRAGMA这样的命令, 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]12默认情况下,sqlite3模块不支持可加载扩展,因为一些平台(特别是Mac OS X)没有此功能的库。要获得可加载的扩展支持,必须传递-enable-loadable-sqlite-extensions以进行配置。