SQLAlchemy Results to JSON – the Easy Way

Another short web coding tidbit for today. I’ve been working on a Bottle.py+SQLAlchemy based backend for a personal project, and found very little information on how to convert a SQLAlchemy Core resultset into JSON.

Here is a glimpse into what was most commonly suggested. Tons of code. But wait, did you know you can cast a row into a dict? No? Well, it makes a world of difference:

import json

# somewhere here, accounts table is defined with SQLAlchemy syntax

def example():
    res = conn.execute(select([accounts]))

    # return all rows as a JSON array of objects
    return json.dumps([dict(r) for r in res])

Now, which do you prefer, the last line or the longer 17-lines in StackOverflow? There is a slight problem though, namely date and numeric(x,y) fields, as they will turn into datetime.date and decimal.Decimal types. Thankfully, Python’s JSON library can be provided with a function to handle classes that the library itself doesn’t handle:

import decimal, datetime

def alchemyencoder(obj):
    """JSON encoder function for SQLAlchemy special classes."""
    if isinstance(obj, datetime.date):
        return obj.isoformat()
    elif isinstance(obj, decimal.Decimal):
        return float(obj)

def example():
    res = conn.execute(select([accounts]))

    # use special handler for dates and decimals
    return json.dumps([dict(r) for r in res], default=alchemyencoder)

Published by

Joonas Pihlajamaa

Coding since 1990 in Basic, C/C++, Perl, Java, PHP, Ruby and Python, to name a few. Also interested in math, movies, anime, and the occasional slashdot now and then. Oh, and I also have a real life, but lets not talk about it!

7 thoughts on “SQLAlchemy Results to JSON – the Easy Way”

  1. Hi
    Unfortunately it doesn’t work for me. It tells me that the db.Model Object I use is not iterable:

    ‘Article’ object is not iterable

    Can you please help me? Many thanks in advance.
    Best regards

  2. This was fantastic Joonas!

    I struggled for a day and a half trying to sort this out reading the SQLAlchemy docs and doing lots of searches before finally coming across your post – wonderful.

    Mathias – you must be using the flask-sqlalchemy extension – I had similar problems and just switched everything to work directly with SQLAlchemy – without the flask wrapper – see http://docs.sqlalchemy.org/en/latest/

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.