MIME-Version: 1.0
References: 
 <CAMqTPqm7H4yVk0qGA8=TyTtVsNX88bo8x1BJkHc6vrOVYyW=RQ@mail.gmail.com>
 <CA+mi_8YNpi7a-OcWr3eWM4kRcCNPeapraSOif3yYCdTnxuuZwA@mail.gmail.com>
In-Reply-To: 
 <CA+mi_8YNpi7a-OcWr3eWM4kRcCNPeapraSOif3yYCdTnxuuZwA@mail.gmail.com>
From: Vladimir Ryabtsev <greatvovan@gmail.com>
Date: Tue, 10 Nov 2020 16:35:57 -0800
Message-ID: 
 <CAMqTPqmo=_Ev5R=0B93vgvxMf_RjiPkvJ_WqMEFbyK+Rx0MAsg@mail.gmail.com>
Subject: Re: Using composite types in psycopg3
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000038481905b3c9fba5"
Precedence: bulk

--00000000000038481905b3c9fba5
Content-Type: text/plain; charset="UTF-8"

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.varrazzo@gmail.com>
wrote:

> On Tue, 10 Nov 2020 at 21:59, Vladimir Ryabtsev <greatvovan@gmail.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
>

--00000000000038481905b3c9fba5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Awesome, thanks.<br><div><br></div><div>It would be great =
if it worked out of the box, as in other drivers.</div><div>Appreciate your=
 efforts.</div><div><br></div><div>Vladimir</div></div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 10 Nov 2020 at 16:=
24, Daniele Varrazzo &lt;<a href=3D"mailto:daniele.varrazzo@gmail.com">dani=
ele.varrazzo@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex">On Tue, 10 Nov 2020 at 21:59, Vladimir Ryabtsev &lt;<=
a href=3D"mailto:greatvovan@gmail.com" target=3D"_blank">greatvovan@gmail.c=
om</a>&gt; wrote:<br>
<br>
&gt; psycopg2 returns the &#39;result&#39; as a basic string, while<br>
&gt; in asyncpg and py-postgresql I have structured data<br>
&gt; (roughly &#39;List[Tuple[int, List[str]]]&#39;).<br>
&gt;<br>
&gt; I tried the same in psycopg3 and it is little bit better, but<br>
&gt; not entirely: it shows the outer list, the tuples inside it,<br>
&gt; but the innermost list is still represented as a basic string:<br>
&gt; &#39;{one,&quot;one more&quot;}&#39;.<br>
&gt;<br>
&gt; Is it something you are still working on? Any workarounds?<br>
Yes: by obtaining data from the db in binary mode you can get<br>
information about deeply nested objects. psycopg2 works only in text<br>
mode, psycopg3 in both.<br>
<br>
=C2=A0 =C2=A0 In [1]: query =3D &quot;&quot;&quot;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...: with test as (<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0select 1 as id, &#39;one=
&#39; val<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0union all<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0select 1, &#39;one more&=
#39;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0union all<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0select 2, &#39;two&#39;<=
br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...: )<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...: select array(<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0select (id, array_agg(va=
l))<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0from test<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...:=C2=A0 =C2=A0 =C2=A0group by id<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0...: )&quot;&quot;&quot;<br>
<br>
=C2=A0 =C2=A0 In [2]: import psycopg3<br>
<br>
=C2=A0 =C2=A0 In [3]: from psycopg3.pq import Format<br>
<br>
=C2=A0 =C2=A0 In [4]: cnn =3D psycopg3.connect(&quot;&quot;)<br>
<br>
=C2=A0 =C2=A0 In [5]: cnn.cursor().execute(query).fetchone()[0]<br>
=C2=A0 =C2=A0 Out[5]: [(&#39;1&#39;, &#39;{one,&quot;one more&quot;}&#39;),=
 (&#39;2&#39;, &#39;{two}&#39;)]<br>
<br>
=C2=A0 =C2=A0 In [6]: cnn.cursor(format=3DFormat.BINARY).execute(query).fet=
chone()[0]<br>
=C2=A0 =C2=A0 Out[6]: [(1, [&#39;one&#39;, &#39;one more&#39;]), (2, [&#39;=
two&#39;])]<br>
<br>
Binary loading/dumping is not supported yet for all the data types,<br>
but the plan is to cover all the builtins. Still not sure about the<br>
interface to request text/binary results, or whether binary shouldn&#39;t<b=
r>
be the default as opposed to text. There is still ground to cover, but<br>
we are getting there.<br>
<br>
-- Daniele<br>
</blockquote></div>

--00000000000038481905b3c9fba5--