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

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

Answer 2

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 3

Always good answers here, but in Python 3, you should write the following:

placeholder = ", ".join(["%s"] * len(dict))
stmt = "insert into `{table}` ({columns}) values ({values});".format(table=table_name, columns=",".join(dict.keys()), values=placeholder)
cur.execute(stmt, list(dict.values()))

Don't forget to convert dict.values() to a list because in Python 3, dict.values() returns a view, not a list.

Also, do NOT pour the dict.values() in stmt because it tears a quote out of a string by joining it, which caused MySQL error in inserting it. So you should always put it in cur.execute() dynamically.

Answer 4

table='mytable'    
columns_string= '('+','.join(myDict.keys())+')'    
values_string = '('+','.join(map(str,myDict.values()))+')'    
sql = """INSERT INTO %s %s
     VALUES %s"""%(table, columns_string,values_string)

Answer 5

I tried @furicle's solution but it still inputs everything as a string - if your dict is a mixed one then this may not work as you would want it to. I had a similar issue and this is what I came up with - this is only a query builder and you could use it (with changes) to work with any database of your choice. Have a look!

def ins_query_maker(tablename, rowdict):
    keys = tuple(rowdict)
    dictsize = len(rowdict)
    sql = ''
    for i in range(dictsize) :
        if(type(rowdict[keys[i]]).__name__ == 'str'):
            sql += '\'' + str(rowdict[keys[i]]) + '\''
        else:
            sql += str(rowdict[keys[i]])
        if(i< dictsize-1):
            sql += ', '
    query = "insert into " + str(tablename) + " " + str(keys) + " values (" + sql + ")"
    print(query) # for demo purposes we do this
    return(query) #in real code we do this

This is crude and still needs sanity checks, etc, but it works as intended. for a dict:

tab = {'idnumber': 1, 'fname': 'some', 'lname': 'dude', 'dob': '15/08/1947', 'mobile': 5550000914, 'age' : 70.4}

running the query I get the following output

results of query generated by the suite

Answer 6

This code worked for me (Python 3):

fields = (str(list(dictionary.keys()))[1:-1])
values = (str(list(dictionary.values()))[1:-1])
sql = 'INSERT INTO Table (' + fields + ') VALUES (' + values + ')'
cursor.execute(sql)

It does rely on the dictionary outputting its keys and values in the same order. I'm unclear if this is always true :)

Answer 7

I'm a little late to the party but there is another way that I tend to prefer since my data is usually in the form of a dict already. If you list the bind variables in the form of %(columnName)s you can use a dictionary to bind them at execute. This partially solves the problem of column ordering since the variables are bound in by name. I say partially because you still have to make sure that the columns & values portion of the insert are mapped correctly; but the dictionary itself can be in any order (since dicts are sort of unordered anyway)

There is probably a more pythonic way to achieve all this, but pulling the column names into a list and working off it ensures we have a static ordering to build the columns & values clauses.

data_dict = {'col1': 'value 1', 'col2': 'value 2', 'col3': 'value 3'}
columns = data_dict.keys()
cols_comma_separated = ', '.join(columns)
binds_comma_separated = ', '.join(['%(' + item + ')s' for item in columns])

sql = f'INSERT INTO yourtable ({cols_comma_separated}) VALUES ({binds_comma_separated})'

cur.execute(sql, data_dict)

Now whether or not it is a good idea to dynamically build your columns & values clause like this is a topic for a SQL injection thread.

Answer 8

I used this thread for my usage and tried to keep it much simpler

ins_qry = "INSERT INTO {tablename} ({columns}) VALUES {values};" .format(
            tablename=my_tablename,
            columns=', '.join(myDict.keys()),
            values=tuple(myDict.values())
        )
cursor.execute(ins_qry)

Make sure to commit the data inserted, either using db_connection.commit() and use cursor.lastrowid, if you need the primary key of the inserted row

Answer 9

columns = ', '.join(str(x).replace('/', '_') for x in row_dict.keys())

values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in row_dict.values())

sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % ("tablename", columns, values)

applicable for python3

Answer 10

This works for me

cursor.execute("INSERT INTO table (col) VALUES ( %(col_value) )", 
        {'col_value': 123})

Answer 11

if you have list in which there are number of dictionaries for example: lst=[d1,d2,d3,d4]

then below one will worked for me:

for i in lst:
    placeholders = ', '.join(['%s'] * len(i))
    columns = ', '.join(i.keys())
    sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
    cursor.execute(sql,list(i.values()))
conn.commit()

Note:Dont ever forget to commit otherwise you wont be able to see columns and values inserted in table

Answer 12

What about:

keys = str(dict.keys())
keys.replace('[', '(')
keys.replace(']', ')')
keys.replace("'",'')

vals = str(dict.values())
vals.replace('[', '(')
vals.replace(']', ')')

cur.execute('INSERT INTO table %s VALUES %s' % (keys, vals))

For python 3:

keys = str(dict.keys())[9:].replace('[', '').replace(']', '')
vals = str(dict.values())[11:].replace('[', '').replace(']', '')

...