how to expunge foreign key record in sqlalchemy

Question

I am using sqlalchemy to manage my db. These are my tables

class Series(Base):
    __tablename__ = "Series"
    id = Column("Id", Integer, primary_key=True, autoincrement=True)
    series_name = Column("SeriesName", Unicode(50), nullable=False, unique=True)


class Season(Base):
    __tablename__ = "Seasons"
    id = Column("Id", Integer, primary_key=True, autoincrement=True)
    series_id = Column(
        "SeriesId", Integer, ForeignKey(f"{Series.__tablename__}.Id"), nullable=False
    )
    season_number = Column("SeasonNumber", Integer, nullable=False)

    series = relationship("Series", uselist=False, lazy='joined', innerjoin=True)
    __table_args__ = (UniqueConstraint("SeriesId", "SeasonNumber", name="UQ_SeriesId_SeasonNumber"),)

class Episode(Base):
    __tablename__ = "Episodes"
    id = Column("Id", Integer, primary_key=True, autoincrement=True)
    episode = Column("Episode", Integer, nullable=False)
    episode_name = Column("EpisodeName", Unicode, nullable=True)
    season_id = Column(
        "SeasonId", Integer, ForeignKey(f"{Season.__tablename__}.Id"), nullable=False
    )

    season = relationship("Season", uselist=False, lazy='joined', innerjoin=True)
    __table_args__ = (UniqueConstraint("SeasonId", "Episode", name="UQ_SeasonId_Episode"),)


class DownloadStatus(aenum.Enum):
    Start = "Start"
    Processing = "Processing"
    Downloading = "Downloading"
    Finish = "Finish"


class Request(Base):
    __tablename__ = "DownloadRequests"
    id = Column("Id", Integer, primary_key=True, autoincrement=True)
    status = Column("Status", Enum(DownloadStatus), nullable=False)
    episode_id = Column(
        "EpisodeId", Integer, ForeignKey(f"{Episode.__tablename__}.Id"), nullable=True
    )

    episode = relationship("Episode", uselist=False, lazy='joined', innerjoin=True)

In my code I need to find a specific request, after I end the session with the sql server I return the request, after the session ended I want to use the episode linked to this request with the foreign key however when I try to access the episode using request.episode I get a DetachedInstanceError how can I expunge the all the foreign keys record along with the the main record?

Answer 1

From your question, I assume that your code looks something like this:

first_request = session.query(Request).first()

session.commit()
session.close()

first_request.episode

While the last line raises an DetachedInstanceError with a description of Episode being not bounded to your object first_request.

I have reproduced some situations in my repository, look at https://github.com/Mizaro/stackoverflow/blob/master/60815309/models.py. I have shown there how expunging different objects.

The conclusion is that if you want to expunge everything under your query, the easiest solution is to use expunge_all.

Like this code:

first_request = session.query(Request).first()
session.expunge_all()

session.commit()
session.close()

first_request.episode  # Will work.

Hope it has answered your question.

Good Luck :)