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.

Answer 1

Use RealDictCursor:

import psycopg2.extras  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".

Answer 2

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.