jsonify a SQLAlchemy result set in Flask

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   lt flaskext sqlalchemy BaseQuery object at 0x102c2df90 gt   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

User · Answer

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)

User · Answer

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

User · Answer

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       Hope this helps      Edit 2019   In case you have more complex objects or circular references  use a library like marshmallow

User · Answer

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  monty python org   schema   UserSchema   result   schema dump user  pprint result      name    Monty       email    monty python org       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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   Happy coding    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here s my approach  https   github com n0nSmoker SQLAlchemy-serializer  pip install SQLAlchemy-serializer  You can easily add mixin to your model and than just call  to dict   method on it s instance  You also can write your own mixin on base of SerializerMixin

User · Answer

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   stackoverflow com a 5249214 196358 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

[python] jsonify a SQLAlchemy result set in Flask

Examples related to python

Examples related to sqlalchemy

Examples related to flask

Examples related to flask-sqlalchemy