How To Jsonify Objects From Sqlalchemy?

I'm using Flask, SQLAlchemy and javascript. I need to pass the results of my query to javascript in the json format, through AJAX, but I keep getting this error: raise TypeError(re

Solution 1:

As you’ve discovered, SQLAlchemy models are not natively serializable to JSON.

The approach you adopted - to manually construct a dict from your model instance’s properties - gets the job done, but if you need to jsonify multiple models, deal with value conversions on serialization, or support deserialization (converting JSON back to a SQLAlchemy model instance) you’ll find yourself writing a lot of tedious code.

A better solution is to use a serialization/deserialization extension library for SQLAlchemy, such as either SQLAthanor or Marshmallow-SQLAlchemy (full disclosure: I am the author of SQLAthanor).

Using either of these libraries, you can basically serialize and deserialize SQLAlchemy models to other formats or deserialize them back into SQLAlchemy model instances.

In the case of SQLAthanor, you can basically get the job done with one method call:

json_result = model_instance.dump_to_json()

or (with some more configuration that gives you more fine-grained control):

json_result = model_instance.to_json()

You can also serialize to dict, or YAML, or CSV.

The reverse process is:

model_instance = ModelClass.new_from_json(json_result)

Hope this helps!

Solution 2:

Like the error message says, you cannot call jsonify on a SQLAlchemy model object (I don't think you can pickle them either). Try:

clouds_d[idx] = dict((k, cloud.__dict__[k]) for k in cloud.__dict__ if k[0] != '_')

or better yet, be explicit:

clouds_d[idx] = {
    'id': cloud.id,
    'other_attrs': '...',
}

Solution 3:

After some more fiddling around, I fixed it, here's how:

@app.route('/past/<user_id>')
def get_past_clouds(user_id):
    if user_id:
        clouds = model_session.query(model.User).filter_by(id=user_id).first().clouds
        if clouds != "":
            clouds_d = {}
            for idx, cloud in enumerate(clouds):
                clouds_d[idx] = [ photo.path + photo.filename for photo in cloud.photos ]
            print "clouds: ", clouds_d
            return json.dumps(clouds_d)
    return None

It looks like SQLAlchemy objects are not serializable, so I had to get out exactly what I wanted from the objects it was returning.

Solution 4:

Based on TypeError: <model.Cloud object at 0x7f72e40c5910> is not JSON serializable' it seems in your clouds_d dictionary has a Cloud object nested inside. please like print clouds_d

Solution 5:

Here is my solution written in python:

step 1: custom JSON.encoder

import json
import uuid
from pprint import pformat
from datetime import datetime
from collections import OrderedDict

class CustomJSONEncoder(json.JSONEncoder):
    def default(self, obj):
        # cls = self.__class__
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S.%f')
        elif isinstance(obj, uuid.UUID):
            return str(obj)
        elif hasattr(obj, '__html__'):
            return str(obj.__html__())
        elif isinstance(obj, OrderedDict):
            m = json.dumps(obj)
        elif hasattr(obj, 'to_dict'):
            return obj.to_dict()
        else:
            mp = pformat(obj, indent=2)
            print("JsonEncodeError", type(obj), mp)
            m = json.JSONEncoder.default(self, obj)
        return m

step 2: override flask_app.json_encoder

from flask import Flask
app = Flask(__name__)
app.json_encoder = CustomJSONEncoder

step 3: Custom A Helper Class for metaclass of sqlalchemy

class CoModel():
    """
    A `Helper Class` for `metaclass` of sqlalchemy
    usage 1 : flask_sqlalchemy

        from flask_sqlalchemy import SQLAlchemy
        db = SQLAlchemy(session_options=session_options)
        class TableName(db.Model, CoModel):
            pass


    usage 2: sqlalchemy

        from sqlalchemy.ext.declarative import declarative_base
        Base = declarative_base()
        class TableName(Base, CoModel):
            __tablename__ = 'table_name'
    """

    def to_dict(self):
        result = {}
        columns = self.columns()
        for col in columns:
            name = col.name
            value = getattr(self, name)
            result[name] = value
        return result

    @classmethod
    def columns(cls):
        tbl = getattr(cls, "__table__", None)
        if tbl is None:
            raise NameError('use sample: class TableName(db.Model, CoModel)')
        else:
            return tbl.columns

Finally, if you response with flask.jsonify, it will be auto encoded by CoModel.to_dict()

Of course, you can use json.dumps(sqlalchmey_object,cls=CustomJSONEncoder) as well.