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

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

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

pysqlite由GerhardHäring编写，提供符合DB-API 2.0规范的SQL接口 PEP 249。

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

conn  =  sqlite3 .connect （'/ tmp / example' ）

您还可以提供特殊名称:memory:以在RAM中创建数据库。

获得之后Connection，您可以创建一个Cursor 对象并调用其execute()方法来执行SQL命令：

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

通常，您的SQL操作需要使用Python变量中的值。你不应该使用Python的字符串操作来组装你的查询，因为这样做是不安全的; 它使您的程序容易受到SQL注入攻击。

而是使用DB-API的参数替换。?在任何想要使用值的位置放置占位符，然后提供值元组作为游标execute()方法的第二个参数。（其他数据库模块可能使用不同的占位符，例如%s或:1。）例如：

# 永远不要这样做 - 不安全！
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# 而是这样做
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# 多参数的示例
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

要在执行SELECT语句后检索数据，可以将游标视为迭代器，调用游标的fetchone()方法以检索单个匹配行，或调用fetchall()以获取匹配行的列表。

此示例使用迭代器形式：

>>> c = conn.cursor()
>>> c.execute('select * from stocks order by price')
>>> for row in c:
...    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'MSOFT', 1000, 72.0)
>>>

http://github.com/ghaering/pysqlite

pysqlite网页 - sqlite3是在外部开发的，名称为“pysqlite”。
http://www.sqlite.org

SQLite网页; 该文档描述了受支持的SQL方言的语法和可用数据类型。
PEP 249 - 数据库API规范2.0

PEP由Marc-AndréLemburg撰写。

模块函数和常量

sqlite3.``PARSE_DECLTYPES

此常量用于与函数的detect_types参数一起使用 connect()。设置它使sqlite3模块解析它返回的每个列的声明类型。它将解析出声明类型的第一个单词，即对于“整数主键”，它将解析出“整数”，或者对于“数字（10）”，它将解析出“数字”。然后对于该列，它将查看转换器字典并使用在那里注册的转换器函数。
sqlite3.``PARSE_COLNAMES

此常量用于与函数的detect_types参数一起使用connect()。设置此选项会使SQLite接口解析它返回的每个列的列名。它将在那里查找形成[mytype]的字符串，然后确定’mytype’是列的类型。它将尝试在转换器字典中找到“mytype”的条目，然后使用在那里找到的转换器函数来返回值。找到的列名Cursor.description 只是列名的第一个单词，即如果你在SQL中使用类似的东西，那么我们将解析所有内容，直到列名的第一个空白：列名只是“x”。'as "x [datetime]"'
sqlite3.``connect（database [，timeout，isolation_level，detect_types，factory ]）

打开与SQLite数据库文件数据库的连接。您可以使用 ":memory:"打开与驻留在RAM而不是磁盘上的数据库的数据库连接。当多个连接访问数据库，并且其中一个进程修改数据库时，SQLite数据库将被锁定，直到提交该事务为止。该超时参数指定连接应该多长时间等待锁消失，直到引发异常。timeout参数的默认值为5.0（五秒）。对于isolation_level参数，请参阅对象的 Connection.isolation_level属性Connection。SQLite本身仅支持TEXT，INTEGER，REAL，BLOB和NULL类型。如果您想使用其他类型，您必须自己添加对它们的支持。使用模块级函数注册的 detect_types参数和使用自定义转换器register_converter()可以轻松地执行此操作。detect_types缺省值为0（即关闭，无类型检测），可以将其设置为任意组合PARSE_DECLTYPES和PARSE_COLNAMES开启类型检测上。默认情况下，sqlite3模块使用其Connection类进行连接调用。但是，您可以通过为工厂参数提供类来替代Connection类并 connect()使用您的类。有关详细信息，请参阅本手册的SQLite和Python类型部分。该sqlite3模块在内部使用语句缓存来避免SQL解析开销。如果要显式设置为连接缓存的语句数，可以设置cached_statements参数。当前实现的默认值是缓存100个语句。
sqlite3.``register_converter（typename，callable ）

注册callable以将数据库中的bytestring转换为自定义Python类型。将为typename类型的所有数据库值调用callable 。赋予函数的参数detect_types以connect()进行类型检测的工作方式。请注意，typename的大小写和查询中类型的名称必须匹配！
sqlite3.``register_adapter（类型，可调用）

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

返回True如果字符串SQL包含由分号终止一个或多个完整的SQL语句。它不验证SQL在语法上是否正确，只是没有未闭合的字符串文字，并且语句以分号结束。这可以用于为SQLite构建一个shell，如下例所示：

#最小SQLite shell的实验研究

from pysqlite2 import dbapi2 as sqlite3

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

buffer = ""

print "Enter your SQL commands to execute in SQLite."
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, e:
            print "An error occurred:", e.args[0]
        buffer = ""

con.close()

sqlite3.``enable_callback_tracebacks（旗）

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

连接对象

类sqlite3.``Connection

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

Connection.``isolation_level

获取或设置当前隔离级别。None用于自动提交模式或“DEFERRED”，“IMMEDIATE”或“EXCLUSIVE”之一。有关更详细的说明，请参阅控制事务一节。
Connection.``cursor（[cursorClass ]）

cursor方法接受一个可选参数cursorClass。如果提供，则必须是扩展的自定义游标类sqlite3.Cursor。
Connection.``commit（）

此方法提交当前事务。如果不调用此方法，commit()则从上次调用以来执行的任何操作都不会从其他数据库连接中看到。如果您想知道为什么没有看到您写入数据库的数据，请检查您是否忘记调用此方法。
Connection.``rollback（）

自上次调用以来，此方法回滚对数据库的任何更改 commit()。
Connection.``close（）

这将关闭数据库连接。请注意，这不会自动调用commit()。如果您只是先关闭数据库连接而不commit()先打电话，那么您的更改将会丢失！
Connection.``execute（sql [，参数]）

这是一个非标准的快捷方式，它通过调用cursor方法创建一个中间游标对象，然后execute使用给定的参数调用游标的方法。
Connection.``executemany（sql [，参数]）

这是一个非标准的快捷方式，它通过调用cursor方法创建一个中间游标对象，然后executemany使用给定的参数调用游标的方法。
Connection.``executescript（sql_script ）

这是一个非标准的快捷方式，它通过调用cursor方法创建一个中间游标对象，然后executescript使用给定的参数调用游标的方法。
Connection.``create_function（name，num_params，func ）

创建一个可以从SQL语句中以后使用下面的功能名称的用户定义函数的名称。num_params是函数接受的参数个数，func是Python可调用的，被称为SQL函数。该函数可以返回SQLite支持的任何类型：unicode，str，int，long，float，buffer和None。

例：创建一个可以从SQL语句中以后使用下面的功能名称的用户定义函数的名称。num_params是函数接受的参数个数，func是Python可调用的，被称为SQL函数。该函数可以返回SQLite支持的任何类型：unicode，str，int，long，float，buffer和None。例：

from pysqlite2 import dbapi2 as 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]

Connection.``create_aggregate（name，num_params，aggregate_class ）

创建用户定义的聚合函数。

聚合类必须实现一个step方法，该方法接受参数num_params的数量，以及一个finalize将返回聚合的最终结果的方法。

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

from pysqlite2 import dbapi2 as 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]

Connection.``create_collation（名称，可调用）

创建具有指定名称和可调用的排序规则。callable将传递两个字符串参数。如果第一个排序低于第二个，则应返回-1;如果排序等于0，则返回0;如果第一个排序高于第二个，则返回1。请注意，这会控制排序（SQL中的ORDER BY），因此您的比较不会影响其他SQL操作。

请注意，callable将其参数作为Python字节串，通常以UTF-8编码。

以下示例显示了对“错误方式”进行排序的自定义排序规则：

from pysqlite2 import dbapi2 as 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作为callable调用：

Connection.``interrupt（）

您可以从其他线程调用此方法以中止可能在连接上执行的任何查询。然后查询将中止，调用者将获得异常。
Connection.``set_authorizer（authorizer_callback ）

此例程注册回调。每次尝试访问数据库中表的列时都会调用回调。SQLITE_OK如果允许访问，SQLITE_DENY则应该返回回调，如果整个SQL语句应该中止并出现错误，SQLITE_IGNORE并且该列应该被视为NULL值。这些常量在sqlite3模块中可用。回调的第一个参数表示要授权的操作类型。第二个和第三个参数将是参数或None 取决于第一个参数。第四个参数是数据库的名称（“main”，“temp”等）（如果适用）。第五个参数是负责访问尝试的最内层触发器或视图的名称，或者 None此访问尝试是否直接来自输入SQL代码。请参阅SQLite文档，了解第一个参数的可能值以及第二个和第三个参数的含义，具体取决于第一个参数。sqlite3模块中提供了所有必需的常量。
Connection.``get_limit（limit_id ）

此例程返回由常量limit_id指定的限制的当前值。有关limit_id参数的可能值，请参阅SQLite文档。
Connection.``set_limit（limit_id，new_value ）

此例程为常量limit_id指定的限制设置新值。有关limit_id参数的可能值，请参阅SQLite文档。
Connection.``set_progress_handler（处理程序，n ）

此例程注册回调。对SQLite虚拟机的每n个指令调用回调。如果要在长时间运行的操作期间从SQLite调用（例如更新GUI），这将非常有用。如果要清除以前安装的任何进度处理程序，请使用Nonefor handler调用该方法。
Connection.``enable_load_extension（启用）

此例程允许/禁止SQLite引擎从共享库加载SQLite扩展。SQLite扩展可以定义新功能，聚合或全新的虚拟表实现。一个众所周知的扩展是与SQLite一起分发的全文搜索扩展。

from pysqlite2 import dbapi2 as sqlite3

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

# 加载全文搜索扩展名
con.enable_load_extension(True)

# 或者您可以使用API调用加载扩展名
con.execute("select load_extension('./fts3.so')")

# 或者，可以使用API加载扩展。
call:
# con.load_extension("./fts3.so")

# 禁用扩展laoding
con.enable_load_extension(False)

# SQLite维基实例
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

Connection.``load_extension（路径）

此例程从共享库加载SQLite扩展。您必须先启用扩展加载，enable_load_extension然后才能使用此例程。
Connection.``row_factory

您可以将此属性更改为可接受游标和原始行作为元组的可调用对象，并返回实际结果行。这样，您可以实现更高级的返回结果的方法，例如返回一个也可以按名称访问列的对象。例：

from pysqlite2 import dbapi2 as 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"]

Connection.``text_factory

使用此属性可以控制为TEXT 数据类型返回的对象。默认情况下，此属性设置为unicode，sqlite3模块将返回Unicode对象TEXT。如果要返回字节串，可以将其设置为str。出于效率原因，还有一种方法只返回非ASCII数据的Unicode对象，否则返回字节串。要激活它，请将此属性设置为 sqlite3.OptimizedUnicode。您还可以将其设置为接受单个bytestring参数的任何其他可调用对象，并返回结果对象。请参阅以下示例代码以进行说明：

from pysqlite2 import dbapi2 as sqlite3

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

# 创建表
con.execute("create table person(lastname, firstname)")

AUSTRIA = u"\xd6sterreich"

# 默认情况下，行返回为Unicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# 但我们可以让pysqlite总是返回bytestrings ... 
con.text_factory = str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) == str
# 字节串将被编码在UTF-8中，除非您将存储垃圾
# 到数据库 ...
assert row[0] == AUSTRIA.encode("utf-8")

# 我们也可以实现一个定制的text_factory…
# 这里我们从UTF-8解码实现了一个忽略Unicode字符的方法。
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]) == unicode
#pysqlite 提供了一个构建优化的 text_factory，它将返回字节串。
#对象，如果数据仅在ASCII中，则返回Unicode对象。
con.text_factory = sqlite3.OptimizedUnicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) == unicode

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

Connection.``total_changes

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

返回以SQL文本格式转储数据库的迭代器。保存内存数据库以便以后恢复时很有用。此函数提供与sqlite3 shell中的.dump命令相同的功能。例：

#将文件existing_db.db转换为SQL转储文件dump.sql
import sqlite3, os

con = sqlite3.connect('existing_db.db')
full_dump = os.linesep.join([line for line in con.iterdump()])
f = open('dump.sql', 'w')
f.writelines(full_dump)
f.close()

游标对象

一个Cursor实例具有以下属性和方法：

SQLite数据库游标具有以下属性和方法：

Cursor.``close（）

现在关闭光标（而不是每次调用__del__）。从这一点开始，光标将无法使用; 如果使用游标尝试任何操作，将引发错误（或子类）异常。

Cursor.``execute（sql [，参数]）

执行SQL语句。SQL语句可以是参数化的（即占位符而不是SQL文本）。该 sqlite3模块支持两种占位符：问号（qmark样式）和命名占位符（命名样式）。此示例显示如何使用qmark样式的参数：

from pysqlite2 import dbapi2 as sqlite3

con = sqlite3.connect("mydb")

cur = con.cursor()

who = "Yeltsin"
age = 72

cur.execute("select name_last, age from people where name_last=? and age=?", (who, age))
print cur.fetchone()

此示例显示如何使用命名样式：

from pysqlite2 import dbapi2 as sqlite3

con = sqlite3.connect("mydb")

cur = con.cursor()

who = "Yeltsin"
age = 72

cur.execute("select name_last, age from people where name_last=:who and age=:age",
    {"who": who, "age": age})
print cur.fetchone()

Cursor.``executemany（sql，seq_of_parameters ）

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

from pysqlite2 import dbapi2 as 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),) #这是一个 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()

这是使用生成器的简短示例：

from pysqlite2 import dbapi2 as sqlite3

def char_generator():
    import string
    for c in string.letters[:26]:
        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()

Cursor.``executescript（sql_script ）

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

sql_script可以是bytestring或Unicode字符串。

例：

from pysqlite2 import dbapi2 as 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
    );
    """)

Cursor.``fetchone（）

获取查询结果集的下一行，返回单个序列，或者None当没有更多数据可用时。
Cursor.``fetchmany（[size = cursor.arraysize ]）

获取查询结果的下一组行，返回一个列表。当没有更多行可用时，返回空列表。每次调用获取的行数由size参数指定。如果未给出，则游标的arraysize确定要获取的行数。该方法应尝试获取size参数指示的行数。如果由于指定的行数不可用而无法执行此操作，则可能返回的行数较少。请注意，size参数涉及性能方面的考虑因素。为获得最佳性能，通常最好使用arraysize属性。如果使用size参数，那么最好从一次fetchmany()调用到下一次调用保留相同的值。
Cursor.``fetchall（）

获取查询结果的所有（剩余）行，返回列表。请注意，游标的arraysize属性可能会影响此操作的性能。没有可用行时返回空列表。
Cursor.``rowcount

虽然模块的Cursor类sqlite3实现了这个属性，但是数据库引擎自己支持确定“受影响的行”/“选择的行”是古怪的。对于DELETE语句，rowcount如果您没有任何条件，SQLite将报告为0 。DELETE FROM table对于executemany()陈述，修改的数量总结为rowcount。根据Python DB API Spec的要求，rowcount如果没有executeXX()对游标执行或者最后一个操作的rowcount不能被接口确定，则属性“为-1 ”。这包括SELECT语句，因为在获取所有行之前，我们无法确定查询生成的行数。
Cursor.``lastrowid

此只读属性提供上次修改行的rowid。仅当您INSERT使用该execute() 方法发出声明时才设置它。对于调用以外的操作INSERT或executemany()调用的操作，lastrowid设置为None。
Cursor.``description

此只读属性提供上一个查询的列名称。为了与Python DB API保持兼容，它为每个列返回一个7元组，其中每个元组的最后六个项是None。它是为SELECT没有任何匹配行的语句设置的。

行对象

类sqlite3.``Row

甲 Row实例用作一个高度优化的 row_factory用于Connection对象。它试图模仿其大部分功能中的元组。它支持按列名和索引，迭代，表示，等式测试和映射访问len()。如果两个Row对象具有完全相同的列并且它们的成员相等，则它们相等。在2.6版中更改：添加了迭代和相等（hashability）。keys（）此方法返回列名称元组。在查询之后，它立即成为每个元组的第一个成员Cursor.description。版本2.6中的新功能。

假设我们初始化一个表，如上面给出的例子：

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

SQLite和Python类型

介绍

SQLite的原生支持以下几种类型：NULL，INTEGER， REAL，TEXT，BLOB。

因此可以将以下Python类型发送到SQLite而不会出现任何问题：

Python类型	SQLite类型
`None`	`NULL`
`int`	`INTEGER`
`long`	`INTEGER`
`float`	`REAL`
`str` （UTF8编码）	`TEXT`
`unicode`	`TEXT`
`buffer`	`BLOB`

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

SQLite类型	Python类型
`NULL`	`None`
`INTEGER`	`int`或者`long`，视大小而定
`REAL`	`float`
`TEXT`	取决于`text_factory`， `unicode`在默认情况下
`BLOB`	`buffer`

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

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

如前所述，SQLite本身仅支持一组有限的类型。要在SQLite中使用其他Python类型，必须使它们适应 SQLite 的sqlite3模块支持的类型之一：NoneType，int，long，float，str，unicode，buffer之一。

该 sqlite3模块使用Python对象自适应，如中所述 PEP 246为此。要使用的协议是PrepareProtocol。

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

让你的对象适应自己

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

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

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

from pysqlite2 import dbapi2 as 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]

注册可调用的适配器

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

注意

要适应的类型/类必须是新式类，即它必须 object作为其基础之一。

from pysqlite2 import dbapi2 as 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类型。现在让我们假设我们想要存储datetime.datetime不在ISO表示中的对象，而是存储为Unix时间戳。

from pysqlite2 import dbapi2 as 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]

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

编写适配器允许您将自定义Python类型发送到SQLite。但是为了使它真正有用，我们需要使Python到SQLite到Python的往返工作。

输入转换器。

我们回到Point课堂上吧。我们存储了x和y坐标，这些坐标通过分号分隔为SQLite中的字符串。

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

注意

无论您将值发送到SQLite的哪种数据类型，都始终使用字符串调用转换器函数。

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

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

隐含地通过声明的类型
明确地通过列名称

两种方式在模块函数和常量一节中描述，在常量PARSE_DECLTYPES和条目中PARSE_COLNAMES。

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

from pysqlite2 import dbapi2 as 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)

# 注册适配器
sqlite3.register_adapter(Point, adapt_point)

# 注册转换器
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) 使用声明的类型
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) 使用列名
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()

默认适配器和转换器

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

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

这样，在大多数情况下，您可以使用Python中的日期/时间戳，而无需任何额外的摆弄。适配器的格式也与实验SQLite日期/时间函数兼容。

以下示例演示了这一点。

from pysqlite2 import dbapi2 as 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])

控制事务

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

所以，如果你是一个事务中，并发出这样的命令，，，该模块将隐式执行该命令之前提交。这样做有两个原因。首先，其中一些命令在事务中不起作用。另一个原因是pysqlite需要跟踪事务状态（如果事务处于活动状态）。CREATE TABLE ...``VACUUM``PRAGMAsqlite3

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

如果需要自动提交模式，则设置isolation_level为“无”。

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

`sqlite3`有效使用

使用快捷方式

使用非标准的execute()，executemany()并且 executescript()该方法的 Connection对象，您的代码可以更简洁，因为你不必创建（通常是多余的）书面Cursor明确对象。而是Cursor 隐式创建对象，这些快捷方法返回游标对象。这样，您可以执行SELECT语句并直接使用Connection对象上的单个调用对其进行迭代。

from pysqlite2 import dbapi2 as sqlite3

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

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

# 创建表
con.execute("create table person(firstname, lastname)")

# 填表
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# 打印表格内容
for row in con.execute("select firstname, lastname from person"):
    print row

# 使用虚拟WHERE子句不让SQLite删除快捷方式表。
print "I just deleted", con.execute("delete from person where 1=1").rowcount, "rows"

按名称而不是索引访问列

该 sqlite3模块的一个有用功能是内置 sqlite3.Row类，设计用来作为一个row factory.。

用这个类包装的行既可以通过索引（如元组）访问，也可以通过名称不区分大小写：

from pysqlite2 import dbapi2 as sqlite3

con = sqlite3.connect("mydb")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
    assert row[0] == row["name_last"]
    assert row["name_last"] == row["nAmE_lAsT"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

使用连接作为上下文管理器

使用Python 2.5或更高版本，连接对象可以用作自动提交或回滚事务的上下文管理器。如果发生异常，则回滚事务; 否则，事务提交：

from __future__ import with_statement
from pysqlite2 import dbapi2 as 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 catched
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

常见问题

多线程

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

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

Built with Sphinx using a theme provided by Read the Docs

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

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

sqlite3有效使用

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

`sqlite3`有效使用