Preparing your learning space...
75% through Real-World Projects tutorials
A command-line application that manages a small school: students, teachers, classes, subjects, and marks. You pick options from a menu, answer a few questions, and the program reads from or writes to a MySQL database.
This tutorial builds the complete project step by step, from the database schema to the final report. Every feature is one small function, so you can follow along and extend it later.
A school office tracks a lot of data: new students, new teachers, marks recorded every term, and report cards requested by parents. This project replaces the paper trail with a small database-backed program.
The program is menu-driven and runs in the terminal. You choose an action, type the details, and the program stores or fetches data through MySQL. It is the kind of tool a small school, a coaching centre, or a tuition office could use every day.
What you will learn:
%s placeholders to prevent SQL injection.commit() and rollback().mysql-connector-python package, installed once with pip.root).input(), if, and loops.CREATE TABLE, INSERT, SELECT. You will also meet JOIN, AVG, and GROUP BY in this project.One-time setup before coding:
pip install mysql-connector-pythonmysql -u root -pIf you skip step 1, your first run will fail with ModuleNotFoundError: No module named 'mysql.connector'. That error is covered in Common Errors.
school-management-system/
|-- database.sql
|-- db_connection.py
|-- main.py
|-- requirements.txt
|-- README.md
database.sql creates the school_db database, all five tables, and fills them with seed data. Run it once with the mysql client.db_connection.py contains one helper, get_connection(), that returns a MySQL connection.main.py holds the menu loop and every business operation as its own function.requirements.txt lists the Python package the project depends on.README.md gives a short setup guide for anyone who downloads the project.All five files sit in the same folder, so main.py can import db_connection without any extra configuration.
A set of tables that model a school: classes, students, teachers, subjects, and marks. Each table stores one kind of thing, and foreign keys connect related rows.
The schema is the foundation of the whole project. If the tables are wrong, every query that comes later will be painful to fix. Foreign keys guarantee that a student belongs to a real class and a mark belongs to a real student and a real subject, so bad rows cannot be inserted by accident.
Five tables with these relationships:
classes 1 ---- * students
teachers 1 ---- * subjects
students 1 ---- * marks
subjects 1 ---- * marks
The * side is the "many" side. One class has many students, one subject has many marks, and so on. The foreign key lives on the many side and stores the id of the row on the one side.
A mark is uniquely identified by the combination of student, subject, and term, so those three columns get a UNIQUE constraint. That stops the same mark from being recorded twice.
One design choice to notice: subjects.teacher_id has ON DELETE SET NULL. If a teacher leaves, the subject stays but no longer has a teacher. By contrast, marks use ON DELETE CASCADE, so deleting a student also removes their marks instead of leaving orphans.
The first part of database.sql: the school_db database and its five tables.
MySQL will not accept any INSERT or SELECT until the database and tables exist. Running this file once creates the complete storage layer, so the Python code only has to work with the tables.
Save this as database.sql.
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;
CREATE TABLE IF NOT EXISTS classes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
class_id INT NOT NULL,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS marks (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
exam_term VARCHAR(20) NOT NULL,
score INT NOT NULL,
CHECK (score BETWEEN 0 AND 100),
UNIQUE KEY (student_id, subject_id, exam_term),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);
CREATE DATABASE IF NOT EXISTS means you can run the file more than once without an error. Every table uses AUTO_INCREMENT for its id primary key, so MySQL picks the next number for each new row.
classes.name and teachers.email are UNIQUE, which stops two classes with the same name or two teachers with the same email. subjects.teacher_id is nullable, because a new subject may start without a teacher and the "assign teacher to subject" operation fills it in later.
The CHECK (score BETWEEN 0 AND 100) constraint rejects marks outside the valid range before they ever reach the database. The marks table has two foreign keys and a UNIQUE KEY on (student_id, subject_id, exam_term), so one student has at most one mark per subject per term.
ON DELETE CASCADE removes child rows when the parent is deleted: deleting a student removes their marks, and deleting a class removes its students. ON DELETE SET NULL keeps a subject when its teacher is deleted but clears the teacher reference.
INSERT statements that give the tables a first set of realistic rows, so the program is useful immediately after setup.
Testing against empty tables is awkward. With seed data you can run every menu option right away, see meaningful output, and compare your results with the Testing section later in the tutorial.
Append this to database.sql.
INSERT INTO classes (name) VALUES
('6A'), ('6B'), ('7A'), ('8A');
INSERT INTO teachers (name, email) VALUES
('Rahul Sharma', 'rahul.sharma@school.edu'),
('Priya Patel', 'priya.patel@school.edu'),
('John Walker', 'john.walker@school.edu'),
('Sara Khan', 'sara.khan@school.edu');
INSERT INTO subjects (name, teacher_id) VALUES
('Mathematics', 1),
('Science', 2),
('English', 3),
('Computer Science', NULL);
INSERT INTO students (name, class_id) VALUES
('Aarav Verma', 1),
('Meera Iyer', 1),
('Kabir Singh', 1),
('Ananya Rao', 2),
('Dev Patel', 2),
('Ishaan Gupta', 3),
('Navya Reddy', 3),
('Rohan Das', 4);
INSERT INTO marks (student_id, subject_id, exam_term, score) VALUES
-- Term 1, class 6A
(1, 1, 'Term 1', 88), (1, 2, 'Term 1', 76), (1, 3, 'Term 1', 82),
(2, 1, 'Term 1', 92), (2, 2, 'Term 1', 88), (2, 3, 'Term 1', 90),
(3, 1, 'Term 1', 65), (3, 2, 'Term 1', 58), (3, 3, 'Term 1', 71),
-- Term 1, class 6B
(4, 1, 'Term 1', 80), (4, 2, 'Term 1', 74), (4, 3, 'Term 1', 85),
(5, 1, 'Term 1', 55), (5, 2, 'Term 1', 61), (5, 3, 'Term 1', 66),
-- Term 1, classes 7A and 8A
(6, 1, 'Term 1', 78), (6, 2, 'Term 1', 70), (6, 3, 'Term 1', 80),
(7, 1, 'Term 1', 90), (7, 2, 'Term 1', 92), (7, 3, 'Term 1', 86),
(8, 1, 'Term 1', 72), (8, 2, 'Term 1', 68), (8, 3, 'Term 1', 74),
-- Term 2, classes 6A and 6B
(1, 1, 'Term 2', 90), (1, 2, 'Term 2', 80), (1, 3, 'Term 2', 85),
(2, 1, 'Term 2', 95), (2, 2, 'Term 2', 84), (2, 3, 'Term 2', 93),
(3, 1, 'Term 2', 70), (3, 2, 'Term 2', 62), (3, 3, 'Term 2', 75),
(4, 1, 'Term 2', 84), (4, 2, 'Term 2', 78), (4, 3, 'Term 2', 88),
(5, 1, 'Term 2', 60), (5, 2, 'Term 2', 58), (5, 3, 'Term 2', 70),
-- Term 2, class 7A
(6, 1, 'Term 2', 82), (6, 2, 'Term 2', 74), (6, 3, 'Term 2', 76),
(7, 1, 'Term 2', 88), (7, 2, 'Term 2', 90), (7, 3, 'Term 2', 89);
Each INSERT lists the column names, then a set of value rows. The seed data covers four classes, four teachers, and four subjects.
Computer Science has a NULL teacher on purpose. That gives the "assign a teacher to a subject" feature something to work with on the first run.
The marks table has more rows than the other tables, which is normal: it is the fact table. Every student has a mark for Mathematics, Science, and English, and most have two terms. The numbers in the marks rows are the id values of the students and subjects created above. Student 1 is Aarav Verma, subject 1 is Mathematics, so (1, 1, 'Term 1', 88) means "Aarav scored 88 in Mathematics in Term 1."
The comment lines starting with -- label groups of rows and make the file readable. MySQL ignores them.
db_connection.py, a small module with one function that returns a MySQL connection.
Every menu operation needs a connection. Putting mysql.connector.connect(...) in one place means you change your password or database name in exactly one file, and every other file picks it up.
Save this as db_connection.py.
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="school_db"
)
import mysql.connector gives access to the connector package. If this line fails, the package is not installed; see Common Errors.
get_connection() returns a live connection object. The caller, usually main.py, uses it to create a cursor and run queries. Replace your_password with the real MySQL password you use. Everything else, including the default port 3306, is the MySQL default.
database="school_db" selects the database automatically, so the code never has to write USE school_db.
The skeleton of main.py: a menu loop, and the first operation, add_student.
The menu is the entry point of the program. Every feature you build later hangs off this same loop, so it is worth setting up correctly first. Adding a student is the smallest operation and a good way to learn the cursor.execute() pattern used everywhere else.
Save this as main.py and grow it step by step.
import mysql.connector
import db_connection
def show_menu():
print("\n=== School Management System ===")
print("1. Add a student")
print("2. Add a teacher")
print("3. Create a class")
print("4. Assign a teacher to a subject")
print("5. Record a mark")
print("6. Generate a report card")
print("7. List all students of a class")
print("8. Subject-wise class average")
print("9. Top 3 students of a class")
print("0. Exit")
print("=================================")
def add_student(cursor, conn):
print("\n--- Add a Student ---")
name = input("Student name: ").strip()
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute("SELECT id FROM classes WHERE name = %s", (class_name,))
class_row = cursor.fetchone()
if class_row is None:
print(f"No class called '{class_name}' exists. Create it first.")
return
cursor.execute(
"INSERT INTO students (name, class_id) VALUES (%s, %s)",
(name, class_row["id"])
)
conn.commit()
print(f"Student '{name}' added to class {class_name}.")
def main():
conn = db_connection.get_connection()
cursor = conn.cursor(dictionary=True)
while True:
show_menu()
choice = input("Enter your choice: ").strip()
if choice == "1":
add_student(cursor, conn)
elif choice == "0":
break
else:
print("Invalid choice. Try again.")
cursor.close()
conn.close()
print("Goodbye!")
if __name__ == "__main__":
main()
conn.cursor(dictionary=True) returns rows as dictionaries, so inside the code you write class_row["id"] instead of class_row[0]. That makes the code much easier to read.
show_menu() only prints text. The main() loop compares the user's input to the menu numbers and calls the matching function. Any new feature is one more elif.
add_student first looks up the class id by name. fetchone() returns one row, or None if the class does not exist. The check on class_row is None gives the user a clear message instead of a confusing database error.
The insert uses %s placeholders and passes values as a tuple. Never build SQL by putting a variable directly into the string. Parameterized queries make MySQL treat the input as data, which blocks SQL injection.
conn.commit() is required. Without it the insert stays in an uncommitted transaction and disappears when the program ends.
Three more operations: add_teacher, create_class, and assign_subject.
These are the setup operations a school needs before marks make sense. A class must exist before students can join it, and a subject must have a teacher before the timetable is complete.
Add these functions to main.py and wire them into the menu.
def add_teacher(cursor, conn):
print("\n--- Add a Teacher ---")
name = input("Teacher name: ").strip()
email = input("Email: ").strip()
try:
cursor.execute(
"INSERT INTO teachers (name, email) VALUES (%s, %s)",
(name, email)
)
conn.commit()
print(f"Teacher '{name}' added.")
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("A teacher with that email already exists.")
def create_class(cursor, conn):
print("\n--- Create a Class ---")
name = input("New class name (e.g., 7B): ").strip()
try:
cursor.execute("INSERT INTO classes (name) VALUES (%s)", (name,))
conn.commit()
print(f"Class {name} created.")
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("That class name already exists.")
def assign_subject(cursor, conn):
print("\n--- Assign a Teacher to a Subject ---")
cursor.execute("SELECT id, name FROM subjects WHERE teacher_id IS NULL")
subjects = cursor.fetchall()
if not subjects:
print("Every subject already has a teacher.")
return
for s in subjects:
print(f"{s['id']}. {s['name']}")
subject_id = input("Subject ID: ").strip()
teacher_email = input("Teacher email: ").strip()
cursor.execute("SELECT id, name FROM teachers WHERE email = %s", (teacher_email,))
teacher = cursor.fetchone()
if teacher is None:
print("No teacher found with that email.")
return
cursor.execute(
"UPDATE subjects SET teacher_id = %s WHERE id = %s",
(teacher["id"], subject_id)
)
conn.commit()
print(f"Subject assigned to {teacher['name']}.")
add_teacher and create_class wrap the insert in try/except mysql.connector.errors.IntegrityError. The UNIQUE constraints on teachers.email and classes.name raise this error when a duplicate is entered, and the except turns it into a friendly message instead of a crash.
Notice the conn.rollback() inside except. After a failed statement, the connection is in a broken state; rolling back clears it so the next operation starts fresh.
assign_subject lists the subjects with no teacher, so the user does not have to remember ids. It then looks up the teacher by email and runs an UPDATE.
Because teacher_id is nullable, "no teacher" is simply a NULL in the column. The query WHERE teacher_id IS NULL finds exactly those rows. NULL comparisons need IS NULL, not = NULL.
All three functions follow the same pattern: ask for input, validate with a lookup, run the write, and commit.
The record_mark function, which saves one mark after checking that the student, subject, and score are valid.
Recording a mark is a multi-step operation: look up the student, look up the subject, validate the score, then insert. If any step fails, none of it should be saved. A transaction groups these steps so they either all succeed (commit) or all roll back.
Add this function to main.py and wire it into the menu.
def record_mark(cursor, conn):
print("\n--- Record a Mark ---")
student_name = input("Student name: ").strip()
subject_name = input("Subject name: ").strip()
exam_term = input("Exam term (e.g., Term 1): ").strip()
try:
score = int(input("Score (0-100): ").strip())
cursor.execute("SELECT id FROM students WHERE name = %s", (student_name,))
student = cursor.fetchone()
cursor.execute("SELECT id FROM subjects WHERE name = %s", (subject_name,))
subject = cursor.fetchone()
if student is None:
raise ValueError("No student with that name.")
if subject is None:
raise ValueError("No subject with that name.")
if score < 0 or score > 100:
raise ValueError("Score must be between 0 and 100.")
cursor.execute(
"INSERT INTO marks (student_id, subject_id, exam_term, score) "
"VALUES (%s, %s, %s, %s)",
(student["id"], subject["id"], exam_term, score)
)
conn.commit()
print(f"Mark recorded: {student_name}, {subject_name}, {exam_term}, {score}.")
except ValueError as e:
conn.rollback()
print(e)
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("A mark for that student, subject, and term already exists.")
score = int(...) converts the user's text into a number. If they type something like "abc", Python raises a ValueError that the except ValueError block catches and reports.
The two lookups confirm that the student and subject exist. The code raises ValueError with a clear message when either is missing, and that message is printed by the except block.
All steps sit inside one try, so commit() only runs when every check passes. If anything raises an exception, rollback() undoes whatever the failed statements did.
The insert still uses %s placeholders for all four values. The database also enforces the 0-100 range with its CHECK constraint and the one-mark-per-term rule with its UNIQUE key, so even a buggy caller cannot store bad rows.
The two except blocks handle the two realistic failure modes: bad input from the user and a duplicate mark.
The reporting features: report_card, list_class_students, subject_class_average, and top_students.
Recording data is only half the job. A school wants answers: what is a student's average, who tops the class, how is the class doing in Science? These functions join the tables and turn raw marks into useful information.
Add these four functions and the grade helper to main.py, then wire them into the menu.
def grade_for(average):
if average >= 85:
return "A"
if average >= 70:
return "B"
if average >= 50:
return "C"
return "F"
def report_card(cursor, conn):
print("\n--- Report Card ---")
student_name = input("Student name: ").strip()
cursor.execute("SELECT id FROM students WHERE name = %s", (student_name,))
student = cursor.fetchone()
if student is None:
print("No student with that name.")
return
cursor.execute(
"""SELECT m.exam_term, sub.name AS subject_name, m.score
FROM marks m
JOIN subjects sub ON m.subject_id = sub.id
WHERE m.student_id = %s
ORDER BY m.exam_term, sub.name""",
(student["id"],)
)
marks = cursor.fetchall()
if not marks:
print("No marks recorded for this student yet.")
return
total = 0
for m in marks:
print(f"{m['exam_term']} {m['subject_name']}: {m['score']}")
total += m["score"]
average = total / len(marks)
print(f"Average: {average:.1f}")
print(f"Letter grade: {grade_for(average)}")
def list_class_students(cursor, conn):
print("\n--- Students of a Class ---")
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute(
"""SELECT s.name
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.name = %s
ORDER BY s.name""",
(class_name,)
)
students = cursor.fetchall()
if not students:
print(f"No students found in class {class_name}.")
return
for i, s in enumerate(students, 1):
print(f"{i}. {s['name']}")
def subject_class_average(cursor, conn):
print("\n--- Subject-wise Class Average ---")
class_name = input("Class name (e.g., 6A): ").strip()
subject_name = input("Subject name: ").strip()
cursor.execute(
"""SELECT AVG(m.score) AS avg_score
FROM marks m
JOIN students s ON m.student_id = s.id
JOIN classes c ON s.class_id = c.id
JOIN subjects sub ON m.subject_id = sub.id
WHERE c.name = %s AND sub.name = %s""",
(class_name, subject_name)
)
row = cursor.fetchone()
if row["avg_score"] is None:
print("No marks found for that class and subject.")
return
print(f"Average score in {subject_name} for class {class_name}: {row['avg_score']:.1f}")
def top_students(cursor, conn):
print("\n--- Top 3 Students of a Class ---")
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute(
"""SELECT s.name, AVG(m.score) AS avg_score
FROM students s
JOIN classes c ON s.class_id = c.id
JOIN marks m ON m.student_id = s.id
WHERE c.name = %s
GROUP BY s.id
ORDER BY avg_score DESC
LIMIT 3""",
(class_name,)
)
students = cursor.fetchall()
if not students:
print("No marks available for that class.")
return
for i, s in enumerate(students, 1):
print(f"{i}. {s['name']} - average {s['avg_score']:.1f}")
grade_for is plain Python. Letter grades are business logic, and keeping them in Python makes them trivial to change later without touching the database.
report_card joins marks to subjects so each row shows a subject name instead of a bare id. The ORDER BY groups the marks by term and subject, and the average is total / len(marks).
list_class_students uses a JOIN on classes so the filter can be the class name. enumerate(students, 1) adds numbering starting at 1.
subject_class_average uses AVG(). When there are no matching marks, AVG returns NULL, which the is None check catches; otherwise the number is formatted to one decimal.
top_students groups by student, takes the average, sorts descending, and keeps the top 3 with LIMIT 3. Grouping on s.id rather than s.name avoids problems when two students share a name.
All four read-only functions only run SELECT statements, so they never need commit(). Only the write operations in earlier steps call it.
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;
CREATE TABLE IF NOT EXISTS classes (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
class_id INT NOT NULL,
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS marks (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
exam_term VARCHAR(20) NOT NULL,
score INT NOT NULL,
CHECK (score BETWEEN 0 AND 100),
UNIQUE KEY (student_id, subject_id, exam_term),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);
INSERT INTO classes (name) VALUES
('6A'), ('6B'), ('7A'), ('8A');
INSERT INTO teachers (name, email) VALUES
('Rahul Sharma', 'rahul.sharma@school.edu'),
('Priya Patel', 'priya.patel@school.edu'),
('John Walker', 'john.walker@school.edu'),
('Sara Khan', 'sara.khan@school.edu');
INSERT INTO subjects (name, teacher_id) VALUES
('Mathematics', 1),
('Science', 2),
('English', 3),
('Computer Science', NULL);
INSERT INTO students (name, class_id) VALUES
('Aarav Verma', 1),
('Meera Iyer', 1),
('Kabir Singh', 1),
('Ananya Rao', 2),
('Dev Patel', 2),
('Ishaan Gupta', 3),
('Navya Reddy', 3),
('Rohan Das', 4);
INSERT INTO marks (student_id, subject_id, exam_term, score) VALUES
-- Term 1, class 6A
(1, 1, 'Term 1', 88), (1, 2, 'Term 1', 76), (1, 3, 'Term 1', 82),
(2, 1, 'Term 1', 92), (2, 2, 'Term 1', 88), (2, 3, 'Term 1', 90),
(3, 1, 'Term 1', 65), (3, 2, 'Term 1', 58), (3, 3, 'Term 1', 71),
-- Term 1, class 6B
(4, 1, 'Term 1', 80), (4, 2, 'Term 1', 74), (4, 3, 'Term 1', 85),
(5, 1, 'Term 1', 55), (5, 2, 'Term 1', 61), (5, 3, 'Term 1', 66),
-- Term 1, classes 7A and 8A
(6, 1, 'Term 1', 78), (6, 2, 'Term 1', 70), (6, 3, 'Term 1', 80),
(7, 1, 'Term 1', 90), (7, 2, 'Term 1', 92), (7, 3, 'Term 1', 86),
(8, 1, 'Term 1', 72), (8, 2, 'Term 1', 68), (8, 3, 'Term 1', 74),
-- Term 2, classes 6A and 6B
(1, 1, 'Term 2', 90), (1, 2, 'Term 2', 80), (1, 3, 'Term 2', 85),
(2, 1, 'Term 2', 95), (2, 2, 'Term 2', 84), (2, 3, 'Term 2', 93),
(3, 1, 'Term 2', 70), (3, 2, 'Term 2', 62), (3, 3, 'Term 2', 75),
(4, 1, 'Term 2', 84), (4, 2, 'Term 2', 78), (4, 3, 'Term 2', 88),
(5, 1, 'Term 2', 60), (5, 2, 'Term 2', 58), (5, 3, 'Term 2', 70),
-- Term 2, class 7A
(6, 1, 'Term 2', 82), (6, 2, 'Term 2', 74), (6, 3, 'Term 2', 76),
(7, 1, 'Term 2', 88), (7, 2, 'Term 2', 90), (7, 3, 'Term 2', 89);
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="school_db"
)
import mysql.connector
import db_connection
def show_menu():
print("\n=== School Management System ===")
print("1. Add a student")
print("2. Add a teacher")
print("3. Create a class")
print("4. Assign a teacher to a subject")
print("5. Record a mark")
print("6. Generate a report card")
print("7. List all students of a class")
print("8. Subject-wise class average")
print("9. Top 3 students of a class")
print("0. Exit")
print("=================================")
def add_student(cursor, conn):
print("\n--- Add a Student ---")
name = input("Student name: ").strip()
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute("SELECT id FROM classes WHERE name = %s", (class_name,))
class_row = cursor.fetchone()
if class_row is None:
print(f"No class called '{class_name}' exists. Create it first.")
return
cursor.execute(
"INSERT INTO students (name, class_id) VALUES (%s, %s)",
(name, class_row["id"])
)
conn.commit()
print(f"Student '{name}' added to class {class_name}.")
def add_teacher(cursor, conn):
print("\n--- Add a Teacher ---")
name = input("Teacher name: ").strip()
email = input("Email: ").strip()
try:
cursor.execute(
"INSERT INTO teachers (name, email) VALUES (%s, %s)",
(name, email)
)
conn.commit()
print(f"Teacher '{name}' added.")
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("A teacher with that email already exists.")
def create_class(cursor, conn):
print("\n--- Create a Class ---")
name = input("New class name (e.g., 7B): ").strip()
try:
cursor.execute("INSERT INTO classes (name) VALUES (%s)", (name,))
conn.commit()
print(f"Class {name} created.")
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("That class name already exists.")
def assign_subject(cursor, conn):
print("\n--- Assign a Teacher to a Subject ---")
cursor.execute("SELECT id, name FROM subjects WHERE teacher_id IS NULL")
subjects = cursor.fetchall()
if not subjects:
print("Every subject already has a teacher.")
return
for s in subjects:
print(f"{s['id']}. {s['name']}")
subject_id = input("Subject ID: ").strip()
teacher_email = input("Teacher email: ").strip()
cursor.execute("SELECT id, name FROM teachers WHERE email = %s", (teacher_email,))
teacher = cursor.fetchone()
if teacher is None:
print("No teacher found with that email.")
return
cursor.execute(
"UPDATE subjects SET teacher_id = %s WHERE id = %s",
(teacher["id"], subject_id)
)
conn.commit()
print(f"Subject assigned to {teacher['name']}.")
def record_mark(cursor, conn):
print("\n--- Record a Mark ---")
student_name = input("Student name: ").strip()
subject_name = input("Subject name: ").strip()
exam_term = input("Exam term (e.g., Term 1): ").strip()
try:
score = int(input("Score (0-100): ").strip())
cursor.execute("SELECT id FROM students WHERE name = %s", (student_name,))
student = cursor.fetchone()
cursor.execute("SELECT id FROM subjects WHERE name = %s", (subject_name,))
subject = cursor.fetchone()
if student is None:
raise ValueError("No student with that name.")
if subject is None:
raise ValueError("No subject with that name.")
if score < 0 or score > 100:
raise ValueError("Score must be between 0 and 100.")
cursor.execute(
"INSERT INTO marks (student_id, subject_id, exam_term, score) "
"VALUES (%s, %s, %s, %s)",
(student["id"], subject["id"], exam_term, score)
)
conn.commit()
print(f"Mark recorded: {student_name}, {subject_name}, {exam_term}, {score}.")
except ValueError as e:
conn.rollback()
print(e)
except mysql.connector.errors.IntegrityError:
conn.rollback()
print("A mark for that student, subject, and term already exists.")
def grade_for(average):
if average >= 85:
return "A"
if average >= 70:
return "B"
if average >= 50:
return "C"
return "F"
def report_card(cursor, conn):
print("\n--- Report Card ---")
student_name = input("Student name: ").strip()
cursor.execute("SELECT id FROM students WHERE name = %s", (student_name,))
student = cursor.fetchone()
if student is None:
print("No student with that name.")
return
cursor.execute(
"""SELECT m.exam_term, sub.name AS subject_name, m.score
FROM marks m
JOIN subjects sub ON m.subject_id = sub.id
WHERE m.student_id = %s
ORDER BY m.exam_term, sub.name""",
(student["id"],)
)
marks = cursor.fetchall()
if not marks:
print("No marks recorded for this student yet.")
return
total = 0
for m in marks:
print(f"{m['exam_term']} {m['subject_name']}: {m['score']}")
total += m["score"]
average = total / len(marks)
print(f"Average: {average:.1f}")
print(f"Letter grade: {grade_for(average)}")
def list_class_students(cursor, conn):
print("\n--- Students of a Class ---")
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute(
"""SELECT s.name
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.name = %s
ORDER BY s.name""",
(class_name,)
)
students = cursor.fetchall()
if not students:
print(f"No students found in class {class_name}.")
return
for i, s in enumerate(students, 1):
print(f"{i}. {s['name']}")
def subject_class_average(cursor, conn):
print("\n--- Subject-wise Class Average ---")
class_name = input("Class name (e.g., 6A): ").strip()
subject_name = input("Subject name: ").strip()
cursor.execute(
"""SELECT AVG(m.score) AS avg_score
FROM marks m
JOIN students s ON m.student_id = s.id
JOIN classes c ON s.class_id = c.id
JOIN subjects sub ON m.subject_id = sub.id
WHERE c.name = %s AND sub.name = %s""",
(class_name, subject_name)
)
row = cursor.fetchone()
if row["avg_score"] is None:
print("No marks found for that class and subject.")
return
print(f"Average score in {subject_name} for class {class_name}: {row['avg_score']:.1f}")
def top_students(cursor, conn):
print("\n--- Top 3 Students of a Class ---")
class_name = input("Class name (e.g., 6A): ").strip()
cursor.execute(
"""SELECT s.name, AVG(m.score) AS avg_score
FROM students s
JOIN classes c ON s.class_id = c.id
JOIN marks m ON m.student_id = s.id
WHERE c.name = %s
GROUP BY s.id
ORDER BY avg_score DESC
LIMIT 3""",
(class_name,)
)
students = cursor.fetchall()
if not students:
print("No marks available for that class.")
return
for i, s in enumerate(students, 1):
print(f"{i}. {s['name']} - average {s['avg_score']:.1f}")
def main():
conn = db_connection.get_connection()
cursor = conn.cursor(dictionary=True)
while True:
show_menu()
choice = input("Enter your choice: ").strip()
if choice == "1":
add_student(cursor, conn)
elif choice == "2":
add_teacher(cursor, conn)
elif choice == "3":
create_class(cursor, conn)
elif choice == "4":
assign_subject(cursor, conn)
elif choice == "5":
record_mark(cursor, conn)
elif choice == "6":
report_card(cursor, conn)
elif choice == "7":
list_class_students(cursor, conn)
elif choice == "8":
subject_class_average(cursor, conn)
elif choice == "9":
top_students(cursor, conn)
elif choice == "0":
break
else:
print("Invalid choice. Try again.")
cursor.close()
conn.close()
print("Goodbye!")
if __name__ == "__main__":
main()
mysql-connector-python
# School Management System
A command-line application that manages students, teachers, classes, subjects,
and marks for a school, backed by MySQL.
## Setup
1. pip install mysql-connector-python
2. mysql -u root -p < database.sql
3. Open db_connection.py and set your MySQL password.
4. python main.py
## Features
- Add students, teachers, and classes
- Assign teachers to subjects
- Record marks and generate report cards
- List class rosters, subject averages, and top students
pip install mysql-connector-python
This downloads and installs the MySQL driver that the Python code imports.
mysql -u root -p < database.sql
This runs the whole SQL file: it creates school_db, builds the five tables, and inserts the seed rows. You will be asked for your MySQL password.
python main.py
This starts the menu. On your first run you should see:
=== School Management System ===
1. Add a student
2. Add a teacher
3. Create a class
4. Assign a teacher to a subject
5. Record a mark
6. Generate a report card
7. List all students of a class
8. Subject-wise class average
9. Top 3 students of a class
0. Exit
=================================
Enter your choice:
If the menu appears, the connection is working. Choose option 6 and enter Meera Iyer to confirm the seed data was loaded correctly.
These test cases each start from a fresh database loaded with database.sql. The 0 at the end of each input list exits the program.
Test 1 – Add a student to an existing class
Input:
1
Zara Sheikh
6A
0
Expected Output:
Student 'Zara Sheikh' added to class 6A.
Explanation: The function looks up class 6A, finds its id, inserts the new student with that class_id, and commits. The fetchone() lookup is what prevents adding a student to a class that does not exist.
Test 2 – Record a mark and generate a report card
Input:
5
Meera Iyer
English
Term 3
96
6
Meera Iyer
0
Expected Output:
Mark recorded: Meera Iyer, English, Term 3, 96.
Term 1 English: 90
Term 1 Mathematics: 92
Term 1 Science: 88
Term 2 English: 93
Term 2 Mathematics: 95
Term 2 Science: 84
Term 3 English: 96
Average: 91.1
Letter grade: A
Explanation: The mark insert passes validation and commits. The report card then joins marks with subjects, prints all seven marks, and computes the average in Python: (90+92+88+93+95+84+96) / 7 = 91.1, which is above 85, so the grade is A.
Test 3 – Subject-wise class average
Input:
8
6A
Mathematics
0
Expected Output:
Average score in Mathematics for class 6A: 83.3
Explanation: The query joins marks to students and classes, filters by class 6A and subject Mathematics, and averages the six scores (88, 92, 65, 90, 95, 70) stored in the seed data, giving 83.3.
Test 4 – Top 3 students of a class
Input:
9
6A
0
Expected Output:
1. Meera Iyer - average 90.3
2. Aarav Verma - average 83.5
3. Kabir Singh - average 66.8
Explanation: The query groups marks by student for class 6A, averages them, sorts descending, and keeps the top three rows. Because Meera Iyer's average is highest, she is listed first.
Problem:
ModuleNotFoundError: No module named 'mysql.connector'
Reason:
The mysql-connector-python package is not installed, or pip installed it for a different Python installation.
Solution:
Run pip install mysql-connector-python. If that still fails, try python -m pip install mysql-connector-python, which installs the package for the exact Python that will run main.py.
Problem: A record appears to be saved, but it is gone when the program restarts.
Reason:
conn.commit() was never called after INSERT or UPDATE. MySQL keeps the change in an open transaction that is discarded when the connection closes.
Solution:
Call conn.commit() after every write operation. When several statements must succeed together, commit only after all of them, and call conn.rollback() in an except block.
Problem:
Cannot add or update a child row: a foreign key constraint fails
Reason:
The program tried to insert a row whose foreign key does not exist, for example a student whose class_id does not match any class.
Solution:
Look up the parent row first with SELECT and check that it exists before inserting. This is exactly what the class_row is None check in add_student does.
Problem:
Table 'school_db.classes' already exists when running database.sql a second time.
Reason:
The schema was written without IF NOT EXISTS, or the database was created without IF NOT EXISTS.
Solution:
Use CREATE TABLE IF NOT EXISTS in the schema file. To start from scratch, drop the database first with DROP DATABASE IF EXISTS school_db; and then re-run the file.
Problem:
Duplicate entry ... for key 'student_id'
Reason:
A mark for that student, subject, and term already exists, so the UNIQUE constraint on those three columns rejected the new row.
Solution:
That is the intended protection. The program catches IntegrityError and prints "A mark for that student, subject, and term already exists." To allow a correction, add an UPDATE option instead of inserting a second row.
Problem:
1045 (28000): Access denied or 2003: Can't connect to MySQL server on 'localhost'
Reason:
Either the password in db_connection.py does not match the MySQL password (error 1045), or the MySQL server is not running (error 2003).
Solution:
Test the login first with mysql -u root -p. If that works, fix the password in db_connection.py. If the server is down, start the MySQL service: check the Services panel on Windows, or run sudo systemctl start mysql on Linux.
UPDATE and DELETE option for marks so a wrong entry can be corrected.attendance table and record daily attendance per student.csv module.class_averages report that shows, for every class, the average of all subjects combined, ordered best to worst.term_report function that takes a term name (for example "Term 1") and shows the rank list of a class for just that term.search_student function that accepts a partial name (for example "ar") and lists every matching student along with their class.Save your progress and earn XP for completing tutorials.
Technology
MySQL
Lesson group
Real-World Projects
Progress
75% complete