CODEWITHSUNDEEP
pythonmysql
lefterav
lefterav

Reputation: 15973

Python MySQLdb query returns error when quotes added to the arguments

I am running the following code, which gives me an error

import MySQLdb as mdb

source_sentence = "%Barack Obama%"
filterquery = ""
try:
    con = mdb.connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB)
    cur = con.cursor()

    print "SELECT sentence_id FROM translation_all WHERE source_sentence LIKE '%s' %s ORDER BY id" % (source_sentence, filterquery)
    cur.execute("SELECT sentence_id FROM translation_all WHERE source_sentence LIKE '%s' %s ORDER BY id", (source_sentence, filterquery))

    uid = cur.fetchone()
    print uid
except mdb.Error, e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:    
    if con:    
        con.close()

The error is the following:

SELECT sentence_id FROM translation_all WHERE source_sentence LIKE '%Barack Obama%'  ORDER BY id
Error 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 '%Barack Obama%'' '' ORDER BY id' at line 1

If I replace single quotes with double ones at the query, there is no error, but an empty query is given

SELECT sentence_id FROM translation_all WHERE source_sentence LIKE "%Barack Obama%"  ORDER BY id
None

Funnily enough, both queries above, return results when executed directly on mysql through commandline or phpmyadmin. Do you have any idea why this may be failing?

Upvotes: 0

Views: 805

Answers (1)

mata
mata

Reputation: 69012

When you're passing parameters to cur.execute, the dbapi already does the quoting for you. That means don't use:

... LIKE '%s' %s ORDER BY id

but

... LIKE %s ORDER BY id

You'll notice that i've removed the second %s as it's an error having it here. When it's substituted with '' the query doesn't make sense.

Don't let yourself be fooled by the string that is printed before executing the query. That isn't the query that gets executed:

"... %s" % ('spam',)

does something completely different to a query then:

cursor.execute("... %s", ('spam',))

Upvotes: 2

Related Questions