Geek Answers Handbook

Jsonb with psycopg2 RealDictCursor

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

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

now I sow it, if I make a choice, 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)

In the end, I get

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

What would you not tell me? good misc is type str. I would expect it to be recognized as json and converted as a Python dict.

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

using RealDictCursor it seems like this is not the case. this means that I cannot access the strings [0] ['misc'] ['age'], as that would be convenient ...

ok, I could do it manually using

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

but if I can avoid it because there is a nicer solution ...

ps. someone with 1500+ rep can create a postgresql9.4 tag; -)

+3
source share
2 answers

The current version of psycopg (2.5.3) does not know oid for type jsonb. To support it, just call:

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

once in your project.

You can find more information in this ML post .

+5
source

Works out of the box with psycopg2 2.7.1 (no json.loads needed - dictionaries are what comes out of the requests.)

  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)
+1
source

More articles:


All Articles