In message <[EMAIL PROTECTED]>, Dennis Lee Bieber wrote: > On Sun, 20 Jul 2008 18:43:03 -0700, "bruce" <[EMAIL PROTECTED]> > declaimed the following in comp.lang.python: >> >> mysql cmd - select * from foo where dog like "%small%"; >> >> sql ="""select * from foo where dog like "%%%s%%" """ >> c.execute(sql, (var,)) > > The execute statement is responsible quoting your literals, so the > final statement you end up submitting looks like: > > select * from foo where dog like ""%"xxx"%"" > > or some variant thereof. > > Anything parameterized with %s must be a complete term! > > Try: > > sql = "select * from foo where dog like %s" > c.execute(sql, ("%" + var + "%",) ) > > IOW: you need to massage the variable search term to include the > wildcard % FIRST, then let MySQLdb substitute it (with surrounding > quotes and escapes) into the SQL.
More general, less pitfall-prone solution: select * from foo where dog like %s" % SQLString("%" + EscapeSQLWild("small") + "%") where SQLString and EscapeSQLWild are defined as in <http://groups.google.co.nz/group/comp.lang.python/msg/ed7e561036895cbe>. -- http://mail.python.org/mailman/listinfo/python-list