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
– Kein-Wai, Commented Oct 29, 2014 at 11:19
Now try removing the ; As far as I remember it ok for editors, not for db api — haraprasadj
– haraprasadj, Commented Oct 29, 2014 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
– haraprasadj, Commented Oct 29, 2014 at 12:17

haraprasadj · Accepted Answer · 2014-10-29 11:44:33Z

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, 2014 at 11:44

answered Oct 29, 2014 at 11:26

haraprasadj

1,0871 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

haraprasadj Over a year ago

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.

Daniel Roseman Over a year ago

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.

haraprasadj Over a year ago

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"

Daniel Roseman Over a year ago

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.

haraprasadj Over a year ago

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?

|

Sundar Rajan · Accepted Answer · 2014-10-29 11:30:00Z

0

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

try this dnt need to put ; near %s

edited Oct 29, 2014 at 11:30

answered Oct 29, 2014 at 11:14

Sundar Rajan

1331 gold badge1 silver badge12 bronze badges

7 Comments

Kein-Wai Over a year ago

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 Over a year ago

still giving me the error. i think is the way it treats %s...not sure why

Sundar Rajan Over a year ago

u query is in the typo error normally if u use wildcards % are only applicable in LIKE-queries.

Daniel Roseman Over a year ago

Argh, so much wrongness. %s is a substitution parameter, nothing to do with like queries.

Daniel Roseman Over a year ago

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.

|

TheExorcist · Accepted Answer · 2017-03-30 15:21:34Z

0

I did

enter code here

got this!!

answered Mar 30, 2017 at 15:21

TheExorcist

2,0341 gold badge22 silver badges25 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:31:06Z

0

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.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Apr 14, 2017 at 17:51

Tchotchke

3,1213 gold badges27 silver badges38 bronze badges

Collectives™ on Stack Overflow

syntax error in select from where mysql statement

4 Answers 4

6 Comments

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related