# Flask app-expense tracking
Project checklist:
1. **Manage environment**
- [ ] Install anaconda and install basic environment
- [ ] Install vscode and extensions
- [ ] Define your project tree structure
2. **Design basic features, connect to database**
- [ ] Design front end: `home.html`:
- [ ] Add bootstrap
- [ ] Add navigation bar
- [ ] Add main content: Jumbotron and Cards
- [ ] Make it responsive
- [ ] Design flask backend:
- [ ] Route to `home.html`
- [ ] Design database with SQLAlchemy
- [ ] Connect database to `home.html`
3. **Add CRUD process**
- [ ] Create forms with WTForms:
- [ ] Create ExpenseForm class, inherit from the FlaskForm
- [ ] Create suitable fields and validations type
- [ ] Create the `add.html`:
- [ ] Add bootstrap
- [ ] Add navigation bar
- [ ] Add main content: Jumbotron and Fields
- [ ] `Add` feature:
- [ ] Add more functionality to the `Add new expense` button in the home page
- [ ] Create new routes for the `add` feature
- [ ] Link the forms to the newly create route
- [ ] Get the data from the front end and store it in the database
- [ ] `Edit` feature:
- [ ] The `Edit` button in the expense cards should be able to take the expense card ID
- [ ] Create new route for the `edit` feature. The route should be able to take the expense card ID that we just get from the `edit` button
- [ ] Link the ExpenseForm to the newly create route
- [ ] Fill the Form with data from the chosen expense card (hint: use `GET` method)
- [ ] When the user submit the form, update the database with the value in the fields
- [ ] `Delete` feature:
- [ ] The `Delate` button should be able to create a `Modal window`
- [ ] The `Modal Window` should contain:
- A delete confirmation message
- A confirm button, which capture the `expense_id` of the card
- A cancel button, which close the `modal`
- [ ] Redirect the user to the homepage after they delete a card
## **Create virtual environment for the flask app**
conda create --name flask python=3.7
conda activate flask
conda install flask flask-sqlalchemy flask-wtf
## **Add extension for Vscode**
```
Jinja
flask-snippet
Bootstrap 4, Font awesome 4, Font Awesome 5 Free & Pro snippets
Python
```
## **Define your project tree**
1) Create your project folder
2) Go to your project folder
3) Create a templates folder
4) Create `app.py`. Then create home.html in templates folder
5) Open the `vscode` at the project folder
```
mkdir expense_app
cd expense_app
mkdir templates
touch app.py templates/home.html
code .
```
## **Basic flask app template**
1. Enter visual code at your project folder. Type `code .`
2. Select `app.py` file.
3. Type `fapp` on the `app.py` file. Press Enter. The `flask-snippets` will paste some code for a simple "Hello World" app like this
```
from flask import Flask, render_template
app = Flask(__name__)
@app.route('/')
def index():
return render_template('index.html')
if __name__ == '__main__':
app.run(host='127.0.0.1', port=5000, debug=True)
```
Note: The port might be different. Please change it to 5000 to make it similar to the lecture (it does not affect anything). The lecture will use port 5000.
4. Change the flask template a bit to suit with our tutorial:
```
from flask import Flask, render_template
app = Flask(__name__)
@app.route('/')
def home():
return render_template('home.html')
if __name__ == '__main__':
app.run(host='127.0.0.1', port=5000, debug=True)
```
5. Run `python app.py` on terminal
6. Open [`http://127.0.0.1:5000/`](http://127.0.0.1:5000/) on your browser to check if your flask app is working.
## Design the homepage: Add navbar and jumbotron
1) Add basic bootstrap template. Open `home.html` and type `b4-$` to get the boostrap template
2) Add the navigation bar to the body. Below is a simple [Navbar](https://getbootstrap.com/docs/4.3/components/navbar/) from bootstrap documentation.
```
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<a class="navbar-brand" href="#">Expense</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('home') }}">Home <span class="sr-only">(current)</span></a>
</li>
<div class="navbar-nav">
<a class="nav-item nav-link" href="#">Login</a>
<a class="nav-item nav-link" href="#">Register</a>
</div>
</div>
</nav>
```
3) Next we want to add a `jumbotron` to our homepage. The `jumbotron` is just a large section contain your key marketing message.
```
<section class="jumbotron text-center">
<div class="container">
<h1 class="jumbotron-heading">Expense Tracking app</h1>
<p class="lead text-muted">A cool Expense Tracking app. Put your money under control!</p>
<p>
<a href="/edit" class="btn btn-primary my-2">Add new expense</a>
<a href="#" class="btn btn-secondary my-2">View Dashboard</a>
</p>
</div>
</section>
```
4) Open your website on the terminal again with `python app.py`. From now on we will test constantly to see whether our front end and back end has connected.
## Create a list of expenses
1) In our `app.py` makes a list, which contains the information of each expense card. Here is a test database:
```
from datetime import datetime
all_expenses = [
{
"id":1,
"title":"Headphones",
"category":"Electronics",
"amount":100,
"date": datetime.strptime("22-02-2019", '%d-%m-%Y')
},
{
"id":2,
"title":"BBQ and Bacons",
"category":"Food",
"amount":200,
"date":datetime.strptime("12-09-2019", '%d-%m-%Y')
},
{
"id":3,
"title":"Spotify",
"category":"Sevices",
"amount":15,
"date":datetime.strptime("23-08-2019", '%d-%m-%Y')
},
{
"id":4,
"title":"Netflix",
"category":"Sevices",
"amount":30,
"date":datetime.strptime("23-12-2019", '%d-%m-%Y')
}
]
```
Note that here we use the datetime module for the datetime data of our card. Therefore we need to import the `datetime` library.
2) Pass the list to the `home.html` template:
```
def home():
return render_template('home.html', expenses=all_expenses)
```
3) Display the expenses on the homepage.
Now we want to display our data from the server as cards. Bootstrap 4 offers us some [basic cards templates](https://getbootstrap.com/docs/4.3/components/card/). The value from card-header, body and text are taken from our list **all_expenses**.
There two things to notice here.
- First the `{{"${:,.2f}".format(expense.amount)}}` is a string format from python. It takes the expense.amount and show it on the card with the dollar and the comma sign. You can read about python string formating [here](https://pyformat.info/)
- Second we need to include day time in our card. Basically, we take date time input from user, and format it to our predefined input. In order to use datetime datatype, we need a python library called `datetime`. You can read about it in details [here](https://docs.python.org/2/library/datetime.html)
```
{% for expense in expenses %}
<div class="card border-success mb-3">
<div class="card-header">{{ expense.category }}</div>
<div class="card-body">
<h5 class="card-title">{{expense.title}} </h5>
<p class="card-text">{{"${:,.2f}".format(expense.amount)}}</p>
<p class="card-text"><small class="text-muted">{{expense.date.strftime('%Y-%m-%d')}}</small></p>
<a href="#" class="btn btn-primary btn-sm m-1">Edit</a>
<a href="#" class="btn btn-danger btn-sm m-1">Delete</a>
</div>
</div>
</div>
{% endfor %}
```
5) We have create multiple cards, but each card occupied one row (looks ugly). Besides, our website is not responsive, which means cards stays the same when we reduce the window side. Now we want to make it responsive
```
<div class="container d-flex justify-content-center">
<div class="row justify-content-center">
{% for expense in expenses %}
<div class="card border-danger mb-3" style="margin: 1rem; min-width: 18rem;">
<div class="card-header">{{ expense.category }}</div>
<div class="card-body">
<h5 class="card-title">{{expense.title}}</h5>
<p class="card-text">{{expense.amount}}</p>
<p class="card-text"><small class="text-muted">{{expense.date.strftime('%Y-%m-%d')}}</small>
</p>
<a href='#' class="btn btn-primary">Edit</a>
<a href='#' class="btn btn-danger">Delete</a>
</div>
</div>
</div>
{% endfor%}
</div>
</div>
```
6) The final version of the `home.html` file should look something like this
```
<!doctype html>
<html lang="en">
<head>
<title>Title</title>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<!-- navbar -->
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<a class="navbar-brand" href="{{ url_for('home') }}"">Expense</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('home') }}">Home <span class="sr-only">(current)</span></a>
</li>
<div class="navbar-nav">
<a class="nav-item nav-link" href="#">Login</a>
<a class="nav-item nav-link" href="#">Register</a>
</div>
</div>
</nav>
<!-- jumbotron -->
<section class="jumbotron text-center">
<div class="container">
<h1 class="jumbotron-heading">Expense Tracking app</h1>
<p class="lead text-muted">A cool Expense Tracking app. Put your money under control!</p>
<p>
<a href="{{ url_for('add')}} " class="btn btn-primary my-2">Add new expense</a>
<a href="#" class="btn btn-secondary my-2">View Dashboard</a>
</p>
</div>
</section>
<main>
<div class="container d-flex justify-content-center">
<div class="row justify-content-center">
{% for expense in expenses %}
<div class="card border-success mb-3" style="margin: 1rem; min-width: 18rem;">
<div class="card-header">{{expense.category}}</div>
<div class="card-body">
<h5 class="card-title">{{expense.title}} </h5>
<p class="card-text">{{"${:,.2f}".format(expense.amount)}}</p>
<p class="card-text"><small class="text-muted">{{expense.date.strftime('%Y-%m-%d')}}</small>
</p>
<a href="#" class="btn btn-primary btn-sm m-1">Edit</a>
<a href="#" class="btn btn-danger btn-sm m-1">Delete</a>
</div>
</div>
{% endfor%}
</div>
</div>
</main>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
crossorigin="anonymous"></script>
</body>
</html>
```
## Create forms with WTForms.
### CRUD process: Create
WTForms is a flexible forms validation and rendering library for Python web development. It is framework agnostic and can work with whatever web framework and template engine you choose.
1. Import and then add the FlaskForms to your `app.py`. The FlaskForm class let you create specific input field in your form. It also comes with multiple type of validators that you should import along with the Field if you want to use them.
```
...
from flask_wtf import FlaskForm
from wtforms import StringField, DecimalField, SubmitField, DateField
from wtforms.validators import DataRequired, Length, Email, EqualTo, ValidationError
...
# you should choose Field corresponding to your expected datatype.
class ExpenseForm(FlaskForm):
title = StringField('Title', validators=[DataRequired()])
category = StringField('Category', validators=[DataRequired()])
amount = DecimalField('Amount', validators=[DataRequired()])
date = DateField('Date')
submit = SubmitField('Submit')
def __repr__(self):
return f"ExpenseForm('{self.title}', '{self.category}', {self.amount}, {self.date})"
....
```
2. After we have define the fields, lets add it to our application. We need a `route` to redirect users when they want to edit or add more expense. The new `route` will have forms that accept input from the users. As usual, we start with the front-end first. In the templates folder, creates `add.html`. Here is the content of `add.html`:
- First we need the `Bootstrap template`, the `navbar` and the `jumbotron` from `home.html`.
```
<!doctype html>
<html lang="en">
<head>
<title>Title</title>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<a class="navbar-brand" href="#">Expense</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('home') }}">Home <span class="sr-only">(current)</span></a>
</li>
<div class="navbar-nav">
<a class="nav-item nav-link" href="#">Login</a>
<a class="nav-item nav-link" href="#">Register</a>
</div>
</div>
</nav>
<!-- Jumbotron -->
<section class="jumbotron text-center">
<div class="container">
<h1 class="jumbotron-heading">Add new expense</h1>
</div>
</section>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
crossorigin="anonymous"></script>
</body>
</html>
```
- Now, we want to create form fields that allows user to type in input. The syntax to create a single title is as follow.
```
<div class="form-group">
{{ form.title.label(class="form-control-label") }}
{{ form.title(class="form-control form-control") }}
</div>
```
- We have 4 fields according that we just create in the ExpenseForm: `title` `category` `date` `amount` `submit`. Let's create those,
- Note that here we have `{ form.hidden_tag() }}`. You should not worry about this now, but FYI, this line of code protect you from Cross Site Request Forgery (CSRF) attack. In order to use CSRF, you need to add a secret key to your app, which I will show you later when we edit `app.py`.
```
<div class="container content-section">
<form method="POST" action="">
{{ form.hidden_tag() }}
<fieldset class="form-group">
<div class="form-group">
{{ form.title.label(class="form-control-label") }}
{{ form.title(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.category.label(class="form-control-label") }}
{{ form.category(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.amount.label(class="form-control-label") }}
{{ form.amount(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.date.label(class="form-control-label") }}
{{ form.date(class="form-control form-control") }}
</div>
</fieldset>
<div class="form-group">
{{ form.submit(class="btn btn-outline-info") }}
</div>
</form>
</div>
</main>
```
- The final `add.html` look like this:
```
<!doctype html>
<html lang="en">
<head>
<title>Title</title>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<a class="navbar-brand" href="#">Expense</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent"
aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('home') }}">Home <span class="sr-only">(current)</span></a>
</li>
<div class="navbar-nav">
<a class="nav-item nav-link" href="#">Login</a>
<a class="nav-item nav-link" href="#">Register</a>
</div>
</div>
</nav>
<!-- Jumbotron -->
<section class="jumbotron text-center">
<div class="container">
<h1 class="jumbotron-heading">Add new expense</h1>
</div>
</section>
<div class="container content-section">
<form method="POST">
{{ form.hidden_tag() }}
<fieldset class="form-group">
<div class="form-group">
{{ form.title.label(class="form-control-label") }}
{{ form.title(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.category.label(class="form-control-label") }}
{{ form.category(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.amount.label(class="form-control-label") }}
{{ form.amount(class="form-control form-control") }}
</div>
<div class="form-group">
{{ form.date.label(class="form-control-label") }}
{{ form.date(class="form-control form-control") }}
</div>
</fieldset>
<div class="form-group">
{{ form.submit(class="btn btn-outline-info") }}
</div>
</form>
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
crossorigin="anonymous"></script>
</body>
</html>
```
4. Now we add the WTForms to our backend. We need to create a route that redirect user to `add.html` and accept user data from front-end via `POST` method. Then we take those data and store it in our list.
```
# we use redirect method, don't forget to import it
from flask import Flask, render_template, redirect
# remember to add the SECRET_KEY that we mention above.
app.config['SECRET_KEY'] = 'a_secret_key. You may change it later'
@app.route('/add', methods=['GET', 'POST'])
def add():
form = ExpenseForm()
if form.validate_on_submit():
all_expenses.append({
'title':form.title.data,
'category':form.category.data,
'amount':form.amount.data,
'date':form.date.data
})
return redirect('home')
form.date.data = datetime.utcnow()
return render_template('add.html', form=form)
```
5. If you test run your app, you will see that pressing `Add new expense` button on the homepage does nothing. We use the `url_for` method from `Flask` here to link the button with the add route, so please remember to import it in the `app.py`
```
<a href="{{ url_for('add') }}" class="btn btn-primary my-2">Add new expense</a>
```
6. The `app.py` after we add the `add` feature
```
from flask import Flask, render_template, redirect
from datetime import datetime
from flask_wtf import FlaskForm
from wtforms import StringField, DecimalField, SubmitField, DateField
from wtforms.validators import DataRequired, Length, Email, EqualTo, ValidationError
app = Flask(__name__)
app.config['SECRET_KEY'] = 'a_secret_key' # You may change it later'
# we create a fake database here since we haven't had a database yet
all_expenses = [
{
"id":1,
"title":"Headphones",
"category":"Electronics",
"amount":100,
"date": datetime.strptime("22-02-2019", '%d-%m-%Y')
},
{
"id":2,
"title":"BBQ and Bacons",
"category":"Food",
"amount":200,
"date":datetime.strptime("12-09-2019", '%d-%m-%Y')
},
{
"id":3,
"title":"Spotify",
"category":"Sevices",
"amount":15,
"date":datetime.strptime("23-08-2019", '%d-%m-%Y')
},
{
"id":4,
"title":"Netflix",
"category":"Sevices",
"amount":30,
"date":datetime.strptime("23-12-2019", '%d-%m-%Y')
}
]
class ExpenseForm(FlaskForm):
title = StringField('Title', validators=[DataRequired()])
category = StringField('Category', validators=[DataRequired()])
amount = DecimalField('Amount', validators=[DataRequired()])
date = DateField('Date')
submit = SubmitField('Submit')
def __repr__(self):
return f"ExpenseForm('{self.title}', '{self.category}', {self.amount}, {s.date})"
# assign home route and / route to home
@app.route('/home',)
@app.route('/')
def home():
return render_template('home.html', expenses=all_expenses)
@app.route('/add', methods=['POST', 'GET'])
def add():
form = ExpenseForm()
if form.validate_on_submit():
all_expenses.append({
'title':form.title.data,
'category':form.category.data,
'amount':form.amount.data,
'date':form.date.data
})
return redirect('home')
form.date.data = datetime.utcnow()
return render_template('add.html', form=form)
if __name__ == '__main__':
app.run(debug=True)
```
## SQLAlchemy: add the database
Flask SQLAlchemy is a Flask specific extension that provides some useful and helpers function of ORM. ORM stands for Object Relational Mapper and basically it allows us to access databases in an easy to use object-oriented way and the thing I like the most is that you can use different databases without changing your Python Code.
1. Add SQLAlchemy database to `app.py`
```
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Expense(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(50), nullable=False)
category = db.Column(db.String(50), nullable=False)
amount = db.Column(db.Float, nullable=False, default=0.0)
date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
def __repr__(self):
return f"Expense('{self.title}', '{self.category}', {self.amount}, {self.date})"
```
2 ]. Open `Terminal` to create your database. When you type `python`, you are in python shell instead of terminal shell, the `>>>` specify that you are in python shell. To exit python shell and return to terminal shell, either type `exit()` or `Ctrl+Z`.
```
python
from app import db
from app import Expense
db.create_all()
expense_1 = Expense(title='MacBook', category='Work', amount=1500)
expense_2 = Expense(title='Headphone', category='Person', amount=100)
expense_3 = Expense(title='Netflix', category='Service', amount=15)
expense_4 = Expense(title='Spotify', category='Person', amount=30)
db.session.add(expense_1)
db.session.add(expense_2)
db.session.add(expense_3)
db.session.add(expense_4)
db.session.commit()
Expense.query.all()
[Expense('MacBook', 'Work', 1500.0, 2019-10-22), Expense('Headphone', 'Person', 100.0, 2019-10-22)]
Expense.query.get(1)
Expense('MacBook', 'Work', 1500.0, 2019-10-22)
Expense.query.filter_by(title='MacBook').all()
[Expense('MacBook', 'Work', 1500.0, 2019-10-22)]
Expense.query.filter_by(title='MacBook').first()
```
3. We have added the database. Now we need to modifed our route function to query the data and send it to the front end
For the home route
```
@app.route('/home')
@app.route('/')
def home():
all_expenses = Expense.query.all()
return render_template('home.html', expenses=all_expenses)
if __name__ == '__main__':
app.run(debug=True)
```
For the add route:
```
@app.route('/add', methods=['GET', 'POST'])
def add():
form = ExpenseForm()
#if form successfully validated
if form.validate_on_submit():
# we create an expense object
expense = Expense(title=form.title.data, category=form.category.data,
amount=form.amount.data, date=form.date.data)
# add it to the database
db.session.add(expense)
db.session.commit()
return redirect('/home')
form.date.data = datetime.utcnow()
return render_template('add.html', form=form)
```
4. Run your app again to see the result
# CRUD process: Update
Let's take a moment and think about our goal: The users can click on the edit button, which redirect them to a form, where they can fill in information to edit. Sound familiar? This is because we have already done this task in the `add` feature. Therefore, we can reuse the template of the `add.html` without creating new template for `edit`.
The tricky part of the `edit` features, however, lies in the backend. Our `edit` route should be able to do the following things:
- Take in the unique ID of the expense card that we want to edit
- Render the template `add.html` and populate them with data from the chosen expense card. For example: if the chosen card is Macbook, 3000, personal; then our form should have these value appear in the corresponding fields.
- When user submit the form, update these values to our database.
For the first task, remember that in our `home.html`, we have a for-loop that get each item from the database, and assign their data to each of the card. Therefore, we can get the ID of a particular item by modified our `edit` button in `home.html`. We want to get the `erxpense.id` and push it to the backend.
```
<a href="{{ url_for('edit', expense_id=expense.id) }}" class="btn btn-primary btn-sm m-1">Edit</a>
```
**Now the edit button takes in the expense_id and redirect us to the update route. Let add the new route in our `app.py`.**
```
# we use request method here, so we need to import it
from flask import request
@app.route("/update/<int:expense_id>", methods=['GET', 'POST'])
def update(expense_id):
expense = Expense.query.get_or_404(expense_id)
form = ExpenseForm()
# if the form is validated and submited, update the data of the item
# with the data from the field
if form.validate_on_submit():
expense.title = form.title.data
expense.category = form.category.data
expense.amount = form.amount.data
expense.date = form.date.data
db.session.commit()
return redirect(url_for('home'))
# populate the field with data of the chosen expense
elif request.method == 'GET':
form.title.data = expense.title
form.category.data = expense.category
form.amount.data = expense.amount
form.date.data = expense.date
return render_template('add.html', form=form, title='Edit Expense')
```
Let's walk through this route a bit:
- `@app.route` takes in `expense_id`, which mean the url will change depends on the `expense_id`.
- The `Expense.query.get_or_404(expense_id)` return us the item with the `expense_id` and return 404 error if the ID is not exist in our database.
- We specify that the route accept both `POST` and `GET` method. `POST` method, as you have done before, is used to send the update data to the sever. `Get` method, in this case, is to populate the field with data from the item we choose.
# CRUD process: Delete
Delete feature is very similar to Edit feature. You select an expense via its ID, and remove it from the database. The only different is that we do not need any form at all, just a pop up confirmation message.
With the information above in mind, let's list down the steps:
- Adjust the `delete` button. It should create a `Modal`, which is a pop-up box that contain a delete confirmation message, a confirm button and a cancel button
- The confirm button of the `Modal` should get the `expense_id` and return it to the `delete route`
- The `delete_route` will accept `POST` method and remove the chosen `expense_id` from the database. Then it redirect the user back to the home page.
Let add more functionality to the delete button in `home.html`
<button type="button" class="btn btn-danger btn-sm m-1" data-toggle="modal" data-target="#delete-{{expense.id}}">Delete</button>
<!-- Modal -->
<div class="modal fade" id="delete-{{expense.id}}" tabindex="-1" role="dialog" aria-labelledby="deleteModalLabel" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="deleteModalLabel">Delete Expense?</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
<form action="{{ url_for('delete', expense_id=expense.id) }}" method="POST">
<input class="btn btn-danger" type="submit" value="Delete">
</form>
</div>
</div>
</div>
</div>
Add `delete` route in our `app.py`
@app.route("/delete/<int:expense_id>", methods=['POST'])
def delete(expense_id):
expense = Expense.query.get_or_404(expense_id)
db.session.delete(expense)
db.session.commit()
return redirect(url_for('home'))
Congratulation, you have finished a basic expense-tracking app. An app development is never finished, so it is up to you to add new features or improve the UI
Sign in with Wallet
Connect another wallet