Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mX3V6-0002al-La for psycopg@arkaria.postgresql.org; Sun, 03 Oct 2021 15:34:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mX3V3-0000OH-0r for psycopg@arkaria.postgresql.org; Sun, 03 Oct 2021 15:34:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mX3V2-0000O8-ME for psycopg@lists.postgresql.org; Sun, 03 Oct 2021 15:34:00 +0000 Received: from mail-vs1-xe2e.google.com ([2607:f8b0:4864:20::e2e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mX3Uz-0006dG-NC for psycopg@postgresql.org; Sun, 03 Oct 2021 15:34:00 +0000 Received: by mail-vs1-xe2e.google.com with SMTP id o124so16795433vsc.6 for ; Sun, 03 Oct 2021 08:33:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=oa/aT4IJguS79Ob6mekzin1alBSD0sWA6Y8MSZtaK/U=; b=puR+1uRRwSsx9HySZ95v2jK+BNV7kl4p2HTU5Wpuj5wrsnYa9tdlyfxUD33jcEOMAu 5MhMM5vMYALRKw+iuO99Hfcq4wXr8sL9PnAkyGUljJCLjKoZXSAYUvrTlrO++h8X58jR o7QFMv6bK4caj5TmDbGAcvT1IgeBSxpMqzAhGZNYZWYiZiKw53p89VJwMeZPdcsncTX8 rvDkdEqBVTNGJ+lRj2sCIG7imtw8GYrHKazEkHkTQ9kgs0jX2yWJE1gmZIK6LiTyvpW3 Udu9tJCBxO/AVArJz3jzUus8UeGq8CdMAC6xkSLRDiR4gVhAmyvEfbtXa5HwLnZ9XlGO TARQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=oa/aT4IJguS79Ob6mekzin1alBSD0sWA6Y8MSZtaK/U=; b=nBvOllJUddeqesWtuOJfk6EjlkD+J/AnYHJSGOSyZ9xnLAFiEMqrfOe4inl0lEwZ31 7oD5uxBqoFwUpqg4bnOVtRE34su+li2waG920xuZKDtHeeAS59wufohRPI5DBA3E3bMC xUpx0l7H/g60coIu2gCGWVaYS+8WzKpDg8xKmiGURIE5dXAz10eoBJI9YOPIvzqSUy3R OLiFdc/cNwznghSlsf+wFyniqyZNMqFmNy6g3myXOFCBtGlLpFAIChVGNlb7fFrjrBF1 gow3iWqAHP6DzPqzIHpbrirb+tcc9vTFnLDDW+gsFx1EsWPPMntCjsQfjI72b9PtQ5/w POJA== X-Gm-Message-State: AOAM533fzceyib3CZXZHzJ6cS5oIQDCIDO3Go/qr07LPsbkTk462iHEA 8Vpao8DzkCMQ6oBqlNP7ZpwqfSe62kEaoMAfPqtTV5sNoUVT9g== X-Google-Smtp-Source: ABdhPJyFk29NkraKasO2U5QGocT+c75QCBOwEP/oKNz0ZkkwHvZ4amKi208ldx4Ira5pBfL+SQDCfNE0vjgd48VXvOo= X-Received: by 2002:a05:6102:22eb:: with SMTP id b11mr10458861vsh.36.1633275235230; Sun, 03 Oct 2021 08:33:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Daniele Varrazzo Date: Sun, 3 Oct 2021 17:33:43 +0200 Message-ID: Subject: Re: psycopg3 and cur.description behavior To: Paolo De Stefani Cc: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="00000000000037685e05cd7486e6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000037685e05cd7486e6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, 3 Oct 2021 at 15:33, Paolo De Stefani wrote: > > Hello psycopg users > This is my first post on this mailing list Hello Paolo, welcome here. > In my python code i use the return query to create a dictionary in this > way: > > session.update(dict(zip([i[0] for i in cur.description], > cur.fetchall()[0]))) > > This part no longer works in psycopg 3. Looks like cur.description in > psycopg 3 is different if i execute a query or call a function. Are you sure you are using the same statements in psycopg 2 and 3? If you call `select pa_connect` or `select * from pa_connect` you get different results: a table with a single column of records in the first case, expanded records in the second. You can verify that in psql too. Using a simplified set returning function: piro=3D# create or replace function testfunc() returns table(pid int, type text) language plpgsql as $$ begin return query select a.pid, a.backend_type from pg_stat_activity a; end$$; piro=3D# select * from testfunc() limit 3; =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90 =E2=94=82 pid =E2=94=82 type =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4 =E2=94=82 625446 =E2=94=82 autovacuum launcher =E2=94=82 =E2=94=82 625448 =E2=94=82 logical replication launcher =E2=94=82 =E2=94=82 806502 =E2=94=82 client backend =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98 (3 rows) piro=3D# select testfunc() limit 3; =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=90 =E2=94=82 testfunc =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=A4 =E2=94=82 (625446,"autovacuum launcher") =E2=94=82 =E2=94=82 (625448,"logical replication launcher") =E2=94=82 =E2=94=82 (806502,"client backend") =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=98 (3 rows) Psycopg would see pretty much the same: in psycopg2 you obtain two columns if you use "select * from", only one "record" column if you don't: In [1]: import psycopg2 In [2]: cnn =3D psycopg2.connect("") In [3]: cur =3D cnn.cursor() In [4]: cur.execute("select * from testfunc()") In [5]: cur.description Out[5]: (Column(name=3D'pid', type_code=3D23), Column(name=3D'type', type_code=3D25)) In [6]: cur.fetchone() Out[6]: (625446, 'autovacuum launcher') In [7]: cur.execute("select testfunc()") In [8]: cur.description Out[8]: (Column(name=3D'testfunc', type_code=3D2249),) In [9]: cur.fetchone() Out[9]: ('(625446,"autovacuum launcher")',) Psycopg 3 returns something similar: In [1]: import psycopg In [2]: cnn =3D psycopg.connect("") In [3]: cur =3D cnn.execute("select * from testfunc()") In [4]: cur.description Out[4]: [, ] In [5]: cur.fetchone() Out[5]: (625446, 'autovacuum launcher') In [6]: cur =3D cnn.execute("select testfunc()") In [7]: cur.description Out[7]: [] In [8]: cur.fetchone() Out[8]: (('625446', 'autovacuum launcher'),) There is a difference in how the record is handled: psycopg 2 doesn't parse it, psycopg 3 unpacks it in a tuple (although it doesn't have enough info to understand the types contained in the record, so they are left as strings). However the number and oids of the columns in the result is the same. The pattern you use to convert the record into a dict should work the same way in psycopg 3 too: In [9]: cur =3D cnn.execute("select * from testfunc()") In [10]: dict(zip([i[0] for i in cur.description],cur.fetchall()[0])) Out[10]: {'pid': 625446, 'type': 'autovacuum launcher'} > BUT if i execute (call) the already mentioned postgresql function: > > cur.execute('SELECT system.pa_connect(%s, %s, %s, %s, %s, %s);', > ... I think you want to use `SELECT * FROM system.pa_connect(...)` here, and I think it is what you were using before. Does it make sense? Once you are comfortable with how the types of query work, you might want to take a look at 'dict_row()' ( https://www.psycopg.org/psycopg3/docs/advanced/rows.html) to convert records to dicts in a more succinct way: In [11]: from psycopg.rows import dict_row In [12]: cur =3D cnn.cursor(row_factory=3Ddict_row) In [13]: cur.execute("select * from testfunc()").fetchone() Out[13]: {'pid': 625446, 'type': 'autovacuum launcher'} Cheers -- Daniele --00000000000037685e05cd7486e6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, 3 Oct 2021 at 15:33, Paolo De Stefani <paolo@paolodestefani.it> wrote:<= br>>
> Hello psycopg users
> This is my first post on t= his mailing list

Hello Paolo, welcome here.


> In my python code i use the return query to= create a dictionary in this
> way:
>
> session.update(di= ct(zip([i[0] for i in cur.description],
> cur.fetchall()[0])))
>= ;
> This part no longer works in psycopg 3. Looks like cur.descriptio= n in
> psycopg 3 is different if i execute a query or call a fun= ction.

Are you sure you are using the same statements in psycopg 2= and 3? If you call `select pa_connect` or `select * from pa_connect` you g= et different results: a table with a single column of records in the first = case, expanded records in the second. You can verify that in psql too. Usin= g a simplified set returning function:

piro=3D# create or replace functio= n testfunc() returns table(pid int, type text) language plpgsql as $$
begin
return query select a.pid, a.backend_type from pg_sta= t_activity a;
end$$;=

piro=3D# select * from testfunc() limit 3;
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=90
=E2=94=82 =C2=A0pid =C2=A0 =E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=94=82
<= span style=3D"font-family:monospace">=E2=94=9C=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=A4
=E2=94=82 625446 =E2=94=82 autovacuum launcher =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0=E2=94=82
=E2= =94=82 625448 =E2=94=82 logical replication launcher =E2=94=82
=E2=94=82 806502 =E2=94=82 client backe= nd =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=98
(3 rows)

piro=3D# select testfunc() limit 3;<= br>=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=90
=E2=94=82 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0testfunc =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =E2=94=82
=E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=A4
=E2=94=82 (625446,"autovacuum launcher&= quot;) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=94=82
=E2=94=82 (625448,&qu= ot;logical replication launcher") =E2=94=82
=E2=94=82 (806502,"= ;client backend") =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2= =94=82
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=98
(3 rows)


Psycopg would see pretty much the same: in psycopg2 you obtain two= columns if you use "select * from", only one "record" = column if you don't:

In [1]: import psycopg2
In [2]: = cnn =3D psycopg2.connect("")
In [3]: cur =3D cnn.cursor()

In [4]: cur.execute("select * from testfunc()")In [5]: cur.description
Out[5]: (Column(name=3D'pid', type_code= =3D23), Column(name=3D'type', type_code=3D25))
In [6]: cur.fetch= one()
Out[6]: (625446, 'autovacuum launcher')

In [7]: cur= .execute("select testfunc()")
In [8]: cur.description
Out[8= ]: (Column(name=3D'testfunc', type_code=3D2249),)
In [9]: cur.fe= tchone()
Out[9]: ('(625446,"autovacuum launcher")',)

Psycopg 3 returns something similar:
=

In [1]: import psycopg
In [2]: cnn =3D psycopg.connect(&= quot;")

In [3]: cur =3D cnn.execute("select * from testfun= c()")
In [4]: cur.description
Out[4]: [<Column 'pid',= type: int4 (oid: 23)>, <Column 'type', type: text (oid: 25)&= gt;]
In [5]: cur.fetchone()
Out[5]: (625446, 'autovacuum launcher= ')

In [6]: cur =3D cnn.execute("select testfunc()")In [7]: cur.description
Out[7]: [<Column 'testfunc', type: r= ecord (oid: 2249)>]
In [8]: cur.fetchone()
Out[8]: (('625446&#= 39;, 'autovacuum launcher'),)

There= is a difference in how the record is handled: psycopg 2 doesn't parse = it, psycopg 3 unpacks it in a tuple (although it doesn't have enough in= fo to understand the types contained in the record, so they are left as str= ings). However the number and oids of the columns in the result is the same= . The pattern you use to convert the record into a dict should work the sam= e way in psycopg 3 too:

In [9]: cur =3D cnn.execute("se= lect * from testfunc()")

In [10]: dict(zip([i[0] for i in cur.d= escription],cur.fetchall()[0]))
Out[10]: {'pid': 625446, 'ty= pe': 'autovacuum launcher'}

> BUT i= f i execute (call) the already mentioned postgresql function:
>
&g= t; cur.execute('SELECT system.pa_connect(%s, %s, %s, %s, %s, %s);',=
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 ...

I think you want to use `SELECT * FROM system.pa_connect(...)` here, = and I think it is what you were using before.

= Does it make sense?

Once you are comfortable with = how the types of query work, you might want to take a look at 'dict_row= ()' (https://www.psycopg.org/psycopg3/docs/advanced/rows.html) to convert= records to dicts in a more succinct way:

In [11]: from psyc= opg.rows import dict_row

In [12]: cur =3D cnn.cursor(row_factory=3Dd= ict_row)

In [13]: cur.execute("select * from testfunc()").= fetchone()
Out[13]: {'pid': 625446, 'type': 'autovac= uum launcher'}

Cheers
-- Daniele
--00000000000037685e05cd7486e6--