[Python] Getting results from a Many to many relationship with SQLAlchemy

I have the following classes in models.py. I have a many to many relationship between Player and RSlevel table through the RSDetail table. Registration works well.

RsDetail = Table('RsDetail', Base.metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('playerId', Integer, ForeignKey('players.id')),
Column('rslevelId', Integer, ForeignKey('rslevel.id')),
)


class Player(Base):
__tablename__ = "players"

# Columns for its data
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(60), nullable=False)
discord_id: Mapped[int] = mapped_column(Integer, unique=True, nullable=False)
guild_id: Mapped[int] = mapped_column(Integer, ForeignKey("guilds.id"), nullable=False)

# Relationships to other databases
guild: Mapped["Guild"] = relationship("Guild", back_populates="players")
rs: Mapped[list["Rslevel"]] = relationship(secondary=RsDetail, back_populates="players")

def __repr__(self):
return f"id: {self.id}, name: {self.name}"


class Rslevel(Base):
__tablename__ = "rslevel"

# Columns for its data
id: Mapped[int] = mapped_column(primary_key=True)
level: Mapped[int] = mapped_column(nullable=False)
discord_role: Mapped[int] = mapped_column(nullable=False, unique=True)
guild_id: Mapped[int] = mapped_column(Integer, ForeignKey("guilds.id"), nullable=True)
drs: Mapped[int] = mapped_column(nullable=False)

# Relationships to other databases
players: Mapped[list["Player"]] = relationship(secondary=RsDetail, back_populates="rs")
guild: Mapped["Guild"] = relationship("Guild", back_populates="rslevels")

def __repr__(self):
return f"id: {self.id}, level: {self.level}"


Now, I would like to get all the RSDetail records as well as details for the associated players and rslevel.

I cannot get it to work and I am a bit lost as to how to do it.

Here is my statement... It does not seem to get any player or rslevel data. Only the playerId and RslevelId from the RsDetail table.

stmt = select(RsDetail).join(Rslevel, RsDetail.c.rslevelId == Rslevel.id).join(Player, RsDetail.c.playerId == Player.id).join(Guild, Rslevel.guild_id == Guild.id).where(Guild.discord_id == guild_id)
rs_list = session.execute(stmt).all()


I would like to access the results as something like:

for rs in rs_list:
print(rs.player.name, rs.rslevel.level)

Continue reading...