inserting values into database using mysql connector python, web scraping

Question

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

dabingsou · Accepted Answer · 2020-07-05 07:24:52Z

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.

edited Jul 5, 2020 at 7:24

answered Jul 4, 2020 at 22:14

dabingsou

2,4691 gold badge7 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

amir Over a year ago

Thanks for answer, it did not work, I edited the post and put the structure of project table at the end of the post, please check it there

dabingsou Over a year ago

@amir Do you have any error messages? Please post them.

dabingsou Over a year ago

@amir I changed the answer.

amir Over a year ago

I got no error but nothing is inserted into project table's values. What can be the problem?

snakecharmerb Over a year ago

@amir you need to commit the transaction (cnx.commit())

MITHU · Accepted Answer · 2020-07-05 07:19:14Z

0

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

answered Jul 5, 2020 at 7:19

MITHU

1664 gold badges15 silver badges45 bronze badges

Collectives™ on Stack Overflow

inserting values into database using mysql connector python, web scraping

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related