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?

Libraries use different paramstyle. https://www.python.org/dev/peps/pep-0249/#paramstyle — falsetru, Nov 28 '20 at 07:45

JeffUK · Accepted Answer · 2020-11-29T13:39:44.957

2

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

edited Nov 29 '20 at 13:39

answered Nov 28 '20 at 07:45

JeffUK

4,107
2
20
34

But what if I want to insert an integer value. If the the SQL table column has datatype of `INT`, then inserting string will fail. – Shounak Das Nov 28 '20 at 07:59
1

%s != string, this isn't a C/C++ formatted string. – JeffUK Nov 28 '20 at 08:23
Oh, thanks. Now I get it. I had asked this question https://stackoverflow.com/questions/64953563/what-does-the-s-placeholder-stand-for about `%s` few days ago, but no one mentioned me that. Thanks a lot. – Shounak Das Nov 28 '20 at 08:25
The article I link puts it well: "The data values are converted as necessary from Python objects to something MySQL understands." i.e. it checks the type of each variable before passing it over so it knows to use quotes or not etc. – JeffUK Nov 28 '20 at 08:27

score -1 · Answer 2 · edited Oct 26 '21 at 18:08

-1

Instead of this:

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

You should write:

custID = int(input("Customer ID: "))
executeStr = "SELECT * FROM testrec WHERE ID='"+custID+"';"

edited Oct 26 '21 at 18:08

Henry Ecker

34,399
18
41
57

answered Oct 26 '21 at 16:39

mhaskeomkar

1

score -2 · Answer 3 · answered Nov 28 '20 at 08:12

-2

Your should try %s not %d. This is because your data type of MYSQL may be int but the parameter you should pass to cursor must be string.

Try this:

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

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

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

answered Nov 28 '20 at 08:12

Satyajit

115
9

Thanks! That worked. But, can you please explain the `%(custID,)`. What does that do? – Shounak Das Nov 28 '20 at 08:18
1

That's not true at all, the parameter can be in lots of different types, it doesn't have to be a string. https://github.com/mysql/mysql-connector-python/blob/403535dbdd45d894c780da23ba96b1a6c81e3866/lib/mysql/connector/conversion.py#L179 search for `to_mysql` in this file and you'll see all of the type conversions it supports – JeffUK Nov 28 '20 at 09:10
@ShounakDas ```%(custID,)``` is a tuple that passes the argument, where ever you pass ```%s``` , you can also use ```%s``` two times and pass ```%(val1,val2)``` – Satyajit Nov 28 '20 at 09:13

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

3 Answers3