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!

19 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

    1. It sounds like you’re getting a single result, e.g., query().first() instead of a list of results, e.g., query().all(). In that case, you would just use dict(res) instead of wrapping it in an array.

  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.

  5. hello,
    I struggle with this for quite a while now.
    I have this route in my app.py:

    @app.route(‘/json’, methods=[‘GET’])
    def json():
    jsonStr = BerlinWahlkreise.query.all()
    return json.dumps([dict(r) for r in jsonStr])

    and this function in my index.html:
    $.getJSON(‘/json’, function(data) {
    // add GeoJSON layer to the map once the file is loaded
    and I’m getting the error:
    AttributeError: ‘function’ object has no attribute ‘dumps’

    Any idea why?

    1. Usual reason is that the interface has changed in a newer version, if “dumps()” function is not found from json.

      But in this case, your function name is “json”, so probably Python is trying to access your function, not the json module. I’d first try naming your function something else.

  6. Thank you for a helpful tidbit! Today I’m only selecting a database field of string type (postgres’s citext type), but it’s great to know how to handle datetime and decimal as well.

Leave a Reply

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

Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.