Home > Software > Converting SQLAlchemy objects to JSON

Converting SQLAlchemy objects to JSON

August 20th, 2013

(Originally on Zato Blog and re-posted to Planet Python)

[Update 2013/08/22] As suggested on Reddit – for SQLAlchemy 0.8+ you should consider using the runtime inspection API instead of accessing low-level details directly.

Being able to convert SQLAlchemy objects into JSON is a useful feature, particularly for the purpose of convenient logging or returning them directly to a JavaScript frontend.

A function to do it can be as simple as the one below (extracted from Zato’s source code):

# stdlib
from json import dumps
 
def to_json(model):
    """ Returns a JSON representation of an SQLAlchemy-backed object.
    """
    json = {}
    json['fields'] = {}
    json['pk'] = getattr(model, 'id')
 
    for col in model._sa_class_manager.mapper.mapped_table.columns:
        json['fields'][col.name] = getattr(model, col.name)
 
    return dumps([json])

The trick is to list all the columns using the model’s ._sa_class_manager.mapper.mapped_table.columns attribute and fetch them one by one.

Note that you can also use anyjson which is a useful package that picks the best JSON library available and provides a uniform interface to all of them.

Share
Comments are closed.