How to fetch integer values using parameters into a MySQL database with Python connector

Question

I am trying to fetch data from a MySQL database using python connector. I want to fetch records matching the ID. The ID has an integer data type. This is the code:

custID = int(input("Customer ID: "))
executeStr = "SELECT * FROM testrec WHERE ID=%d"

cursor.execute(executeStr, custID)
custData = cursor.fetchall()

if bool(custData):
    pass
else:
    print("Wrong Id")

But the code is raising an error which says:

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 '%d' at line 1

Any ideas why the integer placeholder %d is producing this?

Answer 1

The string only accepts %s or %(name)s , not %d.

executeStr = "SELECT * FROM testrec WHERE ID=%s"

and the variables are supposed to be in a tuple, although that varies by implementation depending on what version you are using, you might need to use:

cursor.execute(executeStr, (custID,))

More information here https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

The current version supports variables of types: int,long,float,string,unicode,bytes,bytearray,bool,none,datetime.datetime,datetime.date,datetime.time,time.struct_time,datetime.timedelta, and decimal.Decimal