sqlite3基本操作 python_Python之sqlite3

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)