Welcome, guest | Sign In | My Account | Store | Cart

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

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.

2 comments

Nathan Rockhold 11 years, 8 months ago  # | flag

This does not work the values that are generated in the sql output are %(key)s

Carson Reynolds (author) 11 years, 8 months ago  # | flag

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

Created by Carson Reynolds on Fri, 25 Nov 2005 (PSF)
Python recipes (4591)
Carson Reynolds's recipes (1)

Required Modules

Other Information and Tasks