sqlalchemy: cannot print objects of a table (infinite loop)

Question

From a large database, I generated the sqlalchemy classes using sqlacodegen. I can open a session but printing the rows of a table results in sort of infinite loop, sqlalchemy just hangs.

Here is a much reduced version of the classes to illustrate:

# some declarations here

@mapper_registry.mapped
@dataclass
class clsA:
    __tablename__ = 'clsa'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='clsa_pkey'),
    )
    __sa_dataclass_metadata_key__ = 'sa'

    id: int = field(init=False, metadata={'sa': Column(Integer)})

    to_clsb: List[clsB] = field(default_factory=list, metadata={'sa': relationship('clsB', back_populates='to_clsa')})

@mapper_registry.mapped
@dataclass
class clsB:
    __tablename__ = 'clsb'
    __table_args__ = (
        ForeignKeyConstraint(['clsc_id'], ['clsc.id'], name='clsb_fk_clsc'),
        ForeignKeyConstraint(['clsa_id'], ['clsa.id'], name='clsb_fk_clsa'),
        PrimaryKeyConstraint('id', name='clsb_pkey')
    )
    __sa_dataclass_metadata_key__ = 'sa'

    id: int = field(init=False, metadata={'sa': Column(Integer)})
    clsc_id: int = field(metadata={'sa': Column(Integer, nullable=False, index=True)})
    clsa_id: int = field(metadata={'sa': Column(Integer, nullable=False, index=True)})

    to_clsc: Optional[clsC] = field(default=None, metadata={'sa': relationship('clsC', back_populates='to_clsb')})
    to_clsa: Optional[clsA] = field(default=None, metadata={'sa': relationship('clsA', back_populates='to_clsb')})
    
@mapper_registry.mapped
@dataclass
class clsC:
    __tablename__ = 'clsc'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='clsc_pkey'),
    )
    __sa_dataclass_metadata_key__ = 'sa'

    id: int = field(init=False, metadata={'sa': Column(Integer)})

    to_clsb: List[clsB] = field(default_factory=list, metadata={'sa': relationship('clsB', back_populates='to_clsc')})

To test it I use the following

# some declarations here

engine = create_engine('postgresql+psycopg2://', connect_args=args)
Session = sessionmaker(bind=engine)
session = Session()
qs = session.query(clsC).all()

for q in qs:
    print(q)

When I reach the print(q), I do not get anything, and the program doesn't stop.

Curiously, it works if I remove the relationship between clsA and clsB.

Thanks for any hints.

sqlalchemy: cannot print objects of a table (infinite loop)

0 Answers0