python and mysqldb: syntax error on perfectly good query ?

Question

My code:

selectQ ="""SELECT * FROM  %s WHERE %s = %%s order by RAND() limit %s""" % (self.table,self.columnSpecName,limit),
self.db.execute(str(selectQ),(idKey))

I get this exception: 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 ''SELECT * FROM table WHERE person_oid = 16 order by RAND() limit 10',)' at line 1")

However if I copy the query and run it through mysql it runs just fine.

What am I overlooking ?

It substitutes %%s with %s so it can be used in db.execute which will substitute it with idKey — Lucas Kauffman
– Lucas Kauffman, Commented Feb 22, 2012 at 12:46

warvariuc · Accepted Answer · 2012-02-22 12:57:59Z

3

You should look closer to:

syntax to use near ''SELECT * FROM table WHERE person_oid = 16 order by RAND() limit 10',)' at line 1")

And you will see double apostrophe at the beginning of the query and ,)' at the end of it.

selectQ ="""SELECT * FROM  %s WHERE %s = %%s order by RAND() limit %s""" % (self.table,self.columnSpecName,limit),
self.db.execute(str(selectQ),(idKey))

You've got a comma at the end of the first line, making it a tuple

Remove it and you will not need str(selectQ) on the second line.

edited Feb 22, 2012 at 12:57

answered Feb 22, 2012 at 12:52

warvariuc

60.1k45 gold badges183 silver badges234 bronze badges

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

Comments

aweis · Accepted Answer · 2012-02-22 12:52:33Z

2

You have a comma to much in the first line (at the end):

selectQ ="""SELECT * FROM  %s WHERE %s = %%s order by RAND() limit %s""" % (self.table,self.columnSpecName,limit),
self.db.execute(str(selectQ),(idKey))

Change it to:

selectQ ="""SELECT * FROM  %s WHERE %s = %%s order by RAND() limit %s""" % (self.table,self.columnSpecName,limit)
self.db.execute(str(selectQ),(idKey))

answered Feb 22, 2012 at 12:52

aweis

5,6564 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

python and mysqldb: syntax error on perfectly good query ?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related