FIX "Check the manual that corresponds to your MySQL server version for the right syntax to use near '%s"

Question

The following code works with insert query, but not with update and delete.

    prono=int(input("Enter the poduct no :"))
    sql_1="select * from product where prno=%s"

    mycursor.execute(sql_1,prono)
    myresult=mycursor.fetchall()
    mydb.commit()
    for x in myresult:
        print(x)

        #details printed

        #req. details for updation
    print("Please enter the new details of the product")
    a=str(input("Enter the new name : "))
    b=int(input("Enter the new price : "))
    sql_2="update product set name=%s ,price=%s where prno=%s"
    set1=(a,b,prono)
    mycursor.execute(sql_2,set1)
    mydb.commit
    print("Product modified")

The error I'm getting is 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 '%s'

@tcadidot0 These get expanded by `mycursor.execute(...)` two lines below. — handle, Oct 12 '19 at 09:28
Does this answer your question? [mysql.connector.errors.ProgrammingError: 1064 (4200): You have an error in your SQL syntax;](https://stackoverflow.com/questions/45665819/mysql-connector-errors-programmingerror-1064-4200-you-have-an-error-in-your) — questionto42, Apr 21 '22 at 13:32

handle · Answer 1 · 2019-10-12T09:26:14.870

0

You seem to be trying to convert a string to an int:

a=int(input("Enter the new name : "))

This probably is not working with your table layout.

Try:

a=input("Enter the new name : ")

Also you are using "No" and "NO" in your queries.

It's not the spelling of no, it's a reserved word. This seems to be valid:

UPDATE
  product
SET
  myname = 'string',
  myprice = 123
WHERE
  myno = 1;

(of course you need to change your column names in the database table for this to work)

edited Oct 12 '19 at 09:26

answered Oct 12 '19 at 09:16

handle

5,859
3
54
82

ahh..yes i fixed that but it still persists..thanks btw – Lafanga Coder Oct 12 '19 at 09:17
well i don't think that upper or lower case matters in mysql queries – Lafanga Coder Oct 12 '19 at 09:22
i actually changed the column name and removed the brackets but still..it didn't worked – Lafanga Coder Oct 12 '19 at 09:46

nacho · Answer 2 · 2019-10-12T09:26:33.450

0

You are using NO as column name, but is a MySql reserved word, you should use backticks for it like this for sql_1 & sql_2:

sql_1="select * from product where `No`=%s"

sql_2="update product set name=%s ,price=%s where `NO`=%s"

But the better solution it is not using reserved words as column names.

EDIT

Also, your sql_1 query is wrong, you don´t need to use (). If you do it, you get a touple with a string, not a string

edited Oct 12 '19 at 09:26

answered Oct 12 '19 at 09:24

nacho

5,280
2
25
34

If my answer worked, please accept it and upvote it – nacho Oct 12 '19 at 09:45
it worked once, i don't know how, ..but not now...also after removing the () – Lafanga Coder Oct 12 '19 at 09:50
Ok, change this _mycursor.execute(sql_1,prono)_ to this **mycursor.execute(sql_1,(prono,))**, you need to send a tuple of parameters to the execute – nacho Oct 12 '19 at 10:27

FIX "Check the manual that corresponds to your MySQL server version for the right syntax to use near '%s"

2 Answers2