ActiveState Code

Recipe 457661: Generate SQL for insertation into table from dictionary object

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 
 1
 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)

Discussion

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.

Sign in to comment