MySQLdb LIKE '%%%s%%' problem

gumbah joost.ruyter at gmail.com
Wed Jan 14 10:55:58 CET 2009


Hi John,

thanks a lot for your quick reply!

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%' "

or:
cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
null and fieldy like '%%therealvalue%%' " # escaping the %

it is not updating the database...

Maybe I am completely overlooking something, but I am pretty lost
here... Googling this it seems as if no one is using "LIKE '%value%'"
type queries with Python & mySQL...

Anyone any other thoughts?

regards,
Joost


On 14 jan, 10:14, John Machin <sjmac... at lexicon.net> wrote:
> On Jan 14, 7:31 pm, gumbah <joost.ruy... at gmail.com> wrote:
>
> > I have this really strange problem. I hope someone can help:
>
> > I am trying to update a database like so:
>
> > UPDATE `tablename` set fieldx='test' WHERE flfieldx = null and fieldy
> > like '%certainvalue%'
>
> > My Python code looks like this:
>
> > fillsql = "UPDATE `tablename` set fieldx='test' WHERE flfieldx = null
> > and fieldy like '%%%s%%' " % certainvalue
>
> call this trial 1
>
> > print fillsql
> > cursor.execute(fillsql)
>
> > #also tried:
> > #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
> > null and fieldy like %s ", "%%%s%%" % certainvalue)
>
> call this trial 2
>
>
>
> > But it doesn't work... But when i copy and past the SQL (printed by
> > "print fillsql" line) and execute that in phpMyAdmin, it does work!!!
>
> You don't say what "doesn't work" means ... did you get exceptions, or
> just silently not updating?
>
>
>
> > Can anyone tell me what i am doing wrong??
>
> Well the "trial 1" method is guaranteed not to work if certainvalue
> contains an apostrophe e.g. "O'Reilly". In any case, you should never
> build your own SQL statement like that; use the "trial 2" method -- it
> will do whatever is necessary in the way of reformatting or escaping
> your input.
>
> I know near nothing about mySQLdb, but here are some comments based on
> general SQL experience:
> (1) `tablename` isn't SQL syntax that I've seen before; perhaps it
> works in phpMyAdmin but not in cursor.execute()
> (2) similarly = NULL ... I'd expect IS NULL
> (3) It is updating but your script and your phpMyAdmin session are
> pointing at different instances of the database
> (4) For trial 2 method, shouldn't the 2nd arg of cursor.execute()
> should be a sequence e.g. ("%%%s%%" % certainvalue, ) ?
>
> HTH
> John




More information about the Python-list mailing list