Return PostgreSQL UUID array as list with psycopg2

Question

I have an SQL statement which contains a subquery embedded in an ARRAY() like so:

SELECT foo, ARRAY(SELECT x from y) AS bar ...

The query works fine, however in the psycopg2 results cursor the array is returned as a string (as in "{1,2,3}"), not a list.

My question is, what would be the best way to convert strings like these into python lists?

Antonetteantoni

Answer 1

It works for me without the need for parsing:

import psycopg2

query = """
    select array(select * from (values (1), (2)) s);
"""

conn = psycopg2.connect('dbname=cpn user=cpn')
cursor = conn.cursor()
cursor.execute(query)
rs = cursor.fetchall()

for l in rs:
    print l[0]

cursor.close()
conn.close()

Result when executed:

$ python stackoverflow_select_array.py 
[1, 2]

Update

You need to register the uuid type:

import psycopg2, psycopg2.extras

query = """
    select array(
        select *
        from (values
            ('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'::uuid),
            ('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'::uuid)
        )s
    );
"""

psycopg2.extras.register_uuid()

conn = psycopg2.connect('dbname=cpn user=cpn')
cursor = conn.cursor()
cursor.execute(query)
rs = cursor.fetchall()

for l in rs:
    print l[0]

cursor.close()
conn.close()

Result:

$ python stackoverflow_select_array.py 
[UUID('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'), UUID('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')]

Ylem

Thanks very much for this, I went back and played with the code some more, and you're right it does work but only for Integer and String column types. The column I'm retrieving in the array is actually of type UUID, and that still comes back as a string. Sorry, I over simplified my code when I originally posted, I didn't realise the column type would affect this. – Antonetteantoni

Answer 2

If every result cursor ARRAY is of the format '{x,y,z}', then you can do this to strip the string of the braces and split it into a list by comma-delimiter:

>>> s = '{1,2,3}'
>>> s
'{1,2,3}'
>>> l = s.rstrip('}').lstrip('{').split(',')
>>> l
['1', '2', '3']
>>>
>>> s = '{1,2,3,a,b,c}'
>>> s
'{1,2,3,a,b,c}'
>>> l = s.rstrip('}').lstrip('{').split(',')
>>> l
['1', '2', '3', 'a', 'b', 'c']

Inning

Thanks for this, this does work. If I can't find out why ARRAY() isn't working for UUID fields (see below), I'll go for this approach. – Antonetteantoni

That's because UUID type was not supported in psycopg2 python. But now, in new version of psycopg2, we can register this type using psycopg2.extras.register_uuid(). Another way would be to cast the uuid array to a text array. See my answer. @BenScott – Banter

Answer 3

Another way of handling this is to explicitly tell postgres that you want text, then the default psycopg2 string parsing logic will kick in and you'll get a list:

db = psycopg2.connect('...')
curs = db.cursor()
curs.execute("""
    SELECT s.id, array_agg(s.kind::text)
        FROM (VALUES ('A', 'A0EEBC99-9C0B-AEF8-BB6D-6BB9BD380A11'::uuid),
                     ('A', 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'::uuid)) AS s (id, kind)
    GROUP BY s.id
""")
for row in curs:
    print "row: {}".format(row)

Results in:

row: (u'A', [u'a0eebc99-9c0b-aef8-bb6d-6bb9bd380a11', u'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'])

and the query

curs.execute("""
    SELECT array(
        SELECT s.kind::text
        FROM (VALUES ('A0EEBC99-9C0B-AEF8-BB6D-6BB9BD380A11'::uuid),
                     ('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'::uuid)) AS s (kind))
""")
for row in curs:
    print "row: {}".format(row)

results in:

row: ([u'a0eebc99-9c0b-aef8-bb6d-6bb9bd380a11', u'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'],)

The trick is specifically attaching the ::text to the fields that you care about.

Fatal

Answer 4

Solution 1

Convert the UUID array uuid[] to a text array text[]

select
    p.name,
    array(
        select _i.item_id
        from items _i
        where _i.owner_id = p.person_id
    )::text[] as item_ids
from persons p;

The from python code:

import psycopg2.extras
curs = conn.cursor(cursor_factory=extras.DictCursor)  # To get rows in dictionary
curs.execute(my_query)
rows = curs.fetchall()
print(dict(row[0]))

Output:

{
    "name": "Alex",
    "item_ids": [
        "db6c19a2-7627-4dff-a963-b90b6217cb11",
        "db6c19a2-7627-4dff-a963-b90b6217cb11"
    ]
}

Solution 2

Register the UUID type so that the PostgreSQL uuid can be converted to python uuid.UUID (see python UUID docs) type.

import psycopg2.extras
psycopg2.extras.register_uuid()

After this, you can use the query without needing to convert to text array using ::text[].

select
    p.name,
    array(
        select _i.item_id
        from items _i
        where _i.owner_id = p.person_id
    ) as item_ids
from persons p;

The output in DictRow will be like:

{
    "name": "Alex",
    "item_ids": [
        UUID("db6c19a2-7627-4dff-a963-b90b6217cb11"),
        UUID("db6c19a2-7627-4dff-a963-b90b6217cb11")  # uuid.UUID data type
    ]
}

Banter