赞
踩
Python sqlite3数据库是一款非常小巧的内置模块,它使用一个文件存储整个数据库,操作十分方便,相比其他大型数据库来说,确实有些差距。但是在性能表现上并不逊色,麻雀虽小,五脏俱全,sqlite3实现了多少sql-92标准,比如说transaction、trigger和复杂的查询等。
描述
Python的数据库模块有统一的接口标准,所以数据库操作都有统一的模式(假设数据库模块名为db):
1. 用db.connect创建数据库连接,假设连接对象为conn
2. 如果该数据库操作不需要返回结果,就直接使用conn.execute查询,根据数据库事物隔离级别的不同,可能修改数据库需要conn.commit
3. 如果需要返回查询结果则用conn.cursor创建游标对象cur,通过cur.execute查询数据库,cursor方法有fetchall、fetchone、fetchmany返回查询结果,根据数据库事物隔离级别不同,可能修改数据库需要coon.commit
4. 关闭cur.close
sqlite3基本操作用例
#coding=utf-8
importsqlite3
conn= sqlite3.connect("sqlite.db") #创建sqlite.db数据库
print ("open database success")
conn.execute("drop table IF EXISTS student")
query= """create table IF NOT EXISTS student(
customer VARCHAR(20),
produce VARCHAR(40),
amount FLOAT,
date DATE
);"""conn.execute(query)print ("Table created successfully")#在表中插入数据
'''方法1'''
#data = '''INSERT INTO student(customer,produce,amount,date)#VALUES("zhangsan","notepad",999,"2017-01-02")'''#conn.execute(data)#data = '''INSERT INTO student(customer,produce,amount,date)#VALUES("lishi","binder",3.45,"2017-04-05")'''#conn.execute(data)#conn.commit()
'''方法2'''statement= "INSERT INTO student VALUES(?,?,?,?)"data= [("zhangsan","notepad",999,"2017-01-02"),("lishi","binder",3.45,"2017-04-05")]
conn.executemany(statement, data)
conn.commit()
curson= conn.execute("select * from student")
conn.commit()print(curson)
rows=curson.fetchall()print(rows)
conn.close()
sqlite3 csv->db->csv
'''将csv数据导入数据库'''
importsysimportcsvimportsqlite3#解析csv文件
defparsecsvFile(filepath):
header=None
data=[]
with open(filepath,'r') as csvfile:
filereader=csv.reader(csvfile)
header=next(filereader)#print (header)
for row infilereader:
data.append(row)#print (data)
returnheader,data#使用sqlite3写数据库
definitdb(header, data):
conn= sqlite3.connect("sqlite.db")print ("connect database success")
conn.execute("drop table IF EXISTS student")
conn.commit()
query= '''create table IF NOT EXISTS student(
Supplier Name VARCHAR(32),
Invoice Number VARCHAR(16),
Part Number VARCHAR(16),
Cost VARCHAR(16),
Purchase Date DATE);'''conn.execute(query)
conn.commit()
statement= "INSERT INTO student VALUES(?,?,?,?,?)"conn.executemany(statement, data)
conn.commit()
curson= conn.execute("select * from student")
conn.commit()print(curson)
rows=curson.fetchall()print(rows)
conn.close()returnrows#根据数据库内容写csv文件
defwirtecsvfile(writefilepath, header, data):
with open(writefilepath,'a+') as writefile:
writer= csv.writer(writefile, delimiter=",")
writer.writerow(header)for row indata:
writer.writerow(row)if __name__ == "__main__":
readfilepath= sys.argv[1]
writefilepath= sys.argv[2]
header,data=parsecsvFile(readfilepath)
rows=initdb(header, data)
wirtecsvfile(writefilepath, header, rows)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。