MySQLdb LIKE '%%%s%%' problem

[Steve Holden]

gumbah 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
> print fillsql
> cursor.execute(fillsql)
> 
> #also tried:
> #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
> null and fieldy like %s ", "%%%s%%" % certainvalue)
> 
> 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!!!
> 
> Can anyone tell me what i am doing wrong??
> 
1. You can't test for equality with NULL.

2. You don't have quote marks around the LIKE argument.

cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx IS
NULL and fieldy like '%s' ", "%%%s%%" % certainvalue)

*might* work.

3. I can't be certain my experience with PostgreSQL extends to MySQl,
but I have done experiments which prove to my satisfaction that it isn't
possible to parameterize LIKE arguments. So the only way to do it
appears to be to build the query yourself. This means that you will need
to make sure the string is made "safe", typically by replacing each
occurrence of the string "'" with "''" to retain the syntactic integrity
of the SQL statement. So finally, try

cursor.execute("""UPDATE tablename set fieldx='test'
                  WHERE flfieldx IS NULL
                  AND fieldy LIKE '%%%s%%'""" %
                  certainvalue.replace("'", "''"))

regards
 Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/