Using INSERT TO sql with with dictionary values

Question

I'm using Python 3.6 mysql 8 and mysql python connector 8
I'm trying to insert to the following table

| Recipes_id| title| Prep_time| servings | Summarize|  Very_healthy| Cuisine| img| source_url|
+-----------+------+----------+---------+----------+--------------+--------+----+-----------+

using a dictionary with different naming for the keys.

I.e a sample object of the dictionary:

{
    'cuisine': None, 
    'id': 521693, 
    'img': None, 
    'prep_time': 5,
    'servings': 1, 
    'source_url': None,
    'summarize': 'Mango, Banana, Rasp...thie</a>.', 
    'title': 'Mango, Banana, Rasp... Smoothie',
    'very_healthy': None
}

Here, the id key differs from the recipe_id column name in the table.

MySQL query is as follows:

Recipes_INSERT_SQL = (
    "INSERT INTO recipes "
    "(Recipes_id, title, Prep_time, servings, Summarize, Very_healthy, Cuisine, Img, Source_url) "
    "VALUES ( %(id)s, %(title)s, %(prep_time)s, %(servings)s, %(summarize)s, %(very_healthy)s, %(cuisine)s, %(img)s, %(source_url)s )"
)

cursor.execute(Recipes_INSERT_SQL, recipes_data)

Notice that the dictionary keys differ from the column names
But the error is:

ProgrammingError(1064, "1064 (42000): You have an error 
in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '%(id)s,
%(title)s, %(prep_time)s, %(serving)s, %(summarize)s, %(very_healthy)s, ' at line 1", 
'42000')

As you can see, the engine didn't even parsed the (title)s and other names.
Am I missing something? the placeholders convention for mysql 8 is %(name)s as stated in the documentary.

Here is the sql hierarchy and recipes table:

Similar kinda problem addressed https://stackoverflow.com/questions/9336270/using-a-python-dict-for-a-sql-insert-statement — Naresh Kumar, Jan 01 '19 at 13:36
No, because here the dict keys are not as the table keys. I'm trying to use this syntax->https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html — DsCpp, Jan 01 '19 at 13:45

score 0 · Answer 1 · answered Jan 01 '19 at 18:20

0

I think, based on your explanation, you have a group of recipes in a dictionary format:

The syntax you're using to insert values in database need to have directly the dictionary on the variable. An example of invalid syntax are:

{
    'cuisine': None, 
    'id': 465645, 
},{
    'cuisine': None, 
    'id': 521693, 
}

If that's the case, you need to iterate the array of dictionaries, and with each value of it (each recipe), launch the SQL query. You cannot do only one query for insert all values.

You can use, for example, a foreach for iterate all values on your recipes_data, storing the current value on recipe (for example). And then, inside the foreach, you can run the cursor.execute()

answered Jan 01 '19 at 18:20

Sakura Kinomoto

1,784
2
21
30

That's what I'm doing../ foreach dict I launch the query – DsCpp Jan 01 '19 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach – Sakura Kinomoto Jan 01 '19 at 18:39
The sampled dict is also the first one, I suspect that the cursor doesn't even parse the %(name)s, and ignore it as unknown syntax – DsCpp Jan 01 '19 at 21:12

Using INSERT TO sql with with dictionary values

1 Answers1