MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versi

Question

I am trying to insert data into a MySQL table using my Flask app as shown below;

#Create MySQL connection and write data into the table user_data from the POST form
    sql_cursor = mysql.connection.cursor()
    
    sql_cursor.execute("""INSERT INTO user_data (make,model,trim,fuel_type,transmission,condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value)
        VALUES (new_info['make'],new_info['model'],new_info['trim'],new_info['fuel_type'],new_info['transmission'],new_info['condition'],
        add_bodytype['body_type'],add_region['region'],new_data['description'],new_data['eng_capacity'],year,new_data['mileage'], estm_value);""")
        
    mysql.connection.commit()
    sql_cursor.close()

This does not work too:

    sql_cursor = mysql.connection.cursor()
    
    sql_cursor.execute("INSERT INTO user_data (make,model,trim,fuel_type,transmission,condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", (new_info['make'],new_info['model'],new_info['trim'],new_info['fuel_type'],new_info['transmission'],new_info['condition'],add_bodytype['body_type'],add_region['region'],new_data['description'],new_data['eng_capacity'],year,new_data['mileage'], estm_value))
        
    mysql.connection.commit()
    sql_cursor.close()

I get the below error:

I get the below error:

MySQLdb._exceptions.ProgrammingError: (1064, "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 'condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value)\n ' at line 1")

Someone please help me.

You can't put Python code into a string like that. Look at how to use placeholders. — mechanical_meat, Oct 27 '21 at 17:45

nbk · Answer 1 · 2021-10-27T19:24:14.900

2

The homepage explains how to use placeholders https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

If your variables are all valid.

this should work

sql_cursor.execute("""INSERT INTO user_data (make,model,trim,fuel_type,transmission,condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value)"""\
                """ VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s);""",(new_info['make'],new_info['model'],new_info['trim'],new_info['fuel_type'],new_info['transmission'],new_info['condition'],
    add_bodytype['body_type'],add_region['region'],new_data['description'],new_data['eng_capacity'],year,new_data['mileage'], estm_valu)))
    
mysql.connection.commit()
sql_cursor.close()

edited Oct 27 '21 at 19:24

answered Oct 27 '21 at 17:53

nbk

45,398
8
30
47

Hi nbk. Imagine its still throwing the same error. – Elijah Kalii Oct 27 '21 at 18:27
you error code shows that you have a \n, `estm_value)\n ` try to make it like it ios decribed here https://note.nkmk.me/en/python-long-string/ i also added some backslash,around some columns that looked like reserved words – nbk Oct 27 '21 at 18:39
MySQLdb._exceptions.ProgrammingError: (1064, "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 'condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value) VALU' at line 1") . This is the error when I remove new lines – Elijah Kalii Oct 27 '21 at 18:58
that is niot the corrected code i poosted please check again please – nbk Oct 27 '21 at 18:59
Whao. your code is attempting to write some lines halfway. Seems like you included some spaces on the column names? It seems to be working – Elijah Kalii Oct 27 '21 at 19:15
MySQLdb._exceptions.ProgrammingError: (1064, "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 '' ' VALUES ('Toyota','Belta',' ','Petrol','Automatic','Locally u' at line 1") This is the new error. You can see its attempting to write – Elijah Kalii Oct 27 '21 at 19:17
good, i chnged in my edited answer the single quotes with the three double quotes youu use, s so try it again please – nbk Oct 27 '21 at 19:25

score 0 · Answer 2 · answered Oct 27 '21 at 17:53

0

Apparently, your connection driver is not configured to accept line breaks \n, try change your template string using concatenation or all inline statement...

    sql_cursor = mysql.connection.cursor()
    
    sql_cursor.execute("""INSERT INTO user_data (make,model,trim,fuel_type,transmission,condition,body_type,region,descriptn,eng_capacity,year,mileage, estm_value) VALUES (new_info['make'],new_info['model'],new_info['trim'],new_info['fuel_type'],new_info['transmission'],new_info['condition'],add_bodytype['body_type'],add_region['region'],new_data['description'],new_data['eng_capacity'],year,new_data['mileage'], estm_value);""")
        
    mysql.connection.commit()
    sql_cursor.close()

answered Oct 27 '21 at 17:53

Alan Ferreira

74
5

Thanks alan but I am still getting the same error – Elijah Kalii Oct 27 '21 at 18:27
Hummm... you can put the new error here, should be out new error description, not same – Alan Ferreira Oct 27 '21 at 20:23
MySQLdb._exceptions.ProgrammingError: (1064, "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 'usage,body_type,region,descriptn,eng_capacity,year_man,mileage, estm_value) VALU' at line 1") This is the new error – Elijah Kalii Oct 27 '21 at 22:49

MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versi

2 Answers2