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

Using Django Using the ORM
1

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: ...om 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:
    cursor.execute(sql=self.raw_query(),
                           params={
                               "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.

2

It works for me if I pass the list directly.
e.g.:

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))
3

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

3

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

Screenshot 2567-07-11 at 10.29.56
Screenshot 2567-07-11 at 10.29.561076×886 97.5 KB

4

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

5

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 ...
6

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
7

It works perfectly, thank you!