jsonb with psycopg2 RealDictCursor

Question

I have a postgresql 9.4 (aka mongodb killer ;-) ) and this simple schema :

CREATE TABLE test (id SERIAL, name text, misc jsonb);

now i populate this, if i make a select it will show something like

id    |     name      |    misc
1     |     user1     | { "age" : 23, "size" : "M" }
2     |     user2     | { "age" : 30, "size" : "XL" }

now, if i make a request with psycopg2,

cur.execute("SELECT * FROM test;")
rows = list(cur)

i'll end up with

[ { 'id' : 1, 'name' : 'user1', 'misc' : '{ "age" : 23, "size" : "M" }' }, 
{ 'id2' : 2, 'name' : 'user2', 'misc' : '{ "age" : 30, "size" : "XL' }' }]

what's wrong you would tell me ? well misc is type str. i would expect it to be recognized as json and converted as Python dict.

from psycopg2 doc (psycopg2/extras page) it states that "Reading from the database, json values will be automatically converted to Python objects."

with RealDictCursor it seems that it is not the case. it means that that i cannot access rows[0]['misc']['age'] as it would be convenient...

ok, i could do manually with

for r in rows:
    r['misc'] = json.loads(r['misc'])

but if i can avoid that because there's a nicer solution...

ps. someone with 1500+ rep could create the postgresql9.4 tag ;-)

Answer 1

Current psycopg version (2.5.3) doesn't know the oid for the jsonb type. In order to support it it's enough to call:

import psycopg2.extras
psycopg2.extras.register_json(oid=3802, array_oid=3807, globally=True)

once in your project.

You can find further information in this ML message.

Answer 2

Works out of the box with psycopg2 2.7.1 (not need to json.loads -- dictionaries are what come out of queries.)

  sudo apt-get install libpq-dev
  sudo pip install psycopg2 --upgrade
  python -c "import psycopg2 ; print psycopg2.__version__"

  2.7.1 (dt dec pq3 ext lo64)