Mixing
mycursor.executemany UPDATE not working as expected
5
Question:
I have a python script to scrape and website it gets 2 variables and stores them in 2 lists. I then use executemany to update MySQL database using one variable to match a pre-existing row to insert the other variable into.
Code:
Python Script
import mysql.connector
from bs4 import BeautifulSoup as soup
from selenium import webdriver
import time, re
mydb = mysql.connector.connect(
host="host",
user="user",
passwd="passwd",
database="database"
)
mycursor = mydb.cursor()
d = webdriver.Chrome('D:/Uskompuf/Downloads/chromedriver')
d.get('https://au.pcpartpicker.com/products/cpu/overall-list/#page=1')
def cpus(_source):
result = soup(_source, 'html.parser').find('ul', {'id':'category_content'}).find_all('li')
_titles = list(filter(None, [(lambda x:'' if x is None else x.text)(i.find('div', {'class':'title'})) for i in result]))
data = [list(filter(None, [re.findall('(?<=().*?(?=))', c.text) for c in i.find_all('div')])) for i in result]
return _titles, [a for *_, [a] in filter(None, data)]
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
print(sql, list(zip(_titles, _cpus)))
_last_page = soup(d.page_source, 'html.parser').find_all('a', {'href':re.compile('#page=d+')})[-1].text
for i in range(2, int(_last_page)+1):
d.get(f'https://au.pcpartpicker.com/products/cpu/overall-list/#page={i}')
time.sleep(3)
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
mydb.commit()
MySQL UPDATE code
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print
print(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print output
UPDATE cpu set family = %s where name = %s [('Pinnacle Ridge', 'AMD Ryzen 5 2600'), ('Coffee Lake-S', 'Intel Core i7-8700K'),...
First 2 rows of table
Expected result
The first variable is the name and that is the variable that needs to be matched the second variable is the family to be updated to row. The name matches perfectly and there are no errors when running the program however all family values are null.
Not sure what the best way to go solving this, I though i could make a fiddle but not sure about the list in executemany?
Other
If you need any more information please let me know.
Thanks
python mysql python-3.x mysql-python
asked Jan 1 at 2:36
UskompufUskompuf
947
|
show 3 more comments
5
Question:
I have a python script to scrape and website it gets 2 variables and stores them in 2 lists. I then use executemany to update MySQL database using one variable to match a pre-existing row to insert the other variable into.
Code:
Python Script
import mysql.connector
from bs4 import BeautifulSoup as soup
from selenium import webdriver
import time, re
mydb = mysql.connector.connect(
host="host",
user="user",
passwd="passwd",
database="database"
)
mycursor = mydb.cursor()
d = webdriver.Chrome('D:/Uskompuf/Downloads/chromedriver')
d.get('https://au.pcpartpicker.com/products/cpu/overall-list/#page=1')
def cpus(_source):
result = soup(_source, 'html.parser').find('ul', {'id':'category_content'}).find_all('li')
_titles = list(filter(None, [(lambda x:'' if x is None else x.text)(i.find('div', {'class':'title'})) for i in result]))
data = [list(filter(None, [re.findall('(?<=().*?(?=))', c.text) for c in i.find_all('div')])) for i in result]
return _titles, [a for *_, [a] in filter(None, data)]
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
print(sql, list(zip(_titles, _cpus)))
_last_page = soup(d.page_source, 'html.parser').find_all('a', {'href':re.compile('#page=d+')})[-1].text
for i in range(2, int(_last_page)+1):
d.get(f'https://au.pcpartpicker.com/products/cpu/overall-list/#page={i}')
time.sleep(3)
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
mydb.commit()
MySQL UPDATE code
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print
print(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print output
UPDATE cpu set family = %s where name = %s [('Pinnacle Ridge', 'AMD Ryzen 5 2600'), ('Coffee Lake-S', 'Intel Core i7-8700K'),...
First 2 rows of table
Expected result
The first variable is the name and that is the variable that needs to be matched the second variable is the family to be updated to row. The name matches perfectly and there are no errors when running the program however all family values are null.
Not sure what the best way to go solving this, I though i could make a fiddle but not sure about the list in executemany?
Other
If you need any more information please let me know.
Thanks
python mysql python-3.x mysql-python
asked Jan 1 at 2:36
UskompufUskompuf
947
Can i ask a question? There are any name's value isPinnacle Ridge. Because i ran a small example likecur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.
– kcorlidy
Jan 1 at 5:07
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
Actually, your situation confused me. I created a similar table and insert all value in_titlesfirst. Then use part of your code(before for-loop), i got what you desire| AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |
– kcorlidy
Jan 1 at 5:57
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16
|
show 3 more comments
5
5
5
Question:
I have a python script to scrape and website it gets 2 variables and stores them in 2 lists. I then use executemany to update MySQL database using one variable to match a pre-existing row to insert the other variable into.
Code:
Python Script
import mysql.connector
from bs4 import BeautifulSoup as soup
from selenium import webdriver
import time, re
mydb = mysql.connector.connect(
host="host",
user="user",
passwd="passwd",
database="database"
)
mycursor = mydb.cursor()
d = webdriver.Chrome('D:/Uskompuf/Downloads/chromedriver')
d.get('https://au.pcpartpicker.com/products/cpu/overall-list/#page=1')
def cpus(_source):
result = soup(_source, 'html.parser').find('ul', {'id':'category_content'}).find_all('li')
_titles = list(filter(None, [(lambda x:'' if x is None else x.text)(i.find('div', {'class':'title'})) for i in result]))
data = [list(filter(None, [re.findall('(?<=().*?(?=))', c.text) for c in i.find_all('div')])) for i in result]
return _titles, [a for *_, [a] in filter(None, data)]
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
print(sql, list(zip(_titles, _cpus)))
_last_page = soup(d.page_source, 'html.parser').find_all('a', {'href':re.compile('#page=d+')})[-1].text
for i in range(2, int(_last_page)+1):
d.get(f'https://au.pcpartpicker.com/products/cpu/overall-list/#page={i}')
time.sleep(3)
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
mydb.commit()
MySQL UPDATE code
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print
print(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print output
UPDATE cpu set family = %s where name = %s [('Pinnacle Ridge', 'AMD Ryzen 5 2600'), ('Coffee Lake-S', 'Intel Core i7-8700K'),...
First 2 rows of table
Expected result
The first variable is the name and that is the variable that needs to be matched the second variable is the family to be updated to row. The name matches perfectly and there are no errors when running the program however all family values are null.
Not sure what the best way to go solving this, I though i could make a fiddle but not sure about the list in executemany?
Other
If you need any more information please let me know.
Thanks
python mysql python-3.x mysql-python
asked Jan 1 at 2:36
UskompufUskompuf
947
Question:
I have a python script to scrape and website it gets 2 variables and stores them in 2 lists. I then use executemany to update MySQL database using one variable to match a pre-existing row to insert the other variable into.
Code:
Python Script
import mysql.connector
from bs4 import BeautifulSoup as soup
from selenium import webdriver
import time, re
mydb = mysql.connector.connect(
host="host",
user="user",
passwd="passwd",
database="database"
)
mycursor = mydb.cursor()
d = webdriver.Chrome('D:/Uskompuf/Downloads/chromedriver')
d.get('https://au.pcpartpicker.com/products/cpu/overall-list/#page=1')
def cpus(_source):
result = soup(_source, 'html.parser').find('ul', {'id':'category_content'}).find_all('li')
_titles = list(filter(None, [(lambda x:'' if x is None else x.text)(i.find('div', {'class':'title'})) for i in result]))
data = [list(filter(None, [re.findall('(?<=().*?(?=))', c.text) for c in i.find_all('div')])) for i in result]
return _titles, [a for *_, [a] in filter(None, data)]
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
print(sql, list(zip(_titles, _cpus)))
_last_page = soup(d.page_source, 'html.parser').find_all('a', {'href':re.compile('#page=d+')})[-1].text
for i in range(2, int(_last_page)+1):
d.get(f'https://au.pcpartpicker.com/products/cpu/overall-list/#page={i}')
time.sleep(3)
_titles, _cpus = cpus(d.page_source)
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
mydb.commit()
MySQL UPDATE code
sql = "UPDATE cpu set family = %s where name = %s"
mycursor.executemany(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print
print(sql, list(zip(_cpus, _titles)))
MySQL UPDATE code print output
UPDATE cpu set family = %s where name = %s [('Pinnacle Ridge', 'AMD Ryzen 5 2600'), ('Coffee Lake-S', 'Intel Core i7-8700K'),...
First 2 rows of table
Expected result
The first variable is the name and that is the variable that needs to be matched the second variable is the family to be updated to row. The name matches perfectly and there are no errors when running the program however all family values are null.
Not sure what the best way to go solving this, I though i could make a fiddle but not sure about the list in executemany?
Other
If you need any more information please let me know.
Thanks
python mysql python-3.x mysql-python
python mysql python-3.x mysql-python
asked Jan 1 at 2:36
UskompufUskompuf
947
asked Jan 1 at 2:36
UskompufUskompuf
947
edited Jan 1 at 6:26
Uskompuf
asked Jan 1 at 2:36
UskompufUskompuf
947
asked Jan 1 at 2:36
UskompufUskompuf
947
asked Jan 1 at 2:36
UskompufUskompuf
947
947
Can i ask a question? There are any name's value isPinnacle Ridge. Because i ran a small example likecur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.
– kcorlidy
Jan 1 at 5:07
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
Actually, your situation confused me. I created a similar table and insert all value in_titlesfirst. Then use part of your code(before for-loop), i got what you desire| AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |
– kcorlidy
Jan 1 at 5:57
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16
|
show 3 more comments
Can i ask a question? There are any name's value isPinnacle Ridge. Because i ran a small example likecur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.
– kcorlidy
Jan 1 at 5:07
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
Actually, your situation confused me. I created a similar table and insert all value in_titlesfirst. Then use part of your code(before for-loop), i got what you desire| AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |
– kcorlidy
Jan 1 at 5:57
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16
Can i ask a question? There are any name's value is
Pinnacle Ridge. Because i ran a small example like cur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.– kcorlidy
Jan 1 at 5:07
Can i ask a question? There are any name's value is
Pinnacle Ridge. Because i ran a small example like cur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.– kcorlidy
Jan 1 at 5:07
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
Actually, your situation confused me. I created a similar table and insert all value in
_titles first. Then use part of your code(before for-loop), i got what you desire | AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |– kcorlidy
Jan 1 at 5:57
Actually, your situation confused me. I created a similar table and insert all value in
_titles first. Then use part of your code(before for-loop), i got what you desire | AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |– kcorlidy
Jan 1 at 5:57
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16
|
show 3 more comments
1 Answer
1
active
oldest
votes
0
Just had to add:
mydb.commit()
after
executemany
answered Jan 1 at 6:30
UskompufUskompuf
947
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
});
}
});
draft saved
draft discarded
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%2f53992711%2fmycursor-executemany-update-not-working-as-expected%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
0
Just had to add:
mydb.commit()
after
executemany
answered Jan 1 at 6:30
UskompufUskompuf
947
add a comment |
0
Just had to add:
mydb.commit()
after
executemany
answered Jan 1 at 6:30
UskompufUskompuf
947
add a comment |
0
0
0
Just had to add:
mydb.commit()
after
executemany
answered Jan 1 at 6:30
UskompufUskompuf
947
Just had to add:
mydb.commit()
after
executemany
answered Jan 1 at 6:30
UskompufUskompuf
947
answered Jan 1 at 6:30
UskompufUskompuf
947
answered Jan 1 at 6:30
UskompufUskompuf
947
answered Jan 1 at 6:30
UskompufUskompuf
947
947
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53992711%2fmycursor-executemany-update-not-working-as-expected%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
Can i ask a question? There are any name's value is
Pinnacle Ridge. Because i ran a small example likecur.executemany("UPDATE test SET col = %s WHERE col = %s", [("a","1"),("b","2")]), it works well.– kcorlidy
Jan 1 at 5:07
Sorry I'm not sure i completely understand the name values are AMD Ryzen 5 2600, Intel Core i7-8700k etc the family are Pinnacle Ridge, Coffe-Lake-S. Im not sure if the order is incorrect if so how can i rearrange it?
– Uskompuf
Jan 1 at 5:16
I think you should fix your question, what you printed and what you used in executemany are different.
– kcorlidy
Jan 1 at 5:34
Actually, your situation confused me. I created a similar table and insert all value in
_titlesfirst. Then use part of your code(before for-loop), i got what you desire| AMD Ryzen 5 2600 | Pinnacle Ridge | | Intel Core i7-8700K | Coffee Lake-S | | AMD Ryzen 7 2700X | Pinnacle Ridge | | AMD Ryzen 3 2200G | Raven Ridge |– kcorlidy
Jan 1 at 5:57
sorry im not sure what you mean i am trying the achieve the families being matched to the rows by the names? Do you know what i need to change in my code to achieve this?
– Uskompuf
Jan 1 at 6:16