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