CODEWITHSUNDEEP
pythonpymysql
jondoyle
jondoyle

Reputation: 53

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

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

Upvotes: 4

Views: 5838

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121486

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.

Upvotes: 3

Related Questions