Re: JSON type caster

On Tue, Sep 18, 2012 at 8:51 AM, Tobias Oberstein
<tobias(dot)oberstein(at)gmail(dot)com> wrote:
> Hi,
>
> I am adding a SQL to Python type caster for the new native JSON type in
> Postgres.

Sounds like a good idea.

> This seems to work, but I wonder if it's really complete / done right and
> should maybe already built into
> Psycopg2 since JSON is now a native PG type.

yes, I think we should add something for the next psycopg version.

> Any comments?
> Thanks,
> Tobias

> cur.execute("SELECT null::json, null::json[]")
> (json_oid, jsonarray_oid) = (cur.description[0][1], cur.description[1][1])
>
> print json_oid, jsonarray_oid # 114 199 => are those fixed for PG or
> instance specific?

Those are fixed since PG 9.2, but not in previous versions where json
could be installed as an extension. So it makes sense to register the
typecaster with these values but also to provide a function
register_json() with interface similar to register_hstore() that would
either take a connection or cursor and query them to know the oids, or
just take the oids in input for setup where querying is not possible.

> def cast_json(value, cur):
> if value is None:
> return None
> try:
> #o = json.loads(value)
> o = simplejson.loads(value, use_decimal = True)

I think this should be dependent on the Python version and use either
the builtin module or simplejson where not available. The loads
function should be probably exposed to the customer for better
customization of the result. So, all in all, I think psycopg should
expose a psycopg2.extras.register_json() function taking the loads
function in input, callling it with pre-configured parameters in order
to create a default typecaster in psycopg2.extensions (using the PG
9.2 oids, using the most appropriate loads function for the python
version etc) but leaving the user the possibility to override the
result by calling it again (use a different module, use different
parameters for loads etc.)

> return o
> except:
> raise InterfaceError("bad JSON representation: %r" % value)

Probably should be DataError. Not sure it is the case to dump the
entire value into the exception: it may be huge.

What should we do as an extension? There is no such a thing as a
Python json object. I think we should provide a thin wrapper similar
to psycopg2.Binary, that would be used:

from psycopg2.extensions import Json
data = {'my': ["stuff"]}
cur.execute("insert ...", [Json(data)])

If somebody wants to provide the code *and* the tests *and* the docs
let us know. I've opened a ticket about the feature
(http://psycopg.lighthouseapp.com/projects/62710-psycopg/tickets/124).
Best way to contribute would be a git branch.

Thank you very much,

-- Daniele