Medical Store Stock and Expiry Alert

1Introduction: the problem it solves

Ask a chemist what worries them and expiry comes up quickly. Medicine that quietly passes its date is money thrown away, and a strip sold past its date is worse than that. Checking a thousand boxes by hand is nobody's idea of a morning.

A shop that records what it has can be asked both questions — what is about to expire, and what is about to run out — every single morning.

Why it is worth computerising

A chemist's shop carries several hundred items, each in batches with its own expiry date. Checking those dates means pulling boxes off shelves, so in practice it is done when there is time rather than when it is needed. Medicine that expires unnoticed is money lost, and a strip sold after its date is a great deal worse than that.

Once the same information is in a table with a real date column, both questions the shop cares about become sorting problems: what expires soonest, and what has nearly run out. The computer answers them every morning without anybody moving a box, which is the whole argument for putting the register into a database.

Objectives

1. To hold the shop's stock in a table that records batch and expiry, not just quantity
2. To reduce the quantity on every sale and refuse to sell more than exists
3. To list every medicine expiring within the next ninety days, soonest first
4. To list everything below the reorder level as a shopping list
5. To keep both thresholds as constants the shop can tune

2How the job is done today

Before writing anything it is worth asking how the work is handled at present, and why each of those answers falls short. These were examined:

3Functionalities

• A stock table holding name, batch, expiry date, quantity and price
• Selling reduces the quantity, and refuses to sell more strips than exist
• An expiry report listing anything dated within the next ninety days, soonest first
• A reorder list of everything below the reorder level
• Both thresholds are constants, so the shop can tune them

The functions that provide them

4Technical details

5How the data is stored

One table is enough here, and that is worth noticing: not every project needs many. What makes it useful is the expiry DATE column, because a date column can be compared and sorted, which a date typed into a text field cannot.

6How it works, step by step

7Source code

# ---------------------------------------------------------------------------
# pharmacy.py
#
# Stock, expiry and reorder alerts for a chemist's shop, using Python with
# MySQL.
#
# One table is enough here, and that is worth noticing: not every project needs
# many. What makes it useful is that expiry is stored as a real DATE, so it can
# be compared and sorted — which a date typed into a text field cannot.
# ---------------------------------------------------------------------------

import mysql.connector
from datetime import date, timedelta

REORDER_LEVEL = 20             # anything below this needs ordering
EXPIRY_WARNING_DAYS = 90       # how far ahead to look for expiry

db = mysql.connector.connect(host="localhost", user="root",
                             passwd="shop", database="lambdalab_pharmacy")
cur = db.cursor()


def sell(medicine_id, qty):
    """Sell some strips, if there are enough."""
    cur.execute("SELECT name, quantity FROM stock WHERE medicine_id = %s", (medicine_id,))
    name, have = cur.fetchone()

    # Checked before the UPDATE, so the quantity can never go negative.
    if have < qty:
        print("Only {} strips of {} left.".format(have, name))
        return

    # Written as quantity - %s rather than a number worked out in Python, so the
    # subtraction happens inside the database and cannot use a stale figure.
    cur.execute("UPDATE stock SET quantity = quantity - %s WHERE medicine_id = %s",
                (qty, medicine_id))

    db.commit()
    print("Sold {} x {}. Left in stock: {}".format(qty, name, have - qty))


def expiring_soon():
    """Everything dated within the warning window, soonest first."""
    # Today plus ninety days is the cut-off the query compares against.
    limit = date.today() + timedelta(days=EXPIRY_WARNING_DAYS)

    cur.execute("SELECT name, batch, expiry, quantity FROM stock"
                " WHERE expiry <= %s ORDER BY expiry", (limit,))

    print("MEDICINES EXPIRING WITHIN {} DAYS".format(EXPIRY_WARNING_DAYS))
    print("{:<18}{:<10}{:<12}{:>6}".format("NAME", "BATCH", "EXPIRY", "QTY"))

    # ORDER BY expiry puts the most urgent at the top, which is the only order
    # that is any use to the shop.
    for name, batch, expiry, qty in cur.fetchall():
        print("{:<18}{:<10}{:<12}{:>6}".format(name, batch, str(expiry), qty))


def reorder_list():
    """Everything that has nearly run out."""
    cur.execute("SELECT name, quantity FROM stock WHERE quantity < %s"
                " ORDER BY quantity", (REORDER_LEVEL,))

    print("\nTO BE ORDERED (below {} strips)".format(REORDER_LEVEL))

    for name, qty in cur.fetchall():
        print("  {:<18} {:>3} left".format(name, qty))


# --- the program itself ----------------------------------------------------
# The two reports the shop wants every morning.

expiring_soon()
reorder_list()

db.close()

CREATE DATABASE IF NOT EXISTS lambdalab_pharmacy;
USE lambdalab_pharmacy;

CREATE TABLE stock (
    medicine_id INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(30) NOT NULL,
    batch       VARCHAR(10),
    expiry      DATE,
    quantity    INT DEFAULT 0,
    price       DECIMAL(8,2)
);

INSERT INTO stock (name, batch, expiry, quantity, price) VALUES
    ('Paracetamol 500', 'B2291', '2026-10-30',  48, 22.50),
    ('Cough Syrup',     'C1180', '2026-09-12',   6, 88.00),
    ('ORS Sachet',      'O7742', '2027-06-01', 120, 18.00),
    ('Antacid Tablet',  'A3310', '2026-11-05',  14, 35.00);

8Sample output

This is a real run, not a mock-up — the transcript below is what the program actually printed.

C:\LambdaLab\Projects\Pharmacy> python pharmacy.py
MEDICINES EXPIRING WITHIN 90 DAYS
NAME              BATCH     EXPIRY         QTY
Cough Syrup       C1180     2026-09-12       6
Paracetamol 500   B2291     2026-10-30      48
Antacid Tablet    A3310     2026-11-05      14

TO BE ORDERED (below 20 strips)
  Cough Syrup          6 left
  Antacid Tablet      14 left

9Testing

Every case below was actually run and the result recorded as it appeared — including the ones expected to fail.

Two kinds of case are in there on purpose. The boundary cases test the edge of a rule, where a program is most often wrong by one. The failure cases check that it refuses cleanly and says why, instead of quietly producing a wrong answer.

10Advantages

Set against the ways the job is done today:

• Expiry is checked every morning instead of when somebody remembers
• Stock cannot go negative, because a sale is refused before it happens
• The reorder list is produced from the same data, not compiled separately
• Dates are stored as dates, so they sort correctly
• One table serves both the counter and the purchase decision

11Future scope

What this version does not do

A report should be honest about its own limits, and each of these is the reason for one of the additions below:

• Sales are not recorded, so the program cannot say how fast an item moves
• There is no supplier table, so the reorder list cannot be split into orders
• Batches of the same medicine are separate rows and are not totalled
• The reorder level is the same for every medicine regardless of how it sells

Proposed enhancements

This is also where you make the project yours. Pick one or two of these, or something nobody here thought of:

• Record every sale in a second table, so you can see what actually moves
• Suggest a reorder quantity from how fast an item sells
• A supplier table, so the reorder list can be split into orders
• Scan a barcode instead of typing the medicine id
• A monthly report of value lost to expiry, which is what convinces the owner

12What you may have to teach yourself

CBSE expects some self-learning in a project, and says so. For this one, that means:

• Why a DATE column beats a date stored as text — try sorting both
• Parameterised queries with %s, and why you never build SQL by joining strings
• Optional: a tkinter counter window for the shop

13Conclusion

Two reports that used to mean pulling boxes off a shelf are now produced from the same table every morning: what is about to expire, and what is about to run out.

The single decision that made this work was storing the expiry as a date rather than as text. Everything the shop cares about is a question of order, and only a real date column can be ordered.

14References

Every report needs a bibliography. This one used:

• Computer Science with Python, Class XII — the NCERT / CBSE prescribed textbook, for the chapters on database concepts, SQL and interfacing Python with a database
• Computer Science with Python, Class XI — for functions, lists, dictionaries and string handling
• Python 3 documentation — https://docs.python.org/3/
• MySQL 8.0 Reference Manual and the Connector/Python Developer Guide — https://dev.mysql.com/doc/
• CBSE Senior School Curriculum, Computer Science (Subject Code 083) — the project guidelines this report follows
• LambdaLab — https://www.lambdalab.in