[ Python 文章收集 ] SQLAlchemy ORM - Working with Related Objects, Joins and common operators

Source From Here 
Working with Related Objects 
In this chapter, we will focus on the related objects in SQLAlchemy ORM. Now when we create a Customer object, a blank invoice collection will be present in the form of Python List: 

view plaincopy to clipboardprint?

1. c1 = Customers(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "gk@gmail.com")  

The invoices attribute of c1.invoices will be an empty list. We can assign items in the list as: 

view plaincopy to clipboardprint?

1. c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]  

Let us commit this object to the database using Session object as follows: 

view plaincopy to clipboardprint?

1. session.add(c1)  
2. session.commit()  

This will automatically generate INSERT queries for customers and invoices tables: 

view plaincopy to clipboardprint?

1. INSERT INTO customers (name, address, email) VALUES (?, ?, ?)   
2. ('Gopal Krishna', 'Bank Street Hydarebad', 'gk@gmail.com')  
3. INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)  
4. (2, 10, 15000)  
5. INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)  
6. (2, 14, 3850)  

Let us now look at contents of customers table and invoices table in the table view: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM customers;  
2. id |     name      |          address           |      email  
3. ----+---------------+----------------------------+-----------------  
4.   1 | Ravi Kumar    | Station Road Nanded        | ravi@gmail.com  
5.   3 | Rajender Nath | Sector 40, Gurgaon         | nath@gmail.com  
6.   4 | S.M.Krishna   | Budhwar Peth, Pune         | smk@gmail.com  
7.   2 | Komal Pande   | Banjara Hills Secunderabad | komal@gmail.com  
8.   5 | Gopal Krishna | Bank Street Hydarebad      | gk@gmail.com  
9. (5 rows)  
10.   
11. testdb=# SELECT * FROM invoices;  
12. id | custid | invno | amount  
13. ----+--------+-------+--------  
14.   1 |      5 |    10 |  15000  
15.   2 |      5 |    14 |   3850  
16. (2 rows)  

You can construct Customer object by providing mapped attribute of invoices in the constructor itself by using the below command: 

view plaincopy to clipboardprint?

1. c2 = [  
2.    Customer(  
3.       name = "Govind Pant",   
4.       address = "Gulmandi Aurangabad",  
5.       email = "gpant@gmail.com",  
6.       invoices = [Invoice(invno = 3, amount = 10000),   
7.       Invoice(invno = 4, amount = 5000)]  
8.    )  
9. ]  

Or a list of objects to be added using add_all() function of session object as shown below 

view plaincopy to clipboardprint?

1. rows = [  
2.    Customer(  
3.       name = "Govind Kala",   
4.       address = "Gulmandi Aurangabad",   
5.       email = "kala@gmail.com",   
6.       invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),  
7.   
8.    Customer(  
9.       name = "Abdul Rahman",   
10.       address = "Rohtak",   
11.       email = "abdulr@gmail.com",  
12.       invoices = [Invoice(invno = 9, amount = 15000),   
13.       Invoice(invno = 11, amount = 6000)  
14.    ])  
15. ]  
16.   
17. session.add_all(rows)  
18. session.commit()  

Working with Joins 
Now that we have two tables, we will see how to create queries on both tables at the same time. To construct a simple implicit join between Customer and Invoice, we can use Query.filter() to equate their related columns together. Below, we load the Customer and Invoice entities at once using this method: 

view plaincopy to clipboardprint?

1. for c, i in session.query(Customers, Invoice).filter(Customers.id == Invoice.custid).all():  
2.     print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id, c.name, i.invno, i.amount))  

The SQL expression emitted by SQLAlchemy is as follows: 

view plaincopy to clipboardprint?

1. SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email, invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount  
2. FROM customers, invoices  
3. WHERE customers.id = invoices.custid  

And the result of the above lines of code is as follows: 

ID: 5 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 5 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 6 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 6 Name: Govind Kala Invoice No: 8 Amount: 18500
ID: 7 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 7 Name: Abdul Rahman Invoice No: 11 Amount: 6000

Common Relationship Operators 
Here we will discuss about the operators which build on relationships. So far we have below data in DB: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM customers;  
2. id |     name      |          address           |      email  
3. ----+---------------+----------------------------+------------------  
4.   1 | Ravi Kumar    | Station Road Nanded        | ravi@gmail.com  
5.   3 | Rajender Nath | Sector 40, Gurgaon         | nath@gmail.com  
6.   4 | S.M.Krishna   | Budhwar Peth, Pune         | smk@gmail.com  
7.   2 | Komal Pande   | Banjara Hills Secunderabad | komal@gmail.com  
8.   5 | Gopal Krishna | Bank Street Hydarebad      | gk@gmail.com  
9.   6 | Govind Kala   | Gulmandi Aurangabad        | kala@gmail.com  
10.   7 | Abdul Rahman  | Rohtak                     | abdulr@gmail.com  
11. (7 rows)  
12.   
13. testdb=# SELECT * FROM invoices;  
14. id | custid | invno | amount  
15. ----+--------+-------+--------  
16.   1 |      5 |    10 |  15000  
17.   2 |      5 |    14 |   3850  
18.   3 |      6 |     7 |  12000  
19.   4 |      6 |     8 |  18500  
20.   5 |      7 |     9 |  15000  
21.   6 |      7 |    11 |   6000  
22. (6 rows)  

__eq__() 
The above operator is a many-to-one “equals” comparison. The line of code for this operator is as shown below: 

view plaincopy to clipboardprint?

1. rst = session.query(Customers).filter(Invoice.invno.__eq__(10)).all()  
2. for r in rst:  
3.     print("Name={} ({})".format(r.name, list(map(lambda e: e.invno, r.invoices))))  

The equivalent SQL query for the above line of code is: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers, invoices  
7. WHERE invoices.invno = ?  

__ne__() 
This operator is a many-to-one “not equals” comparison. The line of code for this operator is as shown below: 

view plaincopy to clipboardprint?

1. s = session.query(Customer).filter(Invoice.custid.__ne__(2))  

The equivalent SQL query for the above line of code is given below: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers, invoices  
7. WHERE invoices.custid != ?  

contains() 
This operator is used for one-to-many collections and given below is the code for contains(): 

view plaincopy to clipboardprint?

1. rst = session.query(Customers).filter(Customers.name.contains('R')).all()  

The equivalent SQL query: 

view plaincopy to clipboardprint?

1. 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 LIKE '%%' || %(name_1)s || '%%')  
4. 2019-06-25 09:27:02,999 INFO sqlalchemy.engine.base.Engine {'name_1': 'R'}  

any() 
any() operator is used for collections as shown below: 

view plaincopy to clipboardprint?

1. rst = session.query(Customers).filter(Customers.invoices.any(Invoice.invno==10))  

The equivalent SQL query for the above line of code is shown below 

view plaincopy to clipboardprint?

1. 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 EXISTS (SELECT 1  
4. FROM invoices  
5. WHERE customers.id = invoices.custid AND invoices.invno = %(invno_1)s)  
6. 2019-06-25 09:31:47,649 INFO sqlalchemy.engine.base.Engine {'invno_1': 10}  

has() 
This operator is used for scalar references as follows: 

view plaincopy to clipboardprint?

1. rst = session.query(Invoice).filter(Invoice.customer.has(Customers.name == 'Gopal Krishna'))  

The equivalent SQL query for the above line of code is: 

view plaincopy to clipboardprint?

1. SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount  
2. FROM invoices  
3. WHERE EXISTS (SELECT 1  
4. FROM customers  
5. WHERE customers.id = invoices.custid AND customers.name = %(name_1)s)  
6. 2019-06-25 09:54:35,338 INFO sqlalchemy.engine.base.Engine {'name_1': 'Gopal Krishna'}  

Supplement 
* Prev - SQLAlchemy ORM - Building Relationship 
* Next - SQLAlchemy ORM - Common Relationship Operators 
* SQLAlchemy Doc - Using the Session 
* SQLAlchemy Doc - Query API