CODEWITHSUNDEEP
pythonmysqlmysql-python
David542
David542

Reputation: 110205

Insert into mysql using dictionary

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

Upvotes: 0

Views: 1031

Answers (1)

Bob Dylan
Bob Dylan

Reputation: 1833

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)

Upvotes: 2

Related Questions