Income,Expense Tracker using Azure SQL Database and App Service

This blog will cover steps to develop a single page Flask based web-app using Azure Services.

Solution Overview

A single page Flask based income and expense tracker web application for a single user is developed and deployed on Azure Platform. It integrates with MS Single Sign-on using msal library

Azure Services Used

1. Azure SQL Database
2. Azure App Service
3. Azure Active Directory

Application Overview

In this simple income-expense tracker web application, a user can log in, view income, expenses and add income, expenses. Income and/or expense is referred to as an item. Each item consists of a date of expense, category, amount. The item(s) are stored in Azure SQL Server.

The income-expense tracker includes the following components:

1. A webapp using Python with the Flask framework.
2. A SQL database that contains a users and income-expense table for the webapp to query.

Prerequisites

1. Python 3.7 or higher version
2. Visual Studio Code Editor

Development Environment Setup on Linux

Install Visual Studio Code
Install Python 3
Create Python Virtual Environment - Execute the below command to create python virtual environment

# Create the environment
python3 -m venv venv
# Activate the environment
source ./venv/bin/activate

Download requirements.txt. It contains all the required libraries. Execute the below command to install all of them.

pip install -r requirements.txt

Overview of Application Code

Tables

CREATE TABLE USERS (
    id INT NOT NULL IDENTITY(1, 1),
    username VARCHAR(64) NOT NULL,
    password_hash VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE INCOME_EXPENSE(
    id INT NOT NULL IDENTITY(1, 1),
    category VARCHAR(150) NOT NULL,
    date DATE NOT NULL DEFAULT(GETDATE()),
    amount INT NOT NULL,
    user_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Sample Data

INSERT INTO dbo.users (username, password_hash)
VALUES ('admin', 'pbkdf2:sha256:150000$QlIrz6Hg$5f4cd25d78a6c79906a53f74ef5d3bb2609af2b39d9e5dd6f3beabd8c854dd60')

INSERT INTO dbo.income_expense (category, amount, user_id)
VALUES (
    'income',
    5000,
    1
);

Models

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    password_hash = db.Column(db.String(128))

    def __repr__(self):
        return '<User {}>'.format(self.username)

class Income_Expense(db.Model):
    __tablename__ = 'income_expense'
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(150))
    date = db.Column(db.Date)
    amount = db.Column(db.Integer)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

    def __repr__(self):
        return '<Amount {}>'.format(self.amount)

    def save_changes(self, form, userId, new=False):
        self.category = form.category.data
        self.amount = form.amount.data
        self.date = form.date.data
        self.user_id = userId

        if new:
            db.session.add(self)
        db.session.commit()

Forms

class LoginForm(FlaskForm):
    username = StringField('Username', validators=[DataRequired()])
    password = PasswordField('Password', validators=[DataRequired()])
    remember_me = BooleanField('Remember Me')
    submit = SubmitField('Sign In')

class IncomeExpenseForm(FlaskForm):
    category = StringField('Category', validators=[DataRequired()])
    amount = IntegerField('Amount', validators=[DataRequired()])
    date = DateField('Date', format='%m/%d/%y',
                          render_kw={'placeholder': '3/16/21 for March 16, 2021'}, validators=[DataRequired()])
    submit = SubmitField('Save')

config.py - contains the code to read the configurable values from an external source like environment variables.

views.py - contains the routing logic for login, logout, creating and showing items.

Github contains the complete source code

Azure Infrastructure Setup

Resource Group and Azure SQL Database Creation

Integration with Active Directory

Azure App Service Creation and Deployment