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?

Cataplasia

Answer 1

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)

Dexamethasone

Is this SQL injection safe? – Almund

@Almund no, it's not. Python database drivers accept lists as parameters: cursor.execute("INSERT INTO ... VALUES (%s, %s)", [(1, 2), (3, 4), (5, 6)]); you should never use string formatting/interpolation for inserting query params (and database libraries usually warn you about it multiple times) because it's not SQL injection safe. – Gesticulation

@Gesticulation one correction it should be cursor.executemany(). As shown below: cursor.executemany("INSERT INTO ... VALUES (%s, %s)", [(1, 2), (3, 4), (5, 6)]); – Ranged

Sorry, my bad. You're right: docs.python.org/3/library/… – Gesticulation

Answer 2

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.

Roxyroy

Answer 3

You can also try the following:

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
 )
 mycursor = mydb.cursor()
 sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
 val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
 ]

 mycursor.executemany(sql, val)

 mydb.commit()

 print(mycursor.rowcount, "record was inserted.")

Verina