CSV文件到SQL插入语句 [英] CSV File to SQL Insert Statement

问题描述

我有一个看起来像这样的CSV文件:

I have a CSV file that looks something like this:

Date,Person,Time Out,Time Back,Restaurant,Calories,Delicious?
6/20/2016,August,11:58,12:45,Black Bear,850,Y
6/20/2016,Marcellus,12:00,12:30,Brought Lunch,,Y
6/20/2016,Jessica,11:30,12:30,Wendy's,815,N
6/21/2016,August,12:05,1:01,Brought Lunch,,Y

到目前为止，我已经设法将每一行打印到字符串列表中(例如-['Date', 'Person', 'Time Out', etc.] or ['6/20/2016', 'August', '11:58' etc.]).

So far I have managed to print each row into a list of strings (ex. - ['Date', 'Person', 'Time Out', etc.] or ['6/20/2016', 'August', '11:58' etc.]).

现在我需要再做2件事:

Now I need to do 2 more things:

1. 向每行添加一个ID标头和顺序数字字符串(例如-['ID', 'Date', 'Person', etc.] and ['1', '6/20/2016', 'August', etc.])
2. 分隔每一行，以便可以将其格式化为插入 语句，而不仅仅是让程序逐行打印出每一行(例如-INSERT INTO Table ['ID', 'Date', 'Person', etc.] VALUES ['1', '6/20/2016', 'August', etc.])

1. Add an ID header and sequential numeric string to each row (for ex. - ['ID', 'Date', 'Person', etc.] and ['1', '6/20/2016', 'August', etc.])
2. Separate each row so that they can be formatted into insert statements rather than just having the program print out every single row one after another (for ex. - INSERT INTO Table ['ID', 'Date', 'Person', etc.] VALUES ['1', '6/20/2016', 'August', etc.])

以下是到目前为止我得到的代码:

Here is the code that has gotten me as far as I am now:

import csv

openFile = open('test.csv', 'r')
csvFile = csv.reader(openFile)
for row in csvFile:
    print (row)
openFile.close()

推荐答案

尝试一下(我忽略了ID部分，因为您可以使用mySQL auto_increment)

Try this (I ignored the ID part since you can use the mySQL auto_increment)

import csv

openFile = open('test.csv', 'r')
csvFile = csv.reader(openFile)
header = next(csvFile)
headers = map((lambda x: '`'+x+'`'), header)
insert = 'INSERT INTO Table (' + ", ".join(headers) + ") VALUES "
for row in csvFile:
    values = map((lambda x: '"'+x+'"'), row)
    print (insert +"("+ ", ".join(values) +");" )
openFile.close()

这篇关于CSV文件到SQL插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！