Manual parameter substitution in sqlite3

[Tim Chase]

On 2017-03-01 04:40, Chris Angelico wrote:
> curs.execute("select * from mumble where key in (" +
> ",".join(["?"]*len(keys)) + ")", keys)
> 
> If this is combined with another parameter, it'd be messier, but you
> could do something like:
> 
> curs.execute("select * from mumble where key in (" +
> ",".join(["?"]*len(keys)) + ") and category = ?",
>     tuple(keys) + (cat,))
> 
> Either way, you're still letting the sqlite connector do the
> processing of the elements, but handling the collection yourself.

These are the methods I use both with sqlite and with the SQL Server
adapter (at $DAYJOB).  Works great and also pretty easy to debug if
you preformulate the strings into a variable:

  PLACEHOLDER = "?" # SQL Server's place-holder

  params = [3, 1, 4, 5, 9]
  field2 = "Some value"

  sql = """
    SELECT *
    FROM tbl
    WHERE
      field1 IN (%(all_placeholders)s)
      AND field2 = %(placeholder)s
    """ % dict(
     placeholders=",".join(
       PLACEHOLDER
       for _
       in params
       ),
     placeholder=PLACEHOLDER,
     )
  cur.execute(sql, params + [field2,])

-tkc