CODEWITHSUNDEEP
pythonmysql
ptim ktim
ptim ktim

Reputation: 65

Python / MySQL error at insertion

Hi guys I am trying to insert some values into MySQL database after crawling them using python from website.But it gives me error while inserting them into database . Error 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 .

please help me. Thanks in advance. Below is the code:

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=[]
# GATHER INFORMATION FROM 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 REMOVE UNWANTED CHARACTER FROM 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()

Upvotes: 1

Views: 1087

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 600026

You're passing the actual strings "d0", etc, to MySQL, which has no idea what to do with them. Those are Python variables, so you need to pass their values to MySQL instead. You do that with placeholders:

mycursor.execute("""INSERT INTO mobile_spec(android_os, camera, sim, d_size)
                 VALUES (%s, %s, %s , %s)""", (d0, d1, d2, d3))

Your code can be simplified further, since you get the params in a list in the first place, so you can pass that directly to MySQL without the d* variables at all.

mycursor.execute("""INSERT INTO mobile_spec(android_os, camera, sim, d_size)
                 VALUES (%s, %s, %s , %s)""", my_list)

Upvotes: 2

Related Questions