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?

Clodoaldo Neto · Accepted Answer · 2013-05-28T20:37:24.603

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')]

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. — Ben Scott, May 28 '13 at 18:49

score 0 · Answer 2 · answered May 28 '13 at 17:20

0

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']

answered May 28 '13 at 17:20

Gurkaran Singh

21
3

1

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. – Ben Scott May 28 '13 at 18:51
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 – Ali Sajjad Feb 14 '22 at 07:49

score 0 · Answer 3 · answered Sep 21 '16 at 20:23

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.

score 0 · Answer 4 · answered Oct 30 '21 at 09:09

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
    ]
}

Return PostgreSQL UUID array as list with psycopg2

4 Answers4

Update

Solution 1

Output:

Solution 2

Linked