Others
Others

Flask CRUD Application with MySQL


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

Flask MySql Login and Registration With Session

First, we have to install Flask package and MySQL

pip install flask
pip install flask_mysqldb

Creating the Database and Table

Execute the following SQL query to create a table named 'tbl_users' inside your MySQL database named 'db_flask'.

  
CREATE TABLE  tbl_users (
  uid int(11) NOT NULL AUTO_INCREMENT,
  name varchar(150) NOT NULL,
  email varchar(50) NOT NULL,
  age varchar(50) NOT NULL,
  contact varchar(50) NOT NULL,
  address varchar(150) NOT NULL,
  PRIMARY KEY (uid)
);

File Structure:

Flask MySql Login and Registration With Session

Creating Project :

  1. Create a folder 'crudapp'.
  2. Create 'app.py' file inside of 'crudapp' folder.
  3. Create the 'templates' folder inside of 'crudapp' folder for creating HTML page.
  4. Create _layout.html, index.html, add_user.html and edit.html files inside of 'templates' folder.
app.py 
from flask import Flask,render_template,request,flash,session,redirect
from flask_mysqldb import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST']='localhost'
app.config['MYSQL_USER']='root'
app.config['MYSQL_PASSWORD']=''
app.config['MYSQL_DB']='db_flask'
app.config['MYSQL_CURSORCLASS']='DictCursor'
mysql=MySQL(app)

@app.route('/')
@app.route('/index',methods=['POST','GET'])
def index():
    cur = mysql.connection.cursor()
    cur.execute("select * from tbl_users")
    data = cur.fetchall()
    return render_template("index.html",datas = data)
    
@app.route('/home',methods=['POST','GET'])
def home():
    return render_template("home.html")

    
@app.route('/add_user',methods=['POST','GET'])
def add_user():
    if request.method=='POST':
        a=request.form["uname"]
        b=request.form["contact"]
        c=request.form["age"]
        d=request.form["email"]
        e=request.form["address"]
        cur = mysql.connection.cursor()
        cur.execute("INSERT INTO tbl_users( `name`, `email`, `age`, `contact`,`address`) values(%s,%s,%s,%s,%s)",(a,d,c,b,e))
        mysql.connection.commit()
        cur.close()
        flash('User Added Successfully','success')
    return render_template('add_user.html')
    
@app.route('/edit/<string:uid>',methods=['GET','POST'])
def edit(uid):
    if request.method=='POST':
        a=request.form["uname"]
        b=request.form["contact"]
        c=request.form["age"]
        d=request.form["email"]
        e=request.form["address"]
        cur=mysql.connection.cursor()
        cur.execute("update `tbl_users` set  `name`=%s, `email`=%s, `age`=%s, `contact`=%s, `address`=%s where uid=%s",(a,d,c,b,e,uid))
        mysql.connection.commit()
        cur.close()
    cur=mysql.connection.cursor()
    cur.execute("select * from tbl_users where uid=%s",(uid,))
    data=cur.fetchone()
    return render_template("edit.html",data=data)    
    
@app.route('/user_delete/<string:uid>',methods=['GET','POST'])
def user_delete(uid):
    cur=mysql.connection.cursor()
    cur.execute("delete from tbl_users where uid=%s",(uid))
    mysql.connection.commit()
    cur.close()
    return redirect(url_for("index"))
    
if __name__ == ('__main__'):
    app.secret_key='secret123'
    app.run(debug=True)
_layout.html 
<html>
 <head>
  <title>Index</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" integrity="sha384-xOolHFLEh07PJGoPkLv1IbcEPTNtaed2xpHsD9ESMhqIYd0nLMwNLD69Npy4HI+N" crossorigin="anonymous">
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.min.js" integrity="sha384-+sLIOodYLS7CIrQpBjl+C7nPvqq+FbNUBDunl/OZv93DB7Ln/533i8e/mZXLi/P+" crossorigin="anonymous"></script>
 </head>
 <body>
  <nav class="navbar navbar-expand-lg navbar-dark bg-danger">
     <a class="navbar-brand" href="#">FLASK CRUD OPERATION WITH MySQL</a>
     <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
    <div class="navbar-nav ml-auto">
      <a class="nav-link active" href="#">Home </a>
    </div>
     </div>
  </nav>
  <div class='container'>
   <div class='row mt-5'>
    <div class='col-md-8'>
     {% with messages = get_flashed_messages(with_categories=true) %}
      {% if messages %}
       {% for category,message in messages %}
        <div class="alert alert-dismissible fade show alert-{{ category }}"" role="alert">
         {{ message }}
          <button type="button" class="close" data-dismiss="alert" aria-label="Close">
         <span aria-hidden="true">&times;</span>
          </button>
        </div>
       {% endfor %}
      {% endif %}
     {% endwith %}
    </div>
   </div>
   <div class='row'>
     {% block body %}
   
    {% endblock %}
   </div>
  </div>
 </body>
</html>
index.html 
{% extends '_layout.html' %}
{% block body %}
 <p class='mx-auto '><h3 style='color:#6c757d;'>FLASK CRUD OPERATION WITH MySQL</h3></p>
 <p class='ml-auto mt-5'><a href='/add_user' class='btn btn-success'>+ Add User</a></p>
  <table class='table table-bordered table-striped'>
   <thead>
     <tr>
    <th>Sno</th>
    <th>Name</th>
    <th>Contact</th>
    <th>Age</th>
    <th>Email</th>
    <th>Address</th>
    <th>Edit</th>
    <th>Delete</th>
     </tr>
   </thead>
   <tbody>
     {% for row in datas %}
    <tr>
    <td>{{loop.index}}</td>
    <td>{{row.name}}</td>
    <td>{{row.contact}}</td>
    <td>{{row.age}}</td>
    <td>{{row.email}}</td>
    <td>{{row.address}}</td>
    <td><a href='{{url_for("edit",uid=row.uid)}}' class='btn btn-info'>Edit</a></td>
    <td><a href='{{url_for("user_delete",uid=row.uid)}}' class='btn btn-danger'>Delete</a></td>
      </tr>
   {% endfor %}
   </tbody>
  </table>
{% endblock %}
add_user.html 
{% extends '_layout.html' %}
{% block body %}
<div class='col-md-6 mx-auto'>
    <form method='post' autocomplete='off'>
        <div class='form-group'>
            <label>Name</label>
            <input type='text' class='form-control form-control-sm' name='uname'>
        </div>
        <div class='form-group'>
            <label>Contact</label>
            <input type='text' class='form-control form-control-sm' name='contact'>
        </div>
        <div class='form-group'>
            <label>Age</label>
            <input type='text' class='form-control form-control-sm' name='age'>
        </div>
        <div class='form-group'>
            <label>Email</label>
            <input type='text' class='form-control form-control-sm' name='email'>
        </div>
        <div class='form-group'>
            <label>Address</label>
            <input type='text' class='form-control form-control-sm' name='address'>
        </div>
        <input type='submit' name='submit' value='submit' class='btn btn-sm btn-success'>
    </form>
</div>
{% endblock %}
edit.html 
{% extends '_layout.html' %}
{% block body %}
  <div class='col-md-6 mx-auto'>
      <form method='post' autocomplete='off'>
    <div class='form-group'>
        <label>Name</label>
        <input type='text' class='form-control form-control-sm' name='uname' value='{{data.name}}'>
    </div>
    <div class='form-group'>
        <label>Contact</label>
        <input type='text' class='form-control form-control-sm' name='contact' value='{{data.contact}}'>
    </div>
    <div class='form-group'>
        <label>Age</label>
        <input type='text' class='form-control form-control-sm' name='age' value='{{data.age}}'>
    </div>
    <div class='form-group'>
        <label>Email</label>
        <input type='text' class='form-control form-control-sm' name='email' value='{{data.email}}'>
    </div>
    <div class='form-group'>
        <label>Address</label>
        <input type='text' class='form-control form-control-sm' name='address' value='{{data.address}}'>
    </div>
    <input type='submit' name='submit' value='submit' class='btn btn-sm btn-success'>
      </form>
  </div>
{% endblock %}

Run the Project:

  1. Run 'app.py' file.
  2. Browse the URL 'localhost:5000'.