sqlite3 -- DB-API 2.0 SQLite数据库接口[Python参考库翻译]
http://blog.csdn.net/sharkw/archive/2007/12/22/1958139.aspx 13.13 sqlite3 -- DB-API 2.0 interface for SQLite databases SQLite是一个提供轻量级的基于磁盘的数据库的C语言库,它不需要一个独立的服务进程,并且允许你使用一种非标准的SQL查询语言来访问数据库。其他程序也可以使用SQLite来管理内部数据存储。你也可以使用SQLite来构建应用程序原型,然后把代码迁移到一个更大的数据库,比如PostgreSQL或者Oracle。 pysqlite是Gerhard Haring写的,提供一个与PEP 249描述的DB-API 2.0规格说明书兼容的SQL接口。 要使用这个模块,你必须现创建一个代表数据库的连接对象(Connection object)。如下的数据将被存储在/tmp/example文件中: conn = sqlite2.connect('/tmp/example') 你还可以提供一个特殊的名称":memory:"在内存中来创建一个数据库。 一旦你建立了一个连接(Connection),你就可以创建一个游标对象(Cursor object),并且调用它的execute()方法来执行SQL命令: c = conn.cursor() #Creat table c.execute('''creat table stocks (data 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)''') 通常你的SQL操作会用到你的Python程序中的变量的值。你不应该使用Python的字符串操作符来集合你的查询,因为这样是不安全的;它使你的程序容易首道SQL注入攻击。 替代方法是使用DB-API的参数交换。在你需要使用一个变量的时候,使用"?"作为一个占位符,然后提供一个包含变量的元组作为油表对象的execute()方法的第二个参数。(其他的数据库模块可能使用不同的占位符,比如"%s"或者":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','IBM',1000,45.00), ('2006-04-05','MSOFT',72.00), ('2006-04-06','SELL',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',35.140000000000001) (u'2006-03-28',u'IBM',45.0) (u'2006-04-06',u'SELL',53.0) (u'2006-04-05',u'MSOFT',72.0) >>> See Also: http://www.pysqlite.org pysqlite的网站。 http://www.sqlite.org SQLite的网站;相关文档描述了语法以及对其支持的可爱的SQL的可用的数据类型。 PEP 249,Database API Specification 2.0 Marc-André Lemburg写的PEP。 13.13.1 模块函数和常量 这个常量是用来作为connect函数的参数detect_types的值使用的。 设置它使sqlite3模块解析返回的每一列的声明类型。他会解析出声明类型中的第一个词,i. e. 对于'integer primary key',将会解析出'integer'。然后对于这一列,他会在转换字典中查找并且使用对这种类型注册的转换函数。注意:转换器的名称是大小写敏感的! PARSE_COLNAMES 这个常量是用来作为connect函数的参数detect_types的值使用的。 设置它使SQLite接口解析返回的每一列的列名字。他会在其中查找一个这样书写的字符串[mytype],然后将‘mytype’作为这一列的类型。他将尝试在转换字典中查找‘mytype’的入口,然后使用在字典中找到的转换函数来返回值。在cursor.description中找到的名字只是这个列的第一个字,i. e. 如果你在SQL中使用这样的语句:'as "x [datetime]"',那么将会解析出直到列名字中第一个空格出现时的任何字符——这个列的名字只是一个简单的'x'。 connect(database[,timeout,isolation_level,detect_types,factory]) 当一个数据库被多重连接时(when a database is accessed by multiple connections),并且有一个进程更改了数据库,这个SQLite数据库将会被锁住直到交易被执行。timeout参数指定这个连接在抛出一个异常之前等待的时间。默认的timeout参数是5.0(5秒钟)。 对于isolation_level参数,请查看在13.13.2中描述的连接对象(Connection objects)的isolation_level属性。 SQLite本身只支持TEXT,INTEGER,FLOAT,BLOB和NULL类型。如果你希望使用其他类型,你需要添加自己的支持。detect_types参数,以及使用定制的利用模块级的register_converter函数注册的converters,可以让你很容易实现它。 detect_types默认为0(i. e. off,没有类型检测),你可以设置其为PARSE_DECLTYPES和PARSE_COLNAMES的任意组合来将类型检测打开。 sqlite3默认模块使用其Connection类来实现connection调用。然而,你可以编写Connection类的一个子类,然后将你的类提供给factory参数来使用你自己的类建立连接。 详细信息请参阅本手册的 13.13.4 章节。 register_converter(typename,callable) register_adapter(type,callable) complete_statement(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() enable_callback_tracebacks(flag) 默认情况下,在用户定义的functions,aggregates,converters,authorizer等中你不会得到任何跟踪信息。如果你想要调试它们,你就可以用True作为flag参数的值来调用这个函数。之后,你就可以在sys.stderr中得到从回调中返回的跟踪信息。使用False来再次关闭这个功能。 13.13.2 连接对象(Connection Objects) isolation_level cursor([cursorClass]) execute(sql,[parameters]) executemany(sql,[parameters]) executescript(sql_script) creat_function(name,num_params,func) 这个函数返回SQLite支持的任意类型:unicode,str,int,buffer和None。 Example: import sqlite3 import md5 def md5sum(t): return md5.md5(t).hexdigest() con = sqlite3.connect(":memory:") con.creat_function("md5",1,md5sum) cur = con.cursor() cur.execute("select mdt(?)",("foo",)) print cur.getchone()[0] creat_aggregate(name,aggregate_class) 一个聚合类必须提供一个step方法,它接受参数的数量num_params,和一个finalize方法,它返回最终的聚合结果。 finalize方法可以返回SQLite支持的任何类型:unicode,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.creat_aggregate("mysum",MySum) cur = con.cursor() cur.execute("create table test(i)") cru.execute("insert into test(i) values (1)") cru.execute("insert into test(i) values (2)") cur.execute("select mysum(i) from test") print cur.fetchone()[0] creat_collation(name,callable) 注意:调用器将会按照Python的字节码来接受参数,这些参数通常的编码是UTF-8。 下面的例子显示了一个定制的对"the wrong way"进行排序的校对: 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("creat 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) 调用器的第一个参数表明什么类型的操作要进行审定。第二个参数和第三个参数根据第一个参数,会使参数或者None。如果可用的话,第四个参数是数据库的名字("main","temp"等等)。第五个参数是对试图进行的访问负责的最内的(inner-most)触发器的名字或者视图,或者为None如果这个访问直接来自于SQL代码。 关于第一个参数的可能的取值,以及依赖于第一个参数的第二个和第三个参数的含义,请参阅SQLite的文档。所有的必须的常量在sqlite3模块中都是可用的。 row_factory 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"] 如果返回一个元组不能满足需要,而你想要对列的基于名字的访问,那么你可以考虑将row_factory设置为高度优化的sqlite3.Row类型。Row既提供基于索引的,也提供基于名字的对列的访问,却几乎不需要消耗额外的内存。它可能会比你自己定制的基于字典的访问甚至基于db_row的解决方法还要好。 text_factory 你也可以将其设置为任何接受一个单一字节串参数并且返回最终对象的调用器。 参考下面的解释实例代码: 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 type(row[0]) == str # the bytestrings will be encoded in UTF-8,unless you stored garbage in the # database ... assert row[0] == AUSTRIA.encode("uft-8") # we can also implement a custom text_factory ... # here we implement one that will ignore Unicode characters that cannot be # decoded form 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]) == unicode # pysqlite offers a builtin 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 ?",)) row = cur.fetchone() assert type(row[0]) == unicode cur.execute("select ?",("germany",)) row = cur.fetchone() assert type(row[0]) == str total_changes 13.13.3 游标对象(Cursor Objects) execute(sql,[parameters]) 这个例子展示了怎样使用问号风格的参数: import 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() 下面的例子展示了如何使用命名风格: import 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() execute()方法执行一条单一SQL语句。如果你试图用其执行多余一条的语句,将会抛出一个警告(Warning)。你可以是用executescript()来再一次调用中执行多条SQL语句。 executemany(sql,seq_of_parameters) 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() 下面是一个使用生成器的小例子: import 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 charaters(c)") cur.executemany("insert into characters(c) values (?)",char_generator()) cur.execute("select c from characters") print cur.fetchall() executescript(sql_script) 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 ); """) rowcount 对于select语句,rowcount总是None,以为在所有行都被取出之前,我们无从得知一个查询所产生的行数。 对于delete语句,如果你使用DELETE FROM而没有任何条件的话,SQLite将rowcount作为0报告。 根据Python DB API Spec的要求,这个rowcount属性“是-1,如果在这个游标上还没有executeXX()被执行过,或者其最后一册操作的rowcount对于接口不可知。” 13.13.4 SQLite and Python types 13.13.4.1 Introduction 从而,如下的Python类型可以直接存入SQLite,而不会出现任何问题: Python type 下面是默认情况下SQLite类型转换为Python类型的对照: SQLite type sqlite3模块的类型系统可以通过两种方式扩展:可以通过对象适配器(object adaptation)将补充的Python类型存储在一个SQLite数据库中,也可以让sqlite3模块通过转换器把SQLite类型转换为不同的Python类型。 13.13.4.2.2 使用适配器在SQLite数据库中储存补充的Python类型 sqlite3模块使用Python对象适配器,在PEP 246中对此有描述。使用的约定为PrepareProtocol。 有两种方法能够使sqlite3模块将定制的Python类型适配为被支持的类型。 13.13.4.2.1 让你的对象自适应 class Point(object): def __init__(self,x,y): self.x,self.y = x,y 现在你希望将point储存于SQLite中的一个单独的列中。首先你需要选择一个支持的类型来替代point。让我们只是用一个字符串,用分号分割两个坐标。然后你需要为你的类添加一个__conform__(self,protocol)方法,它必须返回转换后的值。参数protocol将会是PrepaerProtocol。 import sqlite3 class Point(object): def __init__(self,y def __conform__(self,protocol): if protocol is sqlite3.PrepareProrocol: 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] 13.13.4.2.2 注册一个适配器调用 注意:进行适配的类型/类必须是一种新样式(new-style)的类,也即,它必须有对象作为其基类。 import sqlite3 class Point(object): def __init__(self,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,)) print cur.fetchone()[0] 对于Python的内置的datetime.date和datetime.datetime类型,sqlite3模块有两个默认的适配器。现在我们假设要按照Unix的时间戳而不是ISO替代来存储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 = cur.cursor() now = datetime.datetime.now() cur.execute("select ?",(now,)) print cur.fetchone()[0] 13.13.4.3 把SQLite值转换为定制的Python类型 进入转换器。 让我们再次回到Point类。我们使用分号分割的字符串在SQLite中储存了x和y坐标。 首先,我们会定义一个转换函数,接受这个字符串作为参数,并且从这个字符串构建一个Point对象。 注意:转换函数总是以字符串作为参数被调用,而不管你以什么方式将数据存入SQLite。 注意:转换函数对大小写敏感。 def convert_point(s): x,y = map(float,s.split(";")) return Point(x,y) 现在你需要让sqlite3模块知道,你从数据库中取出的实际上是一个point。有两种方法可以做到这一点: <!--[if !supportLists]-->l <!--[endif]-->通过声明隐式进行 <!--[if !supportLists]-->l <!--[endif]-->通过列名显式进行 两种方法都在“模块常量(Module Constants)”中描述,13.13.1章,在常量PARSE_DECLTYPES和PARSE_COLNAMES的记录中。 下面的例子说明了两种方法: import sqlite3 class Point(object): def __init__(self,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) # 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 (?)",)) cur.execute("select p from test") print "with declared types:",cur.fetchone()[0] cur.close() con.close() ################# # 2) 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 (?)",)) cur.execute('select p as "p [point]" from test') print "with column names:",cur.fetchone()[0] cur.close() cur.close() 13.13.4.4 默认的适配器和转换器 默认的转换器以"date"名字对应datetime.date,和"timestamp"名字对应datetime.datetime注册。 这样,在大多数情况下,你可以从Python中使用date/timestamps,而不用做任何多余的工作。转换器的格式也与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",type(row[0]) print "current_timestamp",type(row[1]) 13.13.5 交易控制 所以,如果你正处于一个交易之中,并且提交一个像CREATE TABLE ...,VACUUM,PRAGMA这样的命令,sqlite3模块将在执行这个名之前,隐式的提交者个交易。有两种原因要这样做。一是,这些命令中的一部分不能在交易中工作。二是pysqlite需要对交易保持跟踪(一个交易是否活跃)。 你可以控制pysqlite隐式的执行那种"BEGIN"语句,通过connect调用的isolation_level参数,或者通过连接的isolation_level属性。 如果你希望是自动执行模式,就把isolation_level设置为None。 否则,就采用默认设置,这将会产生普通的"BEGIN"语句,或者将其设置为SQLite支持的isolation级别:DEFERRED,IMMEDIATE或者EXCLUSIVE。 因为sqlite3模块需要对交易保持跟踪,所以你不能在你的SQL中使用OR ROLLBACK或者ON CONFLICT ROLLBACK。取而代之,你需要捕捉IntegrityError错误,并且自己调用连接的rollback方法。 13.13.6 高效的使用pysqlite import sqlite3 persons = [ ("Hugo","Boss"), ("Calvin","Klein") ] con = sqlite3.connect(":memory:") # Create ghe table con.execute("create table person(firstname,lastname)") # Fill the table con.executemany("insert into person(firtname,lastname) values (?,persons) # Print the table contents for row in con execute("select firstname,lastname from person"): print row # Using a dummy WHERE clause to not let SQLite take the shortcut table deletes. print "I just deleted",con.execute("delete from person where 1=1").rowcount,"rows") 13.13.6.2 通过名字而不是索引来访问列 使用这个类包装的行,既可以通过索引(有点像元组)来访问,也可以通过大小写敏感的名字来访问。 import 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_1AsT"] assert row[1] == row["age"] assert row[1] == row["Age"] 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/sharkw/archive/2007/12/22/1958139.aspx (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |