Interact with Databases in Flask Using Flask-SQLAlchemy

In web applications, you usually need a database, which is an organized collection of data. You use a database to store and maintain persistent data that can be retrieved and manipulated efficiently. For example, in a social media application, you have a database where user data (personal information, posts, comments, followers) is stored in a way that can be efficiently manipulated. You can add data to a database, retrieve it, modify it, or delete it, depending on different requirements and conditions. In a web application, these requirements might be a user adding a new post, deleting a post, or deleting their account, which may or may not delete their posts. The actions you perform to manipulate data will depend on specific features in your application. For example, you might not want users to add posts with no titles.

Flask is a lightweight Python web framework that provides useful tools and features for creating web applications in the Python Language. SQLAlchemy is an SQL toolkit that provides efficient and high-performing database access for relational databases. It provides ways to interact with several database engines such as SQLite, MySQL, and PostgreSQL. It gives you access to the database’s SQL functionalities. It also gives you an Object Relational Mapper (ORM), which allows you to make queries and handle data using simple Python objects and methods. Flask-SQLAlchemy is a Flask extension that makes using SQLAlchemy with Flask easier, providing you tools and methods to interact with your database in your Flask applications through SQLAlchemy.

In this tutorial, you’ll build a small student management system that demonstrates how to use the Flask-SQLAlchemy extension. You’ll use it with Flask to perform basic tasks, such as connecting to a database server, creating a table, adding data to your table, retrieving it, and updating and deleting items from your database. You will also learn to manage schema changes with Flask-Migrate, examine performance considerations, and weigh the benefits and drawbacks of using an ORM. You’ll use SQLAlchemy with SQLite, although you can use it with other database engines too, such as PostgreSQL and MySQL. SQLite works well with Python because the Python standard library provides the sqlite3 module, which is used by SQLAlchemy behind the scenes to interact with SQLite databases without having to install anything. SQLite is installed on Linux systems by default, and is installed as part of the Python package on Windows.

• A local Python 3 programming environment. Follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial we’ll call our project directory flask_app.

• An understanding of basic Flask concepts, such as routes, view functions, and templates. If you are not familiar with Flask, check out How to Create Your First Web Application Using Flask and Python and How to Use Templates in a Flask Application.

• An understanding of basic HTML concepts. You can review our How To Build a Website with HTML tutorial series for background knowledge.

In this step, you’ll install the necessary packages for your application.

With your virtual environment activated, use pip to install Flask and Flask-SQLAlchemy:

pip install Flask Flask-SQLAlchemy

Once the installation is successfully finished, you’ll see a line similar to the following at the end of the output:

Output
Successfully installed Flask-2.0.3 Flask-SQLAlchemy-2.5.1 Jinja2-3.0.3 MarkupSafe-2.1.0 SQLAlchemy-1.4.31 Werkzeug-2.0.3 click-8.0.4 greenlet-1.1.2 itsdangerous-2.1.0

With the required Python packages installed, you’ll set up the database next.

In this step, you’ll set up your database connection, and create an SQLAlchemy database model, which is a Python class that represents the table that stores your data. You’ll initiate the database, create a table for students based on the model you’ll declare, and add a few students into your students table.

Setting up The Database Connection

Open a file called app.py in your flask_app directory. This file will have code for setting up the database and your Flask routes:

nano app.py

This file connects to an SQLite database named database.db, and defines a class called Student that represents the student table for storing student information, in addition to your Flask routes. Add the following import statements at the top of app.py:

import os
from flask import Flask, render_template, request, url_for, redirect
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.sql import func

Here, you import the os module, which gives you access to miscellaneous operating system interfaces. You’ll use it to construct a file path for your database.db database file.

From the flask package, you then import the necessary helpers you need for your application: the Flask class to create a Flask application instance, the render_template() function to render templates, the request object to handle requests, the url_for() function to construct URLs for routes, and the redirect() function for redirecting users. For more information on routes and templates, see How To Use Templates in a Flask Application.

You then import the SQLAlchemy class from the Flask-SQLAlchemy extension, which gives you access to all the functions and classes from SQLAlchemy, in addition to helpers, and functionality that integrates Flask with SQLAlchemy. You’ll use it to create a database object that connects to your Flask application, allowing you to create and manipulate tables using Python classes, objects, and functions without needing to use the SQL language.

You also import the func helper from the sqlalchemy.sql module to access SQL functions. You’ll need it in your student management system to set a default creation date and time for when a student record is created.

Below the imports, you’ll set up a database file path, instantiate your Flask application, and configure and connect your application with SQLAlchemy. Add the following code:

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

Here, you construct a path for your SQLite database file. You first define a base directory as the current directory. You use the os.path.abspath() function to get the absolute path of the current file’s directory. The special __file__ variable holds the pathname of the current app.py file. You store the absolute path of the base directory in a variable called basedir.

You then create a Flask application instance called app, which you use to configure two Flask-SQLAlchemy configuration keys:

• SQLALCHEMY_DATABASE_URI: The database URI to specify the database you want to establish a connection with. In this case, the URI follows the format sqlite:///path/to/database.db. You use the os.path.join() function to intelligently join the base directory you constructed and stored in the basedir variable, and the database.db file name. This will connect to a database.db database file in your flask_app directory. The file will be created once you initiate the database.

• SQLALCHEMY_TRACK_MODIFICATIONS: A configuration to enable or disable tracking modifications of objects. You set it to False to disable tracking and use less memory. For more, see the configuration page in the Flask-SQLAlchemy documentation.

postgresql://username:password@host:port/database_name

mysql://username:password@host:port/database_name

After configuring SQLAlchemy by setting a database URI and disabling tracking, you create a database object using the SQLAlchemy class, passing the application instance to connect your Flask application with SQLAlchemy. You store your database object in a variable called db. You’ll use this db object to interact with your database.

Declaring The Table

With the database connection established and the database object created, you’ll use the database object to create a database table for students, which is represented by a model — a Python class that inherits from a base class Flask-SQLAlchemy provides through the db database instance you created earlier. To define a student table as a model, add the following class to your app.py file:

# ...

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(100), nullable=False)
    lastname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(80), unique=True, nullable=False)
    age = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True),
                           server_default=func.now())
    bio = db.Column(db.Text)

    def __repr__(self):
        return f'<Student {self.firstname}>'

Here, you create a Student model, which inherits from the db.Model class. This represents the student table. You use the db.Column class to define columns for your table. The first argument represents the column type, and additional arguments represent column configuration.

You define the following columns for the Student model:

• id: The student ID. You define it as an integer with db.Integer. primary_key=True defines this column as a primary key, which will assign it a unique value by the database for each entry (that is a student).
• firstname: The student’s first name. A string with a maximum length of 100 characters. nullable=False signifies that this column should not be empty.
• lastname: The student’s last name. A string with a maximum length of 100 characters. nullable=False signifies that this column should not be empty.
• email: The student’s email. A string with a maximum length of 80 characters. unique=True signifies that each email should be unique for each student. nullable=False signifies that this column should not be empty.
• age: The student’s age.
• created_at: The time the student record was created at in the database. You use db.DateTime to define it as a Python datetime object. timezone=True enables timezone support. server_default sets the default value in the database when creating the table, so that default values are handled by the database rather than the model. You pass it the func.now() function which calls the SQL now() datetime function. In SQLite, it is rendered as CURRENT_TIMESTAMP when creating the student table.
• bio: The student’s bio. db.Text() indicates the column holds long texts.

See the SQLAlchemy documentation for column types other than the types you used in the preceding code block.

The special __repr__ function allows you to give each object a string representation to recognize it for debugging purposes. In this case you use the student’s first name.

The app.py file will now look as follows:

import os
from flask import Flask, render_template, request, url_for, redirect
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.sql import func


basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =\
        'sqlite:///' + os.path.join(basedir, 'database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(100), nullable=False)
    lastname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(80), unique=True, nullable=False)
    age = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True),
                           server_default=func.now())
    bio = db.Column(db.Text)

    def __repr__(self):
        return f'<Student {self.firstname}>'

Save and close app.py.

Creating the Database

Now that you’ve set the database connection and the student model, you’ll use the Flask shell to create your database and your student table based on the Student model.

With your virtual environment activated, set the app.py file as your Flask application using the FLASK_APP environment variable. Then open the Flask shell using the following command in your flask_app directory:

export FLASK_APP=app
flask shell

A Python interactive shell will be opened. This special shell runs commands in the context of your Flask application, so that the Flask-SQLAlchemy functions you’ll call are connected to your application.

Import the database object and the student model, and then run the db.create_all() function to create the tables that are associated with your models. In this case you only have one model, which means that the function call will only create one table in your database:

from app import db, Student
db.create_all()

Leave the shell running, open another terminal window and navigate to your flask_app directory. You will now see a new file called database.db in flask_app.

db.drop_all()
db.create_all()

If you receive an error, make sure your database URI and your model declaration are correct.

Populating the Table

After creating the database and student table, you’ll use the flask shell to add some students to your database through the Student model.

Use the same flask shell you opened earlier, or open a new one with your virtual environment activated in your flask_app directory:

flask shell

To add a student to your database, you’ll import the database object and the Student model, and create an instance of the Student model, passing it student data through keyword arguments as follows:

from app import db, Student
student_john = Student(firstname='john', lastname='doe',
                       email='jd@example.com', age=23,
                       bio='Biology student')

The student_john object represents a student that will be added to the database, but this object has not been written to the database yet. Check out the object in the flask shell to see its representation string you constructed with the __repr__() method:

student_john

You’ll receive the following output:

Output
<Student john>

You can get the value of columns using the class attributes you defined in the Student model:

student_john.firstname
student_john.bio

Output
'john'
'Biology student'

Because this student has not been added to the database yet, its ID will be None:

print(student_john.id)

Output
None

To add this student to the database, you’ll first need to add it to a database session, which manages a database transaction. Flask-SQLAlchemy provides the db.session object through which you can manage your database changes. Add the student_john object to the session using the db.session.add() method to prepare it to be written to the database:

db.session.add(student_john)

This will issue an INSERT statement, but you won’t get an ID back because the database transaction is still not committed. To commit the transaction and apply the change to database, use the db.session.commit() method:

db.session.commit()

Now that student John is added to the database, you can get its ID:

print(student_john.id)

Output
1

You can also use the db.session.add() method to edit an item in the database. For example, you can modify the student’s email like so:

student_john.email = 'john_doe@example.com'
db.session.add(student_john)
db.session.commit()

Use the Flask shell to add a few more students to your database:

sammy = Student(firstname='Sammy',
               lastname='Shark',
               email='sammyshark@example.com',
               age=20,
               bio='Marine biology student')

carl = Student(firstname='Carl',
               lastname='White',
               email='carlwhite@example.com',
               age=22,
               bio='Marine geology student')

db.session.add(sammy)
db.session.add(carl)
db.session.commit()

Now, you can query all the records in the student table using the query attribute with the all() method:

Student.query.all()

You’ll receive the following output:

Output
[<Student john>, <Student Sammy>, <Student Carl>]

At this point, you have three students in your database. Next, you’ll create a Flask route for the index page and display all of the students in your database on it.

In this step, you’ll create a route and a template to display all the students in the database on the index page.

Leave the Flask shell running and open a new terminal window.

Open your app.py file to add a route for the index page to it:

nano app.py

Add the following route at the end of the file:

# ...

@app.route('/')
def index():
    students = Student.query.all()
    return render_template('index.html', students=students)

Save and close the file.

Here, you create an index() view function using the app.route() decorator. In this function, you query the database and get all the students using the Student model with the query attribute, which allows you to retrieve one or more items from the database using different methods. You use the all() method to get all student entries in the database. You store the query result in a variable called students and pass it to a template called index.html that you render using the render_template() helper function.

Before you create the index.html template file on which you’ll display the existing students in the database, you’ll first create a base template, which will have all the basic HTML code other templates will also use to avoid code repetition. Then you’ll create the index.html template file you rendered in your index() function. To learn more about templates, see How to Use Templates in a Flask Application.

Create a templates directory, then open a new template called base.html:

mkdir templates
nano templates/base.html

Add the following code inside the base.html file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %} - FlaskApp</title>
    <style>
        .title {
            margin: 5px;
        }

        .content {
            margin: 5px;
            width: 100%;
            display: flex;
            flex-direction: row;
            flex-wrap: wrap;
        }

        .student {
            flex: 20%;
            padding: 10px;
            margin: 5px;
            background-color: #f3f3f3;
            inline-size: 100%;
        }

        .bio {
            padding: 10px;
            margin: 5px;
            background-color: #ffffff;
            color: #004835;
        }

        .name a {
            color: #00a36f;
            text-decoration: none;
        }

        nav a {
            color: #d64161;
            font-size: 3em;
            margin-left: 50px;
            text-decoration: none;
        }
    </style>
</head>
<body>
    <nav>
        <a href="{{ url_for('index') }}">FlaskApp</a>
        <a href="#">Create</a>
        <a href="#">About</a>
    </nav>
    <hr>
    <div class="content">
        {% block content %} {% endblock %}
    </div>
</body>
</html>

Save and close the file.

This base template has all the HTML boilerplate you’ll need to reuse in your other templates. The title block will be replaced to set a title for each page, and the content block will be replaced with the content of each page. The navigation bar has three links: one for the index page, which links to the index() view function using the url_for() helper function, one for a Create page, and one for an About page if you choose to add one to your application. You’ll edit this file later after you add a page for creating new students to make the Create link functional.

Next, open a new index.html template file. This is the template you referenced in the app.py file:

nano templates/index.html

Add the following code to it:

{% extends 'base.html' %}

{% block content %}
    <h1 class="title">{% block title %} Students {% endblock %}</h1>
    <div class="content">
        {% for student in students %}
            <div class="student">
                <p><b>#{{ student.id }}</b></p>
                <b>
                    <p class="name">{{ student.firstname }} {{ student.lastname }}</p>
                </b>
                <p>{{ student.email }}</p>
                <p>{{ student.age }} years old.</p>
                <p>Joined: {{ student.created_at }}</p>
                <div class="bio">
                    <h4>Bio</h4>
                    <p>{{ student.bio }}</p>
                </div>
            </div>
        {% endfor %}
    </div>
{% endblock %}

Save and close the file.

Here, you extend the base template and replace the contents of the content block. You use an <h1> heading that also serves as a title. You use a Jinja for loop in the line {% for student in students %} to go through each student in the students variable that you passed from the index() view function to this template. You display the student ID, their first and last name, email, age, the date at which they were added to the database, and their bio.

While in your flask_app directory with your virtual environment activated, tell Flask about the application (app.py in this case) using the FLASK_APP environment variable. Then set the FLASK_ENV environment variable to development to run the application in development mode and get access to the debugger. For more information about the Flask debugger, see How To Handle Errors in a Flask Application. Use the following commands to do this:

export FLASK_APP=app
export FLASK_ENV=development

Next, run the application:

flask run

With the development server running, visit the following URL using your browser:

http://127.0.0.1:5000/

You’ll see the students you added to the database in a page similar to the following:

You’ve displayed the students you have in your database on the index page. Next, you’ll create a route for a student page, where you can display the details of each individual student.

In this step, you’ll use the Flask shell to query students by their ID, and create a route and a template to display the details of each student on a dedicated page.

By the end of this step, the URL http://127.0.0.1:5000/1 will be a page that displays the first student (because it has the ID 1). The URL http://127.0.0.1:5000/ID will display the post with the associated ID number, if it exists.

Leave the development server running and open a new terminal window.

Open the Flask shell for a demonstration of how to query students:

flask shell

To query records and retrieve data from the database, Flask-SQLAlchemy provides a query attribute on the model class. You can use its methods to get records with a specific filter.

For example, you can use the filter_by() method with a parameter such as firstname that matches a column in the table with an argument to retrieve a specific student:

from app import db, Student
Student.query.filter_by(firstname='Sammy').all()

Output
[<Student Sammy>]

Here you retrieve all the students with Sammy as their first name. You use the all() method to get a list of all the results. To get the first result, which is the only result here, you can use the first() method:

Student.query.filter_by(firstname='Sammy').first()

Output
<Student Sammy>

To get a student by its ID, you can use filter_by(id=ID):

Student.query.filter_by(id=3).first()

Or, you can use the shorter get() method, which allows you to retrieve a specific item using its primary key:

Student.query.get(3)

Both will give the same output:

Output
<Student Carl>

You can now exit the shell:

exit()

To retrieve a student by their ID, you’ll create a new route that renders a page for each individual student. You’ll use the get_or_404() method Flask-SQLAlchemy provides, which is a variant of the get() method. The difference is that get() returns the value None when no result matches the given ID, and get_or_404() returns a 404 Not Found HTTP response. Open app.py for modification:

nano app.py

Add the following route at the end of the file:

# ...

@app.route('/<int:student_id>/')
def student(student_id):
    student = Student.query.get_or_404(student_id)
    return render_template('student.html', student=student)

Save and close the file.

Here, you use the route '/<int:student_id>/', with int: being a converter that converts the default string in the URL into an integer. And student_id is the URL variable that will determine the student you’ll display on the page.

The ID is passed from the URL to the student() view function through the student_id parameter. Inside the function, you query the students collection and retrieve a student by the ID using the get_or_404() method. This will save the student data in the student variable if it exists, and respond with a 404 Not Found HTTP error if no student with the given ID exists in the database.

You render a template called student.html and pass it the student you retrieved.

Open this new student.html template file:

nano templates/student.html

Type the following code in this new student.html file. This will be similar to the index.html template, except that it will only display a single student:

{% extends 'base.html' %}

{% block content %}
    <span class="title">
        <h1>{% block title %} {{ student.firstname }} {{ student.lastname }}{% endblock %}</h1>
    </span>
    <div class="content">
            <div class="student">
                <p><b>#{{ student.id }}</b></p>
                <b>
                    <p class="name">{{ student.firstname }} {{ student.lastname }}</p>
                </b>
                <p>{{ student.email }}</p>
                <p>{{ student.age }} years old.</p>
                <p>Joined: {{ student.created_at }}</p>
                <div class="bio">
                    <h4>Bio</h4>
                    <p>{{ student.bio }}</p>
                </div>
            </div>
    </div>
{% endblock %}

Save and close the file.

In this file, you extend the base template, setting the student’s full name as a title for the page. You display the student ID, the student’s first and last name, email, age, the date of record creation, and their bio.

Use your browser to navigate to the URL for the second student:

http://127.0.0.1:5000/2

You’ll see a page similar to the following:

Now, edit index.html to make each student name link to their page:

nano templates/index.html

Edit the for loop to look as follows:

{% for student in students %}
    <div class="student">
        <p><b>#{{ student.id }}</b></p>
        <b>
            <p class="name">
                <a href="{{ url_for('student', student_id=student.id)}}">
                    {{ student.firstname }} {{ student.lastname }}
                </a>
            </p>
        </b>
        <p>{{ student.email }}</p>
        <p>{{ student.age }} years old.</p>
        <p>Joined: {{ student.created_at }}</p>
        <div class="bio">
            <h4>Bio</h4>
            <p>{{ student.bio }}</p>
        </div>
    </div>
{% endfor %}

Save and close the file.

You added an <a> tag to the student’s full name which links to the student page using the url_for() function, passing the student ID that’s stored in student.id to the student() view function.

Navigate to your index page or refresh it:

http://127.0.0.1:5000/

You’ll now see that each student name links to the proper student page.

After creating a page for individual students, you’ll next add a page for adding new students to the database.

In this step, you’ll add a new route to your application for adding new students to the database using web forms.

You’ll render a page with a web form where users enter the student’s data. Then you’ll handle the form submission, create an object for the new student using the Student model, add it to the session, then commit the transaction, similar to how you’ve added student entries in Step 2.

Leave the development server running and open a new terminal window.

First, open your app.py file:

nano app.py

Add the following route at the end of the app.py file:

# ...


@app.route('/create/', methods=('GET', 'POST'))
def create():
    return render_template('create.html')

Save and close the file.

In this route, you pass the tuple ('GET', 'POST') to the methods parameter to allow both GET and POST requests. GET requests are used to retrieve data from the server. POST requests are used to post data to a specific route. By default, only GET requests are allowed. When the user first requests the /create route using a GET request, a template file called create.html will be rendered. You will later edit this route to handle POST requests for when users fill in and submit the web form for adding new students.

Open the new create.html template:

nano templates/create.html

Add the following code to it:

{% extends 'base.html' %}

{% block content %}
    <h1 style="width: 100%">{% block title %} Add a New Student {% endblock %}</h1>
    <form method="post">
        <p>
            <label for="firstname">First Name</label>
            <input type="text" name="firstname"
                   placeholder="First name">
            </input>
        </p>

        <p>
            <label for="lastname">Last Name</label>
            <input type="text" name="lastname"
                   placeholder="Last name">
            </input>
        </p>

        <p>
            <label for="email">Email</label>
            <input type="email" name="email"
                   placeholder="Student email">
            </input>
        </p>

        <p>
            <label for="age">Age</label>
            <input type="number" name="age"
                   placeholder="Age">
            </input>
        </p>

        <p>
        <label for="bio">Bio</label>
        <br>
        <textarea name="bio"
                  placeholder="Bio"
                  rows="15"
                  cols="60"
                  ></textarea>
        </p>
        <p>
            <button type="submit">Submit</button>
        </p>
    </form>
{% endblock %}

Save and close the file.

You extend the base template, set a heading as a title, and use a <form> tag with the attribute method set to post to indicate that the form will submit a POST request.

You have two text fields with the names firstname and lastname. You’ll use these names to access the form data the user submits in your view function later.

You have an email field with the name email, a number field for the student’s age, and a text area for the student’s bio.

Last, you have a Submit button at the end of the form.

Now, with the development server running, use your browser to navigate to the /create route:

http://127.0.0.1:5000/create

You will see an Add a New Student page with a web form and a Submit button like so:

If you fill in the form and submit it, sending a POST request to the server, nothing happens because you did not handle POST requests on the /create route.

Open app.py to handle the POST request the user submits:

nano app.py

Edit the /create route to look as follows:

@app.route('/create/', methods=('GET', 'POST'))
def create():
    if request.method == 'POST':
        firstname = request.form['firstname']
        lastname = request.form['lastname']
        email = request.form['email']
        age = int(request.form['age'])
        bio = request.form['bio']
        student = Student(firstname=firstname,
                          lastname=lastname,
                          email=email,
                          age=age,
                          bio=bio)
        db.session.add(student)
        db.session.commit()

        return redirect(url_for('index'))

    return render_template('create.html')

Save and close the file.

You handle POST requests inside the if request.method == 'POST' condition. You extract the first name, last name, email, age, and bio the user submits from the request.form object. You convert the age that gets passed as a string into an integer using the int() Python function. You construct a student object using the Student model. You add the student object to the database session, then commit the transaction.

Lastly, you redirect the user to the index page where they can see the newly added student below the existing students.

With the development server running, use your browser to navigate to the /create route:

http://127.0.0.1:5000/create

Fill in the form with some data and submit it.

You’ll be redirected to the index page where you’ll see your newly added student.

Now that you have the functionality to add new students, you’ll need to add a link to the Create page in the navigation bar. Open base.html:

nano templates/base.html

Edit the <body> tag by modifying the value of the href attribute for the Create link:

<body>
    <nav>
        <a href="{{ url_for('index') }}">FlaskApp</a>
        <a href="{{ url_for('create') }}">Create</a>
        <a href="#">About</a>
    </nav>
    <hr>
    <div class="content">
        {% block content %} {% endblock %}
    </div>
</body>

Save and close the file.

Refresh your index page and you’ll notice the Create link in the navigation bar is now functional.

You now have a page with a web form for adding new students. For more on web forms, see How To Use Web Forms in a Flask Application. For a more advanced and more secure method of managing web forms, see How To Use and Validate Web Forms with Flask-WTF. Next, you’ll add a page for editing the data of existing students.

In this step, you’ll add a new page to your application for editing existing student data. You’ll add a new /ID/edit/ route to edit the data of students based on their ID.

Open app.py:

nano app.py

Add the following route to the end of the file. This fetches the student entry you want to edit using its ID. It extracts the new student data submitted via a web form you’ll will create later. Then it edits the student data, and redirects the user to the index page:

# ...


@app.route('/<int:student_id>/edit/', methods=('GET', 'POST'))
def edit(student_id):
    student = Student.query.get_or_404(student_id)

    if request.method == 'POST':
        firstname = request.form['firstname']
        lastname = request.form['lastname']
        email = request.form['email']
        age = int(request.form['age'])
        bio = request.form['bio']

        student.firstname = firstname
        student.lastname = lastname
        student.email = email
        student.age = age
        student.bio = bio

        db.session.add(student)
        db.session.commit()

        return redirect(url_for('index'))

    return render_template('edit.html', student=student)

Save and close the file.

Here, you have the route /<int:student_id>/edit/ that accepts both POST and GET methods, with student_id as a URL variable that passes the ID to the edit() view function.

You use the get_or_404() query method on the Student model to get the student associated with the given student ID. This will respond with a 404 Not Found error in case no student with the given ID exists in the database.

If the given ID has a student associated with it, code execution continues to the if request.method == 'POST' condition. If the request was a GET request, meaning that the user did not submit a form, then this condition is false, and the code inside it will be skipped to the line return render_template('edit.html', student=student). This renders an edit.html template, passing it the student object you got from the database, allowing you to fill the student web form with current student data. You will create this edit.html template later.

When a user edits student data and submits the form, the code inside the if request.method == 'POST' is executed. You extract the submitted student data from the request.form object into corresponding variables. You set each attribute of the student object to the newly submitted data to change column values as you’ve done in Step 2. If no change was performed on a field on the web form, the value of that column will stay the same in the database.

After you set the student data to the newly submitted data, you add the student object to the database session, then you commit the changes. Lastly, you redirect the user to the index page.

Next, you need to create a page where users can do the editing. Open a new edit.html template:

nano templates/edit.html

This new file will have a web form similar to the one that’s in the create.html file with current student data as default values for the fields. Add the following code inside it:

{% extends 'base.html' %}

{% block content %}
    <h1 style="width: 100%">
        {% block title %} Edit {{ student.firstname }}
                               {{ student.lastname }}'s Details
        {% endblock %}
    </h1>
    <form method="post">
        <p>
            <label for="firstname">First Name</label>
            <input type="text" name="firstname"
                   value={{ student.firstname }}
                   placeholder="First name">
            </input>
        </p>

        <p>
            <label for="lastname">Last Name</label>
            <input type="text" name="lastname"
                   value={{ student.lastname }}
                   placeholder="Last name">
            </input>
        </p>

        <p>
            <label for="email">Email</label>
            <input type="email" name="email"
                   value={{ student.email }}
                   placeholder="Student email">
            </input>
        </p>

        <p>
            <label for="age">Age</label>
            <input type="number" name="age"
                   value={{ student.age }}
                   placeholder="Age">
            </input>
        </p>

        <p>
        <label for="bio">Bio</label>
        <br>
        <textarea name="bio"
                  placeholder="Bio"
                  rows="15"
                  cols="60"
                  >{{ student.bio }}</textarea>
        </p>
        <p>
            <button type="submit">Submit</button>
        </p>
    </form>
{% endblock %}

Save and close the file.

The title has the student’s first name and last name. The value attribute of each input field and the value of the bio text area are set to the corresponding value in the student object you passed from the edit() view function to the edit.html template.

Now, navigate to the following URL to edit the first student’s details:

http://127.0.0.1:5000/1/edit

You will see a page similar to the following:

Edit the student’s data and submit the form. You’ll be redirected to the index page, and the student’s information will be updated.

Next, you’ll add an Edit button below each student on the index page to link to their edit page. Open the index.html template file:

nano templates/index.html

Edit the for loop in this index.html file to look exactly like the following:

{% for student in students %}
    <div class="student">
        <p><b>#{{ student.id }}</b></p>
        <b>
            <p class="name">
                <a href="{{ url_for('student', student_id=student.id)}}">
                    {{ student.firstname }} {{ student.lastname }}
                </a>
            </p>
        </b>
        <p>{{ student.email }}</p>
        <p>{{ student.age }} years old.</p>
        <p>Joined: {{ student.created_at }}</p>
        <div class="bio">
            <h4>Bio</h4>
            <p>{{ student.bio }}</p>
        </div>
        <a href="{{ url_for('edit', student_id=student.id) }}">Edit</a>
    </div>
{% endfor %}

Save and close the file.

Here you add an <a> tag to link to the edit() view function, passing in the student.id value to link to the edit page of each student with an Edit link.

You now have a page for editing existing students. Next, you’ll add a Delete button to delete students from the database.

In this step, you’ll add a new route and Delete button for deleting existing students.

First, you’ll add a new /id/delete route that accepts POST requests. Your new delete() view function will receive the ID of the student you want to delete, pass the ID to the get_or_404() query method on the Student model to get it if it exists, or respond with a 404 Not Found page if no student with the given ID was found on the database.

Open app.py for editing:

nano app.py

Add the following route to the end of the file:

# ...

@app.post('/<int:student_id>/delete/')
def delete(student_id):
    student = Student.query.get_or_404(student_id)
    db.session.delete(student)
    db.session.commit()
    return redirect(url_for('index'))

Save and close the file.

Here, instead of using the usual app.route decorator, you use the app.post decorator introduced in Flask version 2.0.0, which added shortcuts for common HTTP methods. For example, @app.post("/login") is a shortcut for @app.route("/login", methods=["POST"]). This means that this view function only accepts POST requests, and navigating to the /ID/delete route on your browser will return a 405 Method Not Allowed error, because web browsers default to GET requests. To delete a student, the user clicks on a button that sends a POST request to this route.

This delete() view function receives the ID of the student to be deleted via the student_id URL variable. You use the get_or_404() method to get a student and save it in a student variable, or respond with a 404 Not Found in case the student doesn’t exist. You use the delete() method on the database session in the line db.session.delete(student), passing it the student object. This sets up the session to delete the student whenever the transaction is committed. Because you don’t need to perform any other modifications, you directly commit the transaction using db.session.commit(). Lastly, you redirect the user to the index page.

Next, edit the index.html template to add a Delete Student button:

nano templates/index.html

Edit the for loop by adding a new <form> tag directly below the Edit link:

{% for student in students %}
    <div class="student">
        <p><b>#{{ student.id }}</b></p>
        <b>
            <p class="name">
                <a href="{{ url_for('student', student_id=student.id)}}">
                    {{ student.firstname }} {{ student.lastname }}
                </a>
            </p>
        </b>
        <p>{{ student.email }}</p>
        <p>{{ student.age }} years old.</p>
        <p>Joined: {{ student.created_at }}</p>
        <div class="bio">
            <h4>Bio</h4>
            <p>{{ student.bio }}</p>
        </div>
        <a href="{{ url_for('edit', student_id=student.id) }}">Edit</a>

        <hr>
        <form method="POST"
                action="{{ url_for('delete', student_id=student.id) }}">
            <input type="submit" value="Delete Student"
                onclick="return confirm('Are you sure you want to delete this entry?')">
        </form>

    </div>
{% endfor %}

Save and close the file.

Here, you have a web form that submits a POST request to the delete() view function. You pass student.id as an argument for the student_id parameter to specify the student entry to be deleted. You use the confirm() method function available in web browsers to display a confirmation message before submitting the request.

Now refresh your index page.

You’ll see a Delete Student button below each student entry. Click on it, and confirm the deletion. You’ll be redirected to the index page, and the student will no longer be there.

You now have a way of deleting students from the database in your student management application.

When you build a database-backed application, your database schema will often change as you add or modify features. For example, you might need to add a new column to a user table to store a profile picture URL. If you make this change directly in your model, the database will not automatically update, which will cause your application to fail. Manually updating the database is prone to error and difficult to manage, especially in a team environment.

A database migration is a programmatic way to manage incremental and reversible changes to your database schema. Flask-Migrate is a Flask extension that handles SQLAlchemy database migrations using Alembic, a lightweight and flexible database migration tool. It allows you to update your database schema in a structured and repeatable way.

Setting up Flask-Migrate

First, install the Flask-Migrate extension using pip:

pip install Flask-Migrate

Next, you need to initialize Flask-Migrate in your Flask application. This is done by creating an instance of the Migrate class after you have initialized your application and your SQLAlchemy object.

Modify your app.py file to include the highlighted code:

import os
from flask import Flask, render_template, request, url_for, redirect
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

from sqlalchemy.sql import func

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
migrate = Migrate(app, db)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(100), nullable=False)
    lastname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(80), unique=True, nullable=False)
    age = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    bio = db.Column(db.Text)

    def __repr__(self):
        return f'<Student {self.firstname}>'

Creating the Initial Migration

With Flask-Migrate initialized, the first step is to create a migration repository. This is a directory that will store all the migration scripts. Run the following command in your terminal:

flask db init

This command creates a migrations directory in your project. Inside this directory, you will find a versions sub-directory, which will contain the individual migration files, and a file named alembic.ini, which is the configuration file for Alembic.

Next, you need to generate the first migration. This migration will capture the current state of your database models and create a script to generate the corresponding schema.

flask db migrate -m "Initial migration."

This command will create a new file in the migrations/versions directory. The file will contain the Python code that defines the steps to create your tables.

Finally, to apply the migration and create the tables in your database, run the upgrade command:

flask db upgrade

Your database now contains the students table, and it is fully managed by Flask-Migrate.

Managing Migrations

Before you start making changes to your schema, it is important to adopt a few best practices to ensure a smooth and safe migration process.

• Review Migration Files: Alembic does its best to detect changes in your models, but it is not infallible. Before you apply any migration, you should always open the generated script in the migrations/versions directory and review the code. Make sure that the changes in the script match the changes you intended to make to your models. This can help you catch potential issues before they affect your database.
• Backup Production Databases: Before you run a migration on a production database, always create a backup. Migrations can sometimes fail, and if they do, you will need a way to restore your database to its previous state. A backup ensures that you can recover from any unexpected problems without losing data.
• Version Control for Migrations: Your migration scripts are a part of your application’s codebase. As such, they should be committed to your version control system, such as Git. This keeps a history of your schema changes and ensures that all team members have the same migration scripts. When a new team member joins the project, they can run flask db upgrade to bring their local database schema up to date with the latest version.

Making and Applying Schema Changes

Now, let’s say you want to add a new city column to the students model. You would first update the model in your Python code:

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(100), nullable=False)
    lastname = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(80), unique=True, nullable=False)
    age = db.Column(db.Integer)
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    bio = db.Column(db.Text)
    city = db.Column(db.String(80))

After updating the model, you need to generate a new migration script:

flask db migrate -m "Add city column to students table."

This will create a new migration file in the versions directory. The file will contain the necessary code to add the city column to the students table.

To apply this change to the database, run the upgrade command again:

flask db upgrade

The students table in your database now has the new city column. Using Flask-Migrate, you can keep your database schema in sync with your models in a controlled and versioned way.

As a Flask application grows, the performance of its database interactions becomes a critical factor. Inefficient queries, poor data loading strategies, and unhandled database errors can significantly degrade user experience, particularly under high traffic or with large datasets.

This section provides techniques to optimize your Flask-SQLAlchemy application. You will learn how to write efficient queries, choose appropriate data loading strategies, and use debugging tools to identify and resolve performance bottlenecks.

Writing Efficient Queries

The foundation of a high-performing database layer is well-written queries. SQLAlchemy provides several methods to ensure your application retrieves only the data it needs, reducing database load and speeding up response times.

Selecting Specific Columns

By default, a query like User.query.all() retrieves all columns for the matching rows and creates a full model object for each one. If you only need data from a few specific columns, this approach is inefficient.

To retrieve only the necessary columns, use the with_entities() method. This method returns tuples containing just the data you requested, which uses less memory and reduces query execution time.

# Fetches only the email column for all users
user_emails = db.session.query(Student.email).all()

This is particularly effective when you need to populate a dropdown list or a simple table and do not require the entire model object.

Limiting and Ordering Results

When working with tables that contain thousands of rows, fetching the entire result set is often unnecessary and slow. Use the limit() method to restrict the number of records returned and order_by() to sort the results.

This is a common pattern for implementing pagination or displaying a “most recent” list.

# Get the 10 most recently created users
recent_users = Student.query.order_by(Student.created_at.desc()).limit(10).all()

Using Database Indexes

An index is a data structure that improves the speed of data retrieval operations on a database table. If your application frequently filters queries based on a specific column, such as a username or an email address, adding an index to that column can produce a significant performance gain.

You can define an index directly in your model using the index=True argument.

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False, index=True)
    email = db.Column(db.String(120), unique=True, nullable=False, index=True)

After adding an index to your model, you must generate and apply a new database migration for the change to take effect in the database schema. While indexes speed up read operations, they add a small amount of overhead to write operations (inserts and updates), so they should be used on columns that are frequently used in WHERE clauses.

Choosing a Relationship Loading Strategy

When you query for an object that has a relationship with another object, SQLAlchemy must decide when to load the related data. The choice between lazy loading and eager loading has a direct impact on the number of queries your application executes.

By default, relationships use lazy loading. This can lead to the N+1 query problem, where one query retrieves the parent objects and N subsequent queries are executed to retrieve the related objects for each parent.

To prevent this, you can specify an eager loading strategy for a query using joinedload.

from sqlalchemy.orm import joinedload

users_with_posts = User.query.options(joinedload(User.posts)).all()

This loads all users and their posts in a single database query. This approach is much more efficient when you know you will need to access the related posts for each user.

Caching Frequent Queries

If your application repeatedly executes the same queries for data that does not change often, such as a list of top articles or user statistics, caching can reduce the load on your database. You can integrate a caching library like Flask-Caching to store the results of these queries in memory for a short period.

First, install the library:

pip install Flask-Caching

Then, configure it in your application and use the @cache.cached() decorator on your routes.

from flask_caching import Cache

# App configuration
app.config["CACHE_TYPE"] = "SimpleCache"  # Use a simple in-memory cache
cache = Cache(app)

@app.route("/students")
@cache.cached(timeout=300)  # Cache the result for 300 seconds (5 minutes)
def get_students():
    students = Student.query.all()
        # ... logic to render results

The first time this route is accessed, the database query runs and the result is stored in the cache. Subsequent requests within the timeout period will receive the cached result directly, avoiding any database interaction.

Monitoring and Debugging Tools

To find performance issues, you need to be able to inspect the SQL that your application is running.

Logging SQL Queries to the Console

For immediate feedback during development, you can configure Flask-SQLAlchemy to print every executed SQL statement to the console.

Add the following line to your application configuration:

app.config["SQLALCHEMY_ECHO"] = True

This is a direct way to see exactly what queries are being generated by your code. This option should only be used in development.

Using Flask-DebugToolbar

The Flask-DebugToolbar is an extension that provides a debugging overlay in your browser. One of its most useful features is the SQLAlchemy panel, which lists every query executed for a given request, along with its execution time. This makes it easy to spot slow or duplicate queries.

First, install the extension:

pip install flask-debugtoolbar

Then, initialize it in your application:

from flask_debugtoolbar import DebugToolbarExtension

app.debug = True
app.config["SECRET_KEY"] = "a-secret-key-for-the-toolbar"
toolbar = DebugToolbarExtension(app)

When you run your application, the toolbar will appear on the side of your browser window, giving you detailed insight into your application’s database activity.

Handling Database Errors

Database operations can fail for many reasons, such as connection issues (OperationalError) or duplicate data violations (IntegrityError). It is important to handle these exceptions gracefully to prevent your application from crashing.

Use a try...except block to catch potential exceptions from SQLAlchemy and roll back the session if an error occurs.

from sqlalchemy.exc import IntegrityError

try:
    new_user = User(username="alice", email="alice@example.com")
    db.session.add(new_user)
    db.session.commit()
except IntegrityError:
    db.session.rollback()
    flash("Error: A user with that username or email already exists.")

This ensures that the database session is left in a clean state and provides clear feedback to the user.

Flask-SQLAlchemy is a powerful tool for managing databases in Flask applications, but like any technology, it comes with its own set of advantages and potential drawbacks. Understanding these trade-offs can help you decide if it is the right choice for your project and how to use it most effectively.

The following table provides a balanced overview of the benefits and considerations when working with Flask-SQLAlchemy.

1. What is Flask-SQLAlchemy used for?

Flask-SQLAlchemy is a Flask extension that simplifies the use of SQLAlchemy in a Flask application. Its primary purpose is to act as an Object Relational Mapper (ORM), which allows you to interact with your relational database (like SQLite, PostgreSQL, or MySQL) using Python classes and objects instead of writing raw SQL statements.

It handles tasks such as:

• Configuring the database connection.
• Managing database sessions within Flask’s request cycle.
• Providing base classes for defining database models.
• Simplifying common database operations like querying, inserting, and deleting records.

In short, it provides a high-level, Python-friendly interface for your database within the Flask framework.

2. How do I create a database in Flask?

You can create a database and its tables from your models using the db.create_all() method.

1. Configure your app: Set the SQLALCHEMY_DATABASE_URI in your Flask application’s configuration to point to your desired database file or server.
2. Define your models: Create Python classes that inherit from db.Model to define your database tables and columns.
3. Run db.create_all(): Within the Flask application context, call db.create_all() to create all the tables defined in your models. This command will create the database file if it doesn’t exist (for SQLite) and generate the table schema.

Here is a simple example:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# Configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.db"

db = SQLAlchemy(app)

# Define a model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)

# Create the database and tables
with app.app_context():
    db.create_all()

Running this script will create a project.db file with a user table in your instance folder.

3. Can I use Flask-SQLAlchemy with PostgreSQL or MySQL?

Yes. Flask-SQLAlchemy is built on top of SQLAlchemy, which supports a wide range of database engines. To switch from SQLite to a more robust database like PostgreSQL or MySQL, you only need to change the SQLALCHEMY_DATABASE_URI configuration string and install the appropriate database driver.

First, install the required driver:

• For PostgreSQL: pip install psycopg2-binary
• For MySQL: pip install PyMySQL

Next, update the configuration URI in your app.py file:

• PostgreSQL URI format:

  app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:password@host:port/dbname'
  

• MySQL URI format:

  # Note the 'mysql+pymysql' scheme to specify the driver
  app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://user:password@host:port/dbname'
  

The rest of your application code, including your models and queries, remains the same.

4. How do I run migrations in Flask?

Database migrations, the process of managing incremental changes to your database schema, are handled by the Flask-Migrate extension, which integrates the Alembic migration tool with Flask.

Here is the standard workflow:

1. Install Flask-Migrate:

   pip install Flask-Migrate
   

2. Initialize it in your app: Add Flask-Migrate to your application after initializing Flask-SQLAlchemy.

   from flask_migrate import Migrate
   
   # after db = SQLAlchemy(app)
   migrate = Migrate(app, db)
   

3. Run the migration commands in your terminal:

   • flask db init: Creates the migrations directory. This is a one-time command for a new project.
   • flask db migrate -m "A short message describing the changes": Automatically generates a new migration script by comparing your current models to the state of the database.
   • flask db upgrade: Applies the latest migration script to update the database schema.

You should run migrate and upgrade every time you make a change to your models, such as adding a new column or table.

5. What’s the difference between SQLAlchemy and Flask-SQLAlchemy?

The main difference is that SQLAlchemy is a general-purpose SQL toolkit and ORM, while Flask-SQLAlchemy is a Flask-specific extension that makes it easier to use SQLAlchemy within a Flask application.

You can think of Flask-SQLAlchemy as a convenience layer. It handles the boilerplate code required to integrate SQLAlchemy into the Flask framework, letting you focus on defining your models and writing queries.

You built a small Flask web application for managing students using Flask and Flask-SQLAlchemy with an SQLite database. You learned how to connect to your database, set up database models that represent your tables, add items to your database, query your table, and modify database data. You’ve also explored how to handle schema changes with Flask-Migrate, examined performance considerations, and weighed the benefits and drawbacks of using an ORM.

Using SQLAlchemy in your application allows you to use Python classes and objects to manage your SQL database. Instead of SQLite, you can use another database engine, and other than the SQLALCHEMY_DATABASE_URI configuration responsible for the connection, you don’t need to change anything in your core application code. That allows you to move from one SQL database engine to another with minimal code change. See the Flask-SQLAlchemy documentation for more information.

If you would like to read more about Flask, check out the other tutorials:

• How To Build Web Applications with Flask
• How to Build a Flask Python Web Application from Scratch
• How to Optimize the Performance of a Flask Application
• Django vs Flask: Which Python Framework for API Development to Choose.