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
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
The “usual stuff”:
- Create a virtual environment
virtualenv venv
. - 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.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
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
EXAMPLE CODE DOWNLOAD
Click here for the source code on GitHub gist, 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.
EXTRA BITS & LINKS
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!