Subject: Re: iterating over DictRow
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>, psycopg@lists.postgresql.org
References: <20200923215444.GA25406@hermes.hilbert.loc>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <27c3e20a-3ff8-28a7-b16b-4d052628f02d@aklaver.com>
Date: Wed, 23 Sep 2020 16:54:44 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.12.0
MIME-Version: 1.0
In-Reply-To: <20200923215444.GA25406@hermes.hilbert.loc>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 9/23/20 2:54 PM, Karsten Hilbert wrote:
> Dear all,
> 
> I cannot currently wrap my head around why I am seeing this:
> 
> 	2020-09-23 23:30:23 gmConnectionPool.py::<module>() #87): psycopg2 module version: 2.8.5 (dt dec pq3 ext)
> 	2020-09-23 23:30:23 gmConnectionPool.py::<module>() #88): PostgreSQL via DB-API module "<module 'psycopg2' from '/usr/lib/python3/dist-packages/psycopg2/__init__.py'>": API level 2.0, thread safety 2, parameter style "pyformat"
> 	2020-09-23 23:30:23 gmConnectionPool.py::<module>() #89): libpq version (compiled in): 120002
> 	2020-09-23 23:30:23 gmConnectionPool.py::<module>() #90): libpq version (loaded now) : 120004
> 	...
> 	2020-09-23 23:30:28 gmConnectionPool.py::__log_on_first_contact() #445): heed Prime Directive
> 	2020-09-23 23:30:28 gmConnectionPool.py::__log_on_first_contact() #457): PostgreSQL version (numeric): 11.7
> 	...
> 	2020-09-23 23:31:02 gmMacro.py::_get_variant_diagnoses() #2442): [[260, 'L: Bewegungsapparat', False, 'B', 'clin.health_issue'], [260, 'K: aHT/HI', False, 'B', 'clin.health_issue'], [260, 'D: Verdauung', False, None, 'clin.health_issue']]
> 	2020-09-23 23:31:02 gmMacro.py::_get_variant_diagnoses() #2443): <class 'psycopg2.extras.DictRow'>
> 	2020-09-23 23:31:02 gmMacro.py::_escape_dict() #2851): 260
> 	2020-09-23 23:31:02 gmMacro.py::__getitem__() #880): placeholder handling error: diagnoses::		\item %(diagnosis)s::
> 	Traceback (most recent call last):
> 	  File "/home/ncq/Projekte/gm/git/gnumed/gnumed/Gnumed/wxpython/gmMacro.py", line 869, in __getitem__
> 	    val = handler(data = options)
> 	  File "/home/ncq/Projekte/gm/git/gnumed/gnumed/Gnumed/wxpython/gmMacro.py", line 2445, in _get_variant_diagnoses
> 	    return '\n'.join(template % self._escape_dict(dx, none_string = '?', bool_strings = [_('yes'), _('no')]) for dx in selected)
> 	  File "/home/ncq/Projekte/gm/git/gnumed/gnumed/Gnumed/wxpython/gmMacro.py", line 2445, in <genexpr>
> 	    return '\n'.join(template % self._escape_dict(dx, none_string = '?', bool_strings = [_('yes'), _('no')]) for dx in selected)
> 	  File "/home/ncq/Projekte/gm/git/gnumed/gnumed/Gnumed/wxpython/gmMacro.py", line 2852, in _escape_dict
> 	    val = the_dict[field]
> 	  File "/usr/lib/python3/dist-packages/psycopg2/extras.py", line 169, in __getitem__
> 	    return super(DictRow, self).__getitem__(x)
> 	IndexError: list index out of range
> 
> This line logs a list of DictRow's:
> 
> 	2020-09-23 23:31:02 gmMacro.py::_get_variant_diagnoses() #2442): [[260, 'L: Bewegungsapparat', False, 'B', 'clin.health_issue'], [260, 'K: aHT/HI', False, 'B', 'clin.health_issue'], [260, 'D: Verdauung', False, None, 'clin.health_issue']]
> 
> as evidenced here:
> 
> 	2020-09-23 23:31:02 gmMacro.py::_get_variant_diagnoses() #2443): <class 'psycopg2.extras.DictRow'>
> 
> The logging code:
> 
> 	_log.debug('%s', selected)
> 	_log.debug('%s', type(selected[0]))
> 
> and then iterates over the list of DictRow's as per list
> comprehension like so:
> 
> 	return '\n'.join(template % self._escape_dict(dx, none_string = '?', bool_strings = [_('yes'), _('no')]) for dx in selected)
> 
> where _escape_dict() does this:
> 
> 	def _escape_dict(self, the_dict=None, date_format='%Y %b %d  %H:%M', none_string='', bool_strings=None):
> 		data = {}
> 		for field in the_dict:
> 			_log.debug('%s', field)
> 			val = the_dict[field]
> 			if val is None:
> 				...
> 			if isinstance(val, bool):
> 				...
> 			if isinstance(val, datetime.datetime):
> 				...
> 			if self.__esc_style in ['latex', 'tex']:
> 				...
> 			elif self.__esc_style in ['xetex', 'xelatex']:
> 				...
> 		return data
> 
> Iterating over the_dict should work for lists (produces
> values) OR dicts (produces keys, under py3). It seems as if
> the line
> 
> 	for field in the_dict:			# I hoped to iterate over the keys = column names
> 
> somehow "decides": "currently we are treating the_dict as a
> list (after all, it is a DictRow, which _can_ be treated as a
> list) which then "turns" the dict-style access in
> 
> 	val = the_dict[field]
> 
> into a list access.
> 
> Somehow I have a feeling that due to the type of "field"
> -- it being integer (namely, a primary key) -- forces
> 
> 	the_dict[field]
> 
> to attempt a list-style index based access (which fails, due
> to there not being 260 columns in the SQL query result :-)
> 
> Solutions that come to mind:
> 
> 	_Must_ I use RealDictCursor() to safely avoid this trap ?
> 
> 	(or else dict()ify DictRow's as needed)
> 
> Any insights to be had ?


Maybe?:

con = psycopg2.connect("dbname=production host=localhost user=postgres", 
cursor_factory=DictCursor)
cur = con.cursor()
cur.execute("select * from cell_per")

the_dict = cur.fetchall()

# Example 1

for row in the_dict:
     for fld in row:
         print(fld)

# Return just field values
...

4
HERB 3.5
19
None
2020-09-22 17:34:31
None
postgres
herb
none
HR3

#Example 2

for row in the_dict:
     for fld in dict(row):
         print(fld, row[fld])

Showing the field name and field value.
...
line_id 4
category HERB 3.5
cell_per 19
ts_insert None
ts_update 2020-09-22 17:34:31
user_insert None
user_update postgres
plant_type herb
season none
short_category HR3


> 
> Thanks,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com