Why are not ForeignKey and RelationshipProperty attributes in SQLAlchemy automatically synchronized?

Question

I am trying to figure out the way SQLAlchemy is handling foreign keys.

Consider these models:

class ModelOne(Base):
   __tablename__ = 'model_one'

   id = Column(Integer, primary_key=True)
   ...

class ModelTwo(Base):
   __tablename__ = 'model_two'

   id = Column(Integer, primary_key=True)
   one_id = Column(Integer, ForeignKey('model_one.id'))
   one = relationship('ModelOne')

I assumed that if we have two models like this, then SQLAlchemy will figure out that ModelTwo.one_id and ModelTwo.one are referring to the same database column.

When creating ModelTwo records, we can use either numerical ID of the ModelOne records, or ModelOne instances to fill the FK field:

one = DBSession.query(ModelOne)....
two_a = ModelTwo(one_id=one.id)
two_b = ModelTwo(one=one)

This works.

But here's a twist: immediately after instantiating the ModelTwo instances, depending on how we initialize the FK field, one or the other representation of it will remain unpopulated:

two_a.one is None # evaluates to True
two_b.one_id is None # evaluates to True

Is this an expected behaviour? If so, this has to be a huge red flag in the head of a developer. When we retrieve a record from DB as an instance of a declarative model class, both record.one and record.one_id will be populated, but when we are creating a new record, that is not the case!

Answer 1

That is the standard behavior. SQLAlchemy will not reflect foreign key changes or relationship changes based on the other changing until commit. If you would like to set the property when the other is set, you can use the Attribute Set event.

You are usually encouraged to use the relationship and ignore the foreign key, as SQLAlchemy will perform a lot of nice "magic" in the background when setting relationships. If you would like foreign keys to perform this same magic, see the Expire Relationship on FK Change recipe.

This is just my opinion now, but this is not a "red flag". You should instead structure your program to avoid comparison between new or updated instances and persistent instances.