Python program: Calculate total order cost with SQLAlchemy

Python SQLAlchemy: Exercise-11 with Solution

Write a Python program to calculate and display the total cost of all orders for a given user 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 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)
    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


Total cost of all orders for user 1: $83.60


Flowchart: Calculate total order cost with SQLAlchemy.

Previous: Retrieve user-specific orders with SQLAlchemy.
Next: List top products by sales quantity 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.