import pymysql
def numchk(i):
    while not(type(i)==int):
        try:
            i=int(i)
        except:
            i=input("You need to give a numerical input. Enter numeric input: ")
    return i

try:
    conn = pymysql.connect(host='localhost', user='root', passwd='', db='python')
except Exception as e:
    print("Cant connect to database. Exiting.")
    print("Error Message: ",e)
    quit()
cur = conn.cursor()
try:
    n=1
    while(n!=5):
        n=input("\n\nEnter 1 to insert, 2 to view, 3 to search by roll no, 4 to delete, 5 to exit: ")
        n=numchk(n)
        n=int(n)
        if(n==1):
            name=input("Enter name: ")
            address=input("Enter Address: ")
            print("Select Gender: 1.Male 2.Female 3.Other")
            gen=input("Enter Gender: ")
            gen=numchk(gen)
            if not(gen==1 or gen ==2 or gen ==3):
               print("Invalid Gender Input")
            elif(len(name)>50):
                print("Name should be within 50 characters")
            else:
                if(gen==1):
                    gender="Male"
                elif(gen==2):
                    gender="Female"
                else:
                    gender="Other"
                
                sql="INSERT INTO student(name,address,gender)VALUES('"+name+"','"+address+"','"+gender+"')"
                if(cur.execute(sql)):
                    r=cur.lastrowid
                    print("Registration Successful. Your roll number is ",r)
                    conn.commit()
                else:
                    print("Insert error")
        elif(n==2):
            if(cur.execute("SELECT * FROM student")):
                d=cur.fetchall()
                print("Showing all data:")
                for row in d:
                    print('Roll: ',row[0],', Name: ',row[1],', Address: ',row[2],', Gender: ',row[3])
            else:
                print("No data present. Insert something first.")
        elif(n==3):
            roll=input("Enter roll number: ")
            roll=numchk(roll)
            roll=str(roll)
            sql="SELECT * FROM student WHERE id='"+roll+"'"
            if(cur.execute("SELECT * FROM student WHERE id=%s",roll)):
                d=cur.fetchone()
                print('Roll: ',d[0],', Name: ',d[1],', Address: ',d[2],', Gender: ',d[3])
            else:
                print("Entered roll number doesnt exist")
        elif(n==4):
            roll=input("Enter roll number of the student you want to delete: ")
            roll=numchk(roll)
            roll=str(roll)
            sql="SELECT * FROM student WHERE id='"+roll+"'"
            if(cur.execute(sql)):
                d=cur.fetchone()
                print("Are you sure, you want to delete this student (Y or N):")
                print('Roll: ',d[0],', Name: ',d[1],', Address: ',d[2],', Gender: ',d[3])
                s=input("Enter Y or N: ")
                if(s=='Y' or s=='y'):
                      sqld="DELETE FROM student WHERE id='"+roll+"'"
                      if(cur.execute(sqld)):
                          print("Data deleted successfully")
                      else:
                          print("Deleted failed")
                      conn.commit()
                elif(s=='N' or s=='n'):
                      print("Data deletion cancelled by user")
                else:
                      print("Invalid Input")
            else:
                print("Entered roll number doesnt exist")
        elif(n==5):
            print("Exiting")
            cur.close()
            conn.close()
            exit()
        else:
            print("Invalid Input")
except Exception as e:
    print("Error Occured.\nError Message: ",e)