w3resource

Python program: Retrieve user-specific orders with SQLAlchemy

Python SQLAlchemy: Exercise-10 with Solution

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:

Flowchart: Retrieve user-specific orders with SQLAlchemy.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/python-exercises/sqlalchemy/python-sqlalchemy-exercise-10.php