In the last article: Python-SQL Alchemy: Section 1: Introduction to SQL Alchemy

In practical programming, database records need to be queried according to various conditions, and SQL Alchemy query conditions are called filters. Here is a list of the most commonly used filters.

1. Equivalent filter (==)

Equivalent filters are the most commonly used filters to determine whether a column is equal to a value.

session.query(Account).filter(Account.user_name=='Mark') #Judging String Type
session.query(Account).filter(Account.salary==2000) #Judgment of numerical type

2. unequal fi lt ers (!=,<,>,<=,>=)

As opposed to equivalent filters, they are not equal to filters. They can be extended into several forms: not equal to, less than, greater than, less than, equal to, or greater than or equal to.

session.query(Account).filter(Account.user_name !="mark" ) #Not equal to string type
session.query(Account).filter(Account.salary !=2000) #Not equal to numerical type
session.query(Account).filter(Account.salary >2000) #Larger than filter
session.query(Account).filter(Account.salary <2000) #Less than filter
session.query(Account).filter(Account.salary <=2000) #Less than or equal to a filter
session.query(Account).filter(Account.salary >=2000) #Greater than or equal to a filter

3. Fuzzy query (like)

Fuzzy query is suitable for querying different results by setting the position of wildcards when only part of the queried string is known. Wildcards are denoted by percent.

Assume that the data in the table is:

#Query all users whose names contain the letter i, and the results include four records with IDs of 1, 2, 3, and 4
session.query(Account).filter(Account.user_name.like('%i%'))

#Query all title s for users ending with Manager, resulting in two records with IDs of 1 and 5
session.query(Account).filter(Account.title.like('%Manager'))

#Query all users whose names start with Da and the results include two records with IDs of 1 and 3
session.query(Account).filter(Account.user_name.like('Da%'))

Note: Fuzzy queries only apply to query string types, not numerical types.

4. Including filters (in_)

Containment filters can be used when you know exactly what field content you want to query for records, but there are more than one content to query for a field.

#The query id is not 1, 3, 5 records, and the result contains two records with id 2, 4
session.query(Account).filter(~Account.id.in_([1,3,5]))
#Query the records of wages not 2000, 3000, 4000, and the result contains 1 record with id of 5.
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))
#Query for all title s that are not Engineer and Acountant records, and the results include two records with id 1 and 5
session.query(Account).filter(~Account.title.in_(['Accountant','Engineer']))

5. Judging whether it is empty (is NULL, is not NULL)

Null NULL is a special value in the database field. Supports the determination of whether a field is empty in SQLAlchemy. When judging, it can be filtered by equivalent or unequal filters, or it can be filtered by is or isnot.

#Query for a record with salary null and the result contains a record with id 5
#The following two methods have the same effect
session.query(Account).filter(Account.salary==None)
session.query(Account).filter(Account,salary.is_(None))

#Query salary for records that are not null, and the results contain records with IDs of 1, 2, 3, and 4
#The following two methods have the same effect
session.query(Account).filter(Account.salary!=None)
session.query(Account).filter(Account.salary.isnot(None))

6. Non-logical (~)

Non-logic can be used when querying records that do not satisfy certain conditions.

#The query id is not 1, 3, 5 records, and the result contains two records with id 2, 4
session.query(Account).filter(~Account.id.in_([1,3,5]))

#Query the records of wages not 2000, 3000, 4000, and the result contains 1 record with id of 5.
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))

#Query all title s that are not Engineer and Acountant records, and the results include two records with IDs of 1 and 5.
session.query(Account).filter(~Account.title.in(['Accountant','Engineer']))

7. and Logic (and)

When you need to query records that satisfy multiple conditions at the same time, you need to use and logic. In SQLAlchemy, logic can be expressed in three ways.

The following three statements have the same query results, all of which are records with id 3.

#Adding multiple conditions directly to the filter, i.e. representation and logic
session.query(Account).filter(Account.title=='Engineer',Account.salary=3000)

#Logical Query with Shutdown and uuuuuuuuuuuu
from sqlalchemy import and_
session.query(Account).filter(and_(Account.title=='Engineer',Account.salary=3000))

#Link representation and logic through multiple filter s
session.query(Account).filter(Account.title=='Engineer').filter(Account.salary=3000)

8. Or Logic (or_)

When multiple conditions need to be queried but only one condition is satisfied, it needs to be used or logic.

#Introduce or logical keyword or_
from sqlalchemy import or_

#Query title is a record with Engineer or salary of 3000 and returns a record with id of 1, 2, 3, 4.
session.query(Account).filter(or_(Account.title=='Engineer',Account.salary=3000))