Python MySQL Connector database query with %s fails [duplicate]

Question

I have a basic program that is supposed to query a database that contains user information. I am trying to select the information for a specific user and print it out to the console.

Here is my code:

import mysql.connector

funcon = mysql.connector.connect(user='root', password='pass', host='127.0.0.1', database='fundata')
funcursor = funcon.cursor()

query = ("SELECT * FROM funtable WHERE userName=%s")
uName = 'user1'

funcursor.execute(query, uName)

for (userName) in funcursor:
    print("{}".format(userName))

I have the username stored in a variable because later I plan on getting the user name from a tkinter entry box. When I execute this code I get the following error:

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

I have tried putting the %s in quotes in the query but then it literally searches for a user names '%s' and returns nothing. How can I change my code so I can query the database for only this user?

Thank you.

FYI: I am using python 3.3.

Bridoon

Answer 1

Change your funcursor.execute(query, uName) call to:

funcursor.execute(query, (uName, ))

The second argument in execute takes a list/tuple of strings, not a string. The above call creates the tuple before passing in the string to execute, so no error is thrown.

The reason why execute takes a list/tuple of strings is because it does not know beforehand how many strings it needs in order to satisfy your query.

Conflation

Thank you very much. This worked. Could you explain, though, why the comma is necessary? I tried without the comma and got the same error. – Bridoon

It's because execute expects a list or tuple of values as a second argument. The comma creates a tuple on the spot, so that covers the requirements of the function. execute doesn't know how many strings it needs to replace in the query before reading it, and it doesn't want to read your query twice. So it just asks you for a query and then a list or tuple of strings. Even if you only want to replace one string, you have to make it into a list or tuple first before passing it in. – Conflation

Thanks for your answer, I was pulling my hair out. This seems like a poor design decision to have a trailing comma + space :/ – Cuspid