使用psycopg2返回PostgreSQL UUID数组作为列表

Question

我有一个SQL语句，其中包含嵌入在ARRAY()中的子查询，如下所示:

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

查询工作得很好，但是在psycopg2结果游标中，数组作为字符串(与"{1,2,3}"一样)返回，而不是列表。

我的问题是，把这样的字符串转换成python列表的最好方法是什么?

Answer 1

它不需要解析就可以为我工作:

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

执行结果:

$ python stackoverflow_select_array.py 
[1, 2]

更新

您需要注册uid类型:

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

结果:

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

Answer 2

如果每个结果游标ARRAY的格式都是'{x,y,z}'，那么您可以这样做来剥离花括号字符串并通过逗号分隔符将其拆分为列表:

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

Answer 3

另一种处理方法是显式地告诉postgres您需要文本，然后默认的psycopg2字符串解析逻辑将启动，您将得到一个列表:

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)

结果:

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

和查询

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)

结果:

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

技巧是特别将::text附加到您关心的字段。

Answer 4

解决方案1

将UUID数组uuid[]转换为文本数组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;

from python代码:

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

输出:

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

<标题>解决方案2 h1> 册UUID类型，以便PostgreSQL uuid可以转换为python uuid.UUID(参见python UUID文档)类型。

import psycopg2.extras
psycopg2.extras.register_uuid()

在此之后，您可以使用查询而无需使用::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;

DictRow中的输出如下:

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