John Machin wrote: > On Jan 14, 9:42 pm, Steve Holden <st...@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/ -- http://mail.python.org/mailman/listinfo/python-list