MySQL syntax error in python but working in MySQL shell

Question

I am using MySQLdb in python

I have a table let table1 with 4 fields one is index which is PRIMARY KEY and suppose others are field2, field3, field4. Since field2 is not unique, I have many rows with same value for this field.

Now when I query select field3,field4 from table1 where field2=example, I get a MySQL syntax error near 's'. This 's' belongs to 'select'.
To debug it I printed the query in runtime and pasted it in MySQL shell where it returned all the rows matching the where clause.

Here is my actual python code

query = "select `field3`,`field4` from `" + atable + "` where `field2` = '"+avalue+"'"
cur.execute(query)
temp = cur.fetchall()



 Error:
_mysql_exceptions.ProgrammingError: (1064, "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")

falsetru · Accepted Answer · 2013-07-21 13:06:07Z

4

Remove backticks

query = "select field3,field4 from " + atable + " where field2 = %s"
cur.execute(query, (avalue,))
temp = cur.fetchall()

edited Jul 21, 2013 at 13:06

answered Jul 21, 2013 at 12:18

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

5 Comments

Sudhanshu Mishra Over a year ago

Didn't work... but yes the error is changed. Syntax error shifted to last part

falsetru Over a year ago

@SudhanshuMishra, What are the values of atable, avalue?

Sudhanshu Mishra Over a year ago

This one is working query = "select field3,field4 from " + atable + " where field2 = %s" % avalue

falsetru Over a year ago

@SudhanshuMishra, "... %s" % avalue is not good idea. It is vulnerable to SQL injection.

Ander2 Over a year ago

@falsetru execute params should be a tuple, not a list. Like this: cur.execute(query, (avalue,))

Prahalad Deshpande · Accepted Answer · 2013-07-21 13:38:17Z

0

falsetru's solution is almost correct - however there is one little problem with the SQL query:

The original query and the related code is as shown below:

query = "select field3,field4 from " + atable + " where field2 = %s"
cur.execute(query, (avalue,))
temp = cur.fetchall()

Note the past part of the where clause. Here you have %s as a string hence the correct way to write the SQL would be:

query = "select field3,field4 from " + atable + " where field2 = '%s'"

Note that %s has been enclosed within single quotes (') character.

answered Jul 21, 2013 at 13:38

Prahalad Deshpande

4,7871 gold badge23 silver badges23 bronze badges

2 Comments

Ander2 Over a year ago

You don't need to enclose the %s with single quotes, MySQLdb takes care of it. @falsetru 's solution is correct.

Prahalad Deshpande Over a year ago

Thanks for the note - I was looking at it from a pure SQL perspective and did not know about this tiny tidbit. Thank you once again :)

Collectives™ on Stack Overflow

MySQL syntax error in python but working in MySQL shell

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related