程式扎記: [ 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?
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?
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?
session.add(c1)  
session.commit()  

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

view plaincopy to clipboardprint?
INSERT INTO customers (name, address, email) VALUES (?, ?, ?)   
('Gopal Krishna', 'Bank Street Hydarebad', 'gk@gmail.com')  
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)  
(2, 10, 15000)  
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)  
(2, 14, 3850)  

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

view plaincopy to clipboardprint?
testdb=# SELECT * FROM customers;  
id |     name      |          address           |      email  
----+---------------+----------------------------+-----------------  
  1 | Ravi Kumar    | Station Road Nanded        | ravi@gmail.com  
  3 | Rajender Nath | Sector 40, Gurgaon         | nath@gmail.com  
  4 | S.M.Krishna   | Budhwar Peth, Pune         | smk@gmail.com  
  2 | Komal Pande   | Banjara Hills Secunderabad | komal@gmail.com  
  5 | Gopal Krishna | Bank Street Hydarebad      | gk@gmail.com  
(5 rows)  
  
testdb=# SELECT * FROM invoices;  
id | custid | invno | amount  
----+--------+-------+--------  
  1 |      5 |    10 |  15000  
  2 |      5 |    14 |   3850  
(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?
c2 = [  
   Customer(  
      name = "Govind Pant",   
      address = "Gulmandi Aurangabad",  
      email = "gpant@gmail.com",  
      invoices = [Invoice(invno = 3, amount = 10000),   
      Invoice(invno = 4, amount = 5000)]  
   )  
]  

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

view plaincopy to clipboardprint?
rows = [  
   Customer(  
      name = "Govind Kala",   
      address = "Gulmandi Aurangabad",   
      email = "kala@gmail.com",   
      invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),  
  
   Customer(  
      name = "Abdul Rahman",   
      address = "Rohtak",   
      email = "abdulr@gmail.com",  
      invoices = [Invoice(invno = 9, amount = 15000),   
      Invoice(invno = 11, amount = 6000)  
   ])  
]  
  
session.add_all(rows)  
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?
for c, i in session.query(Customers, Invoice).filter(Customers.id == Invoice.custid).all():  
    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?
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  
FROM customers, invoices  
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?
testdb=# SELECT * FROM customers;  
id |     name      |          address           |      email  
----+---------------+----------------------------+------------------  
  1 | Ravi Kumar    | Station Road Nanded        | ravi@gmail.com  
  3 | Rajender Nath | Sector 40, Gurgaon         | nath@gmail.com  
  4 | S.M.Krishna   | Budhwar Peth, Pune         | smk@gmail.com  
  2 | Komal Pande   | Banjara Hills Secunderabad | komal@gmail.com  
  5 | Gopal Krishna | Bank Street Hydarebad      | gk@gmail.com  
  6 | Govind Kala   | Gulmandi Aurangabad        | kala@gmail.com  
  7 | Abdul Rahman  | Rohtak                     | abdulr@gmail.com  
(7 rows)  
  
testdb=# SELECT * FROM invoices;  
id | custid | invno | amount  
----+--------+-------+--------  
  1 |      5 |    10 |  15000  
  2 |      5 |    14 |   3850  
  3 |      6 |     7 |  12000  
  4 |      6 |     8 |  18500  
  5 |      7 |     9 |  15000  
  6 |      7 |    11 |   6000  
(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?
rst = session.query(Customers).filter(Invoice.invno.__eq__(10)).all()  
for r in rst:  
    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?
SELECT customers.id   
AS customers_id, customers.name   
AS customers_name, customers.address   
AS customers_address, customers.email   
AS customers_email  
FROM customers, invoices  
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?
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?
SELECT customers.id   
AS customers_id, customers.name   
AS customers_name, customers.address   
AS customers_address, customers.email   
AS customers_email  
FROM customers, invoices  
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?
rst = session.query(Customers).filter(Customers.name.contains('R')).all()  

The equivalent SQL query:

view plaincopy to clipboardprint?
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 LIKE '%%' || %(name_1)s || '%%')  
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?
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?
SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email  
FROM customers  
WHERE EXISTS (SELECT 1  
FROM invoices  
WHERE customers.id = invoices.custid AND invoices.invno = %(invno_1)s)  
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?
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?
SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount  
FROM invoices  
WHERE EXISTS (SELECT 1  
FROM customers  
WHERE customers.id = invoices.custid AND customers.name = %(name_1)s)  
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

程式扎記

標籤

2019年6月21日星期五

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

沒有留言:

張貼留言

[Git 常見問題] error: The following untracked working tree files would be overwritten by merge

檢舉濫用情形

學習筆記

標籤

2019年6月21日 星期五