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
CREATE TABLE users (
id serial PRIMARY KEY,
fullname VARCHAR ( 100 ) NOT NULL,
username VARCHAR ( 50 ) NOT NULL,
password VARCHAR ( 255 ) NOT NULL,
email VARCHAR ( 50 ) NOT NULL
);
#app.py
from flask import Flask, request, render_template, jsonify
import psycopg2 #pip install psycopg2
import psycopg2.extras
app = Flask(__name__)
app.secret_key = "cairocoders-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 home():
try:
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("SELECT * from users order by id")
userslist = cursor.fetchall()
return render_template('index.html',userslist=userslist)
except Exception as e:
print(e)
finally:
cursor.close()
@app.route("/update",methods=["POST","GET"])
def update():
try:
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
field = request.form['field']
value = request.form['value']
editid = request.form['id']
if field == 'username':
sql = "UPDATE users SET username=%s WHERE id=%s"
if field == 'name':
sql = "UPDATE users SET fullname=%s WHERE id=%s"
data = (value, editid)
cursor.execute(sql, data)
conn.commit()
success = 1
return jsonify(success)
except Exception as e:
print(e)
finally:
cursor.close()
if __name__ == "__main__":
app.run()
templates/index.html
//templates/index.html
<!doctype html>
<html>
<head>
<title>Jquery Ajax Live Editable Table using Python Flask PostgreSQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script type='text/javascript'>
$(document).ready(function(){
// Show Input element
$('.edit').click(function(){
$('.txtedit').hide();
$(this).next('.txtedit').show().focus();
$(this).hide();
});
// Save data
$(".txtedit").focusout(function(){
// Get edit id, field name and value
var id = this.id;
var split_id = id.split("_");
var field_name = split_id[0];
var edit_id = split_id[1];
var value = $(this).val();
// Hide Input element
$(this).hide();
// Hide and Change Text of the container with input elmeent
$(this).prev('.edit').show();
$(this).prev('.edit').text(value);
$.ajax({
url: '/update',
type: 'post',
data: { field:field_name, value:value, id:edit_id },
success:function(response){
if(response == 1){
console.log('Save successfully');
}else{
console.log("Not saved.");
}
}
});
});
});
</script>
</head>
<body >
<div class="container" >
<div class="row" style="padding:50px;">
<p><h1>Jquery Ajax Live Editable Table using Python Flask PostgreSQL</h1></p>
<table width='100%' border='0'>
<tr>
<th width='10%'>ID</th>
<th width='40%'>Username</th>
<th width='40%'>Name</th>
</tr>
{% for row in userslist %}
<tr>
<td>{{row.id}}</td>
<td>
<div class='edit' > {{row.username}}</div>
<input type='text' class='txtedit' value='{{row.username}}' id='username_{{row.id}}' >
</td>
<td>
<div class='edit' >{{row.fullname}} </div>
<input type='text' class='txtedit' value='{{row.fullname}}' id='name_{{row.id}}' >
</td>
</tr>
{% endfor %}
</table>
</div>
</div>
<style>
.edit{
width: 100%;
height: 25px;
}
.editMode{
border: 1px solid black;
}
table {
border:3px solid lavender;
border-radius:3px;
}
table tr:nth-child(1){
background-color:#4285f4;
}
table tr:nth-child(1) th{
color:white;
padding:10px 0px;
letter-spacing: 1px;
}
table td{
padding:10px;
}
table tr:nth-child(even){
background-color:lavender;
color:black;
}
.txtedit{
display: none;
width: 99%;
height: 30px;
}
</style>
</body>
</html>
