我可以让 SQLAlchemy 填充基于当前外键值的关系吗? [英] Can I get SQLAlchemy to populate a relationship based on the current foreign key values?
问题描述
这是一些代码:
# 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.
"
session.add(bob)
print bob.parent, ": In session. Should be Fred but is None.
"
session.commit()
print bob.parent, ": Committed. Is Fred.
"
bob.parent_id = 2
print bob.parent, ": Dirty. Should be George but is Fred.
"
session.add(bob)
print bob.parent, ": Added to session. Should be George but is Fred.
"
session.expire(bob,['parent'])
print bob.parent, ": Expired. Should be George but is None? Wtf?
"
session.commit()
print bob.parent, ": Committed again. Is None. Ugh.
"
if __name__ == '__main__':
run()
这个例子表明，简单地设置关系所依赖的外键字段永远不足以使该关系查询正确.无论我做什么，这几乎都会发生.
This example demonstrates that simply setting the foreign key fields that a relationship depends on is never enough to make that relationship query for the right thing. This happens pretty much no matter what I do.
是否可以让 sqlalchemy 根据当前外键值填充关系，而无需先保留记录?我可以做些什么让它运行查询吗?
Is it possible to get sqlalchemy to populate the relationship based on the current foreign key values, without persisting the record first? Can I do something to make it run the query?
这个问题在处理网页表单时经常出现.表单帖子只包含事物的 ID，因此处理帖子的最简单方法是在您的记录中设置 ID 字段并尝试提交它，如果引用的项目不存在，或者如果有一些，则让事务失败其他只有数据库才能真正知道而不会冒竞争条件风险的问题，例如唯一约束违规.一旦事务失败，您可能希望向用户重新显示表单.不幸的是，这些关系都不再正确了.
This problem comes up a lot when dealing with web forms. Form posts just contain the IDs of things, so the simplest way to handle the post is to set the ID fields in your record and attempt to commit it, and let the transaction fail if the referenced items do not exist, or if there is some other problem that only the database can really know about without risking race conditions, such as a unique constraint violation. Once the transaction fails, you may want to re-display the form to the user. Unfortunately, none of the relationships are correct anymore.
这可能是也可能不是问题，但就我而言，这非常令人沮丧.为了更正这些关系，我需要复制这些关系中的逻辑，因为我找不到告诉他们只执行查询的方法.
This may or may not be a problem, but in my case it is pretty frustrating. In order to correct the relationships, I need to duplicate the logic in those relationships, as I can't find a way to tell them to just do the query.
推荐答案
您的添加"调用错误:
your "add" call is wrong:
session.add_all([fred, george])
对于甚至不在会话中的完全瞬态对象(顺便说一句，我不同意这种用例)，请使用 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.
"
对于要加载其关系的待处理对象(也是我不同意的用例，请参阅 我将实例上的foo_id"属性设置为7"，但foo"属性仍然为None——它不应该加载带有id#7的Foo?)使用 load_on_pending 标志:
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)
当您将 'parent_id' 更改为某些内容时，要重新加载 'parent'，如 FAQ 条目所述，请使用 expire:
to reload 'parent' when you've changed 'parent_id' to something, as the FAQ entry discusses, use expire:
session.expire(bob, ['parent'])
bob.parent_id = 2
print bob.parent, ": Dirty. Should be George but is Fred.
"
脚本完全运行:
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.
"
session.add(bob)
print bob.parent, ": In session. Should be Fred but is None.
"
session.commit()
print bob.parent, ": Committed. Is Fred.
"
session.expire(bob, ['parent'])
bob.parent_id = 2
print bob.parent, ": Dirty. Should be George but is Fred.
"
session.add(bob)
print bob.parent, ": Added to session. Should be George but is Fred.
"
session.expire(bob,['parent'])
print bob.parent, ": Expired. Should be George but is None? Wtf?
"
session.commit()
print bob.parent, ": Committed again. Is None. Ugh.
"
if __name__ == '__main__':
run()
这篇关于我可以让 SQLAlchemy 填充基于当前外键值的关系吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
