How to avoid maximum recursion depth exceeded with Sqlalchemy when using DataClass approach

Question

I have sqlalchemy classes mapped as dataclass. It is very convenient to export data as json and later use it for flask. When exported to json, Child class will be auto exported as children list in Parent class and that is great. My problem is when I want to show parent object as field in Child class also. Then it goes to infinite recursion. My question is if there is some way to map columns to select when create the sqlalchemy realtionship, or is there any other way to customize data export from sqlalchemy-dataclass objects?

When using this code, the "maximum recursion depth exceeded" error is thrown:

@reg.mapped_as_dataclass
class Parent:
    __tablename__ = 'parent'

    Id: int = db.Column('id', db.Integer, primary_key=True)
    Name: str = db.Column('name', db.String)
    
    ChildrenList: 'Child' = db.relationship('Child', default_factory=list, back_populates='ParentObject')


@reg.mapped_as_dataclass
class Child:
    __tablename__ = 'child'

    Id: int = db.Column('id', db.Integer, primary_key=True)
    ParentId: int = db.Column('parent$id', db.Integer, ForeignKey('parent.id'))
    
    # This field create infinite recursion
    ParentObject: 'Parent' = db.relationship('Parent', back_populates='ChildrenList')
    
    # Can we create something like this to avoid recursion
    # ParentObject: 'Parent' = db.relationship('Parent', back_populates='ChildrenList', select_fields='Id, Name')
    
    # Or like this
    # ParentObject: 'Parent' = db.relationship('Parent', back_populates='ChildrenList', exclude_fields='ChildrenList')


def json_return_example()
    return db.session.query(Parent).all()

Answer 1

It isn't possible to define such behaviour within a relationship. You can:

• use a dedicated serialisation package that supports the desired behaviour, such as Marshmallow or Pydantic
• write your own serialisation function to handle the circular references
• define the relationship on one side only, for example a Parent has a children attribute but Child does not have a parent attribute.

This comment from SQLAlchemy's creator covers the same ground.

Answer 2

Like @snakecharmerb mentioned, you would need to make it only one side,

But I was able to get around it somewhat by doing something like this:

class Parent:
   __tablename__ = "parent"
   id: int = Column("id",primary_key=True)
   # Now you can still do patterns like: parent.children
   # without the max recursion problem
   @property
   def children(self):
       return db.session.query(Child).all()

class Child:
   __tablename__ = "child"
   id: int = ...
   parent_id: int = db.Column('parent.id', db.Integer, ForeignKey('parent.id'))
   # This field no longer creates infinite recursion
   parent: Parent = relationship('Parent')