带有psycopg2 RealDictCursor的jsonb [英] jsonb with psycopg2 RealDictCursor
问题描述
我有一个PostgreSQL 9.4(aka mongodb killer ;-))和这个简单的模式:
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" }
现在,如果我使用psycopg2的请求,
now, if i make a request with psycopg2,
cur.execute("SELECT * FROM test;")
rows = list(cur)
我最终会得到
[ { 'id' : 1, 'name' : 'user1', 'misc' : '{ "age" : 23, "size" : "M" }' },
{ 'id2' : 2, 'name' : 'user2', 'misc' : '{ "age" : 30, "size" : "XL' }' }]
您会告诉我什么地方错了?错是类型str。我希望它会被识别为json并转换为Python dict。
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.
( psycopg2 / extras页面)指出从数据库中读取json值将自动转换为Python对象。
from psycopg2 doc (psycopg2/extras page) it states that "Reading from the database, json values will be automatically converted to Python objects."
使用RealDictCursor似乎并非如此。
,这意味着我无法访问row [0] ['misc'] ['age'],因为这很方便...
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...
好的,我可以手动使用
for r in rows:
r['misc'] = json.loads(r['misc'])
但是如果可以避免的话,因为有更好的解决方案...
but if i can avoid that because there's a nicer solution...
ps。
具有1500+代表的人可以创建postgresql9.4标记;-)
ps. someone with 1500+ rep could create the postgresql9.4 tag ;-)
推荐答案
当前的psycopg版本(2.5。 3)不知道jsonb类型的oid。为了支持它,调用它就足够了:
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)
在您的项目中一次。
您可以在此ML消息。
这篇关于带有psycopg2 RealDictCursor的jsonb的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!