How to GroupBy and Sum SQL Columns using SQLAlchemy in Python?

SQLAlchemy provides powerful tools for database operations in Python. One common task is grouping data and calculating sums, similar to SQL's GROUP BY and SUM() functions. This article demonstrates how to perform these operations using SQLAlchemy's ORM capabilities.

Syntax

The basic syntax for grouping and summing columns in SQLAlchemy ?

stmt = session.query(Table.column, func.sum(Table.numeric_column).label('total')).group_by(Table.column)
results = stmt.all()

Setting Up the Database Model

First, let's create a sample database model to demonstrate the GroupBy and Sum operations ?

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

# Create engine and base
engine = create_engine('sqlite:///sales.db')
Base = declarative_base()

# Define Sales table
class Sales(Base):
    __tablename__ = 'sales'
    
    id = Column(Integer, primary_key=True)
    product = Column(String)
    quantity = Column(Integer)
    price = Column(Integer)

# Create tables
Base.metadata.create_all(engine)
print("Database and tables created successfully")

Database and tables created successfully

Method 1: Using Session with Context Manager

This approach uses a context manager for better resource management ?

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

# Setup
engine = create_engine('sqlite:///sales.db')
Base = declarative_base()

class Sales(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    product = Column(String)
    quantity = Column(Integer)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

# Sample data
sample_data = [
    Sales(product='Apples', quantity=10),
    Sales(product='Bananas', quantity=15),
    Sales(product='Apples', quantity=8),
    Sales(product='Oranges', quantity=12),
    Sales(product='Bananas', quantity=5),
]

# Insert and query data
with Session() as session:
    # Add sample data
    session.add_all(sample_data)
    session.commit()
    
    # Group by product and sum quantities
    results = session.query(
        Sales.product, 
        func.sum(Sales.quantity).label('total_quantity')
    ).group_by(Sales.product).all()
    
    print("Product-wise Total Quantities:")
    for product, total in results:
        print(f"{product}: {total}")

Product-wise Total Quantities:
Apples: 18
Bananas: 20
Oranges: 12

Method 2: Using Traditional Session Management

This approach manually manages the session lifecycle ?

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

# Setup
engine = create_engine('sqlite:///sales2.db')
Base = declarative_base()

class Sales(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    product = Column(String)
    quantity = Column(Integer)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

try:
    # Sample data
    sample_data = [
        Sales(product='Laptops', quantity=3),
        Sales(product='Phones', quantity=7),
        Sales(product='Laptops', quantity=2),
        Sales(product='Tablets', quantity=4),
        Sales(product='Phones', quantity=6),
    ]
    
    # Add and commit data
    session.add_all(sample_data)
    session.commit()
    
    # GroupBy and Sum query
    stmt = session.query(
        Sales.product,
        func.sum(Sales.quantity).label('total_quantity')
    ).group_by(Sales.product)
    
    results = stmt.all()
    
    print("Product Sales Summary:")
    for product, total in results:
        print(f"{product}: {total} units")
        
finally:
    session.close()

Product Sales Summary:
Laptops: 5 units
Phones: 13 units
Tablets: 4 units

Advanced GroupBy Operations

You can also perform multiple aggregations and group by multiple columns ?

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

# Enhanced model
engine = create_engine('sqlite:///advanced_sales.db')
Base = declarative_base()

class Sales(Base):
    __tablename__ = 'sales'
    id = Column(Integer, primary_key=True)
    product = Column(String)
    region = Column(String)
    quantity = Column(Integer)

Base.metadata.create_all(engine)

# Sample data with regions
data = [
    Sales(product='Apples', region='North', quantity=10),
    Sales(product='Apples', region='South', quantity=15),
    Sales(product='Bananas', region='North', quantity=8),
    Sales(product='Bananas', region='South', quantity=12),
]

with sessionmaker(bind=engine)() as session:
    session.add_all(data)
    session.commit()
    
    # Group by multiple columns
    results = session.query(
        Sales.product,
        Sales.region,
        func.sum(Sales.quantity).label('total_quantity'),
        func.count(Sales.id).label('transaction_count')
    ).group_by(Sales.product, Sales.region).all()
    
    print("Product-Region Summary:")
    for product, region, total, count in results:
        print(f"{product} in {region}: {total} units ({count} transactions)")

Product-Region Summary:
Apples in North: 10 units (1 transactions)
Apples in South: 15 units (1 transactions)
Bananas in North: 8 units (1 transactions)
Bananas in South: 12 units (1 transactions)

Key Functions and Methods

Conclusion

SQLAlchemy makes GroupBy and Sum operations straightforward with its group_by() method and func.sum() function. Always use proper session management, either with context managers or try-finally blocks, to ensure resources are properly released.