Python MySQL Connector database query with %s fails

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0