Mixing
Using INSERT TO sql with with dictionary values
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:
python mysql
asked Jan 1 at 13:26
DsCppDsCpp
453211
add a comment |
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:
python mysql
asked Jan 1 at 13:26
DsCppDsCpp
453211
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 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
Jan 1 at 13:45
add a comment |
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:
python mysql
asked Jan 1 at 13:26
DsCppDsCpp
453211
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:
python mysql
python mysql
asked Jan 1 at 13:26
DsCppDsCpp
453211
asked Jan 1 at 13:26
DsCppDsCpp
453211
edited Jan 1 at 21:11
DsCpp
asked Jan 1 at 13:26
DsCppDsCpp
453211
asked Jan 1 at 13:26
DsCppDsCpp
453211
asked Jan 1 at 13:26
DsCppDsCpp
453211
453211
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 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
Jan 1 at 13:45
add a comment |
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 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
Jan 1 at 13:45
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 at 13:36
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 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
Jan 1 at 13:45
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
Jan 1 at 13:45
add a comment |
1 Answer
1
active
oldest
votes
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 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995812%2fusing-insert-to-sql-with-with-dictionary-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
add a comment |
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 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
add a comment |
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 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
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 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
answered Jan 1 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
answered Jan 1 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
answered Jan 1 at 18:20
Sakura KinomotoSakura Kinomoto
1,0411122
1,0411122
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
add a comment |
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
That's what I'm doing../ foreach dict I launch the query
– DsCpp
Jan 1 at 18:38
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 at 18:39
Try to debug it a bit, printing on screen the value of your recipes_data inside the foreach
– Sakura Kinomoto
Jan 1 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 1 at 21:12
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 1 at 21:12
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995812%2fusing-insert-to-sql-with-with-dictionary-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Similar kinda problem addressed stackoverflow.com/questions/9336270/…
– Naresh Kumar
Jan 1 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
Jan 1 at 13:45