我可以让 SQLAlchemy 根据当前外键值填充关系吗？

这是一些代码：

# latest version at https://gist.github.com/nickretallack/11059102

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)

    def __str__(self):
        return self.name

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey(Parent.id), nullable=False)
    name = Column(String, nullable=False)

    parent = relationship(Parent)

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)

def run():
    # Basic Setup
    Base.metadata.create_all(engine)
    session = Session()
    fred = Parent(name="Fred", id=1)
    george = Parent(name="George", id=2)
    session.add(fred, george)
    session.commit()

    # The test
    bob = Child(name="Bob", parent_id=1)
    print bob.parent, ": Out of session. Should be Fred but is None.\n"

    session.add(bob)
    print bob.parent, ": In session.  Should be Fred but is None.\n"

    session.commit()
    print bob.parent, ": Committed.  Is Fred.\n" 

    bob.parent_id = 2
    print bob.parent, ": Dirty.  Should be George but is Fred.\n"

    session.add(bob)
    print bob.parent, ": Added to session.  Should be George but is Fred.\n"

    session.expire(bob,['parent'])
    print bob.parent, ": Expired.  Should be George but is None?  Wtf?\n"

    session.commit()
    print bob.parent, ": Committed again.  Is None.  Ugh.\n"

if __name__ == '__main__':
    run()

此示例表明，仅设置关系所依赖的外键字段永远不足以使关系查询正确的内容。无论我做什么，这种情况都会发生。

是否可以让 sqlalchemy 根据当前外键值填充关系，而无需先保留记录？我可以做些什么来让它运行查询吗？

在处理 Web 表单时，这个问题经常出现。表单帖子只包含事物的 ID，因此处理帖子的最简单方法是在记录中设置 ID 字段并尝试提交它，如果引用的项目不存在，或者存在某些项目，则让事务失败只有数据库才能真正了解的其他问题，而不会冒竞争条件的风险，例如违反唯一约束。一旦事务失败，您可能希望向用户重新显示表单。不幸的是，这些关系都不再正确了。

这可能是问题，也可能不是问题，但就我而言，这非常令人沮丧。为了纠正这些关系，我需要复制这些关系中的逻辑，因为我找不到一种方法来告诉他们只进行查询。

1. 您的“添加”调用是错误的：

    session.add_all([fred, george])
   

2. 对于甚至不在会话中的完全瞬态对象（顺便说一句，这不是我同意的用例），请使用启用关系加载 https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading:

    # The test
    bob = Child(name="Bob", parent_id=1)
    session.enable_relationship_loading(bob)
    print bob.parent, ": Out of session. Should be Fred but is None.\n"
   

3. 对于挂起的对象加载其关系（也是我不同意的用例，请参阅我将实例上的“foo_id”属性设置为“7”，但“foo”属性仍然为 None - 它不应该加载 id #7 的 Foo 吗？ http://docs.sqlalchemy.org/en/rel_1_0/faq/sessions.html?highlight=foo_id#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7） 使用挂起时加载 https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship.params.load_on_pending flag:

    class Child(Base):
        __tablename__ = 'child'
        id = Column(Integer, primary_key=True)
        parent_id = Column(ForeignKey(Parent.id), nullable=False)
        name = Column(String, nullable=False)
   
        parent = relationship(Parent, load_on_pending=True)
   

4. 当您将“parent_id”更改为某些内容时，要重新加载“parent”（如常见问题解答条目所述），请使用过期：

    session.expire(bob, ['parent'])
    bob.parent_id = 2
    print bob.parent, ": Dirty.  Should be George but is Fred.\n"
   

脚本完全运行：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)

    def __str__(self):
        return self.name

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey(Parent.id), nullable=False)
    name = Column(String, nullable=False)

    parent = relationship(Parent, load_on_pending=True)

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)

def run():
    # Basic Setup
    Base.metadata.create_all(engine)
    session = Session()
    fred = Parent(name="Fred", id=1)
    george = Parent(name="George", id=2)
    session.add_all([fred, george])
    session.commit()

    # The test
    bob = Child(name="Bob", parent_id=1)
    session.enable_relationship_loading(bob)
    print bob.parent, ": Out of session. Should be Fred but is None.\n"

    session.add(bob)
    print bob.parent, ": In session.  Should be Fred but is None.\n"

    session.commit()
    print bob.parent, ": Committed.  Is Fred.\n"

    session.expire(bob, ['parent'])
    bob.parent_id = 2
    print bob.parent, ": Dirty.  Should be George but is Fred.\n"

    session.add(bob)
    print bob.parent, ": Added to session.  Should be George but is Fred.\n"

    session.expire(bob,['parent'])
    print bob.parent, ": Expired.  Should be George but is None?  Wtf?\n"

    session.commit()
    print bob.parent, ": Committed again.  Is None.  Ugh.\n"

if __name__ == '__main__':
    run()