Can I get SQLAlchemy to populate relationships based on current foreign key values?

Question

Here is the 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()

This example demonstrates that simply setting the foreign key fields that the relationship depends on is never enough to make this relationship request correct. This happens almost regardless of what I do.

Is it possible to get sqlalchemy to populate a relationship based on the current values ​​of a foreign key without saving the first record? Can I do something to run a query?

-. , - , , , , , , , . , . , .

, . , , .

Answer 1

• "":

  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.\n"
  

• ( , , . "foo_id" "7" , "foo" - . Foo № 7?) 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 -, FAQ, expire:

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

Script :

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()