8

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.

davidism
  • 121,510
  • 29
  • 395
  • 339
Sebastian Bartholomew Charles
  • 93
  • 1
  • 7

1 Answers1

20

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.

huu
  • 7,032
  • 2
  • 34
  • 49
  • 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. – Sebastian Bartholomew Charles May 12 '14 at 15:18
  • 1
    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. – huu May 12 '14 at 15:20
  • 1
    Thanks for your answer, I was pulling my hair out. This seems like a poor design decision to have a trailing comma + space :/ – Garbit Dec 14 '16 at 11:51