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基本操作用例

1#coding=utf-8
2
3import sqlite3
4
5conn = sqlite3.connect("sqlite.db")  #创建sqlite.db数据库
6print ("open database success")
7conn.execute("drop table IF EXISTS student")
8query = """create table IF NOT EXISTS student(
9    customer VARCHAR(20),
10    produce VARCHAR(40),
11    amount FLOAT,
12    date DATE   
13);"""
14conn.execute(query)
15print ("Table created successfully")
16
17#在表中插入数据
18
19''' 方法1 '''
20#data = '''INSERT INTO student(customer,produce,amount,date)\
21#    VALUES("zhangsan","notepad",999,"2017-01-02")'''
22#conn.execute(data)
23#data = '''INSERT INTO student(customer,produce,amount,date)\
24#    VALUES("lishi","binder",3.45,"2017-04-05")'''
25#conn.execute(data)
26#conn.commit()
27
28''' 方法2 '''
29statement = "INSERT INTO student VALUES(?,?,?,?)"
30data = [("zhangsan","notepad",999,"2017-01-02"),("lishi","binder",3.45,"2017-04-05")]
31conn.executemany(statement, data)
32conn.commit()
33
34curson = conn.execute("select * from student")
35conn.commit()
36print (curson)
37rows = curson.fetchall()
38print (rows)
39conn.close()

sqlite3 csv->db->csv

1'''将csv数据导入数据库'''
2import sys
3import csv
4import sqlite3
5
6#解析csv文件
7def parsecsvFile(filepath):
8    header = None
9    data = []
10    with open(filepath, 'r') as csvfile:
11        filereader = csv.reader(csvfile)
12        header = next(filereader)
13        #print (header)
14        for row in filereader:
15            data.append(row)
16        #print (data)
17    return header,data
18
19#使用sqlite3写数据库
20def initdb(header, data):
21    conn = sqlite3.connect("sqlite.db")
22    print ("connect database success")
23    conn.execute("drop table IF EXISTS student")
24    conn.commit()
25    query = '''create table IF NOT EXISTS student(\
26        Supplier Name VARCHAR(32),
27        Invoice Number VARCHAR(16),
28        Part Number VARCHAR(16),
29        Cost VARCHAR(16),
30        Purchase Date DATE);'''
31    conn.execute(query)
32    conn.commit() 
33    statement = "INSERT INTO student VALUES(?,?,?,?,?)"
34    conn.executemany(statement, data)
35    conn.commit()
36    curson = conn.execute("select * from student")
37    conn.commit()
38    print (curson)
39    rows = curson.fetchall()
40    print (rows)
41    conn.close()
42    return rows
43
44#根据数据库内容写csv文件
45def wirtecsvfile(writefilepath, header, data):
46    with open(writefilepath, 'a+') as writefile:
47        writer = csv.writer(writefile, delimiter=",")
48        writer.writerow(header)
49        for row in data:
50            writer.writerow(row)
51    
52if __name__ == "__main__":
53    readfilepath = sys.argv[1]
54    writefilepath = sys.argv[2]
55    header,data = parsecsvFile(readfilepath)
56    rows = initdb(header, data)
57    wirtecsvfile(writefilepath, header, rows)