article

Saturday, July 24, 2021

Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database

Drag and Drop Reorder with Jquery Ajax Jquery-UI Python Flask and PostgreSQL Database

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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
//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>

Posted by ednalan at 2:57 AM . Under , .

Related Post