Flask Database Integration with SQLAlchemy
March 16, 2021
Databases are integral components of building web applications. Throughout the life-cycle of a web application, the user sends bits of data that are needed to be stored for future reference. Simultaneously, the user also requests information from where they are stored.
Data and information are facets that make web applications valuable. If the user can’t send or receive data from your web application, then it’s not providing value in its usage. Therefore, the information supplied by the user has to be stored in a database so it can be accessed later.
In Flask web applications, to manipulate databases, we can use SQL and Object Relational Mapping (ORM). An ORM makes writing SQL queries easier for a programmer, because it enables us to write queries in an object-oriented language, and then ORM automatically translates it to SQL and retrieves the result as an object.
The prerequisites for this article includes understanding of the following concepts:
- Basic usage and implementation of a Flask web app.
- Understanding of Object-Oriented Programming concepts with Python.
- Understanding of Flask views and templates.
- Intermediate knowledge with the use of terminal.
- Basic understanding of databases.
Table of contents
- Setting up the work environment
- Install and setup SQLAlchemy
- Database relationship
- One to many relationship
- Creating a model in Flask
- Storing user registration data with SQLAlchemy
- Adding user data to registration database
- Further reading
Setting up the work environment
Before we can work with the SQLAlchemy library, we need to have a Flask application running. To make the process easier, we will clone an existing Flask web application used in our previous tutorial which contains a registration form with all the routes setup.
We proceed to clone the starter pack file from this Github repository.
$ git clone https://github.com/corpsgeek/flask-form-handling.git
Change the directory to the cloned folder and install the packages in the
requirement.txt file using the command:
$ pip install -r requirement.txt
We also need to set up the flask environment variable to development, where the default state is the production environment.
$ export FLASK_ENV="DEVELOPMENT"
The above terminal command sets the flask environment as a development environment. Now, we can run our flask app using the following command:
$ flask run
Our flask web application should be live. Redirect to the host URL (127.0.0.1:5000) and you should be able to view the homepage displaying “Hello world”. Navigate to the registration route
127.0.0.1:5000/register to view the registration page.
Install and setup SQLAlchemy
To work with SQLAlchemy, we need to install the SQLAlchemy library first.
$ pip install SQLAlchemy
With SQLAlchemy installed, we have to import the SQLAlchemy class from the
flask_sqlalchemy module into the main application file (
__init__.py) of our project.
# import sqlaclhemy from the flask_sqlachemy module from flask_sqlalchemy import SQLAlchemy
Next, proceed to configure the variable to the database file. In this case, we are using the SQL database, therefore we make use of SQLite to run queries through our database.
# set the database uniform resource identifier for the database app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///registration.db'
In the code above, the SQLite uniform resource indicator will receive the name of the database, which in our case is
registration.db. With this setup, we have to initialize the database connection by creating an object of the SQLAlchemy class, then we pass our application as a parameter.
# create an instance of the database module db = SQLAlchemy(app)
By following the process above, we have our database initialized and ready to work with. But we can’t add data to this database because it contains no tables for storing the registration details.
However, to solve this problem we have to start working with database models for the application.
In the process of creating models, we find that some models do have some relationship to one another or with other data. For example, a user might have a specific ID, such that this specific ID also refers back to the user, but how do we tell the database about the existence of this symbiotic relationship?
To do this, we first need to understand the basic database relationship and how they are being initialized in our models.
In the process of working with databases, the following are the two types of relationship that you would frequently interact with when working with tables:
- One to Many
- One to One
In our understanding of the various database relationships, consider the creation of a Pet management system. The application will consist of the following models:
PetComment as shown below:
# pet handler class class PetHandler(db.Model): # create handler id column handler_id = db.Column(db.Integer, primary_key = True) # create handler name column handler_name = db.Column(db.String(150), nullable = False) # pet category class class PetCategory(db.Model): # create pet id column pet_id = db.Column(db.Integer, primary_key = True) # create pet name column pet_name = db.Column(db.String(100), nullable = False) # create pet category column pet_category = db.Column(db.String(50), nullable = False ) class PetComnment(db.Model): # create comment id column comment_id = db.Column(db.Integer, primary_key = True) # create comment content column comment_content = db.Column(db.String(200), nullable = False)
You might not understand the implementation of the code above, but focus on the concept of database relationship, we will get to the point of the components of creating a database.
From our pet management system as an analogy, an example of a one-to-many relationship is analyzed between a pet handler and a pet in the
PetCategory, such that a pet handler can be assigned to multiple (many) pets to care for.
The representation of this relationship in our model is by following this process:
We create a column containing a
ForeignKey() in the
PetCategory class. Simply, the pet handler is the attribute having multiple relationships with the pets, therefore the pets will have a column consisting of a
ForeignKey() to the pet handler’s unique identify key which is the pet handler’s id
# create pet category table class PetCategory(db.Model): # create pet id, name, category column pet_id = db.Column(db.Integer, primary_key = True) pet_name = db.Column(db.String(100), nullable = False) pet_category = db.Column(db.String(50), nullable = False ) # create a handler id column establishing a relationship with the foreign key class table handler_id = db.Column(db.Integer, db.ForeignKey('pethandler.handler_id'), nullable = False)
The last line of code, with the
handler_id variable name, initializes the existence of a one-to-many relationship from the pet handler to the pets. The
db.ForeignKey receives an argument pointing to the
handler_id column in the pethandler table.
Note that, the
pethandler class name has to be in lowercase which tells SQLAlchemy that we are referring to a table.
Now, the pets are aware of a relationship from the handlers, but the handler table isn’t yet aware of any relationship with any table.
To do this, we have to add a line of code to the
# create pet handler table class PetHandler(db.Model): # create pet handler id and handler name column handler_id = db.Column(db.Integer, primary_key = True) handler_name = db.Column(db.String(150), nullable = False) # initialize the relationship from the pet handler table with pet category table pet_category = db.relationship('PetCategory', backref='pet_handler', lazy = True)
PetHandler class, we wrote code to inform the handler that it has a relationship with the
PetCategory table. The
backref argument creates a
pet_handler virtual variable in the
PetCategory table, such that we can access the handler of the pet by using
Creating a model in flask
With a firm understanding of the two most common database relationships, we can proceed with the creation of models in our flask web app to store registration data of users.
To define a model, we can follow the template below:
class ModelTableName(db.Model): #table column unique id with a primary key data_column_uniqueid = db.Column(db.Integer, primary_key = True) #table column field with data type data_column_field = db.Column(db.ColumnDataType, nullable = False)
In the process of defining a model, a model must have a unique ID, which serves as its primary key and that’s the first thing you define in your model table. Next, you can proceed to define another data field for your table.
Also, in each column, you have to specify a data type like
Integer, or any other data types that fits the data being stored. It is also a good practice to set
False on data fields, if you know they shouldn’t be empty.
Implementation of the above template should be as follows:
class UserDetails(db.Model): # table column id user_id = db.Column(db.Integer, primary_key = True) # table column name with data type of String user_name = db.Column(db.String, nullable = False)
Storing user registration data With SQLAlchemy
Having a better understanding of the SQLAlchemy implementation, let’s delve into some practical application through our cloned flask web app. Now, let’s navigate to the registration route in our browser
Our registration page contains three input data fields for the username, email, and user password. For the scope of this article, we won’t be delving into password encryption. Instead we will be storing our password in a plain format (although this is not a good practice).
Creating user registration model
app folder, create a new file and name it
models.py. In our models file, we define the models for the user registration form.
In our model, we start by importing the db object we initialized in our app
# import the db object from the flask app from app import db
Next let’s create the User model to handle the registration form data for the username, email, and password.
# create user table with required field class User(db.Model): id = db.Column(db.Integer, primary_key =True) username = db.Column(db.String(100), nullable = False) user_email = db.Column(db.String(100), nullable= False) user_password = db.Column(db.String(150), nullable = False)
In the model declaration, you can observe that the arguments passed to the
String data type contains a required length. For
db.String(100), it is expected that the string must have its length more than 100.
Next, in our
__init__.py file, beneath the database object initialization, we import the models file. The reason for this is that when we create our database, flask will not be able to access the database models in our
models.py file. Unless we draw it back into the app initialization file for the database object to be aware of its existence.
# import the models module from app import models
At this point, your
__init__.py file should look like this:
# import flask and sqlaclhemy from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) # app configuration app.config["SECRET_KEY"] = '571ebf8e13ca209536c29be68d435c00' app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///registration.db' db = SQLAlchemy(app) # import views/routes and models from app import views from app import models
models.py file should look exactly like this:
# import db object from flask app from app import db # database tables class User(db.Model): id = db.Column(db.Integer, primary_key =True) username = db.Column(db.String(100), nullable = False) user_email = db.Column(db.String(100), nullable= False) user_password = db.Column(db.String(150), nullable = False)
We have the data models created, let’s now create this database. To do this, we need to open our terminal to our working directory and follow this process:
Initialize the Python interpreter.
Next, import the database variable from the
__init__.py file where we initialized the SQLAlchemy instance of the object, then create the database.
# import db object and create all tables in the database from the models file $ >>> from app import db $ >>> db.create_all()
Remember that the database variable is the variable name we used in our
__init__.py, if you intend to use a different variable, then you have to call that variable name and not the database variable created here.
To confirm our database is created, check your app folder, you should see a new file titled
registration.db, which is the
db name we specified in our
Next, we can import the
User table into our models file using this command:
# import the user table from the models $ >>> from app.models import User
To ensure the functionality of the database is intact, let’s create a dummy user from the Python interpreter.
# create an instance of a user with required arguments from the database table $ >>> user1 = User(username = "Demo User", user_email = "email@example.com", user_password = "demouserpassword")
In the command above, we specify the data for each variable as defined in our
User model, and then hit the
ENTER key. Next, we have to add a new user to the database, then commit the changes.
# add the user instance to the database $ >>> db.session.add(user1)
Next, we commit the changes made to our database.
# commit the changes to the database $ >>> db.session.commit()
To confirm if our database works, we query the database and it should return an object containing the new user details using the following command:
# query all data from the User table $ >>> User.query.all()
Note that, we are querying the
Now, we can add data manually to the registration database, how do we add data to the database directly via the frontend?
Adding user data to registration database
In our view, we start by importing the
User model and the
db object from our app.
# import db object and User table from app import db from app.models import User
In our registration route logic, we create a user object and store our fetched data from the previous tutorial in the User models column.
@app.route("/register", methods=["GET", "POST"]) def register(): # check the request method to ensure the handling of POST request only if request.method == "POST": # store the form value user_name = request.form["username"] email = request.form["email"] password = request.form["password"] # create an instance of the user table user = User(username = user_name, user_email = email, user_password = password) return username + " <br/> " + email return render_template('register.html')
Next, we add the user and commit the changes to the database and we replace the return statement with “User registration successful”.
By the end of the implementation, your registration route should like this:
@app.route("/register", methods=["GET", "POST"]) def register(): #check the request method to ensure the handling of POST request only if request.method == "POST": #store the form value user_name = request.form["username"] email = request.form["email"] password = request.form["password"] # store the user details in the user database table user = User(username = user_name, user_email = email, user_password = password) # add the user object to the database db.session.add(user) # commit changes to the database db.session.commit() return 'User registration successful' return render_template('register.html')
Test running our app
Let’s test run our app. If you have the server running, terminate and restart. Now, you should have your server running, then proceed to the registration page on your web app.
Let’s fill in the database with this dummy data:
We received the registration successful message, but let’s check the database to check if our user data is stored.
# import db and User table $ >>> from app import db $ >>> from app.models import User # query User table $ >>> User.query.all() $ [<User 1>, <User 2>]
When you run the following command in sequential order, you should get a response indicating the new user was added. In our case, we have already added an user manually, and due to the registration, we now have a new user.
Let’s dive deeper and run a new command to query the User table by the username of the registered user.
$ >>> user_data = User.query.all() # store queried data in the user_data variable $ >>> for user in user_data: # loop through the user data print(user.username)
If you run these code snippets in a sequential order, you should get the username data of all the registered users in the database.
$ Demo user $ Peter
This article has covered the usage of SQLAlchemy while building Flask web applications, storing data, accessing stored data through the Python interpreter, and database modeling.
We also covered the types of database relationships that could exist in building database models.
As a practice test to improve your skills, try creating a contact form in your flask web app, and then store the content in your custom database.
You can find the codebase for this article here.
Further Reading and References
I recommend you look up on how to implement a Many-to-Many database relationship here.
Peer Review Contributions by: Srishilesh P S