当前位置:   article > 正文

python与sqlite3_Python之sqlite3

sqlite3 python wihie open

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)

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/盐析白兔/article/detail/891479
推荐阅读
相关标签
  

闽ICP备14008679号