SQLAlchemy ORM - Applying Filter

In this chapter, we will discuss how to apply filter and also certain filter operations along with their codes.

Resultset represented by Query object can be subjected to certain criteria by using filter() method. The general usage of filter method is as follows −

session.query(class).filter(criteria)

In the following example, resultset obtained by SELECT query on Customers table is filtered by a condition, (ID>2) −

result = session.query(Customers).filter(Customers.id>2)

This statement will translate into following SQL expression −

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.id > ?

Since the bound parameter (?) is given as 2, only those rows with ID column>2 will be displayed. The complete code is given below −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine( sqpte:///sales.db , echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ =  customers 
   
   id = Column(Integer, primary_key = True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).filter(Customers.id>2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

The output displayed in the Python console is as follows −

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com