Skip to main content

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

源代码: Lib/sqlite3/


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

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

要使用模块,必须首先创建一个表示数据库的 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)

要在执行SELECT语句之后检索数据,可以将游标视为 iterator,调用游标的 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. 模块函数和常量

sqlite3.version

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

sqlite3.version_info

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

sqlite3.sqlite_version

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

sqlite3.sqlite_version_info

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

sqlite3.PARSE_DECLTYPES

该常数意味着与 connect() 函数的 detect_types 参数一起使用。

设置它使 sqlite3 模块解析它返回的每个列的声明类型。它将解析出声明类型的第一个单词, e。对于“整数主键”,它将解析出“整数”,或者对于“数字(10)”它将解析出“数字”。然后对于那个列,它将查看转换器字典并使用为那个类型注册的转换器函数。

sqlite3.PARSE_COLNAMES

该常数意味着与 connect() 函数的 detect_types 参数一起使用。

设置这使SQLite接口解析它返回的每个列的列名。它将在那里寻找一个形成的字符串[mytype],然后决定’mytype’是列的类型。它将尝试在转换器字典中找到“mytype”的条目,然后使用在那里找到的转换器函数来返回值。在 Cursor.description 中找到的列名称只是列名称的第一个单词, e。如果你在你的SQL中使用类似 'as "x [datetime]"' 的东西,那么我们将解析出所有的东西,直到列名的第一个空格:列名将只是“x”。

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

打开与SQLite数据库文件 database 的连接。您可以使用 ":memory:" 打开与驻留在RAM而不是磁盘上的数据库的数据库连接。

当多个连接访问数据库,并且其中一个进程修改数据库时,SQLite数据库将被锁定,直到该事务提交。 timeout 参数指定在发生异常之前连接应该等待锁定消失多长时间。超时参数的默认值为5.0(五秒)。

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

SQLite本身只支持类型TEXT,INTEGER,REAL,BLOB和NULL。如果你想使用其他类型,你必须自己添加支持。 detect_types 参数和使用模块级 register_converter() 函数注册的自定义 转换器 允许您轻松地执行。

detect_types 默认为0(即关闭,无类型检测),您可以将其设置为 PARSE_DECLTYPESPARSE_COLNAMES 的任意组合,以打开类型检测。

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

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

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

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

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

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

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

在 3.4 版更改: 添加了 uri 参数。

sqlite3.register_converter(typename, callable)

注册一个可调用函数,用于将数据库中的字节数转换为自定义Python类型。将为 typename 类型的所有数据库值调用可调用。为类型检测如何工作,提供 connect() 函数的参数 detect_types。注意,typename 的情况和查询中的类型名称必须匹配!

sqlite3.register_adapter(type, callable)

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

sqlite3.complete_statement(sql)

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

这可以用于构建SQLite的shell,如以下示例所示:

# A minimal SQLite shell for experiments

import sqlite3

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

buffer = ""

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

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

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

con.close()
sqlite3.enable_callback_tracebacks(flag)

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

12.6.2. 连接对象

class sqlite3.Connection

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

isolation_level

获取或设置当前隔离级别。 None 用于自动提交模式或“DEFERRED”,“IMMEDIATE”或“EXCLUSIVE”之一。参见 控制事务 部分获得更详细的解释。

in_transaction

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

3.2 新版功能.

cursor(factory=Cursor)

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

commit()

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

rollback()

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

close()

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

execute(sql[, parameters])

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

executemany(sql[, parameters])

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

executescript(sql_script)

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

create_function(name, num_params, func)

创建用户定义的函数,以后可以在函数名 name 下的SQL语句中使用。 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 创建归类。 callable将传递两个字符串参数。如果第一个排序低于第二个,它应返回-1,如果它们排序为等于0,则返回0,如果第一个排序高于第二个,则返回1。请注意,这会控制排序(SQL中为ORDER BY),因此您的比较不会影响其他SQL操作。

注意,callable将获得它的参数为Python bytestrings,它通常以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()

要删除归类,请将具有 Nonecreate_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 数据类型返回的对象。默认情况下,此属性设置为 strsqlite3 模块将返回 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. 光标对象

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语句。如果你试图用它执行多个语句,它会引发一个 Warning。如果要通过一次调用执行多个SQL语句,请使用 executescript()

executemany(sql, seq_of_parameters)

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

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

虽然 sqlite3 模块的 Cursor 类实现了这个属性,但数据库引擎自己对确定“行受影响”/“选择的行”的支持是古怪的。

对于 executemany() 语句,修改的数量总计为 rowcount

根据Python DB API Spec的要求,rowcount 属性“在游标上没有执行 executeXX() 的情况下为-1”,或者最后一个操作的行计数不能由接口确定。这包括 SELECT 语句,因为我们无法确定在获取所有行之前生成的查询的行数。

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

lastrowid

此只读属性提供最后一个修改行的rowid。仅当您使用 execute() 方法发出 INSERTREPLACE 语句时,才会设置此值。对于除 INSERTREPLACE 之外的操作或当调用 executemany() 时,lastrowid 设置为 None

如果 INSERTREPLACE 语句未能插入,则返回先前成功的rowid。

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

description

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

它为没有任何匹配行的 SELECT 语句设置。

connection

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

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

12.6.4. 行对象

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. 例外

exception sqlite3.Warning

Exception 的子类。

exception sqlite3.Error

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

exception sqlite3.DatabaseError

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

exception sqlite3.IntegrityError

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

exception sqlite3.ProgrammingError

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

12.6.6. SQLite和Python类型

12.6.6.1. 介绍

SQLite本身支持以下类型:NULLINTEGERREALTEXTBLOB

因此,以下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_factorystr 默认

BLOB

bytes

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

12.6.6.2. 使用适配器在SQLite数据库中存储其他Python类型

如前所述,SQLite本身只支持有限的一组类型。要使用其他Python类型与SQLite,你必须 适应 他们的sqlite3模块支持的SQLite类型之一:NoneType,int,float,str,bytes。

有两种方法使 sqlite3 模块能够将自定义的Python类型适配为支持的一种。

12.6.6.2.1. 让你的对象适应自己

这是一个很好的方法,如果你自己写类。让我们假设你有这样的类:

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

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

import sqlite3

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

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

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

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

12.6.6.2.2. 注册适配器可调用

另一种可能性是创建一个函数,将类型转换为字符串表示形式并使用 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 类型。现在让我们假设我们要存储不是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])

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

编写适配器允许您将自定义Python类型发送到SQLite。但是要使它真的有用,我们需要使Python到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 模块知道你从数据库中选择的是什么。有两种方法:

  • 隐式通过声明的类型

  • 显式通过列名

这两种方法在 模块函数和常量 部分,常量 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.6.4. 默认适配器和转换器

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

默认转换器在 datetime.date 的名称“date”下注册,在 datetime.datetime 的名称“timestamp”下注册。

这样,您可以在Python中使用日期/时间戳,在大多数情况下不会出现任何额外的错误。适配器的格式也与实验SQLite日期/时间函数兼容。

下面的例子演示了这一点。

import sqlite3
import datetime

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

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

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

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

如果存储在SQLite中的时间戳具有长于6个数字的小数部分,则其值将被时间戳转换器截断为微秒精度。

12.6.7. 控制事务

默认情况下,sqlite3 模块在数据修改语言(DML)语句(即 INSERT/UPDATE/DELETE/REPLACE)之前隐式打开事务。

因此,如果你在一个事务中并发出一个命令,如 CREATE TABLE ...VACUUMPRAGMAsqlite3 模块将在执行该命令之前隐式提交。这有两个原因。第一个是这些命令中的一些不在事务内工作。另一个原因是sqlite3需要跟踪事务状态(如果事务是活动的或不活动的)。当前事务状态通过连接对象的 Connection.in_transaction 属性显示。

您可以通过对 connect() 调用的 isolation_level 参数或通过连接的 isolation_level 属性来控制哪种类型的 BEGIN 语句sqlite3隐式执行(或根本不执行)。

如果你想要 自动提交模式,然后将 isolation_level 设置为 None

否则将其保留为默认值,这将导致一个简单的“BEGIN”语句,或将其设置为SQLite支持的隔离级别之一:“DEFERRED”,“IMMEDIATE”或“EXCLUSIVE”。

在 3.6 版更改: sqlite3 用于在DDL语句之前隐式提交打开的事务。这不是这样的。

12.6.8. 有效使用 sqlite3

12.6.8.1. 使用快捷方式

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

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.8.2. 按名称而不是按索引访问列

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.8.3. 使用连接作为上下文管理器

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

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.9. 常见问题

12.6.9.1. 多线程

较旧的SQLite版本有共享线程之间的连接的问题。这就是为什么Python模块不允许在线程之间共享连接和游标的原因。如果你仍然试图这样做,你会在运行时得到一个异常。

唯一的例外是调用 interrupt() 方法,这只有从不同的线程调用才有意义。

脚注

[1](1, 2)

sqlite3模块不是默认使用可加载扩展支持构建的,因为一些平台(特别是Mac OS X)具有在没有此功能的情况下编译的SQLite库。要获得可加载的扩展支持,必须传递–enable-loadable-sqlite-extensions以进行配置。