If I use the SQLAlchemy feature contains_eager after a previous query in the same session, the len() of the relationship persists from the previous query.
Am I misusing/misunderstanding SQLAlchemy some way or is this just a massive gotcha?
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, contains_eager
from sqlalchemy.schema import ForeignKey
Base = declarative_base()
class Foo(Base):
__tablename__ = "foo"
foo_id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.bar_id"))
class Bar(Base):
__tablename__ = "bar"
bar_id = Column(Integer, primary_key=True)
foos = relationship(Foo)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
bar = Bar(foos=[
Foo(foo_id=1),
Foo(foo_id=2),
Foo(foo_id=3),
])
session.add(bar)
session.commit()
bar = session.query(Bar).one()
assert len(bar.foos) == 3
##########################################################
# Either line required for the second assertion to pass #
##########################################################
# session.expunge_all()
# session.expire_all()
bar = (
session.query(Bar)
.join(Foo)
.options(contains_eager(Bar.foos))
.filter(Foo.foo_id < 3)
.one()
)
assert len(bar.foos) == 2