63

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?

Improve this question

15 Answers 15

Reset to default
82

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

The line cursor.execute(qry, columns, myDict.values()) has a syntax error. cursor.execute(qry, myDict.values()) is the correct form.
is there any ordering problem about dict ?
The best answer ever!
@zs1986 Per python's documentation, there shouldn't be an ordering problem from the dict: If items(), keys(), values(), iteritems(), iterkeys(), and itervalues() are called with no intervening modifications to the dictionary, the lists will directly correspond.
Use columns = "`%s`" % '`,`'.join(myDict.keys()) if your column names contain hyphens or other special/reserved characters
|
31

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())
Improve this answer

5 Comments

fyi: this did not work for me, but @furicle answer did
Using myDict.keys() in this way cannot guarantee any specific order between the keys. myDict.values() might not even produce values in the corresponding order as returned by myDict.keys().
@PatrikIselind - Incorrect. See stackoverflow.com/questions/835092/…. As long as there's no intervening changes (which there can't be with the above) they will always return in the same order.
this one gets error for the fields since it will detect it as a string although SQL wants it as a variable
This results in an error: unsupported operand type(s) for +: 'dict_keys' and 'dict_values' for Python 3.8
29

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.

Improve this answer

3 Comments

Seems like a good answer - thank you. But using dict as the variable name may not be ideal... Also I get a AttributeError: 'dict_values' object has no attribute 'translate'error from pymysql. Need to do more testing.
I had to do this stackoverflow.com/a/46590820/2075003 to get rid of the AttributeError
That's great help, how would you include 'ON DUPLICATE KEYS UPDATE'? tx!
4

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.

Improve this answer

Comments

2 
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)
Improve this answer

1 Comment

Nice answer. Buy you need to scape a single quote in the join, to be more practical. '(\''+'\',\''.join(myDict.keys())+'\')' or better "('"+"','".join(myDict.keys())+"')"
2

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

Improve this answer

2 Comments

You can get away from the need to manually escape by using SQL parameter substitution, which is %s in MySQL's python wrapper.
I guess it could be done that way. This is an old implementation I came up with, but i've since moved to a NoSQL database
2

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

Improve this answer

Comments

2

When constructing queries dynamically it's important to ensure that both identifiers and values are correctly quoted. Otherwise you risk

  • SQL injection if untrusted data is processed
  • Errors if the column names require quoting (for example embedded spaces)
  • Data corruption or errors if values are incorrectly quoted (for example 2021-07-11 unquoted may be evaluated as 2003)

Quoting values is best delegated to the DB-API connector. However connector packages don't always provide a way to quote identifiers, so you may need to do this manually. MySQL uses backticks (`) to quote identifiers.

This code quotes identifiers and values. It works for MySQLdb, mysql.connector and pymysql and works for Python 3.5+.

data = {'col1': val1, 'col2': val2, ...}

# Compose a string of quoted column names
cols = ','.join([f'`{k}`' for k in data.keys()])

# Compose a string of placeholders for values
vals = ','.join(['%s'] * len(data))

# Create the SQL statement
stmt = f'INSERT INTO `tbl` ({cols}) VALUES ({vals})'

# Execute the statement, delegating the quoting of values to the connector
cur.execute(stmt, tuple(data.values()))
Improve this answer

Comments

2

This is based on other answers here, but it uses back ticks around column names for cases in which you are using reserved words as column names and it it ensures that column names only contain letters, numbers, and underscores to thwart SQL injection attacks.

I've also written a similar upsert that works the same way as the insert but which overwrites data that duplicates the primary key.

import mysql.connector
import re

cnx = mysql.connector.connect(...)

def checkColumnNames(data):
    for name in data.keys():
        assert re.match(r'^[a-zA-Z0-9_]+$',name), "Bad column name: " + name 

def insert(table, data):
    checkColumnNames(data)
    assert table, "No table specified"
    placeholders = ', '.join(['%s'] * len(data))
    columns = '`,`'.join(data.keys())
    sql = "INSERT INTO `%s` (`%s`) VALUES (%s);" % (table, columns, placeholders)
    cnx.cursor().execute(sql, list(data.values()))

def upsert(table, data):
    checkColumnNames(data)
    assert table, "No table specified"
    placeholders = ', '.join(['%s'] * len(data))
    columns = '`,`'.join(data.keys())
    updates = '`' + '`=%s,`'.join(data.keys()) + '`=%s'
    sql = "INSERT INTO `%s` (`%s`) VALUES (%s) ON DUPLICATE KEY UPDATE %s" % (table, columns, placeholders, updates)
    cnx.cursor().execute(sql, list(data.values()) + list(data.values()))

Example usage

insert("animals", {
    "id": 1,
    "name": "Bob",
    "type": "Alligator"
})
cnx.commit()
Improve this answer

Comments

0

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

Improve this answer

1 Comment

Danger! This doesn't escape the values or use ?-substitution, so this is vulnerable to SQL injection attacks.
0

This works for me

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

Comments

0

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

Improve this answer

Comments

0 
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

Improve this answer

Comments

0

Let's say our data is:

data = {
        "name" : "fani",
        "surname": "dogru",
        "number" : 271990
    }

This is my shorter version:

tablo =  "table_name"
cols = ','.join([f" {k}" for k in data.keys()])
vals = ','.join([f"'{k}'" for k in data.values()])
stmt = f'INSERT INTO {tablo} ({cols}) VALUES ({vals})'
Improve this answer

Comments

-2

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(']', '')

...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.