Return json/dictionary from psycopg3 SELECT query

Question

I've been asked to migrate a program from psycopg2 to psycopg3. In this program they use

with connection.cursor(cursor_factory=RealDictCursor) as cursor:

to obtain a dictionary that's later turned into a JSON file.

My problem is that RealDictCursor appears to be a psycopg2 extra feature, and as such get an error when trying to use it for psycopg3. Is there any alternative for use in psycopg3?

Tried using the psycopg2 library but didn't work. Didn't find any suitable alternative for psycopg3 other than manually going through the returned data

score 1 · Answer 1 · answered Nov 22 '22 at 08:38

The way to generate rows as dictionaries in psycopg3 is by passing the dict_row row factory to the connection.

>>> from psycopg.rows import dict_row
>>>
>>> conn = psycopg.connect(dbname='test', row_factory=dict_row)
>>> cur = conn.cursor()
>>> cur.execute('select id, name from users')
<psycopg.Cursor [TUPLES_OK] [INTRANS] (user=me database=test) at 0x7f0a2bebbdc0>
>>> cur.fetchall()
[
    {'id': 1, 'name': 'Alice'},
    {'id': 2, 'name': 'Bob'},
    {'id': 3, 'name': 'Carol'},
    {'id': 4, 'name': 'Dave'},
    {'id': 5, 'name': 'Eve'}
]
>>>

Return json/dictionary from psycopg3 SELECT query

1 Answers1