Python / MySQL error at insertion

Question

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

assuming fields android_os, camera, sim, d_size are varchar, you have to string literal quote them in values clause like : VALUES ('d0','d1','d2','d3'). although you probably want to use the values in variables $d0 ( so concatenate ( with the string literal characters ) — amdixon
– amdixon, Commented Jul 24, 2015 at 9:11
but it is inserting d0, d1, d2, d3 in table instead of inserting values which I retrieve from my_list. — ptim ktim
– ptim ktim, Commented Jul 24, 2015 at 9:16

Daniel Roseman · Accepted Answer · 2015-07-24 09:22:45Z

2

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)

edited Jul 24, 2015 at 9:22

answered Jul 24, 2015 at 9:19

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

4 Comments

ptim ktim Over a year ago

heyy daniel thanks,But when I added more parameters to it it gaves another syntax error.I checked the code but didnt found any syntax error.Please help me friend.

Daniel Roseman Over a year ago

If you've made a change and are getting an error, you should post the change you've made and that error.

ptim ktim Over a year ago

ya change is in the code.Now I have made list of 7 elements.that is the only change I had made.

Daniel Roseman Over a year ago

You still didn't post the error. But I see that you have two opening parentheses in the SQL query.

Collectives™ on Stack Overflow

Python / MySQL error at insertion

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related