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!

11 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/

  3. This was really helpful.
    Is there any methos there we can convert this json in dictionary format to csv or any other format in javascript in order to display it finally as a table in the webpage.

  4. This was really helpful.
    Is there any methos there we can convert this json in dictionary format to csv or any other format in javascript in order to display it finally as a table in the webpage USING D3.

Leave a Reply

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

Time limit is exhausted. Please reload the CAPTCHA.