Chapter 5 - Using a database with Flask

Configuring a database, introduction to model classes, querying objects from the database and setting up relationships between model classes.

I- Configuring a database

Object oriented programming and relational database view data in different ways. An ORM (Object Relational Mapping) is a mechanism that makes the link between the object oriented and the relational database approaches. When using an ORM, model classes are defined for, usually, mapping each of the relational tables.

This chapter will cover how to integrate Flask with SQLAlchemy, the most famous python ORM. Before proceeding, install the SQLAlchemy plugin for Flask, by creating a requirements.txt file and adding the following lines:

Flask==0.10 Flask-SQLAlchemy==2.1 pysqlite

Now, create a database.py script that will contain the database configuration and model classes' definitions:

from flask_sqlalchemy import SQLAlchemy from main import app app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' db = SQLAlchemy(app)

Now, modify the existing main.py so that it creates the database at startup:

if __name__ == "__main__": # Create the DB from database import db print("creating database") db.create_all() print("database created")

II- Introduction to model classes

In the ORM model, model classes are usually an object oriented equivalent to a relational table. In this section, we will modelize the following class:

To get started with model classes, open the database.py file and add the following definition of a task:

class Task(db.Model): id = db.Column(db.Integer, primary_key=True) label = db.Column(db.Text) isDone = db.Column(db.Boolean)

Now, restart the script and modify the code of main.py to make it store data in database instead of using sessions:

@app.route("/") def generate_form(): from database import Task tasks = Task.query.all() # Get all the tasks stored in database return render_template("form.html", tasks=tasks) @app.route("/form_handler", methods=["POST"]) def form_handler(): from database import Task task_text = request.form["task_text"] new_task = Task() # Create a new task locally new_task.label = task_text # set the text of the task new_task.isDone = False # set the isDone boolean # Add the new task to the current DB session. # Once the execution of the DB session is over, # the new task will be present in the database. db.session.add(new_task) return redirect(url_for("generate_form"))

And update the templates/form.html template, to take into account the new data modelization of tasks:

{% for task in tasks %} <li class="list-group-item">{{ task.label }} <a class="float-right" href="#">delete</a></li> {% endfor %}

III- Querying database objects

It is possible to read, update and delete objects from database directly from a model class. The following piece of code illustrates few examples of database querying using Flask (more details here):

from database import Task Task.query.all() # gets a list containing all the tasks from database. If database is empty, returns [] Task.query.first() # returns an task from database. If database is empty, returns None Task.query.filter(Task.id == 1).first() # returns the task whose ID value is equal to 1. If there is no result, returns none Task.query.filter_by(id=1).first() # same as previous query Task.query.filter_by(isDone=False).all() # returns all the tasks that are not done. If no result, returns []

IV- Relationships

It is possible setup links between model classes, by using the relationship concept. In this section, we will modelize the following relationship:

Let's add a new model class in our web application: to do so, apply the following modifications to database.py:

class TaskList(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) owner = db.Column(db.Text) tasks = db.relationship('Task', backref='task_list', lazy='dynamic') class Task(db.Model): id = db.Column(db.Integer, primary_key=True) label = db.Column(db.Text) isDone = db.Column(db.Boolean) task_list_id = db.Column(db.Integer, db.ForeignKey('task_list.id'))

V- Towards a complete task manager

In this section, we will complete the existing application so that it can handle several task list. Please take a look a the modifications we made to the existing code.

database.py:

from flask_sqlalchemy import SQLAlchemy from main import app app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' db = SQLAlchemy(app) class TaskList(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) tasks = db.relationship('Task', backref='task_list', lazy='dynamic') class Task(db.Model): id = db.Column(db.Integer, primary_key=True) label = db.Column(db.Text) isDone = db.Column(db.Boolean) task_list_id = db.Column(db.Integer, db.ForeignKey('task_list.id'))

main.py:

from flask import Flask from flask import request, url_for, redirect, session, render_template app = Flask("firstFormApplication") app.secret_key = "very_important_secret" @app.route("/") @app.route("/<task_list_name>") def generate_form(task_list_name=None): from database import Task, TaskList # If no "task_list_name" parameter is provided, redirect to "default" if task_list_name is None: return redirect(url_for("generate_form", task_list_name="default")) existing_task_list = TaskList.query.filter_by(name=task_list_name).first() # If no existing task list, then create a new one if existing_task_list is None: new_task_list = TaskList() new_task_list.name = task_list_name db.session.add(new_task_list) db.session.commit() existing_task_list = new_task_list # Render the form usign the current task list return render_template("form.html", task_list=existing_task_list) @app.route("/form_handler", methods=["POST"]) def form_handler(): from database import Task task_list_id = request.form["task_list_id"] task_list_name = request.form["task_list_name"] task_text = request.form["task_text"] # Create a new task new_task = Task() new_task.label = task_text new_task.isDone = False new_task.task_list_id = task_list_id # Make the link between Task and TaskList db.session.add(new_task) db.session.commit() return redirect(url_for("generate_form", task_list_name=task_list_name)) if __name__ == "__main__": # Create the DB from database import db print("creating database") db.create_all() print("database created") app.jinja_env.auto_reload = True app.run(host="0.0.0.0", port=8080, debug=True)

templates/form.html:

<!DOCTYPE html> <html> <head> <title>Todolist</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='css/theme.css') }}" type="text/css"/> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/js/bootstrap.min.js"></script> </head> <body> <div id="container"> <div class="row"> <div class="col-md-6 main-div"> <div class="card"> <div class="card-header text-center"> <h1>{{ task_list.name }}</h1> <form action="{{ url_for('form_handler') }}" method="post"> <div class="form-group"> <input class="form-control" name="task_text" type="text" placeholder="Add todo"/> <input type="hidden" name="task_list_id" value="{{ task_list.id }}"/> <input type="hidden" name="task_list_name" value="{{ task_list.name }}"/> </div> <div class="form-group"> <input class="btn btn-success float-right" type="submit"/> </div> </form> </div> <div class="card-body"> <ul class="list-group list-group-flush"> {% for task in task_list.tasks %} <li class="list-group-item">{{ task.label }} <a class="float-right" href="#">delete</a></li> {% endfor %} </ul> </div> </div> </div> </div> </div> </body> </html>