Delete Multiple Rows from Table using Flask, AJAX, jQuery, MySQL

In this tutorial we build an application using Python based Flask framework to fetch all products from MySQL database and display on the HTML table. This example is all about to delete multiple rows from HTML as well as database tables. We will use IN clause with WHERE condition in MySQL query to delete multiple rows from database table.

We will put checkbox against each row on the table so that user will be able to select a particular row for deletion. We will also put a checkbox on the table header and selecting this checkbox all rows on the table will be selected for deletion.

We are using jQuery to check or check the checkbox. If user selects manually all checkboxes for rows on the table then the checkbox in table header will be checked automatically to show that all checkboxes on the table body are checked. If any of the checkboxes on the table body gets unchecked the header checkbox will be unchecked automatically.

Prerequisites

Python 3.8.2, Flask 1.1.1, MySQL 8.0.17, jQuery 3.5.0

MySQL Table

As we are going to deleet data from MySQL database, so we need a table for it. So we will create a table called product under roytuts database.

We will also dump some data to test our application.

CREATE TABLE `product` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL,
	`code` varchar(255) NOT NULL,
	`price` double NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO `product` (`id`, `name`, `code`, `price`) VALUES
(1, 'American Tourist', 'AMTR01', 12000.00),
(2, 'EXP Portable Hard Drive', 'USB02', 5000.00),
(3, 'Shoes', 'SH03', 1000.00),
(4, 'XP 1155 Intel Core Laptop', 'LPN4', 80000.00),
(5, 'FinePix Pro2 3D Camera', '3DCAM01', 150000.00),
(6, 'Simple Mobile', 'MB06', 3000.00),
(7, 'Luxury Ultra thin Wrist Watch', 'WristWear03', 3000.00),
(8, 'Headphone', 'HD08', 400.00);

Create Project Directory

Create a project root directory called python-flask-delete-multiple-rows-ajax-jquery-mysql as per your chosen location.

We may not mention the project’s root directory name in the subsequent sections but we will assume that we are creating files with respect to the project’s root directory.

Configure Application

We will configure application through flask framework.

Create a file called app.py with the below code.

from flask import Flask

app = Flask(__name__)

Database Configuration

We create the below db.py Python script to setup the MySQL database configurations for connecting to database and storing product information into product table under roytuts database.

We need to configure database connection with flask module and that’s why we have imported app module and setup the MySQL configuration with flask module.

Make sure to change the database configuration values according to your database setup.

from app import app
from flaskext.mysql import MySQL

mysql = MySQL()
 
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

Required Database Queries

We are going to build some queries which will help us get products and delete products from database table.

We create a dao.py file to write the below code.

To delete single product from database table we can simply use WHERE condition but to delete multiple rows we need to use IN clause with WHERE condition. Even for single record deletion we can use IN clause with WHERE condition.

import pymysql
from db import mysql

def products():
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		
		sql = "SELECT * FROM product"
		
		cursor.execute(sql)
		
		rows = cursor.fetchall()
		
		return rows
		
	except Exception as e:
		print(e)

	finally:
		if cursor and conn:
			cursor.close()
			conn.close()

def delete_products(ids):
	try:
		conn = mysql.connect()
		cursor = conn.cursor(pymysql.cursors.DictCursor)
		
		sql = "DELETE FROM product WHERE id IN (" + ",".join(ids) + ")"
		
		cursor.execute(sql)
		
		conn.commit()
		
		return True
		
	except Exception as e:
		print(e)
		
		return False

	finally:
		if cursor and conn:
			cursor.close()
			conn.close()

Build Endpoint URLs

We will create endpoint URLs for showing products details on UI (user interface) using flask template.

We will also build a REST API which will be called using AJAX technique from jQuery language for deleting the selected products on UI.

import dao
import pymysql
from app import app
from flask import jsonify, request, render_template
		
@app.route('/delete_products', methods=['POST'])
def delete_products():	
	ids = request.json['ids']
	
	# validate the received values
	if ids:
		if ',' in ids:		
			ids = ids.split(',')
		
		resp = dao.delete_products(ids)
		
		if resp:
			resp = jsonify('<span style=\'color:green;\'>Products deleted successfully</span>')
			resp.status_code = 200
			return resp
		else:
			resp = jsonify('<span style=\'color:red;\'>Something went wrong during products deletion</span>')
			resp.status_code = 500
			return resp
	else:
		resp = jsonify('<span style=\'color:red;\'>Make sure you pass the required parameter</span>')
		resp.status_code = 400
		return resp
		
@app.route('/')
def home():
	products = dao.products()
	return render_template('app.html', products = products)
		
if __name__ == "__main__":
    app.run()

Template or View File

We have the below template or view file called app.html and kept under the standard directory templates.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Multiple Table Rows Deletion Example in Python, Flask, AJAX, jQuery, MySQL</title>
	<link type="text/css"  rel="stylesheet" href="{{ url_for('static', filename='css/table.css') }}"/>
	<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.0.min.js"></script>
	<script type="text/javascript" src="{{ url_for('static', filename='js/app.js') }}"></script>
</head>
<body>

<div>
	<h1>Multiple Table Rows Deletion Example in Python, Flask, AJAX, jQuery, MySQL</h1>

	<div id="body">
		{% if products %}
			<div id="msg"></div>
			<button id="delete_selected">Delete Selected Product(s)</button>
			<table class="datatable">
				<thead>
					<tr>
						<th><input id="check_all" type="checkbox"></th>
						<th>ID</th>
						<th>Code</th>
						<th>Name</th>
						<th>Price</th>
					</tr>
				</thead>
				<tbody>
					{% for p in products %}
						{% set row_class = ('even_col' if loop.index % 2 == 0 else 'odd_col') %}
						<tr class="{{ row_class }}">
							<td><input type="checkbox" name="row-check" value="{{ p['id'] }}"></td>
							<td>{{ p['id'] }}</td>
							<td>{{ p['code'] }}</td>
							<td>{{ p['name'] }}</td>
							<td>{{ p['price'] }}</td>
						</tr>
					{% endfor %}
				</tbody>
			</table>
			{% else %}
				<div style="color:red;"><p>No Record Found</p></div>
		{% endif %}
	</div>
</div>

</body>
</html>

jQuery AJAX

The below file app.js is kept under static/js folder with the below code.

When user selects products on HTML table on UI and click on Delete Selected Product(s) button then the selected products will be deleted.

To see the changes on UI you need to refresh the page.

$(function() {
	//If check_all checked then check all table rows
	$("#check_all").on("click", function () {
		if ($("input:checkbox").prop("checked")) {
			$("input:checkbox[name='row-check']").prop("checked", true);
		} else {
			$("input:checkbox[name='row-check']").prop("checked", false);
		}
	});

	// Check each table row checkbox
	$("input:checkbox[name='row-check']").on("change", function () {
		var total_check_boxes = $("input:checkbox[name='row-check']").length;
		var total_checked_boxes = $("input:checkbox[name='row-check']:checked").length;

		// If all checked manually then check check_all checkbox
		if (total_check_boxes === total_checked_boxes) {
			$("#check_all").prop("checked", true);
		}
		else {
			$("#check_all").prop("checked", false);
		}
	});
	
	$("#delete_selected").on("click", function () {
		var ids = '';
		var comma = '';
		$("input:checkbox[name='row-check']:checked").each(function() {
			ids = ids + comma + this.value;
			comma = ',';			
		});		
		
		if(ids.length > 0) {
			$.ajax({
				type: "POST",
				contentType: 'application/json;charset=UTF-8',
				url: "http://localhost:5000/delete_products",
				data: JSON.stringify({'ids': ids}),
				dataType: "json",
				cache: false,
				success: function(msg) {
					$("#msg").html(msg);
				},
				error: function(jqXHR, textStatus, errorThrown) {
					$("#msg").html("<span style='color:red;'>" + textStatus + " " + errorThrown + "</span>");
				}
			});
		} else {
			$("#msg").html('<span style="color:red;">You must select at least one product for deletion</span>');
		}
	});
});

CSS File

We have applied some basic styles on the table. The style is written table.css under static/css folder.

table.datatable {
	width:100%;
	border: none;
	background:#fff;
}
table.datatable td.table_foot {
	border: none;
	background: #fff;
	text-align: center;
}
table.datatable tr.odd_col {
	background: none;
}
table.datatable tr.even_col {
	background: #ddd;
}
table.datatable td {
	font-size:10pt;
	padding:5px 10px;
	border-bottom:1px solid #ddd;
	text-align: left;
}
table.datatable th {
	text-align: left;
	font-size: 8pt;
	padding: 10px 10px 7px;   
	text-transform: uppercase;
	color: #fff;
	background-color: black;
	font-family: sans-serif;
}

Deploying the Application

Now navigate to the project’s root directory using command line tool and execute the command main.py, your server will be started on default port 5000.

If you want to change the port then you can change the line app.run() to app.run(port=5001), where 5001 is the new port.

Testing the Application

Now we will test our application by hitting the URL http://localhost:5000/ and select the products on UI for deletion.

The below youtube video shows the expected results.

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *