Python program: List top products by sales quantity using SQLAlchemy

Last update on December 21 2024 07:45:57 (UTC/GMT +8 hours)

Write a Python program to list the top 2 products by sales quantity from the 'orders' table using the SQLAlchemy model.

Sample Solution:

Code:

from sqlalchemy import create_engine, Column, Integer, Float, String, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func
from sqlalchemy.orm import relationship
# Create a SQLite database named shop.db
engine = create_engine('sqlite:///shop2.db', echo=False)
# Create a base class for declarative models
Base = declarative_base()
# Define the User model
class User(Base):
    __tablename__ = 'users'    
    user_id = Column(Integer, primary_key=True)
    user_name = Column(String, nullable=False)
    user_email = Column(String, nullable=False)
# Define the Item model
class Item(Base):
    __tablename__ = 'items'    
    item_id = Column(Integer, primary_key=True)
    item_name = Column(String, nullable=False)
    item_price = Column(Float, nullable=False)
    item_quantity = Column(Integer, nullable=False)    
    orders = relationship("Order", back_populates="item")  # Define the relationship to Order
# Define the Order model
class Order(Base):
    __tablename__ = 'orders'    
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)
    item_id = Column(Integer, ForeignKey('items.item_id'), nullable=False)
    order_quantity = Column(Integer, nullable=False)    
    item = relationship("Item", back_populates="orders")  # Define the relationship to Item
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
def top_items_by_sales_quantity(top_n=2):
    # Query the top N products by sales quantity
    query = (
        session.query(Order.item_id, func.sum(Order.order_quantity).label('total_quantity'))
        .group_by(Order.item_id)
        .order_by(func.sum(Order.order_quantity).desc())
        .limit(top_n)
    )    
    # Execute the query and retrieve the results
    results = query.all()    
    # Create a dictionary to store item_id -> total_quantity mapping
    product_quantity = dict(results)    
    # Retrieve the top products using the item IDs
    top_products = session.query(Item).filter(Item.item_id.in_(product_quantity.keys())).all()    
    # Display the top products and their sales quantity
    print(f"Top {top_n} items by sales quantity:")
    for product in top_products:
        quantity = product_quantity[product.item_id]
        print(f"Item: {product.item_name}, Sales Quantity: {quantity}")
# List the top 2 products by sales quantity
top_items_by_sales_quantity(top_n=2)
# Close the session
session.close()

Output:

Top 2 items by sales quantity:
Item: Burger, Sales Quantity: 5
Item: Ramen, Sales Quantity: 10

Explanation:

In the above exercise -

• The "top_items_by_sales_quantity()" function queries the "orders" table to calculate the total sales quantity for each item. It groups the results by 'item_id', and orders them in descending order of sales quantity.
• The limit method limits the results to the top N products.
• The results are then used to retrieve the corresponding item objects from the database.
• The top items and sales quantities are displayed using a loop.
• The top_n parameter specifies the number of top products to retrieve (default is 2).

Flowchart:

Previous: Calculate total order cost with SQLAlchemy.
Next: Delete order from Order table using SQLAlchemy.