Flask CRUD Application with SQLite

In this tutorial we will create a CRUD (Create, Read, Update and Delete) application in Flask with SQLite database

Flask MySql Login and Registration With Session

First, we have to install Flask package

pip install flask

File Structure:

Creating Project :

Create a folder 'crudapp'.
Create 'app.py' file inside of 'crudapp' folder.
Create the 'templates' folder inside of 'crudapp' folder for creating HTML page.
Create layout.html, index.html, add_user.html and edit_user.html files inside of 'templates' folder.

Creating SQLite Database :

Create 'create_db.py' file inside of 'crudapp' folder.
Run 'create_db.py' to create a SQLite database and users table.

create_db.py

import sqlite3 as sql

#connect to SQLite
con = sql.connect('db_web.db')

#Create a Connection
cur = con.cursor()

#Drop users table if already exsist.
cur.execute("DROP TABLE IF EXISTS users")

#Create users table  in db_web database
sql ='''CREATE TABLE "users" (
	"UID"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"UNAME"	TEXT,
	"CONTACT"	TEXT
)'''
cur.execute(sql)

#commit changes
con.commit()

#close the connection
con.close()

app.py

from flask import Flask,render_template,request,redirect,url_for,flash
import sqlite3 as sql
app=Flask(__name__)

@app.route("/")
@app.route("/index")
def index():
    con=sql.connect("db_web.db")
    con.row_factory=sql.Row
    cur=con.cursor()
    cur.execute("select * from users")
    data=cur.fetchall()
    return render_template("index.html",datas=data)

@app.route("/add_user",methods=['POST','GET'])
def add_user():
    if request.method=='POST':
        uname=request.form['uname']
        contact=request.form['contact']
        con=sql.connect("db_web.db")
        cur=con.cursor()
        cur.execute("insert into users(UNAME,CONTACT) values (?,?)",(uname,contact))
        con.commit()
        flash('User Added','success')
        return redirect(url_for("index"))
    return render_template("add_user.html")

@app.route("/edit_user/<string:uid>",methods=['POST','GET'])
def edit_user(uid):
    if request.method=='POST':
        uname=request.form['uname']
        contact=request.form['contact']
        con=sql.connect("db_web.db")
        cur=con.cursor()
        cur.execute("update users set UNAME=?,CONTACT=? where UID=?",(uname,contact,uid))
        con.commit()
        flash('User Updated','success')
        return redirect(url_for("index"))
    con=sql.connect("db_web.db")
    con.row_factory=sql.Row
    cur=con.cursor()
    cur.execute("select * from users where UID=?",(uid,))
    data=cur.fetchone()
    return render_template("edit_user.html",datas=data)
    
@app.route("/delete_user/<string:uid>",methods=['GET'])
def delete_user(uid):
    con=sql.connect("db_web.db")
    cur=con.cursor()
    cur.execute("delete from users where UID=?",(uid,))
    con.commit()
    flash('User Deleted','warning')
    return redirect(url_for("index"))
    
if __name__=='__main__':
    app.secret_key='admin123'
    app.run(debug=True)

layout.html

<html>
  <head>
    <title>Flask - SQLite </title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
  </head>
  <body>
    <div class='container pt-3'>
      {% with messages=get_flashed_messages(with_categories=true) %}
        {% if messages %}
          {% for category,message in messages %}
            <div class='alert alert-{{category}}'>{{message}}</div>
          {% endfor %}
        {% endif %}
      {% endwith %}
      {% block body %}
      
      {% endblock %}
    </div>
  </body>
</html>

index.html

{% extends 'layout.html' %}
{% block body %}
  <h3 class='text-center text-muted mb-3'>Flask - SQLite CRUD Application</h3>
  <p class='text-right'><a href='{{url_for("add_user")}}' class='btn btn-success '>+Add User</a></p>
  <table class='table table-bordered'>
    <thead>
      <th>SNo</th>
      <th>Name</th>
      <th>Contact</th>
      <th>Edit</th>
      <th>Delete</th>
    </thead>
    <tbody>
      {% for row in datas %}
        <tr>
          <td>{{loop.index}}</td>
          <td>{{row.UNAME}}</td>
          <td>{{row.CONTACT}}</td>
          <td><a href='{{url_for("edit_user",uid=row.UID)}}' class='btn btn-primary'>Edit</a></td>
          <td><a href='{{url_for("delete_user",uid=row.UID)}}' class='btn btn-danger' onclick='return confirm("Are You Sure")'>Delete</a></td>
        </tr>
      {%endfor%}
    </tbody>
  </table>
{% endblock %}

add_user.html

{% extends 'layout.html' %}
{% block body %}
  <div class='row'>
    <div class='col-5 mx-auto'>
      <h3>Add user</h3><hr>
      <form method='post' action='{{url_for("add_user")}}'>
        <div class='form-group'>
          <label>User Name</label>
          <input type='text' name='uname' required class='form-control'>
        </div>
        <div class='form-group'>
          <label>Contact</label>
          <input type='text' name='contact' required class='form-control'>
        </div>
        <input type='submit' value='Submit' class='btn btn-primary'>
      </form>
    
    </div>
  </div>
{% endblock %}

edit_user.html

{% extends 'layout.html' %}
{% block body %}
  <div class='row'>
    <div class='col-5 mx-auto'>
      <h3>Edit user</h3><hr>
      <form method='post' action='{{url_for("edit_user",uid=datas.UID)}}'>
        <div class='form-group'>
          <label>User Name</label>
          <input type='text' name='uname' required class='form-control' value='{{datas.UNAME}}'>
        </div>
        <div class='form-group'>
          <label>Contact</label>
          <input type='text' name='contact' required class='form-control' value='{{datas.CONTACT}}'>
        </div>
        <input type='submit' value='Submit' class='btn btn-primary'>
      </form>
    </div>
  </div>
{% endblock %}

Run the Project: