Connect Flask to a Database with Flask-SQLAlchemy
Flask is a micro web framework written in python. Micro-framework is normally a framework with little to no dependencies on external libraries. Though being a micro framework almost everything can be implemented using python libraries and other dependencies when and as required.
In this article, we will be building a Flask application that takes data in a form from the user and then displays it on another page on the website. We can also delete the data. We won’t focus on the front-end part rather we will be just coding the backend for the web application.
In any directory where you feel comfortable create a folder and open the command line in the directory. Create a python virtual environment using the command below.
python -m venv <name>
Once the command is done running activate the virtual environment using the command below.
Now, install Flask using pip(package installer for python). Simply run the command below.
pip install Flask
Once the installation is done create a file name app.py and open it in your favorite editor. To check whether Flask has been properly installed you can run the following code.
Setting Up SQLAlchemy
Now, let’s move on to creating a database for our application. For the purpose of this article, we will be using SQLAlchemy a database toolkit, and an ORM(Object Relational Mapper). We will be using pip again to install SQLAlchemy. The command is as follows,
pip install flask-sqlalchemy
In your app.py file import SQLAlchemy as shown in the below code. We also need to add a configuration setting to our application so that we can use SQLite database in our application. We also need to create an SQLAlchemy database instance which is as simple as creating an object.
In sqlalchemy we use classes to create our database structure. In our application, we will create a Profile table that will be responsible for holding the user’s id, first name, last name, and age.
The table below explains some of the keywords used in the model class.
|Column||used to create a new column in the database table|
|Integer||An integer data field|
|primary_key||If set to True for a field ensures that the field can be used to uniquely identify objects of the data table.|
|String||An string data field. String(<maximum length>)|
|unique||If set to True it ensures that every data in that field in unique.|
|nullable||If set to False it ensures that the data in the field cannot be null.|
|__repr__||Function used to represent objects of the data table.|
Creating the database
In the command line which is navigated to the project directory and virtual environment running, we need to run the following commands.
The above command will initiate a python bash in your command line where you can use further lines of code to create your data table according to your model class in your database.
from app import db db.create_all()
After the commands, the response would look like something in the picture and in your project directory you will notice a new file named ‘site.db’.
Making Migrations in database
Install Flask-Migrate using pip
pip install Flask-Migrate
Now, in your app.py add two lines, the code being as follows,
Now to create migrations we run the following commands one after the other.
flask db init
flask db migrate -m "Initial migration"
flask db upgrade
Now we have successfully created the data table in our database.
Creating the Index Page Of the Application
Before moving forward and building our form let’s create an index page for our website. The HTML file is always stored inside a folder in the parent directory of the application named ‘templates’. Inside the templates folder create a file named index.html and paste the below code for now. We will go back to adding more code into our index file as we move on.
In the app.py add a small function that will render an HTML page at a specific route specified in app.route.
To test whether everything is working fine you can run your application using the command
The command will set up a local server at http://localhost:5000.
Creating HTML page for form
We will be creating an HTML page in which our form will be rendered. Create an HTML file named add_profile in your templates folder. The HTML code is as follows. The important points in the code will be highlighted as you read on.
Adding a function in our application to render the form page
In our app.py file, we will add the following function. At route or site path ‘http://localhost:5000/add_data’ the page will be rendered.
To check whether the code is working fine or not, you can run the following command to start the local server.
Now, visit http://localhost:5000/add_data and you will be able to see the form.
Function to add data using the form to the database
To add data to the database we will be using the “POST” method. POST is used to send data to a server to create/update a resource. In flask where we specify our route that is app.route we can also specify the HTTP methods there. Then inside the function, we create variables to store data and use request objects to procure data from the form.
Note: The name used in the input tags in the HTML file has to be the same one that is being been used in this function,
<input type="number" name="age" placeholder="age..">
“age” should also be used in the python function as,
age = request.form.get("age")
Then we move on to create an object of the Profile class and store it in our database using database sessions.
Once the function is executed it redirects us back to the index page of the application.
Display data on Index Page
On our index page now, we will be displaying all the data that has been stored in our data table. We will be using ‘Profile.query.all()‘ to query all the objects of the Profile class and then use Jinja templating language to display it dynamically on our index HTML file.
Update your index file as follows. The delete function will be written later on in this article. For now, we will query all the data from the data table and display it on our home page.
We loop through every object in profiles that we pass down to our template in our index function and print all its data in a tabular form. The index function in our app.py is updated as follows.
Deleting data from our database
To delete data we have already used an anchor tag in our table and now we will just be associating a function with it.
The function queries data on the basis of id and then deletes it from our database.
The entire code for app.py, index.html, and add-profile.html has been given.