Insert multiple rows into DB with Python list of Tuples

Question

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 1

The idiomatic way to handle this in Python is to use the executemany method of the cursor provided by the database driver that is being used.

For example, for sqlite using the sqlite3 module in the standard library

conn = sqlite3.connect('/path/to/file.db')
cursor = conn.cursor()
sql = """INSERT INTO mytable (ID, Speed, Power) VALUES (?, ?, ?)"""
values = [(1,7,3000),(1,8,3500),(1,9,3900)]
cursor.executemany(stmt, values)

The placeholder used in the VALUES clause varies by the specific driver. The correct value can be found in the driver's documentation or by looking up the driver module's paramstyle attribute.

Using this approach instead of string interpolation / formatting or f-strings ensures that values are correctly quoted, which guards against SQL injection and other errors:

>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> date = '2020-11-23'

>>> # Correctly quoted input is returned as the selected value
>>> cur.execute("""SELECT ? AS today""", (date,)) # <- execute requires a tuple as values
<sqlite3.Cursor object at 0x7f1fa205e1f0>
>>> cur.fetchone()
('2020-11-23',)

>>> # Unquoted input is evaluated as an expression!
>>> cur.execute(f"""SELECT {date} AS today""")
<sqlite3.Cursor object at 0x7f1fa205e1f0>
>>> cur.fetchone()
(1986,)

Here's an example of an SQL injection using string formatting. Because the value "name" is not escaped, the query returns all the usernames and passwords in the table when the programmer's intention was only to return one.

NAMES = [('Alice', 'apple'),  ('Bob', 'banana'),  ('Carol', 'cherry')]

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("""CREATE TABLE users (name text, password text)""")
cur.executemany("""INSERT INTO users (name, password) VALUES (?, ?)""", NAMES)
conn.commit()
cur.execute("""SELECT name, password FROM users WHERE name = {}""".format('name'))
for row in cur.fetchall():
    print(row)

If the value were escaped correctly:

 cur.execute("""SELECT name, password FROM users WHERE name = ?""", ('name',))

no rows would be returned, defeating the attack.

Answer 2

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)