Python List to PostgreSQL Array

Question

I have a list:

[u'ABC', u'DEF', u'GHI']

I have to insert it into a postgresql array: (ALTER TABLE "aTable" ADD COLUMN "Test" text[];)

The syntax for adding data to the postgresql is:

update "aTable" SET "Test" = '{"ABC", "DEF", "GHI"}'

How can I convert the list to the correct format?

Answer 1

Note that with psycopg2 you don't need to do any string processing for arrays. This is considered as bad practice as it is error-prone and can - in the worst case - lead to opening up injection attacks! You should always use bound parameters. In the code below, I will create a new table with only one column with the type TEXT[] (as in your original question). Then I will add a new row, and update all of them. So you will see both an INSERT and UPDATE operation (although both are pretty much identical).

There is one Python gotcha though if you update with only one value: cur.execute expects the SQL statement as first argument and an iterable containing the parameters to be bound as second argument. The following will not work:

from psycopg2 import connect

conn = connect('dbname=exhuma')
cur = conn.cursor()
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values))
conn.commit()

The reason is that (new_values) is seen by python as new_values (the parens are dropped in this case, they are not seen as tuple). This will result in the error that you supply 3 values ('a', 'b' and 'c') as values to be bound, but there's only one placeholder (%s) in the query. Instead you must specify it as follows (notice the added comma at the end):

from psycopg2 import connect

conn = connect('dbname=exhuma')
cur = conn.cursor()
stmt = 'UPDATE foo SET example_value=%s'
new_values = ['a', 'b', 'c']
cur.execute(stmt, (new_values,))
conn.commit()

This will make Python see (new_values,) as a tuple (which is an iterable) with one element, which matches the query place-holders. For a more detailed explanation of the trailing comma, see the official docs on tuples.

Alternatively, you could also write [new_values] instead of (new_values,), but - in my opinion - (new_values,) is cleaner as tuples are immutable, whereas lists are mutable.

---

Here's the table with which I tested:

CREATE TABLE foo (
    values TEXT[]
);

And here's Python code both inserting and updating values:

from psycopg2 import connect


conn = connect('dbname=exhuma')
cur = conn.cursor()

cur.execute('INSERT INTO foo VALUES (%s)', (['a', 'b'], ))

print('>>> Before update')
cur.execute('SELECT * FROM foo')
for row in cur:
    print(type(row[0]), repr(row[0]))

print('>>> After update')

cur.execute('UPDATE foo SET example_values = %s',
            (['new', 'updated', 'values'],))

cur.execute('SELECT * FROM foo')
for row in cur:
    print(type(row[0]), repr(row[0]))

cur.close()
conn.commit()
conn.close()

On each execution, the code will insert a new row with the same array values, then execute an update without WHERE clause, so all values are updated. After a couple of executions, I this gives the following output:

>>> Before update
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['a', 'b']")
>>> After update
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")
(<type 'list'>, "['new', 'updated', 'values']")