query from postgresql using python as dictionary

Tags: , , , ,



I’m using Python 2.7 and postgresql 9.1. Trying to get dictionary from query, I’ve tried the code as described here: http://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL

import psycopg2
import psycopg2.extras
conn = psycopg2.connect("dbname=mydb host=localhost user=user password=password")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute ("select * from port")
type(cur.fetchall())

It is printing the next answer:

<type 'list'>

printing the item itself, show me that it is list. The excepted answer was dictionary.

Edit:

Trying the next:

ans = cur.fetchall()[0]
print ans
print type(ans)

returns

[288, 'T', 51, 1, 1, '192.168.39.188']
<type 'list'>

Answer

It’s normal: when you call .fetchall() method returns list of tuples. But if you write

type(cur.fetchone())

it will return only one tuple with type:

<class 'psycopg2.extras.DictRow'>

After this you can use it as list or like dictionary:

cur.execute('SELECT id, msg FROM table;')
rec = cur.fetchone()
print rec[0], rec['msg']

You can also use a simple cursor iterator:

res = [json.dumps(dict(record)) for record in cursor] # it calls .fetchone() in loop


Source: stackoverflow