How to create sqlalchemy for json

Question

I have a sqlalchemy result

labels = session.query( LabelsData, LabelsData.id, LabelsData.name, LabelsData.color ).filter(LabelsData.deleted==False).all() 

And I want to convert this result to JSON, but how can I do this?

Answer 1

It looks like your LabelsData object is a SQLAlchemy model. You need to serialize it before sending it to JSON. Here is a short example that extracts all columns from your LabelsData object and converts the results of your query into 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) 

Answer 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 for calling simplejson , Hope this helps ...

UPDATE: at a second glance, this is a dictionary that you can unload with any of the json modules in python.

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

Answer 4

This blog post provided me with a solution: http://blogs.gnome.org/danni/2013/03/07/generating-json-from-sqlalchemy-objects/

The strategy used was to include the .todict method directly in the Base mixin, which iterates over the sqlalchemy columns of the parent class.

As an alternative to Nande ( https://stackoverflow.com/a/123908/ ), using sqlalchemy.ext.serializer is good if you are trying to serialize data over a cable, but do not necessarily need it as json.

Answer 5

I add this anwser as it is a mixture of @mekarpeles and @ hd1. I mean, I did not change the hierarchy of sqlalchemy objects by simply passing in a simple JSONEncoder :

 # given that you have Base = declarative_base() class SqlAlchemyModelEncoder(json.JSONEncoder): def default(self, obj): if isinstance(obj, Base): result = OrderedDict() for key in obj.__mapper__.c.keys(): result[key] = getattr(obj, key) return result return json.JSONEncoder.default(self, obj) 

If your models use non-serializable classes (e.g. datetime), you should add them to Encoder:

 if isinstance(obj, datetime): return obj.isoformat() 

And then I use it with jinja2 context filter:

 @contextfilter def tojson(ctx, model, *elements, **kw): return json.dumps(model, cls=SqlAlchemyModelEncoder) 

Answer 6

https://github.com/TaylorHere/PythonSerializer
The link above can give you a hand. This is a small script that can simply serialize the result of a sql query for a list or dict, like this:

 #Useage with flask and SQLalchemy from serializer import serializer def my_location(): if request.method == 'GET': user = db_session.query(User).filter( User.openid == session['user_id']).first() addresses = user.addresses return jsonify({'data': serializer(addresses.instance, 'sqlalchemy')})