[ Python 文章收集 ] SQLAlchemy ORM - Eager Loading & Deleting Related Objects

Source From Here 
Eager Loading 
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 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. 

view plaincopy to clipboardprint?

1. c1 = session.query(Customers).options(subqueryload(Customers.invoices)).filter_by(name='Gopal Krishna').one()  
2. print('Name of c1 is {}'.format(c1.name))  
3. print('Invoices of c1:')  
4. for iv in c1.invoices:  
5.     print('\t{} (amount={:,d})'.format(iv.invno, iv.amount))  

Output: 

Invoices of c1:
10 (amount=15,000)
14 (amount=3,850)

This emits following expression giving same output as above: 

view plaincopy to clipboardprint?

1. 2019-06-27 09:58:27,452 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email  
2. FROM customers  
3. WHERE customers.name = %(name_1)s  
4. 2019-06-27 09:58:27,452 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  
5. 2019-06-27 09:58:27,454 INFO sqlalchemy.engine.base.Engine 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  
6. FROM (SELECT customers.id AS customers_id  
7. FROM customers  
8. WHERE customers.name = %(name_1)s) AS anon_1 JOIN invoices ON anon_1.customers_id = invoices.custid ORDER BY anon_1.customers_id, invoices.id  
9. 2019-06-27 09:58:27,454 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  

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: 

view plaincopy to clipboardprint?

1. # Joined Load  
2. c1 = session.query(Customers).options(joinedload(Customers.invoices)).filter_by(name='Gopal Krishna').one()  

This emits following expression giving same output as above: 

view plaincopy to clipboardprint?

1. 2019-06-27 10:00:21,443 INFO sqlalchemy.engine.base.Engine 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  
2. FROM customers LEFT OUTER JOIN invoices AS invoices_1 ON customers.id = invoices_1.custid  
3. WHERE customers.name = %(name_1)s ORDER BY invoices_1.id  
4. 2019-06-27 10:00:21,443 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  

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. 

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: 
- build_rel_ex1.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import Column, Integer, String, ForeignKey  
2. from sqlalchemy import create_engine  
3. from sqlalchemy.ext.declarative import declarative_base  
4. from sqlalchemy.orm.session import sessionmaker  
5. from sqlalchemy.orm import relationship  
6.   
7.   
8. db_string = "postgresql://postgres:john7810@localhost/testdb"  
9. engine = create_engine(db_string, echo=True)  
10. Base = declarative_base()  
11.   
12. class Customers(Base):  
13.     __tablename__ = 'customers'  
14.     id = Column(Integer, primary_key=True)  
15.     name = Column(String)  
16.     address = Column(String)  
17.     email = Column(String)  
18.   
19. class Invoice(Base):  
20.    __tablename__ = 'invoices'  
21.   
22.    id = Column(Integer, primary_key = True)  
23.    custid = Column(Integer, ForeignKey('customers.id'))  
24.    invno = Column(Integer)  
25.    amount = Column(Integer)  
26.    customer = relationship("Customers", back_populates = "invoices")  
27.   
28. Customers.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")  

We setup a session and obtain a Customers object by querying it with primary ID using the below program: 

>>> from build_rel_ex1 import *
>>> x = session.query(Customers).get(2)
...
2019-07-21 16:59:31,348 INFO sqlalchemy.engine.base.Engine 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.id = %(param_1)s
2019-07-21 16:59:31,348 INFO sqlalchemy.engine.base.Engine {'param_1': 2}

>>> x.name
'Komal Pande'

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: 

>>> session.delete(x)
>>> session.query(Customers).filter_by(name = 'Komal Pande').count()
...
2019-07-21 17:02:29,587 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (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 = %(name_1)s) AS anon_1
2019-07-21 17:02:29,588 INFO sqlalchemy.engine.base.Engine {'name_1': 'Komal Pande'}
0

However, the related Invoice objects of x are still there. It can be verified by the following: 

>>> session.query(Invoice).filter(Invoice.invno.in_([10, 14])).count()
2019-07-21 17:06:08,553 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount
FROM invoices
WHERE invoices.invno IN (%(invno_1)s, %(invno_2)s)) AS anon_1
2019-07-21 17:06:08,553 INFO sqlalchemy.engine.base.Engine {'invno_1': 10, 'invno_2': 14}
2

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 

* save-update
* merge
* expunge
* delete
* delete-orphan
* refresh-expire

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: 

view plaincopy to clipboardprint?

1. Customers.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer", cascade = 'all, delete, delete-orphan')  

Let us delete the Customers with Gopal Krishna name using the below program and see the count of its related Invoice objects. First of all, let's check DB status: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM customers WHERE name='Gopal Krishna';  
2. id |     name      |        address        |    email  
3. ----+---------------+-----------------------+--------------  
4.   5 | Gopal Krishna | Bank Street Hydarebad | gk@gmail.com  
5. (1 row)  
6.   
7. testdb=# SELECT * FROM Invoices WHERE custid = 5;  
8. id | custid | invno | amount  
9. ----+--------+-------+--------  
10.   1 |      5 |    10 |  15000  
11.   2 |      5 |    14 |   3850  
12. (2 rows)  

Now below is the sample code to delete customer: 
- delete_gk.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python  
2. from  build_rel_ex1 import *  
3.   
4. x = session.query(Customers).get(5)  
5. print('Delete customer={} (id={})'.format(x.name, x.id))  
6. cust_name = x.name  
7. cust_id = x.id  
8. session.delete(x)  
9. num_cust = session.query(Customers).filter_by(name = cust_name).count()  
10. print('Now we have {:,d} customer with name as {}...'.format(num_cust, cust_name))  
11. num_invo = session.query(Invoice).filter(Invoice.custid == x.id).count()  
12. print('Now we have {:,d} invoice associated with customer id={}!'.format(num_invo, cust_id))  

Execution output: 

...
2019-07-21 17:27:09,972 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.emai l AS customers_email
FROM customers
WHERE customers.id = %(param_1)s
2019-07-21 17:27:09,972 INFO sqlalchemy.engine.base.Engine {'param_1': 5}
Delete customer=Gopal Krishna (id=5)
...
2019-07-21 17:27:09,978 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT customers.id AS customers_id, customers.name AS customers_name, customers.addre ss AS customers_address, customers.email AS customers_email
FROM customers
WHERE customers.name = %(name_1)s) AS anon_1
2019-07-21 17:27:09,978 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}
Now we have 0 customer with name as Gopal Krishna...
...
2019-07-21 17:28:35,931 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount
FROM invoices
WHERE invoices.custid = %(custid_1)s) AS anon_1
2019-07-21 17:28:35,932 INFO sqlalchemy.engine.base.Engine {'custid_1': 5}
Now we have 0 invoice associated with customer id=5!

Supplement 
* 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