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
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)