Preparing your learning space...
50% through Real-World Projects tutorials
This tutorial builds a hospital management backend as a command-line program. You will design five related tables, load realistic data, and control the system from a Python menu. Booking an appointment shows how to check a condition before inserting, and the reports show how to count rows across related tables.
The program manages patients, doctors, departments, appointments, and medical records. A receptionist registers a patient or books an appointment, a doctor can check their schedule for a day, and records of diagnoses are kept per patient. Reports count patients and doctors per department.
This is a scaled-down version of software hospitals actually use. The patterns here - linked tables, status columns, availability checks before a write, and LEFT JOIN reports - carry over to booking systems of any kind.
By the end, you will know how to check a condition before inserting a row, why updating a status beats deleting a row, and how LEFT JOINs keep every department visible in a report.
mysql-connector-python package.input(), loops, tuples.Set up once before starting:
pip install mysql-connector-python
This installs the driver that lets Python talk to MySQL. Without it, import mysql.connector fails with ModuleNotFoundError.
Next, create the database by loading the SQL file. From the project folder run:
mysql -u root -p < database.sql
You will be prompted for your MySQL root password. You need a MySQL account that can log in from the command line; root is fine for learning. db_connection.py uses the same credentials, so keep your password handy.
hospital/
├── database.sql
├── db_connection.py
├── main.py
├── requirements.txt
└── README.md
database.sql creates the database and tables, then inserts seed data.db_connection.py has one function, get_connection(), that returns a MySQL connection.main.py holds the menu loop and every business operation.requirements.txt lists the package the project needs.README.md is a short description with the run commands.We are building the five tables that store departments, patients, doctors, appointments, and medical records.
A hospital needs to know which department a doctor belongs to, which doctor treats which patient, and which slots are already taken. Foreign keys enforce those relationships, and the schema is planned so the queries in later steps are simple.
Write the schema into database.sql.
CREATE DATABASE IF NOT EXISTS hospital_db;
USE hospital_db;
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL UNIQUE,
building VARCHAR(50),
phone_extension VARCHAR(10)
);
CREATE TABLE patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
phone VARCHAR(20),
address VARCHAR(200)
);
CREATE TABLE doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
specialization VARCHAR(100),
department_id INT NOT NULL,
phone VARCHAR(20),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
CREATE TABLE appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
appointment_date DATE NOT NULL,
time_slot VARCHAR(10) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'booked',
FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE
);
CREATE TABLE medical_records (
record_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
record_date DATE NOT NULL,
diagnosis VARCHAR(255) NOT NULL,
prescription VARCHAR(255),
notes VARCHAR(500),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE
);
The first two lines create the database and make it active. IF NOT EXISTS means running the file again does not fail on the database itself.
departments, patients, and doctors are the base tables. Each has an AUTO_INCREMENT PRIMARY KEY. doctors.department_id is a foreign key to departments, so a doctor cannot belong to a department that does not exist. UNIQUE on department_name prevents two departments with the same name.
appointments connects a patient and a doctor on a specific date and time. The pair appointment_date and time_slot describes a single slot, and the status column records whether it is booked, completed, or cancelled. ON DELETE CASCADE on both foreign keys means deleting a patient removes their appointments, and deleting a doctor removes that doctor's appointments.
medical_records stores the history of visits: which doctor saw which patient, when, and what was found. Its foreign keys cascade the same way, so the history disappears only when the patient or doctor it belongs to disappears.
We are adding sample rows so the program has data to read before any user action happens.
Reports and schedules only produce meaningful output when the tables have data. Seed data lets you test every menu option immediately and makes the numbers in the reports easy to verify by hand.
Append the INSERT statements to database.sql.
INSERT INTO departments (department_name, building, phone_extension) VALUES
('Cardiology', 'Building A', '4100'),
('Pediatrics', 'Building A', '4200'),
('Orthopedics', 'Building B', '4300'),
('General Medicine', 'Building B', '4400');
INSERT INTO patients (first_name, last_name, date_of_birth, phone, address) VALUES
('John', 'Smith', '1985-03-12', '555-0201', '12 Maple Street'),
('Maria', 'Garcia', '1992-07-24', '555-0202', '45 Cedar Avenue'),
('Ahmed', 'Hassan', '1978-11-02', '555-0203', '8 Oak Road'),
('Lucy', 'Brown', '2015-05-18', '555-0204', '23 Birch Lane'),
('Robert', 'King', '1960-01-30', '555-0205', '90 Pine Court'),
('Nina', 'Patel', '1989-09-09', '555-0206', '14 Elm Street'),
('Omar', 'Farouk', '2005-04-14', '555-0207', '6 Willow Way'),
('Grace', 'Lee', '1995-12-01', '555-0208', '31 Poplar Drive');
INSERT INTO doctors (first_name, last_name, specialization, department_id, phone) VALUES
('Henry', 'White', 'Cardiologist', 1, '555-0301'),
('Julia', 'Adams', 'Pediatrician', 2, '555-0302'),
('Peter', 'Nolan', 'Orthopedic Surgeon', 3, '555-0303'),
('Sofia', 'Marino', 'General Physician', 4, '555-0304'),
('Kevin', 'Brooks', 'Cardiologist', 1, '555-0305'),
('Amanda', 'Reed', 'Pediatrician', 2, '555-0306');
INSERT INTO appointments (patient_id, doctor_id, appointment_date, time_slot, status) VALUES
(1, 1, '2026-08-03', '09:00', 'booked'),
(2, 1, '2026-08-03', '10:00', 'booked'),
(4, 2, '2026-08-04', '09:00', 'booked'),
(3, 3, '2026-08-04', '11:00', 'booked'),
(5, 4, '2026-08-05', '09:00', 'completed'),
(6, 5, '2026-08-06', '14:00', 'booked'),
(7, 2, '2026-08-06', '10:00', 'booked'),
(8, 4, '2026-08-03', '15:00', 'cancelled');
INSERT INTO medical_records (patient_id, doctor_id, record_date, diagnosis, prescription, notes) VALUES
(1, 1, '2026-08-03', 'Hypertension', 'Lisinopril 10 mg', 'Check blood pressure in 3 months'),
(4, 2, '2026-08-04', 'Mild fever', 'Paracetamol syrup', 'Rest and fluids'),
(3, 3, '2026-08-04', 'Sprained ankle', 'Pain relief gel', 'Keep weight off for a week'),
(5, 4, '2026-08-05', 'Type 2 diabetes', 'Metformin 500 mg', 'Annual check-up'),
(6, 5, '2026-08-06', 'Arrhythmia', 'ECG requested', 'Cardiology follow-up'),
(7, 2, '2026-08-06', 'Seasonal allergy', 'Antihistamine', 'Avoid known triggers');
The departments get IDs 1 through 4, so the doctors can reference them directly. Each doctor row names a department by ID; that is why add_doctor later must translate a department name into an ID.
The appointments show the availability rules. Dr. White (doctor 1) sees John at 09:00 and Maria at 10:00 on August 3, two different slots, so both fit. Grace's appointment with Dr. Marino is marked cancelled, which matters later: the schedule still shows it, but it must not block the slot. The status column is what makes cancellation safe without deleting rows.
Each medical record pairs a patient with the doctor who treated them. The diagnosis is required, while prescription and notes can be empty. This is the visit history the hospital keeps.
We are building db_connection.py, a small module that returns a MySQL connection.
Every menu option needs a MySQL connection. Putting the connection in one function means the credentials live in a single place, and every operation uses the same setup. If the database name or password changes, you edit one file.
Create db_connection.py.
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password_here",
database="hospital_db",
)
The function imports the connector and returns a fresh connection every time it is called. host and user usually stay localhost and root during learning. Replace your_password_here with the real MySQL password you set up in Prerequisites. database tells MySQL which schema to use, so queries do not need a USE statement each time.
main.py imports this function and calls it at the start of every operation. Each function closes its cursor and connection when it finishes so the program does not leak open connections.
Two menu operations: register_patient() and add_doctor().
These are the simplest INSERT operations and they establish the pattern every other function follows: connect, run a parameterized query, commit, handle errors, close. add_doctor also demonstrates the lookup of a foreign key value by name.
Add these functions to main.py.
import mysql.connector
from db_connection import get_connection
def register_patient():
print("\n--- Register a New Patient ---")
first = input("First name: ").strip()
last = input("Last name: ").strip()
dob = input("Date of birth (YYYY-MM-DD): ").strip()
phone = input("Phone (optional): ").strip()
address = input("Address (optional): ").strip()
phone = phone if phone else None
address = address if address else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO patients (first_name, last_name, date_of_birth, phone, address) "
"VALUES (%s, %s, %s, %s, %s)",
(first, last, dob, phone, address),
)
conn.commit()
print(f"Patient registered with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not register patient: {err}")
finally:
cursor.close()
conn.close()
def add_doctor():
print("\n--- Add a New Doctor ---")
first = input("First name: ").strip()
last = input("Last name: ").strip()
specialization = input("Specialization: ").strip()
department = input("Department name: ").strip()
phone = input("Phone (optional): ").strip()
phone = phone if phone else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT department_id FROM departments WHERE department_name = %s",
(department,),
)
row = cursor.fetchone()
if row is None:
print(f"Department '{department}' does not exist. Create it in the database first.")
return
department_id = row[0]
cursor.execute(
"INSERT INTO doctors (first_name, last_name, specialization, department_id, phone) "
"VALUES (%s, %s, %s, %s, %s)",
(first, last, specialization, department_id, phone),
)
conn.commit()
print(f"Doctor added with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not add doctor: {err}")
finally:
cursor.close()
conn.close()
Both functions collect input, open a connection, and run an INSERT. The %s placeholders matter: user input is sent as a separate parameter, never pasted into the SQL string. That is what blocks SQL injection, where a user types something like '; DROP TABLE patients; -- and breaks or hijacks the query.
conn.commit() is what actually saves the row. The connector starts with autocommit off, so without commit() the INSERT appears to succeed and then vanishes when the program exits. If the query fails, the except block rolls back and prints the error.
add_doctor does one extra lookup. The doctors table stores a department_id, not a name, so the function resolves the typed department name to an ID first. If the department does not exist, the program stops early with a friendly message instead of hitting a foreign key error.
cursor.lastrowid gives the auto-assigned ID of the just-inserted row, which is how the program tells the user what got created.
book_appointment(): it verifies the patient and doctor exist, checks the doctor is free in the requested slot, and only then inserts the appointment.
Two patients must never get the same doctor in the same slot. The check has to happen before the INSERT, and the whole check-and-insert should be one transaction so a book that is happening at the same moment cannot slip in between the check and the write.
Add book_appointment() to main.py.
def book_appointment():
print("\n--- Book an Appointment ---")
try:
patient_id = int(input("Patient ID: "))
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("IDs must be numbers.")
return
date = input("Appointment date (YYYY-MM-DD): ").strip()
time_slot = input("Time slot (e.g. 09:00): ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT patient_id FROM patients WHERE patient_id = %s", (patient_id,)
)
if cursor.fetchone() is None:
raise ValueError(f"Patient {patient_id} does not exist.")
cursor.execute(
"SELECT doctor_id FROM doctors WHERE doctor_id = %s", (doctor_id,)
)
if cursor.fetchone() is None:
raise ValueError(f"Doctor {doctor_id} does not exist.")
cursor.execute(
"SELECT COUNT(*) FROM appointments "
"WHERE doctor_id = %s AND appointment_date = %s AND time_slot = %s "
"AND status = 'booked'",
(doctor_id, date, time_slot),
)
count = cursor.fetchone()[0]
if count > 0:
raise ValueError(
f"Doctor {doctor_id} is already booked on {date} at {time_slot}."
)
cursor.execute(
"INSERT INTO appointments (patient_id, doctor_id, appointment_date, time_slot) "
"VALUES (%s, %s, %s, %s)",
(patient_id, doctor_id, date, time_slot),
)
conn.commit()
print(f"Appointment booked with ID {cursor.lastrowid}.")
except Exception as err:
conn.rollback()
print(f"Could not book appointment: {err}")
finally:
cursor.close()
conn.close()
The function reads the patient ID, doctor ID, date, and slot from the user. It then starts a transaction. The first two SELECTs confirm that the patient and doctor exist, raising a ValueError if either is missing. That error jumps to the except block, which rolls back and prints a readable message.
The important query is the COUNT. It looks for any appointment for this doctor, on this date, in this time slot, where the status is still booked. Cancelled and completed appointments do not count, so a slot that was cancelled earlier can be reused. If the count is more than zero, the slot is taken and the function refuses.
Only when all checks pass does the INSERT run. The new row starts with the default status booked. A single conn.commit() makes it permanent, and any failure along the way triggers conn.rollback(). The rollback matters because check-then-insert in a transaction is what stops two bookings for the same slot from racing each other.
cancel_appointment() flips an appointment's status, and doctor_schedule() lists all appointments for one doctor on one date.
Cancelling should not destroy the record of the visit. A cancelled appointment still exists for the schedule and for reports, it just no longer blocks the slot. The schedule view is the JOIN that recombines appointments with patient names so the doctor sees who they are seeing.
Add the two functions to main.py.
def cancel_appointment():
print("\n--- Cancel an Appointment ---")
try:
appointment_id = int(input("Appointment ID: "))
except ValueError:
print("Appointment ID must be a number.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"UPDATE appointments SET status = 'cancelled' WHERE appointment_id = %s",
(appointment_id,),
)
if cursor.rowcount == 0:
print(f"Appointment {appointment_id} not found.")
return
conn.commit()
print(f"Appointment {appointment_id} cancelled.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not cancel appointment: {err}")
finally:
cursor.close()
conn.close()
def doctor_schedule():
print("\n--- Doctor's Schedule ---")
try:
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("Doctor ID must be a number.")
return
date = input("Date (YYYY-MM-DD): ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT first_name, last_name FROM doctors WHERE doctor_id = %s",
(doctor_id,),
)
doctor = cursor.fetchone()
if doctor is None:
print(f"Doctor {doctor_id} not found.")
return
print(f"\nSchedule for {doctor[0]} {doctor[1]} on {date}:")
cursor.execute(
"SELECT a.appointment_id, a.time_slot, a.status, "
"p.first_name, p.last_name "
"FROM appointments a "
"JOIN patients p ON a.patient_id = p.patient_id "
"WHERE a.doctor_id = %s AND a.appointment_date = %s "
"ORDER BY a.time_slot",
(doctor_id, date),
)
rows = cursor.fetchall()
if not rows:
print(" No appointments on this date.")
return
for appointment_id, time_slot, status, first, last in rows:
print(f" {time_slot} #{appointment_id} {first} {last} ({status})")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
cancel_appointment runs an UPDATE instead of a DELETE. The row stays in the table with its status changed to cancelled, so the visit history is preserved and a cancelled slot becomes free for the booking check in Step 5. cursor.rowcount tells the program whether the UPDATE matched a row; zero means the ID does not exist.
doctor_schedule runs two queries. The first confirms the doctor and prints their name. The second joins appointments to patients so each row shows a patient name instead of a bare ID. The results are sorted by time_slot, and cancelled appointments are still listed because the doctor should know the slot was once taken.
The JOIN is read-only, so no transaction is needed. The function uses the same connect, query, print, close pattern as every other read.
add_medical_record() records a diagnosis, and two reports count patients and doctors per department.
Medical history is the core record of care, and it belongs in its own table so one patient can accumulate many visits. The department reports answer simple management questions: how many doctors work where, and how many patients each department actually sees.
Add the three functions to main.py.
def add_medical_record():
print("\n--- Add a Medical Record ---")
try:
patient_id = int(input("Patient ID: "))
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("IDs must be numbers.")
return
date = input("Record date (YYYY-MM-DD): ").strip()
diagnosis = input("Diagnosis: ").strip()
prescription = input("Prescription (optional): ").strip()
notes = input("Notes (optional): ").strip()
prescription = prescription if prescription else None
notes = notes if notes else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO medical_records "
"(patient_id, doctor_id, record_date, diagnosis, prescription, notes) "
"VALUES (%s, %s, %s, %s, %s, %s)",
(patient_id, doctor_id, date, diagnosis, prescription, notes),
)
conn.commit()
print(f"Medical record added with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not add record: {err}")
finally:
cursor.close()
conn.close()
def patients_per_department():
print("\n--- Patients per Department ---")
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT d.department_name, COUNT(DISTINCT a.patient_id) AS patient_count "
"FROM departments d "
"LEFT JOIN doctors doc ON d.department_id = doc.department_id "
"LEFT JOIN appointments a ON doc.doctor_id = a.doctor_id "
"GROUP BY d.department_id, d.department_name "
"ORDER BY patient_count DESC, d.department_name"
)
rows = cursor.fetchall()
print(f"\n{'Department':<20}{'Patients':>10}")
for name, count in rows:
print(f"{name:<20}{count:>10}")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
def doctors_per_department():
print("\n--- Doctors per Department ---")
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT d.department_name, COUNT(doc.doctor_id) AS doctor_count "
"FROM departments d "
"LEFT JOIN doctors doc ON d.department_id = doc.department_id "
"GROUP BY d.department_id, d.department_name "
"ORDER BY doctor_count DESC, d.department_name"
)
rows = cursor.fetchall()
print(f"\n{'Department':<20}{'Doctors':>10}")
for name, count in rows:
print(f"{name:<20}{count:>10}")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
add_medical_record inserts into medical_records with the same parameterized pattern as registration. If the patient or doctor ID does not exist, the foreign key raises an IntegrityError, which the except block prints. The diagnosis is required; prescription and notes become NULL when left empty.
patients_per_department uses two LEFT JOINs: departments to doctors, then doctors to appointments. LEFT JOIN means a department with no doctors still appears, showing a count of zero instead of vanishing. COUNT(DISTINCT a.patient_id) counts each patient once even if they have several appointments in that department.
doctors_per_department is the simpler cousin: one LEFT JOIN from departments to doctors and a plain COUNT(doc.doctor_id). Both reports group by department and sort by the count descending, with the department name as a tie-breaker so the order is always the same.
The menu loop in main() that ties all the functions together, plus the module guard.
A file full of functions does nothing by itself. The menu lets a user pick an operation and keeps the program running until they choose to exit. Keeping the loop inside main() and guarded by if __name__ == "__main__" means the module can be imported without automatically starting the loop.
Add the menu and entry point to main.py.
def show_menu():
print("\n=========== HOSPITAL MANAGEMENT ===========")
print("1. Register a new patient")
print("2. Add a new doctor")
print("3. Book an appointment")
print("4. Cancel an appointment")
print("5. View a doctor's schedule for a date")
print("6. Add a medical record")
print("7. Patients per department report")
print("8. Doctors per department report")
print("9. Exit")
print("===========================================")
def main():
while True:
show_menu()
choice = input("Choose an option: ").strip()
if choice == "1":
register_patient()
elif choice == "2":
add_doctor()
elif choice == "3":
book_appointment()
elif choice == "4":
cancel_appointment()
elif choice == "5":
doctor_schedule()
elif choice == "6":
add_medical_record()
elif choice == "7":
patients_per_department()
elif choice == "8":
doctors_per_department()
elif choice == "9":
print("Goodbye!")
break
else:
print("Invalid option. Choose a number from the menu.")
if __name__ == "__main__":
main()
show_menu() just prints the options. main() runs an infinite loop that prints the menu, reads a choice, and dispatches to the matching function. The elif chain is simple to read and expand: adding a menu item means adding one print line and one elif. The else branch catches anything that is not a menu number. if __name__ == "__main__": runs main() only when the file is executed directly, not when imported elsewhere.
With this in place, the project is complete. Load the schema, run python main.py, and every option works.
-- Full schema and seed data for the hospital management project
CREATE DATABASE IF NOT EXISTS hospital_db;
USE hospital_db;
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL UNIQUE,
building VARCHAR(50),
phone_extension VARCHAR(10)
);
CREATE TABLE patients (
patient_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
phone VARCHAR(20),
address VARCHAR(200)
);
CREATE TABLE doctors (
doctor_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
specialization VARCHAR(100),
department_id INT NOT NULL,
phone VARCHAR(20),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
CREATE TABLE appointments (
appointment_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
appointment_date DATE NOT NULL,
time_slot VARCHAR(10) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'booked',
FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE
);
CREATE TABLE medical_records (
record_id INT AUTO_INCREMENT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
record_date DATE NOT NULL,
diagnosis VARCHAR(255) NOT NULL,
prescription VARCHAR(255),
notes VARCHAR(500),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE CASCADE
);
INSERT INTO departments (department_name, building, phone_extension) VALUES
('Cardiology', 'Building A', '4100'),
('Pediatrics', 'Building A', '4200'),
('Orthopedics', 'Building B', '4300'),
('General Medicine', 'Building B', '4400');
INSERT INTO patients (first_name, last_name, date_of_birth, phone, address) VALUES
('John', 'Smith', '1985-03-12', '555-0201', '12 Maple Street'),
('Maria', 'Garcia', '1992-07-24', '555-0202', '45 Cedar Avenue'),
('Ahmed', 'Hassan', '1978-11-02', '555-0203', '8 Oak Road'),
('Lucy', 'Brown', '2015-05-18', '555-0204', '23 Birch Lane'),
('Robert', 'King', '1960-01-30', '555-0205', '90 Pine Court'),
('Nina', 'Patel', '1989-09-09', '555-0206', '14 Elm Street'),
('Omar', 'Farouk', '2005-04-14', '555-0207', '6 Willow Way'),
('Grace', 'Lee', '1995-12-01', '555-0208', '31 Poplar Drive');
INSERT INTO doctors (first_name, last_name, specialization, department_id, phone) VALUES
('Henry', 'White', 'Cardiologist', 1, '555-0301'),
('Julia', 'Adams', 'Pediatrician', 2, '555-0302'),
('Peter', 'Nolan', 'Orthopedic Surgeon', 3, '555-0303'),
('Sofia', 'Marino', 'General Physician', 4, '555-0304'),
('Kevin', 'Brooks', 'Cardiologist', 1, '555-0305'),
('Amanda', 'Reed', 'Pediatrician', 2, '555-0306');
INSERT INTO appointments (patient_id, doctor_id, appointment_date, time_slot, status) VALUES
(1, 1, '2026-08-03', '09:00', 'booked'),
(2, 1, '2026-08-03', '10:00', 'booked'),
(4, 2, '2026-08-04', '09:00', 'booked'),
(3, 3, '2026-08-04', '11:00', 'booked'),
(5, 4, '2026-08-05', '09:00', 'completed'),
(6, 5, '2026-08-06', '14:00', 'booked'),
(7, 2, '2026-08-06', '10:00', 'booked'),
(8, 4, '2026-08-03', '15:00', 'cancelled');
INSERT INTO medical_records (patient_id, doctor_id, record_date, diagnosis, prescription, notes) VALUES
(1, 1, '2026-08-03', 'Hypertension', 'Lisinopril 10 mg', 'Check blood pressure in 3 months'),
(4, 2, '2026-08-04', 'Mild fever', 'Paracetamol syrup', 'Rest and fluids'),
(3, 3, '2026-08-04', 'Sprained ankle', 'Pain relief gel', 'Keep weight off for a week'),
(5, 4, '2026-08-05', 'Type 2 diabetes', 'Metformin 500 mg', 'Annual check-up'),
(6, 5, '2026-08-06', 'Arrhythmia', 'ECG requested', 'Cardiology follow-up'),
(7, 2, '2026-08-06', 'Seasonal allergy', 'Antihistamine', 'Avoid known triggers');
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password_here",
database="hospital_db",
)
import mysql.connector
from db_connection import get_connection
def show_menu():
print("\n=========== HOSPITAL MANAGEMENT ===========")
print("1. Register a new patient")
print("2. Add a new doctor")
print("3. Book an appointment")
print("4. Cancel an appointment")
print("5. View a doctor's schedule for a date")
print("6. Add a medical record")
print("7. Patients per department report")
print("8. Doctors per department report")
print("9. Exit")
print("===========================================")
def register_patient():
print("\n--- Register a New Patient ---")
first = input("First name: ").strip()
last = input("Last name: ").strip()
dob = input("Date of birth (YYYY-MM-DD): ").strip()
phone = input("Phone (optional): ").strip()
address = input("Address (optional): ").strip()
phone = phone if phone else None
address = address if address else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO patients (first_name, last_name, date_of_birth, phone, address) "
"VALUES (%s, %s, %s, %s, %s)",
(first, last, dob, phone, address),
)
conn.commit()
print(f"Patient registered with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not register patient: {err}")
finally:
cursor.close()
conn.close()
def add_doctor():
print("\n--- Add a New Doctor ---")
first = input("First name: ").strip()
last = input("Last name: ").strip()
specialization = input("Specialization: ").strip()
department = input("Department name: ").strip()
phone = input("Phone (optional): ").strip()
phone = phone if phone else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT department_id FROM departments WHERE department_name = %s",
(department,),
)
row = cursor.fetchone()
if row is None:
print(f"Department '{department}' does not exist. Create it in the database first.")
return
department_id = row[0]
cursor.execute(
"INSERT INTO doctors (first_name, last_name, specialization, department_id, phone) "
"VALUES (%s, %s, %s, %s, %s)",
(first, last, specialization, department_id, phone),
)
conn.commit()
print(f"Doctor added with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not add doctor: {err}")
finally:
cursor.close()
conn.close()
def book_appointment():
print("\n--- Book an Appointment ---")
try:
patient_id = int(input("Patient ID: "))
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("IDs must be numbers.")
return
date = input("Appointment date (YYYY-MM-DD): ").strip()
time_slot = input("Time slot (e.g. 09:00): ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT patient_id FROM patients WHERE patient_id = %s", (patient_id,)
)
if cursor.fetchone() is None:
raise ValueError(f"Patient {patient_id} does not exist.")
cursor.execute(
"SELECT doctor_id FROM doctors WHERE doctor_id = %s", (doctor_id,)
)
if cursor.fetchone() is None:
raise ValueError(f"Doctor {doctor_id} does not exist.")
cursor.execute(
"SELECT COUNT(*) FROM appointments "
"WHERE doctor_id = %s AND appointment_date = %s AND time_slot = %s "
"AND status = 'booked'",
(doctor_id, date, time_slot),
)
count = cursor.fetchone()[0]
if count > 0:
raise ValueError(
f"Doctor {doctor_id} is already booked on {date} at {time_slot}."
)
cursor.execute(
"INSERT INTO appointments (patient_id, doctor_id, appointment_date, time_slot) "
"VALUES (%s, %s, %s, %s)",
(patient_id, doctor_id, date, time_slot),
)
conn.commit()
print(f"Appointment booked with ID {cursor.lastrowid}.")
except Exception as err:
conn.rollback()
print(f"Could not book appointment: {err}")
finally:
cursor.close()
conn.close()
def cancel_appointment():
print("\n--- Cancel an Appointment ---")
try:
appointment_id = int(input("Appointment ID: "))
except ValueError:
print("Appointment ID must be a number.")
return
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"UPDATE appointments SET status = 'cancelled' WHERE appointment_id = %s",
(appointment_id,),
)
if cursor.rowcount == 0:
print(f"Appointment {appointment_id} not found.")
return
conn.commit()
print(f"Appointment {appointment_id} cancelled.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not cancel appointment: {err}")
finally:
cursor.close()
conn.close()
def doctor_schedule():
print("\n--- Doctor's Schedule ---")
try:
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("Doctor ID must be a number.")
return
date = input("Date (YYYY-MM-DD): ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT first_name, last_name FROM doctors WHERE doctor_id = %s",
(doctor_id,),
)
doctor = cursor.fetchone()
if doctor is None:
print(f"Doctor {doctor_id} not found.")
return
print(f"\nSchedule for {doctor[0]} {doctor[1]} on {date}:")
cursor.execute(
"SELECT a.appointment_id, a.time_slot, a.status, "
"p.first_name, p.last_name "
"FROM appointments a "
"JOIN patients p ON a.patient_id = p.patient_id "
"WHERE a.doctor_id = %s AND a.appointment_date = %s "
"ORDER BY a.time_slot",
(doctor_id, date),
)
rows = cursor.fetchall()
if not rows:
print(" No appointments on this date.")
return
for appointment_id, time_slot, status, first, last in rows:
print(f" {time_slot} #{appointment_id} {first} {last} ({status})")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
def add_medical_record():
print("\n--- Add a Medical Record ---")
try:
patient_id = int(input("Patient ID: "))
doctor_id = int(input("Doctor ID: "))
except ValueError:
print("IDs must be numbers.")
return
date = input("Record date (YYYY-MM-DD): ").strip()
diagnosis = input("Diagnosis: ").strip()
prescription = input("Prescription (optional): ").strip()
notes = input("Notes (optional): ").strip()
prescription = prescription if prescription else None
notes = notes if notes else None
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO medical_records "
"(patient_id, doctor_id, record_date, diagnosis, prescription, notes) "
"VALUES (%s, %s, %s, %s, %s, %s)",
(patient_id, doctor_id, date, diagnosis, prescription, notes),
)
conn.commit()
print(f"Medical record added with ID {cursor.lastrowid}.")
except mysql.connector.Error as err:
conn.rollback()
print(f"Could not add record: {err}")
finally:
cursor.close()
conn.close()
def patients_per_department():
print("\n--- Patients per Department ---")
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT d.department_name, COUNT(DISTINCT a.patient_id) AS patient_count "
"FROM departments d "
"LEFT JOIN doctors doc ON d.department_id = doc.department_id "
"LEFT JOIN appointments a ON doc.doctor_id = a.doctor_id "
"GROUP BY d.department_id, d.department_name "
"ORDER BY patient_count DESC, d.department_name"
)
rows = cursor.fetchall()
print(f"\n{'Department':<20}{'Patients':>10}")
for name, count in rows:
print(f"{name:<20}{count:>10}")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
def doctors_per_department():
print("\n--- Doctors per Department ---")
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute(
"SELECT d.department_name, COUNT(doc.doctor_id) AS doctor_count "
"FROM departments d "
"LEFT JOIN doctors doc ON d.department_id = doc.department_id "
"GROUP BY d.department_id, d.department_name "
"ORDER BY doctor_count DESC, d.department_name"
)
rows = cursor.fetchall()
print(f"\n{'Department':<20}{'Doctors':>10}")
for name, count in rows:
print(f"{name:<20}{count:>10}")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
def main():
while True:
show_menu()
choice = input("Choose an option: ").strip()
if choice == "1":
register_patient()
elif choice == "2":
add_doctor()
elif choice == "3":
book_appointment()
elif choice == "4":
cancel_appointment()
elif choice == "5":
doctor_schedule()
elif choice == "6":
add_medical_record()
elif choice == "7":
patients_per_department()
elif choice == "8":
doctors_per_department()
elif choice == "9":
print("Goodbye!")
break
else:
print("Invalid option. Choose a number from the menu.")
if __name__ == "__main__":
main()
mysql-connector-python
# Hospital Management System
A command-line hospital backend built with MySQL and Python. It manages patients,
doctors, departments, appointments, and medical records.
## Setup
Run these commands from the project folder:
pip install mysql-connector-python
mysql -u root -p < database.sql
python main.py
Install the connector if you have not already:
pip install mysql-connector-python
Load the schema and seed data. From the folder that contains database.sql:
mysql -u root -p < database.sql
Type your MySQL password when prompted. If you ever want to start fresh, drop the database first with mysql -u root -p -e "DROP DATABASE hospital_db;" and then load the file again.
Run the program:
python main.py
Expected first-run output:
$ python main.py
=========== HOSPITAL MANAGEMENT ===========
1. Register a new patient
2. Add a new doctor
3. Book an appointment
4. Cancel an appointment
5. View a doctor's schedule for a date
6. Add a medical record
7. Patients per department report
8. Doctors per department report
9. Exit
===========================================
Choose an option: 5
Doctor ID: 2
Date (YYYY-MM-DD): 2026-08-04
Schedule for Julia Adams on 2026-08-04:
09:00 #3 Lucy Brown (booked)
The schedule works immediately because of the seed data. Julia Adams has one booked appointment on August 4 with Lucy Brown, and the JOIN pulls Lucy's name from the patients table.
Test 1 - Book an appointment on a free slot.
Input
Choose an option: 3
Patient ID: 2
Doctor ID: 3
Appointment date (YYYY-MM-DD): 2026-08-06
Time slot (e.g. 09:00): 09:00
Expected Output
Appointment booked with ID 9.
Explanation
Dr. Nolan (doctor 3) has no appointment at 09:00 on August 6, so the availability COUNT returns zero and the INSERT succeeds. The new appointment gets the next ID, 9, and starts with the default status booked.
Test 2 - Book an appointment on a slot that is already taken.
Input
Choose an option: 3
Patient ID: 3
Doctor ID: 1
Appointment date (YYYY-MM-DD): 2026-08-03
Time slot (e.g. 09:00): 09:00
Expected Output
Could not book appointment: Doctor 1 is already booked on 2026-08-03 at 09:00.
Explanation
Dr. White (doctor 1) already has appointment 1 in that exact slot with John Smith. The COUNT query returns 1, the function raises a ValueError, and the transaction rolls back. No duplicate row is created, and the slot stays intact.
Test 3 - Cancel an appointment and check the schedule.
Input
Choose an option: 4
Appointment ID: 1
Expected Output
Appointment 1 cancelled.
Then run:
Choose an option: 5
Doctor ID: 1
Date (YYYY-MM-DD): 2026-08-03
Expected Output
Schedule for Henry White on 2026-08-03:
09:00 #1 John Smith (cancelled)
10:00 #2 Maria Garcia (booked)
Explanation
Cancelling updates the status to cancelled instead of deleting the row, so appointment 1 still appears in the schedule with its new status. The 09:00 slot is now free for a new booking because the availability check only counts rows with status booked.
Test 4 - Run the patients per department report.
Input
Choose an option: 7
Expected Output
--- Patients per Department ---
Department Patients
Cardiology 3
Pediatrics 2
General Medicine 2
Orthopedics 1
Explanation
Cardiology counts John, Maria, and Nina across its two doctors. Pediatrics counts Lucy and Omar. General Medicine counts Robert and Grace, whose appointment is cancelled but still connects her to the department. Orthopedics counts only Ahmed. A department with no doctors would appear with a count of zero because the report uses LEFT JOINs.
Problem: ModuleNotFoundError: No module named 'mysql.connector'
Reason: The connector package is not installed in the Python environment you are using.
Solution: Run pip install mysql-connector-python in the same terminal where you run python main.py. If you use multiple Python installations, check that pip and python point at the same one.
Problem: A booked appointment or new patient "disappears" after the program exits.
Reason: conn.commit() was never called. With the connector, autocommit is off by default, so INSERT and UPDATE statements only become permanent after commit().
Solution: Call conn.commit() after the DML statements inside the try block. Every write function in this project does this, so check you did not remove it while experimenting.
Problem: mysql.connector.errors.IntegrityError: 1452 ... a foreign key constraint fails when booking an appointment.
Reason: The patient ID or doctor ID you typed does not exist in its table, so the foreign key rejects the INSERT.
Solution: Confirm the IDs exist before booking. The book_appointment function checks both and prints a readable message; query the patients and doctors tables directly if you are unsure of an ID.
Problem: ERROR 1050 (42S01): Table 'departments' already exists when re-running database.sql.
Reason: The tables were created on a previous run, and the file does not drop them first.
Solution: Run DROP DATABASE hospital_db; before loading the file again, then re-run mysql -u root -p < database.sql. The seed data resets to the original state.
Problem: mysql.connector.errors.DatabaseError: 1045 (28000): Access denied for user 'root'@'localhost' or 2003 Can't connect to MySQL server on 'localhost'.
Reason: A 1045 error means the password in db_connection.py is wrong. A 2003 error means the MySQL service is not running.
Solution: For 1045, use the password that works in mysql -u root -p. For 2003, start the MySQL service (for example with net start mysql80 on Windows) and try again.
Problem: A doctor ends up with two appointments in the same time slot.
Reason: The availability check was removed, or the COUNT query does not filter by status = 'booked', or it checks the wrong doctor. Cancelled and completed rows then still block the slot.
Solution: Keep the check in book_appointment that counts rows where doctor_id, appointment_date, time_slot all match and the status is still booked. Reject the booking when that count is greater than zero.
completed after the visit.Add a room_assignments table linking a patient to a room and bed, with admission and discharge dates. Write a menu option to admit a patient.
Extend cancel_appointment so that cancelling an appointment that already has a medical record is rejected with a warning.
Write a report that counts appointments per doctor per month using the MONTH() function, and show only months with five or more appointments.
Add a symptom field to the appointments table and write a query that lists every patient who booked with a given symptom.
Add follow-up tracking: when a medical record is added, prompt for a follow-up date, store it, and list all follow-ups due within the next seven days.
Save your progress and earn XP for completing tutorials.
Keep learning
Technology
MySQL
Lesson group
Real-World Projects
Progress
50% complete