SQLAlchemy - Update ForeignKey when setting the relationship

Question

I have a class:

class ExampleClass(Base):
    __tablename__ = 'chart'
    id = Column(Integer, primary_key=True)
    element_id = Column(Integer, ForeignKey('anotherTable.id'))
    element = relationship(AnotherClass)
    element2_id = Column(Integer, ForeignKey('anotherTable2.id'))
    element2 = relationship(AnotherClass2)

I want to do a lookup based on the element_id and element2_id :

class ExampleClass(Base):
    ...
    def get_with_element2(self, element2):
        return session.query(ExampleClass).\
                       filter_by(element_id = self.element_id,
                                 element2_id = element2.id).first()

The problem I find is that if I instantiate a new ExampleClass object and assign it an element, the element_id field is not being set:

a = ExampleClass(element=element_obj)
a.element_id => None

How can I solve this? What's the best way to deal with this kind of situation?

Answer 1

First off, all the examples below assume that your ExampleClass instance is at least in the pending state if not the "persistent" state (that is, session.add(a)). In other words, if you aren't yet interacting with a Session and have not added the ExampleClass object to one, then you won't get any of the database-level behavior of relationship(), of which maintaining foreign key column values is the primary feature. You are of course free to make this assignment directly:

a = ExampleClass(element_id=element_obj.id)

but this is obviously not making use of the automation provided by the relationship() construct.

The assignment of foreign key attributes by relationship() occurs during a flush, which is a process that only occurs when interaction with the database is necessary, such as before you emit a SQL statement using session.query() or before you complete your transaction using session.commit().

Generally, the philosophy of relationship() is that you'd deal only with the "element" and "element2" attributes here, and let the foreign key attributes be handled behind the scenes. You can write your query like this:

session.query(ExampleClass).\
     filter_by(element=self.element).\
     filter_by(element2=element2)

The ORM will take a comparison such as SomeClass.somerelationship=someobject and convert that into the foreign-key expression SomeClass.some_fk=some_id, but the difference is, the evaluation of the ultimate value of "some_id" is deferred until the right before the query is executed. Before the query is executed, the Query() object tells the Session to "autoflush", which will have the effect of your ExampleClass row being inserted along with the primary key identifier of element_obj being assigned to the element_id attribute on the ExampleClass object.

you could get a similar effect while still using the FK attributes like this, this is mostly just to understand how it works though:

session.query(ExampleClass).\
   filter_by(element_id=bindparam(callable_=lambda: self.element_id)).\
   filter_by(element2_id=element2.id)

or even more explicit, do the flush first:

session.flush()
session.query(ExampleClass).\
   filter_by(element_id=self.element_id).\
   filter_by(element2_id=element2.id)

So to the degree you'd want to refer to foreign-key attributes like element_id explicitly, you'd also need to do the things relationship() does for you explicitly, as well. If you deal strictly with object instances and the relationship()-bound attribute, and leave typical defaults like autoflush enabled, it will generally do the "right thing" and make sure attributes are ready when needed.