Python SQL ValueError

Question

I am getting the following error:

query=query%db.literal(args)
ValueError: Unsupported Format character 'P' (0x50)

Here is my query being executed from python (note that phraseList is a list)

for elem in phraseList:
 cursor.execute("""SELECT PHRASE,COUNT(CASEID) FROM TEST.NER WHERE LABEL LIKE '%PART%' \
 AND CASEID IN (SELECT DISTINCT CASEID FROM TEST.NER WHERE LABEL LIKE '%CONDITION%'\
 AND PHRASE LIKEPHRASE LIKE %s""",(elem,))

Answer 1

MySQLdb overloads the Python string formatting syntax, and the %P part of %PART% is seen as a string formatting expression.

To prevent this you need to double the character to %%:

for elem in phraseList:
 cursor.execute("""SELECT PHRASE,COUNT(CASEID) FROM TEST.NER WHERE LABEL LIKE '%%PART%%' \
 AND CASEID IN (SELECT DISTINCT CASEID FROM TEST.NER WHERE LABEL LIKE '%%CONDITION%%'\
 AND PHRASE LIKEPHRASE LIKE %s""",(elem,))

Answer 2

Here is what made the error disappear:

for elem in phraseList:
 part='%part%'
 condition='%condition%'
 query="SELECT PHRASE,COUNT(CASEID) FROM TEST.NER WHERE LABEL LIKE %s \
 AND CASEID IN (SELECT DISTINCT CASEID FROM TEST.NER WHERE LABEL LIKE %s \
 AND PHRASE LIKE %s)"
 params=(part,condition,elem)
 cursor.execute(query,params)

• Martijn Pieters: Now part and condition are interpolated without % wildcards and have been lowercased. Although using them as SQL parameters would help here, that is not going to work if you change the meaning.
• Yavar: @Martjin: Actually now it works even with part='%part%' as above
• Martijn Pieters: You still are matching against lowercase text instead of uppercase. The meaning is different from the original.