article

Saturday, April 17, 2021

Like Unlike using Python Flask Mysql and jQuery AJAX

Like Unlike using Python Flask Mysql and jQuery AJAX 

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `link` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `posts` (`id`, `title`, `content`, `link`, `timestamp`) VALUES
(4, 'What is AngularJS', 'AngularJS is a JavaScript MVC framework  developed by Google that lets you build well structured, easily testable,  declarative and maintainable front-end applications which provides solutions to  standard infrastructure concerns.', 'link-5', '2021-03-20 16:00:00'),
(5, 'What is MongoDB', 'It is a quick tutorial on MongoDB and how you can install it on your Windows OS. We will also learn some basic commands in MongoDB for example, creating and dropping a Database, Creation of a collection and some more operations related to the collection.', 'link-6', '2021-03-21 16:00:00'),
(6, 'Python Flask Load content Dynamically in Bootstrap Modal with Jquery AJAX and Mysqldb', 'Python Flask Load content Dynamically in Bootstrap Modal with Jquery AJAX and Mysqldb', 'link-6', '2021-03-20 16:00:00'),
(7, 'AutoComplete Textbox with Image using jQuery Ajax PHP Mysql and JqueryUI', 'AutoComplete Textbox with Image using jQuery Ajax PHP Mysql and JqueryUI', 'link-7', '2021-03-14 16:00:00'),
(8, 'PHP Mysql Registration Form Validation using jqBootstrapValidation with Jquery Ajax', 'PHP Mysql Registration Form Validation using jqBootstrapValidation with Jquery Ajax', 'link-8', '2021-03-20 16:00:00'),
(9, 'Python Flask Registration Form Validation using jqBootstrapValidation with Jquery Ajax and Mysql', 'Python Flask Registration Form Validation using jqBootstrapValidation with Jquery Ajax and Mysql', 'link-9', '2021-03-19 16:00:00'),
(10, 'Displaying Popups data on mouse hover using Jquery Ajax and PHP Mysql database', 'Displaying Popups data on mouse hover using Jquery Ajax and PHP Mysql database', 'link-10', '2021-03-15 16:00:00'),
(11, 'Displaying Popups data on mouse hover using Jquery Ajax and Python Flask Mysql database', 'Displaying Popups data on mouse hover using Jquery Ajax and Python Flask Mysql database', 'link-11', '2021-03-14 16:00:00');


ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

CREATE TABLE `like_unlike` (
  `id` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `postid` int(11) NOT NULL,
  `type` int(2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `like_unlike`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `like_unlike`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#app.py
from flask import Flask, request, render_template, jsonify
from flaskext.mysql import MySQL #pip install flask-mysql
import pymysql
  
app = Flask(__name__)
    
mysql = MySQL()
   
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
  
@app.route('/')
def home():
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute("SELECT * from posts")
        postslist = cursor.fetchall()
        userid = 5
        postArray = []
        for row in postslist:
            postid = row['id']
            type = -1
            #print(postid)
            cursor.execute("SELECT count(*) as cntStatus,type FROM like_unlike WHERE userid=%s AND postid=%s", (userid, postid))
            rs1 = cursor.fetchone()
            count_status = rs1['cntStatus']
            #print(count_status)
            if count_status > 0:
                type = rs1['type']
 
            cursor.execute("SELECT COUNT(*) AS cntLikes FROM like_unlike WHERE type=1 and postid=%s", postid)
            rs2 = cursor.fetchone()
            total_likes = rs2['cntLikes']
            #print(total_likes)
 
            cursor.execute("SELECT COUNT(*) AS cntUnlikes FROM like_unlike WHERE type=0 and postid=%s", postid)
            rs3 = cursor.fetchone()
            total_unlikes = rs3['cntUnlikes']
            #print(total_unlikes)
 
            if type == 1:
                txtcolor = 'color: #ffa449;' 
            else:
                txtcolor = '' 
 
            if type == 0:
                txtcolor2 = 'color: #ffa449;' 
            else:
                txtcolor2 = ''
 
            postObj = {
                    'id': row['id'],
                    'title': row['title'],
                    'content': row['content'],
                    'total_likes': total_likes,
                    'total_unlikes': total_unlikes,
                    'txtcolor': txtcolor,
                    'txtcolor2': txtcolor2}
            postArray.append(postObj)
        return render_template('index.html',postall=postArray)
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()
 
@app.route("/likeunlike",methods=["POST","GET"])
def likeunlike():
    try:
        conn = mysql.connect()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        if request.method == 'POST':
            userid = 7
            postid = request.form['postid']
            type = request.form['type']
            #print(postid)
            #print(type)
            cursor.execute("SELECT COUNT(*) AS cntpost FROM like_unlike WHERE postid=%s AND userid=%s", (postid, userid))
            rscount = cursor.fetchone()
            count = rscount['cntpost']
            #print(count)
 
            if count == 0:
                sql = "INSERT INTO like_unlike(userid,postid,type) VALUES(%s, %s, %s)"
                data = (userid, postid, type)
                conn = mysql.connect()
                cursor = conn.cursor()
                cursor.execute(sql, data)
                conn.commit()
 
                cur = conn.cursor(pymysql.cursors.DictCursor)
                cur.execute("SELECT COUNT(*) AS cntLike FROM like_unlike WHERE type=1 AND postid=%s",postid)
                rscounttotal = cur.fetchone()
                countlike = rscounttotal['cntLike']
                #print(countlike)
 
                cur = conn.cursor(pymysql.cursors.DictCursor)
                cur.execute("SELECT COUNT(*) AS cntUnlike FROM like_unlike WHERE type=0 AND postid=%s",postid)
                rscounttotalunlike = cur.fetchone()
                countUnlike = rscounttotalunlike['cntUnlike']
                #print(countUnlike)
 
                totallikeajax = countlike
                totalunlikeajax = countUnlike
            else:
                sql = "UPDATE like_unlike SET type=%s WHERE userid=%s AND postid=%s"
                data = (type, userid, postid)
                conn = mysql.connect()
                cursor = conn.cursor()
                cursor.execute(sql, data)
                conn.commit()
 
                cur = conn.cursor(pymysql.cursors.DictCursor)
                cur.execute("SELECT COUNT(*) AS cntLike FROM like_unlike WHERE type=1 AND postid=%s",postid)
                rscounttotal = cur.fetchone()
                countlike = rscounttotal['cntLike']
                #print(countlike)
 
                cur = conn.cursor(pymysql.cursors.DictCursor)
                cur.execute("SELECT COUNT(*) AS cntUnlike FROM like_unlike WHERE type=0 AND postid=%s",postid)
                rscounttotalunlike = cur.fetchone()
                countUnlike = rscounttotalunlike['cntUnlike']
                #print(countUnlike)
                 
                totallikeajax = countlike
                totalunlikeajax = countUnlike
        return jsonify({"likes":totallikeajax,"unlikes":totalunlikeajax})
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()
 
         
if __name__ == "__main__":
    app.run()
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
//templates/index.html
<!doctype html>
<html>
<head>
<title>Like Unlike using Python Flask Mysql and jQuery AJAX</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(){
    $(".like, .unlike").click(function(){
        var id = this.id;  
        var split_id = id.split("_");
        var text = split_id[0];
        var postid = split_id[1];
        var type = 0;
        if(text == "like"){
            type = 1;
        }else{
            type = 0;
        }
        $.ajax({
            url: '/likeunlike',
            type: 'post',
            data: {postid:postid,type:type},
            dataType: 'json',
            success: function(data){
                var likes = data['likes'];
                var unlikes = data['unlikes'];
                $("#likes_"+postid).text(likes);      
                $("#unlikes_"+postid).text(unlikes);  
                if(type == 1){
                    $("#like_"+postid).css("color","#ffa449");
                    $("#unlike_"+postid).css("color","lightseagreen");
                }
                if(type == 0){
                    $("#unlike_"+postid).css("color","#ffa449");
                    $("#like_"+postid).css("color","lightseagreen");
                }
            }
        });
    });
});
</script>
</head>
<body >
<div class="container" >
    <div class="row" style="padding:50px;">
        <p><h1>Like Unlike using Python Flask Mysql and jQuery AJAX</h1></p>
        <div class="content">
          {% for row in postall %} 
                    <div class="post">
                        <h1>{{row.title}}</h1>
                        <div class="post-text">
                          {{row.content}}
                        </div>
                        <div class="post-action">
                            <input type="button" value="Like" id="like_{{row.id}}" class="like" style="{{row.txtcolor}}" /> (<span id="likes_{{row.id}}">{{row.total_likes}}</span>) 
                            <input type="button" value="Unlike" id="unlike_{{row.id}}" class="unlike" style="{{row.txtcolor2}}"/> (<span id="unlikes_{{row.id}}">{{row.total_unlikes}}</span>)
                        </div>
                    </div>
            {% endfor %}  
        </div>
   </div>
</div>
<style>
.content{
    border: 0px solid black;
    border-radius: 3px;
    padding: 5px;
    margin: 0 auto;
    width: 70%;
}
.post{
    border-bottom: 1px solid black;
    padding: 10px;
    margin-top: 10px;
    margin-bottom: 10px;
}
.post:last-child{
    border: 0;
}
.post h1{
    font-weight: normal;
    font-size: 30px;
}
.post-text{
    letter-spacing: 1px;
    font-size: 15px;
    font-family: serif;
    color: gray;
    text-align: justify;
}
.post-action{
    margin-top: 15px;
    margin-bottom: 15px;
}
.like,.unlike{
    border: 0;
    background: none;
    letter-spacing: 1px;
    color: lightseagreen;
}
.like,.unlike:hover{
    cursor: pointer;
}
</style>
</body>
</html>

Posted by ednalan at 11:26 PM . Under .

Related Post