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.

Its astonishing how many answers are ignoring the actual problem of DictCursor not working. Half of the work arounds wont work if the DictCursor problem isnt solved. — Shayne, Jan 27 '20 at 11:47

score 139 · Answer 1 · edited Jun 09 '20 at 11:55

139

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

edited Jun 09 '20 at 11:55

Darragh Enright

13,676
7
41
48

answered Feb 10 '12 at 16:10

kerma

2,593
2
17
16

2

Yes this works.. Filed as bug in bottle-plugin https://github.com/raisoblast/bottle-pgsql/issues/2 – PedroMorgan Feb 16 '14 at 17:01
19

To solve `AttributeError: module 'psycopg2' has no attribute 'extras'` do `import psycopg2.extras` – techkuz Oct 22 '18 at 11:15

unutbu · Accepted Answer · 2017-11-30T01:15:26.650

44

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.

edited Nov 30 '17 at 01:15

answered Jul 18 '11 at 21:11

unutbu

842,883
184
1,785
1,677

Thanks, I think you're spot on with "dict-like". It's a list but acts like a dictionary. That seems like a strange design decision but at least it's working now. Thanks again. – Jim Jul 18 '11 at 21:19
24

@Seth: If you want a real dictionary, use the aptly named `RealDictCursor`. – Peter Eisentraut Jul 19 '11 at 10:19
@Seth: a dictionary has no order so you couldn't use it as a tuple anymore, the first column in the query wouldn't be `row[0]` anymore but something arbitrary. So there's some merit to it :) – Wolph Jul 05 '13 at 19:39
2

as @kerma specified below, [RealDictCursor](http://initd.org/psycopg/docs/extras.html#real-dictionary-cursor) can be used. Optionally same effect could be achieved by dict casting the row like so: `dict_cur = (dict(row) for row in cur)` @Seth: The docs state this for providing RealDictCursor: _Note that this cursor is extremely specialized and does not allow the normal access (using integer indices) to fetched data. If you need to access database rows both as a dictionary and a list, then use the generic DictCursor instead of RealDictCursor._ – farthVader Jan 27 '15 at 03:44
That should probably be `for row in res:`? – johndodo Nov 29 '17 at 19:21
1

@johndodo: Since `cur` itself is an iterable, you can use `for row in cur` after calling `cur.execute` (and without calling `cur.fetchall`). – unutbu Nov 30 '17 at 01:18

score 19 · Answer 3 · answered Jan 25 '15 at 18:02

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.

Also, the whole result can be converted to a list of dicts like this: `[row._asdict() for row in cur]` — GetFree, Jun 25 '16 at 11:26

Angry 84 · Answer 4 · 2016-10-19T03:07:51.807

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.

score 0 · Answer 5 · edited Jul 11 '13 at 12:29

0

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

edited Jul 11 '13 at 12:29

dakshbhatt21

3,558
3
31
40

answered Jul 11 '13 at 12:08

Hans Davidson

1

1

Leave out the parentheses and your query works: `SELECT ..., ..., ..., ... FROM ... WHERE ...`. – ᴠɪɴᴄᴇɴᴛ Aug 13 '14 at 13:08

score 0 · Answer 6 · answered Dec 28 '17 at 17:23

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.

DictCursor doesn't seem to work under psycopg2

6 Answers6

Linked