pieceofpy.com

SQLalchemy and JSON w/ Pylons – Best Practices

I asked the question I Stackoverflow and maybe it was too generic for the site, since it just got trolled with “Google keyword” by some d-bag. So I deleted it and figured I’d throw it up on my blog a see about getting some feedback from the people who read this pile about. The reason I ask this is mainly because I am preparing to do some updated screencasts for Pylons.

I’ve seen multiple ways referenced in official docs and I have done it a few different ways myself. I am using Pylons and I am curious what the best practices are for this common scenario?

I have used something similar to this for auto-magically making the conversion happen.

# The auto-magic version
# I pulled this off a blog, forget the source.
def _sa_to_dict(obj):
    for item in obj.__dict__.items():
        if item[0][0] is '_':
            continue
        if isinstance(item[1], str):
            yield [item[0], item[1].decode()]
        else:
            yield item

def json(obj):
    if isinstance(obj, list):
        return dumps(map(dict, map(_sa_to_dict, obj)))
    else:
        return dumps(dict(_sa_to_dict(obj)))

# here is the controller
@jsonify
def index(self, format='html'):
    templates = Session.query(Template).all()
    if format == 'json':
        return json(templates)

I have also done the version where you use the jsonify decorator and build your dictionary manually, something like this, which is ok if I need to define some custom behavior for my JSON, but as the default behavior seems excessive.

@jsonify
def index(self, format='html'):
    if format == 'json':
        q = Session.query
        templates = [{'id': t.id,
                      'title': t.title,
                      'body': t.body} for t in q(Template)]
        return templates

I’ve also created an inherited SA class which defines a json method and have used that on all my objects to convert them to JSON. Similar to the the fedora extensions.

Maybe I missed some obviously library out there or some obvious helper in the Pylons packages, but I feel like this is a very common task being done a dozen different ways between docs, source, and my own personal projects. Curious what others are doing / using.

This entry was posted by Wayne on 17 Aug 2009 at 16:32 under pylons, sqlalchemy. Tagged sqlalchemy pylons json. You can leave a response, or trackback from your own site. Follow any responses to this entry through the RSS 2.0 feed.

13 Comments

Mike Lowe says:

TurboGears (http://turbogrears.org) might be worth looking at. The 2.0 branch is based on pylons and sqlalchemy and does automagical json conversions.
17 Aug 2009, 18:47
Mike Lowe says:

Me and my big fat fingers, that link should be http://turbogears.org
17 Aug 2009, 18:48
David says:

I was also looking for such a thing. However as far as I know there is not a standard solution as everyones requirements are slightly different when it comes to making the jsons. I have currently got something that sadly that is too integrated in my project to separate out, that does the above and a fair bit more. I was thinking of writing a small library to do it at one point. The problem with the solution above, as far as I can tell, is that it does not make sub dictionaries for joins and will not handle dates and decimal (money fields). The proper way would be to inspect the sqlalchemys metadata and work out what needs be extracted from there.
17 Aug 2009, 19:39
Valentino says:

This code actually contains a pretty bad bug. You are doing the opposite of what you should, essentially you are converting unicode objects to ascii (even when they don’t contain ascii-printable characters) by doing str(item[1]) but the real problem is that you are converting to ascii at all. If anything you need to decode a string into unicode and then jsonify it.

You should _never_ pass encoded strings in json.dumps unless they are actual bytes.
17 Aug 2009, 23:15
shayan says:

The problem with doing it automagically is that it’s quite often not exactly what you want & creating sub dictionaries across joins is hella expensive.
18 Aug 2009, 06:49
Lele Gaifax says:

I wrote a package that implements the logic which makes very easy exposing an arbitrary SQLAlchemy query on a controller, with filtering, ordering, formatting… Since it uses ExtJS on the frontend it’s able to export also a generic description of the query, enriched with fields names, descriptions and characteristics.

I used it first in my SoL app (see for example http://progetti.arstecnica.it/sol/browser/sol/controllers/data.py), then I generalized it and its now a standalone package: you can get it with “darcs get http://darcs.arstecnica.it/our/metapensiero.sqlalchemy.proxy“.
18 Aug 2009, 07:56
Hans says:

We typically do this sort of thing when we’re trying to expose some *-RPC services, which I imagine is the use case behind what you’re describing too. Difference for us is that typically we have AMF services in addition to JSON-RPC.

So what we typically do is have a generic “mypackage.services” module that exposes a bunch of methods that return VO (“value object”) versions of the SA objects. This is a Flex convention (Cairngorm, specifically), but it seems to make life easier in JSON too. The VOs are just “light objects” that just have attributes and that map directly to the model representation in the Flex client (or JSON-consuming client); typically the model as seen by the client looks fairly different from the DB definition, which may be strewn with views, stored procedures, etc. The VOs are created automatically from the SA table definitions in some cases, though we provide extension/override points for customizing that behavior.

Anyway, once a service object returns a VO (or a collection of VOs), then it’s up to the controller to actually do the serializing into JSON or AMF. We have a (single) JsonrpcController gateway and a single AmfController (from PyAMF) gateway for that purpose. They simply broker requests to/responses from the generic services methods. Additionally we have a JsonrpcSerializer which (similar to PyAMF) provides a mechanism for adding meta-data to the response JSON — like the “javaClass” attribute if we’re producing JSON for a Java consumer.

Anyway, I realize that’s all very non-concrete, but wanted to mention this idea of having an abstract services layer that deals with “light objects” rather than passing SA objects “directly” to a JSON serializer. While that additional abstraction may be a waste of time for extremely simple projects, we find that with anything more complex we really don’t want to be representing the database *directly* on the client — so that decoupling is important. Obviously in our case the “two-fer” benefit of being able to serialize responses to AMF or JSON is also a big win.
18 Aug 2009, 10:56
Wayne says:

@Mike I will check that out, even if just to rip out the libs that TG uses

@Valentino Thank you.

@Hans You don’t happen to have a brother named Nik do you?
18 Aug 2009, 14:06
Wayne says:

Yeah the auto-magically approach is my least used approach. I tend to just extend Base and place an override on the dict behavior and then allow the json libraries available to perform the conversions as they see fit. Problem with that is you have to ensure you have a list of that type you can’t feed in a ResultProxy and get back JSON and no, it really isn’t that big of deal to perform the extra step.
18 Aug 2009, 14:08
Marius Gedminas says:

I just wanted to point out that map(lambda x: fn(x), a_sequence) is the same as map(fn, a_sequence), only longer and more cryptic. I’d suggest rewriting

return dumps(map(lambda x: dict(x), map(lambda x: _sa_to_dict(x), obj)))

to

return dumps(map(dict, map(_sa_to_dict, obj)))

or

return dumps([dict(_sa_to_dict(x)) for x in obj])
18 Aug 2009, 14:24
Wayne says:

@Marius thank you, I agree completely. I never did take extra time to clean up the example as it was from a third party source. I’ve made your suggested refactor and it reads much easier.

Side note, if anyone knows the origin of that snippet above, please let me know so I can link back to it.
18 Aug 2009, 17:08
Daniel says:

You can use the object_mapper to iterate over all the ORM-defined properties in your mapped class. Usually there are interesting and uninteresting properties as far as serialization goes.

[source language='python']
from sqlalchemy.orm.util import object_mapper
from sqlalchemy.orm.properties import ColumnProperty, RelationProperty

class MappedClass(Base):
…
def properties(self):
propdict = {}
for prop in object_mapper(self).iterate_properties:
if isinstance(prop, (ColumnProperty)) or
isinstance(prop, RelationProperty) and prop.secondary:
propdict[prop.key] = getattr(self, prop.key)
[/source]
14 Jan 2010, 16:17
Chris Santiago says:

Personally, to avoid all of that boilerplate code, I just take advantage of the fact that Elixir Entity objects have a to_dict/from_dict method crafted especially for JSON, although I’m not too sure how nesting works for different cases, but it’s pretty solid for me anyway.
24 Jan 2010, 18:02