This recipe shows you how you might generate SQL code to insert the key-value pairs in a dictionary. The parameter table corresponds to an existing SQL table and dictionary keys correspond to the table's SQL column names. The SQL generated can then be inserted into a database (in this case MySQL) in the manner shown in exampleOfUse().
Python, 21 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict))
sql += ');'
return sql
def dictValuePad(key):
return '%(' + str(key) + ')s'
def exampleOfUse():
import MySQLdb
db = MySQLdb.connect(host='sql', user='janedoe', passwd='insecure', db='food')
cursor = db.cursor()
insert_dict = {'drink':'horchata', 'price':10}
sql = insertFromDict("lq", insert_dict)
cursor.execute(sql, insert_dict)
|
It is often the case that a developer needs to insert a variety of values into many different individual tables on a SQL server. Instead of laboriously coding up functions to produce SQL INSERT statements for each of these cases, a single dictionary can elegantly cover all of them.
Tags: database
This does not work the values that are generated in the sql output are %(key)s
The handling of dicts by map may have changed since this code was published. Here is a nice updated example: http://stackoverflow.com/questions/9336270/using-a-python-dict-for-a-sql-insert-statement