MySQLdb LIKE '%%%s%%' problem

John Machin sjmachin at lexicon.net
Wed Jan 14 07:01:14 EST 2009


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',)]

Cheers,
John



More information about the Python-list mailing list