Question 88849 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    Since your fieldlist variable is of type ARRAY but you are using it as a Tuple list. You’ll have to utilise the tuple(fieldlist).

    Like so:

    query ='UPDATE table SET isValid = false WHERE id = %s AND customerid = %s AND filed IN %s'
data = (id, customerid, tuple(fieldlist))
cursor.execute(query, data)

    Refer to documentation for more details

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search