Connect Flask to a Database with Flask-SQLAlchemy

Flask is a micro web framework written in python. Micro-framework is normally a framework with little to no dependencies on external libraries. Though being a micro framework almost everything can be implemented using python libraries and other dependencies when and as required.

In this article, we will be building a Flask application that takes data in a form from the user and then displays it on another page on the website. We can also delete the data. We won’t focus on the front-end part rather we will be just coding the backend for the web application.

Installing Flask

In any directory where you feel comfortable create a folder and open the command line in the directory. Create a python virtual environment using the command below.

python -m venv <name>

Once the command is done running activate the virtual environment using the command below.

<name>\scripts\activate

Now, install Flask using pip(package installer for python). Simply run the command below.

pip install Flask

Creating app.py

Once the installation is done create a file name app.py and open it in your favorite editor. To check whether Flask has been properly installed you can run the following code.

Python

from flask import Flask
app = Flask(__name__)


'''If everything works fine you will get a
message that Flask is working on the first
page of the application
'''

@app.route('/')
def check():
    return 'Flask is working'


if __name__ == '__main__':
    app.run()

Output:

Setting Up SQLAlchemy

Now, let’s move on to creating a database for our application. For the purpose of this article, we will be using SQLAlchemy a database toolkit, and an ORM(Object Relational Mapper). We will be using pip again to install SQLAlchemy. The command is as follows,

pip install flask-sqlalchemy

In your app.py file import SQLAlchemy as shown in the below code. We also need to add a configuration setting to our application so that we can use SQLite database in our application. We also need to create an SQLAlchemy database instance which is as simple as creating an object.

Python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.debug = True

# adding configuration for using a sqlite database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

# Creating an SQLAlchemy instance
db = SQLAlchemy(app)

if __name__ == '__main__':
    app.run()

Creating Models

In sqlalchemy we use classes to create our database structure. In our application, we will create a Profile table that will be responsible for holding the user’s id, first name, last name, and age.

Python

from flask import Flask, request, redirect
from flask.templating import render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.debug = True

# adding configuration for using a sqlite database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

# Creating an SQLAlchemy instance
db = SQLAlchemy(app)

# Models
class Profile(db.Model):
    # Id : Field which stores unique id for every row in
    # database table.
    # first_name: Used to store the first name if the user
    # last_name: Used to store last name of the user
    # Age: Used to store the age of the user
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(20), unique=False, nullable=False)
    last_name = db.Column(db.String(20), unique=False, nullable=False)
    age = db.Column(db.Integer, nullable=False)

    # repr method represents how one object of this datatable
    # will look like
    def __repr__(self):
        return f"Name : {self.first_name}, Age: {self.age}"

if __name__ == '__main__':
    app.run()

The table below explains some of the keywords used in the model class. 

Column	used to create a new column in the database table
Integer	An integer data field
primary_key	If set to True for a field ensures that the field can be used to uniquely identify objects of the data table.
String	An string data field. String(<maximum length>)
unique	If set to True it ensures that every data in that field in unique.
nullable	If set to False it ensures that the data in the field cannot be null.
__repr__	Function used to represent objects of the data table.

Creating the database

In the command line which is navigated to the project directory and virtual environment running, we need to run the following commands. 

python

The above command will initiate a python bash in your command line where you can use further lines of code to create your data table according to your model class in your database. 

from app import db
db.create_all()

After the commands, the response would look like something in the picture and in your project directory you will notice a new file named ‘site.db’. 

Making Migrations in database

Install Flask-Migrate using pip 

pip install Flask-Migrate

Now, in your app.py add two lines, the code being as follows, 

Python

# Import for Migrations
from flask_migrate import Migrate, migrate

# Settings for migrations
migrate = Migrate(app, db)

Now to create migrations we run the following commands one after the other.

flask db init

flask db init

flask db migrate -m "Initial migration"

flask db migrate -m “Initial migration”

flask db upgrade

flask db upgrade

Now we have successfully created the data table in our database.

Creating the Index Page Of the Application

Before moving forward and building our form let’s create an index page for our website. The HTML file is always stored inside a folder in the parent directory of the application named ‘templates’. Inside the templates folder create a file named index.html and paste the below code for now. We will go back to adding more code into our index file as we move on.

HTML

<html>
   <head>
      <title>Index Page</title>
   </head>
   <body>
      <h3>Profiles</h3>
   </body>
</html>

 In the app.py add a small function that will render an HTML page at a specific route specified in app.route. 

Python

from flask import Flask, request, redirect
from flask.templating import render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.debug = True

# adding configuration for using a sqlite database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

# Creating an SQLAlchemy instance
db = SQLAlchemy(app)

# Models
class Profile(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(20), unique=False, nullable=False)
    last_name = db.Column(db.String(20), unique=False, nullable=False)
    age = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f"Name : {self.first_name}, Age: {self.age}"

# function to render index page
@app.route('/')
def index():
    return render_template('index.html')

if __name__ == '__main__':
    app.run()

To test whether everything is working fine you can run your application using the command 

python app.py

The command will set up a local server at http://localhost:5000.

Output:

Creating HTML page for form

We will be creating an HTML page in which our form will be rendered. Create an HTML file named add_profile in your templates folder. The HTML code is as follows. The important points in the code will be highlighted as you read on.

HTML

<!DOCTYPE html>
<html>
   <head>
      <title>Add Profile</title>
   </head>
   <body>
      <h3>Profile form</h3>
      <form action="/add" method="POST">
         <label>First Name</label>
         <input type="text" name="first_name" placeholder="first name...">
         <label>Last Name</label>
         <input type="text" name= "last_name" placeholder="last name...">
         <label>Age</label>
         <input type="number" name="age" placeholder="age..">
         <button type="submit">Add</button>
      </form>
   </body>
</html>

Adding a function in our application to render the form page

In our app.py file, we will add the following function. At route or site path ‘http://localhost:5000/add_data’ the page will be rendered. 

Python

@app.route('/add_data')
def add_data():
    return render_template('add_profile.html')

To check whether the code is working fine or not, you can run the following command to start the local server. 

python app.py

Now, visit http://localhost:5000/add_data and you will be able to see the form.

Output:

Function to add data using the form to the database

To add data to the database we will be using the “POST” method. POST is used to send data to a server to create/update a resource. In flask where we specify our route that is app.route we can also specify the HTTP methods there. Then inside the function, we create variables to store data and use request objects to procure data from the form. 

Note: The name used in the input tags in the HTML file has to be the same one that is being been used in this function,

For example,  

<input type="number" name="age" placeholder="age..">

“age” should also be used in the python function as, 

age = request.form.get("age")

Then we move on to create an object of the Profile class and store it in our database using database sessions. 

Python

# function to add profiles
@app.route('/add', methods=["POST"])
def profile():

    # In this function we will input data from the
    # form page and store it in our database.
    # Remember that inside the get the name should
    # exactly be the same as that in the html
    # input fields
    first_name = request.form.get("first_name")
    last_name = request.form.get("last_name")
    age = request.form.get("age")

    # create an object of the Profile class of models
    # and store data as a row in our datatable
    if first_name != '' and last_name != '' and age is not None:
        p = Profile(first_name=first_name, last_name=last_name, age=age)
        db.session.add(p)
        db.session.commit()
        return redirect('/')
    else:
        return redirect('/')

Once the function is executed it redirects us back to the index page of the application.

Display data on Index Page

On our index page now, we will be displaying all the data that has been stored in our data table. We will be using ‘Profile.query.all()‘ to query all the objects of the Profile class and then use Jinja templating language to display it dynamically on our index HTML file.

Update your index file as follows. The delete function will be written later on in this article. For now, we will query all the data from the data table and display it on our home page.

HTML

<!DOCTYPE html>
<html>
   <head>
      <title>Index Page</title>
   </head>
   <body>
      <h3>Profiles</h3>
      <a href="/add_data">ADD</a>
      <br>
      <table>
         <thead>
            <th>Id</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
            <th>#</th>
         </thead>
         {% for data in profiles %}
         <tbody>
            <td>{{data.id}}</td>
            <td>{{data.first_name}}</td>
            <td>{{data.last_name}}</td>
            <td>{{data.age}}</td>
            <td><a href="/delete/{{data.id}}" type="button">Delete</a></td>
         </tbody>
         {% endfor%}
      </table>
   </body>
</html>

We loop through every object in profiles that we pass down to our template in our index function and print all its data in a tabular form. The index function in our app.py is updated as follows. 

Python

@app.route('/')
def index():
      # Query all data and then pass it to the template
    profiles = Profile.query.all()
    return render_template('index.html', profiles=profiles)

Deleting data from our database

To delete data we have already used an anchor tag in our table and now we will just be associating a function with it.

Python

@app.route('/delete/<int:id>')
def erase(id):
    # Deletes the data on the basis of unique id and
    # redirects to home page
    data = Profile.query.get(id)
    db.session.delete(data)
    db.session.commit()
    return redirect('/')

The function queries data on the basis of id and then deletes it from our database.

Complete Code

The entire code for app.py, index.html, and add-profile.html has been given. 

app.py

Python

from flask import Flask, request, redirect
from flask.templating import render_template
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate, migrate

app = Flask(__name__)
app.debug = True

# adding configuration for using a sqlite database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

# Creating an SQLAlchemy instance
db = SQLAlchemy(app)

# Settings for migrations
migrate = Migrate(app, db)

# Models
class Profile(db.Model):
    # Id : Field which stores unique id for every row in
    # database table.
    # first_name: Used to store the first name if the user
    # last_name: Used to store last name of the user
    # Age: Used to store the age of the user
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(20), unique=False, nullable=False)
    last_name = db.Column(db.String(20), unique=False, nullable=False)
    age = db.Column(db.Integer, nullable=False)

    # repr method represents how one object of this datatable
    # will look like
    def __repr__(self):
        return f"Name : {self.first_name}, Age: {self.age}"

# function to render index page
@app.route('/')
def index():
    profiles = Profile.query.all()
    return render_template('index.html', profiles=profiles)

@app.route('/add_data')
def add_data():
    return render_template('add_profile.html')

# function to add profiles
@app.route('/add', methods=["POST"])
def profile():
    # In this function we will input data from the
    # form page and store it in our database. Remember
    # that inside the get the name should exactly be the same
    # as that in the html input fields
    first_name = request.form.get("first_name")
    last_name = request.form.get("last_name")
    age = request.form.get("age")

    # create an object of the Profile class of models and
    # store data as a row in our datatable
    if first_name != '' and last_name != '' and age is not None:
        p = Profile(first_name=first_name, last_name=last_name, age=age)
        db.session.add(p)
        db.session.commit()
        return redirect('/')
    else:
        return redirect('/')

@app.route('/delete/<int:id>')
def erase(id):

    # deletes the data on the basis of unique id and
    # directs to home page
    data = Profile.query.get(id)
    db.session.delete(data)
    db.session.commit()
    return redirect('/')

if __name__ == '__main__':
    app.run()

index.html

HTML

<!DOCTYPE html>
<html>
   <head>
      <title>Index Page</title>
   </head>
   <body>
      <h3>Profiles</h3>
      <a href="/add_data">ADD</a>
      <br>
      <table>
         <thead>
            <th>Id</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
            <th>#</th>
         </thead>
         {% for data in profiles %}
         <tbody>
            <td>{{data.id}}</td>
            <td>{{data.first_name}}</td>
            <td>{{data.last_name}}</td>
            <td>{{data.age}}</td>
            <td><a href="/delete/{{data.id}}" type="button">Delete</a></td>
         </tbody>
         {% endfor%}
      </table>
   </body>
</html>

add_profile.html

HTML

<!DOCTYPE html>
<html>
   <head>
      <title>Add Profile</title>
   </head>
   <body>
      <h3>Profile form</h3>
      <form action="/add" method="POST">
         <label>First Name</label>
         <input type="text" name="first_name" placeholder="first name...">
         <label>Last Name</label>
         <input type="text" name= "last_name" placeholder="last name...">
         <label>Age</label>
         <input type="number" name="age" placeholder="age..">
         <button type="submit">Add</button>
      </form>
   </body>
</html>

Output: