”;
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.
Subquery Load
We want that Customer.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.
from sqlalchemy.orm import subqueryload c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = ''Govind Pant'').one()
This results in the following two SQL expressions −
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.name = ? (''Govind Pant'',) SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount, anon_1.customers_id AS anon_1_customers_id FROM ( SELECT customers.id AS customers_id FROM customers WHERE customers.name = ?) AS anon_1 JOIN invoices ON anon_1.customers_id = invoices.custid ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479 INFO sqlalchemy.engine.base.Engine (''Govind Pant'',)
To access the data from two tables, we can use the below program −
print (c1.name, c1.address, c1.email) for x in c1.invoices: print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))
The output of the above program is as follows −
Govind Pant Gulmandi Aurangabad [email protected] Invoice no : 3, Amount : 10000 Invoice no : 4, Amount : 5000
Joined Load
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.
from sqlalchemy.orm import joinedload c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name=''Govind Pant'').one()
This emits following expression giving same output as above −
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email, invoices_1.id AS invoices_1_id, invoices_1.custid AS invoices_1_custid, invoices_1.invno AS invoices_1_invno, invoices_1.amount AS invoices_1_amount FROM customers LEFT OUTER JOIN invoices AS invoices_1 ON customers.id = invoices_1.custid WHERE customers.name = ? ORDER BY invoices_1.id (''Govind Pant'',)
The OUTER JOIN resulted in two rows, but it gives one instance of Customer back. This is because Query applies a “uniquing” strategy, based on object identity, to the returned entities. Joined eager loading can be applied without affecting the query results.
The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship.
”;