Dumping SQLAlchemy Models to JSON

DONG Yuxuan @ Apr 09, 2019 Asia/Shanghai

Develop a SQLAlchemy to JSON encoder.

Dumping an object to JSON is one of the most common tasks in programming. Instead of doing it manually, we prefer to build some kind of automatic process.

The object needs to be converted to a JSON-encodable dictionary. Python provides the dir function and the getattr function for this purpose. However, specific to SQLAlchemy models there’re some subtle problems. Only columns and relationships need to be dumped but dir gives more than the expected. A relationship is often duplicated with a foreign-key column thus we need to chose which one to dump and this choice should be parameterized. If we chose to dump relationships, they are usually not expected to be dumped directly but only the primary key of the remote side of the relationship because nested dumping may lead to an infinite loop, and if the relationship is of the to-many type it should be dumped to a list of primary keys. An SQLAlchemy-to-dict converter considering the above problems is written below. For simplicity, we assume the model has one and only one column as the primary key.

import json
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm.relationships import RelationshipProperty

def _get_primary_key(model):
	return getattr(model, model.__mapper__.primary_key[0].key)

def sqla2dict(model, foreign_keys=True, relationships=False, excludes=None):
	excludes = excludes or hasattr(model, "__excludes__") and model.__excludes__ or []
	ret = {}

	for prop in model.__mapper__.iterate_properties:
		key = prop.key
		if key in excludes:
		val = getattr(model, key)

		if isinstance(prop, ColumnProperty) \
			and (foreign_keys or not prop.columns[0].foreign_keys):

			try: # This will fail on non-encodable types like DateTime
			except TypeError:
				val = str(val)
			ret[key] = val

		elif isinstance(prop, RelationshipProperty) and relationships:
			if prop.uselist:
				ret[key] = [_get_primary_key(v) for v in val]
				ret[key] = _get_primary_key(val)
	return ret

The code is also recorded at Gist. The try-catch trick is from stackoverflow.com and the original code just ignores non-encodable attributes but I think stringifying them is a better idea.