- SQLAlchemy Tutorial
- SQLAlchemy - Home
- SQLAlchemy - Introduction
- SQLAlchemy Core
- Expression Language
- Connecting to Database
- Creating Table
- SQL Expressions
- Executing Expression
- Selecting Rows
- Using Textual SQL
- Using Aliases
- Using UPDATE Expression
- Using DELETE Expression
- Using Multiple Tables
- Using Multiple Table Updates
- Parameter-Ordered Updates
- Multiple Table Deletes
- Using Joins
- Using Conjunctions
- Using Functions
- Using Set Operations
- SQLAlchemy ORM
- Declaring Mapping
- Creating Session
- Adding Objects
- Using Query
- Updating Objects
- Applying Filter
- Filter Operators
- Returning List and Scalars
- Textual SQL
- Building Relationship
- Working with Related Objects
- Working with Joins
- Common Relationship Operators
- Eager Loading
- Deleting Related Objects
- Many to Many Relationships
- Dialects
- SQLAlchemy Useful Resources
- SQLAlchemy - Quick Guide
- SQLAlchemy - Useful Resources
- SQLAlchemy - Discussion
Common Relationship Operators
In this chapter, we will discuss about the operators which build on relationships.
__eq__()
The above operator is a many-to-one “equals” comparison. The line of code for this operator is as shown below −
s = session.query(Customer).filter(Invoice.invno.__eq__(12))
The equivalent SQL query for the above line of code is −
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__()
This operator is a many-to-one “not equals” comparison. The line of code for this operator is as shown below −
s = session.query(Customer).filter(Invoice.custid.__ne__(2))
The equivalent SQL query for the above line of code is given below −
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()
This operator is used for one-to-many collections and given below is the code for contains() −
s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))
The equivalent SQL query for the above line of code is −
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() operator is used for collections as shown below −
s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))
The equivalent SQL query for the above line of code is shown below −
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()
This operator is used for scalar references as follows −
s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))
The equivalent SQL query for the above line of code is −
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 = ?)