install psycopg2 https://pypi.org/project/psycopg2/
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
(venv) PS C:\flaskmyproject> pip install psycopg2
CREATE TABLE employee (
id serial PRIMARY KEY,
name VARCHAR ( 100 ) NOT NULL,
position VARCHAR ( 100 ) NOT NULL,
office VARCHAR ( 100 ) NOT NULL
);
INSERT INTO
employee(name, position, office)
VALUES
('Tiger Wood', 'Accountant', 'Tokyo'),
('Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London'),
('Jacob thompson', 'Junior Technical Author', 'San Francisco'),
('cylde Ednalan', 'Software Engineer', 'Olongapo');
#app.py
from flask import Flask, render_template, redirect, request, flash, jsonify
import psycopg2 #pip install psycopg2
import psycopg2.extras
app = Flask(__name__)
app.secret_key = "caircocoders-ednalan"
DB_HOST = "localhost"
DB_NAME = "sampledb"
DB_USER = "postgres"
DB_PASS = "admin"
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
@app.route('/')
def index():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM employee ORDER BY id")
employee = cur.fetchall()
return render_template('index.html', employee=employee)
@app.route("/ajax_add",methods=["POST","GET"])
def ajax_add():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
txtname = request.form['txtname']
txtposition = request.form['txtposition']
txtoffice = request.form['txtoffice']
print(txtname)
if txtname == '':
msg = 'Please Input name'
elif txtposition == '':
msg = 'Please Input Position'
elif txtoffice == '':
msg = 'Please Input Office'
else:
cur.execute("INSERT INTO employee (name,position,office) VALUES (%s,%s,%s)",[txtname,txtposition,txtoffice])
conn.commit()
cur.close()
msg = 'New record created successfully'
return jsonify(msg)
@app.route("/ajax_update",methods=["POST","GET"])
def ajax_update():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
string = request.form['string']
txtname = request.form['txtname']
txtposition = request.form['txtposition']
txtoffice = request.form['txtoffice']
print(string)
cur.execute("UPDATE employee SET name = %s, position = %s, office = %s WHERE id = %s ", [txtname, txtposition, txtoffice, string])
conn.commit()
cur.close()
msg = 'Record successfully Updated'
return jsonify(msg)
@app.route("/ajax_delete",methods=["POST","GET"])
def ajax_delete():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
getid = request.form['string']
print(getid)
cur.execute('DELETE FROM employee WHERE id = {0}'.format(getid))
conn.commit()
cur.close()
msg = 'Record deleted successfully'
return jsonify(msg)
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('[data-toggle="tooltip"]').tooltip();
var actions = $("table td:last-child").html();
// Append table with add row form on add new button click
$(".add-new").click(function(){
$(this).attr("disabled", "disabled");
var index = $("table tbody tr:last-child").index();
var row = '<tr>' +
'<td><input type="text" class="form-control" name="name" id="txtname"></td>' +
'<td><input type="text" class="form-control" name="position" id="txtposition"></td>' +
'<td><input type="text" class="form-control" name="office" id="txtoffice"></td>' +
'<td>' + actions + '</td>' +
'</tr>';
$("table").append(row);
$("table tbody tr").eq(index + 1).find(".add, .edit, .delete").toggle();
$('[data-toggle="tooltip"]').tooltip();
});
// Add row on add button click
$(document).on("click", ".add", function(){
var empty = false;
var input = $(this).parents("tr").find('input[type="text"]');
input.each(function(){
if(!$(this).val()){
$(this).addClass("error");
empty = true;
} else{
$(this).removeClass("error");
}
});
var txtname = $("#txtname").val();
var txtposition = $("#txtposition").val();
var txtoffice = $("#txtoffice").val();
$.post("/ajax_add", { txtname: txtname, txtposition: txtposition, txtoffice: txtoffice}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
$(this).parents("tr").find(".error").first().focus();
if(!empty){
input.each(function(){
$(this).parent("td").html($(this).val());
});
$(this).parents("tr").find(".add, .edit, .delete").toggle();
$(".add-new").removeAttr("disabled");
}
});
// Delete row on delete button click
$(document).on("click", ".delete", function(){
$(this).parents("tr").remove();
$(".add-new").removeAttr("disabled");
var id = $(this).attr("id");
var string = id;
$.post("/ajax_delete", { string: string}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
});
// update rec row on edit button click
$(document).on("click", ".update", function(){
var id = $(this).attr("id");
var string = id;
var txtname = $("#txtname").val();
var txtposition = $("#txtposition").val();
var txtoffice = $("#txtoffice").val();
$.post("/ajax_update", { string: string,txtname: txtname, txtposition: txtposition, txtoffice: txtoffice}, function(data) {
$("#displaymessage").html(data);
$("#displaymessage").show();
});
});
// Edit row on edit button click
$(document).on("click", ".edit", function(){
$(this).parents("tr").find("td:not(:last-child)").each(function(i){
if (i=='0'){
var idname = 'txtname';
}else if (i=='1'){
var idname = 'txtposition';
}else if (i=='2'){
var idname = 'txtoffice';
}else{}
$(this).html('<input type="text" name="updaterec" id="' + idname + '" class="form-control" value="' + $(this).text() + '">');
});
$(this).parents("tr").find(".add, .edit").toggle();
$(".add-new").attr("disabled", "disabled");
$(this).parents("tr").find(".add").removeClass("add").addClass("update");
});
});
</script>
</head>
<body>
<div class="container"><p><h1 align="center">Add Edit Delete Datatable Row Using Jquery Ajax Python Flask and PostgreSQL database</h1></p>
<div class="table-wrapper">
<div class="table-title">
<div class="row">
<div class="col-sm-8"><h2>Employee <b>Details</b></h2></div>
<div class="col-sm-4">
<button type="button" class="btn btn-info add-new"><i class="fa fa-plus"></i> Add New</button>
</div>
<div class='btn btn-info' id="displaymessage" style="display:none;width:100%;margin-top:10px;"></div>
</div>
</div>
<table class="table table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
{% for row in employee %}
<tr>
<td>{{row.name}}</td>
<td>{{row.position}}</td>
<td>{{row.office}}</td>
<td>
<a class="add" title="Add" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-user-plus"></i></a>
<a class="edit" title="Edit" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-pencil"></i></a>
<a class="delete" title="Delete" data-toggle="tooltip" id="{{row.id}}"><i class="fa fa-trash-o"></i></a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
<style type="text/css">
body {
color: #404E67;
background: #F5F7FA;
font-family: 'Open Sans', sans-serif;
}
.table-wrapper {
width: 700px;
margin: 30px auto;
background: #fff;
padding: 20px;
box-shadow: 0 1px 1px rgba(0,0,0,.05);
}
.table-title {
padding-bottom: 10px;
margin: 0 0 10px;
}
.table-title h2 {
margin: 6px 0 0;
font-size: 22px;
}
.table-title .add-new {
float: right;
height: 30px;
font-weight: bold;
font-size: 12px;
text-shadow: none;
min-width: 100px;
border-radius: 50px;
line-height: 13px;
}
.table-title .add-new i {
margin-right: 4px;
}
table.table {
table-layout: fixed;
}
table.table tr th, table.table tr td {
border-color: #e9e9e9;
}
table.table th i {
font-size: 13px;
margin: 0 5px;
cursor: pointer;
}
table.table th:last-child {
width: 100px;
}
table.table td a {
cursor: pointer;
display: inline-block;
margin: 0 5px;
min-width: 24px;
}
table.table td a.add {
color: #27C46B;
}
table.table td a.edit {
color: #FFC107;
}
table.table td a.delete {
color: #E34724;
}
table.table td i {
font-size: 19px;
}
table.table td a.add i {
font-size: 24px;
margin-right: -1px;
position: relative;
top: 3px;
}
table.table .form-control {
height: 32px;
line-height: 32px;
box-shadow: none;
border-radius: 2px;
}
table.table .form-control.error {
border-color: #f50000;
}
table.table td .add {
display: none;
}
</style>
</body>
</html>
