SQLAlchemy - Format Results as a List of Dictionaries

Viewed 347

How can a SQLAlchemy object with relationships eagerly loaded be formatted as a list of dicts or as a json string?

Attempted using obj.__dict__ but the loaded relationships are showing up as their __repr__.

How can we solve this without manually hardcoding the mapping from SQLAlchemy object to the desired list of dicts (eg: marshmallow schemas)?


Actual Result

For example, the owner column is joined from a second table but is not converted to dict.

[{'name': 'Brad', 'owner_id': 1, 'id': 1, 'owner': <main.Owner object at 0x10dc9ec90>}]

Expected Result

[{'name': 'Brad', 'owner_id': 1, 'id': 1, 'owner': {'id': 1, 'name': 'Jennifer'}}]

Ideal Results

Without owner_id

[{'name': 'Brad', 'id': 1, 'owner': {'id': 1, 'name': 'Jennifer'}}]


Working code to reproduce problem

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, joinedload

# Models
Base = declarative_base()

class Animal(Base):
    __tablename__ = 'animals'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    owner_id = Column(Integer, ForeignKey('owners.id'))
    owner = relationship('Owner')

    def __init__(self, name, owner):
        self.name = name
        self.owner = owner

class Owner(Base):
    __tablename__ = 'owners'
    id = Column(Integer, primary_key=True)
    name = Column(Text)

    def __init__(self, name):
        self.name = name

# Create engine
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()

# Create tables
Base.metadata.create_all(engine) 

# Populate tables
owner1 = Owner("Jennifer")
animal1 = Animal('Brad', owner1)
session.add(owner1)
session.add(animal1)
session.commit()

# Query database and print results as a list of dicts
results = []
for a in session.query(Animal).options(joinedload('*')).all():
    d = a.__dict__
    d.pop('_sa_instance_state', None)
    results.append(d)
print(results)
# [{'name': 'Brad', 'owner_id': 1, 'id': 1, 'owner': <__main__.Owner object at 0x10dc9ec90>}]
0 Answers
Related