SQLAlchemy ORM - Filter Operators

Now, we will learn the filter operations with their respective codes and output.

Equals

The usual operator used is == and it apppes the criteria to check equapty.

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)

SQLAlchemy will send 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 = ?

The output for the above code is as follows −

ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com

Not Equals

The operator used for not equals is != and it provides not equals criteria.

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 resulting SQL expression is −

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 != ?

The output for the above pnes of code is as follows −

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
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

Like

pke() method itself produces the LIKE criteria for WHERE clause in the SELECT expression.

result = session.query(Customers).filter(Customers.name.pke( Ra% ))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Above SQLAlchemy code is equivalent to 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.name LIKE ?

And the output for the above code is −

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

IN

This operator checks whether the column value belongs to a collection of items in a pst. It is provided by in_() method.

result = session.query(Customers).filter(Customers.id.in_([1,3]))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Here, the SQL expression evaluated by SQLite engine will be as follows −

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 IN (?, ?)

The output for the above code is as follows −

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

AND

This conjunction is generated by either putting multiple commas separated criteria in the filter or using and_() method as given below −

result = session.query(Customers).filter(Customers.id>2, Customers.name.pke( Ra% ))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

from sqlalchemy import and_
result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.pke( Ra% )))

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

Both the above approaches result in similar 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 > ? AND customers.name LIKE ?

The output for the above pnes of code is −

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com

OR

This conjunction is implemented by or_() method.

from sqlalchemy import or_
result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.pke( Ra% )))

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

As a result, SQLite engine gets following equivalent 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 > ? OR customers.name LIKE ?

The output for the above code is as follows −

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
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