Python Flask Datatable using SQLalchemy With Mysql
First you need to install flask, and after that you need to install flask-sqlalchemy.
pip install Flask
pip install Flask-SQLAlchemy
#app.py
from flask import Flask, render_template, request, redirect, url_for, flash
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.secret_key = "cairocoders-ednalan-06300131"
#SqlAlchemy Database Configuration With Mysql
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:''@localhost/testingdb'
#mysql+pymysql://username:passwd@host/databasename
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
#Creating model table for our CRUD database
class Employee(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(100))
email = db.Column(db.String(100))
phone = db.Column(db.String(100))
def __init__(self, name, email, phone):
self.name = name
self.email = email
self.phone = phone
#query on all our employee data
@app.route('/')
def Index():
all_data = Employee.query.all()
return render_template("index.html", employees = all_data)
#insert data to mysql database via html forms
@app.route('/insert', methods = ['POST'])
def insert():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
phone = request.form['phone']
my_data = Employee(name, email, phone)
db.session.add(my_data)
db.session.commit()
flash("Employee Inserted Successfully")
return redirect(url_for('Index'))
#update employee
@app.route('/update', methods = ['GET', 'POST'])
def update():
if request.method == 'POST':
my_data = Employee.query.get(request.form.get('id'))
my_data.name = request.form['name']
my_data.email = request.form['email']
my_data.phone = request.form['phone']
db.session.commit()
flash("Employee Updated Successfully")
return redirect(url_for('Index'))
#delete employee
@app.route('/delete//', methods = ['GET', 'POST'])
def delete(id):
my_data = Employee.query.get(id)
db.session.delete(my_data)
db.session.commit()
flash("Employee Deleted Successfully")
return redirect(url_for('Index'))
if __name__ == "__main__":
app.run(debug=True)
//index.html
{% extends 'base.html' %}
{% include 'header.html' %}
{% block title %} Home {% endblock %}
{% block body %}
<div class="container">
<div class="row">
<div class="col md-12">
<div class="jumbotron p-3">
<h2>Manage <b>Employees </b> <button type="button" class="btn btn-success float-right"
data-toggle="modal" data-target="#mymodal">Add New Employees</button> </h2>
{% with messages = get_flashed_messages() %}
{% if messages %}
{% for message in messages %}
<div class="alert alert-success alert-dismissable" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="close">
<span aria-hidden="true">x</span>
</button>
{{message}}
</div>
{% endfor %}
{% endif %}
{% endwith %}
<table class="table table-hover table-striped">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Action</th>
</tr>
{% for row in employees %}
<tr>
<td>{{row.id}}</td>
<td>{{row.name}}</td>
<td>{{row.email}}</td>
<td>{{row.phone}}</td>
<td>
<a href="/update/{{row.id}}" class="btn btn-warning btn-xs" data-toggle="modal" data-target="#modaledit{{row.id}}">Edit</a>
<a href="/delete/{{row.id}}" class="btn btn-danger btn-xs" onclick="return confirm('Are You Sure To Delete ?')">Delete</a>
</td>
</tr>
<!-- Modal Edit Employee-->
<div id="modaledit{{row.id}}" class="modal fade" role="dialog">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header"><h4 class="modal-title">Update Information</h4></div>
<div class="modal-body">
<form action="{{url_for('update')}}" method="POST">
<div class="form-group">
<label>Name:</label>
<input type="hidden" name="id" value="{{row.id}}">
<input type="text" class="form-control" name="name" value="{{row.name}}">
</div>
<div class="form-group">
<label>Email:</label>
<input type="text" class="form-control" name="email" value="{{row.email}}">
</div>
<div class="form-group">
<label>Phone:</label>
<input type="text" class="form-control" name="phone" value="{{row.phone}}">
</div>
<div class="form-group">
<button class="btn btn-primary" type="submit">Update</button>
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
{% endfor %}
</table>
</div>
<!-- Modal Add Employee-->
<div id="mymodal" class="modal fade" role="dialog">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header"><h4 class="modal-title">Add Employee</h4></div>
<div class="modal-body">
<form action="{{url_for('insert')}}" method="POST">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" name="name" required="1">
</div>
<div class="form-group">
<label>Email:</label>
<input type="email" class="form-control" name="email" required="1">
</div>
<div class="form-group">
<label>Phone:</label>
<input type="number" class="form-control" name="phone" required="1">
</div>
<div class="form-group">
<button class="btn btn-primary" type="submit">Add Employee</button>
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
{% endblock %}
//base.html
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<meta charset="UTF-8">
<title>{% block title %} {% endblock %} </title>
</head>
<body>
{% block body %} {% endblock %}
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>
//header.html
{% extends 'base.html' %}
{% block title %} Python Flask Datatable {% endblock %}
{% block body %}
<div class="jumbotron p-3">
<div class="well text-center">
<h1>Python Flask Datatable using SQLalchemy With Mysql</h1>
</div>
</div>
{% endblock %}
