18

AFAIU and from docs, RealDictCursor is a specialized DictCursor that enables to access columns only from keys (aka columns name), whereas DictCursor enables to access data both from keys or index number.
I was wondering why RealDictCursor has been implemented if DictCursor offers more flexibility? Is it performance-wise (or memory-wise) so different (in favor of RealDictCursor I imagine...)?
In other words, what are RealDictCursor use cases vs DictCursor?

klin
  • 112,967
  • 15
  • 204
  • 232
comte
  • 3,092
  • 5
  • 25
  • 41
  • 2
    In fact they are not as similar as documentation understates: access is similar but if you json.dumps(your_request), DictCursor won't show keys, RealDictCursor will show them. – comte Oct 08 '17 at 12:34
  • 1
    I assume that if your query returns a large number of rows that a real dict for each row would be a huge overhead, because it is storing keys (column names) with each value on each row, instead of the regular tuple, which has a smaller memory footprint. Personally I think named variables and named database columns are preferable to assuming that the order and number of columns in a database table will never change, and always try to use names and not index ordinals. It seems the dictcursor is somewhere in between, saving space per row by separately storing an index to column name mapping. – Davos Nov 17 '17 at 12:25

2 Answers2

26

The main advantage of real dictionary cursor is the easiness to get a query output as json.

Compare:

with psycopg2.connect('dbname=test') as connection:
    with connection.cursor(cursor_factory=RealDictCursor) as cursor:
        cursor.execute("select * from my_table")
        print(json.dumps(cursor.fetchall()))

versus

with psycopg2.connect('dbname=test') as connection:
    with connection.cursor() as cursor:
        cursor.execute("select * from my_table")
        columns = [desc[0] for desc in cursor.description]
        real_dict = [dict(zip(columns, row)) for row in cursor.fetchall()]
        print(json.dumps(real_dict))

There is no important difference between these options when it comes to performance.

You cannot get an expected json using json.dumps(cursor.fetchall()) for regular or dictionary-like cursors and need the conversion showed above. On the other hand, real dictionary cursor produces a much larger result so you should not use it if you really do not need it.

klin
  • 112,967
  • 15
  • 204
  • 232
1

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. class psycopg2.extras.RealDictConnection A connection that uses RealDictCursor automatically.

Note Not very useful since Psycopg2.5: you can use psycopg2.connect(dsn, cursor_factory=RealDictCursor) instead of RealDictConnection. class psycopg2.extras.RealDictRow(cursor) A dict subclass representing a data record.

Zahmati.qdev
  • 27
  • 3
  • This is the definition from the http://initd.org/psycopg/docs/extras.html site. It actually made me question using a RealDictCursor vs using what appears to be the suggested dictcursor as the default. I believe the note on this page is misleading with stating that it is very specialized. In most cases the header is needed for a returned query when translating the response to a table or csv with header. – Eric Evans Jul 26 '19 at 18:47