pymysql query formatting, TypeError: %d format: a number is required, not str

Question

So I was writing a sql query to get data from the database using python pymysql library. The user will enter a year as a text field in HTML form and display the data associated with this year. I'm using this code and I'm keep getting error for it.

sql = "SELECT 'Name' From 'winners' WHERE 'Year'=%d"
c.execute(sql, (textfield,))

Can someone show me how to fix this please ? Thank you

Answer 1

There are two issues:

• You are using incorrect syntax. ' single quotes are used to define string values, not to quote table and column names. You probably meant to use backticks, `. See When to use single quotes, double quotes, and backticks?

• You are passing in a string where your query parameter states you wanted to receive an integer. SQL parameters only support %s format placeholders, however.

Corrected code would look like this:

sql = "SELECT `Name` From `winners` WHERE `Year`=%s"
c.execute(sql, (int(textfield),))

Note the int() call; you want to select on integers, not strings, if your column type is NUMBER or similar.