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 gallery (
id serial PRIMARY KEY,
photoname VARCHAR ( 150 ) NOT NULL,
display_order INT NOT NULL,
created TIMESTAMP,
modified TIMESTAMP
);
INSERT INTO
gallery(photoname, display_order, created, modified)
VALUES
('01.jpg', '1', '2021-06-29', '2021-06-29'),
('02.jpg', '2', '2021-06-29', '2021-06-29'),
('03.jpg', '3', '2021-06-29', '2021-06-29'),
('04.jpg', '4', '2021-06-29', '2021-06-29');
https://jqueryui.com/
Sortable https://jqueryui.com/sortable/
#app.py
from flask import Flask, render_template, jsonify, request
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 main():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM gallery ORDER BY display_order")
gallery = cur.fetchall()
return render_template('index.html', gallery=gallery)
@app.route("/orderupdate",methods=["POST","GET"])
def orderupdate():
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if request.method == 'POST':
cur.execute("SELECT * FROM gallery")
number_of_rows = cur.rowcount
print(number_of_rows)
getorder = request.form['order']
order = getorder.split(",", number_of_rows)
count=0
for value in order:
count +=1
#print(count)
cur.execute("UPDATE gallery SET display_order = %s WHERE id = %s ", [count, value])
conn.commit()
cur.close()
return jsonify(order)
if __name__ == "__main__":
app.run(debug=True)
templates/index.html
//templates/index.html
<html>
<head>
<title>Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<style>
.gallery{ width:100%; float:left;}
.gallery ul{ margin:0; padding:0; list-style-type:none;}
.gallery ul li{ padding:7px; border:2px solid #ccc; float:left; margin:10px 7px; background:none; width:auto; height:auto;}
.gallery img{ width:250px;}
</style>
</head>
<body>
<div class="container">
<h2>Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database</h2>
<div>
<div class="gallery">
<ul class="reorder-gallery">
{% for row in gallery %}
<li id="{{row.id}}" class="ui-sortable-handle"><a href="javascript:void(0);"><img src="/static/images/{{row.photoname}}" alt=""></a></li>
{% endfor %}
</ul>
</div>
</div><div id="test"></div>
</div>
<script>
$(document).ready(function(){
$("ul.reorder-gallery").sortable({
update: function( event, ui ) {
updateOrder();
}
});
});
function updateOrder() {
var item_order = new Array();
$('ul.reorder-gallery li').each(function() {
item_order.push($(this).attr("id"));
});
var order_string = 'order='+item_order;
$.ajax({
method: "POST",
url: "/orderupdate",
data: order_string,
cache: false,
success: function(data){
$("#test").html(data);
}
});
}
</script>
</body>
</html>
