db question

[bruce]

Hi...

simple test

mysql cmd - select * from foo where dog like "%small%";

sql ="""select * from foo where dog like "%%%s%%" """
c.execute(sql, (var,))


the above doesn't work, and I can't seem to figure out how to display/print
out the sql as i't actually been excuted, so I can see where the issue is.

i've tried to "/" escape, as well as a few other things, now of which shed
any light on the issue. haven't found any information via google either.

the above works if i have something like
sql="""select * from foo where dog=%s"""
c.execute(sql,(var,))

so.. any help/pointers on what i've screwed up/missed would be helpful.

thanks

 

[Lawrence D'Oliveiro]

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>.