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