python - SQLAlchemy:别名模型上查询 "field IS NULL"和 "IS NOT NULL"的不同输出

Question

在从别名模型查询字段时，我注意到一个非常奇怪的行为。似乎 SQLAlchemy 在针对 NOT NULL 条件过滤关系时打破了别名。这是一个例子:

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    children = relation('Child', back_populates='parent')

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parents.id'))
    parent = relation('Parent', back_populates='children', uselist=False)

aChild = aliased(Child)

print Session.query(aChild.id).filter(aChild.parent == None)
""" SELECT children_1.id AS children_1_id
    FROM children AS children_1
    WHERE children_1.parent_id IS NULL
"""

print Session.query(aChild.id).filter(aChild.parent != None)
""" SELECT children_1.id AS children_1_id
    FROM children AS children_1, children
    WHERE children.parent_id IS NOT NULL
"""

print Session.query(aChild.id).filter(~(aChild.parent == None))
""" SELECT children_1.id AS children_1_id
    FROM children AS children_1
    WHERE children_1.parent_id IS NOT NULL
"""

您可以看到第二个查询中有交叉联接，而第一个和第三个查询按预期工作。除了第三个例子之外，这个问题还有其他解释或解决方案吗？

Answer 1

您在 sqlalchemy 中发现了一个错误!我向 sqlalchemy 团队提交了一份错误报告，并找到了一个可能的修复方案。您可以查看状态here

错误修复现已到位，您可以从树顶部拉取来构建/修复您的 sqlalchemy 包。