Python program: Calculate total order cost with SQLAlchemy

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

Write a Python program to calculate and display the total cost of all orders for a given user 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 calculate_total_cost(user_id):
    total_cost = (
        session.query(func.sum(Order.order_quantity * Item.item_price))
        .join(Item, Order.item_id == Item.item_id)
        .filter(Order.user_id == user_id)
        .scalar()
    )
    return total_cost 
# Calculate and display the total cost of all orders for a given user
user_id = 1  # Replace with the user's ID
total_cost = calculate_total_cost(user_id)
print(f"Total cost of all orders for user {user_id}: ${total_cost:.2f}")
# Close the session
session.close()

Output:

Total cost of all orders for user 1: $83.60

Flowchart:

Previous: Retrieve user-specific orders with SQLAlchemy.
Next: List top products by sales quantity using SQLAlchemy.