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.

Answer 1

According to the Django documentation 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]).