Python program: Delete order from Order table using SQLAlchemy

Python SQLAlchemy: Exercise-13 with Solution

Write a Python program to delete an order from the 'Order' table by its id. Use 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 delete_order_by_id(order_id):
    # Query the order by its ID and delete it
    order_to_delete = session.query(Order).filter_by(order_id=order_id).first()
    if order_to_delete:
        session.delete(order_to_delete)
        session.commit()
        print(f"Order with ID {order_id} deleted successfully.")
    else:
        print(f"Order with ID {order_id} not found.")

# Delete an order by its ID
order_id_to_delete = 100  # Replace with the order's ID you want to delete
delete_order_by_id(order_id_to_delete)

# Close the session
session.close()

Output:

Order with ID 100 deleted successfully

Explanation:

In the above exercise -

• The "delete_order_by_id()" function queries the 'orders' table to retrieve an order with the specified ID.
• If the order is found, it is deleted using the delete method and the session is committed.
• If the order is not found, a message is displayed indicating that the order was not found.
• Replace ‘order_id_to_delete’ with the actual order's id that we want to delete.

Flowchart:

Previous: Calculate total order cost with SQLAlchemy.
Next: Aggregation queries with SQLAlchemy.