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?

score 81 · Answer 1 · edited Apr 26 '20 at 14:37

81

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

edited Apr 26 '20 at 14:37

TitanFighter

4,582
3
45
73

answered Feb 12 '13 at 14:19

furicle

1,197
1
9
10

4

The line `cursor.execute(qry, columns, myDict.values())` has a syntax error. `cursor.execute(qry, myDict.values())` is the correct form. – Mehraban Feb 25 '14 at 12:39
7

is there any ordering problem about dict ? – zx1986 Dec 01 '15 at 02:22
1

The best answer ever! – Aliweb Jul 19 '16 at 22:51
2

@zs1986 Per [python's documentation](https://docs.python.org/2/library/stdtypes.html#dictionary-view-objects), 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. – Anconia Aug 11 '16 at 17:43
Use `columns = "`\``%s`\``" % '`\``,`\``'.join(myDict.keys())` if your column names contain hyphens or other special/reserved characters – NrY Oct 04 '18 at 15:29
Your asking for bugs if you depend on the order of a dictionary – FlipMcF Apr 08 '19 at 16:43
And to take this one to the next level all I need to do is figure out how to wrap the dictionary values in `''` so that I can actually get them into the database – theYnot Jan 04 '23 at 08:12

score 30 · Accepted Answer · answered Feb 17 '12 at 22:36

30

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

answered Feb 17 '12 at 22:36

g.d.d.c

46,865
9
101
111

1

fyi: this did not work for me, but @furicle answer did – Tjorriemorrie Jun 09 '14 at 14:08
1

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()`. – Patrik Iselind Mar 26 '18 at 19:36
2

@PatrikIselind - Incorrect. See https://stackoverflow.com/questions/835092/python-dictionary-are-keys-and-values-always-the-same-order. As long as there's no intervening changes (which there _can't_ be with the above) they will _always_ return in the same order. – g.d.d.c Mar 26 '18 at 23:07
2

this one gets error for the fields since it will detect it as a string although SQL wants it as a variable – Aminah Nuraini May 26 '18 at 21:05
This results in an error: unsupported operand type(s) for +: 'dict_keys' and 'dict_values' for Python 3.8 – u tyagi Feb 01 '23 at 06:57

Blaszard · Answer 3 · 2016-09-07T04:31:43.183

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.

edited Sep 07 '16 at 04:31

answered Sep 07 '16 at 04:09

Blaszard

30,954
51
153
233

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. – n1000 Sep 06 '18 at 00:25
I had to do this https://stackoverflow.com/a/46590820/2075003 to get rid of the AttributeError – n1000 Sep 06 '18 at 00:46
That's great help, how would you include 'ON DUPLICATE KEYS UPDATE'? tx! – Je Je Mar 15 '20 at 00:36

score 4 · Answer 4 · answered Jun 08 '20 at 16:20

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.

score 2 · Answer 5 · edited Jan 24 '15 at 02:51

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)

edited Jan 24 '15 at 02:51

chiwangc

3,566
16
26
32

answered Jan 24 '15 at 01:56

hsamba

29
1

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())+"')" – Pjl Mar 16 '15 at 18:36

score 2 · Answer 6 · edited Jan 04 '20 at 18:37

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

You can get away from the need to manually escape by using SQL parameter substitution, which is `%s` in MySQL's python wrapper. — vy32, Mar 24 '19 at 12:45
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 — kilokahn, Mar 25 '19 at 09:43

score 2 · Answer 7 · answered May 09 '20 at 02:42

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

snakecharmerb · Answer 8 · 2022-05-04T07:19:31.367

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

score 2 · Answer 9 · answered Jul 21 '22 at 10:54

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

score 0 · Answer 10 · edited Feb 10 '19 at 09:23

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

edited Feb 10 '19 at 09:23

Eric Aya

69,473
35
181
253

answered Feb 09 '19 at 22:38

ExtractTable.com

762
10
20

2

Danger! This doesn't escape the values or use ?-substitution, so this is vulnerable to SQL injection attacks. – vy32 Mar 24 '19 at 12:43

score 0 · Answer 11 · edited Jul 11 '21 at 10:08

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

edited Jul 11 '21 at 10:08

snakecharmerb

47,570
11
100
153

answered Apr 02 '20 at 23:46

Emma Y

555
1
9
16

score 0 · Answer 12 · answered Mar 11 '21 at 12:25

0

This works for me

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

answered Mar 11 '21 at 12:25

Umair Ayub

19,358
14
72
146

score 0 · Answer 13 · answered Mar 17 '21 at 04:41

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

score 0 · Answer 14 · answered Jan 13 '22 at 13:42

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

score -2 · Answer 15 · edited Oct 22 '15 at 07:35

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

...

edited Oct 22 '15 at 07:35

Iamat8

3,888
9
25
35

answered Oct 22 '15 at 07:12

MaybE_Tree

11
2

Using a Python dict for a SQL INSERT statement

15 Answers15

Example usage

What about:

For python 3:

...

Linked