How to update postgres array on Python-CodePudding

I selected column "user_list" in 'users' table and fetched to a python variable called "u_list". I appended 'item' in it and tried to update "user_list", but got a lot of errors. I tried searching on stackoverflow, but nothing helped.

code:

cursor.execute(f'SELECT user_list FROM users WHERE id=442392434899681280')
u_list = cursor.fetchone()[0]
u_list.append('item')
cursor.execute('UPDATE users SET user_list = {} WHERE id = 442392434899681280'.format(u_list))
data_base.commit()

but got an error:

Traceback (most recent call last):
  File "d:\workspace\sabo\test.py", line 30, in <module>
    cursor.execute('UPDATE users SET user_list = {} WHERE id = 442392434899681280'.format(u_list))
psycopg2.errors.SyntaxError: syntax error at or near "["
LINE 1: UPDATE users SET user_list = ['item'] WHERE id = 4423924348996...

Another try and error code:

cursor.execute(f'SELECT user_list FROM users WHERE id=442392434899681280')
u_list = cursor.fetchone()[0]
u_list.append('item')
cursor.execute("UPDATE users SET user_list= (%s) WHERE id = 442392434899681280", (u_list))
data_base.commit()

error:

  File "d:\workspace\sabo\test.py", line 33, in <module>
    cursor.execute("UPDATE users SET user_list= (%s) WHERE id = 442392434899681280", (u_list))
psycopg2.errors.InvalidTextRepresentation: malformed array literal: "item"
LINE 1: UPDATE users SET user_list= ('item') WHERE id = 4423924348996...
                                    ^
DETAIL:  Array value must start with "{" or dimension information.

CodePudding user response：

Think about it like if you were typing the query yourself. As the error statement specifies, to PostgreSQL arrays must star with '{' so they will have to be within curly braces. If you were to write the query in SQL yourself it would look like this:

UPDATE users SET user_list = '{"foo", "bar", "item"}' WHERE id = 442392434899681280;

Handcrafted way

So in Python, it would have to be done like this:

cursor.execute(f'SELECT user_list FROM users WHERE id=442392434899681280')
u_list = cursor.fetchone()[0]
u_list.append('item')
cursor.execute('UPDATE users SET user_list = \'{{{}}}\' WHERE id = 442392434899681280'.format(','.join(['"{}"'.format(v) for v in u_list])))
data_base.commit()

Notice the three curly braces in the formatting, two are to escape so one '{' remains an the third is for the formatting. Also, if your list is not of strings you will have to convert it before joining.

Let psycopg2 handle it

psycopg2's docs also state that Python lists are converted to PostgreSQL ARRAY so the above would be done like like this:

cursor.execute("UPDATE users SET user_list= %s WHERE id = 442392434899681280", (u_list,))

You are missing a comma after the list and there is an extra parentheses surrounding %s in your code sample.