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)