Re: Using composite types in psycopg3

Awesome, thanks.

It would be great if it worked out of the box, as in other drivers.
Appreciate your efforts.

Vladimir

On Tue, 10 Nov 2020 at 16:24, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
wrote:

> On Tue, 10 Nov 2020 at 21:59, Vladimir Ryabtsev <greatvovan(at)gmail(dot)com>
> wrote:
>
> > 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.
>
> -- Daniele
>