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

While this is an older question, it still comes up in google so i thought i would add my code to this for anyone else coming from the big G.

For me, i have multiple rows that i would like to return back into a dictionary and ideally dont want to use a loop or similar to set the key from a field in the database..

So using dict comprehension syntax i can do the following.

Table Rows Into Dictionary

---

pgCursor = Conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
pgCursor.execute("SELECT * FROM tablename;",([]))
dictRows = {n['id']: n for n in pgCursor}

---

Function & Calling It

#NOTE this is using a class object hence the self param
def DBTableToDictByID(self, squery):
    self.Pointer.execute(squery,([]))
    return {n['id']: n for n in self.Pointer}

dictRows = self.DBTableToDictByID("SELECT * FROM tablename;")

---

While this is using a for x in y loop, its pythonic as far as i can tell....Hopefully this will be of help to some out there.

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.

Answer 3

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".

Answer 4

In addition to use RealDictCursor feature, you may also have to ask for all columns (using the * symbol after select), as is done in the answer.

I was not interested in some columns of the result, since they had known values already used in WHERE conditions. But the SELECT (..., ..., ..., ...) FROM ... WHERE ... variant didn't give me dictionaries.

Best regards ! Harley

Answer 5

Another solution would be to use the Named Tuple Cursor since the Real Dict Cursor will break any query that uses integer indicies as explained in its documentation.

With Named Tuple Cursors, you can access them with dot syntax like so:

import psycopg2
import psycopg2.extras
cur = conn.cursor(cursor_factory = psycopg2.extras.NamedTupleCursor)
cur.execute("SELECT * from review")
res = cur.fetchone()
res.key1
res.key2

This keeps things tidy and won't break anything as far as I know.

Answer 6

So to make this work like the mysql version of the Dictionary cursor you will have to wrap it in another function or code. I will go on the forums and suggest this to them for future deployments of their code to return a dictionary when the fetchall() call is used with the Dictionary Cursor. Here is some sample code you can use to fix for it:

cursor.execute(query)
# Python 2.7 and beyond with dictionary comprehension
results = [{key:value for key,value in row.iteritems()} for row in cursor]
# Python 2.6 and before
# results = [dict((key,value) for key,value in row.iteritems()) for row in cursor]

This code makes it the same format as the MySQL version of the dictionary cursor using fetchall(). Not sure why they implemented it differently, but this will help you get the same output of an actual python dictionary rather than a list in the fetchall() case.