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)
- Python Data Persistence – ORM – Querying
- Python Data Persistence – ORM – Add Data
- Python Data Persistence – ORM – Relationships
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.