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()--游标滚动