Re: [pysqlite] binding arguments for 'in' operator

Alex Bogma wrote:
> Hello,
> 
> Does anybody know how to bind argument for statement like this:
> 
> cur.execute('select id, title from table where id in (:args)', {
> 'args':[1,2,3,4,5] }).fetchall()
> 
> I got "Error binding parameter 0 - probably unsupported type."
> Passing { 'args': '1,2,3,4,5' } don't work too (no errors,but no data,
> it just tries to find a record with id='1,2,3,4,5')

You cannot bind this way, as you have found out.

> If this is impossible, then tell me please, which function would be
> 'best practice' for string escaping.

I'd suggest an approach like the following:

create temporary table search_ids(search_id);

cursor.executemany("insert into search_ids(search_id) values (?)", 
[(1,), (2,), (3,)])

cursor.execute("select ... from mytable inner join search_ids on 
(mytable.id=search_ids.search_id) and ...")

cursor.execute("drop table search_ids")

That's the most clean way to solve problems like this, in my opinion.


Another approach is to build parts of the SQL query manually, of course.

 >>> ids = [3,4,5,6]
 >>> str(tuple(ids))
'(3, 4, 5, 6)'
 >>> sql = "select ... where id in %s" % str(tuple(ids))
 >>> sql
'select ... where id in (3, 4, 5, 6)'
 >>>

-- Gerhard