visual studio code - How to get multiple results from sql table with python

Question

I am currently working on a school project whereby I need to get the total monthly debt repayment of the user. However, each user (e.g. Account number: 12345-6) has multiple different types of loan with different methods of calculating interest rates.

Initially, I was going with this code (account number hardcoded for now):

account_no = "11111-3"

def getsaving(account_no): 
    query = """
        select SUBSTRING(LoanID, 1, 2)
        from Account_Delinquency
        where AccountNo = '{}'
        """ .format(account_no)

and it would return "PL"/"HL"/"CL" (personal loan, home loan, car loan) etc. After which, I would use if/else, which I'm not too sure if I've coded correctly, to determine the method for calculating monthly repayment like this:

def meet_criteria(account_no):
    if getsaving(account_no) in ["HL"]:
        HLmonthly()
    else:
        if getsaving(account_no) in ["RL"]:
            RLmonthly()
        else:
            if getsaving(account_no) in ["EL"]:
                ELmonthly()
            else:
                if getsaving(account_no) in ["CL"]:
                    CLmonthly()
                else:
                    PLmonthly()

However, I realized that this code would not work if there were more than one loan under this user. For example, if he has a car loan, home loan and a personal loan or maybe two personal loans, this would not work. I would also be unable to add the the loan amounts together to get the total amount this user needs to pay monthly for all his debt.

I'm not sure if it is necessary but these are the codes to calculate monthly repayment for each type of loan (only Home loan and renovation loan for now). They are in a different file for now as I am still unclear as to how to combine these codes. Current age, monthly income etc are also only hardcoded for now but I am supposed to get it from another table in sql.

import requests
import json

currentAge1 = "45"
totalMonthlyIncome1 = "1000"
repaymentPeriodhome ="35"
loanAmt = "20000"
repaymentPeriod ="5"

url = "https://api.ocbc.com:8243/Home_Loan/1.0?currentAge1={}&totalMonthlyIncome1={}&totalMonthlyDebt1=0&outstandingLoans1=2&repaymentPeriod={}".format(currentAge1,totalMonthlyIncome1,repaymentPeriodhome)
key = "Bearer 6abd7ab92fdf9919135e69cadacfd2b2"

url1 = "https://api.ocbc.com:8243/Renovation_Loan/1.0?homeLoanCustomer=falso&loanAmt={}&repaymentPeriod={}".format(loanAmt,repaymentPeriod)
key1 = "Bearer 6abd7ab92fdf9919135e69cadacfd2b2"

headers    = {'Accept': 'Application/json',
              'Authorization': key}

headers1    = {'Accept': 'Application/json',
              'Authorization': key1}

response = requests.get(url, headers=headers)

response1 = requests.get(url1, headers=headers1)

results = response.json()
print(json.dumps(results, indent=4, separators=(',',':')))

results1 = response1.json()
print(json.dumps(results1, indent=4, separators=(',',':')))

print("Home Loan")
print(results['results']['thereafterMonthlyPayment'])

print("other loan")
print(results1['results']['totalRepayment'])

And this is an example of the table in SQL: | Loan number | Account number | LoanID | |:---- |:------:| -----:| | L0002 | 77777-1 | RL000005 | | L0003 | 77777-1 | HL000002 | | L0004 | 11111-3 | CL000003 | | L0005 | 33333-3 | EL000006 |

Unfortunately, this code is very incomplete because I am stuck at all aspects and not just this so I am unable to show the whole code but I hope what I have here is enough. If not, please do let me know what else I need to put in this question. Thank you!

question from:https://stackoverflow.com/questions/65651198/how-to-get-multiple-results-from-sql-table-with-python

Answer 1

Load pyodbc and pandas import pandas to create data from in python PLease make sure you use the corect server ip and DB instance and user name and password according you your DB.Here in example we are using MS-SQL server

Connection to DB

server='ip address of server '
database='DB instancename'
username='username'
password='password'

cnxn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

The below part is for reading the data from sql and directly saving it into PD dataframe for easy manipulation

data=pd.read_sql_query('select * from Account_Delinquency',cnxn)

Based on your requirement change the select statement