Python program: Retrieve user-specific orders with SQLAlchemy

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

Write a Python program to retrieve all orders for a specific user from the 'Order' table 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.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 get_orders_for_user(user_id):
    orders = session.query(Order).filter_by(user_id=user_id).all()
    return orders
# Retrieve and display all orders for a specific user
user_id = 1  # Replace with the user's ID
orders = get_orders_for_user(user_id)
print(f"Orders for user {user_id}:")
for order in orders:
    print(f"Order ID: {order.order_id}, Item ID: {order.item_id}, Quantity: {order.order_quantity}")

# Close the session
session.close()

Output:

Orders for user 1:
Order ID: 100, Item ID: 1, Quantity: 2
Order ID: 101, Item ID: 3, Quantity: 10
Order ID: 103, Item ID: 1, Quantity: 3

Flowchart:

Previous: Update item quantity using SQLAlchemy.
Next: Calculate total order cost with SQLAlchemy.