Here's some code:
# 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.
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?
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.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…