Eager load reduces the number of queries. SQLAlchemy offers eager loading functions invoked via query options which give additional instructions to the Query. These options determine how to load various attributes via the Query.options() method.
We want that Customers.invoices should load eagerly. The orm.subqueryload() option gives a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” causes the SELECT statement to be constructed directly via the Query re-used and embedded as a subquery into a SELECT against the related table.
This emits following expression giving same output as above:
The other function is called orm.joinedload(). This emits a LEFT OUTER JOIN. Lead object as well as the related object or collection is loaded in one step:
The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship.
Deleting Related Objects
It is easy to perform delete operation on a single table. All you have to do is to delete an object of the mapped class from a session and commit the action. However, delete operation on multiple related tables is little tricky. In our testdb database, Customers and Invoice classes are mapped to customer and invoice table with one to many type of relationship. We will try to delete Customers object and see the result.
As a quick reference, below are the definitions of Customers and Invoice classes:
In our sample table, x.name happens to be 'Gopal Krishna'. Let us delete this x from the session and count the occurrence of this name:
However, the related Invoice objects of x are still there. It can be verified by the following:
Here, 10 and 14 are invoice numbers belonging to customer Gopal Krishna. Result of the above query is 2, which means the related objects have not been deleted. This is because SQLAlchemy doesn’t assume the deletion of cascade; we have to give a command to delete it.
To change the behavior, we configure cascade options on the Customers.invoices relationship. Let us close the ongoing session, use new declarative_base() and redeclare the User class, adding in the addresses relationship including the cascade configuration.
The cascade attribute in relationship function is a comma-separated list of cascade rules which determines how Session operations should be “cascaded” from parent to child. By default, it is False, which means that it is "save-update, merge". The available cascades are as follows
Often used option is "all, delete-orphan" to indicate that related objects should follow along with the parent object in all cases, and be deleted when de-associated.
Hence redeclared Customer class is shown below:
* Prev - SQLAlchemy ORM - Working with Related Objects, Joins and common operators
* Next - SQLAlchemy ORM - Many to Many Relationships
* SQLAlchemy Doc - Query API
* SQLAlchemy Doc - Relationship Loading Techniques