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()
