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

创建数据库

首先来创建数据库，以及数据库中的表。在使用connect()连接数据库后，我就可以通过定位指针cursor，来执行SQL命令：

import sqlite3

# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")

c = conn.cursor()

# create tables
#不需要返回结果,无需cursor.execute
#c.execute('''CREATE TABLE category---------)
conn.execute('''CREATE TABLE category
      (id int primary key, sort int, name text)''')
#不需要返回结果,无需cursor.execute
#c.execute('''CREATE TABLE book---------)
conn.execute('''CREATE TABLE book
      (id int primary key, 
       sort int, 
       name text, 
       price real, 
       category int,
       FOREIGN KEY (category) REFERENCES category(id))''')
# 此处不需要返回结果，用conn.execute()即可
# save the changes
conn.commit()
c.close()
# close the connection with the database
conn.close()

插入数据

import sqlite3

conn = sqlite3.connect("test.db")

books = [(1, 1, 'Cook Recipe', 3.12, 1),
            (2, 3, 'Python Intro', 17.5, 2),
            (3, 2, 'OS Intro', 13.6, 2),
           ]
#不需要返回结果,无需cursor.execute
# execute "INSERT" 
conn.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")

# using the placeholder
conn.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])

# execute multiple commands 执行多条SQL命令executemany
conn.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)

conn.commit()
conn.close()

查询

在执行查询语句后，Python将返回一个循环器，包含有查询获得的多个记录。你循环读取，也可以使用sqlite3提供的fetchone()和fetchall()方法读取记录：

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
#需要返回结果，所以需要cursor.execute()

# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())

# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())

# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    print(row)

更新与删除

你可以更新某个记录，或者删除记录：

conn = sqlite3.connect("test.db")

conn.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
conn.execute('DELETE FROM book WHERE id=2')

conn.commit()
conn.close()

你也可以直接删除整张表：

conn.execute('DROP TABLE book')

如果删除test.db，那么整个数据库会被删除

sqlite3基本操作用例

#coding=utf-8

import sqlite3

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()

cursor = conn.execute("select * from student")
#或者cursor = conn.cursor()    cursor.execute('select * from ...')
conn.commit()
print (cursor)
rows = cursor.fetchall()
print (rows)
cursor.close()
conn.close()

sqlite3 csv->db->csv

'''将csv数据导入数据库'''
import sys
import csv
import sqlite3

#解析csv文件
def parsecsvFile(filepath):
    header = None
    data = []
    with open(filepath, 'r') as csvfile:
        filereader = csv.reader(csvfile)
        header = next(filereader)
        #print (header)
        for row in filereader:
            data.append(row)
        #print (data)
    return header,data

#使用sqlite3写数据库
def initdb(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()
    cursor = conn.execute("select * from student")
        #或者cursor = conn.cursor()    cursor.execute('select * from ...')
    conn.commit()
    print (cursor)
    rows = cursor.fetchall()
    print (rows)
    conn.close()
    return rows

#根据数据库内容写csv文件
def wirtecsvfile(writefilepath, header, data):
    with open(writefilepath, 'a+') as writefile:
        writer = csv.writer(writefile, delimiter=",")
        writer.writerow(header)
        for row in data:
            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)

 

　　　　#或者cursor = conn.cursor()    cursor.execute('select * from ...')