python数据库编程_sqlite

llcong

　　sqlite是一个轻量级的数据库，与很多大型的数据库(例如DB2,Oracle,MSSQL,MYSQL,PostgreSQL)相比，它不需要一个Server Process,因此更相像于其他的小型数据库，例如Access，而且事实上，它的作用很类似于Access，不过由于缺乏商业支持，没有像Access一样提供丰富的界面组件，相信如果需要的话，其实不是很难做的。   
  前面也用到过sqlite，例如在对pickle模块的学习，以及我所写的下载糗事百科的程序中都大量的使用到了sqlite，这里将python对sqlite的支持抽取出来专门进行讲解。   
  从python 2.5开始(前面的我就不知道了，没用过~，现在用的python 3.0，相信可以用上5年吧)，python提供了对sqlite3的内生支持，一般来说，可以在PythonDir\Dlls\文件夹下发现 sqlite3.dll和_sqlite3.pyd ，这2个文件是python支持sqlite3的基础；其次就是下面说要描述的sqlite3模块了。   
  在文档中有一个pysqlite模块，该模块和DB-API 2.0(python的DB API，其文档位于http://www.python.org/dev/peps/pep-0249)兼容。   
  其实与数据库的交互操作是很简单的，基本的操作只有2步：连接数据库，执行SQL语句。   
一、sqlite3模块入门   
1)连接数据库   
在python中连接sqlite3数据库有2种模式，一种是内存中的sqlite3数据库，一种是磁盘上的sqlite3数据库，这2种数据库的差别不仅在于方便和效率上，另外内存中的任何操作都不需要commit，这个下面再说。   
cn=sqlite3.connect(database[, timeout, isolation_level, detect_types, factory])   
该函数返回一个Connection对象；其中的database指数据库文件的地址，如果要使用内存中的数据库可以使用:memory:代替；如果有多个Connection存取同一个数据库，同一时间只有一个进程可以更改数据库(select语句不受限制)   
2)执行SQL语句   
执行SQL语句在sqlite3模块中是很容易实现的，无论是Select、还是Update、Insert、Delete，在sqlite3中的实现方法是一致的，具体如下：   
cursor=cn.cursor()   
cursor.execute(SQL)   
#cursor.execute(SQL,tuple)  当SQL语句中有参数时,可以将参数放在tuple中。   
#cursor.commit()          当涉及Update、Insert、Delete语句时需要这一条语句   
cursor.close()   
例子：
  

   

• 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 cursor if we are done with it   
  
• c.close()   
  
• 
  

  　　二、sqlite3高级操作   
1>sqlite3.register_converter(typename,callable)   
  据说是将数据库中提取出来的内容转化为python的类型，但是没看懂   
2>sqlite3.register_adapter(type,callable)   
  上面那个函数倒过来的，同样没看懂   
3>sqlite3.complete_statement(sql)   
  当sql中为一条或多条可执行的SQL语句时返回True.   
  文档中使用该语句完成了一个小型的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 = 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:                             #python 3.0中异常的写法，不会的快学   
  
• print("An error occurred:", e.args[0])   
  
•         buffer = ""   
  
•    
  
• con.close()   
  
• 
  

  　　4>Connection类   
sqlite3.Connection类很用来管理与sqlite3数据库的链接的，主要有   
4.1 Connection.isolation_level  一般设置为None就可以了，或者干脆就不要管了   
4.2 Connection.cursor()         大家很熟悉了，也不怎么需要叙述   
4.3 Connection.commit()         该方法很重要，如果你对数据库进行了更新，那么一定要使用该方法，否则其他对该数据库的链接无法察觉到你的更新；如果你更新了数据库，而又体现不出来，多半就是因为没调用这个函数了。   
4.4 Connection.rollback()       可以取消最后一次的commit   
4.5 Connection.close()          太简单了，没什么可说的   
4.6 Connection.execute(sql[, params])   
4.7 Connection.executemany(sql[, params])   
4.8 Connection.executescript(sql_script)   
4.9 Connection.create_function(name,params_num,func)   
    这个函数很有趣，可以对该Connection创建一个函数，函数名为name，函数为func，函数的参数个数为params_num   
    这个函数可以用于SQL语句中，应该说很有前途
  

   

• import sqlite3   
  
• import hashlib   
  
•    
  
• def md5sum(t):   
  
• return hashlib.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])   
  
• 
  

  　　4.10 Connection.create_aggregate(name,params_num,class)   
     和上面那个很像，不过由函数变成类了
  

   

• 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])   
  
• 
  

  　　4.11 Connection.interrupt()   
     可以从其他线程终止该Connection上的所有查询。   
4.12 Connection.row_factory   
     可以使用该属性来更改返回值的表现方式，例如返回为字典之类或更高级的方式   
     这个方法也很有发展前途。
  

   

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

  　　4.13 Connection.total_changes   
     连接的总更改条数   
5>游标对象   
sqlite.Cursor   
5.1 cursor.execute(sql[,params])   
    很easy，随便贴个例子了
  

   

• 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))   
  
•    
  
• cur.execute("select name_last, age from people where name_last=:who and age=:age",   
  
•     {"who": who, "age": age})   
  
•    
  
• print(cur.fetchone())   
  
• 
  

  　　5.2 cursor.executemany(sql[,params])   
5.3 cursor.executescript(sql_script)   
5.4 cursor.fetchone()   
5.5.cursor.fetchmany()   
5.6 cursor.fetchall()   
5.7 cursor.rowcount   
    这个熟悉不太好使，建议大家不要用它   
5.8 cursor.description   
    只用于select语句，返回一行的列名，为了Python DB API兼容，返回值为1*7的数组，但事实上后面的六个数为None   
6>Row   
  这个类很有趣，很实用，可以让行对象看起来像列表，又像字典，用法很简单，只需要将Connection的row_factory设置为Row对象即可，例子如下
  

   

• 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()   
  
•    
  
•    
  
•    
  
• >>> conn.row_factory = sqlite3.Row   
  
• >>> c = conn.cursor()   
  
• >>> c.execute('select * from stocks')   
  
•    
  
• >>> r = c.fetchone()   
  
• >>> type(r)   
  
•    
  
• >>> r   
  
• (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001)   
  
• >>> 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   
  
• 
  

  　　注意：该例子是对大小写不敏感的。
　　
　　
　　原文：http://blog.iyunv.com/jj_liuxin/archive/2008/12/22/3584448.aspx