DB-API execute params, am I missing something?

[John Machin]

On May 28, 11:12 am, Lawrence D'Oliveiro <l... at geek-
central.gen.new_zealand> wrote:
> In message <784h2cF1kem0... at mid.uni-berlin.de>, Diez B. Roggisch wrote:
>
> > Lawrence D'Oliveiro wrote:
>
> >> In message <mailman.766.1243354300.8015.python-l... at python.org>, Dennis
> >> Lee Bieber wrote:
>
> >>> Notice that db.literal() call? That's part of the mechanism used to
> >>> escape and quote parameters -- it only returns strings that are safe for
> >>> insertion into the SQL statement.
>
> >> Does it deal with "like"-wildcards?
>
> > Why shouldn't it?
>
> > cursor.execute("select * from table where column like %s", "%name%")
>
> What if the string you're searching for includes a "%" or "_" character?

AFAIK:
The LIKE argument is a just any ol' string expression.
The wrapper neither knows nor cares that it is a LIKE argument.
All the wrapper will do when presented with a string value is to quote
apostrophes if needed so that the result is a valid SQL literal e.g.
'O''Brien'
If you need to escape % or _ in a LIKE argument, do whatever the host
convention is.
E.g. you are searching for text that contains literally "5% discount",
with SQLite you could do:
[avoiding backslashes and concomitant confusion]
  LIKE '%5~% discount%' ESCAPE '~'
on the command line, so using DB-API:
  like_expr = user_input.replace("~", "~~").replace("%", "~%").replace
(etc etc)
  cursor.execute(
     "... LIKE ? ESCAPE '~' ...",
     (..., like_expr, ...)
     )

HTH,
John