Seth Seth - 1 month ago 10
Python Question

DictCursor doesn't seem to work under psycopg2

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.

Answer
res = cur.fetchall()

makes res a list of psycopg2.extras.DictRows. If you do something like

for row in cur:
    print(row['column_name'])

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