Error selecting from sql table using mysql placeholders

Question

I am trying to select some values within a table that has a column called "Name". That column contains tennis players names. I want to store some statistics for each player in python, but I am having trouble accessing the table. I keep getting a "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 '='Rafael Nadal'' at line 1" As you can see, mysql clearly interprets the %s I had in place of 'Rafael Nadal' correctly, but it still brings up an error. Can anyone help me?

recordTuple = ('Rafael Nadal', )
mySql_insert_query = """SELECT `First_Serve(%)` FROM `serve2` WHERE Name =%s"""
cursor.execute(mySql_insert_query, recordTuple)
aI = cursor.fetchall()[0][0]/100

You really have a `%` in the column name? Double it to `%%`. — Barmar, Aug 28 '19 at 20:18
The %s is a placeholder. This is used in MySql python connector to represent a variable name. In this case the variable is 'Rafael Nadal'. I eventually want the user to be able select the player for themself — muth, Aug 28 '19 at 20:22
Try putting the column name `Name` in backticks. [`NAME` is a reserved word in MySQL](https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-N) — Luke Woodward, Aug 28 '19 at 20:26
@LukeWoodward It's not a reserved word, there's no `(R)` next to it. — Barmar, Aug 28 '19 at 20:26
@Barmar fair enough, it's just odd that the error is reported immediately after `Name`. — Luke Woodward, Aug 28 '19 at 20:28
@muth: Should it not qouted: `Name ='%s'`, there is a **blank** in `'Rafael Nadal'` — stovfl, Aug 28 '19 at 20:55
@LukeWoodWard you were right, I just needed to add backticks to Name. Thanks! — muth, Aug 28 '19 at 23:00

Barmar · Answer 1 · 2019-08-28T20:38:21.887

0

Since % is used to mark placeholders in the SQL, you have to double it if you want to use it literally.

mySql_insert_query = """SELECT `First_Serve(%%)` FROM `serve2` WHERE Name =%s"""

See How do I escape % from python mysql query

edited Aug 28 '19 at 20:38

answered Aug 28 '19 at 20:27

Barmar

741,623
53
500
612

Thanks but that didnt work. It returns the error "Unknown column 'First_Serve(%%)' in 'field list'". I'm pretty sure its a problem with the last part of the query because when I replace the %s with just 'Rafael Nadal' it works perfectly. – muth Aug 28 '19 at 20:36
I've linked to another question like this. But maybe it's different in MySQL Connector than PyMySQL. – Barmar Aug 28 '19 at 20:40

Error selecting from sql table using mysql placeholders

1 Answers1