mk8efz mk8efz - 7 months ago 26
SQL Question

Insert multiple rows into DB with Python list of Tuples

I have a list of tuples:

list_ = [(1,7,3000),(1,8,3500), (1,9,3900)]


I want to update a table with multiple rows/values for a given ID (in this case ID = 1)

So:

INSERT INTO table (ID, Speed, Power) VALUES (1,7,3000),(1,8,3500),(1,9,3900)


I'm having trouble with the format - I've gotten the string down to something like this:

INSERT INTO ... VALUES ((1,7,3000),(1,8,3500),(1,9,3900))


But of course this doesn't work due to the extra parenthesis wrapped around the tuples. Any ideas for constructing a way to do this "pythonically?

Answer

Well, you need to construct the line:

INSERT INTO ... VALUES (1,7,3000), (1,8,3500), (1,9,3900)

Try that one:

rows = [(1,7,3000), (1,8,3500), (1,9,3900)]
values = ', '.join(map(str, rows))
sql = "INSERT INTO ... VALUES {}".format(values)