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>}]