«

Zahlungspläne mit Python basteln

2025-06-21, 22:10

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)
Schlagwörter: ,
Category: Python / SciPy / pandas