The art of handling complex queries with Python ORM

python Object Relational Mapping (ORM) Framework can map the relationships in the database Data is seamlessly mapped to Python objects, simplifying interaction with the database. Even the most complex queries can be executed easily and efficiently with an ORM.

1. Nested query:

Nested queries allow the results of one query to be used as input to another query. In an ORM, this can be achieved by using nested filters. For example, you can use the filter() method to nest a subquery to find records that match specific criteria.

Example:

from sqlalchemy import and_, Column
from sqlalchemy.orm import sessionmaker, relationship

# Create an ORM Session
Session = sessionmaker()
session = Session()

# Book table and Author table
class Book(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
author_id = Column(Integer, ForeignKey("authors.id"))

# Author table
class Author(Base):
id = Column(Integer, primary_key=True)
name = Column(String)

# Use a nested query to find all book titles whose author is named "John"
query = session.query(Book.title).filter(Book.author_id.in_(
session.query(Author.id).filter(Author.name == "John")
))

2. Join query:

Join queries combine records from multiple tables. In the ORM, this can be achieved by using the join() method. For example, you can join two tables by using the join() method to find books with a specific author.

Example:

from sqlalchemy import and_, Column
from sqlalchemy.orm import sessionmaker, relationship

# Create an ORM Session
Session = sessionmaker()
session = Session()

# Book table and Author table
class Book(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
author_id = Column(Integer, ForeignKey("authors.id"))

# Author table
class Author(Base):
id = Column(Integer, primary_key=True)
name = Column(String)

# Use a join query to find all book titles whose author name is "John"
query = session.query(Book.title).join(Book.author).filter(Author.name == "John")

3. Aggregation function:

Aggregation functions combine multiple values ​​into a single value, such as summing, averaging, or finding the maximum value. In the ORM, this can be achieved by using aggregate functions such as sum(), avg(), and max(). For example, you can use the sum() function to calculate the total number of books by a specific author.

Example:

from sqlalchemy import and_, Column
from sqlalchemy.orm import sessionmaker, relationship

# Create an ORM Session
Session = sessionmaker()
session = Session()

# Book table and Author table
class Book(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
author_id = Column(Integer, ForeignKey("authors.id"))

# Author table
class Author(Base):
id = Column(Integer, primary_key=True)
name = Column(String)

# Calculate the total number of books by a specific author using an aggregate function
query = session.query(Author.name).group_by(Author).having(func.count(Book.id) > 1)

4. Dynamic query:

Dynamic queries allow queries to be constructed at runtime. In the ORM, this can be achieved by using the dynamic() function. For example, you can use the dynamic() function to build a query that contains specific filter criteria.

Example:

from sqlalchemy import and_, Column, literal
from sqlalchemy.orm import sessionmaker, relationship

# Create an ORM Session
Session = sessionmaker()
session = Session()

# Book table and Author table
class Book(Base):
id = Column(Integer, primary_key=True)
title = Column(String)
author_id = Column(Integer, ForeignKey("authors.id"))

# Author table
class Author(Base):
id = Column(Integer, primary_key=True)
name = Column(String)

# Use dynamic query to build a query containing specific filter conditions
query = session.query(Book).filter(literal(True).in_(
session.query(1).filter(Book.title == "Book Title")
))

By effectively leveraging these features of ORM, developers can build complex and efficient queries without writing SQL statements directly. This simplifies database interaction and improves readability and maintainability.

The above is the detailed content of The art of handling complex queries with Python ORM. For more information, please follow other related articles on the PHP Chinese website!