Create a SQL statement to insert multiple rows into MySQL database at once using Python
I am trying to create a variable that I could use to insert multiple rows into a MySQL database or save to a file.
Since I am new to python my mind is now buzzing with all the new concepts I need to learn and I am looking for a little confidence that my approach is good.
SQL syntax for inserting multiple elements into a table:
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 almost works (it has final, not latest sql addition), but I'm sure there should be a more concise approach. Can you tell me what it is?
source to share
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 statement INSERT
(it prepares a query plan for it), the database client tier will handle the quotes for you and prevent SQL injection from loading.
source to share