def updateToRecovered(personId): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; recoverDateValidated = False; recoverDate = 'YYYY-MM-DD'; confirmValidated = False; confirm = 0; print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\nUPDATE TO RECOVERED\n\nEnter recovery date:") while(not recoverDateValidated): try: print("\nRecovery Date\n--------------") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); recoverDate = datetime.date(year, month, day); try: cur.callproc('validate_recoveryDate', [personId, recoverDate]) recoverDateValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not confirmValidated): print("\nAre you sure you want to update person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): try: cur.callproc('infected_to_recovered_', [personId, recoverDate]) cnx.commit() print("Successfully inserted in recovered and deleted from infected!") covid19_tracker_interface.update() except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\nChange of status aborted") covid19_tracker_interface.update() elif(confirm == 2): print("\nChange of status aborted") update_status();
def top5InfectedRegions(): import covid19_tracker_interface global cur global cnx cur = covid19_tracker_interface.cur cnx = covid19_tracker_interface.cnx print("\n" + covid19_tracker_interface.line_separator(".", 65)) try: cur.callproc('top5_most_infected_regions') rows = cur.fetchall() columns = [] for cd in cur.description: columns.append(cd[0]) print("\nTop 5 most infected regions:\n" + tabulate(rows, headers="keys", tablefmt='psql')) except: print("Cannot view top 5 infected regions at this time.")
def displayStatistics(): import covid19_tracker_interface global cur global cnx cur = covid19_tracker_interface.cur cnx = covid19_tracker_interface.cnx print("\n" + covid19_tracker_interface.line_separator(".", 65)) print( "(0) Exit application\n(1) Go back\n\nStatistics:\n-----------\n(2) Top 5 most infected regions\n(3) Regions with specific risk levels\n(4) Actively infected people before or after date" ) count = 0 lineBreak = "\n" actionValidated = False while (not actionValidated): if (count > 0): lineBreak = "" try: numAction = int(input(lineBreak + "Enter a number (0-4): ")) if (numAction == 0): cnx.close() actionValidated = True print(exitMsg) os._exit(0) elif (numAction == 1): covid19_tracker_interface.view() actionValidated = True elif (numAction == 2): top5InfectedRegions() actionValidated = True elif (numAction == 3): regionsOfRiskLevel() actionValidated = True elif (numAction == 4): viewActivelyInfectedBeforeAfter() actionValidated = True count += 1 except Exception as e: count += 1 print("\n") displayStatistics()
def regionsOfRiskLevel(): import covid19_tracker_interface global cur global cnx cur = covid19_tracker_interface.cur cnx = covid19_tracker_interface.cnx print("\n" + covid19_tracker_interface.line_separator(".", 65)) count = 0 lineBreak = "\n" actionValidated = False while (not actionValidated): if (count > 0): lineBreak = "" try: level = int(input("Choose a risk level (0-3): ")) drawRegionRisk(level) actionValidated = True count += 1 except: count += 1 print("\n")
def displayTables(): import covid19_tracker_interface global cur global cnx cur = covid19_tracker_interface.cur cnx = covid19_tracker_interface.cnx print("\n" + covid19_tracker_interface.line_separator(".", 65)) tables = [ "Person", "Infected", "Recovered", "Dead", "Region", "Hospital", "Hospital_visit", "Test" ] rowCount = 2 count = 0 lineBreak = "\n" actionValidated = False print("(0) Exit application\n(1) Go back\n\nTables:\n-------") for x in tables: print("(" + str(rowCount) + ") " + x) rowCount += 1 while (not actionValidated): if (count > 0): lineBreak = "" try: numAction = int(input(lineBreak + "Enter a number (0-9): ")) if (numAction == 0): cnx.close() actionValidated = True print(exitMsg) os._exit(0) elif (numAction == 1): covid19_tracker_interface.view() actionValidated = True elif (numAction == 2): viewAllPerson() actionValidated = True elif (numAction == 3): viewAllInfected() actionValidated = True elif (numAction == 4): viewAllRecovered() actionValidated = True elif (numAction == 5): viewAllDead() actionValidated = True elif (numAction == 6): viewAllRegion() actionValidated = True elif (numAction == 7): viewAllHospital() actionValidated = True elif (numAction == 8): viewAllHospitalVisit() actionValidated = True elif (numAction == 9): viewAllTest() actionValidated = True count += 1 except ValueError: count += 1 print("\n") displayTables()
def viewActivelyInfectedBeforeAfter(): import covid19_tracker_interface global cur global cnx cur = covid19_tracker_interface.cur cnx = covid19_tracker_interface.cnx print("\n" + covid19_tracker_interface.line_separator(".", 65)) print( "View actively infected people before (2) or after (3) a specified date" ) count = 0 lineBreak = "\n" actionValidated = False tArr = ["before", "after"] while (not actionValidated): if (count > 0): lineBreak = "" try: beforeOrAfter = int( input( "\n(0) Exit Application\n(1) Go back\n\nEnter a number (0-3): " )) if (beforeOrAfter == 0): cnx.close() actionValidated = True print(exitMsg) os._exit(0) elif (beforeOrAfter == 1): displayStatistics() elif (beforeOrAfter == 2 or beforeOrAfter == 3): year = int(input("Enter a year [YYYY]: ")) month = int(input("Enter a month [M]: ")) day = int(input("Enter a day [D]: ")) date = datetime.date(year, month, day) try: cur.callproc('infected_before_afterDate', [beforeOrAfter, date]) if (cur.rowcount > 0): rows = cur.fetchall() columns = [] for cd in cur.description: columns.append(cd[0]) print("\nActively infected people (" + tArr[beforeOrAfter - 2] + " " + str(year) + "-" + str(month) + "-" + str(day) + "):\n" + tabulate(rows, headers="keys", tablefmt='psql') + "\nTuple count: " + str(cur.rowcount)) actionValidated = True else: print( "\n No records found for your specifications. Try a different date!" ) except Exception as e: count += 1 print(e) print("Cannot retrieve people infected at this time") except: count += 1
def insert_hospital(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; nameValidated = False; regionValidated = False; regionValidated2 = False; confirmValidated = False; hospitalName = ''; region = ''; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nINSERT ON HOSPITAL" + "\nFields: [id, name, regionId]\n\nEnter fields\n------------"); while(not regionValidated): region = str(input("Hospital region: ")); if(not region.replace(" ", "").isalpha()): print("Please enter a string") else: try: cur.callproc('verify_hospital_regionName', [region]) print("\nRegion verified!\n") try: query = 'SELECT getRegionId(%s)' tempReg = cur.execute(query, region) tempReg2 = cur.fetchall()[0] tempReg = getNum("%s" % str(tempReg2)) region = tempReg regionValidated = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") regionValidated = True; except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('not exist' in err): print("Refer to the following table below") try: query = 'SELECT DISTINCT(regionName) FROM region'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif('Multiple instances'): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT id, regionName, country, latitude, longitude FROM region WHERE regionName = %s'; cur.execute(query, (region)) return_table(cur) except Exception as e: print(e) while(not regionValidated2): try: region = int(input("Region ID: ")); try: cur.callproc('does_regionId_exist', [region]) regionValidated2 = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter a number") regionValidated = True; while(not nameValidated): hospitalName = str(input("Hospital name: ")); if(not hospitalName.replace(" ", "").isalpha()): print("Please enter a string") else: try: cur.callproc('verify_hospital', [hospitalName, region]) print("\nHospital name verified!\n") nameValidated = True; except pymysql.Error as e: err = e.args[1] print("\n" + err) print("Use another name for hospital.\n") while(not confirmValidated): print("\nAre you sure you want to insert into hospital?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm == 1): try: cur.callproc('insert_in_hospital', [hospitalName, int(region)]) cnx.commit(); print("\nSuccessfully inserted!\n") confirmValidated = True; covid19_tracker_interface.create(); except pymysql.Error as e: print("\n" + e.args[1]) elif(confirm == 2): print("Insert of " + hospitalName + " aborted.") confirmValidated = True; covid19_tracker_interface.create(); else: print("\nPlease enter a number 1-2")
def insert_person(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; fNameValidated = False; lNameValidated = False; sexValidated = False; dobValidated = False; regionValidated = False; regionValidated2 = False; infectDateValidated = False; admitHospitalValidated = False; admitHospitalValidated2 = False; hospitalValidated = False; confirmValidated = False; firstName = ''; lastName = ''; sex = ''; dob = 'YYYY-MM-DD'; homeRegion = 0; currentRegion = 0; hospital = 0; count = 0; lineBreak = "\n"; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nINSERT ON PERSON" + "\nFields: [id, firstName, lastName, dob, sex, homeRegion, currentRegion]\n\nEnter fields\n------------"); while(not fNameValidated): firstName = str(input("First name: ")); if(not firstName.replace(" ", "").isalpha()): print("Please enter a string") else: fNameValidated = True; while(not lNameValidated): lastName = str(input("Last name: ")); if(not lastName.replace(" ", "").isalpha()): print("Please enter a string") else: lNameValidated = True; while(not sexValidated): sex = str(input("Sex: ")).lower() if(sex == 'male' or sex == 'female' or sex == 'other'): sexValidated = True; else: print("Please enter string: male, female, or other") while(not dobValidated): try: print("Date of Birth") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); dob = datetime.date(year, month, day); try: cur.callproc('validate_dateOfBirth', [dob]) dobValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not regionValidated): homeRegion = str(input("Home Region: ")); currentRegion = str(input("Current Region: ")); if((not homeRegion.replace(" ", "").isalpha()) or (not currentRegion.replace(" ", "").isalpha())): print("Please enter a string") else: try: cur.callproc('verify_person_regionName', [homeRegion, currentRegion]) print("\nRegions successfully validated!") try: query = 'SELECT getRegionId(%s)'; tempHome = cur.execute(query, homeRegion) tempHome2 = cur.fetchall()[0] tempHome = getNum("%s" % str(tempHome2)) homeRegion = tempHome tempCurrent = cur.execute(query, currentRegion) tempCurrent2 = cur.fetchall()[0] tempCurrent = getNum("%s" % str(tempCurrent2)) currentRegion = tempCurrent regionValidated = True; except pymysql.Error as e: print("\n" + err + "\n") regionValidated = true; except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(regionName) FROM region'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif('Multiple instances' in err): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT id, regionName, country, latitude, longitude FROM region WHERE regionName = %s or regionName = %s'; cur.execute(query, (homeRegion, currentRegion)) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not regionValidated2): try: homeRegion = int(input("Home Region ID: ")); currentRegion = int(input("Current Region ID: ")) try: cur.callproc('does_regionId_exist', [homeRegion]) cur.callproc('does_regionId_exist', [currentRegion]) regionValidated2 = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") except Exception as e: print("Please enter a number") regionValidated = True; print("\n" + firstName + " " + lastName + " will be added to the list of infected people.\n" + "Please provide a few more details:\n"); while(not infectDateValidated): try: print("Infection Date") iyear = int(input("YYYY: ")); imonth = int(input("MM: ")); iday = int(input("DD: ")); infectDate = datetime.date(iyear, imonth, iday); try: cur.callproc('validate_infectionDate', [infectDate, dob]) infectDateValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") try: cur.callproc('check_hospital_in_region', [int(currentRegion)]); while(not admitHospitalValidated): print("\n\nAdmit " + firstName + " " + lastName + " to a hospital?\n(1) Yes\n(2) No") try: admitToHospital = int(input("\nEnter a number (1-2): ")); if(admitToHospital == 1): admitHospitalValidated = True; while(not hospitalValidated): hospital = str(input("Enter hospital name: ")) for x in hospital: if(x.isalpha() or x == ' '): continue; else: print("Please enter a string") break; try: cur.callproc('hospital_exists_in_region', [hospital, int(currentRegion)]) print("Hospital successfully validated!") try: query = 'SELECT getHospId(%s, %s)'; tempHospital = cur.execute(query, (hospital, currentRegion)) tempHospital2 = cur.fetchall()[0] tempHospital = getNum("%s" % str(tempHospital2)) hospital = tempHospital hospitalValidated = True; admitHospitalValidated = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('does not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(name) FROM hospital where regionId = %s'; cur.execute(query, (int(currentRegion))) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif(admitToHospital == 2): admitHospitalValidated = True; except: print("\nPlease enter a number 1-2") except pymysql.Error as e: admitToHospital = 0; print("\n" + e.args[1]) print(firstName + " " + lastName + " will not be admitted to a hospital at this time.\n") while(not confirmValidated): print("\nAre you sure you want to insert into person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm == 1): try: cur.callproc('insert_person', [firstName, lastName, dob, sex, homeRegion, currentRegion, infectDate, admitToHospital, hospital]) cnx.commit(); print("\nSuccessfully inserted!\n") confirmValidated = True; covid19_tracker_interface.create(); except pymysql.Error as e: print("\n" + e.args[1]) elif(confirm == 2): print("Insert of " + firstName + " " + lastName + " aborted.") confirmValidated = True; covid19_tracker_interface.create(); else: print("\nPlease enter a number 1-2")
def insert_region(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; nameValidated = False; countryValidated = False; latValidated = False; longValidated = False; totalPopValidated = False; lockdownValidated = False; confirmValidated = False; region = ''; country = ''; lat = 0; lon = 0; totalPop = 0; lockdown = False; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nINSERT ON REGION" + "\nFields: [id, regionName, country, latitude, longitude, totalPop, riskLevel, percentInfected]\n\nEnter fields\n------------"); while(not nameValidated): print("Region name can be anywhere an outbreak has occured\n" + "including a city, country, cruise ship, etc.\n") region = str(input("Region name: ")) if(not region.replace(" ", "").isalpha()): print("Please enter a string") else: nameValidated = True; while(not countryValidated): country = str(input("Country name: ")) if(not country.replace(" ", "").isalpha()): print("Please enter a string") else: countryValidated = True; while(not latValidated): try: lat = float(input("Latitude: ")) if(not isinstance(lat, float) and not isinstance(lat, int)): print("Please enter a number") else: latValidated = True; except: print("Please enter a number") while(not longValidated): try: lon = float(input("Longitude: ")) if(not isinstance(lon, float) and not isinstance(lon, int)): print("Please enter a number") else: longValidated = True; except: print("Please enter a number") while(not totalPopValidated): try: totalPop = int(input("Total Population: ")) if(not isinstance(totalPop, int)): print("Please enter a number") else: totalPopValidated = True; except: print("Please enter a number") while(not lockdownValidated): lockdown = input(region + " on lockdown? (y/n): ") if((lockdown is not 'y') and (lockdown is not 'n')): print("Please enter y or n") else: if(lockdown == 'y'): lockdown = True; elif(lockdown == 'n'): lockdown = False; lockdownValidated = True; try: cur.callproc('verify_uniqueRegion', [region, country, lat, lon]) print("Region fields validated!") except pymysql.Error as e: print("\n" + e.args[1] + "\n"); print("Try again?\n(1) Yes\n(2) Abort insert region") tryAgain = int(input("Enter a number (1-2)")) if(tryAgain == 1): insert_region(); elif(tryAgain == 2): create(); else: print("Please enter a number 1-2") while(not confirmValidated): print("\nAre you sure you want to insert into region?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm == 1): try: cur.callproc('insert_region', [region, country, lat, lon, totalPop, lockdown]) cnx.commit(); print("\nSuccessfully inserted!\n") confirmValidated = True; covid19_tracker_interface.create(); except pymysql.Error as e: print("\n" + e.args[1]) elif(confirm == 2): print("Insert of " + region +" aborted.") confirmValidated = True; covid19_tracker_interface.create(); else: print("\nPlease enter a number 1-2")
def updateToInfected(personId): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; infectDateValidated = False; infectDate = 'YYYY-MM-DD'; confirmValidated = False; admitHospitalValidated = False; admitToHospital = 0; considerHospital = False; hospitalValidated = False; currentRegion = 0; confirm = 0; try: curRegQuery = 'SELECT getCurrentRegion(%s)'; tempReg = cur.execute(curRegQuery, (personId)) tempReg2 = cur.fetchall()[0] tempReg = getNum("%s" % str(tempReg2)) currentRegion = tempReg considerHospital = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\nUPDATE TO INFECTED\n\nEnter infection date:") while(not infectDateValidated): try: print("\nInfection Date\n----------------") year = int(input("YYYY: ")); month = int(input("MM: ")); day = int(input("DD: ")); infectDate = datetime.date(year, month, day); try: cur.callproc('validate_InfectDate', [personId, infectDate]) infectDateValidated = True; except pymysql.Error as e: print(e.args[1] + "\n") except Exception as e: print("Please enter using the specified date format\n") while(not confirmValidated): print("\nAre you sure you want to update person?\n(1) Yes\n(2) No") confirm = int(input("\nEnter a number (1-2): ")) if(confirm is not 1 and confirm is not 2): print("\nPlease enter a number 1-2") else: confirmValidated = True; if(confirm == 1): try: cur.callproc('recovered_to_infected', [personId, infectDate]) print("Successfully inserted in dead and deleted from infected!") if(considerHospital): try: cur.callproc('check_hospital_in_region', [int(currentRegion)]); while(not admitHospitalValidated): print("\n\nAdmit to a hospital?\n(1) Yes\n(2) No") try: admitToHospital = int(input("\nEnter a number (1-2): ")); if(admitToHospital == 1): admitHospitalValidated = True; while(not hospitalValidated): hospital = str(input("Enter hospital name: ")) for x in hospital: if(x.isalpha() or x == ' '): continue; else: print("Please enter a string") break; try: cur.callproc('hospital_exists_in_region', [hospital, int(currentRegion)]) print("Hospital successfully validated!") try: query = 'SELECT getHospId(%s, %s)'; tempHospital = cur.execute(query, (hospital, currentRegion)) tempHospital2 = cur.fetchall()[0] tempHospital = getNum("%s" % str(tempHospital2)) hospital = tempHospital hospitalValidated = True; admitHospitalValidated = True; hospQuery = 'INSERT INTO hospital_visit() VALUES(DEFAULT, %s, %s, %s, DEFAULT, DEFAULT)' cur.execute(hospQuery, (int(personId), int(hospital), infectDate)); print("Person admitted to hospital.\n") except pymysql.Error as e: print("\n" + e.args[1] + "\n") except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('does not exist' in err): print("Refer to the following table below:") try: query = 'SELECT DISTINCT(name) FROM hospital where regionId = %s'; cur.execute(query, (int(currentRegion))) return_table(cur) except pymysql.Error as e: print(e.args[1]) elif(admitToHospital == 2): admitHospitalValidated = True; except: print("\nPlease enter a number 1-2") except pymysql.Error as e: admitToHospital = 0; print("\n" + e.args[1]) print("Person will not be admitted to a hospital at this time.\n") cnx.commit() covid19_tracker_interface.update() except pymysql.Error as e: print("\n" + e.args[1] + "\n") print("\nChange of status aborted") covid19_tracker_interface.update() elif(confirm == 2): print("\nChange of status aborted") update_status();
def update_status(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; personTable = 'SELECT * FROM person'; count = 0; personId = 0; numAction = 0; actionValidated = False; validateId = False; lineBreak = "\n"; print("\n" + covid19_tracker_interface.line_separator(".", 90) + "\n(0) Exit Application\n(1) Go back"); print("\nSelect id of person to change status for from the table below: "); cur.execute(personTable) return_table(cur); while(not validateId): try: personId = int(input("Person ID: ")) if(not isinstance(personId, int)): print("Please enter a number") else: try: cur.callproc('does_personId_exist', [int(personId)]) print("ID validated!\n") validateId = True; except pymysql.Error as e: err = e.args[1] print(err + "\n") except: print("Please enter a number") while(not actionValidated): print("\nSelect a Status Change:\n-----------------------" + "\n(2) Update to infected\n(3) Update to recovered\n(4) Update to dead"); if(count > 0): lineBreak = ""; try: numAction = int(input(lineBreak + "Enter a number (0-4): ")); if(not isinstance(numAction, int)): print("\nPlease enter a number"); else: if(numAction > 5 or numAction < 0): print("\nPlease enter a number 0-4") elif(numAction == 0): print(exitMsg); actionValidated = True; os._exit(0); cnx.close(); elif(numAction == 1): covid19_tracker_interface.update() actionValidated = True; elif(numAction == 2): updateToInfected(personId); actionValidated = True; elif(numAction == 3): updateToRecovered(personId) actionValidated = True; elif(numAction == 4): updateToDead(personId) actionValidated = True; else: print("\nPlease enter a number 0-4") count += 1; actionValidated = True; except Exception as e: print("\nPlease enter a number 0-4")
def delete_region(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; confirmValidated = False; nameValidated = False; nameValidated2 = False; regionName = ''; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nDELETE FROM REGION" + "\nSpecify region name to delete\n\nEnter field\n-----------"); while(not nameValidated): regionName = input("regionName: ") if(not regionName.replace(" ", "").isalpha()): print("Please enter a string") else: try: cur.callproc('verify_regionName', [regionName]) print("\nRegion verified!\n") try: query = 'SELECT getRegionId(%s)' tempRegion = cur.execute(query, regionName) tempRegion2 = cur.fetchall()[0] tempRegion = getNum("%s" % str(tempRegion2)) regionName = tempRegion nameValidated = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") nameValidated = True; except pymysql.Error as e: err = e.args[1] print("\n" + e.args[1] + "\n") if('Multiple instance' in err): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT id, regionName, country, latitude, longitude FROM region WHERE regionName = %s'; cur.execute(query, (regionName)) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not nameValidated2): try: regionName = int(input("Region ID: ")); try: cur.callproc('does_regionId_exist', [regionName]) nameValidated2 = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") except Exception as e: print("Please enter a number") nameValidated = True; elif('not exist' in err): print("Refer to the following table below") try: query = 'SELECT DISTINCT(regionName) FROM region'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not confirmValidated): print("\nAre you sure you want to delete from region?\n(1) Yes\n(2) No") try: confirm = int(input("Enter a number (1-2): ")) if(not isinstance(confirm, int)): print("Please enter a number") except: print("Please enter a number") if(confirm == 1): try: cur.callproc('delete_region', [regionName]) cnx.commit(); print("\nSuccessfully deleted!\n") confirmValidated = True; covid19_tracker_interface.delete(); except pymysql.Error as e: print("\n" + e.args[1] + "\n") confirmValidated = True; covid19_tracker_interface.delete(); elif(confirm == 2): print("Deletion of region aborted.") confirmValidated = True; covid19_tracker_interface.delete(); else: print("\nPlease enter a number 1-2")
def delete_person(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; fNameValidated = False; lNameValidated = False; firstAndLastValidated = False; personIdValidated = False; confirmValidated1 = False; confirmValidated2 = False; personIdValidated = False; personId = 0; firstName = ''; lastName = ''; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nDELETE FROM PERSON" + "\nSpecify person to delete\n\nEnter fields\n------------"); while(not firstAndLastValidated): while(not fNameValidated): firstName = input("First name: ") if(not firstName.replace(" ", "").isalpha()): print("Please enter a string") else: fNameValidated = True; while(not lNameValidated): lastName = input("Last name: ") if(not lastName.replace(" ", "").isalpha()): print("Please enter a string") else: lNameValidated = True; try: query = 'SELECT * FROM person WHERE firstName = %s AND lastName = %s'; cur.execute(query, (firstName, lastName)); if(cur.rowcount == 1): while(not confirmValidated1): print("\nAre you sure you want to delete from person?\n(1) Yes\n(2) No") try: confirm = int(input("Enter a number (1-2): ")) if(not isinstance(confirm, int)): print("Please enter a number") except: print("Please enter a number") if(confirm == 1): try: query = 'SELECT getPersonId(%s, %s)'; tempPerson = cur.execute(query, (firstName, lastName)) tempPerson2 = cur.fetchall()[0] tempPerson = getNum("%s" % str(tempPerson2)) personId = tempPerson try: cur.callproc('delete_person', [personId]) cnx.commit(); print("\nSuccessfully deleted!\n") confirmValidated1 = True; firstAndLastValidated = True; covid19_tracker_interface.delete(); except pymysql.Error as e: print("\n" + e.args[1] + "\n") confirmValidated1 = True; firstAndLastValidated = True; covid19_tracker_interface.delete(); except Exception as e: print(e) confirmValidated1 = True; firstAndLastValidated = True; covid19_tracker_interface.delete(); elif(confirm == 2): print("Deletion of " + str(firstName) + " " + str(lastName) + " aborted.") confirmValidated = True; covid19_tracker_interface.delete(); else: print("\nPlease enter a number 1-2") elif(cur.rowcount == 0): print("\nNo person found with name " + str(firstName) + " " + str(lastName) + "\n") print("Refer to the following table below:") try: f = 'First Name' l = 'Last Name' query = 'SELECT firstName AS %s, lastName AS %s FROM person'; cur.execute(query, (f,l)) return_table(cur) fNameValidated = False; lNameValidated = False; except pymysql.Error as e: print("\n" + e.args[1] + "\n") elif(cur.rowcount > 1): print("\nMultiple instances of " + str(firstName) + " " + str(lastName) + "exist.") print("Please enter a unique id.\nRefer to the table below:") try: query = 'SELECT id, firstName, lastName FROM person WHERE firstName = %s AND lastName = %s'; cur.execute(query, (str(firstName), str(lastName))) return_table(cur) except Exception as e: print(e) while(not personIdValidated): try: personId = int(input("Person ID: ")); cur.callproc('does_personId_exist', [personId]) personIdValidated = True; while(not confirmValidated2): print("\nAre you sure you want to delete " + str(firstName) + " " + str(lastName) + "?\n(1) Yes\n(2) No") try: confirm = int(input("Enter a number (1-2): ")) if(not isinstance(confirm, int)): print("Please enter a number") except: print("Please enter a number") if(confirm == 1): try: cur.callproc('delete_person', [personId]) cnx.commit(); print("\nSuccessfully deleted!\n") confirmValidated2 = True; firstAndLastValidated = True; covid19_tracker_interface.delete(); except pymysql.Error as e: print("\n" + e.args[1] + "\n") confirmValidated1 = True; firstAndLastValidated = True; covid19_tracker_interface.delete(); elif(confirm == 2): print("Deletion of " + str(firstName) + " " + str(lastName) + " aborted.") confirmValidated2 = True; covid19_tracker_interface.delete(); else: print("Please enter a number 1-2") except pymysql.Error as e: print(e.args[1] + "\n") except pymysql.Error as e: print("\n" + e.args[1] + "\n")
def delete_hospital(): import covid19_tracker_interface global cur; global cnx; cur = covid19_tracker_interface.cur; cnx = covid19_tracker_interface.cnx; confirmValidated = False; nameValidated = False; nameValidated2 = False; hospitalName = ''; print("\n" + covid19_tracker_interface.line_separator(".", 65) + "\nDELETE FROM HOSPITAL" + "\nSpecify hospital to delete\n\nEnter field\n-----------"); while(not nameValidated): hospitalName = input("name: ") if(not hospitalName.replace(" ", "").isalpha()): print("Please enter a string") else: try: cur.callproc('does_hospital_exist', [hospitalName]) print("\nHospital verified!\n") try: query = 'SELECT getHospitalId(%s)' tempHospital = cur.execute(query, hospitalName) tempHospital2 = cur.fetchall()[0] tempHospital = getNum("%s" % str(tempHospital2)) hospitalName = tempHospital nameValidated = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") regionValidated = True; except pymysql.Error as e: err = e.args[1] print("\n" + err + "\n") if('Multiple instance' in err): print("Please enter the unique id of the desired region.\nRefer to the table below:") try: query = 'SELECT t.id AS id, name AS %s, regionName AS %s FROM (SELECT id, name, regionId FROM hospital WHERE name = %s) t JOIN region WHERE region.id = t.regionId'; cur.execute(query, ("Hospital Name", "Region Name",str(hospitalName))) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not nameValidated2): try: hospitalName = int(input("Hospital ID: ")); try: cur.callproc('does_hospitalId_exist', [hospitalName]) nameValidated2 = True; except pymysql.Error as e: print("\n" + e.args[1] + "\n") except Exception as e: print("Please enter a number") nameValidated = True; elif('not exist' in err): print("Refer to the following table below") try: query = 'SELECT DISTINCT(name) FROM hospital'; cur.execute(query) return_table(cur) except pymysql.Error as e: print(e.args[1]) while(not confirmValidated): print("\nAre you sure you want to delete from hospital?\n(1) Yes\n(2) No") try: confirm = int(input("Enter a number (1-2): ")) if(not isinstance(confirm, int)): print("Please enter a number") except: print("Please enter a number") if(confirm == 1): try: cur.callproc('delete_hospital', [hospitalName]) cnx.commit(); print("\nSuccessfully deleted!\n") confirmValidated = True; covid19_tracker_interface.delete(); except pymysql.Error as e: print("\n" + e.args[1] + "\n") confirmValidated = True; covid19_tracker_interface.delete(); elif(confirm == 2): print("Deletion of hospital aborted.") confirmValidated = True; covid19_tracker_interface.delete(); else: print("\nPlease enter a number 1-2")