How to pass list of integers as arguments for django's raw SQL, without formatting string

I’m struggling to figure out how to pass list of integer as arguments for Django’s raw SQL without using f-string method.

I have tried a simple method of

def raw_query(self) -> str:
    return """select * from table where some_id in %s"""

and call with (where ids are list of integer that I already wrap them as tuple)

tupled_ids = tuple(ids)
with connection.cursor() as cursor:
    cursor.execute(self.raw_query(), [ids])

Apparently, this wraps the argument with single quote, making them ‘(1,2,3)’ instead of (1,2,3). Thus, syntax error.

django.db.utils.ProgrammingError: syntax error at or near "'(1,2,3)'"

I’ve also tried

def raw_query(self) -> str:
    return """select * from table where some_id = any(%s)"""

also give me similar error

django.db.utils.DataError: malformed array literal: "(1,2,3,4,5)"
LINE 2: table where some_id = any('(1,2,3...`

I’ve also tried with named params

def raw_query(self) -> str:
    return """select * from table where some_id in %(ids)s"""
with connection.cursor() as cursor:
                               "ids": tuple(ids)

Still produce syntax error.

Looks like django keep wrapping my list with quote. I feel like there must have been an obvious mistake but I can’t really figure it out.

There are other solutions that suggest to generate a place holder and use f-string to format the raw SQL statement, such as.

def raw_query(self, ids: list[int]) -> str:
    placeholder = ','.join(['%s'] * len(ids))
    return f"""select * from table where some_id in ({placeholders})"""

^ This works

Still, I am wondering if there’s a better solution as I don’t want to string format my SQL statement.

Any help is gladly appreciated.

It works for me if I pass the list directly.

In [25]: ids = (1,2,3)

In [26]: cursor = connection.cursor()

In [27]: cursor.execute("select * from auth_user where id in %s", [idl])

In [28]: data = cursor.fetchall()

In [29]: print(len(data))

Is it possible that ids isn’t what you think it is at this point?

Unfortunately no, I try with your example, I also got error. Perhaps I’m using the wrong package? but last I check from documentation it’s from django.db import connection

I’m on django 5.0.6 and postgresql if that matters

What version of psycopg do you have installed? (I also use PostgreSQL)

Thank you for your time, I just want to let you know I really appreciated your help so far!

I’m using 3.2.1 :sweat_smile:

poetry show | grep psycopg
psycopg                         3.2.1          PostgreSQL database adapter ...
psycopg-binary                  3.2.1          PostgreSQL database adapter ...

This is a difference between psycopg2 and psycopg 3.

See the docs at Differences from psycopg2 - psycopg 3.2.2.dev1 documentation for some options here. (I’ve tried a couple of them and they do work - I’m just not sure whether they’re as secure as the original for your use-case - I’m no expert on creating SQL injection exploits.)

1 Like

It works perfectly, thank you!