Python Data Persistence – ORM – Filter Criteria

Python Data Persistence – ORM – Filter Criteria

The query object has a filter ( ) method that implements the WHERE clause as used in the raw SQL SELECT statement. The argument to filter can be any Boolean expression. In the following snippet, the filter is ‘price>20000’.

rows=q.filter (Product .price>20000)

This will translate into corresponding SQL statements as under:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", "Products". 
name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "Products".price >?
(20000, )

SQLAlchemy supports the use of wild cards for filter operations on string columns. The LIKE keyword in SQL is implemented by applying a ( ) filter. Products. name. like (‘ %er’) filters rows with product name ending with ‘er’>

rows=q.filter (Product .name. like (1 %er') )

In effect above statement is equivalent to the following SQL query:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products" 
WHERE "Products".name LIKE ? 
(' %er' ,)

As you will expect, the following output will be displayed:

name: Router price: 2000 
name: Scanner Price: 5000 
name: Printer price: 9000

The filter ( ) can have AND/OR conjunctions implemented by and_ ( ) and or_( ).

Following filter returns products with a price between 10000 and 30000

from sqlalchemy 
import and_ rows=q.filter(and_
(Product.price>10000, Product. price<30000) )

Here is the generated SQL:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products" .price AS "Products__price" 
FROM "Products" 
WHERE "Products".price > ? AND "Products".price < ? 
(10000, 30000)

The OR operation is performed by the following statement

Example

from sqlalchemy import or_ 
rows=q.filter(or_(Product.price>20000, Product.name. like (' %er' ) ) )

which is equivalent to the following SQL statement:

Example

SELECT "Products"."ProductID" AS "Products_ ProductID", 
"Products".name AS "Products_name", "Products".price AS "Products_price" 
FROM "Products", 
"Customers" WHERE "Products".price <=? 
OR "Customers".name LIKE? 
(5000, '%er')