sqlite3 遵循Python PDB-API 2.0 接口规范的SQLite库(1)
marlboro027
|
1#
marlboro027 发表于 2007-04-03 01:26
sqlite3 遵循Python PDB-API 2.0 接口规范的SQLite库(1)最近在学习Python,数据库访问是必不可少的部分,就想通过SQLite来进行这方面的学习。 sqlite3 -- DB-API 2.0 interface for SQLite databases New in version 2.5 13.13 sqlite3 SQLite是一个提供轻量级磁盘文件数据库支持的C库,这样我们就不再需要独立的数据库服务器进程,并且不需要使用基于各种数据库服务器的非标准SQL查询语言的变种。应用程序可以使用SQLite作为内部的数据存储。也可以先利用它来编制应用系统的原型,之后再发布到大型的数据库服务器上去,如PostgreSQL、Oracel。 Pysqlite库由Gerhard Häring编写,提供了一个完全符合Python DB-API 2.0标准规范(PEP249)的SQL接口。 要使用此模块,你必须首先创建一个代表数据库的Connection对象。这个例子中我们将数据保存在文件/tmp/example中。 conn = sqlite3.connect('/tmp/example') 你也可以使用特定的名字’:memory:’来在内存中创建一个数据库。 一旦你拥有了一个Connection,你可以通过调用execute()方法来创建一个用来执行SQL命令的Cursor对象, 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)""") 一般情况下,你的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', '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语句后检索数据,一种方法,你可以将cursor视作一个迭代器,调用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'BUY', u'RHAT', 100, 35.140000000000001) (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) >>> 13.13.1 模块级的函数与常量 l PARSE_DECLTYPES 这个常量被connect函数作为detect_types参数进行使用。通过设置它可以让sqlite3模块对返回的每列的声明类型进行分析。它将会分析出声明类型的第一个单词,也就是说,例如“integer primary key”,将会分析出“integer”。然后对于此列,它将会查找转换字典,并使用针对此类型的转换函数。请注意转换名字是大小写敏感的! l PARSE_COLNAMES 这个常量被connect函数作为detect_types参数进行使用。通过设置它可以让SQLite接口分析返回的每个列名。它将会查找格式为[mytype]的字符串,然后决定”mytype”是否是此列的类型。它还会尝试在转换字典中查找是否存在“mytype”条目,如果找到就使用转换函数来返回列的值。被查找的列表只是在cursor.description中列名的第一个单词,例如,你在SQL语句中使用”x [datetime]”,则列名将会是”x”。 l connect( database[, timeout, isolation_level, detect_types, factory]) 找开一个针对SQLite database文件的connection。你可以使用”:memory:”来打开一个驻留在内存中的数据库connection。 当一个数据库由多个connection访问,其中一个进程修改了数据库,SQLite数据将会自动加锁直到事务被commit。Timeout参数指定connection在抛出异常之前将会因为锁定等待多长时间。默认值是5.0(5秒)。 Isolation_level参数,请参见13.13.2节中connection对象的isolation_level属性。 SQLite本身只支持TEXT,INTEGER,FLOAT,BLOB,以及NULL类型。若你要使用其它类型,你只能自己加上相应的支持。通过Detect_types参数,以及使用由模块级函数register_converter注册的定制转换函数可以比较容易地做到这些。 Detect_types默认为0(意味着,没有类型检测),你可以为它设置为PARSE_DECLTYPES,PARSE_COLNAMES,或它们的任意组合,来打开类型检测。 默认情况下,SQLite3模块使用Connection类来处理所有的连接调用。但是,你也可以声明并使用Connection的子类,通过将你自己的类传入参数factory,可以让connect使用你自定义的类。请参考本手册13.13.4节寻找更多的细节。 SQLite3模块在内部使用一个语句缓存来避免不必要的SQL语句分析开支。若你想要明确声明connection可以在缓存中存放多少语句,可以设置cached_statements参数。在现在的实现中默认值为100。 l register_converter( typename, callable) 注册一个可调用对象,将数据库中的bytestring转换为一个定制的Python类型。所有类型名与参数typename相同的值在从数据库中被检索出来后都会进行转换调用。通过connect函数中指定的detect_types可以协定类型检测的工作方式。注意typename参数与你的查询语句中的类型必须大小写一致。 l register_adapter( type, callable) 注册一个可执行对象来将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调用此函数可以禁止此功能。 13.13.2 Connection对象 一个Connection实例有以下的属性与方法: l isolation_level 获取或设置当前的隔离级别。对于autocommit模式没有相应的隔离级别,其它模式可以是"DEFERRED", "IMMEDIATE" 或 "EXLUSIVE"中的一个值。请参见``Controlling Transactions'', 第13.13.5节。 l cursor( [cursorClass]) cursor方法只接受一个可选参数cursorClass。如果给出此参数,则必须是从sqlite3.cursor中继承的定制类。 l execute( sql, [parameters]) 这是一个非标准的快捷调用,它将会通过调用cursor()方法创建一个中间cursor对象,之后使用给出的参数调用此cursor对象的execute方法。 l executemany( sql, [parameters]) 这是一个非标准的快捷调用,它将会通过调用cursor()方法创建一个中间cursor对象,之后使用给出的参数调用此cursor对象的executemany方法。 l executescript( sql_script) 这是一个非标准的快捷调用,它将会通过调用cursor()方法创建一个中间cursor对象,之后使用给出的参数调用此cursor的executescript方法。 l create_function( name, num_params, func) 创建一个用户自定义函数,稍后你可以在SQL语句中通过定义的函数名name直接使用。Num_params参数定义了函数接收的参数个数,func是Python中的可执行对象,也就是这个可以在SQL语句中调用的自定义函数。 此函数可以返回任何SQLite支持的类型:Unicode, string, integer, long, float, 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(?)", ("foo",)) print cur.fetchone()[0] l create_aggregate( name, num_params, aggregate_class) 创建一个用户自定义的聚合函数。 参数aggregate_class必须实现一个step方法,此方法接收由num_params定义个数的参数,还必须实现一个finalize方法用来返回聚合的最终结果。Finalize方法可以返回任意SQLite支持的类型:Unicode, string, integer, long, float, 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", 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] l create_collation( name, callable) 使用一个指定的名字与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",), ("b",)]) cur.execute("select x from test order by x collate reverse") for row in cur: print row con.close() 要移除一个整理器,用参数None作为可执行对象传入即可。 con.create_collation("reverse", None) l interrupt( ) 你可以从一个其它的线程上调用此方法来取消可能正在此connection上的执行中查询。查询将会被取消,查询的调用者会得到一个异常。 l set_authorizer( authorizer_callback) 此例程注册一个回调。在每次访问数据库表中的一个列时都会调用此回调。若允许访问,则回调函数必须返回SQLITE_OK,若访问应该被取消,同时产生一个错误,则返回SQLITE_DENY,而返回SQLITE_IGNORE则表示将此列的值作为NULL来对待。这些常量都由在sqlite3模块中定义。 传递给callable对象的第一个参数表示将要对什么类型的操作进行授权。第二、三个参数是表示相应的参数还是None则都信赖于第一个参数的值。第四个参数是数据库的名字,如果可能提供的话。如果通过内部的触发器或视图来访问数据,则第五个参数为触发器或视图的名字,如果直接从外部的SQL访问数据,则为None。 请参考SQLite文档来详细了解第一个参数可能的值,以及第二、三个参数如何信赖于第一个参数。所有必需的常量都由sqlite3模块提供。 l 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 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 pysqlite always return bytestrings ... con.text_factory = str 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("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]) == 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 ?", (AUSTRIA,)) row = cur.fetchone() assert type(row[0]) == unicode cur.execute("select ?", ("Germany",)) row = cur.fetchone() assert type(row[0]) == str l total_changes 返回自从connection被打开后所有被修改、新增或删除的行数合计。 |