Flask SQLAlchemy explained with examples

What is Flask SQLAlchemy

  • Flask SQLAlchemy is an extension for flask that adds support for SQLAlchemy to flask by adding useful defaults and helpers that simplify basic tasks.
  • SQLAlchemy is an  object relational mapper(ORM) for python.
  • The function of an ORM is to convert normal objects (classes) to SQL tables that can be saved in an SQL database, and it saves each instance of that object under the corresponding table, so an ORM acts an abstraction layer over a database .
  • ORMs make the development of a software product easier as they handle all SQL commands and schemas allowing the developers to focus on creating their actual app.

In this article we are going to walk you through the process of using Flask SQLAlchemy in your flask app.

As an example for this article, we are going to create a table for football teams and another table for football players, and then we are going to link each player in the players' table to a team in the teams' table.

Advertisement

SQLAlchemy can be used with all all SQL databases, but in this article we are going to use sqlite3.

 

Lab environment

  • OS: Ubuntu 20.04
  • Code editor: VS code
  • Python version: 3.8.10
  • sqlite3 version: 3.31.1
  • Flask SQLAlchemy version: 2.5.1
  • Flask version: 2.0.1

 

Installing Flask SQLAlchemy

Before installing flask sqlalchemy, we need to create a virtual environment(venv) to keep different versions of packages separated, in a Linux or a Unix OS use this command to create a virtual environment called my-venv in your current directory:.

I will create a separate directory to store the scripts and files from this tutorial and then create virtual environment inside this directory:

deepak@ubuntu:~$ mkdir flask_sqlalchemy
deepak@ubuntu:~$ cd flask_sqlalchemy/

deepak@ubuntu:~/flask_sqlalchemy$ python3 -m venv my-venv

This command will create a directory called my-venv, it will contains everything related to the virtual environment including the activation script of the venv which can be ran using the following command:

deepak@ubuntu:~/flask_sqlalchemy$ source ./my-venv/bin/activate

Once the virtual environment is active, you can start installing your dependencies:

(my-venv) deepak@ubuntu:~/flask_sqlalchemy$ pip3 install Flask Flask-SQLAlchemy
NOTE:

To exit a venv, use the deactivate command.

 

Advertisement

Creating an SQL table using flask sqlalchemy

Let's start by creating our python file, we will call it app,py, you can call it whatever you want. Open app.py in your favourite text editor or IDE and write the following code:

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///mydb.db"

db = SQLAlchemy(app)
  • In the above code we imported the Flask class and created an instance of it called app which is our application.
  • Secondly, we specified the kind of the database which is sqlite3 and the path to the database that the app going to use, in our case the database is in the same directory as our app and it's called mydb.db.
  • Thirdly, we imported the SQLAlchemy object which allows us to communicate with an SQL database via sqlalchemy and then created an instance of it and then passed our application to it.

Now we are ready to create the team's table:

class Team(db.Model): 
    team_id = db.Column(db.Integer, primary_key=True) 
    name = db.Column(db.String(50), unique=True, nullable=False)

As seen above, we created a normal python object that inherits from another object db.Model, the db.Model object gives our normal python object the ability to be converted to an SQL table. Then we created columns for the table, the first column is the id of the team. The id in an SQL database is a unique identifier generated automatically by the database, it normally a value that is incremented with each instance saved under a table, for example, the first team will have an id of 1 and the second team 2 etc, so we don't need to add team_id for each instance manually, it will be assigned automatically to the instance by SQLAlchemy. Then we created the name column which will hold the name of the team, we specified the maximum size of the team's name to be 50 characters, you can use any size you want.

 

Initialize SQLite3 Database

Now we need to initialize our database, this can be done in two ways, either using the python interactive shell or via the flask app directly, let's cover the python interactive method first.

 

Method-1: Using the python interactive shell

Before we are able to create any teams we need to create out database, this can be done in the python interactive shell, open a terminal and enter the python3 command to open a python interactive shell, once you enter the command, your terminal should look something like this:

(my-venv) deepak@ubuntu:~/flask_sqlalchemy$ python3
Python 3.6.9 (default, Jan 26 2021, 15:33:00)
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

Now you are ready to initialize your database, once it is initialized, you can add teams to the database to initialize the database enter the following command:

>>> from app import db
>>> db.create_all()

In the above code snippet, we imported our database from our app and then used the create_all() method to create the Team's table. This is what the python interactive shell is mainly used for, whenever we add a new table we open the interactive shell and run the db.create_all() method for the new tables to be added. Now we are ready to add teams to the database.

To add teams via the interactive shell follow the following steps:

Advertisement
>>> from app import Team
>>> team = Team(name="real madrid")
>>> team2 = Team(name="barcelona")
>>> db.session.add(team)
>>> db.session.add(team2)

As seen above, adding data to the database is as easy as a creating an instance of an existing python object, then use the add() method in the session object to add data to the database. but they are not saved yet, to save the created instances to our sqlite3 database, we need to commit the changes by entering one more command:

>>> db.session.commit()

The above command will save the  instances to our database. Now let's see how we can see all the teams saved in the database.

>>> Team.query.all()
[<Team 1>, <Team 2>]
>>> list(map(lambda x: x.name, Team.query.all()))
['real madrid', 'barcelona']
>>> Team.query.filter_by(team_id=1).first()
<Team 1>
>>> Team.query.filter_by(team_id=1).first().name
'real madrid'

The query.all() method returns a list of all the teams that are saved under the Team table in the sqlite3 database, we can then access the id or name field of each team in this list. In the second python expression we used the map function, for those who do not know how the map function works, this expression is equivalent to a for loop that iterates over the list of teams returned from the Team.query.all() method and then accesses the name field of each team using the '.' operator. The third and fourth expressions are similar, both of them makes use of the

Team.query.filter_by() method that filters the results and returns a list of items that fits the criteria of the filter, then we they the first() method to get the first item of that list only.

 

Odds are you do not want to enter the python interactive shell whenever you want to add a new entry and add it manually,  that's why we will exit the shell using the  exit()method and see how we can manage the database from the flask app directly and automate the process of adding new entry to the database.

 

Method-2: Managing the database via the flask app

Now we will see how we can do what we just did in the python interactive shell via the flask app directly, the two ways are not very different so this part should be easy. Open your flask app in your favorite text editor or IDE and then create instances of the Team object that we created above by and then add them to the database using the db.session.add()method and finally commit the changes using the db.session.commit() method, here is an example:

db.create_all()

team = Team(name="Liverpool")
db.session.add(team)
db.session.commit()

Here is the complete code of app.py at this stage:

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///mydb.db"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Team(db.Model):
    team_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)

db.create_all()
team = Team(name="Liverpool")
db.session.add(team)
db.session.commit()

The above code does exactly what we did in the interactive shell, the only difference is in the name of the team that we added.

Advertisement

But it is probably not a good idea to run the db.create_all() command every time you run your app in case of that there is a table do not want to add a table to the database yet. So it is the flask convention to run the db.create_all() command via the python interactive shell and add instances and commit the changes via the flask app.

Check the list of Team members now using interactive python shell:

(my-venv) deepak@ubuntu:~/flask_sqlalchemy$ python3
Python 3.6.9 (default, Jan 26 2021, 15:33:00)
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import db
>>> from app import Team
>>> list(map(lambda x: x.name, Team.query.all()))
['real madrid', 'barcelona', 'Liverpool']

Now let's create the players table and make sure to follow all the correct conventions.

 

Select, Insert and Delete records

Edit records

Editing a row's information in SQL using SQLAlchemy is straightforward, it is like changing the value of any python variable and then committing the changes to the database. Here is an example of updating a row in our Team table via the flask app.

>>> team = Team.query.filter_by(name="real madrid").first()
>>> team.name = "arsenal"
>>> db.session.commit()

 

Query records

Now if we run our app and then delete that piece of code (I will explain why later), the name of the team will be changed and we can test this by opening the shell and running this python expression:

>>> list(map(lambda x: x.name, Team.query.all()))
['arsenal', 'barcelona', 'Liverpool']

We removed the piece of code that changes the name after the running the app because when we enter the python interpreter(shell) and import the Team model from our app, the python module will be ran again and if we don't remove the piece of code that looks for team named 'real madrid' and changes it to 'arsenal', an error will be raised as there will be no team named 'real madrid' because we changed it before.

 

Delete records

We can delete posts using the db.sesssion.delete()method, here is an example of how it is used. Open you flask app and add this bit of code to delete a team named 'barcelona' from your database.

>>> team = Team.query.filter_by(name="barcelona").first()
>>> db.session.delete(team)
>>> db.session.commit()
>>> list(map(lambda x: x.name, Team.query.all()))
['arsenal', 'Liverpool']

Once you run the above code snippet to your app and run the app, the team named 'barcelona' will be deleted from the teams table. Make sure to remove that piece of code after running the app once.

 

Declaring Models

The players' table is going to contain 5 columns,

  • the first column will hold the SQL id of the player,
  • the second column will contain the name of the player,
  • the third column will contain the number of the jersey of the player,
  • the fourth column will contain the id of the team that the players belongs to,
  • and fifth and last field will enable the members of the Team table to access its members in the Player .

Let's create it.

class Player(db.Model):
	player_id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(50), nullable=False)
	jersey_num = db.Column(db.Integer, nullable=False)
	team_id = db.Column(db.Integer, db.ForeignKey("team.team_id"), nullable=False)
	team = db.relationship("Team", backref=db.backref("players", lazy=True))

After creating the Player object we then need to save the python file, then enter the python interactive shell, and run the following commands:

>>> from app import db
>>> db.create_all()

Now we are ready to create players and save them under our Player SQL table, let's create a player in the python interactive shell.

>>> from app import Team, Player, db
>>> team = Team.query.filter_by(team_id=1).first()
>>> player = Player(name="messi", jersey_num=10, team=team)
>>> db.session.add(player)
>>> db.session.commit()

As seen above, we imported the Team and Player model and our SQLAlchemy instance named db. Then we grabbed the first team in the teams table, then we used that team to create the player. Notice that we did not supply the player_id or the team_id that are required in the Player manually, they were added automatically to our instance by SQLAlchemy.

Now let's see how we can create a web interface with flask to fill our database.

 

Creating a web interface for the database

In the final part of this article we are going to create a simple web interface to add data to the database. CSS is not the main focus of this article, so we are not going to design this web interface, however, you can add all the CSS that you want.

 

Adding teams

Append the following code in your app.py:

@app.route("/team", methods=["GET", "POST"])
def create_team():
	if request.method.upper() == "POST":
		name = request.form.get("name")
                if name.strip() == "":
                    return render_template("create_team.html")
		team = Team(name=name)
		
		db.session.add(team)
		db.session.commit()

		return redirect(url_for("main"))
	
	return render_template("create_team.html")

This endpoint renders a form that consists of one field that is the name of the team that the user would like to create for the user to fill and on submitting the form, the team is created with the chosen name and saved to the database and the user is returned to the main page that displays the teams and the players.

If the form is empty, the form is reloaded for the user to fill.

 

Adding data

Append the following section in app.py to add more players to the team:

@app.route("/player", methods=["GET", "POST"])
def create_player():
	if request.method.upper() == "POST":
		name = request.form.get("name")
		jersey_num = str(request.form.get("jersey_num"))
		team = Team.query.filter_by(name=request.form.get("team")).first()

		if team == None or name.strip() == "" or jersey_num.strip() == "":
			return render_template("create_player.html")
		
		player = Player(name=name, jersey_num=jersey_num, team=team)

		db.session.add(player)
		db.session.commit()
		
		print("created player")

		return redirect(url_for("main"))
	
	return render_template("create_player.html")

This is similar to adding teams, the difference is that the form that the user fills consists of the name of the player, the jersey number and the name of the team that the player belongs to, and if the user enters the name of a team that does not exist or one of the fields of the form are empty, we reload the form for the user to refill.

 

Displaying data

In the below code snippet, we create a flask endpoint that fetches all the teams from the database and passes them to the jinja2 template engine to be rendered to the user.

@app.route("/")
def main():
	teams = Team.query.all()

	return render_template("main.html", teams=teams)

 

Full code for flask web app

Here is the full code for reference which we have used throughout this tutorial:

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

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///mydb.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class Team(db.Model):
	team_id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(50), unique=True, nullable=False)


class Player(db.Model):
	player_id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(50), nullable=False)
	jersey_num = db.Column(db.Integer, nullable=False)
	team_id = db.Column(db.Integer, db.ForeignKey("team.team_id"), nullable=False)
	team = db.relationship("Team", backref=db.backref("players", lazy=True))

@app.route("/")
def main():
	teams = Team.query.all()

	return render_template("main.html", teams=teams)

@app.route("/team", methods=["GET", "POST"])
def create_team():
	if request.method.upper() == "POST":
		name = request.form.get("name")
                if name.strip() == "":
                    return render_template("create_team.html")
		team = Team(name=name)
		
		db.session.add(team)
		db.session.commit()

		return redirect(url_for("main"))
	
	return render_template("create_team.html")

@app.route("/player", methods=["GET", "POST"])
def create_player():
        if request.method.upper() == "POST":
                name = request.form.get("name")
                jersey_num = str(request.form.get("jersey_num"))
                team = Team.query.filter_by(name=request.form.get("team")).first()

                if team == None or name.strip() == "" or jersey_num.strip() == "":
                        return render_template("create_player.html")

                player = Player(name=name, jersey_num=jersey_num, team=team)

                db.session.add(player)
                db.session.commit()

                print("created player")

                return redirect(url_for("main"))

        return render_template("create_player.html")

if __name__ == "__main__":
        app.run(debug=True)

 

Flask Templates

Here are the templates used in the flask app (They are located in the templates/directory).

This ~/flask_sqlalchemy/templates/create_team.html template will be used to create a Team:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <form method="POST">
        <input placeholder="Team's name" name="name">
        <button type="submit"> save </button>
    </form>
</body>
</html>

 

You can visit http://127.0.0.1:5000/team which can be used to create your Team. I will go ahead and create India team.

Flask SQLAlchemy explained with examples

 

Following ~/flask_sqlalchemy/templates/create_player.html template file will be used to create a Player and add to a Team.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <form method="POST">
        <input placeholder="Player's name" name="name">
        <input placeholder="Jersey N." name="jersey_num">
        <input placeholder="Team's name" name="team">
        <button type="submit"> save </button>
    </form>
</body>
</html>

 

Visit http://127.0.0.1/player to create a new player and assign to your Team.

Flask SQLAlchemy explained with examples

 

This is our ~/flask_sqlalchemy/templates/main.html which will act as a home page which will list all the Teams and Player details.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    {% for team in teams %}
        <hr>
        <ul> <h3>{{ team.name }}</h3>
            {% for player in team.players %}
                <li> {{ player.name }} </li>
            {% endfor %}
        </ul>
    {% endfor %}
</body>
</html>

 

Once you create your Team or Player, you will be redirected to this page or you can directly access 127.0.0.1:5000/

Flask SQLAlchemy explained with examples

 

Summary

In this article we used Flask SQLAlchemy ORM to communicate with a sqlite3 database directly from python. We created python objects and Flask SQLAlchemy converted them to SQL tables. Firstly, we created a table for football teams called Team, the table contains two columns one column for the SQL id and the other is for the team's name. Secondly, we created a table fro football players called Player , the player's table consists of 5 columns, the player's SQL id, the player's name, the player's jersey number, the SQL id that the player belongs to and a final fifth  relationship column that allows a team to access the players that belong to it.

 

Sources

Flask-SQLAlchemy

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment