How to use a list/tuple/array in Django with a raw SQL cursor

Title: How to use a list/tuple/array in Django with a raw SQL cursor - Peterbe.com

URL Source: http://www.peterbe.com/plog/how-to-use-a-listtuplearray-in-django-with-a-raw-sql-cursor

Markdown Content: This does not work:

``` from django.db import connection

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() ```

It will give you:

django.db.utils.ProgrammingError: syntax error at or near "'(1,2,3)'" LINE 4: WHERE id IN '(1,2,3)' It used to work with psycopg v2. Now, in psycopg v3, you have to use the ANY operator. See "You cannot use IN %s with a tuple"

This will work:

``` from django.db import connection

list_of_values = [1, 2, 3] with connection.cursor() as cursor: cursor.execute( """ SELECT * FROM my_model_table WHERE some_value = ANY(%s) """, [ list_of_values, ], ) results = cursor.fetchall() ```

Note the ANY(%s), and instead of a list that has a tuple, it's a list that has a list.

What About a List of Strings

Consider...

``` from django.db import connection

-list_of_values = [1, 2, 3] +list_of_values = ['foo', 'bar', 'fiz'] with connection.cursor() as cursor: cursor.execute( """ SELECT * FROM my_model_table WHERE some_value = ANY(%s) """, [ list_of_values, ], ) results = cursor.fetchall() ```

That will result in:

django.db.utils.DataError: invalid input syntax for type integer: "foo" LINE 4: WHERE some_value = ANY('{foo,bar,fiz}') My solution was to rewrite the SQL string itself and treat each value as a parameter each. In other words, the SQL string, before being sent to cursor.execute(...) will contain something like this:

AND ( some_value = % OR some_value = % OR some_value = % OR some_value = % OR -- ...etc... some_value = % )

This will work and is safe:

``` from django.db import connection

list_of_values = ["foo", "bar", "fiz"] with connection.cursor() as cursor: cursor.execute( f""" SELECT * FROM my_model_table WHERE ({" OR ".join(["some_value = %s" for _ in list_of_values])}) """, list_of_values, ) results = cursor.fetchall() ```