Preparing your learning space...
8% through Real-World Projects tutorials
A Student Management System stores student and course records in a MySQL database and lets you manage everything from the command line. You will build a menu-driven Python program that adds, edits, and removes students, enrols them in courses, and prints simple reports. Along the way you will work with foreign keys, joins, transactions, and parameterized queries.
This is a small university administration tool. A school office needs to keep track of students, the courses on offer, and which student is enrolled in which course. The program runs in the terminal and talks to a MySQL database, so every change is stored permanently.
Students work with a real many-to-many relationship: one student can take several courses, and one course can hold several students. That relationship is stored in a separate enrollments table, which is the core of the project.
Students will learn how to design a normalized schema, write parameterized queries, use transactions for multi-step operations, and combine tables with JOIN to build reports.
sudo apt install mysql-server)input(), try/except, dictionaries or tuplesSELECT, INSERT, UPDATE, DELETEOne-time setup before you start coding:
pip install mysql-connector-python
Make sure the MySQL service is running, and note the username and password you use to log in (the examples use root). You will put those credentials in db_connection.py.
Create the database by loading the schema file (the file database.sql that you build in Step 1):
mysql -u root -p < database.sql
You will be asked for your MySQL password. After that, the student_management database exists with its tables and sample data.
student-management-system/
├── database.sql # creates the database, tables, and seed data
├── db_connection.py # get_connection() helper for MySQL connections
├── main.py # the menu loop and every business operation
├── requirements.txt # lists the Python package to install
└── README.md # short project notes
database.sql holds everything database-related: the CREATE DATABASE statement, the three CREATE TABLE statements, and the INSERT seed data.db_connection.py has one small function that opens a connection. Every operation in main.py imports it, so credentials live in only one place.main.py is the entire program: it prints the menu, reads the user's choice, and calls one function per operation.requirements.txt lets other people install dependencies with pip install -r requirements.txt.README.md is a short page describing the project and how to run it.The student_management database with three tables: students, courses, and enrollments. The enrollments table links a student to a course and carries the date they enrolled.
A student can be enrolled in many courses, and a course can have many students. Storing the link inside either table would force duplicate rows. The standard solution is a junction table with two foreign keys, one pointing at students and one at courses.
Create the file database.sql with this content:
CREATE DATABASE IF NOT EXISTS student_management;
USE student_management;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
roll_number VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(20) NOT NULL UNIQUE,
course_name VARCHAR(100) NOT NULL,
credits INT NOT NULL
);
CREATE TABLE enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE NOT NULL,
UNIQUE KEY unique_enrollment (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
CREATE DATABASE IF NOT EXISTS is safe to run more than once. USE student_management makes that database the active one for every statement that follows.
In students, the roll_number and email columns have UNIQUE, so MySQL refuses duplicate values automatically. That is the same protection you will also enforce in the Python code, and it acts as a safety net.
In enrollments, the composite key UNIQUE KEY unique_enrollment (student_id, course_id) means the same student cannot be enrolled in the same course twice at the database level. The two FOREIGN KEY clauses reference the parent tables, and ON DELETE CASCADE says: if a student row is deleted, delete their enrollment rows too. Without it, MySQL would raise an error whenever you tried to delete a student who is enrolled in anything.
Realistic sample rows for all three tables so the program has something to show on the first run.
An empty database makes every report look the same. Seed data lets you test search, reports, and statistics immediately, and it shows how the tables are meant to be filled.
Append these statements to database.sql:
INSERT INTO students (roll_number, name, email, phone) VALUES
('CS101', 'Aarav Sharma', 'aarav.sharma@example.edu', '9876543210'),
('CS102', 'Priya Patel', 'priya.patel@example.edu', '9876543211'),
('EC201', 'Rahul Verma', 'rahul.verma@example.edu', '9876543212'),
('EC202', 'Sneha Reddy', 'sneha.reddy@example.edu', '9876543213'),
('ME301', 'Kabir Singh', 'kabir.singh@example.edu', '9876543214'),
('ME302', 'Ananya Iyer', 'ananya.iyer@example.edu', '9876543215'),
('CS103', 'Rohan Mehta', 'rohan.mehta@example.edu', '9876543216');
INSERT INTO courses (course_code, course_name, credits) VALUES
('CS101', 'Introduction to Computer Science', 4),
('CS201', 'Data Structures and Algorithms', 4),
('EC101', 'Digital Electronics', 3),
('EC201', 'Signals and Systems', 3),
('ME101', 'Engineering Mechanics', 3),
('ME201', 'Thermodynamics', 4);
INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 1, '2026-01-10'),
(1, 2, '2026-01-10'),
(2, 1, '2026-01-12'),
(2, 3, '2026-01-12'),
(3, 3, '2026-01-14'),
(4, 4, '2026-01-15'),
(5, 5, '2026-01-18'),
(6, 5, '2026-01-20'),
(7, 1, '2026-01-22');
The id columns are AUTO_INCREMENT, so the inserts never mention them. MySQL assigns 1, 2, 3, and so on in the order the rows appear. That is why the enrollments insert can reference student ids 1 through 7 and course ids 1 through 6 — they line up with the order of the earlier inserts.
The roll numbers carry the department prefix (CS for computer science, EC for electronics, ME for mechanical). Course codes follow the same style, which lets the statistics reports look like a real department list. The enrollment dates are spread over January 2026 so the ORDER BY in later reports produces a sensible sequence.
The file db_connection.py with one function, get_connection(), that returns a fresh MySQL connection.
Every operation in main.py needs to connect to the database. If the connection details were repeated in each function, changing a password would mean editing the file in eight places. A single helper keeps the credentials in one spot and gives every function the same starting point.
Create db_connection.py:
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="student_management"
)
mysql.connector.connect() takes the MySQL host, the login credentials, and the database name, and returns a connection object. Change password="your_password" to the real password for your MySQL root user before running the program.
Each call to get_connection() opens a new connection. Opening one per operation is fine for a learning project; in a large application you would reuse connections with a connection pool. The key point is that every function gets its own conn, does its work, and closes it in a finally block, so no connection is left hanging.
The main menu that runs in a while True loop, plus the add_student() function.
A terminal program needs a way to keep asking what to do next. The menu prints the available options, reads a number, and routes to the matching function. add_student() is the first business operation and the template for most of the others, so it is worth building carefully.
Create main.py with the import, the menu skeleton, and add_student():
import mysql.connector
from mysql.connector import Error
from db_connection import get_connection
def add_student():
roll = input("Enter roll number: ").strip()
name = input("Enter full name: ").strip()
email = input("Enter email: ").strip()
phone = input("Enter phone: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO students (roll_number, name, email, phone) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (roll, name, email, phone))
conn.commit()
print(f"Student {name} added with roll number {roll}.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def main():
while True:
print("\n===== STUDENT MANAGEMENT SYSTEM =====")
print("1. Add student")
print("2. View all students")
print("3. Update student")
print("4. Delete student")
print("5. Add course")
print("6. Enroll student in a course")
print("7. List all enrollments")
print("8. Search student")
print("9. Show statistics")
print("0. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_student()
elif choice == "0":
print("Goodbye!")
break
else:
print("Invalid option. Try again.")
if __name__ == "__main__":
main()
The menu prints all nine options even though only option 1 and option 0 work yet. In the next steps you add the elif lines for the rest, one function at a time. At the moment choosing option 2, for example, prints "Invalid option. Try again." because the branch does not exist yet.
The add_student() function collects four values with input(), then runs one INSERT. Two details matter:
%s placeholders and the values are passed as a separate tuple to cursor.execute(). The connector escapes the values before sending them, so a user who types Robert'); DROP TABLE students;-- into the name field gets a name containing that text, not a dropped table. This is the standard defence against SQL injection.try/except/finally structure is used everywhere in this project. conn.commit() writes the change to the database; without it the INSERT only happens inside the connection's temporary transaction and is lost. If the query fails, conn.rollback() undoes any partial work and the error is printed. The finally block always closes the cursor and the connection, even when an error occurred.view_students() prints every student, and search_student() looks up students by a partial name or roll number.
Adding records is only half of the system. The office needs to read records back: a full list for a glance at the whole roster, and a search for the moment someone calls asking for "that student from CS".
Add these two functions to main.py and wire them into the menu:
def view_students():
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, roll_number, name, email, phone FROM students ORDER BY roll_number")
rows = cursor.fetchall()
if not rows:
print("No students found.")
else:
print()
print(f"{'ID':<4}{'Roll':<8}{'Name':<22}{'Email':<32}{'Phone'}")
print("-" * 78)
for row in rows:
print(f"{row[0]:<4}{row[1]:<8}{row[2]:<22}{row[3]:<32}{row[4]}")
cursor.close()
conn.close()
def search_student():
term = input("Search by name or roll number: ").strip()
like = f"%{term}%"
conn = get_connection()
cursor = conn.cursor()
query = """
SELECT roll_number, name, email, phone
FROM students
WHERE name LIKE %s OR roll_number LIKE %s
ORDER BY roll_number
"""
cursor.execute(query, (like, like))
rows = cursor.fetchall()
if not rows:
print("No matching students.")
else:
print()
for row in rows:
print(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}")
cursor.close()
conn.close()
In the menu, add two branches:
elif choice == "2":
view_students()
elif choice == "8":
search_student()
view_students() runs a plain SELECT and calls fetchall(), which returns a list of tuples. The f-string formatting with <4, <8, and so on left-aligns each column to a fixed width, so the output reads as a clean table. The header line uses the same alignment, which is why the columns line up.
search_student() is the first place a LIKE query appears. The user types something like pri and the code wraps it in % characters to build %pri%, the SQL wildcard pattern that matches any string containing pri. The same pattern is passed twice, once for name LIKE %s and once for roll_number LIKE %s, so one search term covers both columns. Passing the pattern as a parameter (rather than splicing it into the string) keeps the query safe from injection.
update_student() edits a student's details, and delete_student() removes a student from the system.
Records change. A student changes their phone number, or leaves the university and must be removed. Both operations must handle the "record not found" case gracefully and, for delete, must deal with the enrollments that reference the student.
Add these functions and their menu branches:
def update_student():
roll = input("Enter roll number of the student to update: ").strip()
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, name, email, phone FROM students WHERE roll_number = %s", (roll,))
student = cursor.fetchone()
if not student:
print("No student found with that roll number.")
cursor.close()
conn.close()
return
print(f"Found: {student[1]}")
new_name = input("New name (Enter to keep): ").strip()
new_email = input("New email (Enter to keep): ").strip()
new_phone = input("New phone (Enter to keep): ").strip()
if not new_name:
new_name = student[1]
if not new_email:
new_email = student[2]
if not new_phone:
new_phone = student[3]
try:
query = "UPDATE students SET name = %s, email = %s, phone = %s WHERE id = %s"
cursor.execute(query, (new_name, new_email, new_phone, student[0]))
conn.commit()
print("Student record updated.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def delete_student():
roll = input("Enter roll number of the student to delete: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
query = "DELETE FROM students WHERE roll_number = %s"
cursor.execute(query, (roll,))
if cursor.rowcount == 0:
print("No student found with that roll number.")
else:
conn.commit()
print("Student deleted. Their enrollments were removed automatically.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
Menu branches:
elif choice == "3":
update_student()
elif choice == "4":
delete_student()
update_student() first looks the student up by roll number. If nothing comes back, it prints a message and returns early, which avoids an UPDATE that would match zero rows. If the user leaves a field blank, the code keeps the value that was already stored — the if not new_name: lines fall back to the values fetched from the database. This is friendlier than overwriting a field with an empty string by accident. The WHERE id = %s clause uses the primary key, so exactly one row is updated.
delete_student() checks cursor.rowcount, the number of rows the DELETE affected. Zero means the roll number did not exist, so nothing is deleted and nothing is committed. Otherwise the delete is committed. The enrollments for that student disappear automatically because of the ON DELETE CASCADE rule defined in Step 1 — the Python code does not have to run a second DELETE for the enrollments table.
add_course() registers a new course, and enroll_student() links a student to a course, rejecting duplicate enrollments.
The catalog grows over time, so new courses must be insertable. Enrolling is the most important business rule in the whole system: a student must exist, the course must exist, and the pair must not already be enrolled.
Add these two functions and their branches:
def add_course():
code = input("Enter course code: ").strip()
name = input("Enter course name: ").strip()
credits = input("Enter credits: ").strip()
if not credits.isdigit():
print("Credits must be a whole number.")
return
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO courses (course_code, course_name, credits) VALUES (%s, %s, %s)"
cursor.execute(query, (code, name, int(credits)))
conn.commit()
print(f"Course {name} added.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def enroll_student():
roll = input("Enter student roll number: ").strip()
code = input("Enter course code: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT id FROM students WHERE roll_number = %s", (roll,))
student = cursor.fetchone()
cursor.execute("SELECT id FROM courses WHERE course_code = %s", (code,))
course = cursor.fetchone()
if not student:
print("No student with that roll number.")
return
if not course:
print("No course with that course code.")
return
cursor.execute(
"SELECT COUNT(*) FROM enrollments WHERE student_id = %s AND course_id = %s",
(student[0], course[0])
)
if cursor.fetchone()[0] > 0:
print("That student is already enrolled in this course.")
return
query = "INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (%s, %s, CURDATE())"
cursor.execute(query, (student[0], course[0]))
conn.commit()
print("Enrollment recorded.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
Menu branches:
elif choice == "5":
add_course()
elif choice == "6":
enroll_student()
add_course() validates that credits is a whole number with isdigit() before the insert. This catches a user typing four for credits, which would otherwise fail when the database tried to store it in an INT column.
enroll_student() is a multi-check operation. It looks up the student by roll number and the course by course code. If either lookup fails, the function prints a message and returns without touching the database. Then it counts existing enrollments for that pair; a count above zero means the enrollment already exists and is rejected. Only when both records exist and the pair is new does the INSERT run. The date is filled in by MySQL's CURDATE() function, so the Python code does not need to format a date. This function is the reason the composite UNIQUE key exists in the schema — the check in Python gives a friendly message, and the constraint is the final backstop if two users enrolled at the same time.
list_enrollments() shows the full enrollment list with student and course names, and show_statistics() counts students per course and prints the total.
Raw ids in the enrollments table are not useful to a person. Reports must translate them into names, and the statistics give the office a quick sense of course demand. This is where JOIN and GROUP BY pay off.
Add the final two functions and complete the menu dispatch:
def list_enrollments():
conn = get_connection()
cursor = conn.cursor()
query = """
SELECT s.roll_number, s.name, c.course_code, c.course_name, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY e.enrollment_date
"""
cursor.execute(query)
rows = cursor.fetchall()
if not rows:
print("No enrollments yet.")
else:
print()
print(f"{'Roll':<8}{'Student':<22}{'Code':<10}{'Course':<38}{'Enrolled On'}")
print("-" * 92)
for row in rows:
print(f"{row[0]:<8}{row[1]:<22}{row[2]:<10}{row[3]:<38}{row[4]}")
cursor.close()
conn.close()
def show_statistics():
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM students")
print(f"Total students in the database: {cursor.fetchone()[0]}")
print("\nStudents enrolled per course:")
query = """
SELECT c.course_code, c.course_name, COUNT(e.student_id) AS enrolled
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.course_code, c.course_name
ORDER BY enrolled DESC
"""
cursor.execute(query)
for row in cursor.fetchall():
print(f" {row[0]} {row[1]}: {row[2]} student(s)")
cursor.close()
conn.close()
The final main() dispatch:
if choice == "1":
add_student()
elif choice == "2":
view_students()
elif choice == "3":
update_student()
elif choice == "4":
delete_student()
elif choice == "5":
add_course()
elif choice == "6":
enroll_student()
elif choice == "7":
list_enrollments()
elif choice == "8":
search_student()
elif choice == "9":
show_statistics()
elif choice == "0":
print("Goodbye!")
break
else:
print("Invalid option. Try again.")
list_enrollments() uses two inner JOINs. The first links enrollments.student_id to students.id so the query can return s.roll_number and s.name; the second links enrollments.course_id to courses.id so it can return the course code and name. A JOIN only keeps rows that have a match on the linked column, which is exactly what we want here because every enrollment references a real student and course. The short aliases s, c, and e make the query readable.
show_statistics() runs two queries. The first is SELECT COUNT(*) FROM students, which returns a single value giving the total. The second uses LEFT JOIN instead of JOIN, and that difference matters: LEFT JOIN keeps every row from the left table (courses) even when there is no matching enrollment. A course with zero students still appears with a count of 0. Then GROUP BY c.id collects the rows per course and COUNT(e.student_id) counts the enrollments in each group. Ordering by enrolled DESC puts the most popular course first. With the menu dispatch finished, all nine options now work.
CREATE DATABASE IF NOT EXISTS student_management;
USE student_management;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
roll_number VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(20) NOT NULL UNIQUE,
course_name VARCHAR(100) NOT NULL,
credits INT NOT NULL
);
CREATE TABLE enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE NOT NULL,
UNIQUE KEY unique_enrollment (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
INSERT INTO students (roll_number, name, email, phone) VALUES
('CS101', 'Aarav Sharma', 'aarav.sharma@example.edu', '9876543210'),
('CS102', 'Priya Patel', 'priya.patel@example.edu', '9876543211'),
('EC201', 'Rahul Verma', 'rahul.verma@example.edu', '9876543212'),
('EC202', 'Sneha Reddy', 'sneha.reddy@example.edu', '9876543213'),
('ME301', 'Kabir Singh', 'kabir.singh@example.edu', '9876543214'),
('ME302', 'Ananya Iyer', 'ananya.iyer@example.edu', '9876543215'),
('CS103', 'Rohan Mehta', 'rohan.mehta@example.edu', '9876543216');
INSERT INTO courses (course_code, course_name, credits) VALUES
('CS101', 'Introduction to Computer Science', 4),
('CS201', 'Data Structures and Algorithms', 4),
('EC101', 'Digital Electronics', 3),
('EC201', 'Signals and Systems', 3),
('ME101', 'Engineering Mechanics', 3),
('ME201', 'Thermodynamics', 4);
INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 1, '2026-01-10'),
(1, 2, '2026-01-10'),
(2, 1, '2026-01-12'),
(2, 3, '2026-01-12'),
(3, 3, '2026-01-14'),
(4, 4, '2026-01-15'),
(5, 5, '2026-01-18'),
(6, 5, '2026-01-20'),
(7, 1, '2026-01-22');
import mysql.connector
def get_connection():
return mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="student_management"
)
import mysql.connector
from mysql.connector import Error
from db_connection import get_connection
def add_student():
roll = input("Enter roll number: ").strip()
name = input("Enter full name: ").strip()
email = input("Enter email: ").strip()
phone = input("Enter phone: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO students (roll_number, name, email, phone) VALUES (%s, %s, %s, %s)"
cursor.execute(query, (roll, name, email, phone))
conn.commit()
print(f"Student {name} added with roll number {roll}.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def view_students():
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, roll_number, name, email, phone FROM students ORDER BY roll_number")
rows = cursor.fetchall()
if not rows:
print("No students found.")
else:
print()
print(f"{'ID':<4}{'Roll':<8}{'Name':<22}{'Email':<32}{'Phone'}")
print("-" * 78)
for row in rows:
print(f"{row[0]:<4}{row[1]:<8}{row[2]:<22}{row[3]:<32}{row[4]}")
cursor.close()
conn.close()
def update_student():
roll = input("Enter roll number of the student to update: ").strip()
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT id, name, email, phone FROM students WHERE roll_number = %s", (roll,))
student = cursor.fetchone()
if not student:
print("No student found with that roll number.")
cursor.close()
conn.close()
return
print(f"Found: {student[1]}")
new_name = input("New name (Enter to keep): ").strip()
new_email = input("New email (Enter to keep): ").strip()
new_phone = input("New phone (Enter to keep): ").strip()
if not new_name:
new_name = student[1]
if not new_email:
new_email = student[2]
if not new_phone:
new_phone = student[3]
try:
query = "UPDATE students SET name = %s, email = %s, phone = %s WHERE id = %s"
cursor.execute(query, (new_name, new_email, new_phone, student[0]))
conn.commit()
print("Student record updated.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def delete_student():
roll = input("Enter roll number of the student to delete: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
query = "DELETE FROM students WHERE roll_number = %s"
cursor.execute(query, (roll,))
if cursor.rowcount == 0:
print("No student found with that roll number.")
else:
conn.commit()
print("Student deleted. Their enrollments were removed automatically.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def add_course():
code = input("Enter course code: ").strip()
name = input("Enter course name: ").strip()
credits = input("Enter credits: ").strip()
if not credits.isdigit():
print("Credits must be a whole number.")
return
conn = get_connection()
cursor = conn.cursor()
try:
query = "INSERT INTO courses (course_code, course_name, credits) VALUES (%s, %s, %s)"
cursor.execute(query, (code, name, int(credits)))
conn.commit()
print(f"Course {name} added.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def enroll_student():
roll = input("Enter student roll number: ").strip()
code = input("Enter course code: ").strip()
conn = get_connection()
cursor = conn.cursor()
try:
cursor.execute("SELECT id FROM students WHERE roll_number = %s", (roll,))
student = cursor.fetchone()
cursor.execute("SELECT id FROM courses WHERE course_code = %s", (code,))
course = cursor.fetchone()
if not student:
print("No student with that roll number.")
return
if not course:
print("No course with that course code.")
return
cursor.execute(
"SELECT COUNT(*) FROM enrollments WHERE student_id = %s AND course_id = %s",
(student[0], course[0])
)
if cursor.fetchone()[0] > 0:
print("That student is already enrolled in this course.")
return
query = "INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (%s, %s, CURDATE())"
cursor.execute(query, (student[0], course[0]))
conn.commit()
print("Enrollment recorded.")
except Error as e:
conn.rollback()
print("Error:", e)
finally:
cursor.close()
conn.close()
def list_enrollments():
conn = get_connection()
cursor = conn.cursor()
query = """
SELECT s.roll_number, s.name, c.course_code, c.course_name, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY e.enrollment_date
"""
cursor.execute(query)
rows = cursor.fetchall()
if not rows:
print("No enrollments yet.")
else:
print()
print(f"{'Roll':<8}{'Student':<22}{'Code':<10}{'Course':<38}{'Enrolled On'}")
print("-" * 92)
for row in rows:
print(f"{row[0]:<8}{row[1]:<22}{row[2]:<10}{row[3]:<38}{row[4]}")
cursor.close()
conn.close()
def search_student():
term = input("Search by name or roll number: ").strip()
like = f"%{term}%"
conn = get_connection()
cursor = conn.cursor()
query = """
SELECT roll_number, name, email, phone
FROM students
WHERE name LIKE %s OR roll_number LIKE %s
ORDER BY roll_number
"""
cursor.execute(query, (like, like))
rows = cursor.fetchall()
if not rows:
print("No matching students.")
else:
print()
for row in rows:
print(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}")
cursor.close()
conn.close()
def show_statistics():
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM students")
print(f"Total students in the database: {cursor.fetchone()[0]}")
print("\nStudents enrolled per course:")
query = """
SELECT c.course_code, c.course_name, COUNT(e.student_id) AS enrolled
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.course_code, c.course_name
ORDER BY enrolled DESC
"""
cursor.execute(query)
for row in cursor.fetchall():
print(f" {row[0]} {row[1]}: {row[2]} student(s)")
cursor.close()
conn.close()
def main():
while True:
print("\n===== STUDENT MANAGEMENT SYSTEM =====")
print("1. Add student")
print("2. View all students")
print("3. Update student")
print("4. Delete student")
print("5. Add course")
print("6. Enroll student in a course")
print("7. List all enrollments")
print("8. Search student")
print("9. Show statistics")
print("0. Exit")
choice = input("Choose an option: ").strip()
if choice == "1":
add_student()
elif choice == "2":
view_students()
elif choice == "3":
update_student()
elif choice == "4":
delete_student()
elif choice == "5":
add_course()
elif choice == "6":
enroll_student()
elif choice == "7":
list_enrollments()
elif choice == "8":
search_student()
elif choice == "9":
show_statistics()
elif choice == "0":
print("Goodbye!")
break
else:
print("Invalid option. Try again.")
if __name__ == "__main__":
main()
mysql-connector-python
# Student Management System
A command-line Student Management System built with Python and MySQL.
## Setup
1. Install the connector: `pip install mysql-connector-python`
2. Load the schema: `mysql -u root -p < database.sql`
3. Put your MySQL password in `db_connection.py`.
4. Run: `python main.py`
## Features
- Add, view, update, and delete students
- Add courses and enrol students (no duplicate enrollments)
- Search students by name or roll number
- Enrollments list and statistics reports
pip install mysql-connector-python
< character tells the shell to feed the file's contents into the mysql client:mysql -u root -p < database.sql
Edit db_connection.py and replace your_password with your real MySQL password.
Start the program:
python main.py
On the first run, the menu appears. Choosing option 2 shows the seven seeded students:
$ python main.py
===== STUDENT MANAGEMENT SYSTEM =====
1. Add student
2. View all students
3. Update student
4. Delete student
5. Add course
6. Enroll student in a course
7. List all enrollments
8. Search student
9. Show statistics
0. Exit
Choose an option: 2
ID Roll Name Email Phone
---------------------------------------------------------------------
1 CS101 Aarav Sharma aarav.sharma@example.edu 9876543210
2 CS102 Priya Patel priya.patel@example.edu 9876543211
3 EC201 Rahul Verma rahul.verma@example.edu 9876543212
4 EC202 Sneha Reddy sneha.reddy@example.edu 9876543213
5 ME301 Kabir Singh kabir.singh@example.edu 9876543214
6 ME302 Ananya Iyer ananya.iyer@example.edu 9876543215
7 CS103 Rohan Mehta rohan.mehta@example.edu 9876543216
Test 1 – Add a new student
Input
Choose an option: 1
Enter roll number: CS104
Enter full name: Farhan Qureshi
Enter email: farhan.q@example.edu
Enter phone: 9876543217
Expected Output
Student Farhan Qureshi added with roll number CS104.
Explanation
The INSERT runs inside the try block, commit() saves it, and the confirmation message shows the values that were stored. If you press option 2 afterwards, Farhan appears at the bottom of the list.
Test 2 – Enroll a student, then try the same pair again
Input (first time)
Choose an option: 6
Enter student roll number: CS103
Enter course code: CS201
Expected Output
Enrollment recorded.
Input (second time)
Choose an option: 6
Enter student roll number: CS103
Enter course code: CS201
Expected Output
That student is already enrolled in this course.
Explanation
The first run passes every check and inserts the enrollment. The second run finds the count of existing enrollments for the pair is greater than zero and stops before the INSERT. The database's composite unique key would reject the duplicate even if the Python check were removed.
Test 3 – Delete a student who has enrollments
Input
Choose an option: 4
Enter roll number of the student to delete: EC202
Expected Output
Student deleted. Their enrollments were removed automatically.
Explanation
Sneha Reddy was enrolled in course EC201, so deleting her also deletes that enrollment row. Verify it by choosing option 7 and confirming Sneha no longer appears in the enrollments list. The cascade did the cleanup.
Test 4 – Show statistics
Input
Choose an option: 9
Expected Output
Total students in the database: 7
Students enrolled per course:
CS101 Introduction to Computer Science: 3 student(s)
EC101 Digital Electronics: 2 student(s)
ME101 Engineering Mechanics: 2 student(s)
CS201 Data Structures and Algorithms: 1 student(s)
EC201 Signals and Systems: 1 student(s)
ME201 Thermodynamics: 0 student(s)
Explanation
The LEFT JOIN keeps every course in the output, which is why Thermodynamics still shows with a count of 0. The GROUP BY groups enrollment rows per course and COUNT(e.student_id) tallies each group. The counts match the seed data in Step 2.
Problem: ModuleNotFoundError: No module named 'mysql.connector'
Reason: The connector package is not installed, or pip installed it into a different Python than the one running the script.
Solution: Run pip install mysql-connector-python. If you use a virtual environment, activate it before both the install and the python main.py run, so both use the same interpreter.
Problem: mysql.connector.errors.DatabaseError: 1045 (28000): Access denied for user 'root'@'localhost'
Reason: The password in db_connection.py does not match the MySQL root password, or the username is wrong.
Solution: Check the user and password in get_connection() against what you use in mysql -u root -p. On some MySQL installs root uses no password; use password="" in that case.
Problem: mysql.connector.errors.OperationalError: 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
Reason: The MySQL service is not running, or it is listening on a different host or port.
Solution: Start MySQL (on Windows: Services, find MySQL80, start it; on Linux: sudo systemctl start mysql). Confirm the host and port in get_connection() match the server. If the port is not the default 3306, pass it as port=....
Problem: A student appears to be saved, but after restarting the program the row is gone.
Reason: The INSERT ran but conn.commit() was never called. The change lived only in the connection's transaction and was discarded.
Solution: Call conn.commit() after the statements that modify data. In this project every insert, update, and delete is followed by a commit inside the try block.
Problem: mysql.connector.errors.IntegrityError: Duplicate entry 'CS101' for key 'students.roll_number'
Reason: A student with roll number CS101 already exists, and the column has a UNIQUE constraint.
Solution: The error is expected when the user re-enters an existing roll number. add_student() catches the error and prints it. You could improve the function to detect an IntegrityError specifically and print "That roll number is already in use."
Problem: ERROR 1050 (42S01): Table 'students' already exists when re-running the schema.
Reason: database.sql creates tables that already exist from a previous run.
Solution: Either drop the database first (DROP DATABASE IF EXISTS student_management;) at the top of database.sql, or create the database and tables with IF NOT EXISTS and accept that re-running keeps the old data. To reset everything, run DROP DATABASE student_management; and then load the file again.
department column to students and let the reports filter by department.grades table (or a grade column on enrollments) and compute a per-course average or student GPA.fees table with payment status and generate a simple dues report.csv module.Write a function that lists every student who is not enrolled in any course. A LEFT JOIN from students to enrollments with a WHERE enrollments.id IS NULL filter will get you there.
Add an operation that counts how many courses each student is enrolled in, and print the list ordered from most courses to fewest.
Modify add_student() so a duplicate roll number prints a friendly message instead of the raw database error. Catch IntegrityError specifically.
Add a drop_course() function that removes a single enrollment given a roll number and a course code, without deleting the student or the course.
Add a grade column to the enrollments table, record grades for existing enrollments, and write a function that shows the average grade per course.
Save your progress and earn XP for completing tutorials.
Technology
MySQL
Lesson group
Real-World Projects
Progress
8% complete