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
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
//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>
