sqlite之更新数据（根据csv文件）

sqlite之更新表中数据

import csv
import sqlite3

file = "I:/db/customer.csv"

创建数据库

con = sqlite3.connect("I:/db/customer.db")
query = """
        create table customer
        (customer varchar(20),
        product varchar(20),
        amount float,
        date date);
"""
con.execute(query)
con.commit()

插入几行数据

data = [('Rick','Notepad',2.5,'2014-01-02'),
       ('Jenny','Binder',4.15,'2014-01-15'),
       ('Seven','Printer',155.75,'2014-02-15'),
       ('Tom','Computer',679.40,'2014-02-20')]
for item in data:
    print(item)
statement = "insert into customer values(?,?,?,?)"
con.executemany(statement,data)
con.commit()

('Rick', 'Notepad', 2.5, '2014-01-02')
('Jenny', 'Binder', 4.15, '2014-01-15')
('Seven', 'Printer', 155.75, '2014-02-15')
('Tom', 'Computer', 679.4, '2014-02-20')

读取csv文件并且更行数据库中特定的行

file_reader = csv.reader(open(file,'r',encoding='utf-8'),delimiter=',')
header = next(file_reader,None)
for row in file_reader:
    data=[]
    for index in range(len(header)):
        data.append(row[index])
    print(data)
    con.execute("update customer set amount=?,date=? where customer=?;",data)
con.commit()

['4.25', '5/11/2014', 'Rick']
['6.27', '5/12/2014', 'Tom']

查询数据中的表格（看是否跟新）

cursor = con.execute("select * from customer")
rows = cursor.fetchall()
for row in rows:
    output=[]
    for index in range(len(row)):
        output.append(row[index])
    print(output)

['Rick', 'Notepad', 4.25, '5/11/2014']
['Jenny', 'Binder', 4.15, '2014-01-15']
['Seven', 'Printer', 155.75, '2014-02-15']
['Tom', 'Computer', 6.27, '5/12/2014']