Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

query from postgresql using python as dictionary

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'>
like image 483
Guy Dafny Avatar asked Jan 16 '14 09:01

Guy Dafny


3 Answers

Tnx a lot Andrey Shokhin ,

full answer is:

#!/var/bin/python 
import psycopg2
import psycopg2.extras
conn = psycopg2.connect("dbname=uniart4_pr host=localhost user=user password=password")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute ("select * from port")
ans =cur.fetchall()
ans1 = []
for row in ans:
    ans1.append(dict(row))

print ans1  #actually it's return
like image 137
Guy Dafny Avatar answered Oct 18 '22 00:10

Guy Dafny


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
like image 16
Andrey Shokhin Avatar answered Oct 17 '22 23:10

Andrey Shokhin


Perhaps to optimize it further we can have

#!/var/bin/python 
import psycopg2
import psycopg2.extras

def get_dict_resultset(sql):
    conn = psycopg2.connect("dbname=pem host=localhost user=postgres password=Drupal#1008")
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute (sql)
    ans =cur.fetchall()
    dict_result = []
    for row in ans:
        dict_result.append(dict(row))
    return dict_result

sql = """select * from tablename"""
return get_dict_resultset(sql)
like image 13
Pralhad Narsinh Sonar Avatar answered Oct 18 '22 00:10

Pralhad Narsinh Sonar