app.py

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="tdb"
)

cursor = con.cursor()

# student table create if not exists
cursor.execute("CREATE TABLE IF NOT EXISTS student (an INT PRIMARY KEY, fname VARCHAR(255), lname VARCHAR(255), house CHAR(10))")

def display_menu():
    print("SELECT YOUR OPTION")
    print("------------------")

    print("1. show all student")
    print("2. view data for a student")
    print("3. update record of student")
    print("4. delete a student record")
    print("5. add a new student")
    print("0. to exit this program")

    print("------------------")

def show_all_students():
    # show all students
    sql = "SELECT an, fname, lname FROM student ORDER BY fname"
    cursor.execute(sql)
    
    students = cursor.fetchall()
    if len(students) == 0:
        print("No students found.")
    else:
        for student in students:
            print("Admission Number: ", student[0])
            print("Student Name:", student[1], student[2])

def display_student_data():
    an = int(input("admission number: "))
    sql = "SELECT * FROM student WHERE an=%s"

    cursor.execute(sql, (an,))

    student_data = cursor.fetchone()

    if student_data is None:
        print("Student not found")
    else:
        print("SHOWING DATA FOR", student_data[1], student_data[2])
        print("HOUSE:", student_data[3])
        print("Admission Number:", student_data[0])

def delete_student():
    an = int(input("admission number: "))
    cursor.execute("SELECT fname, lname FROM student WHERE an=%s", (an,))

    student_to_delete = cursor.fetchone()

    if student_to_delete is None:
        print("Student not found.")
        return

    sql = "DELETE FROM student WHERE an=%s"

    cursor.execute(sql, (an,))
    print("Student", student_to_delete[0], student_to_delete[1], "deleted.")


def add_student():
    an = int(input("admission number: "))
    fname = input("first name: ")
    lname = input("last name: ")
    house = input("house (red, green, blue, yellow): ")

    sql = "INSERT INTO student VALUES (%s, %s, %s, %s)"
    data = (an, fname, lname, house.upper())

    cursor.execute(sql, data)

    print("new student inserted.")

while True:
    display_menu()
    ui = int(input("Please enter your choice: "))
    if ui == 1:
        show_all_students()
    elif ui == 2:
        display_student_data()
    elif ui == 3:
        pass
    elif ui == 4:
        delete_student()
    elif ui == 5:
        add_student()
    elif ui == 0:
        break
    else:
        print("Invalid Input! Try again.")
    
    print("*" * 20)

con.commit()
con.close()