使用psycopg2将PostgreSQL UUID数组返回为列表 [英] Return PostgreSQL UUID array as list with psycopg2

问题描述

我有一条SQL语句，其中包含一个嵌入在 ARRAY（）中的子查询，如下所示：

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

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

查询工作正常，但是在psycopg2结果游标中，数组以字符串形式返回（如 {1,2,3} ），而不是列表

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.

我的问题是，将诸如此类的字符串转换为python列表的最佳方法是什么？

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

推荐答案

它对我有用，无需解析：

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

执行时的结果：

$ python stackoverflow_select_array.py 
[1, 2]

更新

您需要注册uuid类型：

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

结果：

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

这篇关于使用psycopg2将PostgreSQL UUID数组返回为列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！