CODEWITHSUNDEEP
pythonsqltuples
mk8efz
mk8efz

Reputation: 1424

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?

Upvotes: 17

Views: 39135

Answers (3)

snakecharmerb
snakecharmerb

Reputation: 55699

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.

Upvotes: 9

PdotNJ
PdotNJ

Reputation: 51

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.")

Upvotes: 1

Viach Kakovskyi
Viach Kakovskyi

Reputation: 1517

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)

Upvotes: 17

Related Questions