Python Data Persistence – ORM – Querying

Python Data Persistence – ORM – Querying

In order to fetch data from a database table, we need to obtain a query object. The query () method is defined in the Session class. It needs the mapped class as an argument.

q=sessionobj.query(Product)

The query object itself has access to various methods to fetch rows from the underlying table and return objects of the mapped class.

The query .get ( ) method accepts the primary key as an argument and returns the corresponding object. For example, the following statement returns object with ProductID=2 (ProductID being the primary key of Products table)

p=q . get(2)

Because the ‘echo’ parameter is set to True in the Engine constructor, the console shows the corresponding SQL statement generated by SQLAIchemy as below:

Example

BEGIN (implicit) 
SELECT "Products"."ProductID" AS 
"Products_ ProductID", "Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "ProductsProductID" = ? sqlalchemy.engine.base.Engine (2,)

Attributes of the object (p.name and p.price) can now be displayed. Still better, provide a__str__( ) method in Products class. Modify it in myclasses.py script as under:

Example

class Product(base):
__tablename ___= 'Products'

ProductID = Column(Integer, primary_key=True)
name = Column(String)
price = Column(Integer)
def__str__(self):
return 'name:{ } price: { } ' . format ( self . name , self . price )

The query, all ( ) method returns a list of all objects which can be traversed using a loop. Here is a fetchllrecs.py script:

Example

#fetchallrecs.py 
from sqlalchemy import Column, Integer, String 
from sqlalchemy import create_engine 
from myclasses import Product,base, Customers 
engine = create_engine('sqlite:///mydb.sqlite', echo=True) 
base.metadata.create_all(engine) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session ( ) 
q=sessionobj.query(Products) 
rows=q.all ( ) 
for row in rows: 
print (row)

Shown below is the output of this script along with shortened log in the console window (figure 9.4):

(SQLAlchemyEnv) E: \ SQLA1chemyEnv>python 
fetchallrecs.py PRAGMA table info( BEGIN (implicit) "Products") 
SELECT "Products". "ProductID" AS "Products_ 
ProductID", "Products".name AS 
"Products_name", "Products".price AS 
"Products_price" FROM "Products"
 ( ) 
 name: Laptop price: 25000 
 name: TV price: 40000 
 name: Router price: 2000 
 name: Scanner Price : 5000 
 name: Printer price : 9000 
 name: Mobile price: 15000