python基础学习-python操作sqlite数据库

'''
python使用sqlite数据库
'''
import sqlite3
con = sqlite3.connect("./test.db")#在磁盘上创建数据库
# con = sqlite3.connect(":memory:")#在内存中创建数据库
cu=con.cursor()#创建一个游标
returnCu = cu.execute("select count(*) from sqlite_master where type='table' and name='user'")
returnTuple = returnCu.fetchone()
#如果表存在则删除表
if returnTuple[0]==1:
cu.execute("drop table user")
#创建表
cu.execute("create table user(id integer primary key,username varchar(20),password varchar(20))")
#插入数据
cu.execute("insert into user values(?,?,?)",(1,"xiaobing","123"))
cu.execute("insert into user values(?,?,?)",(2,"xiaoming","123"))
con.commit()#提交
#查询表数据
returnCu = cu.execute("select * from user")
for (userid,username,password) in returnCu:
print userid,username,password
#修改数据
cu.execute("update user set password=? where id=?",("123456",1))
con.commit()
returnCu = cu.execute("select * from user where id=?",(1,))
getUserCu = returnCu.fetchone()
print getUserCu[2]
#删除数据
cu.execute("delete from user where id=?",(2,))
con.commit()
returnCu = cu.execute("select * from user")
for (userid,username,password) in returnCu:
print userid,username,password
con.close()