sqlalchemy when does an object become "not persistent"

Question

I have a function that has a semi-long running session that I use for a bunch of database rows... and at a certain point I want to reload or "refresh" one of the rows to make sure none of the state has changed. most of the time this code works fine, but every now and then I get this error

sqlalchemy.exc.InvalidRequestError: Instance '<Event at 0x58cb790>' is not persistent within this Session

I've been reading up on state but cannot understand why an object would stop being persistent? I'm still within a session, so I'm not sure why I would stop being persistent.

Can someone explain what could cause my object to be "not persistent" within the session? I'm not doing any writing to the object prior to this point.

db_event below is the object that is becoming "not persistent"

async def event_white_check_mark_handler(
    self: Events, ctx, channel: TextChannel, member: discord.Member, message: Message
):
    """
    This reaction is for completing an event
    """
    session = database_objects.SESSION()
    try:
        message_id = message.id
        db_event = self.get_event(session, message_id)
        if not db_event:
            return
        logger.debug(f"{member.display_name} wants to complete an event {db_event.id}")
        db_guild = await db.get_or_create(
            session, db.Guild, name=channel.guild.name, discord_id=channel.guild.id
        )
        db_member = await db.get_or_create(
            session,
            db.Member,
            name=member.name,
            discord_id=member.id,
            nick=member.display_name,
            guild_id=db_guild.discord_id,
        )
        db_scheduler_config: db.SchedulerConfig = (
            session.query(db.SchedulerConfig)
            .filter(db.SchedulerConfig.guild_id == channel.guild.id)
            .one()
        )
        # reasons to not complete the event
        if len(db_event) == 0:
            await channel.send(
                f"{member.display_name} you cannot complete an event with no one on it!"
            )
        elif (
            db_member.discord_id == db_event.creator_id
            or await db_scheduler_config.check_permission(
                ctx, db_event.event_name, member, db_scheduler_config.MODIFY
            )
        ):
            async with self.EVENT_LOCKS[db_event.id]:
                session.refresh(db_event)                ###########  <---- right here is when I get the error thrown
                db_event.status = const.COMPLETED
                session.commit()
                self.DIRTY_EVENTS.add(db_event.id)

            member_list = ",".join(
                filter(
                    lambda x: x not in const.MEMBER_FIELD_DEFAULT,
                    [str(x.mention) for x in db_event.members],
                )
            )
            await channel.send(f"Congrats on completing a event {member_list}!")
            logger.info(f"Congrats on completing a event {member_list}!")
            # await self.stop_tracking_event(db_event)
            del self.REMINDERS_BY_EVENT_ID[db_event.id]

        else:
            await channel.send(
                f"{member.display_name} you did not create this event and do not have permission to delete the event!"
            )
            logger.warning(f"{member.display_name} you did not create this event!")
    except Exception as _e:
        logger.error(format_exc())
        session.rollback()
    finally:
        database_objects.SESSION.remove()

Answer 1

I experienced this issue when retrieving a session from a generator, and try to run the exact same query again from different yielded sessions:

SessionLocal = sessionmaker(bind=engine, class_=Session)
    
def get_session() -> Generator:
    with SessionLocal() as session:
        yield session

The solution was to use session directly (in my case). Perhaps in your case I would commit the session, before executing a new query.

def get_data():
    with Session(engine) as session:
        statement = select(Company)
        results = session.exec(statement)