Can I get SQLAlchemy to populate a relationship based on the current foreign key values?

Question

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

Answer 1

1. your "add" call is wrong:

    session.add_all([fred, george])
   

2. for a totally transient object that isn't even in a Session (which btw is not a use case I agree with), use 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. For a pending object to load its relationships (also a use case I disagree with, see 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?) use the 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. 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.\n"
   

Script working fully:

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