DictCursor doesn't seem to work under psycopg2


Question

I haven't worked with psycopg2 before but I'm trying to change the cursor factory to DictCursor so that fetchall or fetchone will return a dictionary instead of a list.

I created a test script to make things simple and only test this functionality. Here's my little bit of code that I feel should work

import psycopg2
import psycopg2.extras

conn = psycopg2.connect("dbname=%s user=%s password=%s" % (DATABASE, USERNAME, PASSWORD))

cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
cur.execute("SELECT * from review")

res = cur.fetchall()

print type(res)
print res

The res variable is always a list and not a dictionary as I would expect.

A current workaround that I've implemented is to use this function that builds a dictionary and run each row returned by fetchall through it.

def build_dict(cursor, row):
    x = {}
    for key,col in enumerate(cursor.description):
        x[col[0]] = row[key]
    return d

Python is version 2.6.7 and psycopg2 is version 2.4.2.

1
51
7/18/2011 8:59:48 PM

Accepted Answer

res = cur.fetchall()

makes res a list of psycopg2.extras.DictRows.


Alternatively, instead of calling cur.fetchall you can take advantage of the fact that cur is an iterable:

cur.execute("SELECT * from review")
for row in cur:
    print(row['column_name'])

and thus you'll be able to access the data with dict-like syntax.

27
11/30/2017 1:15:26 AM

Use RealDictCursor:

cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
cur.execute("SELECT * from review")
res = cur.fetchall()    

This gives you a list with rows as real python dictionaries instead of "advanced psycopg2 list".


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon