def run(): # create tables db = DB() db.create_tables() data = None # loop while True: # display menu Menu.display(Menu.menu_options) # get user input selection = Menu.get_user_input(Menu.menu_options) if selection == 1: # returns a tuple with ( name, country, number of catches ) data = Dialogs.show_create() db.insert(data) if selection == 2: Menu.display(Menu.find_options) # get user input selection = Menu.get_user_input(Menu.find_options) if selection == 1: name = Dialogs.get_string_input('Enter name\n') data = db.find_by_name(name) elif selection == 2: country = Dialogs.get_string_input('Enter country\n') data = db.find_by_country(country) elif selection == 3: catches = Dialogs.get_int_input('Enter number of catches\n') data = db.find_by_catches(str(catches)) else: data = db.get_all() Menu.print_results(data) # update a record if selection == 3: data = db.get_all() Menu.print_results_with_id(data) new_data = Dialogs.show_update() db.update_prepare(new_data) # delete a record if selection == 4: data = db.get_all() Menu.print_results_with_id(data) id_to_delete = Dialogs.show_delete() db.delete_prepare(id_to_delete) data = db.get_all() Menu.print_results_with_id(data) # exit program if selection == 5: db.close_connection() exit()
def execute_dbquerys(self): try: db = DB() connection = db.get_connection() # Append new query with values into emtyDf then assigned to new datafreame i.e df for count, query in self.dbquery_dict.items(): series = pd.Series(query) frame = series.to_frame(name=None) row = 3 frame.to_excel(self.writer, sheet_name='Sheet{c}'.format(c=count), index=False, startrow=row) row = row + len(frame.index) + 2 # Execute newly form query against DB & write output to excel file dbquery_output = pd.read_sql_query(query, connection) dbquery_output.to_excel(self.writer, sheet_name='Sheet{c}'.format(c=count), startrow=row, startcol=0) # if Fare query then rename the listed column as per view requirement. if count == 0 and len(dbquery_output) != 0: dbquery_output.rename(columns={ 'TAR_NO': 'FARE_TAR_NO', 'SEQ_NO': 'FARE_SEQ_NO', 'RM_MCN': 'FARE_MCN', 'LOAD_TRANS': 'FARE_LOAD_TRANS', 'EXPIRE_TRANS': 'FARE_EXPIRE_TRANS' }, inplace=True) self.fare_loadcycle = dbquery_output.LOAD_CYCLE[0] self.fare_expirecycle = dbquery_output.EXPIRE_CYCLE[0] # Eliminate the duplicate columns if any dataframe = dbquery_output.loc[:, ~dbquery_output.columns. duplicated()] self.final_dataframe['{s}'.format( s=self.step[count])] = dataframe self.final_dataframe_len['{s}'.format( s=self.step[count])] = len(dataframe) self.excel_formatter(count) except Exception as e: raise e finally: self.writer.save() db.close_connection()