SQLAlchemy ORM - 過濾運算子

---

---

現在，我們將學習過濾操作及其相應的程式碼和輸出。

等於

常用的運算子是 ==，它應用條件來檢查相等性。

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 Name: Komal Pande Address: Banjara Hills Secunderabad Email: 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