Income,Expense Tracker using Azure SQL Database and App Service
Tags: azuredevstories
Published: 2021-04-07 18:14:15

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](https://code.visualstudio.com/?WT.mc_id=python-11210-chrhar)
[Install Python 3](https://docs.microsoft.com/en-us/learn/modules/python-install-vscode/3-exercise-install-python3?WT.mc_id=python-11210-chrhar)
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](https://github.com/sumanbgl/udacity_azure_developer_project/blob/master/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**
```python
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**
```python
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](https://github.com/sumanbgl/udacity_azure_developer_project/tree/master) contains the complete source code

#Azure Infrastructure Setup
###Resource Group and Azure SQL Database Creation
{% youtube mPYueuu8FCE %}

###Integration with Active Directory
{% youtube GQ593A1PHNQ %}

###Azure App Service Creation and Deployment
{% youtube X1vgJMlT3jo %}