我有一条SQL语句,其中包含一个嵌入在ARRAY()中的子查询,如下所示:
SELECT foo, ARRAY(SELECT x from y) AS bar ...查询运行良好,但是在psycopg2结果游标中,数组以字符串(如"{1,2,3}")的形式返回,而不是列表。
我的问题是,将这样的字符串转换为python列表的最佳方法是什么?
发布于 2013-05-29 01:42:14
它不需要解析就能为我工作:
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类型:
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')]发布于 2013-05-29 01:20:46
如果每个结果游标数组的格式都是'{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']发布于 2016-09-22 04:23:32
另一种处理方法是显式地告诉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附加到您关心的字段。
https://stackoverflow.com/questions/16797767
复制相似问题