python操作sqlite3

python操作sqlite3，写了点代码，留下备用。

#!/usr/bin/env python  
# -*- coding: UTF-8 -*-  
 
import os 
import sys 
import codecs 
import sqlite3 
 
#set default encoding as UTF-8  
reload(sys) 
sys.setdefaultencoding('utf-8') 
 
#connect_database  
#if db exists,connect it  
#else create db file  
def connect_db(db_name): 
    conn = sqlite3.connect(db_name) 
    return conn 
 
#close connect   
def close_db(conn): 
    conn.close() 
 
 
#insert values into table  
def insert_values(conn,table_name): 
     
    cu = conn.cursor() 
    try: 
        cu.execute("insert into %s values (\'%s\',\'%d\')" %(table_name,'xxx',12)) 
    except sqlite3.Error,e: 
        print 'insert value failed:',e.args[0] 
        return 
    conn.commit() 
 
 
#query values from table  
def query_values(conn,table_name): 
    cu = conn.cursor() 
 
    try: 
        cu.execute('select * from %s' %table_name) 
    except sqlite3.Error,e: 
        print 'query data failed:',e.args[0] 
        return 
    return cu.fetchall() 
 
 
#create table  
def create_table(conn,table_name): 
    cu = conn.cursor() 
    try: 
        cu.execute('create table %s (Name,Age integer) ' %table_name) 
    except sqlite3.Error,e: 
        print 'create table failed:',e.args[0] 
        return 
    conn.commit() 
 
#drop table if exist  
def drop_table(conn,table_name): 
    cu = conn.cursor() 
     
    try: 
        cu.execute('drop table if exists %s' %table_name) 
    except suqlit3.Error,e: 
        print 'drop table failed:',e.args[0] 
        return 
    conn.commit() 
 
#main function  
if __name__ == '__main__': 
    db_name = './Per.db' 
    conn = connect_db(db_name) 
 
    table_name = 'per' 
    drop_table(conn,table_name) 
    create_table(conn,table_name) 
 
     
    close_db(conn) 

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import os
import sys
import codecs
import sqlite3

#set default encoding as UTF-8
reload(sys)
sys.setdefaultencoding('utf-8')

#connect_database
#if db exists,connect it
#else create db file
def connect_db(db_name):
 conn = sqlite3.connect(db_name)
 return conn

#close connect
def close_db(conn):
 conn.close()


#insert values into table
def insert_values(conn,table_name):
 
 cu = conn.cursor()
 try:
  cu.execute("insert into %s values (\'%s\',\'%d\')" %(table_name,'xxx',12))
 except sqlite3.Error,e:
  print 'insert value failed:',e.args[0]
  return
 conn.commit()


#query values from table
def query_values(conn,table_name):
 cu = conn.cursor()

 try:
  cu.execute('select * from %s' %table_name)
 except sqlite3.Error,e:
  print 'query data failed:',e.args[0]
  return
 return cu.fetchall()


#create table
def create_table(conn,table_name):
 cu = conn.cursor()
 try:
  cu.execute('create table %s (Name,Age integer) ' %table_name)
 except sqlite3.Error,e:
  print 'create table failed:',e.args[0]
  return
 conn.commit()

#drop table if exist
def drop_table(conn,table_name):
 cu = conn.cursor()
 
 try:
  cu.execute('drop table if exists %s' %table_name)
 except suqlit3.Error,e:
  print 'drop table failed:',e.args[0]
  return
 conn.commit()

#main function
if __name__ == '__main__':
 db_name = './Per.db'
 conn = connect_db(db_name)

 table_name = 'per'
 drop_table(conn,table_name)
 create_table(conn,table_name)

 
 close_db(conn)

 

1. 数据库连接对象

conn是一个数据库的连接对象，它可以有以下操作：

         commit()--事务提交
         rollback()--事务回滚
         close()--关闭一个数据库连接
         cursor()--创建一个游标

2. 游标对象

所有sql语句的执行都要在游标对象下进行。

cu = conn.cursor()#这样定义了一个游标。

游标对象有以下的操作：

        execute()--执行sql语句
        executemany--执行多条sql语句
        close()--关闭游标
        fetchone()--从结果中取一条记录
        fetchmany()--从结果中取多条记录
        fetchall()--从结果中取出多条记录
        scroll()--游标滚动