Why am I unable to generate a query using relationships?

user_11277108 0
Viewed 253

I'm experimenting with relationship functionality within SQLAlchemy however I've not been able to crack it. The following is a simple MRE:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()


class Tournament(Base):

    __tablename__ = "tournament"
    __table_args__ = {"schema": "belgarath", "extend_existing": True}

    id_ = Column(Integer, primary_key=True)
    tournament_master_id = Column(Integer, ForeignKey("belgarath.tournament_master.id_"))

    tournament_master = relationship("TournamentMaster", back_populates="tournament")


class TournamentMaster(Base):

    __tablename__ = "tournament_master"
    __table_args__ = {"schema": "belgarath", "extend_existing": True}

    id_ = Column(Integer, primary_key=True)
    tour_id = Column(Integer, index=True)

    tournament = relationship("Tournament", back_populates="tournament_master")


engine = create_engine("mysql+mysqlconnector://root:root@localhost/")
Session = sessionmaker(bind=engine)
session = Session()

qry = session.query(Tournament.tournament_master.id_).limit(100)

I was hoping to be able to query the id_ field from the tournament_master table through a relationship specified in the tournament table. However I get the following error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Tournament.tournament_master has an attribute 'id_'

I've also tried replacing the two relationship lines with a single backref line in TournamentMaster:

tournament = relationship("Tournament", backref="tournament_master")

However I then get the error:

AttributeError: type object 'Tournament' has no attribute 'tournament_master'

Where am I going wrong?

(I'm using SQLAlchemy v1.3.18)

sqlalchemy mysql python
1 Answers

Your ORM classes look fine. It's the query that's incorrect.

In short you're getting that "InstrumentedAttribute" error because you are misusing the session.query method.

From the docs the session.query method takes as arguments, "SomeMappedClass" or "entities". You have 2 mapped classes defined, Tournament, and TournamentMaster. These "entities" are typically either your mapped classes (ORM objects) or a Column of these mapped classes.

However you are passing in Tournament.tournament_master.id_ which is not a "MappedClass" or a column and thus not an "entity" that session.query can consume.

Another way to look at it is that by calling Tournament.tournament_master.id_ you are trying to access a 'TournamentMaster' record (or instance) from the 'Tournament' class, which doesn't make sense.

It's not super clear to me what exactly you hoping to return from the query. In any case though here's a start.

Instead of

qry = session.query(Tournament.tournament_master.id_).limit(100)

try

qry = session.query(Tournament, TournamentMaster).join(TournamentMaster).limit(100)

This may also work (haven't tested) to only return the id_ field, if that is you intention

qry = session.query(Tournament, TournamentMaster).join(Tournament).with_entities(TournamentMaster.id_).limit(100)
user_14138260
Related