How to generate insert statements of each row from csv in python?

Question

I am creating a script where I am reading from csv file and iterating all the rows in the csv file but i need help to generate insert statements of each row and I am also output it that into a file. So my only issue is the generate insert statements of each row. Thanks for the help.

here is my code:

import csv
openFile = open('data.csv', 'r')
csvFile = csv.reader(openFile)
header = next(csvFile)
headers = map((lambda x: '`' + x + '`'), header)
insert = 'INSERT INTO my_table (' + ", ".join(headers) + ") VALUES "
l = [*range(0, 59, 1)]
for row in csvFile:
 values = list(row[i] for i in l)
 print(values)
data = insert + "(" + ", ".join(values) + ");"
print(data)
createOnFile = open("data.txt","w+")
createOnFile.write(data)
createOnFile.close()
openFile.close()

What is happening? How does it differ from what you're expecting to happen? Are you getting an error or is the output just now what you expect? — Macattack, Dec 18 '20 at 19:42
in your `for` loop you overwrite `values` each time so you'd only see the very last row of values. you probably need to use a list to store each `values` you generate — Macattack, Dec 18 '20 at 19:59
`values` is still just getting assigned to the current row. each iteration of the for loop you overwrite your previous `values` without storing it anywhere — Macattack, Dec 18 '20 at 21:00

score 0 · Accepted Answer · answered Dec 18 '20 at 21:02

0

Take this as an example you can work from. You need to store each row where you'v reformatted the values then combine all of this.

csvFile = [
        ['columnA', 'columnB'],
        ['a1', 'b1'],
        ['a2', 'b2'],
]
header = csvFile[0]
headers = map((lambda x: '`' + x + '`'), header)
insert = 'INSERT INTO my_table (' + ", ".join(headers) + ") VALUES "
values = []
for row in csvFile[1:]:
    values.append('(' + ','.join(map(lambda x: '"%s"' % x,  row)) + ')')

data = insert + ', '.join(values)
print(data)

answered Dec 18 '20 at 21:02

Macattack

1,917
10
15

I get `TypeError: '_csv.reader' object is not subscriptable` at `for row in csvFile[1:]:` – Crazy Mike Dec 18 '20 at 22:06
that was a change so i could access it as a list instead of an actual csv reader like yours. You could just do like you had in your original code and have `for row in csvFile:` rather than slice it like i did – Macattack Dec 18 '20 at 22:16
@CrazyMike My code was more meant to be an example of what I meant for you to do, not a direct copy and paste solution. – Macattack Dec 18 '20 at 22:17
for some reason the `'`' + x + '`')` are cause some syntax error in microsoft sql server to run the statement. do you think I can remove the tilda `` and or commas? – Crazy Mike Dec 18 '20 at 22:50
Sounds like you should switch the ` to `"` what you're trying to quote are the "identifiers" - https://stackoverflow.com/a/2901499/1609219 – Macattack Dec 18 '20 at 23:44
I changed it.. however it said `incorrect syntax near ,` but it does not show the red scribbly line where is it? – Crazy Mike Dec 20 '20 at 00:05

How to generate insert statements of each row from csv in python?

1 Answers1