DictCursor doesn't seem to work under psycopg2

我以前没有使用过PsycopG2，但我正在尝试将光标工厂更改为DictCursor，以便FetchAll或FetchOne将返回字典而不是列表。

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.

64700 次浏览
小开
最佳答案 
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.

小开

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

小开

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

小开

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.

小开

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.

小开

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.


提交答案