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

Answer 2

You want to add parameter placeholders to the query. This might get you what you need:

qmarks = ', '.join('?' * len(myDict)) qry = "Insert Into Table (%s) Values (%s)" % (qmarks, qmarks) cursor.execute(qry, myDict.keys() + myDict.values())