Python Flask Student List CRUD System Using MySQL Database for Beginners

In this tutorial, we are going to learn the python flask student list crud system using MySQL database for beginners.

Before you have to learn how to python flask setting up and connect MySQL database connection and templates uses.

on the create project folder you have to install two things to fetch data and flask. Here, below code to install using python terminal, and for MySQL, am using xampp software and database inside environment to turn on the apache server and MySQL database.

Import Flask and MySQL:


from flask import Flask, render_template, request, redirect, url_for
from flask_mysqldb import MySQL

MySQL Database Connection:


app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'phptutorial'

mysql = MySQL(app)

Insert Code and Fetch Data:

Here ,the data fetch from the mysql database and order by desc id and Limit 10 data to show in the html table in "home.html"


@app.route('/')
def home_page():
    table = """select id,student_name,dept_name from student
               order by id desc
               Limit 10"""
    cursor = mysql.connection.cursor()
    cursor.execute(table)
    results = cursor.fetchall()
    return render_template('home.html', results=results)
    mysql.connection.commit()
    cursor.close()


@app.route('/add', methods=['POST', 'GET'])
def add():
    if request.method == 'POST':
        name = request.form['name']
        dept = request.form['dept']
        cursor = mysql.connection.cursor()
        result = cursor.execute(''' INSERT INTO student 
        (student_name,dept_name) VALUES (%s,%s)''', (name, dept))
        mysql.connection.commit()
        cursor.close()
        return redirect(url_for('home_page'))

Update code:


@app.route('/update', methods=['POST'])
def update():
    if request.method == 'POST':
        id_no = request.form.get('id')
        name = request.form.get('name')
        dept = request.form.get('dept')
        cursor = mysql.connection.cursor()
        cursor.execute('''UPDATE student SET student_name=%s, 
        dept_name=%s WHERE id=%s''', (name, dept, id_no))
        mysql.connection.commit()
        return redirect(url_for('home_page'))

Delete Code:


@app.route('/delete/<string:id_no>', methods=['GET'])
def delete(id_no):
    cursor = mysql.connection.cursor()
    cursor.execute("DELETE FROM student WHERE id=%s", (id_no,))
    mysql.connection.commit()
    return redirect(url_for('home_page'))

home.html


<div class="container py-5">
<form class="form-control" action="/add" method = "POST">
    <div class="row g-3 align-items-center">
    <div class="mb-3">
        <label class="form-label">Student Name</label>
        <input class="form-control" type = "text" name = "name" />
    </div>
    <div class="mb-3">
        <lable class="form-label">Department Name</lable>
        <input class="form-control" type = "text" name = "dept" />
    </div>
   </div>
       <button class="btn btn-primary" type = "submit">submit</button>

</form>


<h1 class="py-3" >List of Students:</h1>
<table class="table table-bordered table-striped py-5" >

    <th scope="col">Id</th>
    <th scope="col">Student Name</th>
    <th scope="col">Department Name</th>
    <th scope="col">Actions</th>
    {% for i in results %}
    <tr scope="row">
        <td>{{i[0]}}</td>
        <td>{{i[1]}}</td>
        <td>{{i[2]}}</td>
        <td>
            <a href="/update" data-bs-toggle="modal" data-bs-target="#edit{{i[0]}}" class="btn btn-warning"  value="Edit"
     >edit</a>
        <a href="/delete/{{ i[0] }}" class="btn btn-danger btn-xs" onclick="return confirm('Are You Sure For Delete?')">Delete</a></td>
    </tr>
<!-- Modal -->
<div class="modal fade" id="edit{{i[0]}}" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Modal title</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <form action="{{ url_for('update') }}" method = "POST">
    <div class="row">
        <div class="mb-3">
            <label>ID:</label>
            <input class="form-control" type="text"  name="id" value="{{i[0]}}">
        </div>
    <div class="mb-3">
        <label class="form-label">Student Name</label>
        <input class="form-control" type = "text" name = "name" value="{{i[1]}}" />
    </div>
    <div class="mb-3">
        <lable class="form-label">Department Name</lable>
        <input class="form-control" type = "text" name = "dept" value="{{i[2]}}"/>
    </div>
   </div>
       <button class="btn btn-primary" type = "submit">submit</button>

</form>
      </div>
    </div>
  </div>
</div>
{% endfor %}
</table>
</div>
<!---modal popover--->

Output:

Download Full source code