Generating JSON from SQLAlchemy objects

I had to put together a small web app the other day, using SQLAlchemy and Flask. Because I hate writing code multiple times, when I can do things using a better way, I wanted to be able to serialise SQLAlchemy ORM objects straight to JSON.

I decided on an approach where taking a leaf out of Javascript, I would optionally implement a tojson() method on a class, which I would attempt to call from my JSONEncoder ((The tojson() method actually returns a Python dict understandable by JSONEncoder)).

It turns out to be relatively simple to extend SQLAlchemy’s declarative base class to add additional methods (we can also use this as an excuse to implement a general __repr__().

from sqlalchemy.ext.declarative import declarative_base as real_declarative_base

# Let's make this a class decorator
declarative_base = lambda cls: real_declarative_base(cls=cls)

@declarative_base
class Base(object):
    """
    Add some default properties and methods to the SQLAlchemy declarative base.
    """

    @property
    def columns(self):
        return [ c.name for c in self.__table__.columns ]

    @property
    def columnitems(self):
        return dict([ (c, getattr(self, c)) for c in self.columns ])

    def __repr__(self):
        return '{}({})'.format(self.__class__.__name__, self.columnitems)

    def tojson(self):
        return self.columnitems

We can then define our tables in the usual way:

class Client(Base):
    __tablename__ = 'client'

    ...

You can obviously replace any of the methods in your subclass, if you don’t want to serialise the whole thing. Bonus points for anyone who wants to extend this to serialise one-to-many relationships.

And what about calling the tojson() method? That’s easy, we can just provide our own JSONEncoder.

import json

class JSONEncoder(json.JSONEncoder):
    """
    Wrapper class to try calling an object's tojson() method. This allows
    us to JSONify objects coming from the ORM. Also handles dates and datetimes.
    """

    def default(self, obj):
        if isinstance(obj, datetime.date):
            return obj.isoformat()

        try:
            return obj.tojson()
        except AttributeError:
            return json.JSONEncoder.default(self, obj)

Cutting edge Flask provides a way to replace the default JSON encoder, but the version I got out of pip does not. This is relatively easy to work around though by replacing jsonify with our own version.

from flask import Flask

app = Flask(__name__)

def jsonify(*args, **kwargs):
    """
    Workaround for Flask's jsonify not allowing replacement of the JSONEncoder
    in my version of Flask.
    """

    return app.response_class(json.dumps(dict(*args, **kwargs),
                                         cls=JSONEncoder),
                              mimetype='application/json')

If you do have a newer Flask, where you don’t have to replace jsonify, you can also inherit from Flask’s JSONEncoder, which already handles things like datetimes for you.

Author: Danielle

Danielle is an Australian software engineer, computer scientist and feminist. She doesn't really work on GNOME any more (sadly). Opinions and writing are solely her own and so not represent her employer, the GNOME Foundation, or anyone else but herself. View all posts by Danielle

3 thoughts on “Generating JSON from SQLAlchemy objects”

My experience with Django over the years is that a direct ORM to JSON serializer is much less useful than you would imagine because you usually get either too much (wasting bytes and processing power in both ends), or in a wrong format, or in a less useful way than it could be. What I do is something like this in the view:

data = json.dumps([dict(foo=o.foo, bar=massage(o.bar), …) for o in some_queryset])

Simple, flexible and explicit, using the power of Python. Of course, YMMV.

BTW in your columnitems, you don’t actually need to use a list comprehension, instead you can just use a generator expression, i.e.:

return dict((c, getattr(self, c)) for c in self.columns)

Generator expressions returns a lazily evaluated iterable. They arrived later than list comprehensions, so somehow people still don’t think about them. I personally try to think of a list comprehension as a generator expression materialized into a list. Often you don’t need the materialization, and then it’s just more code and more work for the interpreter.

Thanks for a nice article.

I think it’s useful sometimes that models have default serialization methods. Of course that does not fit in every usecase, it helps to make some tasks easy, e.g. scaffolding admin controller/views if models are not so complex.

Indeed, the point here is to provide a default case that is overridden when needs/security/speed requires it, either by implementing your own columns property, your own tojson() method or wrapping the object in a “view” (which provides its own tojson()) that prepares the JSON in a particular way.

Author: Danielle

3 thoughts on “Generating JSON from SQLAlchemy objects”

Leave a Reply