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 # 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 # 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
