In SQLAlchemy should I always pair contains_eager with expire_all

Viewed 79

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
0 Answers
Related