mysql - Using a Python dict for a SQL INSERT statement

Question

I am trying to use a dict to do a SQL INSERT. The logic would basically be:

INSERT INTO table (dict.keys()) VALUES dict.values()

However, I am having a tough time figuring out the correct syntax / flow to do this. This is what I currently have:

# data = {...}
sorted_column_headers_list = []
sorted_column_values_list = []
for k, v in data.items():
    sorted_column_headers_list.append(k)
    sorted_column_values_list.append(v)
sorted_column_headers_string = ', '.join(sorted_column_headers_list)
sorted_column_values_string = ', '.join(sorted_column_values_list)

cursor.execute("""INSERT INTO title (%s) 
            VALUES (%s)""", 
            (sorted_column_headers_string, sorted_column_values_string))

From this I get a SQL exception (I think related to the fact that commas are also included in some of the values that I have). What would be the correct way to do the above?

Answer 1

I think the comment on using this with MySQL is not quite complete. MySQLdb doesn't do parameter substitution in the columns, just the values (IIUC) - so maybe more like

placeholders = ', '.join(['%s'] * len(myDict))
columns = ', '.join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
# valid in Python 2
cursor.execute(sql, myDict.values())
# valid in Python 3
cursor.execute(sql, list(myDict.values()))

You're not getting escaping on the columns though, so you might want to check them first....

See http://mail.python.org/pipermail/tutor/2010-December/080701.html for a more complete solution