'''
创建数据库中的表，在表中插入数据，获取数据并计数数据
'''
import sqlite3
con=sqlite3.connect(':memory:')
table1="""CREATE TABLE sales
            (customer VARCHAR(20),
            product VARCHAR(40),
            amount FLOAT,
            date DATE);"""
con.execute(table1)
con.commit()

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

query1=con.execute("SELECT * FROM sales")
rows=query1.fetchall()

row_counter=0
for row in rows:
    print(row)
    row_counter=row_counter+1
print('Number of rows in table:%d'%(row_counter))


'''
向表中插入csv数据
'''
import sqlite3
import csv
import sys
input_file=sys.argv[1]
con=sqlite3.connect('Database1.db')
c=con.cursor()        #建立数据库连接
table2="""CREATE TABLE IF NOT EXISTS Suppliers
            (suppliers_name VARCHAR(20),
            invoice_number VARCHAR(20),
            part_number VARCHAR(20),
            cost FLOAT,
            purchase_date DATE);"""
c.execute(table2)
con.commit()

file_reader=csv.reader(open(input_file,'r'),delimiter=',')
header=next(file_reader,None)
print('csv文件中的内容是：')
for row in file_reader:
    data=[]
    for column in range(len(header)):
        data.append(row[column])
    print(data)
    c.execute("INSERT INTO Suppliers VALUES(?,?,?,?,?);",data)
con.commit()
print('写入的内容是:')

query2=c.execute("SELECT * FROM Suppliers")
rows=query2.fetchall()
for row in rows:
    print(row)


'''
使用csv数据更新表中的数据
'''
import csv
import sqlite3
import sys
input_file=sys.argv[1]
con=sqlite3.connect('Database2.db')
c=con.cursor()
table3="""CREATE TABLE IF NOT EXISTS sales
            (customer VARCHAR(20),
            product VARCHAR(40),
            amount FLOAT,
            date DATE);"""
c.execute(table3)
con.commit()

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')]
print('写入数据表中的数据是：')
for tuple in data:
    print(tuple)
statement="INSERT INTO sales VALUES(?,?,?,?);"
c.executemany(statement,data)
con.commit()

file_reader=csv.reader(open(input_file,'r'),delimiter=',')
header=next(file_reader,None)
print('将要更新进数据表中的csv数据是：')
for row in file_reader:
    data=[]
    for column in range(len(header)):
        data.append(row[column])
    print(data)
    c.execute("UPDATE sales SET amount=?,date=? WHERE customer=?;",data)
con.commit()

query3=c.execute("SELECT * FROM sales;")
rows=query3.fetchall()
print('数据表中的所有数据是：')
for row in rows:
    print(row)