SQLAlchemy 关系运算符
在本章中,我们将讨论建立在关系上的运算符。
__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 = ?)