syntax error in select from where mysql statement

Question

I got an syntax error in this code:

msql=("SELECT FILE_NAME FROM file_processed WHERE FILE_NAME = %s ;")

if cursor.execute(msql,name):
    return True
else:
    return False

i got an error near '%s' can anybody tell me wherE? thanks!

error: mysql.connector.errors.ProgrammingError: 1064(42000): You have an error in SQL syntax: check Mysql server version for the right syntax to use near '%s' at line 1 Im using mysql 5.6

Now I try this:

msql=("SELECT FILE_NAME FROM file_processed WHERE FILE_NAME='HCTC3153_INF.TXT'; ")
if cursor.execute(msql):
    print "its is in the db"
    return True
else:
    print "its not in db"
    return False

Always return its not in db even is in there... The error raise now is: raise errors.InternalError("Unread result found.") mysql.connector.errors.InternalError: Unread result found.

mysql.connector.errors.ProgrammingError: 1064(42000): You have an error in SQL syntax: check Mysql server version for the right syntax to use near '%s' at line 1 Im using mysql 5.6 — Kein-Wai, Oct 29 '14 at 11:19
Now try removing the ; As far as I remember it ok for editors, not for db api — haraprasadj, Oct 29 '14 at 11:48
@Kein-Wai I checked it in my machine and it fails when I use semi-colon at the end. Also please show where and how you are substituting the values using %s. That would help identify the problems. — haraprasadj, Oct 29 '14 at 12:17

haraprasadj · Answer 1 · 2014-10-29T11:44:33.957

1

Firstly I assume you are using %s for string formatting and not just a file with name %s.

I think you need to use '%s', not %s as in

msql=("SELECT FILE_NAME FROM file_processed WHERE FILE_NAME ='%s'")

Sorry if I misunderstood the use of %s completely

edited Oct 29 '14 at 11:44

answered Oct 29 '14 at 11:26

haraprasadj

1,059
1
8
17

Wow! an instant -1!! Whoever did it, it would help a lot for me to learn from my mistake if you also gave a reason for down-voting it. – haraprasadj Oct 29 '14 at 11:27
It's down-voted because it's wrong, simple as that. You don't quote values when you use parameter substitution with the db API. – Daniel Roseman Oct 29 '14 at 11:36
Thanks for explaining. But my logic is simply that we are creating a query and executing it. Normally if I write the same query to check for file names like FILE1, I would specify the query as "FILE_NAME LIKE 'FILE1'", not "FILE_NAME LIKE FILE1" – haraprasadj Oct 29 '14 at 11:41
But where are you (and Sundar) getting this idea that OP wants to do a LIKE query? That has nothing to do with the question asked or the error he is getting. – Daniel Roseman Oct 29 '14 at 11:42
Sorry I had initially thought it is = but changed seeing above answer, my bad. My correct comments: Normally if I write the same query to check for file with name FILE1, I would specify the query as "FILE_NAME ='FILE1'", not "FILE_NAME = FILE1". So if you are using %s for substitution, dont we still need quotes? – haraprasadj Oct 29 '14 at 11:46
@DanielRoseman I did some research. Thanks for helping me learn that we dont need quotes for db api. But using quotes it still works for me. So I guess I am not wrong, just that what I did was unneeded :) – haraprasadj Oct 29 '14 at 12:09

Sundar Rajan · Answer 2 · 2014-10-29T11:30:00.523

0

msql=("SELECT FILE_NAME FROM file_processed WHERE FILE_NAME LIKE '%s'")

try this dnt need to put ; near %s

edited Oct 29 '14 at 11:30

answered Oct 29 '14 at 11:14

Sundar Rajan

133
1
1
12

mysql.connector.errors.ProgrammingError: 1064(42000): You have an error in SQL syntax: check Mysql server version for the right syntax to use near '%s' at line 1 Im using mysql 5.6 – Kein-Wai Oct 29 '14 at 11:19
still giving me the error. i think is the way it treats %s...not sure why – Kein-Wai Oct 29 '14 at 11:25
u query is in the typo error normally if u use wildcards % are only applicable in LIKE-queries. – Sundar Rajan Oct 29 '14 at 11:27
Argh, so much wrongness. %s is a substitution parameter, nothing to do with like queries. – Daniel Roseman Oct 29 '14 at 11:37
I have no idea what that comment means. As I said to haraprasadj, there is nothing in the question to indicate OP is trying to do a LIKE query. He is simply trying to use parameter substitution to insert the value of a variable into the query. – Daniel Roseman Oct 29 '14 at 11:43
@Daniel IMHO he is not trying to insert in this line msql=("SELECT FILE_NAME FROM file_processed WHERE FILE_NAME = %s ;") he is getting a error as server version for the right syntax to use near '%s' at the line he is missing LIKE operator as well as ' ' in %s – Sundar Rajan Oct 29 '14 at 11:47
@Daniel he should have put that variable definition too. – Sundar Rajan Oct 29 '14 at 11:55

score 0 · Answer 3 · answered Mar 30 '17 at 15:21

0

I did

enter code here

got this!!

answered Mar 30 '17 at 15:21

TheExorcist

1,966
1
19
25

score 0 · Answer 4 · edited May 23 '17 at 10:31

The answer to the first error, which I assume looks like:

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'

Is because cursor.execute() expects a tuple in the second argument, as explained by @mata in this SO post. In your case, your code should be cursor.execute(mysql,(name,)) - notice the comma after name.

I think you're receiving your second error:

mysql.connector.errors.InternalError: Unread result found

because you aren't doing anything with the result. If you added a result = cursor.fetchall() in your if block your code should work.

syntax error in select from where mysql statement

4 Answers4