Junaid

Posted on Jul 28, 2021 • Updated on Aug 28, 2021

Creating a CRUD App using Flask and SqlAlchemy

#python #postgres #database #orm

Flask is a python library that lets you create dynamic webapps using python.

Flask has great powers that we will be looking into in a minute.

In this tutorial we will be creating a simple Todo App using flask along with sqlite using our ORM i.e flask_sqlalchemy and jinja2 for using great powers inside of the html.

ORM means object relational mapping meaning that you get to interact with the database using your language of choice instead of using SQL queries .

ORM also comes helpful in a way that if you in the future would have to change your database for the app you do not have to write the whole schema for the database again .

you just have to change the database url and its done (easy -peasy).

OK , let's get started with the code.

First of all what you need to do is Create a folder with your app name
e.g FlaskTodoApp

inside of the folder create a new folder called venv

We are going to create a virtual environment for the flask to run inside of.
open up your project in vscode and go the terminal
Type

python3 -m venv venv

then type for mac users

. venv/bin/active.fish

For windows users type

venv/bin/active

and your environment will be created .
Now create two more folders inside of the project but outside of the venv folder namely templates and static

Template is where all the templates i.e html css files will be saved
and if you have any static file like an image or anything that will be saved inside of the static folder.
Your folder structure should look like this

now create a file namely app.py and type this code inside of that:-

from flask import Flask

app = Flask(__name__)

@app.route("/")
def hello_world():   
    return "<p>Hello world from flask</p>"

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

If you look closely there is a lot going on here

first the @app.route is used to declare a route for the webpage ("/" means the index page i.e abc.com)
Then we are telling the app to run in debug mode(until we create the app fully then we can go to production , don't push this to production).

You can test this app by going to the terminal and typing
python app.py

and it will show you where it is running e.g http://127.0.0.1:5000/ is the default .
Head over to there and see magic 🥳.

Great so we have just made a simple webpage which shows a p tag inside .
But what if we want to display a whole webpage with some cool images , text , headings and all that .
Thats where the built in render_template of flask comes into play.

create a file inside of your templates e.g home.html
write whatever html you would want to show to the user
Head back over to your app.py
Inside of that file the code will look like this now

from flask import Flask, render_template

app = Flask(__name__)

@app.route("/")
def hello_world():   
    return render_template('home.html')

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

Now head over to the url again and refresh the page and see the magic happening

If your webpage is working properly thats amazing ,
Congratulations to you 🥳🥳.
First part of the app is done .
Now for the database part we will be using sqlite for the database and flasksqlalchemy as the ORM as already discussed and for the data to show to the user we will use Jinja2 for that.

So let's get started.

First install flask_sqlalchemy by
pip install flask_sqlalchemy
and it should be installed for you.

Now the boiler plate for creating a database connection is as follows

from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///todo.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db=SQLAlchemy(app)

type this code in the same app.py file of yours.

now for creating a table in the database we need to use classes in python to create a model like this

class Todo(db.Model):
    sno=db.Column(db.Integer,primary_key=True)
    title=db.Column(db.String(200),nullable=False)
    description = db.Column(db.String(200), nullable=False)
    def __repr__(self) :
        return "{} is the title and {} is the description".format(self.title,self.description)

Here you can see we are creating a class for the todo table that has 3 columns:-

S.no this will act as a primary key and will be auto generated by the database
Title of the todo
Description of the todo.

Great , now let's create the database for the first time.

Go to your terminal
type python and hit Enter
now type from app import db and hit enter
then type db.create_all() and hit enter
you should see the todo.db file created inside your project since we gave the database url as local file

Great if everything works perfectly then congratulations, your database has now been created .

Your Database is created
Your table is created with the columns you wanted to have .
Perfect.

Now lets create a form inside of your home.html to upload the data to the database.

  <form action="/" id="formSubmission" method="POST">
    <span>Title</span>
    <input type="text"name="title" placeholder="enter your Title" id="name">
    <span>Description</span>
    <input type="text"name="description" placeholder="Enter your description" id="description">
    <button type="submit">Add Todo</button>
  </form>

whats important here is you give name attribute to your inputs
Lets also create a table to showcase the todos coming from the database

 <ul id="data">
   <li>Title</li>
   <li>Time</li>
   <li>Description</li>
</ul>

We will be using jinja for loop here to show the data but first lets get the C(create) part working.

So lets create our end point for the creation of data in our database.

@app.route("/",methods=['POST'])
def main_page():
    if request.method=='POST':
        title=request.form['title']
        description=request.form['description']
        todo=Todo(title=title ,description=description)
        db.session.add(todo)
        db.session.commit()
    alltodos=Todo.query.all()
    return render_template('index.html',todos=alltodos)

For adding values in your database the command is db.session.add(your_data_object_here)
It wont get saved if you do not commit it so db.session.commit()
Perfect
Here you can see that we are checking that if the request coming is a post request then get the data from the inputs and create a todo object with the details you want to save and then add to the database.
If the request is not a post request then get all the todos that are saved in the database and send them over to the webpage using jinja . (here we have named it todos that we will be using in the webpage ).
Now head over to your webpage and do the following changes

 {% for user in todos %}
     <li>{{loop.index}}</li>
  <li>{{user.title}}</li>
     <li>{{user.description}}
         <div class="buttons">


      <button><a href="/delete/{{user.sno}}">Delete</a>
<button><a href="/update/{{user.sno}}">Delete</a></button>

              </div>
     </li>

{% endfor %}

This is for loop that is given to us by jinja and we can use it.
We have also put the buttons for deleting and updating the todo but lets ignore that for now.
So you can see we extracted title and description from our todos array that we are getting.
loop.index is the index just as we have index in any loop in any language , this is just used to give a number to the todo.

OK 👍 Most of the heavy lifting is done.

Lets look at some of the commands of sqlalchemy that we can use

we already saw

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

We also have

db.session.filter_by(criteria to filter e.g sno = 1) This is used to filter the database based on the criteria we provide e.g filter the database and get me only the row where in we have sno = 1 and it will give it to you. Now if the database has more than 1 with the same criteria it will give you all of that , so we can chain it up with .first() in order to get only the first instance of it.

Then we also have db.session.delete(todo_to_delete)

Ok ! so upto now if you run the code you should be able to view all the todos that are there and also save the new todos to the database.

Now Let's delete them.
For deleting any todo you need something thats unique to it only e.g here we have the sno thats a primary key and that will be unique to every todo.
Ok lets create the end point for deletion.

@app.route("/delete/<int:sno>")
def delete(sno):
    deletetodo = Todo.query.filter_by(sno=sno).first()
    db.session.delete(deletetodo)
    db.session.commit()
    return redirect("/")

Easy , right

Ok so here you can see that the end point needs a sno that would be an integer value and then we pass that to the function

What we then do is query the database and then filter out the exact todo from the database

Then pass that todo to the delete function

Then we commit the changes

Great ! Once all the changes would be saved we will be redirecting the user back to the index page using redirect module imported from the flask

Sweet ! you now would be able to delete your todos as well .
Now we are able to

Create the todo

Delete the todo

And View All the todos

Now in order to Update the todo , its very easy as well
Let me break it down to points for you

First create the end point for the update where in we will get the sno of the todo (the same way we did for delete)
Then create a new webpage for the update todo form
send the todo that we will get from the end point to this page just as we did in the view end point for all todos.
Then once you get the data there , once the user clicks on update send the request to the same endpoint but using a POST request .
And in the end point we will check that if the request is a post request update the todo and then redirect the user back to the index page.

Lets code it.

we already have the button for the updation of the todo .so lets create the end point.

@app.route("/updatetodo/<int:sno>",methods=['POST'])
def update_todo(sno):

     if request.method == 'POST':
        updatetodo = Todo.query.filter_by(sno=sno).first()
        title = request.form['title']
        description = request.form['description']
        todo = Todo(title=title, description=description)
        updatetodo.title=title
        updatetodo.description=description
        db.session.commit()
        return redirect("/")
    updatetodo = Todo.query.filter_by(sno=sno).first()
    return render_template('update.html',
updatetodo=updatetodo)

ok so the end point is created.

Lets create a new file namely update.html for the update todo and put this code inside(along with the boiler plate for html obviously)


<div class="container">
  <form action="/updatetodo/{{updatetodo.sno}}" id="formSubmission" method="POST">

    <span>Title</span>
    <input type="text"name="title" placeholder="enter your Title" id="name" value="{{updatetodo.title}}">
    <span>Description</span>
    <input type="text"name="description" placeholder="Enter your description" id="description" value="{{updatetodo.description}}">
    <button type="submit">Update</button>

  </form>

Ok , Great

So now when you click on the update button the user will be taken to this page with the fields already filled with the existing data .
And on clicking the submit the post request will be sent to the end point and the details i.e title and description will be changed to the new data.

** If you have made it to this point congratulation ✨✨✨.***
There is a lot that can be done afterwards but i will leave you guys to it.
I hope you guys find it useful .
Thankyou so much .
Keep coding

Want to encourage me to create posts like this more
Buy me a coffee