Introduction to the method of setting SQLAlchemy query conditions under Python programming

This article brings you an introduction to the method of setting SQLAlchemy query conditions under Python programming. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

In actual programming, database records need to be queried based on various conditions. SQLAlchemy query conditions are called filters. Here is a list of the most commonly used filters.

1. Equality filter (==)

The equality filter is used to determine whether a column is equal to a certain value and is the most commonly used filter.

session.query(Account).filter(Account.user_name=='Mark') #判断字符串类型
session.query(Account).filter(Account.salary==2000) #判断数值类型

2. Unequal filters (!=, <, >, <=, >=)

Relative to equal filters Is not equal to the filter, not equal to the filter can be extended to several forms: not equal to, less than, greater than, less than or equal to, greater than or equal to.

session.query(Account).filter(Account.user_name !="mark" ) #不等于字符串类型
session.query(Account).filter(Account.salary !=2000) #不等于数值类型
session.query(Account).filter(Account.salary >2000) #大于过滤器
session.query(Account).filter(Account.salary <2000) #小于过滤器
session.query(Account).filter(Account.salary <=2000) #小于等于过滤器
session.query(Account).filter(Account.salary >=2000) #大于等于过滤器

3. Fuzzy query (like)

Fuzzy query is suitable for when only part of the content of the queried string is known. By setting the position of the wildcard, you can query Different results. Wildcards are represented by the percent sign %.

Assume that the data in the table is:

id	user_name	title	salary
1	David Li	System Manager	3000
2	Debeca Li	Accountant	3000
3	David Backer	Engineer	3000
4	Siemon Bond	Enfineer	4000
5	Van Berg	General Manager	NULL

#查询所有名字包含字母i的用户，结果包括id为1、2、3、4的4条记录
session.query(Account).filter(Account.user_name.like('%i%'))

#查询所有title中以Manager结尾的用户，结果包括id为1、5的两条记录
session.query(Account).filter(Account.title.like('%Manager'))

#查询所有名字中以Da开头的用户，结果包括id为1、3的两条记录
session.query(Account).filter(Account.user_name.like('Da%'))

Attention : Fuzzy query is only applicable to query string types, not numeric types.

4. Include filter (in_)

When you know exactly the field content of the record to be queried, but there are multiple contents in a field to be queried, you can use Contains filters.

#查询id不为1，3，5的记录，结果包含id为2，4的两条记录
session.query(Account).filter(~Account.id.in_([1,3,5]))
#查询工资不为2000、3000、4000的记录，结果包含id为5的1条记录
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))
#查询所有title不为Engineer和Accountant记录，结果包括id为1、5的两条记录
session.query(Account).filter(~Account.title.in_(['Accountant','Engineer']))

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

The empty value NULL is a special value in the database field. SQLAlchemy supports judging whether a field is empty. When judging, you can use equal value and unequal value filters to filter, or you can use is and isnot to filter.

#查询salary为空值的记录，结果包含id为5的记录
#下面两方式效果相同
session.query(Account).filter(Account.salary==None)
session.query(Account).filter(Account,salary.is_(None))

#查询salary不为空值的记录，结果包含id为1、2、3、4的记录
#下面两方式效果相同
session.query(Account).filter(Account.salary!=None)
session.query(Account).filter(Account.salary.isnot(None))

6. Non-logical (~)

You can use non-logical when you need to query records that do not meet certain conditions.

#查询id不为1、3、5的记录，结果包含id为2、4的两条记录
session.query(Account).filter(~Account.id.in_([1,3,5]))

#查询工资不为2000、3000、4000的记录，结果包含id为5的1条记录
session.query(Account).filter(~Account.id.in_([2000,3000,4000]))

#查询所有title不为Engineer和Accountant的记录，结果包括id为1、5的2条记录。
session.query(Account).filter(~Account.title.in(['Accountant','Engineer']))

7. AND logic (and_)

When you need to query records that meet multiple conditions at the same time, you need to use AND logic. There are three ways to express logic in SQLAlchemy.

The query results of the following three statements are the same, and they are all records with ID 3.

#直接在filter中添加多个条件即表示与逻辑
session.query(Account).filter(Account.title=='Engineer',Account.salary=3000)

#用关机子and_进行逻辑查询
from sqlalchemy import and_
session.query(Account).filter(and_(Account.title=='Engineer',Account.salary=3000))

#通过多个filter的链接表示与逻辑
session.query(Account).filter(Account.title=='Engineer').filter(Account.salary=3000)

8, or logic (or_)

When you need to query multiple conditions but only one of the conditions needs to be met, you need to use or logic.

#引入或逻辑关键字or_
from sqlalchemy import or_

#查询title是Engineer或者salary为3000的记录，返回结果为id为1、2、3、4的记录
session.query(Account).filter(or_(Account.title=='Engineer',Account.salary=3000))

The above is the detailed content of Introduction to the method of setting SQLAlchemy query conditions under Python programming. For more information, please follow other related articles on the PHP Chinese website!