Insert into mysql using dictionary

Question

I want to do the following SQL statement:

INSERT INTO myTable (name, year) VALUES ("David", 2016)

Is there a simple way to do the above INSERT using a dictionary:

d = {
  "name": "David",
  "year": 2016
}

Perhaps something like:

cursor.execute('INSERT INTO myTable (%s) VALUES (%s)', (d.keys(), d.values())

Or even:

cursor.execute('INSERT INTO myTable %s', (d.items(),))

Possible duplicate of [Using a Python dict for a SQL INSERT statement](http://stackoverflow.com/questions/9336270/using-a-python-dict-for-a-sql-insert-statement) — Selcuk, Mar 10 '16 at 23:42
You'd need to use an `OrderedDict` for the last example, but it would work if you did. — Bob Dylan, Mar 10 '16 at 23:46
@BobDylan could you please show an example of how that would work as an answer, and I'll accept it? — David542, Mar 11 '16 at 00:59
@David542 I don't have mysql to test handy but gave proof of concept based on how i've done in past — Bob Dylan, Mar 11 '16 at 01:35

Bob Dylan · Accepted Answer · 2016-03-11T01:32:14.087

Assuming mysql-connector (but true of most SQL/Python libraries), the cursor expects a tuple with the values (for proper parameterization). A dict is inherently not ordered, so you need to use an ordered dict to use the second example in your question and ensure the keys are in the same order as the table columns/fields. So for example:

from collections import OrderedDict

d = OrderedDict([("name", "David"), ("year", 2016)])

values_tuple = tuple([v for v in d.values()])
q = r'INSERT INTO myTable (%s'
for i in range(len(values_tuple)-1):  # I added first one above so -1
    q += r', %s'
q += ')'

cursor.execute(q, values_tuple)

Insert into mysql using dictionary

1 Answers1