Building a Reimbursement Application Using Flask and Oracle

 In this blog, I’ll walk through the development of a simple Employee Reimbursement Application using Python Flask and Oracle Database.

The application allows employees to submit reimbursement requests, enter expense details, and upload supporting receipts/invoices. The submitted information is stored directly in an Oracle database.

Technology Stack

• Python / Flask – Web application
• Flask-SQLAlchemy – ORM/database interaction
• Oracle Database – Data storage
• python-oracledb – Oracle connectivity
• HTML/CSS – User interface

Step 1 – Create the Oracle Table

First, we create a table to store the reimbursement request.

CREATE TABLE xxflask_reimbursement_req_all (
    xxreimb_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name        VARCHAR2(1000),
    empid            VARCHAR2(1000),
    emailaddress     VARCHAR2(1000),
    department       VARCHAR2(1000),
    expensetype      VARCHAR2(1000),
    expensedate      DATE,
    amount           NUMBER,
    paymentmethod    VARCHAR2(100),
    merchantname     VARCHAR2(100),
    expensepurpose   VARCHAR2(100),
    receiptinvoice   VARCHAR2(100),
    uploadinvoice    BLOB,
    additional_notes VARCHAR2(500),
    confirm_flag     VARCHAR2(1),
    submit_flag      VARCHAR2(1),
    creation_date    DATE DEFAULT SYSDATE
);

The table contains employee information, expense details, reimbursement amount, payment method, notes, and the uploaded receipt.

The UPLOADINVOICE column is a BLOB, allowing us to store the uploaded invoice/receipt as binary data.

Step 2 – Create the Flask Application

Next, we create the Flask application and configure the Oracle database connection.

The database connection information is maintained separately in a json.config file rather than hardcoding it in the application.

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
import os
import json
import oracledb

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
config_path = os.path.join(BASE_DIR, 'json.config')

with open(config_path, 'r') as c:
    dbParams = json.load(c)["params"]

oracledb.init_oracle_client(
    lib_dir=r"C:\instantclient_21_11"
)

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = \
    dbParams["database_conn_link"]

db = SQLAlchemy(app)

Step 3 – Map the Oracle Table Using SQLAlchemy

Instead of writing SQL statements for every database operation, we use SQLAlchemy ORM to map the Oracle table to a Python class.

class XXReimburseReq(db.Model):

    __tablename__ = "XXFLASK_REIMBURSEMENT_REQ_ALL"

    XXREIMBID = db.Column(
        "XXREIMB_ID", db.Integer, primary_key=True
    )

    FULLNAME = db.Column("FULL_NAME", db.String)
    EMPID = db.Column("EMPID", db.String)
    EMAILADDRESS = db.Column("EMAILADDRESS", db.String)
    DEPARTMENT = db.Column("DEPARTMENT", db.String)
    EXPENSETYPE = db.Column("EXPENSETYPE", db.String)
    EXPENSEDATE = db.Column("EXPENSEDATE", db.Date)
    AMOUNT = db.Column("AMOUNT", db.Numeric)
    PAYMENTMETHOD = db.Column("PAYMENTMETHOD", db.String)
    MERCHANTNAME = db.Column("MERCHANTNAME", db.String)
    EXPENSEPURPOSE = db.Column("EXPENSEPURPOSE", db.String)

    UPLOADINVOICE = db.Column(
        "UPLOADINVOICE", db.LargeBinary
    )

    ADDITIONALNOTES = db.Column(
        "ADDITIONAL_NOTES", db.String
    )

This gives Flask a Python representation of our Oracle reimbursement table.

Step 4 – Create the Reimbursement Form

The front end provides a simple reimbursement form where the employee can enter:

• Name and Employee ID
• Email and Department
• Expense Type
• Expense Date
• Amount
• Payment Method
• Merchant/Vendor
• Purpose of Expense
• Receipt/Invoice
• Additional Notes

The form submits the information to the Flask application using a POST request.

Step 5 – Process the Form in Flask

The /ReimbursementApp route handles both displaying the form and processing the submitted request.

@app.route('/ReimbursementApp', methods=['GET', 'POST'])
def ReimbursementAppEntry():

    if request.method == 'POST':

        receipt_file = request.files.get('receipt')

        receipt_data = None

        if receipt_file:
            receipt_data = receipt_file.read()

        entry = XXReimburseReq(
            FULLNAME=request.form.get('fullname'),
            EMPID=request.form.get('employee-id'),
            EMAILADDRESS=request.form.get('email'),
            DEPARTMENT=request.form.get('department'),
            EXPENSETYPE=request.form.get('expense-type'),
            EXPENSEDATE=datetime.strptime(
                request.form.get('expense-date'),
                '%Y-%m-%d'
            ).date(),
            AMOUNT=request.form.get('amount'),
            PAYMENTMETHOD=request.form.get('payment-method'),
            MERCHANTNAME=request.form.get('merchant-name'),
            EXPENSEPURPOSE=request.form.get('purpose'),
            UPLOADINVOICE=receipt_data,
            ADDITIONALNOTES=request.form.get(
                'additional-notes'
            )
        )

        db.session.add(entry)
        db.session.commit()

    return render_template('index.html')


if __name__ == '__main__':
    app.run(debug=True)

The important part here is the invoice upload:

receipt_file = request.files.get('receipt')
receipt_data = receipt_file.read()

The uploaded file is read as binary data and stored in the Oracle BLOB column.

Finally:

db.session.add(entry)
db.session.commit()

inserts the reimbursement request into Oracle.

Application Flow

The overall flow is quite simple:

Employee
   ↓
Reimbursement HTML Form
   ↓
Flask POST Request
   ↓
Extract Form Data
   ↓
Read Receipt / Invoice
   ↓
SQLAlchemy ORM
   ↓
Oracle Database
   ↓
XXFLASK_REIMBURSEMENT_REQ_ALL


Lets fill the form





below is the output