Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
336 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
  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.
    "
    
  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.
    "
    

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...