SQLAlchemy ORM - Filter 过滤运算符

现在，我们将学习过滤操作及其各自的代码和输出。

等于

通常使用的运算符是 ==，它应用条件来检查相等性。

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 将发送以下 SQL 表达式 −

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

上述代码的输出如下 −

ID：2 姓名：Komal Pande 地址：Banjara Hills Secunderabad 电子邮件：komal@gmail.com

不等于

用于不等于的运算符是 !=，它提供不等于标准。

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)

结果 SQL 表达式是 −

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

上述代码行的输出如下 −

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() 方法本身会为 SELECT 表达式中的 WHERE 子句生成 LIKE 条件。

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

上述 SQLAlchemy 代码等同于以下 SQL 表达式 −

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 ?

上述代码的输出是 −

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

此运算符检查列值是否属于列表中的项目集合。它由 in_() 方法提供。

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)

此处，SQLite 引擎评估的 SQL 表达式将如下所示−

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

上述代码的输出如下 −

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

此连接词可通过在过滤器中放置多个逗号分隔的条件或使用 and_() 方法生成，如下所示 −

result = session.query(Customers).filter(Customers.id>2, Customers.name.like('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.like('Ra%')))

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

上述两种方法都产生类似的 SQL 表达式 −

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 ?

上述代码行的输出为 −

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

OR

此连接由 or_() 方法 实现。

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

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

因此，SQLite 引擎得到以下等效的 SQL 表达式 −

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 ?

上述代码的输出如下 −

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