How to create sqlalchemy to json
Solution 1
It looks like your LabelsData
object is a SQLAlchemy model. You need to serialize it before dumping it to JSON. Here's a short example that extracts all the columns from your LabelsData
object and converts the results of your query to JSON:
from json import dumps
from sqlalchemy.orm import class_mapper
def serialize(model):
"""Transforms a model into a dictionary which can be dumped to JSON."""
# first we get the names of all the columns on your model
columns = [c.key for c in class_mapper(model.__class__).columns]
# then we return their values in a dict
return dict((c, getattr(model, c)) for c in columns)
# we can then use this for your particular example
serialized_labels = [
serialize(label)
for label in session.query(LabelsData).filter(LabelsData.deleted == False)
]
your_json = dumps(serialized_labels)
Solution 2
from collections import OrderedDict
class DictSerializable(object):
def _asdict(self):
result = OrderedDict()
for key in self.__mapper__.c.keys():
result[key] = getattr(self, key)
return result
From here and appearing to require simplejson. Hope that helps...
UPDATE: on a second look, it's a dictionary, which you can dump by any of the json modules in python.
Solution 3
Looks like sqlalchemy already has one http://docs.sqlalchemy.org/en/latest/core/serializer.html
from sqlalchemy.ext.serializer import loads, dumps
metadata = MetaData(bind=some_engine)
Session = scoped_session(sessionmaker())
# ... define mappers
query = Session.query(MyClass).filter(MyClass.somedata=='foo').order_by(MyClass.sortkey)
# pickle the query
serialized = dumps(query)
# unpickle. Pass in metadata + scoped_session
query2 = loads(serialized, metadata, Session)
print query2.all()
Solution 4
This blog post provided the solution I went with: http://blogs.gnome.org/danni/2013/03/07/generating-json-from-sqlalchemy-objects/
The strategy used was to include a .todict method directly to a Base mixin which iterates over the parent class's sqlalchemy columns.
Alternatively, Nande's approach (https://stackoverflow.com/a/19602809/837575) to use sqlalchemy.ext.serializer works well if you're trying to serialize data over the wire but don't necessarily need it as json.
Nolik
Updated on July 28, 2022Comments
-
Nolik almost 2 years
I have a sqlalchemy result
labels = session.query( LabelsData, LabelsData.id, LabelsData.name, LabelsData.color ).filter(LabelsData.deleted==False).all()
And I want convert this result to JSON, but how I can do it?