11.13. sqlite3 —用于 SQLite 数据库的 DB-API 2.0 接口

2.5 版的新Function。

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 的参数替换。将?用作占位符，无论您想使用哪个值，然后提供一个值 Tuples 作为游标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

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

11.13.1. 模块Function和常量

• sqlite3. version

  • 此模块的版本号，以字符串形式。这不是 SQLite 库的版本。

• sqlite3. version_info

  • 此模块的版本号，以整数 Tuples 的形式。这不是 SQLite 库的版本。

• sqlite3. sqlite_version

  • 运行时 SQLite 库的版本号，以字符串形式。

• sqlite3. sqlite_version_info

  • 运行时 SQLite 库的版本号，以整数 Tuples 的形式。

• sqlite3. PARSE_DECLTYPES

  • 该常数应与connect()函数的* detect_types *参数一起使用。

设置它会使sqlite3模块为返回的每一列解析语句的类型。它将解析出语句的类型的第一个单词 i。 e。对于“整数主键”，它将解析出“整数”，对于“数字(10)”，它将解析出“数字”。然后，对于该列，它将查找转换器字典，并使用在那里为该类型注册的转换器函数。

• sqlite3. PARSE_COLNAMES
  • 该常数应与connect()函数的* detect_types *参数一起使用。

设置此选项将使 SQLite 接口解析它返回的每个列的列名。它将在其中查找形成[mytype]的字符串，然后确定“ mytype”是列的类型。它将try在转换器字典中找到“ mytype”条目，然后使用在那里找到的转换器函数返回该值。在Cursor.description中找到的列名只是该列名的第一个单词，即。 e。如果您在 SQL 中使用类似'as "x [datetime]"'的名称，那么我们将解析所有内容，直到列名的第一个空白为止：列名将只是“ x”。

• sqlite3. connect(* database * [，* timeout ， detect_types ， isolation_level ， check_same_thread ， factory ， cached_statements *])
  • 打开与 SQLite 数据库文件* database *的连接。您可以使用":memory:"打开与驻留在 RAM 中而不是磁盘上的数据库的数据库连接。

当pass多个连接访问数据库，并且其中一个进程修改了数据库时，SQLite 数据库将被锁定，直到提交该事务为止。 * timeout *参数指定连接应 await 多长时间才能引发锁，直到引发异常为止。超时参数的默认值为 5.0(五秒)。

有关* isolation_level *参数，请参见Connection个对象的Connection.isolation_level属性。

SQLite 本身仅支持 TEXT，INTEGER，REAL，BLOB 和 NULL 类型。如果要使用其他类型，则必须自己添加对它们的支持。pass模块级register_converter()Function注册的* detect_types *参数和使用自定义**转换器，您可以轻松地做到这一点。

• detect_types *默认为 0(即关闭，不进行类型检测)，您可以将其设置为PARSE_DECLTYPES和PARSE_COLNAMES的任意组合以打开类型检测。

默认情况下，sqlite3模块将其Connection类用于 connect 调用。但是，您可以为Connection类提供子类，并pass为* factory *参数提供类来使connect()使用您的类。

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

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

• sqlite3. register_converter(* typename ， callable *)

  • 注册一个 callable，以将字节串从数据库转换为自定义 Python 类型。对于所有* typename 类型的数据库值，将调用 callable。为类型检测的工作方式提供connect()函数的参数 detect_types 。请注意， typename *和查询中类型的名称以不区分大小写的方式匹配。

• sqlite3. register_adapter(* type ， callable *)

  • 注册一个可调用项，以将自定义 Python 类型* type 转换为 SQLite 支持的类型之一。可调用的 callable *接受 Python 值作为单个参数，并且必须返回以下类型的值：int，long，float，str(UTF-8 编码)，unicode 或 buffer。

• sqlite3. complete_statement(* sql *)

  • 如果字符串* sql *包含一个或多个以分号结尾的完整 SQL 语句，则返回True。它不验证 SQL 在语法上是否正确，仅验证不存在未结束的字符串 Literals 并且该语句以分号终止。

可以用来为 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 = raw_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再次禁用该Function。

11.13.2. 连接对象

• 类别 sqlite3. Connection

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

• isolation_level

  • 获取或设置当前的隔离级别。 None用于自动提交模式，或者是“ DEFERRED”，“ IMMEDIATE”或“ EXCLUSIVE”之一。有关详细说明，请参见Controlling Transactions部分。

• cursor(* factory = Cursor *)

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

• commit ( )

  • 此方法提交当前事务。如果不调用此方法，则从其他数据库 Connecting 看不到自上次调用commit()以来的所有操作。如果您想知道为什么看不到已写入数据库的数据，请检查您是否没有忘记调用此方法。

• rollback ( )

  • 自上次调用commit()以来，此方法将回滚对数据库的所有更改。

• close ( )

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

• execute(* sql * [，* parameters *])

  • 这是一个非标准的快捷方式，它pass调用游标方法来创建中间游标对象，然后使用给定的参数调用游标的execute方法。

• executemany(* sql * [，* parameters *])

  • 这是一个非标准的快捷方式，它pass调用游标方法来创建中间游标对象，然后使用给定的参数调用游标的executemany方法。

• executescript(* sql_script *)

  • 这是一个非标准的快捷方式，它pass调用游标方法来创建中间游标对象，然后使用给定的参数调用游标的executescript方法。

• create_function(* name ， num_params ， func *)

  • 创建用户定义的函数，以后可以在 SQL 语句中以函数名* name *使用。 * num_params 是该函数接受的参数数，而 func *是一个可调用的 Python，称为 SQL 函数。

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

Example:

import sqlite3
import md5

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

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

• create_aggregate((* name ， num_params ， aggregate_class *)
  • 创建一个用户定义的聚合函数。

聚合类必须实现step方法和finalize方法，该方法接受参数* num_params *的数量，该方法将返回聚合的finally结果。

finalize方法可以返回 SQLite 支持的任何类型：unicode，str，int，long，float，buffer 和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]

• create_collation(* name ， callable *)
  • 使用指定的* name 和 callable *创建排序规则。可调用对象将传递两个字符串参数。如果第一个命令的 Sequences 低于第二个命令，则应返回-1，如果它们的命令 Sequences 等于第二则返回 0，如果第一个命令的 Sequences 高于第二个命令则返回 1.请注意，此控件控制排序(在 SQL 中为 ORDER BY)，因此您的比较不会影响其他 SQL 操作。

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

以下示例显示了一种按“错误方式”排序的自定义归类：

import sqlite3

def collate_reverse(string1, string2):
    return -cmp(string1, string2)

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

要删除排序规则，请调用create_collation并调用None：

con.create_collation("reverse", None)

• interrupt ( )

  • 您可以从其他线程调用此方法，以中止可能在连接上执行的任何查询。然后查询将中止，并且调用方将获得异常。

• set_authorizer(* authorizer_callback *)

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

回调的第一个参数表示要授权哪种操作。第二和第三个参数将是参数或None，具体取决于第一个参数。第四个参数是数据库的名称(“ main”，“ temp”等)(如果适用)。第 5 个参数是负责访问try的最内部触发器或视图的名称；如果此访问try直接来自 ImportingSQL 代码，则为None。

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

• set_progress_handler(* handler ， n *)
  • 该例程注册一个回调。对于 SQLite 虚拟机的每个* n *指令，都会调用该回调。如果您想在长时间运行的操作中从 SQLite 调用(例如更新 GUI)，这将很有用。

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

2.6 版的新Function。

• enable_load_extension(已启用)
  • 此例程允许/不允许 SQLite 引擎从共享库加载 SQLite 扩展。 SQLite 扩展可以定义新Function，聚合或全新的虚拟表实现。一种众所周知的扩展是随 SQLite 一起分发的全文搜索扩展。

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

2.7 版的新Function。

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

• load_extension(* path *)
  • 此例程从共享库加载 SQLite 扩展。您必须先使用enable_load_extension()启用扩展加载，然后才能使用此例程。

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

2.7 版的新Function。

• row_factory
  • 您可以将此属性更改为可调用的可调用对象，该对象将光标和原始行作为 Tuples 接受，并将返回实际结果行。这样，您可以实现更高级的返回结果的方式，例如返回还可以pass名称访问列的对象。

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

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

• text_factory
  • 使用此属性，您可以控制针对TEXT数据类型返回哪些对象。默认情况下，此属性设置为unicode，并且sqlite3模块将为TEXT返回 Unicode 对象。如果您想返回字节串，则可以将其设置为str。

出于效率原因，还有一种方法仅针对非 ASCII 数据返回 Unicode 对象，否则返回字节串。要激活它，请将此属性设置为sqlite3.OptimizedUnicode。

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

请参见以下示例代码以获取说明：

import sqlite3

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

AUSTRIA = u"\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 = str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is str
# 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 will ignore Unicode characters that cannot be
# decoded from UTF-8
con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
cur.execute("select ?", ("this is latin1 and would normally create errors" +
                         u"\xe4\xf6\xfc".encode("latin1"),))
row = cur.fetchone()
assert type(row[0]) is unicode

# sqlite3 offers a built-in optimized text_factory that will return bytestring
# objects, if the data is in ASCII only, and otherwise return unicode objects
con.text_factory = sqlite3.OptimizedUnicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is unicode

cur.execute("select ?", ("Germany",))
row = cur.fetchone()
assert type(row[0]) is str

• total_changes

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

• iterdump

  • 返回一个迭代器，以 SQL 文本格式转储数据库。在保存内存数据库以供以后还原时很有用。此Function提供的Function与 sqlite3 **Shell 程序中的.dump 命令相同。

2.6 版的新Function。

Example:

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

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

11.13.3. 光标对象

• 类别 sqlite3. Cursor

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

• execute(* sql * [，* parameters *])

  • 执行一条 SQL 语句。可以对 SQL 语句进行参数化(即，占位符而不是 SQLLiterals)。 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 语句。如果您try使用它执行多个语句，则会引发警告。如果要一次调用执行多个 SQL 语句，请使用executescript()。

• executemany(* sql ， seq_of_parameters *)
  • 对所有参数序列或序列* sql *中的 Map 执行 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.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 *可以是字节字符串或 Unicode 字符串。

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

• fetchone ( )

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

• fetchmany([* size = cursor.arraysize *])

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

每次调用要获取的行数由* size *参数指定。如果未给出，则游标的 arraysize 确定要获取的行数。该方法应try获取由 size 参数指示的尽可能多的行。如果由于指定的行数不可用而无法执行此操作，则可能会返回较少的行。

请注意，* size 参数涉及性能方面的考虑。为了获得最佳性能，通常最好使用 arraysize 属性。如果使用 size *参数，则最好从一个fetchmany()调用到下一个fetchmany()调用保留相同的值。

• fetchall ( )

  • 获取查询结果的所有(剩余)行，并返回一个列表。请注意，游标的 arraysize 属性会影响此操作的性能。如果没有可用的行，则返回一个空列表。

• rowcount

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

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

根据 Python DB API 规范的要求，rowcount属性“在没有对光标执行executeXX()或接口无法确定最后一个操作的行数的情况下为-1”。这包括SELECT语句，因为在获取所有行之前，我们无法确定查询产生的行数。

对于 3.6.5 之前的 SQLite 版本，如果不带任何条件进行DELETE FROM table，则rowcount设置为 0.

• lastrowid

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

• description

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

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

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

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

11.13.4. 行对象

• 类别 sqlite3. Row
  • Row实例用作Connection对象的高度优化的row_factory。它试图模仿其大部分Function中的 Tuples。

它支持按列名和索引，迭代，表示形式，相等性测试和len()进行 Map 访问。

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

在 2.6 版中进行了更改：添加了迭代和相等性(可哈希性)。

• keys ( )
  • 此方法返回列名列表。查询后，它立即成为Cursor.description中每个 Tuples 的第一个成员。

2.6 版的新Function。

假设我们像上面给出的示例那样初始化一个表：

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)
<type 'sqlite3.Row'>
>>> r
(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
u'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

11.13.5. SQLite 和 Python 类型

11.13.5.1. Introduction

SQLite 本机支持以下类型：NULL，INTEGER，REAL，TEXT，BLOB。

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

Python type	SQLite type
None	NULL
int	INTEGER
long	INTEGER
float	REAL
str (UTF8-encoded)	TEXT
unicode	TEXT
buffer	BLOB

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

SQLite type	Python type
NULL	None
INTEGER	int或long，具体取决于大小
REAL	float
TEXT	默认情况下取决于text_factory，unicode
BLOB	buffer

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

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

如前所述，SQLite 本机仅支持有限类型的集合。要将其他 Python 类型与 SQLite 一起使用，您必须将它们“适应”为 SQLite 的 sqlite3 模块支持的类型之一：NoneType，int，long，float，str，unicode，buffer 中的一种。

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

11.13.5.2.1. 让您的对象适应自己

如果您自己编写该类，那么这是一个好方法。假设您有一个像这样的类：

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

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

import sqlite3

class Point(object):
    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]

11.13.5.2.2. 注册可调用的适配器

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

import sqlite3

class Point(object):
    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类型的默认适配器。现在，假设我们要不是以 ISO 表示形式而是以 Unix 时间戳存储datetime.datetime对象。

import sqlite3
import datetime, 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]

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

编写适配器可让您将自定义 Python 类型发送到 SQLite。但是要使其 true 有用，我们需要使 Python to SQLite to Python 往返工作。

Enter converters.

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

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

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

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

• 隐式地pass 语句的类型

• pass列名明确表示

两种方法都在模块Function和常量部分的常量PARSE_DECLTYPES和PARSE_COLNAMES的条目中进行了描述。

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

import sqlite3

class Point(object):
    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)

def convert_point(s):
    x, y = map(float, s.split(";"))
    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()

11.13.5.4. 默认适配器和转换器

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

默认转换器在datetime.date下以“日期”名称注册，在datetime.datetime下以“时间戳”名称注册。

这样，您可以在大多数情况下使用 Python 中的日期/时间戳，而无需进行任何其他操作。适配器的格式还与实验性 SQLite 日期/时间Function兼容。

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

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 个数字，则时间戳转换器会将其值截断为微秒精度。

11.13.6. 控制 Transaction

默认情况下，sqlite3模块在数据修改语言(DML)语句(即INSERT/UPDATE/DELETE/REPLACE)之前隐式打开事务，并在非 DML，非查询语句(即SELECT或之前所提)。

因此，如果您在事务中并发出诸如CREATE TABLE ...，VACUUM，PRAGMA之类的命令，则sqlite3模块将在执行该命令之前隐式提交。这样做有两个原因。首先是其中一些命令在事务中不起作用。另一个原因是 sqlite3 需要跟踪事务状态(无论事务是否处于活动状态)。

您可以passconnect()调用的* isolation_level *参数或pass连接的isolation_level属性来控制 sqlite3 隐式执行(或根本不执行)哪种BEGIN语句。

如果要 自动提交模式 ，则将isolation_level设置为None。

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

11.13.7. 有效使用 sqlite3

11.13.7.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"

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

sqlite3模块的一个有用Function是内置的sqlite3.Row类，该类设计用作行工厂。

可以用索引(如 Tuples)和不区分大小写的名称访问用此类包装的行：

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

11.13.7.3. 将连接用作上下文 Management 器

2.6 版的新Function。

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

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"

11.13.8. 常见问题

11.13.8.1. Multithreading

较旧的 SQLite 版本在共享线程之间存在连接问题。这就是 Python 模块不允许线程之间共享连接和游标的原因。如果仍然try这样做，则在运行时会出现异常。

唯一的 exception 是调用interrupt()方法，只有从其他线程调用才有意义。

Footnotes

• 1 (1,2)
  • 默认情况下，sqlite3 模块不具有可加载扩展支持，因为某些平台(尤其是 Mac OS X)具有 SQLite 库，这些库在编译时没有此Function。要获得可加载的扩展支持，必须修改 setup.py 并删除设置 SQLITE_OMIT_LOAD_EXTENSION 的行。