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))
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
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.
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()