Welcome to a tutorial on how to create a star rating system with Python Flask and SQLite. Want to open a product, video, audio, or post for user ratings? Well, here’s a quick star rating example that I have made for beginners – Read on!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
PYTHON FLASK STAR RATING
All right, let us now get into the details of creating a simple star rating system with Python Flask and SQLite.
QUICK SETUP
- Create a virtual environment
virtualenv venv
and activate it –venv\Scripts\activate
(Windows)venv/bin/activate
(Linux/Mac) - Install required libraries –
pip install flask
- For those who are new, the default Flask folders are –
static
Public files (JS/CSS/images/videos/audio)templates
HTML pages
STEP 1) THE DATABASE
1A) STAR RATING
-- (A) STAR RATING TABLE
CREATE TABLE `star_rating` (
`product_id` INTEGER NOT NULL,
`user_id` INTEGER NOT NULL,
`rating` INTEGER NOT NULL DEFAULT '1',
PRIMARY KEY (`product_id`,`user_id`)
);
-- (B) DUMMY DATA
INSERT INTO `star_rating` (`product_id`, `user_id`, `rating`) VALUES
(1, 900, 1),
(1, 901, 2),
(1, 902, 3),
(1, 903, 4),
(1, 904, 5);
Let us start with the database, and this should be self-explanatory.
product_id
The product that is being rated. This can also be the video id, audio id, post id, or whatever you are rating.user_id
The user who made this rating.rating
The number of stars.
1B) CREATE THE DATABASE
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
# (B) DATABASE + SQL FILE
DBFILE = "stars.db"
SQLFILE = "S1A_database.sql"
# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
os.remove(DBFILE)
# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")
Next, we simply read the above SQL file, and create the actual database itself.
STEP 2) STARS RATING LIBRARY
# (A) LOAD SQLITE MODULE
import sqlite3
DBFILE = "stars.db"
# (B) HELPER - EXECUTE SQL QUERY
def query(sql, data):
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
conn.close()
# (C) HELPER - FETCH
def fetch(sql, data=[]):
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
cursor.execute(sql, data)
results = cursor.fetchone()
conn.close()
return results
# (D) SAVE/UPDATE USER STAR RATING
def save(pid, uid, stars):
query(
"REPLACE INTO `star_rating` (`product_id`, `user_id`, `rating`) VALUES (?,?,?)",
[pid, uid, stars]
)
return True
# (E) GET USER STAR RATING FOR PRODUCT
def get(pid, uid):
res = fetch(
"SELECT * FROM `star_rating` WHERE `product_id`=? AND `user_id`=?",
[pid, uid]
)
return 0 if res is None else res[2]
# (F) GET AVERAGE RATING FOR PRODUCT
def avg(pid):
res = fetch("""
SELECT ROUND(AVG(`rating`), 2) `avg`, COUNT(`user_id`) `num`
FROM `star_rating`
WHERE `product_id`=?""", [pid])
return (0, 0) if res[0] is None else res
With the database in place, the next step is to create a library to work with it. Looks scary and massive, but keep calm and study closely.
- (A) Load the SQLite module. Doh.
- (B & C)
query()
Helper function to run an SQL query.fetch()
Helper function to run a SELECT query, get a single row of data. - (D)
save()
Save/update the user’s star rating. - (E)
get()
Get the user’s star rating for the specified product. - (F)
avg()
Get the average star rating for the specified product.
That’s all. Feel free to modify this library, and add more of your own functions as required.
STEP 3) FLASK SERVER
# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
import S2_star_lib as starz
# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
app = Flask(__name__)
# app.debug = True
# (A3) FIXED PRODUCT & USER ID FOR THIS DEMO
pid = 1
uid = 999
# (B) FEEDBACK HTML PAGE
@app.route("/")
def index():
# (B1) GET AVERAGE + USER STARS
astar = starz.avg(pid)
ustar = starz.get(pid, uid)
# (B2) RENDER HTML PAGE
return render_template("S4A_page.html", astar=astar, ustar=ustar)
# (C) SAVE STARS
@app.route("/save/", methods=["POST"])
def save():
data = dict(request.form)
starz.save(pid, uid, data["stars"])
return make_response("OK", 200)
# (D) START
if __name__ == "__main__":
app.run(HOST_NAME, HOST_PORT)
Not going to explain this line-by-line, and this Flask server script should be pretty self-explanatory too.
- A whole bunch of settings.
- Get the star rating from the database, and pass them into the HTML template.
- Handle the “save stars” request when the user rates the product/audio/video/content.
- Start the Flask server.
STEP 4) DUMMY PRODUCT PAGE
<!-- (A) DUMMY PRODUCT -->
<div class="product">
<img class="img" src="static/hamburger.png">
<div class="name">Hamborger</div>
<div class="desc">Piece of meat and veggies with 2 pieces of bun.</div>
<div class="avg">Rating: {{ astar[0] }} ({{ astar[1] }} users)</div>
<div class="rate">Your rating: <div id="demo"></div></div>
</div>
<!-- (B) INIT STAR RATING -->
<script>
starry({
target: document.getElementById("demo"),
max: 5,
now: {{ ustar }},
click : stars => {
let data = new FormData();
data.append("stars", stars);
fetch("/save/", {
method: "post",
body: data
})
.then(res => res.text())
.then(txt => {
if (txt == "OK") { location.reload(); }
else { alert(txt); }
});
}
});
</script>
Remember that we passed the star ratings into this HTML page from earlier? We are just rendering it into “a nice page” here. The star rating is driven by a Javascript widget that I have made, will leave links below.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
PLENTY OF IMPROVEMENTS TO DO
Before the trolls go on with their usual “dumb and stupid” comments – This is only a barebones example and starting point. There are plenty of things that need to be done.
- SQLite is great for learning, but it is better to use a “professional database” for production servers – PostgreSQL, MySQL, Redis, or MongoDB.
- To prevent spam, it is best to reserve the stars rating for registered users only.
- We can also generate a random unique ID in the cookie to track public users. But anyone who knows well enough can just clear the cookie and spam the system.
- The page reloads whenever the user updates the number of stars; Refreshing the average rating on every update can be very heavy on a busy server.
- Consider other techniques for caching the average rating, maybe create another “average rating” table, and update it every hour only.
LINKS & REFERENCES
- Python Flask
- SQLite
- Simple Javascript Star Rating – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
Thanks! This very useful.
I wish it was applicable for a blog article too.