DictCursor doesn't seem to work under psycopg2

user_118850 0
Viewed 63126

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.

psycopg2 postgresql dictionary python
6 Answers

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.

user_14382650
Related