CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpsycopg2
Buzz Moschetti
Buzz Moschetti

Reputation: 7621

psycopg2.extras.DictRow behaves differently using for vs. next

This is v2.8.5 running in python 3.8.5. The following works as expected:

  curs = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  curs.execute("select fid from A")
  for row in curs:                                                   
    print(row['fid'])

but this does not:

  row = next(curs, None)                                             
  print(row['fid'])                                                       

  File "/usr/local/lib/python3.8/site-packages/psycopg2/extras.py", line 168, in __getitem__
    x = self._index[x]
KeyError: 'fid'

The type of row is <class 'psycopg2.extras.DictRow'> in both cases.

Obvious pilot error somewhere?

Upvotes: 0

Views: 1006

Answers (2)

Maurice Meyer
Maurice Meyer

Reputation: 18136

psycopg2.extras.DictRow already implements/wraps your next(...) calls and comes with a __inter__ function which prepares and filles the DictRow (source).

As you can see in the implementation of a _build_index function it populates an OrderedDict.
Looping over the cursor, creates an (filled) _index for each DictRow:

pgconn = psycopg2.connect("dbname=mf port=5959 host=localhost user=mf_usr")
curs = pgconn.cursor(cursor_factory=psycopg2.extras.DictCursor)

curs.execute("select * from users where id > 366200 and id < 366203")

for r in curs:
    print(r._index)

Out:

OrderedDict([('id', 0), ('firstname', 1), ('lastname', 2), ('birth', 3), ('ua', 4), ('nationality', 5)])
OrderedDict([('id', 0), ('firstname', 1), ('lastname', 2), ('birth', 3), ('ua', 4), ('nationality', 5)])

Using next(...) from the 'outside', behaves strange and does not use __iter__, so you end up with an empty index, so the KeyError drops in:

...
curs.execute("select * from users where id > 366200 and id < 366203")

row = next(curs, None)
print(row._index)

Out:

OrderedDict()

Upvotes: 2

Adrian Klaver
Adrian Klaver

Reputation: 19724

DictCursor is a hybrid structure:

https://www.psycopg.org/docs/extras.html

" class psycopg2.extras.DictCursor(*args, **kwargs)

A cursor that keeps a list of column name -> index mappings "

Try with RealDictCursor which returns an actual dictionary:

" class psycopg2.extras.RealDictCursor(*args, **kwargs)

A cursor that uses a real dict as the base type for rows.

Note that this cursor is extremely specialized and does not allow the normal access (using integer indices) to fetched data. If you need to access database rows both as a dictionary and a list, then use the generic DictCursor instead of RealDictCursor. "

As example:

con = psycopg2.connect("dbname=production host=localhost user=postgres", cursor_factory=RealDictCursor) 

cur = con.cursor()
cur.execute("select * from cell_per")
next(cur)["cell_per"]
18

Upvotes: 0

Related Questions