Generate an SQL statement to insert multiple lines into a MySQL database at once using Python

Question

I am trying to generate a variable that I could use to insert multiple lines into a MySQL database or save to a file.

As I am new to python my mind is now buzzing with all the new concepts I am having to learn and I'm looking for a little reassurance that my approach is a good one.

The SQL syntax for inserting multiple items into a table is this:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

This is the code I have:

import shelve

shelf = shelve.open('test.db', flag='r')

sql = "INSERT INTO 'myTableName' ( "
sql += ", ".join(shelf.itervalues().next())
sql = " ) VALUES "
for s in shelf: 

  sql += "( "
  sql += ', '.join(['\'%s\'' % ( value ) for (key, value) in shelf[s].items()])
  sql += " ),"

shelf.close()

print sql

It so nearly works (it has a trailing , instead of a ; on the final sql addition) but I'm sure there must be a more concise approach. Can you tell me what it is?

Answer 1

Don't generate SQL from string concatenation. Use SQL parameters instead:

cursor = connection.cursor()

cursor.executemany('INSERT INTO 'tablename' ('column1', 'column2') VALUES (%s, %s)',
        [sub.values() for sub in shelf.values()])

The database can then reuse the INSERT statement (it prepares a query plan for it), the database client layer will handle quoting for you, and you prevent SQL injection attacks to boot.