final.py

import pandas as pd
import mysql.connector
con = mysql.connector.connect( host ="localhost",user= "u0_a359",passwd= "pass" ,database = "mukesh" )
df1 = pd.read_sql("select * from covid;",con)
print (df1)

def add():
      print("")
      a=input("\t Enter the state:   ")
      print("")
      b=int (input("\t Enter the cases: "))
      print("")
      c=int (input("\t Enter the death: "))
      print("")
      d=int (input("\t Enter the population:  "))
      print("")
      e=int (input("\t Enter the vaccinated_dose1:  "))
      print("")
      f=int (input("\t Enter the vaccinated_dose2:  "))
      q1 = "insert into covid values ( '%s',%d,%d,%d,%d,%s );"%(a,b,c,d,e,f)
      con.cursor().execute(q1)
      con.commit()
      print("")
      print("Record added successfully")
      df2 = pd.read_sql("select * from covid;",con)
      print ("")
      print (df2)

def search ():
      print("")
      print ("\t search record from following options: \n \t 1.state. \n \t 2.cases. \n \t 3.death. \n \t 4.population \n\t 5.vaccinated_dose1: \n\t 6.vaccinated_dose2: ")
      x=int(input("enter the option: "))
      if  x==1:
        a= input("enter state name: ")
        q2="select * from  covid where state='%s';" %a
        df3= pd.read_sql ( q2, con )
        print(df3)
      elif x==2:
          b= int (input("enter cases: "))
          q3="select * from  covid  where cases=%d;" %b
          df4= pd.read_sql ( q3, con )
          print(df4)
      elif x==3:
          c = int ( input (" enter death: "))
          q4="select * from covid  where death=%d;" %c
          df5 = pd.read_sql ( q4, con )
          print(df5)
      elif x==4:
          d= int(input("enter population: "))
          q5="select * from covid where population='%d';" %d
          df6= pd.read_sql ( q5, con )
          print(df6)
      elif x==5:
          e = int ( input (" enter vaccinated_dose1: "))
          q6="select * from covid where vaccinated_dose1='%d';" %e
          df7 = pd.read_sql ( q6, con )
          print(df7)
      elif x==6:
          f = int ( input (" enter vaccinated_dose2:  "))
          q7="select * from covid where vaccinated_dose2='%d';" %f
          df8 = pd.read_sql ( q7, con )
          print(df8)
      else:
          print ("")
          print ("invalid input!")

def update ():
      print ("Update record from following options: \n \t 1.state.  \n \t 2.cases. \n\t 3.death.  \n\t 4.population. \n\t 5.vaccinated_dose1. \n\t 6.vaccinated_dose2. ")
      y = int ( input (" enter choice:  "))
      if y==1:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          a=input("enter existing record: ")
          b=input("enter new record: ")
          q1="update covid set state = '%s' where state = '%s';" %(b,a)
          con.cursor().execute(q1)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )
      elif y==2:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          a=int (input("enter existing record: "))
          b=int (input("enter new record: "))
          q2="update covid set cases = %d where cases = %d;" %(b,a)
          con.cursor().execute(q2)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )
      elif y==3:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          c=int (input("enter existing record: "))
          d=int (input("enter new record: "))
          q2="update covid set death = %d where death = %d;" %(d,c)
          con.cursor().execute(q2)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )
      elif y==4:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          c=int (input("enter existing record: "))
          d=int (input("enter new record: "))
          q2="update covid set population = %d where population = %d;" %(d,c)
          con.cursor().execute(q2)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )
      elif y==5:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          c=int (input("enter existing record: "))
          d=int (input("enter new record: "))
          q2="update covid set vaccinated_dose1= %d where vaccinated_dose1= %d;" %(d,c)
          con.cursor().execute(q2)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )
      elif y==6:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before update")
          print (df1)
          c=int (input("enter existing record: "))
          d=int (input("enter new record: "))
          q2="update covid set vaccinated_dose2= %d where vaccinated_dose2= %d;" %(d,c)
          con.cursor().execute(q2)
          con.commit()
          print (" Record updated successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after update")
          print ( df2 )

def delete():
      print("")
      print ("Delete record from following options: \n \t 1.state. \n \t 2.cases. \n\t 3.death.  \n\t 4.population. \n\t 5.vaccinated_dose1. \n\t 6.vaccinated_dose2.  ")
      print("")
      y = int ( input ("enter choice:  "))
      if y==1:
          df1 = pd.read_sql("select * from covids;",con)
          print ("before delete")
          print (df1)
          c= input("enter record you want to delete: ")
          q1="DELETE from  covid where state ='%s';" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully ")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)
      elif y==2:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before delete")
          print (df1)
          c=int( input("enter record you want to delete: "))
          q1="DELETE from  covid where cases =%d;" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully !")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)
      if y==3:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before delete")
          print (df1)
          c=int( input("enter record you want to delete :  "))
          q1="DELETE from  covid where death=%d;" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully !")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)
      if y==4:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before delete")
          print (df1)
          c=int( input("enter record you want to delete :  "))
          q1="DELETE from  covid where population=%d;" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully !")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)
      if y==5:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before delete")
          print (df1)
          c=int( input("enter record you want to delete :  "))
          q1="DELETE from  covid where vaccinated_dose1=%d;" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully !")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)
      if y==6:
          df1 = pd.read_sql("select * from covid;",con)
          print ("before delete")
          print (df1)
          c=int( input("enter record you want to delete :  "))
          q1="DELETE from  covid where vaccinated_dose2=%d;" %c
          con.cursor().execute(q1)
          con.commit()
          print (" Record deleted successfully !")
          df2= pd.read_sql("select * from covid;",con)
          print ("after delete")
          print ( df2)

def menu():
      print("\t\t MENU  \n \t 1. show all records.\n \t 2. add records.\n \t 3. search records.\n \t 4. delete record. \n \t 5. update record. \n \t 6. exit.")
      print ("")
      x = int( input( "  enter choice number( integer only):  "))

      if  x==1:
          print (df1)
          print ("")
          print ("  To go back to menu press 'y' else 'n'   ")
          print ("")
          y = input ("  back to menu:  ")
          if  y=='y':
              print("")
              menu()
          elif  y=='n':
              print("")
              print ( "  Thanks for using my program 🙏🙏🙂")
          else:
              print("")
              print("  invalid input!  ")
              print ("")
              menu()

      elif x ==2:
          add()
          print ("")
          print ("  To go back to menu press 'y' else 'n'   ")
          print ("")
          y = input ("  back to menu:  ")
          if  y=='y':
              print("")
              menu()
          elif  y=='n':
              print("")
              print ( "  Thanks for using my program 🙏🙏🙂 ")
          else:
              print("")
              print("  invalid input!  ")
              print("")
              menu()

      elif x ==3:
          search()
          print ("")
          print ("  To go back to menu press 'y' else 'n'   ")
          print ("")
          y = input ("  back to menu:  ")
          if  y=='y':
              print("")
              menu()
          elif  y=='n':
              print("")
              print ( "  Thanks for using my program 🙏🙏🙂")
          else:
              print("")
              print("  invalid input!   ")
              print ("")
              menu()

      elif x==4:
        delete()
        print ("")
        print ("  To go back to menu press 'y' else 'n'   ")
        print ("")
        y = input ("  back to menu:  ")
        if  y=='y':
            print("")
            menu()
        elif  y=='n':
            print("")
            print ( "  Thanks for using my program 🙏🙏🙂")
        else:
            print("")
            print("  invalid input!   ")
            print ("")
            menu()

      elif x==5:
        update ()
        print ("")
        print ("  To go back to menu press 'y' else 'n'   ")
        print ("")
        y = input ("  back to menu:  ")
        if  y=='y':
            print("")
            menu()
        elif  y=='n':
            print("")
            print ( "  Thanks for using my program 🙏🙏🙂")
        else:
            print("")
            print("  invalid input!   ")
            print ("")
            menu()

      elif x==6:
        print ("")
        y = input ( "  To EXIT the program press 'y' else 'n':   ")
        if  y=='y':
            print("")
            print ( "  Thanks for using my program 🙏🙏🙂")
        elif  y=='n':
            print("")
            menu ()
        else:
            print("")
            print("  invalid input!   ")
            print ("")
            menu()
      else:
          print("")
          print ("  invalid input!  " )
          print("")
          menu()
con.cursor().execute(menu())