mk8efz mk8efz - 2 years ago 149
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)


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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download