ups ups - 1 year ago 83
SQL Question

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

I mean something like this:

from django.db import connection
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

By CSV is wrong, I mean that for

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download