CODEWITHSUNDEEP
pythonmysqlpython-3.xmysql-python
Luke Prior
Luke Prior

Reputation: 967

mycursor.executemany UPDATE not working as expected

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

Image

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

Upvotes: 5

Views: 4229

Answers (1)

Luke Prior
Luke Prior

Reputation: 967

Just had to add:

mydb.commit()

after

executemany

Upvotes: 3

Related Questions