Zahlungspläne mit Python basteln
Für ein kleines Projekt brauchte ich die Möglichkeit, flexible Zahlungspläne zu erzeugen. Der folgende Python-Code tut genau das und erzeugt auch gleich passende INSERT Statements für Postgres.
Das CREATE TABLE ist wie folgt:
CREATE TABLE paymentplan ( contract INTEGER NOT NULL, paymentdate INTEGER NOT NULL, paymenttype INTEGER NOT NULL, amount NUMERIC(12, 2) NOT NULL ); |
import pandas as pd from datetime import datetime from dateutil.relativedelta import relativedelta def genPaymentPlan(contract, principal, paymentsperyear, interestrate, startdate, years): interest = interestrate/100*principal/paymentsperyear date = datetime.strptime(startdate, '%Y%m%d').date() output = pd.DataFrame(columns=['Contract', 'Paymentdate', 'Paymenttype', 'Amount']) # Add a row to the DataFrame start = {'Contract': contract, 'Paymentdate': startdate, 'Paymenttype': 1, 'Amount' : principal} output = pd.concat([output, pd.DataFrame([start])], ignore_index=True) for payment in range(paymentsperyear*years): new_date = (date + relativedelta(months=12/paymentsperyear)) date = new_date interest_line = {'Contract': contract, 'Paymentdate': new_date.strftime('%Y%m%d'),'Paymenttype': 2, 'Amount' : -1*interest} output = pd.concat([output, pd.DataFrame([interest_line])], ignore_index=True) end = {'Contract': contract, 'Paymentdate': date.strftime('%Y%m%d'), 'Paymenttype': 3, 'Amount' : -principal} output = pd.concat([output, pd.DataFrame([end])], ignore_index=True) return output test = genPaymentPlan(123458, 2000, 2, 12, '20260101', 4) #print(test) # Generate INSERT statements print("Generated SQL INSERT statements:\n") for index, row in test.iterrows(): contract = int(row['Contract']) date = row['Paymentdate'] # already string in format YYYY-MM-DD type = row['Paymenttype'] amount = float(row['Amount']) insert = f"INSERT INTO paymentplan (contract, paymentdate,paymenttype, amount) VALUES ({contract}, '{date}', {type} , {amount});" print(insert) |