> psycopg2 returns the 'result' as a basic string, while > in asyncpg and py-postgresql I have structured data > (roughly 'List[Tuple[int, List[str]]]'). > > I tried the same in psycopg3 and it is little bit better, but > not entirely: it shows the outer list, the tuples inside it, > but the innermost list is still represented as a basic string: > '{one,"one more"}'. > > Is it something you are still working on? Any workarounds? Yes: by obtaining data from the db in binary mode you can get information about deeply nested objects. psycopg2 works only in text mode, psycopg3 in both.
In [1]: query = """ ...: with test as ( ...: select 1 as id, 'one' val ...: union all ...: select 1, 'one more' ...: union all ...: select 2, 'two' ...: ) ...: select array( ...: select (id, array_agg(val)) ...: from test ...: group by id ...: )"""
In [2]: import psycopg3
In [3]: from psycopg3.pq import Format
In [4]: cnn = psycopg3.connect("")
In [5]: cnn.cursor().execute(query).fetchone()[0] Out[5]: [('1', '{one,"one more"}'), ('2', '{two}')]
In [6]: cnn.cursor(format=Format.BINARY).execute(query).fetchone()[0] Out[6]: [(1, ['one', 'one more']), (2, ['two'])]
Binary loading/dumping is not supported yet for all the data types, but the plan is to cover all the builtins. Still not sure about the interface to request text/binary results, or whether binary shouldn't be the default as opposed to text. There is still ground to cover, but we are getting there.