sh.py

print("************STAFF MANAGEMENT SYSTEM**************")
import mysql.connector as sqltor
mycon=sqltor.connect(host='localhost',user='root',password='123456789',database='shreya123',charset='utf8')
cursor=mycon.cursor()

def login():
    print("\t\tLOGIN")
    x=input("enter your username:")
    z=int(input("enter your passwd:"))      
    if x=='shreya' and z==1234:
        print("\n\n\n\nchoose your option from menu")
        def main_menu():
            print("\t\tMAIN MENU")
            print("1.My account")
            print("2.Records")
            print("3.Experience")
            print("4.Leaves")
            print("5.Salary")
            print("6.About the system")
            print("7.Exit")
        
            y=int(input("\nEnter your choice from 1 to 7:"))    
            def my_account():
                if y==1:
                    print("_____________________________________________________________________________")
                    print("\nmy account details are-")
                    a="SELECT* FROM myaccount"
                    cursor.execute(a)
                    result1=cursor.fetchall()
                    for b in result1:
                        print(b)
                    
                    print("1.Return to main menu")
                    choose=int(input("enter your option:"))
                    if choose==1:
                        return main_menu()
                    else:
                        print("wrong value chosen")
        
    
            my_account()   

            def records():
                if y==2:
                    print("___________________________________________________________________________")
                    print("1.show all records")
                    print("2.add staff")
                    print("3.edit staff details")
                    print("4.delete staff")
                    print("5.return back to main menu")
                    s=int(input("enter choice from 1 to 4:"))
                    if s==1:
                        record="select* from all_records"
                        cursor.execute(record)
                        result2=cursor.fetchall()
                        for i in result2:
                            print(i)

                        return records()    
                    elif s==2:
                        t_id=int(input("enter teacher_id:"))
                        name=input("enter full name:")
                        pno=int(input("enter phone no.:"))
                        email=input("enter email id:")
                        gender=input("enter gender:")
                        dob=input("enter DOB in yyyy-mm-dd format:")
                        addr=input("enter address:")
                        sub=input("enter subject:")
                        cursor.execute("""insert into all_records(Teacher_id,full_name,phone_no,email_id,gender,DOB,address,subject)values({},'{}',{},'{}','{}','{}','{}','{}')""".format(t_id,name,pno,email,gender,dob,addr,sub))
                        mycon.commit()
                        print("data entered successfully")
                        mycon.close()
                        return records()
                    elif s==3:
                        print("\n\n\n\nchoose what you wish to edit")
                        print("1.name")
                        print("2.phone_no")
                        print("3.email_id")
                        print("4.gender")
                        print("5.dob")
                        print("6.address")
                        print("7.subject")
                        p=int(input("enter number from 1 to 7:"))
                        if p==1:
                            name="""update all_records set full_name = %s where Teacher_id = %s"""
                            nme=input("enter new name:")
                            id=int(input("enter teacher id of person you want to edit detail of:"))
                    
                            inputdata = (nme,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()
                        elif p==2:
                            name="""update all_records set phone_no = %s where Teacher_id = %s"""
                            phone=input("enter new phone number:")
                            id=int(input("enter teacher id of person you want to edit detail of:"))
                    
                            inputdata = (phone,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()
                        elif p==3:
                            name="""update all_records set email_id = %s where Teacher_id = %s"""
                            email=input("enter new email id:")
                            id=int(input("enter teacher id of person you want to edit detail of:"))
                    
                            inputdata = (email,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()
                        elif p==4:
                            name="""update all_records set gender = %s where Teacher_id = %s"""
                            gender=input("enter edited gender:")
                            id=int(input("enter teacher id of person you want to edit detail of:"))
                    
                            inputdata = (gender,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()                    
                        elif p==5:
                            name="""update all_records set DOB = %s where Teacher_id = %s"""
                            dob=input("enter edited DOB in yyyy-mm-dd format:")
                            id=int(input("enter teacher id of person you want to edit detail of:"))
                    
                            inputdata = (dob,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()
                        elif p==6:
                            name="""update all_records set phone_no = %s where Teacher_id = %s"""
                            addre=input("enter new address:")
                            id=int(input("enter teacher id of person you want to edit name of:"))
                     
                            inputdata = (addre,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()
                        else:
                            name="""update all_records set phone_no = %s where Teacher_id = %s"""
                            subj=input("enter new subject:")
                            id=int(input("enter teacher id of person you want to edit name of:"))
                    
                            inputdata = (subj,id,)
                            cursor.execute(name, inputdata)
                            mycon.commit()
                            print("rows affected:",cursor.rowcount)
                            mycon.close()
                            return records()    
                    elif s==4:
                        sql="DELETE FROM all_records WHERE Teacher_id=%s"
                        Tid=int(input("enter teacher id of person you wish to delete details of:"))
                        inputdata=(Tid,)
                        cursor.execute(sql,(inputdata))
                        mycon.commit()
                        print("record deleted",cursor.rowcount)
                        mycon.close()
                        return records()
                         
                    else:
                        print("returned back to main menu")
                        return main_menu()

            
            records()
            def experiences():
                if y==3:
                    print("---------------------------------------------------------------------------------------------------------------------------------------")
                    print("choose from below")
                    print("1.experience of all teachers")
                    print("2.experience of individual")
                    print("3.add experience of new staff")
                    print("4.delete details")
                    print("5.Return back to main menu")
                    v=int(input("enter your choice from 1 to 3:"))
                    if v==1:
                        exp="SELECT* FROM experience"
                        cursor.execute(*exp)
                        result2=cursor.fetchall()
                        for b in result2:
                            print(b)
                        return experiences()
                    elif v==2:
                        tt_id=int(input("enter Teacher id:"))
                        expp="select* from experience where Teacher_id=%s"
                        inputdata=(tt_id,)
                        cursor.execute(*expp,inputdata)
                        result3=cursor.fetchmany()
                        for b in result3:
                            print(b)
                        
                        return experiences()
                    elif v==3:
                        t_id=int(input("enter teacher id:"))
                        name=input("enter full name:")
                        qualification=input("enter qualifications:")
                        experience=input("enter experience in years:")
                        cursor.execute("""insert into experience(Teacher_id,name,Qualification,experience)values({},'{}','{}','{}')""".format(t_id,name,qualification,experience))
                        mycon.commit()
                        print("data entered successfully")
                        mycon.close()
                    
                        return experiences()
                    elif v==4:
                        sql2="DELETE FROM experience WHERE Teacher_id=%s"
                        Tid1=int(input("enter teacher id of person you wish to delete details of:"))
                        inputdata1=(Tid1,)
                        cursor.execute(*sql2,inputdata1)
                        mycon.commit()
                        print("record deleted",cursor.rowcount)
                        mycon.close()
                    
                        return experiences() 
                    else:
                        return main_menu()
                
            experiences()
            def leaves():
                if y==4:
                    print("_____________________________________________________________________________________________________________________")
                    print("choose from the following")
                
                    print("1.leaves of all staff members(monthly and yearly respectively)")
                    print("2.leaves of individual staff member")
                    print("3.add leaves of new staff member")
                    print("4.delete details")
                    print("5.return back to main menu")
                    sleaves=int(input("\nenter your choice from 1 to 5:"))
                    if sleaves==1:
                        sl="SELECT* FROM leaves_ofstaff"
                        cursor.execute(sl)
                        result3=cursor.fetchall()
                        for b in result3:
                            print(b)
                        
                        return leaves()
                    elif sleaves==2:
                        t_id2=int(input("enter Teacher id:"))
                        sl2="select* from leaves_ofstaff where Teacher_id=%s"
                        inputdata=(t_id2,)
                        cursor.execute(sl2,inputdata)
                        result4=cursor.fetchmany()
                        for b in result4:
                            print(b)
                        
                        return leaves()
                    elif sleaves==3:
                        t_id3=int(input("enter teacher id:"))
                        name1=input("enter full name:")
                        mavg=int(input("enter monthly avg leave in days:"))
                        yleave=int(input("enter yearly leave in days:"))
                        cursor.execute("""insert into leaves_ofstaff(Teacher_id,name,monthly_average_leave,yearly_total)values({},'{}',{},{})""".format(t_id3,name1,mavg,yleave))
                        mycon.commit()
                        print("data entered successfully")
                        mycon.close()
                        return leaves()
                    elif sleaves==4:
                        sql22="DELETE FROM leaves_ofstaff WHERE Teacher_id=%s"
                        T_id4=int(input("enter teacher id of person you wish to delete details of:"))
                        inputdata1=(T_id4,)
                        cursor.execute(sql22,inputdata1)
                        mycon.commit()
                        print("record deleted",cursor.rowcount)
                        mycon.close()
                    
                        return leaves()
                    else:
                        return main_menu()
                
            leaves()
            def salary():
                if y==5:
                    print("_________________________________________________________________________________________________________________________")
                    print("choose from following")
                    print("1.salary of all staff members(monthly and yearly)")
                    print("2.salary of invidual")
                    print("3.add salary of new staff")
                    print("4.delete details")
                    print("5.return back to main menu")
                    sal=int(input("\nenter your choice from 1 to 5:"))
                    if sal==1:
                        sal1="SELECT* FROM salary"
                        cursor.execute(sal1)
                        result5=cursor.fetchall()
                        for b in result5:
                            print(b)

                        return salary()
                    elif sal==2:
                        t_id5=int(input("enter Teacher id:"))
                        print("name,monthly salary,yearly salary,teacher id")
                        sl5="select* from salary where Teacher_id=%s"
                        inputdata=(t_id5,)
                        cursor.execute(sl5,inputdata)
                        result6=cursor.fetchmany()
                        for b in result6:
                            print(b)

                        return salary()
                    elif sal==3:
                        name5=input("enter full name:")
                        msal=int(input("enter monthly salary:"))
                        ysal=int(input("enter yearly salary:"))
                        tid5=int(input("enter teacher id:"))
                        cursor.execute("""insert into salary(name,monthly_salary,yearly_salary,Teacher_id)values('{}',{},{},{})""".format(name5,msal,ysal,tid5))
                        mycon.commit()
                        print("data entered successfully")
                        mycon.close()

                        return salary()
                    elif sal==4:
                        sql6="DELETE FROM salary WHERE Teacher_id=%s"
                        Tid6=int(input("enter teacher id of person you wish to delete details of:"))
                        inputdata6=(Tid6,)
                        print(inputdata6)
                        cursor.execute(sql6,inputdata6)
                        mycon.commit()
                        print("record deleted",cursor.rowcount)
                        mycon.close()
                   
                        return salary()
                    else:
                        return main_menu()
                
            salary()
            def about():
                if y==6:
                    print("______________________________________________________________________________________________________________")
                    print("\nThis is a staff management system python project made by shreya of class 12A")
                    print("\t\t\t\t\t\t!!!!!******THANK YOU******!!!!!")
                    return main_menu()
            about()

        
            if y==7:
                print("\press enter key to exit")
                exit()
            
        main_menu()    
    
    else:
        print("wrong username or password")
        return login()
login()