SQLalchemy custom __repr__ different behaviour

Question

I have some SQLalchemy tables which are relational:

DBNetwork (parent) -> DBNode (child)

When I call the a specific DBNetwork instance the __repr__ function works as expected, only displaying the DBNetwork instance attributes. However, after I call its child object DBNode, the __repr__ function starts to show its childs' instance attributes as well:

net_b = session.scalar(sa.select(DBNetwork).where(DBNetwork.name == 'NET_B'))

print(net_b)
print(net_b.nodes[0])
print(net_b)

output looks like this:

DBNetwork: {'id': 2, 'name': 'NET_B', 'year': 2023, 'description': 'TBD', 'type': 'methane', 'date_time': datetime.datetime(2023, 11, 19, 13, 13, 22, 743904)}

DBNode: {'network_id': 2, 'name': 'CS1I', 'subsys': 'DEFAULT', 'x': -261.30000000000246, 'height': 0.0, 'id': 3, 'alias': '', 'type': 256, 'y': -288.80000000000007}

DBNetwork: {'id': 2, 'name': 'NET_B', 'year': 2023, 'description': 'TBD', 'type': 'methane', 'date_time': datetime.datetime(2023, 11, 19, 13, 13, 22, 743904), 'nodes': [DBNode: {'network_id': 2, 'name': 'CS1I', 'subsys': 'DEFAULT', 'x': -261.30000000000246, 'height': 0.0, 'id': 3, 'alias': '', 'type': 256, 'y': -288.80000000000007}, DBNode: {'network_id': 2, 'name': 'CS1O', 'subsys': 'DEFAULT', 'x': 184.29999999999438, 'height': 0.0, 'id': 4, 'alias': '', 'type': 256, 'y': -288.80000000000007}, DBNode: {'network_id': 2, 'name': 'N3', 'subsys': 'DEFAULT', 'x': 518.499999999992, 'height': 0.0, 'id': 5, 'alias': '', 'type': 256, 'y': -288.80000000000007}, DBNode: {'network_id': 2, 'name': 'END', 'subsys': 'DEFAULT', 'x': 518.5, 'height': 0.0, 'id': 6, 'alias': '', 'type': 256, 'y': -66.0}, DBNode: {'network_id': 2, 'name': 'START', 'subsys': 'DEFAULT', 'x': -595.5, 'height': 0.0, 'id': 7, 'alias': '', 'type': 128, 'y': -66.0}, DBNode: {'network_id': 2, 'name': 'VA1O', 'subsys': 'DEFAULT', 'x': -595.5, 'height': 0.0, 'id': 8, 'alias': '', 'type': 256, 'y': -288.80000000000007}]}

---

The original code defined as:

import sqlalchemy as sa
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session


class Base(DeclarativeBase):
    def __repr__(self):
        name = self.__class__.__name__
        dict_ = {k: v for k, v in self.__dict__.items()
                 if k != '_sa_instance_state'}
        return f'{name}: {dict_}'


class DBNetwork(Base):
    __tablename__ = 'networks'

    id: Mapped[int] = mapped_column(sa.Integer, primary_key=True)
    name: Mapped[str] = mapped_column(sa.String(60))
    ...
    nodes: Mapped[List['DBNode']] = relationship(back_populates='network')


class DBNode(Base):
    __tablename__ = 'nodes'

    id: Mapped[int] = mapped_column(sa.Integer, primary_key=True)
    network_id: Mapped[int] = mapped_column(sa.Integer, sa.ForeignKey(
        'simulation.networks.id'))
    name: Mapped[int] = mapped_column(sa.String(25))
    ...
    network: Mapped['DBNetwork'] = relationship(back_populates='nodes')

---

Somehow since I defined a new __repr__ for the Base, the return value changes. How can I make sure that the return value stays the same???

Answer 1

I found out that the relational elements of a table is not automaticly downloaded from the database, and if you leave the opened Session and try to call them via e.g.: net_b.nodes[0], it raises a sqlalchemy lazy load-error. They are downloaded only if you stay in the Session and call them via e.g.: net_b.nodes[0]. And then the __repr__ function changes.

To prevent that, I added an if-statement as follows:

from sqlalchemy.orm.collections import InstrumentedList

class Base(DeclarativeBase):
    def __repr__(self):
        name = self.__class__.__name__
        dict_ = {k: v for k, v in self.__dict__.items()
                 if k != '_sa_instance_state' and type(v) != InstrumentedList}
        return f'{name}: {dict_}'