MySQLdb LIKE '%%%s%%' problem

[John Machin]

On Jan 14, 8:55 pm, gumbah <joost.ruy... at gmail.com> wrote:
> Hi John,
>
> thanks a lot for your quick reply!

Please don't top-post.
Please answer the question """You don't say what "doesn't work"
means ... did you get exceptions, or just silently not updating?"""

>
> I tried all of your suggestions but none of them work... I have a clue
> on why it is failing: MySQLdb seems to quote the % characters or
> something...
>
> Even when i do:
> cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
> null and fieldy like '%therealvalue%' "

Please stop stuffing around with the 'trial 1' method.

What SQL data type is fieldy?
What Python datatype is therealvalue coming from?
Do this:
   print type(realvalue), repr(realvalue)
Are you sure that the datatypes are compatible? Is there perhaps a
Unicode encoding problem?

Try this:
cursor.execute("select fieldy from tablename")
# dig out a sample result
print type(sample_result), repr(sample_result)
print therealvalue in sample_result

I suggest that you avoid UPDATE and the supersensitive fieldy none of
whose values you can reveal, and try the code on a column with simple
noncontroversial data e.g. if you have an address table with the U.S.
state code in it, we can try a simple query to find all addresses that
are in a state that contains some letter:
state_letter = "A"
sql = "select address_id, state from address where state like %s"
cursor.execute(sql, ("%" + state_letter + "%", ))
That would be my best shot at getting it to work. It is based on this:
http://mail.python.org/pipermail/python-list/2003-August/218382.html

If that works, try applying it to a query on fieldy and then to your
original problem.

Cheers,
John