Aggregate Data by Field Using SQLAlchemy and Flask-SQLAlchemy

This function uses Flask-SQLAlchemy and SQLAlchemy's func module to aggregate data from a specified model. It groups the data by a specified field and calculates the sum of another field.

Technology Stack : Flask-SQLAlchemy, SQLAlchemy, func

Code Type : The type of code

Code Difficulty : Intermediate

                
                    
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func

def aggregate_data(session, model, field, group_by_field):
    """
    This function aggregates data from a given SQLAlchemy model, grouping by a specified field
    and applying an aggregate function to another specified field.

    Args:
        session (SQLAlchemy.Session): The SQLAlchemy session to use for querying.
        model (SQLAlchemy.Model): The SQLAlchemy model to query.
        field (str): The field to apply the aggregate function to.
        group_by_field (str): The field to group the data by.

    Returns:
        list: A list of dictionaries, each containing the group by field and the aggregate result.
    """

    # Using the `func` from SQLAlchemy to apply an aggregate function
    result = session.query(
        model.group_by_field,
        func.sum(model.field).label('total')
    ).all()

    # Returning the result as a list of dictionaries
    return [{'group_by': record[0], 'total': record[1]} for record in result]