***************************************************************************
PROJECT NAME : STUDENT MANAGEMENT SYSTEM
***************************************************************************
The two tables used in this project along with its specifications are given below:
Database Name : SCHOOL

Table : Student
+--------+-------------+------+-----+---------+-------+
| Field    | Type	        | Null | Key | Default | 
+--------+-------------+------+-----+---------+-------+
| admno  | int                     | NO   | PRI | NULL    |    
| name    | varchar(25)        | YES  |        | NULL    |   
| class    | int                      | YES  |        | NULL    |    
| sec      | char(1)               | YES  |        | NULL    |   
| dob     | date                   | YES  |        | NULL    |      
+-------+----------------------+------+------+----------+

Table: Marks
+---------+------------+------+-----+---------+
| Field     | Type       | Null | Key | Default | 
+---------+------------+------+-----+---------+
| admno  | int           | YES  |     | NULL   | 
| term     | int           | YES  |     | NULL    |  
| m1       | float(5,2) | YES  |     | NULL    | 
| g1        | char(1)    | YES  |     | NULL    | 
| m2       | float(5,2) | YES  |     | NULL    | 
| g2        | char(1)    | YES  |     | NULL    | 
| m3       | float(5,2) | YES  |     | NULL    | 
| g3        | char(1)    | YES  |     | NULL    |
| m4       | float(5,2) | YES  |     | NULL    |
| g4        | char(1)    | YES  |     | NULL    |
| m5       | float(5,2) | YES  |     | NULL    |
| g5        | char(1)    | YES  |     | NULL    |
| perc     | float(5,2) | YES  |     | NULL    |
+--------+------------+-------+----+---------+

This project consists of three modules in Python
***************************************************************************
student.py -  This module consists of all functions related to the student table
***************************************************************************
"""This module defines functions to add, delete, update, 
display student details using the student table in the 
school database
"""
import mysql.connector
def check_exists(admno):
    """This function returns True if the admno exists in the table student
    otherwise returns False"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    sql="select * from student where admno=%s"%(admno,)
    mycursor.execute(sql)
    mycursor.fetchall()
    if mycursor.rowcount==0:
        return False
    else:
        return True
  
def add_student():
    """This function add a record of a student"""
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number :"))
    if check_exists(admno)==False:
        name=input("enter your name :")
        clas=int(input("Enter class(11,12) :"))
        sec=input("Enter section(A,B,C) :")
        dob=input("enter your date of birth :")
        sql="INSERT INTO STUDENT VALUES (%s,'%s',%s,'%s','%s')"%(admno,name,clas,sec,dob)
        mycursor.execute(sql)
        mycon.commit()
        print("Record inserted")
    else:
        print("Duplicate Entry")
    mycon.close()
    
def update_student():
    """This function updates the record of a student """
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter the admission number :"))
    if check_exists(admno)==True:
        print("Enter 1 to edit name")
        print("Enter 2 to edit class")
        print("Enter 3 to edit sec")
        print("Enter 4 to edit date of birth")
        ch=int(input("Enter your choice :"))
        if ch==1:
            name=input("Enter new name:")
            sql="update student set name='%s' where admno=%s"%(name,admno)
        elif ch==2:
            clas=int(input("Enter new class(11,12):" ))
            sql="update student set class=%s where admno=%s"%(clas,admno)
        elif ch==3:
            sec=input("Enter new sec(A,B,C):" )
            sql="update student set sec='%s' where admno=%s"%(sec,admno)
        elif ch==4:
            dob=input("Enter new date of birth:" )
            sql="update student set dob='%s' where admno=%s"%(dob,admno)
        else:
            print("Invalid choice")
        if ch in(1,2,3,4):
            mycursor.execute(sql)
            mycon.commit()
            print("Record Updated")
    else:
        print("Invalid Admission Number")
    mycon.close()

def delete_student():
    """This function deletes the record of a student """
    
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number :"))
    if check_exists(admno)==True:
        sql="DELETE FROM STUDENT WHERE ADMNO=%s"%(admno,)
        mycursor.execute(sql)
        mycon.commit()
        print("Record Deleted")
    else:
        print("Invalid Admission Number")
    mycon.close()

def display_student():
    """This function displays the details of one student"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number :"))
    sql="select * from student where admno=%s"%(admno,)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("Invalid Admission Number")
    else:
        print("Name :",mydata[0][1])
        print("Class :",mydata[0][2],"Section :",mydata[0][3])
        print("DOB :",str(mydata[0][4]))
        
        
def display_all():
    """This function displays the record of all student of a particular class"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    clas=int(input("Enter Class (11,12) :"))
    sec=input("Enter section :")
    sql="select * from student where class=%s and sec='%s'"%(clas,sec)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("Invalid Admission Number")
    else:
        print("%8s%20s%15s"%("Admno","Name","DOB"),sep="")
        for x in mydata:
            print("%8s%20s%15s"%(x[0],x[1],x[4]),sep="")
***************************************************************************
marks.py - This module consists of all functions related to the marks table
***************************************************************************
"""This module defines functions to add, delete, update, 
display mark details using the marks table in the 
school database
"""
import mysql.connector

sub1=sub2=sub3=sub4=sub5=""
def cal_grade(m):
    """This function calculates the grade of a subject depending
    upon the marks.
    """
    if m>=75:
        g="A"
    elif m>=65:
        g="B"
    elif m>=55:
        g="C"
    elif m>=45:
        g="D"
    elif m>=35:
        g="E"
    else:
        g="F"
    return g

def find_sub(sec):
    """This function initialises the sub variables to the subjects
    depending upon the section"""
    global sub1,sub2,sub3,sub4,sub5
    if sec=="A":
            sub1="English"
            sub2="Maths/Info"
            sub3="Physics"
            sub4="Chemistry"
            sub5="Biology"
    elif sec=="B":
            sub1="English"
            sub2="Maths"
            sub3="Physics"
            sub4="Chemistry"
            sub5="Comp.Sc"
    elif sec=="C":
            sub1="English"
            sub2="Maths/Info"
            sub3="Economics"
            sub4="Business Studies"
            sub5="Accountancy"

def check_exists(admno):
    """This function returns True if the admno exists in the table student
    otherwise returns False"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    sql="select * student where admno=%s"%(admno,)
    mycursor.execute(sql)
    mycursor.fetchall()
    if mycursor.rowcount==0:
        return False
    else:
        return True
       
def add_marks():
    """This function adds a record into the marks table"""
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number :"))
    sql="select sec from student where admno=%s"%(admno,)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("Invalid Admission Number")
    else:
        
        sec=mydata[0][0]
        find_sub(sec)
        term=int(input("Enter term :"))
        sql="select * from marks where admno=%s and term=%s"%(admno,term)
        
        mycursor.execute(sql)
        mydata=mycursor.fetchall()
        if mycursor.rowcount==0:
            m1=float(input("enter marks in "+sub1+":"))
            m2=float(input("enter marks in "+sub2+":"))
            m3=float(input("enter marks in "+sub3+":"))
            m4=float(input("enter marks in "+sub4+":"))
            m5=float(input("enter marks in "+sub5+":"))
            g1=cal_grade(m1)
            g2=cal_grade(m2)
            g3=cal_grade(m3)
            g4=cal_grade(m4)
            g5=cal_grade(m5)
            perc=(m1+m2+m3+m4+m5)/5
            sql="INSERT INTO marks VALUES(%s,%s,%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s)"%(admno,term,m1,g1,m2,g2,m3,g3,m4,g4,m5,g5,perc)
            mycursor.execute(sql)
            mycon.commit()
            print("Record inserted")
        else:
            print("Duplicate Entry")

    mycon.close()
    
def update_marks():
    """This function updates the marks of a student"""
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter the admission number of a student whose marks you want to update"))
    term=int(input("Enter term :"))
    sql="select * from marks where admno=%s and term=%s"%(admno,term)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("No entry for marks/ Wrong admission number")
    else:
        sec=mydata[0][3]
        find_sub(sec)
        print("Enter 1 to edit "+sub1)
        print("Enter 2 to edit "+sub2)
        print("Enter 3 to edit "+sub3)
        print("Enter 4 to edit "+sub4)
        print("Enter 5 to edit "+sub5)
        print("Enter 0 to exit")
        ch=int(input("Enter your choice"))
        if ch in (1,2,3,4,5):
            m=float(input("Enter updated marks"))
            g=cal_grade(m)
            sql="update marks set m%s=%s , g%s='%s' where admno=%s and term=%s"%(ch,m,ch,g,admno,term)
            mycursor.execute(sql)
            mycon.commit()
            print("Record Updated")
        elif ch==0:
            pass
        else:
            print("Invalid choice")
    mycon.close()

def delete_marks():
    """This function deletes the marks of a student"""
    mycon = mysql.connector.connect(host='localhost',database='school',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number "))
    if check_exists(admno)==True:
        sql="DELETE FROM MARKS WHERE ADMNO=%s"%(admno,)
        mycursor.execute(sql)
        mycon.commit()
        print("Record Deleted")
    else:
        print("Invalid Admission Number")
    mycon.close()

def display_marks():
    """ this function displays the marks of a student"""
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    admno=int(input("Enter admission number "))
    term=int(input("Enter the term "))
    sql="select * from marks where admno=%s and term=%s"%(admno,term)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("Invalid Admission Number/No marks for the specified term")
    else:
        sec=mydata[0][3]
        find_sub(sec)
        print(sub1+":",mydata[0][2],"Grade:",mydata[0][3])
        print(sub2+":",mydata[0][4],"Grade:",mydata[0][5])
        print(sub3+":",mydata[0][6],"Grade:",mydata[0][7])
        print(sub4+":",mydata[0][8],"Grade:",mydata[0][9])
        print(sub5+":",mydata[0][10],"Grade:",mydata[0][11])
        print("Percentage:",mydata[0][12])
        
def display_marksall():
    """This function displays the marks of a particular class"""
    mycon = mysql.connector.connect(host='localhost',
                                 database='school',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    clas=int(input("Enter Class (11,12) :"))
    sec=input("Enter section (A,B,C) :")
    sql="select * from student,marks where class=%s and sec='%s' and student.admno=marks.admno"%(clas,sec)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    
    if mycursor.rowcount==0:
        print("No details available for display")
    else:
        find_sub(sec)
        print("%8s%15s%6s%8s%8s%8s%8s%8s"%("Admno","Name",sub1[:3],sub2[:3],sub3[:3],sub4[:3],sub5[:3],"Perc"))
        for x in mydata:
            print("%8s%15s%6s%2s%6s%2s%6s%2s%6s%2s%6s%2s%6s"%(x[0],x[1],x[7],x[8],x[9],x[10],x[11],x[12],x[13],x[14],x[15],x[16],x[17]))



***************************************************************************
mainprg.py - This module consists of the menu fro which functions of the other modules are called.
***************************************************************************
from student import *
from marks import *
while True:
    print("\n\nMAIN MENU")
    print("1.Student Details")
    print("2.Mark Details")
    print("3.Exit")
    ch=int(input("Enter your choice:"))
    if ch==1:
        while True:
            print("\n\nSTUDENT MENU")
            print("1.Add new Student")
            print("2.Update Student Details")
            print("3.Delete Student Details")
            print("4.Display Particular Student Details")
            print("5.Display Students of a Particular Class")
            print("6.Back to main menu")
            ch1=int(input("Enter your choice:"))
            if ch1==1:
                add_student()
            elif ch1==2:
                update_student()
            elif ch1==3:
                delete_student()
            elif ch1==4:
                display_student()
            elif ch1==5:
                display_all()
            elif ch1==6:
                break
            else:
                print("Invalid Choice!Please choose your choice correctly")
    elif ch==2:
        while True:
            print("\n\nMARK MENU")
            print("1.Add marks of a Student")
            print("2.Update marks of Student ")
            print("3.Delete marks of Student ")
            print("4.Display marks of Particular Student")
            print("5.Display marks of Students in a Class")
            print("6.Back to main menu")
            ch1=int(input("Enter your choice:"))
            if ch1==1:
                add_marks()
            elif ch1==2:
                update_marks()
            elif ch1==3:
                delete_marks()
            elif ch1==4:
                display_marks()
            elif ch1==5:
                display_marksall()
            elif ch1==6:
                break
            else:
                print("Invalid Choice!Please choose your choice correctly")
    elif ch==3:
        break
    else:
        print("Invalid choice")