MIME-Version: 1.0
Date: Sun, 03 Oct 2021 18:35:41 +0200
From: Paolo De Stefani <paolo@paolodestefani.it>
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: psycopg@postgresql.org
Subject: Re: psycopg3 and cur.description behavior
In-Reply-To: 
 <CA+mi_8YCUBfOD9vx1kx4PBt9Lc2PD8DQzT4mPqjx8nhMNWufXQ@mail.gmail.com>
References: <ce48207bd5d37421cf322e70dff3febc@paolodestefani.it>
 <CA+mi_8YCUBfOD9vx1kx4PBt9Lc2PD8DQzT4mPqjx8nhMNWufXQ@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <b5014c6ae5f0ef8355e2e6a7179dc827@paolodestefani.it>
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b5014c6ae5f0ef8355e2e6a7179dc827%40paolodestefani.it>
Precedence: bulk

Il 03/10/2021 17:33 Daniele Varrazzo ha scritto:
> On Sun, 3 Oct 2021 at 15:33, Paolo De Stefani
> <paolo@paolodestefani.it> 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?

Of course in psycopg2 i use:

cur.callproc('system.pa_connect', (MRV_PGSQL,
                                    APPNAME,
                                    APPVERSION,
                                    par['user'],
                                    par['password'],
                                    par['hostname']))


> 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=# 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=# select * from testfunc() limit 3;
> ┌────────┬──────────────────────────────┐
> │  pid   │             type             │
> ├────────┼──────────────────────────────┤
> │ 625446 │ autovacuum launcher          │
> │ 625448 │ logical replication launcher │
> │ 806502 │ client backend               │
> └────────┴──────────────────────────────┘
> (3 rows)
> 
> piro=# select testfunc() limit 3;
> ┌─────────────────────────────────────────┐
> │                testfunc                 │
> ├─────────────────────────────────────────┤
> │ (625446,"autovacuum launcher")          │
> │ (625448,"logical replication launcher") │
> │ (806502,"client backend")               │
> └─────────────────────────────────────────┘
> (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 = psycopg2.connect("")
> In [3]: cur = cnn.cursor()
> 
> In [4]: cur.execute("select * from testfunc()")
> In [5]: cur.description
> Out[5]: (Column(name='pid', type_code=23), Column(name='type',
> type_code=25))
> In [6]: cur.fetchone()
> Out[6]: (625446, 'autovacuum launcher')
> 
> In [7]: cur.execute("select testfunc()")
> In [8]: cur.description
> Out[8]: (Column(name='testfunc', type_code=2249),)
> In [9]: cur.fetchone()
> Out[9]: ('(625446,"autovacuum launcher")',)
> 
> Psycopg 3 returns something similar:
> 
> In [1]: import psycopg
> In [2]: cnn = psycopg.connect("")
> 
> In [3]: cur = cnn.execute("select * from testfunc()")
> In [4]: cur.description
> Out[4]: [<Column 'pid', type: int4 (oid: 23)>, <Column 'type', type:
> text (oid: 25)>]
> In [5]: cur.fetchone()
> Out[5]: (625446, 'autovacuum launcher')
> 
> In [6]: cur = cnn.execute("select testfunc()")
> In [7]: cur.description
> Out[7]: [<Column 'testfunc', type: record (oid: 2249)>]
> 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 = 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?


I see thanks for the clear explanation

> 
> 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 = cnn.cursor(row_factory=dict_row)
> 
> In [13]: cur.execute("select * from testfunc()").fetchone()
> Out[13]: {'pid': 625446, 'type': 'autovacuum launcher'}

Yes, that's what i will use, thanks

By the way i didn't see any cur.mogrify() in psycopg 3 and no logging 
cursor as well something i used frequently
Are they no more available ? Any plan to include them in next versions ?

> 
> Cheers
> 
> -- Daniele

-- 
Paolo De Stefani