MySQLdb LIKE '%%%s%%' problem

[Steve Holden]

John Machin wrote:
> On Jan 14, 9:42 pm, Steve Holden <st... at holdenweb.com> wrote:
> 
>> 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("'", "''"))
> 
> It appears possible to parameterise LIKE arguments in sqlite3:
> 8<--- code
> import sqlite3
> tests = [
>     ["select * from foo", None],
>     ["select * from foo where text like '%o%'", None],
>     ["select * from foo where text like ?", "o"],
>     ["select * from foo where text like ?", "a"],
>     ]
> conn = sqlite3.connect("c:/junk/sql_like/foodb")
> curs = conn.cursor()
> for testno, test in enumerate(tests):
>     sql, parm = test
>     print "\n=== Test %d ===" % (testno + 1)
>     print "sql =", sql
>     print "parm =", parm
>     if parm is None:
>         curs.execute(sql)
>     else:
>         arg2 = "%" + parm + "%"
>         curs.execute(sql, (arg2, ))
>     results = curs.fetchall()
>     print "results:", results
> 8<--- output
> 
> === Test 1 ===
> sql = select * from foo
> parm = None
> results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',),
> (u'echo',), (u'foxtrot',)]
> 
> === Test 2 ===
> sql = select * from foo where text like '%o%'
> parm = None
> results: [(u'bravo',), (u'echo',), (u'foxtrot',)]
> 
> === Test 3 ===
> sql = select * from foo where text like ?
> parm = o
> results: [(u'bravo',), (u'echo',), (u'foxtrot',)]
> 
> === Test 4 ===
> sql = select * from foo where text like ?
> parm = a
> results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',)]
> 
Thanks. So this is probably a driver, or a platform, restriction.

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