inserting values into database using mysql connector python, web scraping

user_13660224 0
Viewed 500

this code is supposed to find the first 20 headphones from the site and then get the item's name and price. Then insert them into database (into "project" table).

import requests
from bs4 import BeautifulSoup
import mysql.connector

cnx=mysql.connector.connect(user="root",password='test',host='127.0.0.1',database='amirdb')
cursor=cnx.cursor()
response=requests.get(f"https://www.digikala.com/search/?q=headphone")
soup=BeautifulSoup(response.text,"html.parser")

container=soup.find("ul",{'class':'c-listing__items'})
item=container.find_all("div",{'class':'c-product-box__title'})
price=container.find_all("div",{'class':'c-price__value-wrapper'})

for num in range(20):
    cursor.execute('INSERT INTO project VALUES(%s,%s)',(item[num].text,price[num].text))
cnx.commit()
cnx.close()

When I want to insert them into my database(into "project"table) so it does not work and I know that the problem is in this part of my code:

for num in range(20):
        cursor.execute('INSERT INTO project VALUES(%s,%s)',(item[num].text,price[num].text))

What is the problem with this part of the code above?

The structure of the table:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| filename | varchar(100) | YES  |     | NULL    |       |
| price    | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

I got these errors:

Traceback (most recent call last):
  File "C:/Users/Administratör/PycharmProjects/untitled/venv/seventh.py", line 13, in <module>
    cursor.execute('INSERT INTO project VALUES(%s,%s)',(item[num].text,price[num].text))
  File "C:\Users\Administratör\PycharmProjects\untitled\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\Administratör\PycharmProjects\untitled\venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\Administratör\PycharmProjects\untitled\venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect string value: '\xD9\x87\xD8\xAF\xD9\x81...' for column 'filename' at row 1
mysql-connector-python web-scraping mysql sql python
2 Answers

It's caused by database encode, modify the database encode.

ALTER TABLE project CONVERT TO CHARACTER SET utf8mb4;

Try this.

cursor.execute('INSERT INTO project VALUES(%s,%s)',(item[num].text,price[num].text))

If not, post the structure of your table project.

user_124558790

Try something like below. I suppose it will work.

for container in soup.find_all(class_='c-product-box')[:20]:
    item_title = container.find(class_='c-product-box__title').get_text(strip=True)
    item_price = container.find(class_='c-price__value-wrapper').get_text(strip=True)
    cursor.execute('INSERT INTO project (item_header,price_header) VALUES (%s,%s)',(item_title,item_price))

cnx.commit()
cnx.close()
user_71801940
Related