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,))

Martijn Pieters · Accepted Answer · 2013-05-08 08:33:19Z

2

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,))

answered May 8, 2013 at 8:33

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

Comments

Yavar · Accepted Answer · 2013-05-08 08:52:18Z

0

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)

edited May 8, 2013 at 8:52

answered May 8, 2013 at 8:14

Yavar

11.9k5 gold badges34 silver badges63 bronze badges

3 Comments

Martijn Pieters Over a year ago

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

@Martjin: Actually now it works even with part='%part%' as above

Martijn Pieters Over a year ago

You still are matching against lowercase text instead of uppercase. The meaning is different from the original.

Collectives™ on Stack Overflow

Python SQL ValueError

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related