[ Python 文章收集 ] SQLAlchemy ORM - Applying Filter & Filter Operators

Source From Here 
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: 

view plaincopy to clipboardprint?

1. session.query(class).filter(criteria)  

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

view plaincopy to clipboardprint?

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

This statement will translate into following SQL expression: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. 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: 
- demo23.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import Column, Integer, String  
2. from sqlalchemy import create_engine  
3. from sqlalchemy.ext.declarative import declarative_base  
4. from sqlalchemy.orm.session import sessionmaker  
5.   
6. db_string = "postgresql://postgres:[password]@localhost/testdb"  
7. engine = create_engine(db_string, echo=True)  
8. Base = declarative_base()  
9.   
10. class Customers(Base):  
11.     __tablename__ = 'customers'  
12.     id = Column(Integer, primary_key=True)  
13.     name = Column(String)  
14.     address = Column(String)  
15.     email = Column(String)  
16.   
17. Base.metadata.create_all(engine)  
18.   
19. Session = sessionmaker(bind = engine)  
20. session = Session()  
21. result = session.query(Customers).all()  
22.   
23. result = session.query(Customers).filter(Customers.id>2)  
24. for row in result:  
25.     print("ID: {}; Name: {}; Address: {}; Email: {}".format(row.id, row.name, row.address, 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

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

Equals 
The usual operator used is == and it applies the criteria to check equality. 

view plaincopy to clipboardprint?

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

SQLAlchemy will send following SQL expression: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. 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: 

view plaincopy to clipboardprint?

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

The resulting SQL expression is − 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. WHERE customers.id != ?  

The output for the above lines 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 
like() method itself produces the LIKE criteria for WHERE clause in the SELECT expression: 

view plaincopy to clipboardprint?

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

Above SQLAlchemy code is equivalent to following SQL expression: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. 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 list. It is provided by in_() method: 

view plaincopy to clipboardprint?

1. result = session.query(Customers).filter(Customers.id.in_([1,3]))  
2. for row in result:  
3.    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: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. 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: 

view plaincopy to clipboardprint?

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

Or 

view plaincopy to clipboardprint?

1. from sqlalchemy import and_  
2. result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%')))  
3.   
4. for row in result:  
5.    print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)  

Both the above approaches result in similar SQL expression: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. WHERE customers.id > ? AND customers.name LIKE ?  

The output for the above lines of code is: 

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

OR 
This conjunction is implemented by or_() method: 

view plaincopy to clipboardprint?

1. from sqlalchemy import or_  
2. result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%')))  
3.   
4. for row in result:  
5.    print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)  

As a result, SQLite engine gets following equivalent SQL expression: 

view plaincopy to clipboardprint?

1. SELECT customers.id   
2. AS customers_id, customers.name   
3. AS customers_name, customers.address   
4. AS customers_address, customers.email   
5. AS customers_email  
6. FROM customers  
7. 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

A full example code to demonstration the usage: 
- demo24.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import Column, Integer, String  
2. from sqlalchemy.sql.expression import or_  
3. from sqlalchemy import create_engine  
4. from sqlalchemy.ext.declarative import declarative_base  
5. from sqlalchemy.orm.session import sessionmaker  
6.   
7.   
8. db_string = "postgresql://postgres:[Password]@localhost/testdb"  
9. engine = create_engine(db_string, echo=True)  
10. Base = declarative_base()  
11.   
12. class Customers(Base):  
13.     __tablename__ = 'customers'  
14.     id = Column(Integer, primary_key=True)  
15.     name = Column(String)  
16.     address = Column(String)  
17.     email = Column(String)  
18.   
19. Base.metadata.create_all(engine)  
20.   
21. Session = sessionmaker(bind = engine)  
22. session = Session()  
23. result = session.query(Customers).all()  
24.   
25. result = session.query(Customers).filter(or_(Customers.id!=1, Customers.name.like('Ra%')), Customers.id.in_([2,3]))  
26. for row in result:  
27.     print("ID: {}; Name: {}; Address: {}; Email: {}".format(row.id, row.name, row.address, row.email))  

Output: 

ID: 2; Name: Komal Pande; Address: Banjara Hills Secunderabad; Email: komal@gmail.com
ID: 3; Name: Rajender Nath; Address: Sector 40, Gurgaon; Email: nath@gmail.com

Supplement 
* SQLAlchemy 1.3 Documentation - Query API 
* SQLAlchemy 1.3 Documentation - Column Elements and Expressions