Python操作sqlite数据库

1 创建数据库，表，简单增删改查

#!/usr/bin/env python3
import sqlite3
con = sqlite3.connect(':memory:')
query = """CREATE TABLE sales
            (customer VARCHAR(20), 
             product VARCHAR(40),
             amount FLOAT,
             date DATE);"""
con.execute(query)
con.commit()
# Insert a few rows of data into the table
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()
# Query the sales table
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()
# Count the number of rows in the output
row_counter = 0
for row in rows:
    print(row)
    row_counter += 1
print('Number of rows: {}'.format(row_counter))

2 向表中插入新记录
Excel中的数据
Supplier Name Invoice Number Part Number Cost Purchase Date
Supplier X 001-1001 2341 $500.00 1/20/14 Supplier X 001-1001 2341$500.00 1/20/14
Supplier X 001-1001 5467 $750.00 1/20/14 Supplier X 001-1001 5467$750.00 1/20/14
Supplier Y 50-9501 7009 $250.00 1/30/14 Supplier Y 50-9501 7009$250.00 1/30/14
Supplier Y 50-9505 6650 $125.00 2002/3/14 Supplier Y 50-9505 6650$125.00 2002/3/14
Supplier Z 920-4803 3321 $615.00 2002/3/14 Supplier Z 920-4804 3321$615.00 2002/10/14
Supplier Z 920-4805 3321 $615.00 2/17/14 Supplier Z 920-4806 3321$615.00 2/24/14

import csv
import sqlite3
import sys

# Path to and name of a CSV input file
input_file = 'supplier_data.csv'

# Create an in-memory SQLite3 database
# Create a table called Suppliers with five attributes
con = sqlite3.connect('Suppliers.db')
c = con.cursor()
create_table = """CREATE TABLE IF NOT EXISTS Suppliers
                (Supplier_Name VARCHAR(20), 
                Invoice_Number VARCHAR(20),
                Part_Number VARCHAR(20),
                Cost FLOAT,
                Purchase_Date DATE);"""
c.execute(create_table)
con.commit()

# Read the CSV file
# Insert the data into the Suppliers table
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    c.execute("INSERT INTO Suppliers VALUES (?, ?, ?, ?, ?);", data)
con.commit()

# Query the Suppliers table
output = c.execute("SELECT * FROM Suppliers")
rows = output.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

3 更新表中的记录
amount date customer
4.25 5/11/2014 Richard Lucas
6.75 5/12/2014 Jenny Kim

import csv
import sqlite3
import sys

# Path to and name of a CSV input file
input_file = 'data_for_updating.csv'

# Create an in-memory SQLite3 database
# Create a table called sales with four attributes
con = sqlite3.connect(':memory:')
query = """CREATE TABLE IF NOT EXISTS sales
            (customer VARCHAR(20), 
                product VARCHAR(40),
                amount FLOAT,
                date DATE);"""
con.execute(query)
con.commit()

# Insert a few rows of data into the table
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
for tuple in data:
    print(tuple)
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()

# Read the CSV file and update the specific rows
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    con.execute("UPDATE sales SET amount=?, date=? WHERE customer=?;", data)
con.commit()

# Query the sales table
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)