title: Can I get SQLAlchemy to populate a relationship based on the current foreign key values?
body: <p>Here's some code: </p>
  
  <pre><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.\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()
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>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.</p>