I need assistance with inserting values into a MySQL database after extracting them using Python from a website. However, I encounter an error while trying to insert them into the database. The error message is as follows: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2.
Any help would be greatly appreciated. Thank you in advance. Below is the code snippet:
conn = mysql.connector.connect(user='root', password='root', host='localhost', database ='crawler_database')
mycursor = conn.cursor()
source_code = requests.get(item_url)
plain_text = source_code.text
soup = BeautifulSoup(plain_text)
my_list=[]
# SCRAPE DATA FROM THE WEB PAGE
for product_name in soup.findAll('span', {'itemprop': 'name'}):
prod_name = " " + product_name.string
my_list.append(prod_name)
print("Product Name: " + prod_name)
for low_price in soup.findAll('span', {'itemprop': 'lowPrice'}):
l_price = " " + low_price.string
my_list.append(l_price)
print("Lowest Price: " + low_price.string)
for high_price in soup.findAll('span', {'itemprop': 'highPrice'}):
h_price = " " + high_price.string
my_list.append(h_price)
print("Highest Price: " + high_price.string)
# FORMAT AND CLEANSE THE DATA
for specs in soup.findAll('ul', {'class': 'c-spec'}):
crop_str1 = str(specs).replace("<li>", "")
crop_str2 = crop_str1.replace("</li>", ",")
crop_str3 = crop_str2.replace("</ul>", "%")
crop_str4 = crop_str3.replace('<ul class="c-spec">', '$')
crop_str5 = crop_str4.partition('$')[-1].rpartition('%')[0]
my_list2 = crop_str5.split(",")
merged_list = my_list + my_list2
print(merged_list)
d0 = " " + str(merged_list[0])
d1 = " " + str(merged_list[1])
d2 = " " + str(merged_list[2])
d3 = " " + str(merged_list[3])
d4 = " " + str(merged_list[4])
d5 = " " + str(merged_list[5])
d6 = " " + str(merged_list[6])
print("Android OS: " + merged_list[3])
print("Camera: " + merged_list[4])
print("SIM: " + merged_list[5])
print("Display Size: " + merged_list[6])
mycursor.execute("""INSERT INTO mobile_spec(prod_name, low_price, high_price, android_os, camera, sim, d_size) VALUES (%s, %s, %s, %s, %s, %s, %s)""",(d0, d1, d2, d3, d4, d5, d6))
conn.commit()