Parameter escaping for IN queries using Django raw SQL cursor

Searching for this little tidbit didn’t turn anything up and I had to figure it out through trial and error, so I figured I’d share the solution in a publicly searchable location:

If you need to perform an IN lookup in a Django raw SQL cursor.execute() invocation, you can do so by passing the list as a tuple. For instance:

from django.db import connection

# This could be a list, a set, or whatever; probably generated programmatically
list_of_values = [1, 2, 3]
with connection.cursor() as cursor:
    cursor.execute("""
        SELECT *
        FROM my_model_table
        WHERE some_value IN %s
    """, [
        tuple(list_of_values),
    ])
    results = cursor.fetchall()
    # Do something with your results

Not complicated, but also as far as I can tell largely undocumented. Enjoy!

2 responses to “Parameter escaping for IN queries using Django raw SQL cursor”

Leave a response

  1. In Django 6 this fails:

    “`
    Traceback (most recent call last):
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/dummy.py”, line 86, in
    cursor.execute(
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/backends/utils.py”, line 122, in execute
    return super().execute(sql, params)
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/backends/utils.py”, line 79, in execute
    return self._execute_with_wrappers(
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/backends/utils.py”, line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/backends/utils.py”, line 100, in _execute
    with self.db.wrap_database_errors:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/utils.py”, line 94, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/django/db/backends/utils.py”, line 105, in _execute
    return self.cursor.execute(sql, params)
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    File “/Users/peterbengtsson/dev/PETERBECOM/django-peterbecom/.venv/lib/python3.12/site-packages/psycopg/cursor.py”, line 117, in execute
    raise ex.with_traceback(None)
    django.db.utils.ProgrammingError: syntax error at or near “‘(1,2,3)'”
    LINE 4: WHERE id IN ‘(1,2,3)’
    ^
    “`

    • Ian Beck says:

      Looks like they must have changed how lists are escaped, since it’s embedding it in a string. That feels like a bug to me; might be worth reporting to Django.

Leave a response