How to pass a list of values for an `in` SQL clause in Django

Question

I need to use a raw SQL query in a Django app I am writing. The SQL query contains an in clause in the where statement:

select *
from abc_mymodel
where some_fk in (1,2,3)
and some_status = 'foo'

I am a big proponent of passing SQL params as parameters. This is easily done for the single value ones. However, I am not sure how (or if) this can be done for in clauses. Ideally I would like to do something like:

sql = """
    select *
    from abc_mymodel
    where some_fk in %s
    and some_status = %s
"""
my_list = [1,2,3]
my_status = 'foo'
trinkets = MyModel.objects.raw(
    sql,
    params=(my_list, my_status)
)

I know I can use string composition to write the in clause and use params for the remaining values. However, I am curious if it is possible to use params for in clauses as well.

This question asks explicitly for a raw sql query, so I don't think it's a duplicate. — Luca, May 27 '14 at 11:18

score 11 · Accepted Answer · answered May 27 '14 at 14:09

According to the Django docs on raw(),

params is a list or dictionary of parameters. You’ll use %s placeholders in the query string for a list... Such placeholders will be replaced with parameters from the params argument.

Conveniently, raw queries are very easy to test in a django shell. If you simply input your raw() query, it will print a RawQuerySet with your resulting query:

>>> from django.contrib.auth.models import User
>>> pk_list = (1, 3, 6)
>>> User.objects.raw('SELECT * FROM auth_user WHERE id IN %s', params=[pk_list])
<RawQuerySet: 'SELECT * FROM auth_user WHERE id IN (1, 3, 6)'>

As you can see, whatever is in params is placed into the raw query. In your example, you need to change my_list to a tuple (or else it will look like "IN [1,2,3]") and change your params input to a list (params=[my_list, my_status]).

Note, this does not work for tuples of only one item. The string output is in the python format (34,) which SQL will choke on. — Justin Meiners, Sep 12 '16 at 04:30

score 0 · Answer 2 · answered May 29 '14 at 07:05

0

try it this way for django orm type querying :

list = [1,2,3]
choices = Model.objects.filter(columnName__in=list)

In your case :

list = [1,2,3]
choices = abc_mymodel.objects.filter(some_fk__in=list)

answered May 29 '14 at 07:05

jacquel

946
1
7
10

3

That's not quite what i was asking about. I needed a way to do this with raw SQL queries. – Krystian Cybulski May 30 '14 at 14:27
1

I came here looking for this. – dfrankow Aug 08 '19 at 21:21

How to pass a list of values for an `in` SQL clause in Django

2 Answers2

Linked