Python program: List top products by sales quantity using SQLAlchemy

Python SQLAlchemy: Exercise-12 with Solution

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

Sample Solution:


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'))
    # 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
# Close the session


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


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: Calculate total order cost with SQLAlchemy.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Follow us on Facebook and Twitter for latest update.