Eager load reduces the range of queries. SQLAlchemy offers keen loading functions invoked thru question alternatives which give extra commands to the Query. These options determine how to load numerous attributes through the Query.Options() approach.
Subquery Load
We need that Customer.Invoices have to load eagerly. The orm.Subqueryload() alternative gives a second SELECT announcement that absolutely masses the collections associated with the consequences simply loaded. The call “subquery” reasons the SELECT assertion to be built at once via the Query re-used and embedded as a subquery into a SELECT towards the associated table.
from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()
This results inside 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 statistics from 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 application is as follows −
Govind Pant Gulmandi Aurangabad gpant@gmail.com
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000
Joined Load
The different function is referred to as orm.Joinedload(). This emits a LEFT OUTER JOIN. Lead object in addition to the associated item 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 led to two rows, but it gives one instance of Customer again. This is due to the fact Query applies a “uniquing” method, based on item identity, to the again entities. Joined keen loading may be carried out with out affecting the query results.
The subqueryload() is greater appropriate for loading related collections at the same time as joinedload() is higher suitable for plenty-to-one courting.