Python Data Persistence - Querying related tables (ORM)

Python Data Persistence – Querying related tables (ORM)

The query object we used earlier in this chapter can act on more than one mapped class. By equating relating columns in two tables using filter ( ) function we can simulate implicit join affected by WHERE clause in SQL syntax.

The snippet gives the blow, we display the name of the product and its price of ProductID in the Invoices table. The filter ( ) establishes join on the basis of equal values of ProductID in Invoices and Products tables.

Example

from sqlalchemy import Column, Integer, String 
from sqlalchemy import create_engine, and_, or_ 
from myclasses import Product,base, Customer, 
Invoice 
engine = create_engine('sqlite:///mydb.sqlite1, echo=True) 
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session() 
q=sessionobj.query(Invoice,Product) 
for i,p in q.filter(Product.ProductID==Invoice. 
ProductID).all():
print (i.InvID, p.name, p.price, i.quantity)

The equivalent SQL expression emitted by SQLAlchemy will be echoed as follows:

Example

SELECT "Invoices"."InvID" AS "Invoices_InvID", "Invoices"."CustID" AS "Invoices_CustID",
"Invoices"."ProductID" AS "Invoices_ProductID",
"Invoices".quantity AS "Invoices_quantity",
"Products"."ProductID" AS "Products_ProductID", "Products".name AS 
"Products_name", "Products".price AS "Products_price"
FROM "Invoices", "Products"
WHERE "Products"."ProductID" =
"Invoices"."ProductID"

The output of the above script:

1 Laptop 25000 2
2 TV 40000 1
3 Mobile 15000 3
4 Laptop 25000 6
5 Printer 9000 3
6 TV 40000 5
7 Laptop 25000 4
8 Router 2000 10
9 Printer 9000 2
10 Scanner 5000 3

To join the ‘Invoices’ table with the ‘Customers’ table and display the name of the corresponding Customer as well, add another condition in the filter – equating their CustID columns). Change the looping statement as follows:

Example

print ("InvID,Customer,Product,Price,Quantity") 
for i,p,c in q.filter \ 
(and_(Product.ProductID==Invoice.ProductID, \ 
Customer.CustID==Invoice.CustID)).all( ): 
print ( ' { } , { } , { } , { } ' . format(i.InvID, 
c.name,p.name, p.price, i.quantity))

Set the echo parameter to False and run the script to obtain the following result:

InvID,Customer,Product,Price,Quantity
1 , Ravikumar , Laptop , 25000
2 , John , TV , 40000
3 , Divya , Mobile , 15000
4 , Nair , Laptop , 25000
5 , John , Printer , 9000
6 , Patel , TV , 40000
7 , Patel , Laptop , 25000 
8 , Shah , Router , 2000
9 , Irfan , Printer , 9000 
10 , Nitin , Scanner , 5000

As mentioned at the beginning of this chapter, SQLAlchemy defines schema-specific SQL Expression Language which is at the core of the domain-centric ORM model. The functionality of Expression Language is closer to raw SQL than ORM which offers a layer of abstraction over it. In the following section, a brief overview of SQLAlchemy’s Expression Language is covered.

Python Data Persistence – Querying related tables (ORM)

Related