def add_new_record(request): if request.method == 'POST': print("Add Row Form submit") form = AddRecordForm(request.POST) if form.is_valid(): lookupid = form.cleaned_data.get("loan_mdm_lookup_id") creditscoremin = form.cleaned_data.get("CreditScoreMin") creditscoremax = form.cleaned_data.get("CreditScoreMax") loanamtmin = form.cleaned_data.get("LoanAmountMin") loanamtmax = form.cleaned_data.get("LoanAmountMax") interestratepct = form.cleaned_data.get("InterestRatePct") durationmonths = form.cleaned_data.get("DurationMonths") efffromdate = form.cleaned_data.get("eff_from_date") efftodate = form.cleaned_data.get("eff_to_date") try: cursor = db.cursor() sql_script = 'insert into loan_table_lookup( loan_mdm_lookup_id, CreditScoreMin, CreditScoreMax, LoanAmountMin, LoanAmountMax, InterestRatePct, DurationMonths, eff_from_date, eff_to_date) VALUES' row_str = str(lookupid) + "," + str(creditscoremin) + "," + str(creditscoremax) + "," + str(loanamtmin) + "," + str(loanamtmax) + "," + str(interestratepct) + "," + str(durationmonths) + "," + "str_to_date('" + efffromdate.strftime('%m/%d/%Y') + "','%m/%d/%Y')" + "," + "str_to_date('" + efftodate.strftime('%m/%d/%Y') + "','%m/%d/%Y')" sql_insert_script = sql_script + "(" + row_str + ");" print(sql_insert_script) cursor.execute(sql_insert_script) db.commit() except errors.Error as e: print(e) finally: cursor.close() return redirect('/bankmgr/first/loanmdm') else: form = AddRecordForm() return render(request, 'addform.html', {'form': form})
def update(request): lookupid = request.POST["loan_mdm_lookup_id"] creditscoremin = request.POST["CreditScoreMin"] creditscoremax = request.POST["CreditScoreMax"] loanamtmin = request.POST["LoanAmountMin"] loanamtmax = request.POST["LoanAmountMax"] interestratepct = request.POST["InterestRatePct"] durationmonths = request.POST["DurationMonths"] efffromdate = request.POST["eff_from_date"] efftodate = request.POST["eff_to_date"] sql_update_query = """update loan_table_lookup set loan_mdm_lookup_id = '"""+str(lookupid)+"""' , CreditScoreMin = '"""+str(creditscoremin)+"""' , CreditScoreMax = '"""+str(creditscoremax)+"""' , LoanAmountMin = '"""+str(loanamtmin)+"""' , LoanAmountMax = '"""+str(loanamtmax)+"""' , InterestRatePct = '"""+str(interestratepct)+"""' , DurationMonths = '"""+str(durationmonths)+"""' , eff_from_date = '"""+str(efffromdate)+"""' , eff_to_date = '"""+str(efftodate)+"""' where loan_mdm_lookup_id = """ + str(lookupid) + ';' try: cursor = db.cursor() print(sql_update_query) cursor.execute(sql_update_query) db.commit() except errors.Error as e: print(e) finally: cursor.close() return redirect("http://127.0.0.1:8000/bankmgr/first/loanmdm")
def showTable(): l_data_show.clear() conn = sql.connect(host='localhost', port=3306, user='******', password='******', db='loan_proj') cursor = db.cursor() cursor.execute("select * from loan_table_lookup;") for loan_mdm_lookup_id, CreditScoreMin, CreditScoreMax, LoanAmountMin, LoanAmountMax, InterestRatePct, DurationMonths, eff_from_date, eff_to_date in cursor.fetchall( ): l_data_show.append({ "loan_mdm_lookup_id": loan_mdm_lookup_id, "CreditScoreMin": CreditScoreMin, "CreditScoreMax": CreditScoreMax, "LoanAmountMin": LoanAmountMin, "LoanAmountMax": LoanAmountMax, "InterestRatePct": InterestRatePct, "DurationMonths": DurationMonths, "eff_from_date": eff_from_date, "eff_to_date": eff_to_date }) master_data = tuple(l_data_show) cursor.close() return master_data
def syncTableDict(): cursor = db.cursor() cursor.execute("select * from loan_table_lookup;") for loan_mdm_lookup_id, CreditScoreMin, CreditScoreMax, LoanAmountMin, LoanAmountMax, InterestRatePct, DurationMonths, eff_from_date, eff_to_date in cursor.fetchall(): l_data[loan_mdm_lookup_id]={"loan_mdm_lookup_id": loan_mdm_lookup_id , "CreditScoreMin": CreditScoreMin , "CreditScoreMax": CreditScoreMax , "LoanAmountMin": LoanAmountMin , "LoanAmountMax": LoanAmountMax , "InterestRatePct": InterestRatePct , "DurationMonths": DurationMonths , "eff_from_date": eff_from_date , "eff_to_date": eff_to_date} cursor.close()
def delete(request,loan_mdm_lookup_id): syncTableDict() try: cursor = db.cursor() SingleRowData = l_data[loan_mdm_lookup_id] lookup_id = SingleRowData['loan_mdm_lookup_id'] sql_delete_script='delete from loan_table_lookup where loan_mdm_lookup_id =' + str(lookup_id) + ';' print(sql_delete_script) cursor.execute(sql_delete_script) db.commit() except errors.Error as e: print(e) finally: cursor.close() return redirect("/bankmgr/first/loanmdm")