Flask SQLAlchemy Tutorial

Flask SQLAlchemy is a highly versatile and dynamic database toolkit designed specifically for Flask. It provides:

• An easy-to-use interface for working with databases in Flask applications.
• Leveraging the strengths of SQLAlchemy.
• A popular Object-Relational Mapping (ORM) library for Python.

What is an Object Relational Mapping (ORM)?

ORM is a technique that maps database tables and their associations to programming language objects, allowing developers to work with a relational database in an object-oriented manner without writing SQL queries.

Flask SQLAlchemy enables developers to perform tasks like defining models, creating queries, and easily managing database migrations and supports multiple database management systems such as SQLite, MySQL, and PostgreSQL.

Let’s learn how to set up and use Flask SQLAlchemy with graspable examples.

Setting up the development environment

Before building our Flask application, we must set up our development environment. First, we should install Flask SQLAlchemy on our system. To install, we can use the command below.

$ pip install flask-sqlalchemy

Next, we should install a suitable database management system. Here we are using SQLite; running the below command will install it.

$ sudo apt-get install sqlite3

After installing SQLite, we can create a database using the below command.

$ sqlite3 mydb.db

Here, ‘mydb’ is the database name, and we can give any name to it. ‘mydb.db’ file has been created in the current directory, which is used as the database by the SQLite engine.

Using the ‘.databases’ command, we can see the created database.

sqlite3> .databases

After setting up our development environment, it’s time to create the Flask application.

Creating the Flask application

Firstly, we have to build a new Flask application instance. We can do this by creating a new Python file and importing the Flask class, as shown below.

from flask import Flask

app = Flask(__name__)

Here we have created the Python file called app.py, and inside it, we have imported the flask library and stored it in the “app” variable. Also, we set the name of our module for the ‘name’ variable. This is mandatory so Flask knows where to find our app’s resources (templates and static files).

Now let’s define a route using the @app.route decorator. This will specify the URL path that will trigger the associated function.

@app.route('/')
def helloworld():
    return 'Hello, World!'

Running the Flask application

Our next step is to invoke the Flask application. Below code snippet can be added to our Python file.

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

If we run the Python file, we can see an output as below.

* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

Now our Flask application is up and running and waiting for incoming requests on port 5000. To see the application in action, navigate to “http://127.0.0.1:5000/” in the web browser and see the output of the function, which displays the “Hello, World!” message.

Creating the Database with SQLAlchemy

Now let’s create our database with SQLAlchemy. Before creating, we have to configure our database.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydb.db'
db = SQLAlchemy(app)

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

Here, from the “flask_sqlalchemy” module, we are importing the “SQLAlchemy” class. Then we are creating a new instance and store it in the “db” variable. Also, we are setting the “SQLALCHEMY_DATABASE_URI” configuration variable to be the URI of our SQLite database, and then our Flask application knows which database it should use. The “sqlite:///” prefix tells SQLAlchemy to use SQLite, and the “test.db” part specifies the name of our database file. Here is the code.

We have successfully configured our database, and our next task is defining our data models. Typically, these data models are Python classes representing tables in our database.

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

    def __repr__(self):
        return '<User %r>' % self.username

This example defines a “User” class that inherits from the “db.Model” class. For our table, we are defining three columns: “id”, “username”, and “email”. The primary key of the table is the “id” column, and the “username” and “email” columns are unique and mandatory. We also define a “repr” method, which returns a string representation of the object.

Migrating the database

After configuring the database, we have to migrate it. Migrations refer to changes made to the database schema over time. Using migrations, we can keep track of the database changes and apply them to the production environment when needed. Flask-Migrate is an extension that Flask provides and helps us manage database migrations.

To use Flask-Migrate, we can use the pip command to install it.

$ pip install Flask-Migrate

Then we need to initialize the database by using the below command.

$ flask db init

Then we can create the migration as follows.

$ flask db migrate -m "Added email column to User model"

When we run the above command, a new migration script will be generated in the “migrations/versions” directory with a filename, including a timestamp and the message we provided. There are two functions: upgrade() and downgrade().

• upgrade() function – responsible for applying the changes to the database schema.
• downgrade() function – reverses the changes made by the upgrade().

Now we can apply our changes by using the below command.

$ flask db upgrade

This command will apply all pending migrations to the database, including the migration we created.

Creating Routes for User Data

After migrating the database, we can start building the routes for user data. Routes are the URLs that our application responds to. We define routes using the @app.route() decorator in a Flask application. Let’s say we have to build a route that displays all users in our database.

@app.route('/users')
def users():
    users = User.query.all()
    return render_template('users.html', users=users)

Here, we use the User.query.all() method to retrieve all the users from the database and then pass the users variable to the render_template() function. This function will render the users.html template with the users variable available in the template to see the desired data on a web browser. It is important to ensure that templates are located in the templates directory, which should be created manually if it doesn’t exist since Flask searches for templates in this directory by default.

Now it’s time to practice these steps with a real-world example. Here we are taking a scenario where a user can CREATE, READ, UPDATE, and DELETE (CRUD) a student’s first name, last name, and subject.

Assuming we have prepared our development environment, let’s create our ‘studentList.db’ database. Here we set up the database through a separate Python file called databaseSetup.py file.

import sys
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()

class Student(Base):
   __tablename__ = 'student'

   id = Column(Integer, primary_key=True)
   fname = Column(String(250), nullable=False)
   lname = Column(String(250), nullable=False)
   subject = Column(String(250))

engine = create_engine('sqlite:///studentList.db')
Base.metadata.create_all(engine)

To create the database for the student list, we first import necessary modules from SQLAlchemy, such as Column, ForeignKey, Integer, and String, to define the column types for the “student” table. We use declarative_base to create a base class for declarative class definitions and an engine for connecting to the database using create_engine.

Next, we create a declarative base instance called Base and define the Student class as a declarative class that inherits from Base and represents the “student” table. The class defines the table name as “student” using the tablename attribute, with columns such as id, fname, lname, and subject. Finally, we create an engine instance using create_engine, which uses an SQLite database called “studentList.db”. The metadata is then created using the Base.metadata.create_all(engine) method, which creates the “student” table in the database. When we run the databaseSetup.py file, it will create an empty database called studentList.db.

Then we create another Python file called populateDatabase.py file to populate our database.

To ensure that a database has useful data, it’s necessary to populate it with some initial data, especially when creating a new database or resetting an existing one. Without any data, the database would be empty and serve no purpose. In our example, we created a database called “studentList.db” and populated the “student” table using the populate.py script we wrote earlier. This script inserts data from a CSV file into the database, making it possible to quickly and easily add a large amount of data without manual entry.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from database_setup import Student, Base

engine = create_engine('sqlite:///studentList.db')
Base.metadata.bind = engine

DBSession = sessionmaker(bind=engine)
session = DBSession()

Here we import the required modules from SQLAlchemy, including create_engine for establishing a connection to the database, sessionmaker for creating a session factory, and Base from database_setup for constructing the database schema. Then, an engine is created that connects to the database file (‘sqlite:///studentList.db’) and binds it to the Base metadata, indicating that the Base class is the blueprint for the database structure to be used. Following that, a DBSession class is created utilizing sessionmaker and linked to the engine to instantiate sessions, which are utilized to interact with the database. Finally, a session object is created utilizing the DBSession class to add, update, and delete objects in the database.

Now it’s time to build up our HTML files to display the content on a web browser.

from flask import Flask, render_template, request, redirect, url_for
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from database_setup import Base, Student

app=Flask(__name__)

engine = create_engine('sqlite:///studentList.db')

Base.metadata.bind = engine

DBSession = sessionmaker(bind=engine)
session = DBSession()

@app.route('/')
@app.route('/student')
def showStudents():
   student = session.query(Student).all()
   return render_template("student.html", student=student)

#Function to add a student
@app.route('/student/new/',methods=['GET','POST'])
def newStudent():
   if request.method == 'POST':
       newStudent = Student(fname = request.form['fname'], lname = request.form['lname'], subject = request.form['subject'])
       session.add(newStudent)
       session.commit()
       return redirect(url_for('showStudents'))
   else:
       return render_template('newStudent.html')

#Function to edit a student
@app.route("/student/<int:student_id>/edit/", methods = ['GET', 'POST'])
def editStudent(student_id):
   editedStudent = session.query(Student).filter_by(id=student_id).one()
   if request.method == 'POST':
       if request.form['subject']:
           editedStudent.subject = request.form['subject']
           return redirect(url_for('showStudents'))
   else:
       return render_template('editStudent.html', student = editedStudent)

#Function to delete a student
@app.route('/student/<int:student_id>/delete/', methods = ['GET','POST'])
def deleteStudent(student_id):
   studentToDelete = session.query(Student).filter_by(id=student_id).one()
   if request.method == 'POST':
       session.delete(studentToDelete)
       session.commit()
       return redirect(url_for('showStudents', student_id=student_id))
   else:
       return render_template('deleteStudent.html',student = studentToDelete)

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

Let’s begin the work by importing Flask, SQLAlchemy, and render_template packages. Then we create an instance of Flask and set the engine to our database. We then set up routes for the web application to display, add, edit, and delete students from the database using POST and GET requests.

• The ‘/’ and ‘/student’ routes display all students in the database by querying the Student table with the session.
• The ‘/student/new/’ route allows adding a new student to the database by accepting a POST request and creating a new Student object with the values from the form.
• The ‘/student/int:student_id/edit/’ route updates the subject attribute of a student with a POST request.
• The ‘/student/int:student_id/delete/’ route removes a student from the database via a POST request after confirming deletion with a GET request.

Now it’s time to build up our HTML files to display the content on a web browser.

student.html

<html>
    <body>
        <h1>Students</h1>
        <a href="{{url_for('newStudent')}}">
            <button>Add Student</button>
        </a>
        <ol>
            {% for student in student %}
            <li> {{student.fname}} {{student.lname}} / Subject: {{student.subject}}</li>
            <a href="{{url_for('editStudent', student_id = student.id )}}">
                Edit
            </a>
            <a href="{{url_for('deleteStudent', student_id = student.id )}}" style="margin-left: 10px;">
                Delete
            </a>
            <br> <br>
            {% endfor %}
        </ol>
    </body>
</html>

newStudent.html

<html>
    <body>
        <h1>Add a Student</h1>
        <form action="#" method="post">
        <div class="form-group">
            <label for="fname">First Name:</label>
            <input type="text" maxlength="100" name="fname" placeholder="First Name">

            <label for="lname">Last Name:</label>
            <input maxlength="100" name="lname" placeholder="Last Name">

            <label for="subject">Subject:</label>
            <input maxlength="100" name="subject" placeholder="Subject">

            <button type="submit">Create</button>
        </div>
        </form>
    </body>
</html>

editStudent.html

<html>
    <body>
        <h1>Edit a Student</h1>
        <form action="{{ url_for('editStudent',student_id = student.id)}}" method="post">
            <div class="form-group">
                <label for="subject">Subject:</label>
                <input type="text" class="form-control" name="subject" value="{{student.subject }}">
                <button type="submit"> SAVE</button>
                <a href='{{url_for('showStudents')}}'>
                    <button>Cancel</button>
                </a>
            </div>
         </form>
    </body>
</html>

deleteStudent.html

<html>
    <body>
        <h1>Delete Student</h1>
        <p>Are you sure you want to delete the following student?</p>
        <p>First Name: {{student.fname}}</p>
        <p>Last Name: {{student.lname}}</p>
        <p>Subject: {{student.subject}}</p>
        <form action="{{ url_for('deleteStudent', student_id=student.id) }}" method="post">
            <button type="submit">Delete</button>
            <a href='{{ url_for('showStudents') }}'>
                <button>Cancel</button>
            </a>
        </form>
    </body>
</html>

Now we can run the app.py file and see the results. When we run the app.py file, we see some output below.

* Running on http://127.0.0.1:4996

* Running on http://10.0.2.15:4996

We can access our Flask web application through any of these two addresses.

Conclusion

Flask SQLAlchemy is a powerful tool for creating web applications with relational databases. In this tutorial, we learned how to set up a development environment, create a Flask application, and use SQLAlchemy to create and manage databases. We also covered migrating the database, creating user data routes, and an example where we added, updated, and deleted students’ information by applying what we learned. Overall, Flask SQLAlchemy provides a flexible and robust way to work with relational databases in a Flask application.