SQLAlchemy Results to JSON – the Easy Way
Sun, Dec 7, 2014 in post Tips and Tricks Web coding conversion dict JSON RowProxy SQLAlchemy
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)
19 comments
R Sanchez:
awesome tip I have been struggling with this all day and the tons of nasty code that seemed to be required!
Zahir J:
I also found the stackoverflow post you quote to be “too much code”. Thank so much for sharing your elegant solution.
JC:
That was ridiculously easy, I too was searching for far too long for an elegant solution!
Thank you
user:
Amazing thank you! Simple and effective.
Thomas:
Awaresome! I dont know how i found your blog but im very thankful for your elegant solution!
Mathias:
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
akramic:
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/
ankur:
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.
ankur:
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.
ankur:
After json dumps, how do i keep order of my columns in same order as the original order
anonymous:
Bless you! This is exactly what I needed – straightforward and simple.
Peter:
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.
Coraline's Cat:
Thanks!
clgp:
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
L.geoJson(data).addTo(map);
});
and I’m getting the error:
AttributeError: ‘function’ object has no attribute ‘dumps’
Any idea why?
Joonas Pihlajamaa:
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.
Claudio Novoa:
Thanks man, works like a charm!
Robert:
It didn’t work for me, but I found something with Marshmallow. Quite simple.
https://www.youtube.com/watch?v=kRNXKzfYrPU
This is not my channel. Just sharing useful info.
Eric:
# simple and tricky
# @see https://docs.python.org/2/library/json.html#basic-usage
import json
json.dumps(my_dict, default=str)
Brian Skipworth:
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.