-1

I am trying to search a database filtering by a category which the user chooses and selects. I have attempted to add a Variable into my Select Query but it keeps failing with a SQL syntax error but I cannot find any syntax issues

var1 = "World"
selectQ = """SELECT name, score FROM score WHERE category = %s"""
cursor.execute(selectQ, Var1)

The Error is 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 1

Dandy
  • 41
  • 7
  • You have to fill in what `%s` is using C-style string formatting patterns. – James Feb 27 '22 at 14:34
  • Can you include the full stack trace of the syntax error you're seeing, as well as tag your RDBMS, in accordance with [ask]? – esqew Feb 27 '22 at 14:34
  • @James, no, `cursor.execute` will perform the substitution automatically (modulo `Var1` being an acceptable value). – snakecharmerb Feb 27 '22 at 14:45
  • See [this answer](https://stackoverflow.com/a/64952928/5320906) on the linked duplicate Q&A. You should ensure that the second argument to `cursor.execute` is inside a `list` or a `tuple`. – snakecharmerb Feb 27 '22 at 14:49

1 Answers1

-1

You can try below,

var1 = "World"

cursor.execute("SELECT name, score FROM score WHERE category = %s", (var1))

Note, the parameters should be passed as tuple.

Maheshkrishna AG
  • 69
  • 5
  • No that just seems to throw up the same syntax error – Dandy Feb 27 '22 at 14:46
  • Could you post your code and the error that you are facing here? – Maheshkrishna AG Feb 27 '22 at 14:49
  • 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 1 Should be in my question as well now – Dandy Feb 27 '22 at 14:49
  • Apologies, missed this. If your parameter is only one variable to be passed. Use a tuple notation like this, (var1,). So your code goes as, cursor.execute("SELECT name, score FROM score WHERE category = %s", (var1,)). – Maheshkrishna AG Feb 27 '22 at 14:56
  • Perfect that fixed it :) – Dandy Feb 27 '22 at 15:03