1

By using mysql-connector-python package

mycursor = mydb.cursor()
new_pin = int(input("Enter Pin Number:"))
Pins_Entry = "INSERT INTO ATM_DEMO_PINS(PIN) VALUES(%s)"  # Middle Program 1
mycursor.execute(Pins_Entry, new_pin)
mydb.commit()

Showing Error:

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 '%s)' at line 3
Anand D
  • 13
  • 4

4 Answers4

0

This seems to be a perennial error here on SO, but you need to obtain a cursor with the prepared statement mode turned on:

mycursor = mydb.cursor(prepared=True)     # change is HERE
new_pin = int(input("Enter Pin Number:"))
Pins_Entry = "INSERT INTO ATM_DEMO_PINS(PIN) VALUES(%s)"  # Middle Program 1
mycursor.execute(Pins_Entry, (new_pin,))
mydb.commit()

The error you are seeing would be consistent with MySQL parsing the query with %s being a literal part of the query, rather than a placeholder.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I don't think having `prepared=True` or not makes a difference in this particular case, but passing a non-collection (the integer itself, instead of a collection of argument(s)) as the second argument to `execute()`. – Ilja Everilä Aug 03 '20 at 09:32
  • Looking at it a bit closer, at least the version of mysql-connector-python from 4 years ago has the behaviour that if given other than list, tuple, dict, or None it will simply ignore the second argument and pass the statement forward as is. – Ilja Everilä Aug 03 '20 at 09:51
  • @IljaEverilä You were right about the tuple issue and I have corrected it, good catch. I stand by what I said about opening with a prepared statement though. – Tim Biegeleisen Aug 03 '20 at 09:52
  • Of course, that's another issue that I've no experience about :) – Ilja Everilä Aug 03 '20 at 09:52
-1

Your problem is in this line

mycursor.execute(Pins_Entry, new_pin)

It will be like this

mycursor.execute(Pins_Entry%new_pin)
Ujjwal Dash
  • 767
  • 1
  • 4
  • 8
-1

mycursor = mydb.cursor()
new_pin = int(input("Enter Pin Number:"))
Pins_Entry = "INSERT INTO ATM_DEMO_PINS(PIN) VALUES(%s)"  # Middle Program 1
mycursor.execute(Pins_Entry%new_pin)#"INSERT INTO ATM_DEMO_PINS(PIN) VALUES(new_pin)"
mydb.commit()
/*
in string manipulation %s is use for input your variable to string so #it work as this
a=12
if use like this
print('my number %s',a)=>my number %s 12
but using this give your proper query
print('my number %s'%a)=>my number 12   
*/
chanaka sandaruwan
  • 1
  • 3
-1

You should do the following:

mycursor = mydb.cursor()
new_pin = int(input("Enter Pin Number:")) 
Pins_Entry = f"INSERT INTO ATM_DEMO_PINS(PIN) VALUES('{new_pin}')" 
mycursor.execute(Pins_Entry) 
mydb.commit()
legalsniper1
  • 1
  • 2