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