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 — Selcuk
– Selcuk, Commented Mar 10, 2016 at 23:42
You'd need to use an OrderedDict for the last example, but it would work if you did. — Bob Dylan
– Bob Dylan, Commented Mar 10, 2016 at 23:46
@BobDylan could you please show an example of how that would work as an answer, and I'll accept it? — David542
– David542, Commented Mar 11, 2016 at 0: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
– Bob Dylan, Commented Mar 11, 2016 at 1:35

Bob Dylan · Accepted Answer · 2016-03-11 01:32:14Z

2

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)

edited Mar 11, 2016 at 1:32

answered Mar 11, 2016 at 1:26

Bob Dylan

1,8432 gold badges17 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Insert into mysql using dictionary

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related