Introduction
In this post we will see how to create voting or rating system using Flask, MySQL and AJAX. We post tutorials, articles or blogs either on our own blog site or as a guest user to other sites. Sometimes we need to let user rate on our tutorials or articles or blogs so that we know how much that blog is famous or how much that blog is intended to be accepted by readers.
In this example I have given one sample content on which you can rate this.
The rating/voting system is calculated by taking average on accumulated votes on five stars. Once you give vote/rate from your system you cannot give vote or rate next time.
As soon as you cast your vote or rate you can see it instantly the updated vote as well as you can also see what vote/rate you have cast.
When you mouse over on stars you can see the highlighted stars on how many stars vote/rate you want to cast.
Prerequisites
Python 3.8.1, Flask, MySQL 8.0.17
Create MySQL Table
As we are going to store rate or vote into MySQL database, so we need a table for it. So we will create a table called blog_vote under roytuts database.
CREATE TABLE IF NOT EXISTS `blog_vote` (
`vote_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`blog_vote` float NOT NULL,
`blog_id` int(10) unsigned NOT NULL,
`ip_address` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`vote_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Create Project Directory
Create a project root directory called python-flask-mysql-jquery-ajax-star-rating-system as per your chosen location.
We may not mention the project’s root directory name in the subsequent sections but we will assume that we are creating files with respect to the project’s root directory.
Configure Flask
We here configure application through flask framework. Create a file called app.py with the below code.
from flask import Flask
app = Flask(__name__)
app.secret_key = "secret key"Database Configuration
We create the below db_config.py Python script to setup the MySQL database configurations for connecting to database and storing user information into employee table under roytuts database.
We need to configure database connection with flask module and that’s why we have imported app module and setup the MySQL configuration with flask module.
Make sure to change the database configuration values according to your database setup.
from app import app
from flaskext.mysql import MySQL
mysql = MySQL()
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'roytuts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)Required Database Queries
We are going to build some queries which will help us get the vote or rate for a particular blog or article. We will also build the query to insert the rate or vote for a particular user.
We create a dao.py file to write the below code.
import pymysql
from flask import request
from db_config import mysql
def get_blog_rating(blog_id):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = "SELECT COUNT(DISTINCT(vote_id)) total_rows, IFNULL(SUM(blog_vote),0) total_rating FROM blog_vote WHERE blog_id=%s LIMIT 1"
sql_where = (blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
return row
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def get_blog_rating_from_ip(blog_id):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
ip = request.remote_addr
sql = "SELECT vote_id as vote_id FROM blog_vote WHERE ip_address=%s AND blog_id=%s LIMIT 1"
sql_where = (ip, blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
if row:
vote_id = row['vote_id']
sql = "SELECT IFNULL(SUM(blog_vote),0) total_rating FROM blog_vote WHERE blog_id=%s LIMIT 1"
sql_where = (blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
rating = row['total_rating']
rating = rating
return int(round(rating, 0))
else:
return 0
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def rate_blog(blog_id, rate):
conn = None
cursor = None
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
ip = request.remote_addr
sql = "SELECT vote_id FROM blog_vote WHERE ip_address=%s AND blog_id=%s LIMIT 1"
sql_where = (ip, blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
if row:
vote_id = row['vote_id']
sql = "SELECT IFNULL(SUM(blog_vote),0) total_rating FROM blog_vote WHERE vote_id=%s AND blog_id=%s LIMIT 1"
sql_where = (vote_id, blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
rating = row['total_rating']
rating = rating
return int(round(rating, 0))
else:
sql = "INSERT INTO blog_vote(blog_vote, blog_id, ip_address) VALUES(%s, %s, %s)"
sql_where = (rate, blog_id, ip,)
cursor.execute(sql, sql_where)
conn.commit()
sql = "SELECT IFNULL(SUM(blog_vote),0) total_rating FROM blog_vote WHERE blog_id=%s LIMIT 1"
sql_where = (blog_id,)
cursor.execute(sql, sql_where)
row = cursor.fetchone()
rating = row['total_rating']
rating = rating
return int(round(rating, 0))
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()Build Endpoint URLs
We will create endpoint URLs for showing UI (user interface) using flask template. We will also build a REST API which will be called using AJAX technique from jQuery language.
import dao
#import pymysql
from app import app
from db_config import mysql
from flask import flash, render_template, request, redirect, url_for, jsonify
@app.route('/rate', methods=['POST'])
def rate_blog():
blog_id = request.json['blog_id']
rate = request.json['rate']
# validate the received values
if blog_id and rate:
curr_rate = dao.rate_blog(blog_id, rate)
result = dao.get_blog_rating(blog_id)
vote_rows = int(result['total_rows'])
rating = result['total_rating']
vote_rate = int(round(rating/(vote_rows if vote_rows > 0 else 1), 0))
vote_dec_rate = int(round(vote_rate, 0))
curr_rating = int(round(curr_rate, 0))
return jsonify({'vote_rows' : vote_rows, 'vote_rate' : vote_rate, 'vote_dec_rate' : vote_dec_rate, 'vote_curr_rate' : curr_rating})
else:
resp = jsonify({'message' : 'Bad Request - invalid credendtials'})
resp.status_code = 400
return resp
@app.route('/')
def home():
#the hard-coded blog id value 1 should come from UI
blog_id = 1;
result = dao.get_blog_rating(blog_id)
vote_rows = int(result['total_rows'])
rating = result['total_rating']
vote_rate = int(round(rating/(vote_rows if vote_rows > 0 else 1), 0))
vote_dec_rate = int(round(vote_rate, 0))
vote_ip_rate = dao.get_blog_rating_from_ip(blog_id)
return render_template('star-rate-vote.html', overall_rows=vote_rows, overall_rate=vote_rate, overall_dec_rate=vote_dec_rate, ip_rate=vote_ip_rate)
if __name__ == "__main__":
app.run()Template or View File
We have the below template or view file called star-rate-vote.html and kept under the standard directory templates.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8"/>
<title>Star Rating/Voting System using Python Flask MySQL AJAX</title>
<!--[if IE]> <script> (function() { var html5 = ("abbr,article,aside,audio,canvas,datalist,details," + "figure,footer,header,hgroup,mark,menu,meter,nav,output," + "progress,section,time,video").split(','); for (var i = 0; i < html5.length; i++) { document.createElement(html5[i]); } try { document.execCommand('BackgroundImageCache', false, true); } catch(e) {} })(); </script> <![endif]-->
<script type="text/javascript" src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script type="text/javascript" src="{{ url_for('static', filename='js/blog_rating.js') }}"></script>
<link type="text/css" rel="stylesheet" href="{{ url_for('static', filename='css/blog_rating.css') }}"/>
</head>
<body>
<div class='singlepost'>
<div class='fullpost clearfix'>
<div class='entry'>
<h1 class='post-title'>
This is a sample blog content
</h1>
<p>
Posted By: roytuts.com,
Posted On: 25-08-2015
</p>
<input type="hidden" name="blog_content_id" id="blog_content_id" value="1"/>
<div id="ajax_vote">
<div class="r">
<div class="rating">
{% if overall_rate > 0 %}
{% for i in range(1, overall_rate + 1) %}
<div class="star" id="{{i}}"></div>
{% endfor %}
{% endif %}
</div>
<div class="transparent">
<div class="star" id="1"></div>
<div class="star" id="2"></div>
<div class="star" id="3"></div>
<div class="star" id="4"></div>
<div class="star" id="5"></div>
<div class="votes">{{overall_dec_rate}}/5, {{overall_rows if overall_rows > 0 else 0}} {{'votes' if overall_rows > 1 else 'vote' }} {% if ip_rate %}<strong>You rated this: <span style="color:#39C;">{{ip_rate}}</span></strong>{% endif %}</div>
</div>
</div>
</div>
<br/>
<p style="text-align: justify;">
The topic of blogging seems to come up a lot in our social media training workshops. The benefits of a quality blog are obvious – fresh content is good for your readers and your rankings. Blogs are easy to set up and even easier to update. We often tell people that if they can use Microsoft Word… they can update a blog.
As easy as they are to set up, they can be difficult to maintain. A good blog is filled with relevant, timely content that is updated on a regular basis. New bloggers often start out with a bang but then fizzle out when they realize that creating content can be challenging.
</p>
</div>
</div>
</div>
</body>
</html>jQuery AJAX
The below file blog_rating.js is kept under static/js folder with the below code.
Here mainly three events are fired from the UI – on mouse over, on mouse click and on mouse out.
On mouse over, we get the actual number of stars when hovered on stars.
On mouse click, we call the REST API to apply the start rating or voting.
On mouse out, you will see the actual star rating or voting you casted.
$(function () {
$(".star").on("mouseover", function () { //SELECTING A STAR
$(".rating").hide(); //HIDES THE CURRENT RATING WHEN MOUSE IS OVER A STAR
var d = $(this).attr("id"); //GETS THE NUMBER OF THE STAR
//HIGHLIGHTS EVERY STAR BEHIND IT
for (i = (d - 1); i >= 0; i--) {
$(".transparent .star:eq(" + i + ")").css({"opacity": "1.0"});
}
}).on("click", function () { //RATING PROCESS
var blog_id = $("#blog_content_id").val(); //GETS THE ID OF THE CONTENT
var rate = $(this).attr("id"); //GETS THE NUMBER OF THE STAR
$.ajax({
method: "POST",
contentType: 'application/json;charset=UTF-8',
data: JSON.stringify({'blog_id': blog_id, 'rate': rate}),
url: "/rate", //CALLBACK
success: function (e) {
var stars = '';
for(var i=1;i<=e.vote_rate;i++){
stars += '<div class="star" id="' + i + '"></div>';
}
var str = '<div class="r"><div class="rating">' + stars + '</div><div class="transparent"><div class="star" id="1"></div><div class="star" id="2"></div><div class="star" id="3"></div><div class="star" id="4"></div><div class="star" id="5"></div><div class="votes">(' + e.vote_dec_rate + '/5, ' + e.vote_rows + ' ' + (e.vote_rows > 1 ? ' votes' : ' vote') + ') ' + (e.vote_curr_rate > 0 ? '<strong>You rated this: <span style="color:#39C;">' + e.vote_curr_rate + '</span></strong>' : '') + '</div></div></div>'
$("#ajax_vote").html(str); //DISPLAYS THE NEW RATING IN HTML
},
error: function (e) {
console.log(e);
}
});
}).on("mouseout", function () { //WHEN MOUSE IS NOT OVER THE RATING
$(".rating").show(); //SHOWS THE CURRENT RATING
$(".transparent .star").css({"opacity": "0.25"}); //TRANSPARENTS THE BASE
});
});Image File
We have an image file star.png for star and is kept under static/img folder.
CSS File
We have applied some basic styles on the stars. The style is written into blog_rating.css under static/css folder.
/*HOLD THE RATING AND THE BASE TOGETHER*/
.r {
position: relative;
} /*MUST BE ABSOLUTE TO STACKED*/
.rating, .transparent {
position: absolute;
}
.rating {
z-index: 1;
}
.star {
background: url("../img/star.png");
cursor: pointer; float: left !important; /*KEEPS THE STAR NEXT TO EACH OTHER*/
height: 20px; width: 20px;
}
.transparent .star {
opacity: .25; /*BASE STARS ARE TRANSPARENT UNTIL MOUSEOVER*/
}
.rating .star {
opacity: 1.0; /*CURRENT RATING IS VISIBLE UNTIL MOUSEOVER*/
}
.votes {
float: left; /*KEEPS THE NUMBER OF VOTES NEXT TO THE RATING & BASE*/
}That’s all about the coding part. Now we will test our application.
Testing the Application
When you run the application by hitting the URL http://localhost:5000/ and you will see that your blog has not been rated or voted yet.
When you mouse over stars, you will see the transparent color becomes deep over stars:
When you rate the blog, you will see the final rating or voting. If you try to vote or rate next time from the same system, you won’t be able to rate or vote.
Finally you will also see a row entry in the database table:
Source Code
Thanks for reading.