Sqlalchemy 简明教程

Common Relationship Operators

在本教程中，我们将讨论基于关系构建的运算符。

eq()

上面的运算符是多对一的"equals"比较。此运算符的代码行如下所示：

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

此运算符是多对一的"not equals"比较。此运算符的代码行如下所示：

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