In this blog post, we will explore how to use SQLAlchemy’s distinct, group by, and having functions in Python. These functions are useful when we need to perform queries with conditions, aggregates, and unique values.

1. Distinct

The distinct function in SQLAlchemy allows us to retrieve unique values from a column in a table. It is particularly useful when we want to remove duplicate values.

Here’s an example of how to use distinct in SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Create a connection to the database
engine = create_engine('sqlite:///database.db')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

# Define a sample table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

Base.metadata.create_all(engine)

# Retrieve distinct names from the users table
distinct_names = session.query(User.name).distinct().all()
print(distinct_names)

In the above example, the distinct function is used to retrieve only the unique names from the users table.

2. Group By

The group_by function in SQLAlchemy is used to group rows together based on a specific column or columns. It is often used in combination with aggregate functions like count, sum, avg, etc., to summarize data.

Here’s an example of how to use group by in SQLAlchemy:

# Group users by their age and count the number of users in each age group
age_group_counts = session.query(User.age, func.count()).group_by(User.age).all()
print(age_group_counts)

In the above example, the group_by function is used to group the users based on their age. The count function is then used to count the number of users in each age group.

3. Having

The having function in SQLAlchemy is used to filter the result of a query based on conditions that apply to the group as a whole. It allows us to apply conditions to the result of the group_by function.

Here’s an example of how to use having in SQLAlchemy:

# Filter out age groups with less than 3 users
filtered_age_groups = session.query(User.age, func.count()).group_by(User.age).having(func.count() >= 3).all()
print(filtered_age_groups)

In the above example, the having function is used to filter out age groups with less than 3 users. Only the age groups with 3 or more users will be included in the result.

Conclusion

In this blog post, we have explored how to use SQLAlchemy’s distinct, group by, and having functions in Python. These functions provide powerful capabilities to retrieve unique values, perform aggregations, and apply conditions to query results.