DEV Community

sumanbgl
sumanbgl

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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

pip install -r requirements.txt
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)