MIME-Version: 1.0
Date: Sun, 03 Oct 2021 15:33:27 +0200
From: Paolo De Stefani <paolo@paolodestefani.it>
To: psycopg@postgresql.org
Subject: psycopg3 and cur.description behavior
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <ce48207bd5d37421cf322e70dff3febc@paolodestefani.it>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ce48207bd5d37421cf322e70dff3febc%40paolodestefani.it>
Precedence: bulk

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