2

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)

Jossy
  • 589
  • 2
  • 12
  • 36

1 Answers1

1

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)
steve
  • 2,488
  • 5
  • 26
  • 39
  • Thanks Steve. Think I understand. So I can only access a "TournamentMaster" record only once I have instantiated a "Tournament" record? Something like this: `session.query(Tournament).filter_by(id_=1).one().tournament_master.id_`? – Jossy Dec 31 '20 at 11:12
  • That's definitely one of several ways to do it. Basically your Tournament ORM has no column (or entity) tournament_master. There is a tournament_master 'relationship' defined but this isn't a column, so you can't pass this relationship directly into the session.query method like you were trying. However b/c you defined the relationship, SQLAlchemy can infer how Tournament and Tournament master are related and thus how they can be JOINed. If you let me know what results you want from the query I may be able to assist further. – steve Dec 31 '20 at 22:38
  • 1
    Thanks Steve. I wasn't looking so much for a specific result but as a way of shortening some of the queries, i.e. not having to write out a `session.join()` statement. Anyway you've definitely answered my question so bounty duly awarded :) – Jossy Jan 01 '21 at 10:46