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?

Accepted Answer

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.

SQLAlchemy - Update ForeignKey when setting the relationship

Solution

Problem