Just like many other people I run into the problem of an instance not being bound to a Session. I have read the SQLAlchemy docs and the top-10 questions here on SO. Unfortunatly I have not found an explanation or solution to my error.

My guess is that the commit() closes the session rendering the object unbound. Does this mean that In need to create two objects, one to use with SQL Alchemy and one to use with the rest of my code?

I create an object something like this:

class Mod(Base):
    __tablename__ = 'mod'
    insert_timestamp = Column(DateTime, default=datetime.datetime.now())
    name = Column(String, nullable=False)

Then I add it to the database using this function and afterwards the object is usesless, I cannot do anything with it anymore, always getting the error that it is not bound to a session. I have tried to keep the session open, keep it closed, copy the object, open two sessions, return the object, return a copy of the object.

def add(self, dataobjects: list[Base]) -> bool:
    s = self.sessionmaker()
    try:
        s.add_all(dataobjects)
    except TypeError:
        s.rollback()
        raise
    else:
        s.commit()
        return True

This is my session setup:

    self.engine = create_engine(f"sqlite:///{self.config.database['file']}")
    self.sessionmaker = sessionmaker(bind=self.engine)
    Base.metadata.bind = self.engine

My last resort would be to create every object twice, once for SQL Alchemy and once so I can actually use the object in my code. This defeats the purpose of SQL Alchemy for me.

CodePudding user response：

ORM entities are expired when committed. By default, if an entity attribute is accessed after expiry, the session emits a SELECT to get the current value from the database. In this case, the session only exists in the scope of the add method, so subsequent attribute access raises the "not bound to a session" error.

There are a few ways to approach this:

1. Pass expire _on_commit=False when creating the session. This will prevent automatic expiry so attribute values will remain accessible after leaving the add function, but they may be stale.
2. Create the session outside of the add function and pass it as an argument (or return it from add, though that's rather ugly). As long as the session is not garbage collected the entities remain bound to it.
3. Create a new session and do mod = session.merge(mod) for each entity to bind it to the new session.

Which option you choose depends on your application.