Python program: Update item quantity using SQLAlchemy

Python SQLAlchemy: Exercise-8 with Solution

Write a Python program to update the quantity of an item in the 'Items' table based on its name using the SQLAlchemy model.

Sample Solution:

Code:

from sqlalchemy import create_engine, Column, Integer, Float, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# Create a SQLite database named shop.db
engine = create_engine('sqlite:///shop2.db', echo=True)
# Create a base class for declarative models
Base = declarative_base()
# 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)
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
def update_item_quantity(item_name, new_quantity):
    item = session.query(Item).filter_by(item_name=item_name).first()
    if item:
        item.item_quantity = new_quantity
        session.commit()
        print(f"Quantity for item '{item_name}' updated to {new_quantity}")
    else:
        print(f"Item '{item_name}' not found")
# Update the quantity of an item based on its name
item_name = 'Pizza'  # Replace with the item's name
new_quantity = 150  # Replace with the new quantity
update_item_quantity(item_name, new_quantity)
# Close the session
session.close()

Output:

2023-08-18 12:33:50,902 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2023-08-18 12:33:50,903 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 12:33:50,905 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2023-08-18 12:33:50,906 INFO sqlalchemy.engine.base.Engine ()
2023-08-18 12:33:50,908 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2023-08-18 12:33:50,909 INFO sqlalchemy.engine.base.Engine SELECT items.item_id AS items_item_id, items.item_name AS items_item_name, items.item_price AS items_item_price, items.item_quantity AS items_item_quantity 
FROM items 
WHERE items.item_name = ?
 LIMIT ? OFFSET ?
2023-08-18 12:33:50,909 INFO sqlalchemy.engine.base.Engine ('Pizza', 1, 0)
2023-08-18 12:33:50,911 INFO sqlalchemy.engine.base.Engine UPDATE items SET item_quantity=? WHERE items.item_id = ?
2023-08-18 12:33:50,911 INFO sqlalchemy.engine.base.Engine (150, 1)
2023-08-18 12:33:50,912 INFO sqlalchemy.engine.base.Engine COMMIT
Quantity for item 'Pizza' updated to 150

Note:

In the above exercise the messages we see are SQL statements and database operations executed by SQLAlchemy. These messages are part of SQLAlchemy's logging mechanism and are printed to the console due to the echo=True parameter we used when creating the database engine.

Flowchart:

Previous: Retrieve items with price greater than.
Next: Python program: Create new order using SQLAlchemy.