sqlite之从csv文件向数据库中插入数据

sqlite之从csv文件向数据库中插入数据

import csv
import sqlite3
file_path = "I:/db/flight4.csv"

创建数据库

con = sqlite3.connect("I:/db/flight.db")
# con.execute("drop table flight_info")
# con.commit()

c = con.cursor()
create_table = """
        create table flight_info
        (sno varchar(20),
        start_time date,
        end_time date,
        start_p varchar(15),
        end_p varchar(15),
        money int,
        remaining int,
        time varchar(10)
        );
"""
c.execute(create_table)
con.commit()

读取数据到数据库中

flie_reader = csv.reader(open(file_path,'r',encoding="utf-8"),delimiter=',')
# header = next(file_path,None)  #去掉属性栏
for row in flie_reader:
    data=[]
    for index in range(8):
        data.append(row[index])
    print(data)
    c.execute("insert into flight_info values(?,?,?,?,?,?,?,?);",data)
con.commit()

['3U8892', '07-22 06:40', '09:40', '北京', '成都', '500', '20', '3h0']
['CZ9530', '07-22 06:40', '09:40', '北京', '成都', '500', '20', '3h0']
['CA4194', '07-17 07:00', '10:10', '北京', '成都', '500', '82', '3h10']
['KN5215', '08-19 07:00', '09:55', '北京', '成都', '778', '77', '2h55']
['MU8727', '07-21 07:00', '09:55', '北京', '成都', '500', '58', '2h55']
['ZH4194', '07-17 07:00', '10:10', '北京', '成都', '500', '34', '3h10']
['HU7147', '07-17 07:25', '10:45', '北京', '成都', '500', '71', '3h20']
['CA1405', '07-17 07:30', '10:35', '北京', '成都', '500', '60', '3h5']
['ZH1405', '07-17 07:30', '10:35', '北京', '成都', '500', '31', '3h5']
['3U1355', '07-18 07:55', '11:10', '北京', '成都', '500', '96', '3h15']
['CZ6161', '07-18 07:55', '11:10', '北京', '成都', '500', '77', '3h15']
['MF1623', '07-18 07:55', '11:10', '北京', '成都', '500', '21', '3h15']
['3U1355', '07-19 08:00', '11:10', '北京', '成都', '500', '72', '3h10']
['3U8819', '07-19 06:40', '12:15', '北京', '重庆', '880', '71', '5h35']
['CZ9487', '07-19 06:40', '12:15', '北京', '重庆', '500', '50', '5h35']
['G58699', '07-19 06:40', '12:15', '北京', '重庆', '500', '84', '5h35']
['MU3977', '07-19 06:40', '12:15', '北京', '重庆', '500', '79', '5h35']
['ZH3867', '07-19 06:40', '12:15', '北京', '重庆', '500', '33', '5h35']
['3U8515', '07-17 06:50', '09:45', '北京', '重庆', '500', '92', '2h55']
['CZ9301', '07-17 06:50', '09:45', '北京', '重庆', '500', '82', '2h55']
['EU7057', '07-17 06:50', '09:45', '北京', '重庆', '500', '81', '2h55']
['G58651', '07-17 06:50', '09:45', '北京', '重庆', '500', '88', '2h55']
['3U1677', '07-19 06:55', '09:55', '北京', '重庆', '500', '38', '3h0']
['CZ8817', '08-25 06:55', '09:55', '北京', '重庆', '730', '78', '3h0']
['MF1963', '10-06 06:55', '09:55', '北京', '重庆', '850', '62', '3h0']
['MU6681', '07-30 06:55', '10:00', '北京', '重庆', '950', '91', '3h5']
['CA1437', '08-29 07:00', '09:45', '北京', '重庆', '690', '45', '2h45']
['G58216', '07-17 07:00', '09:45', '北京', '重庆', '500', '33', '2h45']
['MU6681', '07-30 07:00', '10:00', '北京', '重庆', '950', '72', '3h0']
['SC1437', '07-17 07:00', '09:45', '北京', '重庆', '500', '44', '2h45']
['3U2235', '07-17 06:55', '09:50', '北京', '长沙', '500', '41', '2h55']
['CA1343', '08-27 06:55', '09:35', '北京', '长沙', '720', '98', '2h40']
['CZ4349', '07-19 06:55', '09:50', '北京', '长沙', '630', '88', '2h55']
['G56970', '07-17 06:55', '09:35', '北京', '长沙', '500', '40', '2h40']
['G58255', '07-17 06:55', '09:35', '北京', '长沙', '500', '28', '2h40']

查询flight_info表格

output = c.execute("select * from flight_info")
rows = output.fetchall()
for row in rows:
    out=[]
    for index in range(len(row)):
        out.append(row[index])
    print(out)

['3U8892', '07-22 06:40', '09:40', '北京', '成都', 500, 20, '3h0']
['CZ9530', '07-22 06:40', '09:40', '北京', '成都', 500, 20, '3h0']
['CA4194', '07-17 07:00', '10:10', '北京', '成都', 500, 82, '3h10']
['KN5215', '08-19 07:00', '09:55', '北京', '成都', 778, 77, '2h55']
['MU8727', '07-21 07:00', '09:55', '北京', '成都', 500, 58, '2h55']
['ZH4194', '07-17 07:00', '10:10', '北京', '成都', 500, 34, '3h10']
['HU7147', '07-17 07:25', '10:45', '北京', '成都', 500, 71, '3h20']
['CA1405', '07-17 07:30', '10:35', '北京', '成都', 500, 60, '3h5']
['ZH1405', '07-17 07:30', '10:35', '北京', '成都', 500, 31, '3h5']
['3U1355', '07-18 07:55', '11:10', '北京', '成都', 500, 96, '3h15']
['CZ6161', '07-18 07:55', '11:10', '北京', '成都', 500, 77, '3h15']
['MF1623', '07-18 07:55', '11:10', '北京', '成都', 500, 21, '3h15']
['3U1355', '07-19 08:00', '11:10', '北京', '成都', 500, 72, '3h10']
['3U8819', '07-19 06:40', '12:15', '北京', '重庆', 880, 71, '5h35']
['CZ9487', '07-19 06:40', '12:15', '北京', '重庆', 500, 50, '5h35']
['G58699', '07-19 06:40', '12:15', '北京', '重庆', 500, 84, '5h35']
['MU3977', '07-19 06:40', '12:15', '北京', '重庆', 500, 79, '5h35']
['ZH3867', '07-19 06:40', '12:15', '北京', '重庆', 500, 33, '5h35']
['3U8515', '07-17 06:50', '09:45', '北京', '重庆', 500, 92, '2h55']
['CZ9301', '07-17 06:50', '09:45', '北京', '重庆', 500, 82, '2h55']
['EU7057', '07-17 06:50', '09:45', '北京', '重庆', 500, 81, '2h55']
['G58651', '07-17 06:50', '09:45', '北京', '重庆', 500, 88, '2h55']
['3U1677', '07-19 06:55', '09:55', '北京', '重庆', 500, 38, '3h0']
['CZ8817', '08-25 06:55', '09:55', '北京', '重庆', 730, 78, '3h0']
['MF1963', '10-06 06:55', '09:55', '北京', '重庆', 850, 62, '3h0']
['MU6681', '07-30 06:55', '10:00', '北京', '重庆', 950, 91, '3h5']
['CA1437', '08-29 07:00', '09:45', '北京', '重庆', 690, 45, '2h45']
['G58216', '07-17 07:00', '09:45', '北京', '重庆', 500, 33, '2h45']
['MU6681', '07-30 07:00', '10:00', '北京', '重庆', 950, 72, '3h0']
['SC1437', '07-17 07:00', '09:45', '北京', '重庆', 500, 44, '2h45']
['3U2235', '07-17 06:55', '09:50', '北京', '长沙', 500, 41, '2h55']
['CA1343', '08-27 06:55', '09:35', '北京', '长沙', 720, 98, '2h40']
['CZ4349', '07-19 06:55', '09:50', '北京', '长沙', 630, 88, '2h55']
['G56970', '07-17 06:55', '09:35', '北京', '长沙', 500, 40, '2h40']
['G58255', '07-17 06:55', '09:35', '北京', '长沙', 500, 28, '2h40']