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 stackoverflow.com/questions/9336270/… — Naresh Kumar
– Naresh Kumar, Commented Jan 1, 2019 at 13:36
No, because here the dict keys are not as the table keys. I'm trying to use this syntax->dev.mysql.com/doc/connector-python/en/… — DsCpp
– DsCpp, Commented Jan 1, 2019 at 13:45

Sakura Kinomoto · Accepted Answer · 2019-01-01 18:20:14Z

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 1, 2019 at 18:20

Sakura Kinomoto

1,8822 gold badges22 silver badges32 bronze badges

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

3 Comments

DsCpp Over a year ago

That's what I'm doing../ foreach dict I launch the query

Sakura Kinomoto Over a year ago

Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach

DsCpp Over a year ago

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

Collectives™ on Stack Overflow

Using INSERT TO sql with with dictionary values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related