SQLAlchemy - updating ForeignKey when setting up a 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 search based on 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 is that if I create a new ExampleClass object and assign element , the element_id field will not be set:

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

How can i solve this? What is the best way to handle this situation?

Answer 1

First, all of the examples below assume that your ExampleClass instance is at least in a pending state, if not a “permanent” state (that is, session.add(a) ). In other words, if you have not yet interacted with Session and added an ExampleClass object to one, then you will not get any relationship() database-level behavior from which external Key column values ​​are supported are the main function. Of course, you can complete this task for free:

 a = ExampleClass(element_id=element_obj.id) 

but this obviously does not use the automation provided by the relationship() construct.

The assignment of foreign key attributes to relationship() occurs during flush , which is a process that occurs only when it is necessary to interact with the database, for example, as before you send an SQL statement using session.query() or before you complete the transaction using session.commit() .

Typically, the philosophy of relationship() is that you only deal with the attributes "element" and "element2" here, and let the attributes of the foreign key be handled behind the scenes. You can write your request as follows:

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

ORM will make a comparison, such as SomeClass.somerelationship=someobject , and convert it to a foreign key expression SomeClass.some_fk=some_id , but the difference is that the evaluation of the final value of "some_id" is deferred until it is executed to the right of the request. Before the query is executed, the Query() object tells Session "autoflush" to be inserted into your ExampleClass string inserted with the primary key identifier element_obj assigned by element_id > on the ExampleClass object.

you can get a similar effect when using FK attributes like this, basically it’s just understanding how it works:

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

or even more explicitly, first flash:

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

So, to the extent that you would like to explicitly refer to foreign key attributes such as element_id , you will also need to do things that relationship() does explicitly for you. If you strictly deal with object instances and the relationship() -bound attribute, and leave typical defaults, such as autoflush enabled, it usually does the “right thing” and ensures that the attributes are ready if necessary.