Обсуждение: [psycopg] Parse record type into tuple

Hi Dmytro

I think your query is using row query syntax, the same as

    => select row(1,2);
      row
    -------
     (1,2)

in Postgres (which is not a common thing to want to do).
https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

If you want to select an array from Postgres, you need different syntax,
e.g.

    => select array[1,2];
     array
    -------
     {1,2}
    (1 row)


https://www.postgresql.org/docs/9.6/static/arrays.htmlttps://www.postgresql.org/docs/9.2/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

In psycopg2:

In [1]: import psycopg2
In [2]: conn = psycopg2.connect(dbname='template1', user='dbuser', password='xxx')
In [3]: c = conn.cursor()
In [5]: c.execute("select array[1,2] as ar")
In [6]: c.fetchone()
Out[6]: ([1, 2],)

In [7]: from psycopg2.extras import NamedTupleCursor
In [8]: c = conn.cursor(cursor_factory=NamedTupleCursor)
In [9]: c.execute("select array[1,2] as ar")
In [10]: c.fetchone()
Out[10]: Record(ar=[1, 2])


I put in the NamedTupleCursor example in there as I find it invaluable.

Kind regards
Rory



On 05/09/17, Dmytro Starosud (d.starosud@gmail.com) wrote:
> I do following interactions with DB:
>
> In [48]: conn = psycopg2.connect("dbname=... user=... password=...")
> In [49]: cur = conn.cursor()
> In [50]: cur.execute("select (1, 2)")
> In [51]: cur.fetchone()
> Out[51]: ('(1,2)',)
>
> Is it possible to get that tuple parsed into python tuple in the same way
> array works?
>
> In [55]: cur.execute("select array[1, 2]")
> In [56]: cur.fetchone()
> Out[56]: ([1, 2],)
>
> Looks like that can be done if I register composite type for that tuple.
> But I would like it to work with any tuple.
>
> Please assist.
>
> Thanks in advance!
> Dmytro

Hi Dmytro

I misread your question; sorry. You clearly know the difference betwreen
a row/record and array.

The only approach I can think of is

    => select * from (values (1,2)) x (a,b);
     a | b
    ---+---
     1 | 2

or this works suprisingly well

    => create type e_test as (x integer, y integer);
    CREATE TYPE

    => select (1, 2)::e_test;
      row
    -------
     (1,2)
    (1 row)

    => select to_json((1, 2)::e_test);
        to_json
    ---------------
     {"x":1,"y":2}
    (1 row)

In [54]: c.execute('select to_json((1, 2)::e_test);')
In [55]: r = c.fetchone()
In [56]: r
Out[56]: Record(to_json={u'y': 2, u'x': 1})


On 05/09/17, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> Hi Dmytro
>
> I think your query is using row query syntax, the same as
>
>     => select row(1,2);
>       row
>     -------
>      (1,2)
>
> in Postgres (which is not a common thing to want to do).
> https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
>
> If you want to select an array from Postgres, you need different syntax,
> e.g.
>
>     => select array[1,2];
>      array
>     -------
>      {1,2}
>     (1 row)
>
>
https://www.postgresql.org/docs/9.6/static/arrays.htmlttps://www.postgresql.org/docs/9.2/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
>
> In psycopg2:
>
> In [1]: import psycopg2
> In [2]: conn = psycopg2.connect(dbname='template1', user='dbuser', password='xxx')
> In [3]: c = conn.cursor()
> In [5]: c.execute("select array[1,2] as ar")
> In [6]: c.fetchone()
> Out[6]: ([1, 2],)
>
> In [7]: from psycopg2.extras import NamedTupleCursor
> In [8]: c = conn.cursor(cursor_factory=NamedTupleCursor)
> In [9]: c.execute("select array[1,2] as ar")
> In [10]: c.fetchone()
> Out[10]: Record(ar=[1, 2])
>
>
> I put in the NamedTupleCursor example in there as I find it invaluable.
>
> Kind regards
> Rory
>
>
>
> On 05/09/17, Dmytro Starosud (d.starosud@gmail.com) wrote:
> > I do following interactions with DB:
> >
> > In [48]: conn = psycopg2.connect("dbname=... user=... password=...")
> > In [49]: cur = conn.cursor()
> > In [50]: cur.execute("select (1, 2)")
> > In [51]: cur.fetchone()
> > Out[51]: ('(1,2)',)
> >
> > Is it possible to get that tuple parsed into python tuple in the same way
> > array works?
> >
> > In [55]: cur.execute("select array[1, 2]")
> > In [56]: cur.fetchone()
> > Out[56]: ([1, 2],)
> >
> > Looks like that can be done if I register composite type for that tuple.
> > But I would like it to work with any tuple.
> >
> > Please assist.
> >
> > Thanks in advance!
> > Dmytro
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg

Oh, I wrote this email yesterday and forgot to send it, sorry....

On Tue, Sep 5, 2017 at 4:40 PM, Dmytro Starosud <d.starosud@gmail.com> wrote:
> Hello guys
>
> I do following interactions with DB:
>
> In [48]: conn = psycopg2.connect("dbname=... user=... password=...")
> In [49]: cur = conn.cursor()
> In [50]: cur.execute("select (1, 2)")
> In [51]: cur.fetchone()
> Out[51]: ('(1,2)',)
>
> Is it possible to get that tuple parsed into python tuple in the same way
> array works?
>
> In [55]: cur.execute("select array[1, 2]")
> In [56]: cur.fetchone()
> Out[56]: ([1, 2],)
>
> Looks like that can be done if I register composite type for that tuple.
> But I would like it to work with any tuple.

No, the problem is that the server doesn't pass extra information of
the types in a ROW, e.g. it doesn't tell that the first type is a
number and the second is a date. With the array it returns this
information, saying e.g. that the result is an array of dates, so we
can split on the elements and parse the dates one by one. What
register_composite does is exactly that: it queries the server to
check what type is each field, stores that information, and then uses
it at parsing time to parse each element correctly.

You can definitely write a typecaster yourself and assign it to the
`record` type

    =# select pg_typeof((1,2));
    pg_typeof
    -----------
    record

    =# select pg_typeof((1,2))::int;
    pg_typeof
    -----------
         2249

you can check the docs about registering a new typecaster: you will
have a function receiving '(1,2)' and you can parse it any way you
want. What you have to solve is this ambiguity though:

    =# select (1,'2');
     row
    -------
    (1,2)

your program must be able to tell whether the second element is a
string or a number. The server won't tell it and psycopg will not
punt. You may have this knowledge in your program, so you can actually
do something this way.

-- Daniele