How to serialize SqlAlchemy result to JSON

Question

Django has some good automatic serialization of ORM models returned from DB to JSON format   How to serialize SQLAlchemy query result to JSON format    I tried jsonpickle encode but it encodes query object itself  I tried json dumps items  but it returns  TypeError   lt Product  3    some name    some desc   gt  is not JSON serializable   Is it really so hard to serialize SQLAlchemy ORM objects to JSON  XML  Isn t there any default serializer for it  It s very common task to serialize ORM query results nowadays   What I need is just to return JSON or XML data representation of SQLAlchemy query result   SQLAlchemy objects query result in JSON XML format is needed to be used in javascript datagird  JQGrid http   www trirand com blog

User · Answer

step1  class CNAME            def as dict self          return  item name  getattr self  item name  for item in self   table   columns   step2  list      for data in session query CNAME  all        list append data as dict     step3  return jsonify list

User · Answer

The AlchemyEncoder is wonderful but sometimes fails with Decimal values  Here is an improved encoder that solves the decimal problem -   class AlchemyEncoder json JSONEncoder     To serialize SQLalchemy objects  def default self  obj       if isinstance obj   class    DeclarativeMeta           model fields              for field in  x for x in dir obj  if not x startswith      and x     metadata                data   obj   getattribute   field              print data             try                  json dumps data     this will fail on non-encodable values  like other classes                 model fields field    data             except TypeError                  model fields field    None         return model fields     if isinstance obj  Decimal           return float obj      return json JSONEncoder default self  obj

User · Answer

Even though it s a old post  Maybe I didn t answer the question above  but I want to talk about my serialization  at least it works for me  I use FastAPI SqlAlchemy and MySQL  but I don t use orm model    from sqlalchemy import create engine   from sqlalchemy orm import sessionmaker   engine   create engine config SQLALCHEMY DATABASE URL  pool pre ping True    SessionLocal   sessionmaker autocommit False  autoflush False  bind engine   Serialization code   import decimal import datetime   def alchemy encoder obj        quot  quot  quot JSON encoder function for SQLAlchemy special classes  quot  quot  quot      if isinstance obj  datetime date           return obj strftime  quot  Y- m- d  H  M  S quot       elif isinstance obj  decimal Decimal           return float obj   import json from sqlalchemy import text    db is SessionLocal   object   app sql    SELECT   FROM app info ORDER BY app id LIMIT  page  page size     The next two are the parameters passed in page   1 page size   10    execute sql and return a  lt class  sqlalchemy engine result ResultProxy  gt  object app list   db execute text app sql     page   page   page size   page size      serialize res   json loads json dumps  dict r  for r in app list   default alchemy encoder     If it doesn t work  please ignore my answer  I refer to it here https   codeandlife com 2014 12 07 sqlalchemy-results-to-json-the-easy-way

User · Answer

You can use introspection of SqlAlchemy as this     mysql   SQLAlchemy   from sqlalchemy import inspect  class Contacts mysql Model           tablename      CONTACTS      id   mysql Column mysql Integer  primary key True      first name   mysql Column mysql String 128   nullable False      last name   mysql Column mysql String 128   nullable False      phone   mysql Column mysql String 128   nullable False      email   mysql Column mysql String 128   nullable False      street   mysql Column mysql String 128   nullable False      zip code   mysql Column mysql String 128   nullable False      city   mysql Column mysql String 128   nullable False      def toDict self           return   c key  getattr self  c key  for c in inspect self  mapper column attrs     app route   contacts  methods   GET    def getContacts        contacts   Contacts query all       contactsArr          for contact in contacts          contactsArr append contact toDict         return jsonify contactsArr    app route   contacts  lt int id gt   methods   GET    def getContact id       contact   Contacts query get id      return jsonify contact toDict      Get inspired from an answer here        Convert sqlalchemy row object to python dict

User · Answer

I know this is quite an older post  I took solution given by  SashaB and modified as per my need    I added following things to it    Field ignore list  A list of fields to be ignored while serializing Field replace list  A dictionary containing field names to be replaced by values while serializing  Removed methods and BaseQuery getting serialized   My code is as follows   def alchemy json encoder revisit self   False  fields to expand       fields to ignore       fields to replace                  Serialize SQLAlchemy result into JSon     param revisit self  True   False     param fields to expand  Fields which are to be expanded for including their children and all     param fields to ignore  Fields to be ignored while encoding     param fields to replace  Field keys to be replaced by values assigned in dictionary     return  Json serialized SQLAlchemy object            visited objs         class AlchemyEncoder json JSONEncoder         def default self  obj           if isinstance obj   class    DeclarativeMeta                 don t re-visit self             if revisit self                  if obj in  visited objs                      return None                  visited objs append obj                 go through each field in this SQLalchemy class             fields                  for field in  x for x in dir obj  if not x startswith      and x     metadata  and x not in fields to ignore                   val   obj   getattribute   field                    is this field method defination  or an SQLalchemy object                 if not hasattr val     call     and not isinstance val  BaseQuery                       field name   fields to replace field  if field in fields to replace else field                       is this field another SQLalchemy object  or a list of SQLalchemy objects                      if isinstance val   class    DeclarativeMeta  or                                isinstance val  list  and len val   gt  0 and isinstance val 0    class    DeclarativeMeta                              unless we re expanding this field  stop here                         if field not in fields to expand                                not expanding this field  set it to None and continue                             fields field name    None                             continue                      fields field name    val               a json-encodable dict             return fields          return json JSONEncoder default self  obj     return AlchemyEncoder   Hope it helps someone

User · Answer

Custom serialization and deserialization    from json   class method  builds a Model object based on json data    deserialize  could be called only on instance  and merge all data from json into Model instance    serialize  - recursive serialization    write only   property is needed to define write only properties   password hash  for example    class Serializable object         exclude       id          include              write only              classmethod     def from json cls  json  selfObj None           if selfObj is None              self   cls           else              self   selfObj         exclude    cls   exclude   or       Serializable   exclude           include   cls   include   or            if json              for prop  value in json iteritems                      ignore all non user data  e g  only                 if  not  prop in exclude     prop in include   and isinstance                          getattr cls  prop  None   QueryableAttribute                       setattr self  prop  value          return self      def deserialize self  json           if not json              return None         return self   class   from json json  selfObj self        classmethod     def serialize list cls  object list              output              for li in object list              if isinstance li  Serializable                   output append li serialize                else                  output append li          return output      def serialize self    kwargs              init write only props         if len getattr self   class       write only             0              self   class     write only                dictionary              expand   kwargs get  expand       or            prop    props          if expand                expand all the fields             for key in expand                  getattr self  key          iterable   self   dict   items           is custom property set   False           include only properties passed as parameter         if  prop in kwargs  and  kwargs get prop  None  is not None               is custom property set   True             iterable   kwargs get prop  None            loop trough all accessible properties         for key in iterable              accessor   key             if isinstance key  tuple                   accessor   key 0              if not  accessor in self   class     write only    and not accessor startswith                         force select from db to be able get relationships                 if is custom property set                      getattr self  accessor  None                  if isinstance self   dict   get accessor   list                       dictionary accessor    self   class   serialize list object list self   dict   get accessor                     check if those properties are read only                 elif isinstance self   dict   get accessor   Serializable                       dictionary accessor    self   dict   get accessor  serialize                   else                      dictionary accessor    self   dict   get accessor          return dictionary

User · Answer

My take utilizing  too many   dictionaries   def serialize  query        d   dictionary written to per row      D   dictionary d is written to each time  then reset      Master   dictionary of dictionaries  the id Key  int  unique from database       from D is used as the Key for the dictionary D entry in Master     Master          D          x   0     for u in  query          d   u   dict           D              for n in d keys               if n      sa instance state                       D n    d n          x   d  id           Master x    D     return Master   Running with flask  including jsonify  and flask sqlalchemy to print outputs as JSON   Call the function with jsonify serialize      Works with all SQLAlchemy queries I ve tried so far  running SQLite3

User · Answer

A flat implementation  You could use something like this   from sqlalchemy ext declarative import DeclarativeMeta  class AlchemyEncoder json JSONEncoder        def default self  obj           if isinstance obj   class    DeclarativeMeta                 an SQLAlchemy class             fields                  for field in  x for x in dir obj  if not x startswith      and x     metadata                    data   obj   getattribute   field                  try                      json dumps data    this will fail on non-encodable values  like other classes                     fields field    data                 except TypeError                      fields field    None               a json-encodable dict             return fields          return json JSONEncoder default self  obj    and then convert to JSON using   c   YourAlchemyClass   print json dumps c  cls AlchemyEncoder    It will ignore fields that are not encodable  set them to  None     It doesn t auto-expand relations  since this could lead to self-references  and loop forever    A recursive  non-circular implementation  If  however  you d rather loop forever  you could use   from sqlalchemy ext declarative import DeclarativeMeta  def new alchemy encoder         visited objs           class AlchemyEncoder json JSONEncoder           def default self  obj               if isinstance obj   class    DeclarativeMeta                     don t re-visit self                 if obj in  visited objs                      return None                  visited objs append obj                     an SQLAlchemy class                 fields                      for field in  x for x in dir obj  if not x startswith      and x     metadata                        fields field    obj   getattribute   field                    a json-encodable dict                 return fields              return json JSONEncoder default self  obj       return AlchemyEncoder   And then encode objects using   print json dumps e  cls new alchemy encoder    check circular False    This would encode all children  and all their children  and all their children    Potentially encode your entire database  basically  When it reaches something its encoded before  it will encode it as  None    A recursive  possibly-circular  selective implementation  Another alternative  probably better  is to be able to specify the fields you want to expand   def new alchemy encoder revisit self   False  fields to expand             visited objs           class AlchemyEncoder json JSONEncoder           def default self  obj               if isinstance obj   class    DeclarativeMeta                     don t re-visit self                 if revisit self                      if obj in  visited objs                          return None                      visited objs append obj                     go through each field in this SQLalchemy class                 fields                      for field in  x for x in dir obj  if not x startswith      and x     metadata                        val   obj   getattribute   field                         is this field another SQLalchemy object  or a list of SQLalchemy objects                      if isinstance val   class    DeclarativeMeta  or  isinstance val  list  and len val   gt  0 and isinstance val 0    class    DeclarativeMeta                              unless we re expanding this field  stop here                         if field not in fields to expand                                not expanding this field  set it to None and continue                             fields field    None                             continue                      fields field    val                   a json-encodable dict                 return fields              return json JSONEncoder default self  obj       return AlchemyEncoder   You can now call it with   print json dumps e  cls new alchemy encoder False    parents     check circular False    To only expand SQLAlchemy fields called  parents   for example

User · Answer

Python 3 7  and Flask 1 1  can use the built-in dataclasses package  from dataclasses import dataclass from datetime import datetime from flask import Flask  jsonify from flask sqlalchemy import SQLAlchemy   app   Flask   name    db   SQLAlchemy app     dataclass class User db Model     id  int   email  str    id   db Column db Integer  primary key True  auto increment True    email   db Column db String 200   unique True     app route   users    def users      users   User query all     return jsonify users      if   name         main       users   User email  user1 gmail com    User email  user2 gmail com     db create all     db session add all users    db session commit     app run     The  users  route will now return a list of users         email    user1 gmail com    id   1       email    user2 gmail com    id   2      Auto-serialize related models   dataclass class Account db Model     id  int   users  User    id   db Column db Integer    users   db relationship User     User model would need a db ForeignKey field   The response from jsonify account  would be this           id  1      users                          email   user1 gmail com             id  1                              email   user2 gmail com             id  2                  Overwrite the default JSON Encoder  from flask json import JSONEncoder   class CustomJSONEncoder JSONEncoder      Add support for serializing timedeltas     def default o       if type o     datetime timedelta        return str o      elif type o     datetime datetime        return o isoformat       else        return super   default o   app json encoder   CustomJSONEncoder

User · Answer

While using some raw sql and undefined objects  using cursor description appeared to get what I was looking for   with connection cursor   as cur      print query      cur execute query      for item in cur fetchall            row    column name  item i  for i  column in enumerate cur description           print row

User · Answer

When using sqlalchemy to connect to a db I this is a simple solution which is highly configurable  Use pandas   import pandas as pd import sqlalchemy   sqlalchemy engine configuration engine   sqlalchemy create engine      def my function       read in from sql directly into a pandas dataframe    check the pandas documentation for additional config options   sql DF   pd read sql table  table name   con engine        orient  is optional here but allows you to specify the json formatting you require   sql json   sql DF to json orient  index      return sql json

User · Answer

def alc2json row       return dict   col  str getattr row col    for col in row   table   columns keys       I thought I d play a little code golf with this one    FYI  I am using automap base since we have a separately designed schema according to business requirements  I just started using SQLAlchemy today but the documentation states that automap base is an extension to declarative base which seems to be the typical paradigm in the SQLAlchemy ORM so I believe this should work   It does not get fancy with following foreign keys per Tjorriemorrie s solution  but it simply matches columns to values and handles Python types by str  -ing the column values  Our values consist Python datetime time and decimal Decimal class type results so it gets the job done   Hope this helps any passers-by

User · Answer

Flask-JsonTools package has an implementation of JsonSerializableBase Base class for your models   Usage   from sqlalchemy ext declarative import declarative base from flask ext jsontools import JsonSerializableBase  Base   declarative base cls  JsonSerializableBase     class User Base              Now the User model is magically serializable   If your framework is not Flask  you can just grab the code

User · Answer

A more detailed explanation   In your model  add    def as dict self          return  c name  str getattr self  c name   for c in self   table   columns    The str   is for python 3 so if using python 2 use unicode    It should help deserialize dates  You can remove it if not dealing with those   You can now query the database like this  some result   User query filter by id current user id  first   as dict     First   is needed to avoid weird errors  as dict   will now deserialize the result  After deserialization  it is ready to be turned to json  jsonify some result

User · Answer

I recommend using marshmallow  It allows you to create serializers to represent your model instances with support to relations and nested objects   Here is a truncated example from their docs  Take the ORM model  Author   class Author db Model       id   db Column db Integer  primary key True      first   db Column db String 80       last   db Column db String 80     A marshmallow schema for that class is constructed like this   class AuthorSchema Schema       id   fields Int dump only True      first   fields Str       last   fields Str       formatted name   fields Method  format name   dump only True       def format name self  author           return          format author last  author first       and used like this   author schema   AuthorSchema   author schema dump Author query first         would produce an output like this              first    Tim            formatted name    Peters  Tim            id   1           last    Peters      Have a look at their full Flask-SQLAlchemy Example   A library called marshmallow-sqlalchemy specifically integrates SQLAlchemy and marshmallow  In that library  the schema for the Author model described above looks like this   class AuthorSchema ModelSchema       class Meta          model   Author   The integration allows the field types to be inferred from the SQLAlchemy Column types   marshmallow-sqlalchemy here

User · Answer

You can convert a RowProxy to a dict like this    d   dict row items      Then serialize that to JSON   you will have to specify an encoder for things like datetime values   It s not that hard if you just want one record   and not a full hierarchy of related records     json dumps   dict row items     for row in rs

User · Answer

You could just output your object as a dictionary   class User     def as dict self          return  c name  getattr self  c name  for c in self   table   columns    And then you use User as dict   to serialize your object   As explained in Convert sqlalchemy row object to python dict

User · Answer

install simplejson by pip install simplejson and the create a class class Serialise object        def  asdict self            quot  quot  quot          Serialization logic for converting entities using flask s jsonify           return  An ordered dictionary          rtype   class  collections OrderedDict           quot  quot  quot           result   OrderedDict             Get the columns         for key in self   mapper   c keys                if isinstance getattr self  key   datetime                   result  quot x quot     getattr self  key  timestamp     1000                 result  quot timestamp quot     result  quot x quot               else                  result key    getattr self  key           return result  and inherit this class to every orm classes so that this  asdict function gets registered to every ORM class and boom  And use jsonify anywhere

User · Answer

The built in serializer chokes with utf-8 cannot decode invalid start byte for some inputs   Instead  I went with   def row to dict row       temp   row   dict       temp pop   sa instance state   None      return temp   def rows to list rows       ret rows          for row in rows          ret rows append row to dict row       return ret rows    website blueprint route   api v1 some endpoint   methods   GET    def some api                 some endpoint             rows   rows to list SomeModel query all        response   app response class          response jsonplus dumps rows           status 200          mimetype  application json            return response

User · Answer

Tiny tweak on Sasha B s really excellent answer  This specifically converts datetime objects to strings which in the original answer would be converted to None    Standard library imports from datetime import datetime import json    3rd party imports from sqlalchemy ext declarative import DeclarativeMeta  class JsonEncoder json JSONEncoder       def default self  obj           if isinstance obj   class    DeclarativeMeta               dict                     Remove invalid fields and just get the column attributes             columns    x for x in dir obj  if not x startswith  quot   quot   and x     quot metadata quot                for column in columns                  value   obj   getattribute   column                   try                      json dumps value                      dict column    value                 except TypeError                      if isinstance value  datetime                           dict column    value   str                         else                          dict column    None             return dict          return json JSONEncoder default self  obj

User · Answer

following code will serialize sqlalchemy result to json   import json from collections import OrderedDict   def asdict self       result   OrderedDict       for key in self   mapper   c keys            if getattr self  key  is not None              result key    str getattr self  key           else              result key    getattr self  key      return result   def to array all vendors       v     ven asdict   for ven in all vendors       return json dumps v     Calling fun   def all products        all products   Products query all       return to array all products

User · Answer

While the original question goes back awhile  the number of answers here  and my own experiences  suggest it s a non-trivial question with a lot of different approaches of varying complexity with different trade-offs   That s why I built the SQLAthanor library that extends SQLAlchemy s declarative ORM with configurable serialization de-serialization support that you might want to take a look at   The library supports    Python 2 7  3 4  3 5  and 3 6  SQLAlchemy versions 0 9 and higher serialization de-serialization to from JSON  CSV  YAML  and Python dict serialization de-serialization of columns attributes  relationships  hybrid properties  and association proxies enabling and disabling of serialization for particular formats and columns relationships attributes  e g  you want to support an inbound password value  but never include an outbound one  pre-serialization and post-deserialization value processing  for validation or type coercion  a pretty straightforward syntax that is both Pythonic and seamlessly consistent with SQLAlchemy s own approach   You can check out the  I hope   comprehensive docs here  https   sqlathanor readthedocs io en latest  Hope this helps

User · Answer

Under Flask  this works and handles datatime fields  transforming a field of type  time   datetime datetime 2018  3  22  15  40  into  time    2018-03-22 15 40 00    obj    c name  str getattr self  c name   for c in self   table   columns     This to get the JSON body return json dumps obj     Or this to get a response object return jsonify obj

User · Answer

Use the built-in serializer in SQLAlchemy   from sqlalchemy ext serializer import loads  dumps obj   MyAlchemyObject     serialize object serialized obj   dumps obj     deserialize object obj   loads serialized obj    If you re transferring the object between sessions  remember to detach the object from the current session using session expunge obj   To attach it again  just do session add obj

User · Answer

Maybe you can use a class like this  from sqlalchemy ext declarative import declared attr from sqlalchemy import Table   class Custom         Some custom logic here            table    Table    def for mypy       declared attr     def   tablename   cls      pylint  disable no-self-argument         return cls   name      pylint  disable  no-member      def to dict self  - gt  Dict str  Any              Serializes only column data             return  c name  getattr self  c name  for c in self   table   columns   Base   declarative base cls Custom   class MyOwnTable Base        COLUMNS    With that all objects have the to dict method

User · Answer

It is not so straighforward  I wrote some code to do this  I m still working on it  and it uses the MochiKit framework  It basically translates compound objects between Python and Javascript using a proxy and registered JSON converters    Browser side for database objects is db js It needs the basic Python proxy source in proxy js   On the Python side there is the base proxy module  Then finally the SqlAlchemy object encoder in webserver py  It also depends on metadata extractors found in the models py file

User · Answer

Here is a solution that lets you select the relations you want to include in your output as deep as you would like to go  NOTE  This is a complete re-write taking a dict str as an arg rather than a list  fixes some stuff    def deep dict self  relations             Output a dict of an SA object recursing as deep as you want       Takes one argument  relations which is a dictionary of relations we d     like to pull out  The relations dict items can be a single relation     name or deeper relation names connected by sub dicts      Example          Say we have a Person object with a family relationship             person deep dict relations   family  None           Say the family object has homes as a relation then we can do             person deep dict relations   family    homes  None                OR             person deep dict relations   family   homes            Say homes has a relation like rooms you can do             person deep dict relations   family    homes   rooms                 and so on                mydict    dict  c  str a   for c  a in                     self   dict   items   if c      sa instance state       if not relations            just return ourselves         return mydict        otherwise we need to go deeper     if not isinstance relations  dict  and not isinstance relations  str           raise Exception  relations should be a dict  it is of type     format type relations           got here so check and handle if we were passed a dict     if isinstance relations  dict             we were passed deeper info         for left  right in relations items                myrel   getattr self  left              if isinstance myrel  list                   mydict left     rel deep dict relations right  for rel in myrel              else                  mydict left    myrel deep dict relations right        if we get here check and handle if we were passed a string     elif isinstance relations  str             passed a single item         myrel   getattr self  relations          left   relations         if isinstance myrel  list               mydict left     rel deep dict relations None                                   for rel in myrel          else              mydict left    myrel deep dict relations None       return mydict   so for an example using person family homes rooms    turning it into json all you need is  json dumps person deep dict relations   family    homes   rooms

User · Answer

For security reasons you should never return all the model s fields  I prefer to selectively choose them   Flask s json encoding now supports UUID  datetime and relationships  and added query and query class for flask sqlalchemy db Model class   I ve updated the encoder as follows      app json encoder py       from sqlalchemy ext declarative import DeclarativeMeta     from flask import json       class AlchemyEncoder json JSONEncoder           def default self  o               if isinstance o   class    DeclarativeMeta                   data                      fields   o   json     if hasattr o     json     else dir o                  for field in  f for f in fields if not f startswith      and f not in   metadata    query    query class                         value   o   getattribute   field                      try                          json dumps value                          data field    value                     except TypeError                          data field    None                 return data             return json JSONEncoder default self  o       app   init   py     json encoding from app json encoder import AlchemyEncoder app json encoder   AlchemyEncoder   With this I can optionally add a   json   property that returns the list of fields I wish to encode      app models py   class Queue db Model       id   db Column db Integer  primary key True      song id   db Column db Integer  db ForeignKey  song id    unique True  nullable False      song   db relationship  Song   lazy  joined       type   db Column db String 20   server default u audio mpeg       src   db Column db String 255   nullable False      created at   db Column db DateTime  server default db func now        updated at   db Column db DateTime  server default db func now    onupdate db func now         def   init   self  song           self song   song         self src   song full path      def   json   self           return   song    src    type    created at     I add  jsonapi to my view  return the resultlist and then my output is as follows              created at    Thu  23 Jul 2015 11 36 53 GMT        song                            full path     static music Audioslave Audioslave  2002  1 Cochise mp3                id   2               path name    Audioslave Audioslave  2002  1 Cochise mp3                  src     static music Audioslave Audioslave  2002  1 Cochise mp3        type    audio mpeg

[python] How to serialize SqlAlchemy result to JSON?

Examples related to python

Examples related to json

Examples related to sqlalchemy