Thread: psycopg3 and cur.description behavior
Hello psycopg users This is my first post on this mailing list I'm an hobbistic user of pythom/postgresql/psycopg. I'm trying to port my application from psycopg2 to psycopg3 beta and i have some problems.. In my application a call a postgresql function that returns some data. The relevant part of the function is this: CREATE OR REPLACE FUNCTION system.pa_connect( pg_version numeric, app_name text, app_version text, app_user_name text, app_user_pwd text, app_client_name text) RETURNS TABLE(session_id integer, app_user character varying, user_description text, is_admin boolean, can_edit_views boolean, can_edit_sortfilters boolean, can_edit_reports boolean, l10n character, tool_button_style character, tab_position character, font_family character varying, font_size integer, icon_theme character varying, style_theme character varying, use_dark_palette boolean, auto_hide_dock boolean, company integer, change_password_required boolean) LANGUAGE 'plpgsql' (...) RETURN QUERY SELECT pg_backend_pid(), c.app_user, u.description, u.is_admin, u.can_edit_views, u.can_edit_sortfilters, u.can_edit_reports, u.l10n, u.tool_button_style, u.tab_position, u.font_family, u.font_size, u.icon_theme, u.style_theme, u.use_dark_palette, u.auto_hide_dock, u.last_company, --system.pa_setting('model_select_limit')::int, change_password_required FROM system.connection c JOIN system.app_user u ON c.app_user = u.code LEFT JOIN system.app_user_company uc ON c.app_user = uc.app_user AND c.company = uc.company WHERE c.session_id = pg_backend_pid(); (...) 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. So i tryed to execute this code: cur.execute('SELECT * FROM system.app_user;') print(cur.fetchall()) print(cur.description) And this is the result: [('system', 'pyCOGE system administrator', None, '$2a$06$oFbElI3aMWY7FCY9BiKuf.nJdO.ioGR/oL.MyuNHutKxzv.ib5fgS', datetime.datetime(2021, 9, 21, 19, 9, 40, 352000, tzinfo=datetime.timezone.utc), False, True, True, True, True, True, 'en_US', 10, '10 Prima azienda dimostrativa', datetime.datetime(2021, 10, 1, 8, 2, 41, 708000, tzinfo=datetime.timezone.utc), 'Verdana', 11, 'I', 'N', None, 'oxygen', 'windowsvista', False, True, 'system', datetime.datetime(2021, 9, 21, 19, 9, 40, 320000, tzinfo=datetime.timezone.utc), 'system', datetime.datetime(2021, 10, 1, 8, 2, 41, 708000, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 10, 1, 8, 2, 41, 737965)), ('utente', 'Utente applicativo di pyCOGE', None, '$2a$06$dpNyFb7aiW3xLBkOg8kqX.Vzz0mKuNG72ZpQ5FGLkQZ6iO1K2giHa', datetime.datetime(2021, 9, 21, 19, 9, 40, 361000, tzinfo=datetime.timezone.utc), False, False, False, False, False, False, 'it_IT', 10, '10 Prima azienda dimostrativa', datetime.datetime(2021, 9, 30, 20, 12, 41, 628000, tzinfo=datetime.timezone.utc), 'Arial', 10, 'I', 'N', None, 'oxygen', 'windowsvista', False, False, 'system', datetime.datetime(2021, 9, 21, 19, 9, 40, 320000, tzinfo=datetime.timezone.utc), 'utente', datetime.datetime(2021, 9, 30, 20, 13, 18, 753000, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 9, 30, 20, 13, 18, 753466))] [<Column 'code', type: varchar(48) (oid: 1043)>, <Column 'description', type: text (oid: 25)>, <Column 'image', type: bytea (oid: 17)>, <Column 'user_password', type: varchar(256) (oid: 1043)>, <Column 'password_date', type: timestamptz(3) (oid: 1184)>, <Column 'is_change_password_required', type: bool (oid: 16)>, <Column 'is_admin', type: bool (oid: 16)>, <Column 'system', type: bool (oid: 16)>, <Column 'can_edit_views', type: bool (oid: 16)>, <Column 'can_edit_sortfilters', type: bool (oid: 16)>, <Column 'can_edit_reports', type: bool (oid: 16)>, <Column 'l10n', type: bpchar (oid: 1042)>, <Column 'last_company', type: int4 (oid: 23)>, <Column 'last_company_desc', type: text (oid: 25)>, <Column 'last_login', type: timestamptz(3) (oid: 1184)>, <Column 'font_family', type: varchar(60) (oid: 1043)>, <Column 'font_size', type: int4 (oid: 23)>, <Column 'tool_button_style', type: bpchar (oid: 1042)>, <Column 'tab_position', type: bpchar (oid: 1042)>, <Column 'keyboard_shortcut', type: varchar(48) (oid: 1043)>, <Column 'icon_theme', type: varchar(48) (oid: 1043)>, <Column 'style_theme', type: varchar(48) (oid: 1043)>, <Column 'use_dark_palette', type: bool (oid: 16)>, <Column 'auto_hide_dock', type: bool (oid: 16)>, <Column 'user_ins', type: text (oid: 25)>, <Column 'date_ins', type: timestamptz(3) (oid: 1184)>, <Column 'user_upd', type: text (oid: 25)>, <Column 'date_upd', type: timestamptz(3) (oid: 1184)>, <Column 'row_timestamp', type: timestamp (oid: 1114)>] BUT if i execute (call) the already mentioned postgresql function: cur.execute('SELECT system.pa_connect(%s, %s, %s, %s, %s, %s);', (MRV_PGSQL, APPNAME, APPVERSION, par['user'], par['password'], par['hostname'])) print(cur.fetchall()) print(cur.description) the result is: [(('14120', 'system', 'pyCOGE system administrator', 't', 't', 't', 't', 'en_US', 'I', 'N', 'Verdana', '11', 'oxygen', 'windowsvista', 'f', 't', '10', 'f'),)] [<Column 'pa_connect', type: record (oid: 2249)>] i don't see the description of each field. what am I doing wrong? How can i get the psycopg2 behavior? Python 3.8.9 on windows 10, postgresql 13, psycopg 3.0 beta1 and sorry for my poor english... -- Paolo De Stefani
On Sun, 3 Oct 2021 at 15:33, Paolo De Stefani <paolo@paolodestefani.it> wrote:
>
> Hello psycopg users
> 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
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:
>
> 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=# 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)
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)
┌─────────────────────────────────────────┐
│ 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 [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")',)
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'),)
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'}
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);',
> ...
>
> 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 = cnn.cursor(row_factory=dict_row)
In [13]: cur.execute("select * from testfunc()").fetchone()
Out[13]: {'pid': 625446, 'type': 'autovacuum launcher'}
In [12]: cur = cnn.cursor(row_factory=dict_row)
In [13]: cur.execute("select * from testfunc()").fetchone()
Out[13]: {'pid': 625446, 'type': 'autovacuum launcher'}
Cheers
-- Daniele
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
On Sun, 3 Oct 2021 at 18:35, Paolo De Stefani <paolo@paolodestefani.it> wrote: > 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 ? There is no mogrify, no, because queries are not composed by the client anymore, but they are sent to the server separately from the arguments. Mogrify would be misleading because the same query might behave differently if parameters are merged by the server or by the client. The logging cursor, I don't think should exist as an option: the logging system itself has the option of being enabled or disabled. So, if any, I think psycopg should log every query, let's say at info level, which would normally be discarded, unless the user enables it with a getLogger("psycopg").setLevel(logging.INFO). However that's not in, because I have a fear (but not a measure) that it would add some overhead. Which is totally premature optimisation, I am aware. Maybe someone would like to help and get some measurement of how much overhead does logging add? Comparing no logging/disabled logging/enabled logging? Cheers -- Daniele