passing many arguments to one placeholder of django cursor (placeholer in IN)

Question

I mean something like this:

from django.db import connection
cursor=connection.cursor()
cursor.execute('SELECT * FROM mytable where id IN (%s)', [params])

Parameters can not be just iterable - it doesn't work. Cannot be also in CSV format due escaping value by db handler.

How to use placeholder within IN ?

---

By CSV is wrong, I mean that for params=['1','2','3','4','5']

c.execute('select * from mytable where id in (%s)', [','.join(params)])

will produce:

select * from mytable where id  in ('1,2,3,4,5')

but correct sql is:

select * from mytable where id  in (1,2,3,4,5)

and it seems to be hard to achieve with placeholders.

Answer 1

You cannot use IN for this since indeed, in expects a sequence of ints, but the ORM translates a list to an ARRAY, and if you use join you'll end up with a string.

The solution is to use the equivalent ANY. The formatting is slightly different, but this should work for you:

c.execute('select * from mytable where id = ANY(%s)', [params])

Given params = [1, 2, 3, 4, 5], the resulting SQL will be:

SELECT * FROM mytable where id = ANY(ARRAY[1, 2, 3, 4, 5])

Note that this requires the list of ids to be made up of ints, so if you have a list of strings be sure to convert them first.