2019年6月21日 星期五

[ 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: 
  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: 
  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: 
  1. session.add(c1)  
  2. session.commit()  
This will automatically generate INSERT queries for customers and invoices tables: 
  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. (21015000)  
  5. INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)  
  6. (2143850)  
Let us now look at contents of customers table and invoices table in the table view: 
  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: 
  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 
  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: 
  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: 
  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: 
  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: 
  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: 
  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: 
  1. s = session.query(Customer).filter(Invoice.custid.__ne__(2))  
The equivalent SQL query for the above line of code is given below: 
  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()
  1. rst = session.query(Customers).filter(Customers.name.contains('R')).all()  
The equivalent SQL query: 
  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: 
  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 
  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: 
  1. rst = session.query(Invoice).filter(Invoice.customer.has(Customers.name == 'Gopal Krishna'))  
The equivalent SQL query for the above line of code is: 
  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

[ 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 crea...