我正在使用SQLAlchemy的contains_eager
功能,当对象已经加载到现有会话中时,我看到了奇怪的行为。具体来说,这些对象似乎没有像加载新数据时那样从关系集合中筛选出来
这里是一个最小的例子。步骤是
value
的父级和两个子级李>contains_eager
为父级加载匹配的子级,执行联接的筛选查询。注意filter
应该排除两个孩子中的一个李>children
属性上李>session.expire_all()
可以获得正确的结果,这表明问题在于当前会话中已经存在子会话李>这是预期的行为吗?如果是这样,调用expire_all
是避免这种情况的正确方法吗
更一般地说,是否应该因此而避免contains_eager
?如果在发出查询之前必须跟踪子对象是否已经存在,这似乎是抽象的一个突破。但也许我错过了什么
from sqlalchemy import and_, Column, create_engine, DateTime, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import contains_eager, relationship, sessionmaker
create_statements = ["""
DROP TABLE IF EXISTS child;
""", """
DROP TABLE IF EXISTS parent;
""", """
CREATE TABLE parent
(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR
);
""", """
CREATE TABLE child
(
id INTEGER NOT NULL PRIMARY KEY,
parent_id INTEGER REFERENCES parent(id),
value INTEGER
);
"""
]
Base = declarative_base()
class Parent(Base):
__tablename__ = "parent"
__table_args__ = {'implicit_returning': False}
id = Column(Integer, primary_key=True)
name = Column(String)
children = relationship("Child", back_populates="parent")
class Child(Base):
__tablename__ = "child"
__table_args__ = {'implicit_returning': False}
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id))
value = Column(Integer)
parent = relationship(Parent, back_populates="children")
if __name__ == "__main__":
engine = create_engine(f"sqlite:///")
session = sessionmaker(bind=engine)()
for statement in create_statements:
session.execute(statement)
p1 = Parent(id=1, name="A")
c1 = Child(id=1, parent=p1, value=10)
c2 = Child(id=2, parent=p1, value=20)
session.add_all([p1, c1, c2])
session.flush()
# session.expire_all() # Uncommenting this makes the below work as expected.
results = session \
.query(Parent) \
.join(Child, Parent.id == Child.parent_id) \
.options(
contains_eager(Parent.children)
).filter(Child.value < 15) \
.order_by(Parent.id) \
.all()
print(len(results[0].children)) # We should only have 1 child.
print(all(c.value < 15 for c in results[0].children)) # All children should match the above filter condition.
Iasked this question在SQLAlchemy GitHub页面上。解决方案是对任何使用
contains_eager
和filter
的查询使用populate_existing
。在我的具体示例中,这个查询做了正确的事情相关问题 更多 >
编程相关推荐