6

I have a psql query that returns a json object. I fetch this query with results.fetchall() and I get the json properly as a dict. however, as I'm in python 3.4, not yet in 3.6, the objects' order is not preserved in the dict. I saw there's a way to use OrderedDict to keep the order of the json but I'm not sure how to tell sqlalchemy/psycopg2 to use it.

can anybody help please?

akiva
  • 2,677
  • 3
  • 31
  • 40

1 Answers1

4

As indicated in the documentation, you must provide a custom deserializer when creating your engine:

from functools import partial
import json, collections

engine = create_engine(
    ...,
    json_deserializer=partial(
        json.loads, 
        object_pairs_hook=collections.OrderedDict),
    )
donkopotamus
  • 22,114
  • 2
  • 48
  • 60
  • thanks a lot @donkopotamus any suggestion how to make this deserializer used only for a particular `fetchhall`? – akiva Aug 06 '17 at 10:41
  • 1
    @akiva For a given fetchall you won't necessarily know the connection that is being used (since there is a connection pool). You may need to instead push a custom json deserializer on at the `psycopg2` level (using [`psycopg2.extras.register_default_json`](http://initd.org/psycopg/docs/extras.html#psycopg2.extras.register_default_json)) and then pop it off. That's what sqlalchemy is doing in the engine call – donkopotamus Aug 06 '17 at 11:07