常用關係運算符

本章將討論構建關係的運算子。

__eq__()

上述運算子是多對一“等於”比較。此運算子的程式碼行如下所示：

s = session.query(Customer).filter(Invoice.invno.__eq__(12))

上述程式碼行的等效SQL查詢為：

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?

__ne__()

此運算子是多對一“不等於”比較。此運算子的程式碼行如下所示：

s = session.query(Customer).filter(Invoice.custid.__ne__(2))

上述程式碼行的等效SQL查詢如下：

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?

contains()

此運算子用於一對多集合，以下是contains()的程式碼：

s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))

上述程式碼行的等效SQL查詢為：

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')

any()

any()運算子用於集合，如下所示：

s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))

上述程式碼行的等效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 EXISTS (
   SELECT 1
   FROM invoices
   WHERE customers.id = invoices.custid 
   AND invoices.invno = ?)

has()

此運算子用於標量引用，如下所示：

s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))

上述程式碼行的等效SQL查詢為：

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE EXISTS (
   SELECT 1
   FROM customers
   WHERE customers.id = invoices.custid 
   AND customers.name = ?)