1

I'm using python 3.x and mysql.connector to access a Mysql database. I'm having an issuing with querying the database.

When I run the following query, I get the correct result: 

cursor.execute("SELECT idx FROM db WHERE name = 'John Smith'")

When I try and run the following query I get an error:

cursor.execute("SELECT idx FROM db WHERE name = %s",'John Smith')

File "<input>", line 1, in <module>
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
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've tried running the following query instead, but it appears that the query searches for "%s" not the string "John Smith"

cursor.execute("SELECT idx FROM db WHERE name = '%s'",'John Smith')

I'm not sure if the error is due to the fact that I'm using a string with spaces in it or not, but I haven't been able to solve the problem.

Klaus D.
  • 13,874
  • 5
  • 41
  • 48
Grant
  • 13
  • 1
  • 5
  • Try replacing the comma (,) with a percent symbol (%). – ian Nov 18 '15 at 19:32
  • 1
    @theClap No, never do that! It will allow SQL injections. – Klaus D. Nov 18 '15 at 19:33
  • `cursor.execute` is not `sprintf`. – cdarke Nov 18 '15 at 19:36
  • Have you tried removing the single quotes from around the `%s`? – wpercy Nov 18 '15 at 19:37
  • Try `cursor.execute("SELECT idx FROM db WHERE name = '%s'", ('John Smith',))`. @KlausD. he's not preparing his statements so security was not a prerequisite however very valid point that you might want to bring up to the poster. @cdarke the value being passed is a string and _would_ work – ian Nov 18 '15 at 19:45

3 Answers3

0
q="SELECT idx FROM db WHERE name = %s";

cursor.execute(q,'Xander')
user5509884
  • 36
  • 4
  • Why would this work but not `cursor.execute("SELECT idx FROM db WHERE name = %s",'John Smith')`? – cgmb Nov 18 '15 at 23:10
0

According to the docs, you would need to write:

 cursor.execute("SELECT idx FROM db WHERE name = %s", ('John Smith'))

In other words, the parameter-replacement values must be contained in an iterable.

(But see the answer from theClap who correctly adds a semi-colon to force "tupleness" where required).

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Are you using the MySQL connector library and not some other library? Other libraries / databases use a different parameter substitution indicator. What are the `import` statements at the top of your file? – Larry Lustig Nov 18 '15 at 21:58
  • Add the comma to force `('John Smith')` to be a tuple. Other than that, would need to see your code. – Larry Lustig Nov 18 '15 at 22:08
  • @BK435 your answer works, however note KlausD.'s response to my suggestion of the same answer in the comments under the question. – ian Nov 19 '15 at 15:47
  • It's not a matter of Pythonic or not; creating SQL commands by string concatenation or interpolation is universally considered poor programming practice. Parameterization is superior in every way. Nobody is hating on your answer, just trying to provide information that you and the OP will, hopefully, take into consideration as you go forward in your programming careers. – Larry Lustig Nov 19 '15 at 19:16
  • That is true. Your original answer appeared to confuse what the OP was trying to do (issue a parameterized query) with string interpolation, almost as if you weren't familiar with the very concept of parameterized SQL queries. It's an easy mistake to make given `MySQL.connector`'s extremely confusing and unhelpful syntax but it would have taken the OP down the wrong road -- away from a working parameterized query -- if he had followed it. – Larry Lustig Nov 19 '15 at 19:39
  • I should also note that although you appear to have picked up a single downvote, it's not from me. – Larry Lustig Nov 19 '15 at 19:40
  • That your answer was, in fact, not correct. It did not help the OP solve his original problem, which was issuing a parameterized query in `MySQL.connector`. – Larry Lustig Nov 19 '15 at 20:18
0

Formatting just needs to be changed a little. You were pretty close.

foo = 'John Smith'
cursor.execute("SELECT idx FROM db WHERE name = %s", (foo,))

Take a gander at the links below for more explanation
Python MySQL Parameterized Queries
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Community
  • 1
  • 1
ian
  • 149
  • 6