psycopg2.extras.DictCursor not returning dict in postgres

Question

Im using psycopg2 to access postgres database using the below query. In order to return a dictionary from the executed query, im using DictCursor in my cursor but still my output is a list and not a dictonary.

Here is the program and output below.

import psycopg2.extras

try:
    conn = psycopg2.connect("user='postgres' host='localhost' password='postgres'",
                            )
except:
    print "I am unable to connect to the database"

cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

cur.execute("""SELECT datname from pg_database""")


rows = cur.fetchall()
print "\nShow me the databases:\n"
print rows

Output:-

[['template1'], ['template0'], ['postgres'], ['iip'], ['test'], ['test_postgres'], ['testdb']]

Answer 1

It looks like a list, smells like a list, but it's a DictRow.

rows = cur.fetchall()
for row in rows :
    print(type(row))

#>>> <class 'psycopg2.extras.DictRow'>

This means that you can still use the column names as keys to access the data :

rows = cur.fetchall()
print([row['datname'] for row in rows])

---

This class inherits directly from the builtinlist and add all the needed methods to implement a dictionary logic, but it doesn't change the representation __repr__ or __str__, so the output is the same as a list.

class DictRow(list):
    """A row object that allow by-column-name access to data."""

---

fetchall() packs all the queried rows in a list without specifying the exact type.

Btw, maybe you are looking for this kind of cursor : RealDictCursor ?

Answer 2

For those who came where because they really like the easy reference of the dictionary for column:value record representation, the answer by PRMoureu which notes that the DictRow has all the usual dictionary logic means that you can iterate over the DictRow with .items() and get the key:value pairs.

rows = cur.fetchall()
row_dict = [{k:v for k, v in record.items()} for record in rows]

Will turn your list of DictRow records into a list of dict records.