Syntax error near ARRAY when I try to execute a multiple update in python with psycopg2

Question

I have a problem when I try to perform a multiple update of my table using psycopg2. When I run this query

query ='UPDATE table SET isValid = false where id = %s and customerid = %s and filed in %s'
data = (id,customerid, fieldlist)
cursor.execute(query, data)

where id and customer id are both guid and fieldlist is a list of string I obtain this error syntax error at or near "ARRAY":

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.errors.SyntaxError: syntax error at or near "ARRAY"
LINE 1: ...alse where id = 2 and customerid = 1 and filed in ARRAY['22/11/20', '23...
                                                             ^

I know that the problem is in my fieldlist variable but I can't find a clever way to solve my problem. Thanks

Answer 1

Per psycopg2 docs Type adaption:

list ARRAY Lists adaptation

Lists adaption:

Python lists are converted into PostgreSQL ARRAYs:

cur.mogrify("SELECT %s;", ([10, 20, 30], ))

'SELECT ARRAY[10,20,30];'

Note

You can use a Python list as the argument of the IN operator using the PostgreSQL ANY operator.

ids = [10, 20, 30] cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,))

Furthermore ANY can also work with empty lists, whereas IN () is a SQL syntax error.

Note

Reading back from PostgreSQL, arrays are converted to lists of Python objects as expected, but only if the items are of a known type. Arrays of unknown types are returned as represented by the database (e.g. {a,b,c}). If you want to convert the items into Python objects you can easily create a typecaster for array of unknown types.

So:

... filed = ANY(%s) ...