Join with sum and count of grouped rows in SQLAlchemy

SQLAlchemy is a popular Python ORM (Object-Relational Mapping) library that provides a convenient way to interact with databases. One of the common tasks when working with databases is to perform joins between tables and calculate aggregate values based on grouped rows. In this article, we will explore how to use SQLAlchemy to join tables and calculate sums and counts of grouped rows.

Join with the sum and count of grouped rows in SQLAlchemy

Creating Table

First, let’s consider a simple example scenario where we have two tables – orders and order_items. The orders table contains information about orders such as order ID, customer ID, and order date. The order_items table contains information about the items that are part of each order such as item ID, order ID, item name, and item price.

Orders Table

Order_items Table

Our goal is to join these two tables and calculate the total amount spent by each customer and the number of items purchased by each customer. We can achieve this by grouping the order_items table by customer ID and calculating the sum of item prices and the count of items for each group. Here’s how we can do it using SQLAlchemy.

Join with Sum

In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine()  then we are defining the table structures. Then we are using a sum and join function to get the total sum of item_price in the table.

Python3

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

# Define the database connection
engine = create_engine('postgresql://postgres:root@localhost:5432/test')

# Define the session
Session = sessionmaker(bind=engine)
session = Session()

# Define the base model
Base = declarative_base()

# Define the orders table
class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer)
    order_date = Column(String)

# Define the order items table
class OrderItem(Base):
    __tablename__ = 'order_items'
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'))
    item_name = Column(String)
    item_price = Column(Integer)
    order = relationship(Order, backref='order_items')
total_revenue = session.query(func.sum(
  OrderItem.item_price)).join(Order).scalar()

# Print the result
print(f"Total revenue: {total_revenue}")

Output:

Total revenue: 2600

Join with Count

In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine()  then we are defining the table structures. Then we are counting the total order related to all order_id and print it.

Output:

Order 2: 2 items
Order 3: 1 items
Order 1: 1 items

Join with sum and count

In this example, we have used SQLAlchemy and Postgresql. At first, we are making a connection of SQLAlchemy with Postgres in the function create_engine()  then we are defining the table structures. Then we are querying which customer spend how much total money and bought how many total items and printed it.

Python3

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

# Define the database connection
engine = create_engine('postgresql://postgres:root@localhost:5432/test')

# Define the session
Session = sessionmaker(bind=engine)
session = Session()

# Define the base model
Base = declarative_base()

# Define the orders table
class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer)
    order_date = Column(String)

# Define the order items table
class OrderItem(Base):
    __tablename__ = 'order_items'
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'))
    item_name = Column(String)
    item_price = Column(Integer)
    order = relationship(Order, backref='order_items')

# Define the query to join the tables and calculate sums and counts
query = session.query(Order.customer_id,
                      func.sum(OrderItem.item_price).label('total_spent'),
                      func.count(OrderItem.id).label('item_count')
                      ).join(OrderItem).group_by(Order.customer_id)

# Execute the query and print the results
for row in query:
    print(f"Customer {row.customer_id}: total spent =
          {row.total_spent}, item count = {row.item_count}")

Output:

Customer 30: total spent = 700, item count = 1
Customer 10: total spent = 500, item count = 1
Customer 20: total spent = 1400, item count = 2