jsonify a SQLAlchemy result set in Flask [duplicate]

Question

I'm trying to jsonify a SQLAlchemy result set in Flask/Python.

The Flask mailing list suggested the following method http://librelist.com/browser//flask/2011/2/16/jsonify-sqlalchemy-pagination-collection-result/#04a0754b63387f87e59dda564bde426e :

return jsonify(json_list = qryresult)

However I'm getting the following error back:

TypeError: <flaskext.sqlalchemy.BaseQuery object at 0x102c2df90> 
is not JSON serializable

What am I overlooking here?

I have found this question: How to serialize SqlAlchemy result to JSON? which seems very similar however I didn't know whether Flask had some magic to make it easier as the mailing list post suggested.

Edit: for clarification, this is what my model looks like

class Rating(db.Model):

    __tablename__ = 'rating'

    id = db.Column(db.Integer, primary_key=True)
    fullurl = db.Column(db.String())
    url = db.Column(db.String())
    comments = db.Column(db.Text)
    overall = db.Column(db.Integer)
    shipping = db.Column(db.Integer)
    cost = db.Column(db.Integer)
    honesty = db.Column(db.Integer)
    communication = db.Column(db.Integer)
    name = db.Column(db.String())
    ipaddr = db.Column(db.String())
    date = db.Column(db.String())

    def __init__(self, fullurl, url, comments, overall, shipping, cost, honesty, communication, name, ipaddr, date):
        self.fullurl = fullurl
        self.url = url
        self.comments = comments
        self.overall = overall
        self.shipping = shipping
        self.cost = cost
        self.honesty = honesty
        self.communication = communication
        self.name = name
        self.ipaddr = ipaddr
        self.date = date

Acoustics

jsonify(list(map(lambda x: x.to_dict(), qryresult))) – Jim

Answer 1

It seems that you actually haven't executed your query. Try following:

return jsonify(json_list = qryresult.all())

[Edit]: Problem with jsonify is, that usually the objects cannot be jsonified automatically. Even Python's datetime fails ;)

What I have done in the past, is adding an extra property (like serialize) to classes that need to be serialized.

def dump_datetime(value):
    """Deserialize datetime object into string form for JSON processing."""
    if value is None:
        return None
    return [value.strftime("%Y-%m-%d"), value.strftime("%H:%M:%S")]

class Foo(db.Model):
    # ... SQLAlchemy defs here..
    def __init__(self, ...):
       # self.foo = ...
       pass

    @property
    def serialize(self):
       """Return object data in easily serializable format"""
       return {
           'id'         : self.id,
           'modified_at': dump_datetime(self.modified_at),
           # This is an example how to deal with Many2Many relations
           'many2many'  : self.serialize_many2many
       }
    @property
    def serialize_many2many(self):
       """
       Return object's relations in easily serializable format.
       NB! Calls many2many's serialize property.
       """
       return [ item.serialize for item in self.many2many]

And now for views I can just do:

return jsonify(json_list=[i.serialize for i in qryresult.all()])

[Edit 2019]: In case you have more complex objects or circular references, use a library like marshmallow).

Resor

Hmm, that has changed the error, now I'm getting the error referencing the SQLAlchemy object as in: myapp.models.Rating object at 0x102f25c10> is not JSON serializable. Any clue? The object only contains Strings and Ints. – Acoustics

It seems that your Rating model contains relations to other objects, so please wait a sec, I'll update my answer.. – Resor

It doesn't though, I've added my model definition to my original question. I appreciate your help! – Acoustics

Thanks! That's got it!! For anyone reading through this, the call should be return jsonify(json_list=[i.serialize for i in qryresult.all()]) for the example given above (ie not dump). – Acoustics

@mwan100, thanks. In my original code I had dump property, no idea why I wanted to change that :S – Resor

Curious to know why you chose to make serialize a property rather than a function? – Grosvenor

@Mohamed 7 years ago it made sense. I created serialize is a property because I did not need to push any arguments to it.. Of course, as_json would've been better name. – Resor

@Resor How would you deal with two models with many to many relationships that reference each other? For example a User model has comments and the Comment model has a User attached to it. If you call serialize on any of them, you get a recursion error. – Distributor

I don't really deal with it. And this answer was just a quickie solution... – Resor

Flask's jsonify actually can handle datetimes now, but it may still be helpful to format them on your own since it returns them in RFC 822 format which is pretty unusual IMO. Docs: flask.palletsprojects.com/en/1.1.x/api/#flask.json.JSONEncoder – Wallin

Answer 2

Here's what's usually sufficient for me:

I create a serialization mixin which I use with my models. The serialization function basically fetches whatever attributes the SQLAlchemy inspector exposes and puts it in a dict.

from sqlalchemy.inspection import inspect

class Serializer(object):

    def serialize(self):
        return {c: getattr(self, c) for c in inspect(self).attrs.keys()}

    @staticmethod
    def serialize_list(l):
        return [m.serialize() for m in l]

All that's needed now is to extend the SQLAlchemy model with the Serializer mixin class.

If there are fields you do not wish to expose, or that need special formatting, simply override the serialize() function in the model subclass.

class User(db.Model, Serializer):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String)
    password = db.Column(db.String)

    # ...

    def serialize(self):
        d = Serializer.serialize(self)
        del d['password']
        return d

In your controllers, all you have to do is to call the serialize() function (or serialize_list(l) if the query results in a list) on the results:

def get_user(id):
    user = User.query.get(id)
    return json.dumps(user.serialize())

def get_users():
    users = User.query.all()
    return json.dumps(User.serialize_list(users))

Pornography

If you are using jsonify for get_users, your syntax will need to be: return jsonify( users = User.serialize_list( users ) ) – Scurrility

I think this answer is way better than the others, thanks. inspect() is killer. – Frigging

This works for me. – Pumpkin

Fast and easy, thanks a lot ! – Ham

This is nice solution. But it will not work for nested attributes that need serialization. – Lowpressure

This is awesome, thank you!! – Footpad

I had to customise the method serialize to skip my foreign keys, otherwise it shouts 'myForeignObject is not serializable'. (those attributes you set with db.relationship(...)) – Monster

Answer 3

I had the same need, to serialize into json. Take a look at this question. It shows how to discover columns programmatically. So, from that I created the code below. It works for me, and I'll be using it in my web app.

def to_json(inst, cls):
    """
    Jsonify the sql alchemy query result.
    """
    convert = dict()
    # add your coversions for things like datetime's 
    # and what-not that aren't serializable.
    d = dict()
    for c in cls.__table__.columns:
        v = getattr(inst, c.name)
        if c.type in convert.keys() and v is not None:
            try:
                d[c.name] = convert[c.type](v)
            except:
                d[c.name] = "Error:  Failed to covert using ", str(convert[c.type])
        elif v is None:
            d[c.name] = str()
        else:
            d[c.name] = v
    return json.dumps(d)

class Person(base):
    __tablename__ = 'person'
    id = Column(Integer, Sequence('person_id_seq'), primary_key=True)
    first_name = Column(Text)
    last_name = Column(Text)
    email = Column(Text)

    @property
    def json(self):
        return to_json(self, self.__class__)

Fortnight

This looks like a good fit for my current project, but I'm using non-declarative models. As such, it doesn't seem that I have access to __table__ in the class even after mapping occurs. Any thoughts on how to adapt to_json for a non-declarative model? – Dispatch

I ended up just adding my own Table objects to each model (__table__ = my_table_instance) which seems to have worked. – Dispatch

Also you could extend the declarative Base class to include the json property in all your models automatically. – Mecham

How do I get this to work with datetime? I ended up adding sqlalchemy.sql.sqltypes.Date to the convert dict, and then I changed every instance of c.type to type(c.type). – Azaleeazan

@Fortnight In which cases will the column name be None? – Pollerd

Here's a similar solution: wakatime.com/blog/32-part-1-sqlalchemy-models-to-json – Verticillaster

Answer 4

Here's my approach: https://github.com/n0nSmoker/SQLAlchemy-serializer

pip install SQLAlchemy-serializer

You can easily add mixin to your model and then just call .to_dict() method on its instance.

You also can write your own mixin on base of SerializerMixin.

Rampage

Interesting solution. I had to add elif isinstance(value, str): ret = value before the elif hasattr(value, 'iter'): in python3 to avoid infinite recursion – Phenoxide

I also took out the if value: check in get_public because it was dropping integers = 0 – Phenoxide

Answer 5

For a flat query (no joins) you can do this

@app.route('/results/')
def results():
    data = Table.query.all()
    result = [d.__dict__ for d in data]
    return jsonify(result=result)

and if you only want to return certain columns from the database you can do this

@app.route('/results/')
def results():
    cols = ['id', 'url', 'shipping']
    data = Table.query.all()
    result = [{col: getattr(d, col) for col in cols} for d in data]
    return jsonify(result=result)

Noonan

worked for me as I used postgresql and had a table in it which I was querying on. Thanks a lot! – Ackerman

TypeError: Object of type InstanceState is not JSON serializable is NOT a solution – Popedom

I'm also hitting the same error as @fsalazar_sch: TypeError: Object of type InstanceState is not JSON serializable – Pivotal

Answer 6

Ok, I've been working on this for a few hours, and I've developed what I believe to be the most pythonic solution yet. The following code snippets are python3 but shouldn't be too horribly painful to backport if you need.

The first thing we're gonna do is start with a mixin that makes your db models act kinda like dicts:

from sqlalchemy.inspection import inspect

class ModelMixin:
    """Provide dict-like interface to db.Model subclasses."""

    def __getitem__(self, key):
        """Expose object attributes like dict values."""
        return getattr(self, key)

    def keys(self):
        """Identify what db columns we have."""
        return inspect(self).attrs.keys()

Now we're going to define our model, inheriting the mixin:

class MyModel(db.Model, ModelMixin):
    id = db.Column(db.Integer, primary_key=True)
    foo = db.Column(...)
    bar = db.Column(...)
    # etc ...

That's all it takes to be able to pass an instance of MyModel() to dict() and get a real live dict instance out of it, which gets us quite a long way towards making jsonify() understand it. Next, we need to extend JSONEncoder to get us the rest of the way:

from flask.json import JSONEncoder
from contextlib import suppress

class MyJSONEncoder(JSONEncoder):
    def default(self, obj):
        # Optional: convert datetime objects to ISO format
        with suppress(AttributeError):
            return obj.isoformat()
        return dict(obj)

app.json_encoder = MyJSONEncoder

Bonus points: if your model contains computed fields (that is, you want your JSON output to contain fields that aren't actually stored in the database), that's easy too. Just define your computed fields as @propertys, and extend the keys() method like so:

class MyModel(db.Model, ModelMixin):
    id = db.Column(db.Integer, primary_key=True)
    foo = db.Column(...)
    bar = db.Column(...)

    @property
    def computed_field(self):
        return 'this value did not come from the db'

    def keys(self):
        return super().keys() + ['computed_field']

Now it's trivial to jsonify:

@app.route('/whatever', methods=['GET'])
def whatever():
    return jsonify(dict(results=MyModel.query.all()))

Frigging

I think your answer somehow like what I did. – Cuss

Nice answer because it works with original flask.jsonify() – Salleysalli

Answer 7

If you are using flask-restful you can use marshal:

from flask.ext.restful import Resource, fields, marshal

topic_fields = {
    'title':   fields.String,
    'content': fields.String,
    'uri':     fields.Url('topic'),
    'creator': fields.String,
    'created': fields.DateTime(dt_format='rfc822')
}

class TopicListApi(Resource):
    def get(self):
        return {'topics': [marshal(topic, topic_fields) for topic in DbTopic.query.all()]}

You need to explicitly list what you are returning and what type it is, which I prefer anyway for an api. Serialization is easily taken care of (no need for jsonify), dates are also not a problem. Note that the content for the uri field is automatically generated based on the topic endpoint and the id.

Draghound

Answer 8

Here's my answer if you're using the declarative base (with help from some of the answers already posted):

# in your models definition where you define and extend declarative_base()
from sqlalchemy.ext.declarative import declarative_base
...
Base = declarative_base()
Base.query = db_session.query_property()
...

# define a new class (call "Model" or whatever) with an as_dict() method defined
class Model():
    def as_dict(self):
        return { c.name: getattr(self, c.name) for c in self.__table__.columns }

# and extend both the Base and Model class in your model definition, e.g.
class Rating(Base, Model):
    ____tablename__ = 'rating'
    id = db.Column(db.Integer, primary_key=True)
    fullurl = db.Column(db.String())
    url = db.Column(db.String())
    comments = db.Column(db.Text)
    ...

# then after you query and have a resultset (rs) of ratings
rs = Rating.query.all()

# you can jsonify it with
s = json.dumps([r.as_dict() for r in rs], default=alchemyencoder)
print (s)

# or if you have a single row
r = Rating.query.first()

# you can jsonify it with
s = json.dumps(r.as_dict(), default=alchemyencoder)

# you will need this alchemyencoder where your are calling json.dumps to handle datetime and decimal format
# credit to Joonas @ http://codeandlife.com/2014/12/07/sqlalchemy-results-to-json-the-easy-way/
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)

Bijugate

Answer 9

Flask-Restful 0.3.6 the Request Parsing recommend marshmallow

marshmallow is an ORM/ODM/framework-agnostic library for converting complex datatypes, such as objects, to and from native Python datatypes.

A simple marshmallow example is showing below.

from marshmallow import Schema, fields

class UserSchema(Schema):
    name = fields.Str()
    email = fields.Email()
    created_at = fields.DateTime()

from marshmallow import pprint

user = User(name="Monty", email="[email protected]")
schema = UserSchema()
result = schema.dump(user)
pprint(result)
# {"name": "Monty",
#  "email": "[email protected]",
#  "created_at": "2014-08-17T14:54:16.049594+00:00"}

The core features contain

Declaring Schemas
Serializing Objects (“Dumping”)
Deserializing Objects (“Loading”)
Handling Collections of Objects
Validation
Specifying Attribute Names
Specifying Serialization/Deserialization Keys
Refactoring: Implicit Field Creation
Ordering Output
“Read-only” and “Write-only” Fields
Specify Default Serialization/Deserialization Values
Nesting Schemas
Custom Fields

Gabler

Answer 10

Here is a way to add an as_dict() method on every class, as well as any other method you want to have on every single class. Not sure if this is the desired way or not, but it works...

class Base(object):
    def as_dict(self):
        return dict((c.name,
                     getattr(self, c.name))
                     for c in self.__table__.columns)


Base = declarative_base(cls=Base)

Backward

Answer 11

I've been looking at this problem for the better part of a day, and here's what I've come up with (credit to https://mcmap.net/q/102749/-serializing-sqlalchemy-models-for-a-rest-api-while-respecting-access-control for pointing me in this direction).

(Note: I'm using flask-sqlalchemy, so my model declaration format is a bit different from straight sqlalchemy).

In my models.py file:

import json

class Serializer(object):
  __public__ = None
  "Must be implemented by implementors"

  def to_serializable_dict(self):
    dict = {}
    for public_key in self.__public__:
      value = getattr(self, public_key)
      if value:
        dict[public_key] = value
    return dict

class SWEncoder(json.JSONEncoder):
  def default(self, obj):
    if isinstance(obj, Serializer):
      return obj.to_serializable_dict()
    if isinstance(obj, (datetime)):
      return obj.isoformat()
    return json.JSONEncoder.default(self, obj)


def SWJsonify(*args, **kwargs):
  return current_app.response_class(json.dumps(dict(*args, **kwargs), cls=SWEncoder, indent=None if request.is_xhr else 2), mimetype='application/json')
  # stolen from https://github.com/mitsuhiko/flask/blob/master/flask/helpers.py

and all my model objects look like this:

class User(db.Model, Serializer):
  __public__ = ['id','username']
  ... field definitions ...

In my views I call SWJsonify wherever I would have called Jsonify, like so:

@app.route('/posts')
def posts():
  posts = Post.query.limit(PER_PAGE).all()
  return SWJsonify({'posts':posts })

Seems to work pretty well. Even on relationships. I haven't gotten far with it, so YMMV, but so far it feels pretty "right" to me.

Suggestions welcome.

Debonair

Answer 12

I was looking for something like the rails approach used in ActiveRecord to_json and implemented something similar using this Mixin after being unsatisfied with other suggestions. It handles nested models, and including or excluding attributes of the top level or nested models.

class Serializer(object):

    def serialize(self, include={}, exclude=[], only=[]):
        serialized = {}
        for key in inspect(self).attrs.keys():
            to_be_serialized = True
            value = getattr(self, key)
            if key in exclude or (only and key not in only):
                to_be_serialized = False
            elif isinstance(value, BaseQuery):
                to_be_serialized = False
                if key in include:
                    to_be_serialized = True
                    nested_params = include.get(key, {})
                    value = [i.serialize(**nested_params) for i in value]

            if to_be_serialized:
                serialized[key] = value

        return serialized

Then, to get the BaseQuery serializable I extended BaseQuery

class SerializableBaseQuery(BaseQuery):

    def serialize(self, include={}, exclude=[], only=[]):
        return [m.serialize(include, exclude, only) for m in self]

For the following models

class ContactInfo(db.Model, Serializer):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    full_name = db.Column(db.String())
    source = db.Column(db.String())
    source_id = db.Column(db.String())

    email_addresses = db.relationship('EmailAddress', backref='contact_info', lazy='dynamic')
    phone_numbers = db.relationship('PhoneNumber', backref='contact_info', lazy='dynamic')


class EmailAddress(db.Model, Serializer):
    id = db.Column(db.Integer, primary_key=True)
    email_address = db.Column(db.String())
    type = db.Column(db.String())
    contact_info_id = db.Column(db.Integer, db.ForeignKey('contact_info.id'))


class PhoneNumber(db.Model, Serializer):
    id = db.Column(db.Integer, primary_key=True)
    phone_number = db.Column(db.String())
    type = db.Column(db.String())
    contact_info_id = db.Column(db.Integer, db.ForeignKey('contact_info.id'))

    phone_numbers = db.relationship('Invite', backref='phone_number', lazy='dynamic')

You could do something like

@app.route("/contact/search", methods=['GET'])
def contact_search():
    contact_name = request.args.get("name")
    matching_contacts = ContactInfo.query.filter(ContactInfo.full_name.like("%{}%".format(contact_name)))

    serialized_contact_info = matching_contacts.serialize(
        include={
            "phone_numbers" : {
                "exclude" : ["contact_info", "contact_info_id"]
            },
            "email_addresses" : {
                "exclude" : ["contact_info", "contact_info_id"]
            }
        }
    )

    return jsonify(serialized_contact_info)

Mindimindless

Answer 13

I was working with a sql query defaultdict of lists of RowProxy objects named jobDict It took me a while to figure out what Type the objects were.

This was a really simple quick way to resolve to some clean jsonEncoding just by typecasting the row to a list and by initially defining the dict with a value of list.

    jobDict = defaultdict(list)
    def set_default(obj):
        # trickyness needed here via import to know type
        if isinstance(obj, RowProxy):
            return list(obj)
        raise TypeError


    jsonEncoded = json.dumps(jobDict, default=set_default)

Cagey

Answer 14

I just want to add my method to do this.

just define a custome json encoder to serilize your db models.

class ParentEncoder(json.JSONEncoder):
    def default(self, obj):
        # convert object to a dict
        d = {}
        if isinstance(obj, Parent):
            return {"id": obj.id, "name": obj.name, 'children': list(obj.child)}
        if isinstance(obj, Child):
            return {"id": obj.id, "name": obj.name}

        d.update(obj.__dict__)
        return d

then in your view function

parents = Parent.query.all()
dat = json.dumps({"data": parents}, cls=ParentEncoder)
resp = Response(response=dat, status=200, mimetype="application/json")
return (resp)

it works well though the parent have relationships

Cuss

Answer 15

It's been a lot of times and there are lots of valid answers, but the following code block seems to work:

my_object = SqlAlchemyModel()
my_serializable_obj = my_object.__dict__
del my_serializable_obj["_sa_instance_state"]
print(jsonify(my_serializable_object))

I'm aware that this is not a perfect solution, nor as elegant as the others, however for those who want o quick fix, they might try this.

Thurmond