11.13.sqlite3— DB-API 2.0 interface for sqlite databases

New in version 2.5.

sqlite是一个提供轻量级磁盘文件数据库支持的C库，这样我们就不再需要独立的数据库服务器进程，并且不需要使用基于各种数据库服务器的非标准SQL查询语言的变种。应用程序可以使用sqlite作为内部的数据存储。也可以先利用它来编制应用系统的原型，之后再发布到大型的数据库服务器上去，如Postgresql、Oracel。

sqlite3库由Gerhard H?ring编写，提供了一个完全符合Python DB-API 2.0标准规范（PEP 249）的sql接口。

要使用此模块，你必须首先创建一个代表数据库的Connection对象。这个例子中我们将数据保存在文件/tmp/example中。

conn = sqlite3.connect('/tmp/example')

你也可以通过使用特定的文件名‘:memory:’来在内存中创建一个数据库。例如

':memory:')

一旦你拥有了一个Connection，你可以通过调用execute()方法来创建一个用来执行sql命令的Cursor对象，

@H_502_31@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)""") # 保存变化(commit) conn.commit() # 关闭cursor c.close()

一般情况下，你的sql操作都将会要用来存储在Python变量中的值。记住千万不要使用不安全的使用字符串组装SQL查询命令的方法；它让你的应用程序容易受sql注入攻击的侵害。

相对的，我们可以使用DB-API中的参数替换功能。在你需要使用一个值的地方写上一个”?”作为占位符，然后为Cursor的execute()方法的第二个参数提供一个封闭了相应数据的tuple就可以了。（我们可能也可以使用其它的占位符，如”%”或”:1”等，信赖于具体的数据访问模块）示例如下：

# 千万不要这样来使用Never do this -- insecure! symbol = 'IBM' c.execute("... where symbol = '%s'" % symbol) # 应该使用这种方式Do this instead t = (symbol,) c.execute('select * from stocks where symbol=?',t) # Larger example for t in [('2006-03-28','BUY',128)">'IBM',1000,45.00),('2006-04-05',128)">'MSOFT',72.00),128)">'2006-04-06',128)">'SELL',500,53.00),]: c.execute('insert into stocks values (?,?,?)',t)

要在执行一个SELECT语句后检索数据，一种方法，你可以将cursor视作一个迭代器iterator，调用cursor的fetchone()方法来取行每一行，或者另一种方法，你可以调用fetchall()来得到所有行的一个list。

按迭代器方式的示例如下：

>>> c = conn.cursor() >>> c.execute('select * from stocks order by price') >>> for row in c: ... print row ... (u'2006-01-05',u'RHAT',35.14) (u

See also(参阅)
http://code.google.com/p/pysqlite/
The pysqlite web page – sqlite3 is developed externally under the name “pysqlite”.
http://www.sqlite.org
The sqlite web page; the documentation describes the Syntax and the available data types for the supported sql dialect.
PEP 249 - Database API Specification 2.0
PEP written by Marc-André Lemburg.

11.13.1. Module functions and constants模板函数及常量

• l sqlite3.PARSE_DECLTYPES

这个常量被connect函数作为detect_types参数进行使用。

通过设置它可以让sqlite3模块对返回的每列的声明类型进行分析。它将会分析出声明类型的第一个单词，举例说，例如“integer primary key”，将会分析出“integer”，再或者如“number(10)”则会分析出“number”。然后对于此列，它将会查找转换字典，并使用针对此类型的转换函数。

1. l sqlite3.PARSE_COLNAMES

通过设置它可以让sqlite接口分析返回的每个列名。它将会查找格式为[mytype]的字符串，然后决定”mytype”是否是此列的类型。它还会尝试在转换字典中查找是否存在“mytype”条目，如果找到就使用转换函数来返回列的值。被查找的列表只是在cursor.description中列名的第一个单词，例如，你在sql语句中使用'as "x[datetime]"'，则列名将会是”x”。

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

打开一个针对sqlite database文件的connection。你可以使用”:memory:”来打开一个驻留在内存中的数据库connection。

当一个数据库由多个connection访问，其中一个进程修改了数据库，sqlite数据将会自动加锁直到事务被commit。Timeout参数指定connection在抛出异常之前将会因为锁定等待多长时间。默认值是5.0（5秒）。

Isolation_level参数，请参见Connection对象的theConnection.isolation_level.

sqlite本身只支持TEXT，INTEGER，FLOAT，BLOB，以及NULL类型。若你要使用其它类型，你只能自己加上相应的支持。通过Detect_types参数，以及使用由模块级函数register_converter注册的定制转换函数可以比较容易地做到这些。

Detect_types默认为0（意味着，没有类型检测），你可以为它设置为PARSE_DECLTYPES，PARSE_COLNAMES，或它们的任意组合，来打开类型检测。

默认情况下，sqlite3模块使用Connection类来处理所有的连接调用。但是，你也可以声明并使用Connection的子类，通过将你自己的类传入参数factory，可以让connect使用你自定义的类。

查阅本手册的SQLite and Python types一节来获得更详细信息。.

sqlite3模块在内部使用一个语句缓存来避免不必要的sql语句分析开支。若你想要明确声明connection可以在缓存中存放多少语句，可以设置cached_statements参数。在现在的实现中默认值为100。

l register_converter( typename,callable)

注册一个可调用对象，将数据库中的bytestring（字节串）转换为一个定制的Python类型。所有类型名与参数typename相同的值在从数据库中被检索出来后都会进行转换调用。通过connect函数中指定的detect_types可以协定类型检测的工作方式。注意typename参数与你的查询语句中的类型必须大小写一致。

l register_adapter( type,68); font-size:16px; line-height:24px">注册一个可执行对象来将Python类型转换成某一个sqlite支持的类型。参数callable所表示的可执行对象，如一个函数将会接受一个Python类型的参数，并且必须返回一个以下类型的值：int,long,float,str(UTF-8),Unicode,以及buffer。

l complete_statement( sql)

如果sql变量中的字符串是一语sql语句，或多句由分号分隔的sql语句，则返回True。它不会对sql语法进行检测，只是检测字符串本身是否完整，是否由分号正确地分隔。

我们可以用这个函数来构建一个sqlite的外壳，示例如下：

# 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,e: print "An error occurred:",e.args[0] buffer = "" con.close()

l enable_callback_tracebacks( flag)

默认情况下，你不会在用户自定义函数中得到任何跟踪回调，如聚合（aggregate），转换（converter），或授权(authorizer)回调。如果你想调试它们，你可以用True参数调用此函数。之后，你可以在sys.stderr上得到所有的跟踪回调。再次用False调用此函数可以禁止此功能。

11.13.2 Connection对象

classsqlite3.Connection

一个Connection实例有以下的属性与方法：

l isolation_level

获取或设置当前的隔离级别。对于autocommit模式没有相应的隔离级别，其它模式可以是"DEFERRED","IMMEDIATE" 或 "EXLUSIVE"中的一个值。请参见``Controlling Transactions'',第11.13.5节。

l Connection.cursor( [cursorClass])

cursor方法只接受一个可选参数cursorClass。如果给出此参数，则必须是从sqlite3.cursor中继承的定制类。

l Connection.commit()

这个方法是提交当前事务.如果你提交这个方法,那所有你从上次提交commit()之后的在这个数据库连接上进行的操作都将是无效的。如果你疑惑为什么你在数据库中没有见到的你写入的数据,那么就请检查你是否忘了调用这个方法。

lConnection.rollback()这种方法回滚自上次调用commit（）后任何对数据库的更改。

lConnection.close()

这将关闭数据库连接。请注意，这并不会自动调用commit（）。如果你只是关闭不调用commit（）首先，您的修改将会丢失的数据库连接！

l Connection.execute( sql,[parameters])

这是一个非标准的快捷调用，它将会通过调用cursor()方法创建一个中间cursor对象，之后使用给出的参数调用此cursor对象的execute方法。

l Connection.executemany( sql,68); font-size:16px; line-height:24px">这是一个非标准的快捷调用，它将会通过调用cursor()方法创建一个中间cursor对象，之后使用给出的参数调用此cursor对象的executemany方法。

l Connection.executescript( sql_script)

这是一个非标准的快捷调用，它将会通过调用cursor()方法创建一个中间cursor对象，之后使用给出的参数调用此cursor的executescript方法。

l Connection.create_function( name,num_params,func)

创建一个用户自定义函数，稍后你可以在sql语句中通过定义的函数名name直接使用。Num_params参数定义了函数接收的参数个数，func是Python中的可执行对象，也就是这个可以在sql语句中调用的自定义函数。

此函数可以返回任何sqlite支持的类型：Unicode,string,integer,buffer以及NONE。示例如下：

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(?)",128)">"foo",)) print cur.fetchone()[0]

l Connection.create_aggregate( name,aggregate_class)

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

参数aggregate_class必须实现一个step方法，此方法接收由num_params定义个数的参数，还必须实现一个finalize方法用来返回聚合的最终结果。Finalize方法可以返回任意sqlite支持的类型：Unicode,buffer,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",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]

l Connection.create_collation( name,68); font-size:16px; line-height:24px">使用一个指定的名字与Python可执行对象创建一个数据整理器。此可执行对象接受两个字符串参数。若第一个参数在排序上小于（低于）第二个参数，则它必须返回-1，相等则返回0，大小（高于）则返回1。请注意它只控制排序（sql中的ORDER子句），所以你的比较不会影响到其它的sql操作。

同时，所有参数必须是Python中的bytestring类型，通常都按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",),128)">"b",)]) cur.execute("select x from test order by x collate reverse") in cur: print row con.close()

要移除一个整理器，用参数None作为可执行对象传入即可。

con.create_collation(l Connection.interrupt( )

你可以从一个其它的线程上调用此方法来取消可能正在此connection上的执行中查询。查询将会被取消，查询的调用者会得到一个异常。

l Connection.set_authorizer( authorizer_callback)

这是一个回调例程寄存器。在每次访问数据库表中的一个列时都会调用此回调。若允许访问，则回调函数必须返回sqlITE_OK，若访问应该被取消，同时产生一个错误，则返回sqlITE_DENY，而返回sqlITE_IGNORE则表示将此列的值作为NULL来对待。这些常量都由在sqlite3模块中定义。

传递给callable对象的第一个参数表示将要对什么类型的操作进行授权。第二、三个参数是表示相应的参数还是None则都信赖于第一个参数的值。第四个参数是数据库的名字，如果可能提供的话。如果通过内部的触发器或视图来访问数据，则第五个参数为触发器或视图的名字，如果直接从外部的sql访问数据，则为None。

请参考sqlite文档来详细了解第一个参数可能的值，以及第二、三个参数如何信赖于第一个参数。所有必需的常量都由sqlite3模块提供。

l Connection.row_factory

你可以将此属性改变为一个callable，此callabel按tuple类型接收原始的row数据，然后返回你想要的结果集。使用此方法，你可以实现许多返回结果集的高级方法，如可以返回一个对象用来按列名存取列数据。

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"

如果返回一个tuple还不够，你还想通过列名访问列数据，你可考虑设置row_factory使用调试优化的sqlite3.Row类型。Row提供了按列索引或列名（大小写敏感）访问数据的能力，且几乎不需要额外的内存开销。大部分情况下，使用Row会比自定义基于字典的方法甚至于使用db_row的方法要好得多。

l Connection.text_factory

使用此属性来控制我们可以从TEXT类型得到什么对象。默认情况下，这个属性被设置为Unicode，sqlite3模块将会为TEXT返回Unicode对象。若你想返回bytestring对象，可以将它设置为str。

因为效率的原因，还有一个只针对非ASCII数据，返回Unicode对象，其它数据则全部返回bytestring对象的方法。要激活它，将此属性设置为sqlite3.OptimizedUnicode。

你也可以将它设置为任意的其它callabel，接收一个bytestirng类型的参数，并返回结果对象。

import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() # Create the table con.execute("create table person(lastname,firstname)") 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(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,128)">"utf-8",128)">"ignore") cur.execute("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),)) row = cur.fetchone() assert type(row[0]) == unicode # sqlite3 offers a built-in optimized text_factory that will return bytestring # objects,255)">if the data is in ASCII only,and otherwise return unicode objects con.text_factory = sqlite3.OptimizedUnicode cur.execute("Germany",)) row = cur.fetchone() assert type(row[0]) == str

l Connection.total_changes

返回自从connection被打开后所有被修改、新增或删除的行数合计。

l Connection.iterdump

这是一个迭代器,内容是以sql文本保存的数据库. 这在为了将来恢复而需要保存一个内存数据库时是非常有用的.这个函数实现的功能与在sqlite3 shell中的使用.dump命令同样的作用。

New in version 2.6.

示例：

# 将数据库 existing_db.db 转化成 sql文本保存到 file dump.sql
import sqlite3,os

con = sqlite3.connect('existing_db.db')
with open('dump.sql',128)">'w') as f:
    for line in con.iterdump():

11.13.3. Cursor Objects

lclasssqlite3.Cursor

一个cursor实例拥有如下的属性与方法：

l Cursor.execute( sql,[parameters])

执行一个sql语句。sql语句可以是参数化的（用占位符来代替具体的字面值）。sqlite3支持两种类型的占位符：问号与命名。

下面的例子展示了如果使用问号：

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

下面的例子展示了如何使用命名：

,{"who": who,128)">"age": age})
print cur.fetchone()

方法execute()只能执行一条单独的sql语句。如果你想要执行超过一条语句，将会引发一个warning。可以使用executescript()方法来在一次调用中执行多条sql语句。

l Cursor.executemany( sql,seq_of_parameters)

使用seq_of_parameters中的值来多次调用sql语句，seq_of_parameters可以是序列或映射。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()

下面是一个使用迭代生成器的例子：

import sqlite3

def char_generator():
    import string
    for c in string.letters[:26]:
        yield (c,)

con = sqlite3.connect("create table characters(c)")

cur.executemany("select c from characters")
print cur.fetchall()

l Cursor.executescript( sql_script)

这是一个非标准的可以在一次调用方便地执行多句sql语句的方法。它首先发出一个COMMIT语句，之后执行sql语句。

参数sql_script可以是一个bytestring或Unicode编码的字符串。

示例如下：

":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,lastname,age
    );

    create table book(
        title,author,published
    );

    insert into book(title,published)
    values (
        'Dirk Gently''s Holistic Detective Agency','Douglas Adams',1987
    );
    """)

lCursor.fetchone()

获取查询的结果集的下一行，返回一个单一序列，或无时没有更多的资料。

lCursor.fetchmany([size=cursor.arraysize]

提取了一个查询结果集下一行，返回一个列表。如果没有结果则返回空的列表。

行数size参数，用来指定每次调用所获取行数。如果没有给出，cursor的arraysize的确定行数牵强。该方法应尽量获取尽可能多的行由参数指定的大小。如果这不是可能的，因为指定的行数没有用，可能会返回较少的行

lCursor.fetchall()

提取所有的查询结果（其余）的行，返回一个列表。请注意cursor的arraysize的属性可影响此操作的性能。如果没有提取到结果则返回一个空的list。

l Cursor.rowcount

虽然sqlite3模块中的cursor类实现了这个属性，但是数据库引擎自己对于“行数”统计的能力还是要强得多。

对于DELETE语句，当你在DELETE FROM 语句中没有使用任何条件的话，rowcount为0，

对于executemany语句，所有进行修改的行数都会被总计到rowcount中。

作为Python DB API规范的要求，在没有executeXX()方法被执行的情况下，或者在最近的一次操作中不能确定行数，则rowcount属性值将会是-1。

这包括SELECT的情况。对于SELECT语句，rowcount总是为-1，因为我们在所有行被取回前不能确定总的行数。

l Cursor.lastrowid
这个只读属性提供的最后修改的行的ROWID。如果您发出一个INSERT语句使用Execute（）方法时此属性被赋值。除此外的其他操作被调用时，lastrowid设置为none。

l Cursor.description
这个只读属性提供了一次查询中的列名。为了保持与Python DB API的兼容，对于每一列它返回一个7-tuple 但是每个元组tuple的倒数6个item都是None。

It is set for SELECT statements without any matching rows as well.