Welcome to a tutorial on how to create a table from an SQLite Database in Python Flask. So you have some data in the database that you wish to display in a “nice HTML table”? Well, it really isn’t that difficult. Read on for the step-by-step example!
TABLE OF CONTENTS
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
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.
SQLITE DATABASE TO HTML TABLE
All right, let us now walk through the steps of displaying an HTML table from an SQLite database.
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) DUMMY DATABASE
1A) USERS TABLE
-- (A) USERS
CREATE TABLE `users` (
`user_id` INTEGER NOT NULL,
`user_name` TEXT NOT NULL,
`user_email` TEXT DEFAULT NULL,
PRIMARY KEY("user_id" AUTOINCREMENT)
);
-- (B) DUMMY USERS
INSERT INTO `users`
(`user_name`, `user_email`)
VALUES
("Jo Doe", "jo@doe.com"),
("Job Doe", "job@doe.com"),
("Joe Doe", "joe@doe.com"),
("Jog Doe", "jog@doe.com"),
("Joi Doe", "joi@doe.com"),
("Jol Doe", "jol@doe.com"),
("Jon Doe", "jon@doe.com"),
("Jos Doe", "jos@doe.com"),
("Jou Doe", "jou@doe.com"),
("Joy Doe", "joy@doe.com");
This is the dummy table and data that we will use to generate the HTML table later. Shouldn’t need much explanation, only has 3 fields:
user_id
The user ID, primary key, and auto-increment.user_name
Name of the user.user_email
Email of the user.
1B) CREATE SQLITE DATABASE
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "S1A_users.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 have a simple script to create the SQLite database itself. Feel free to delete this after running… It pretty much serves no other purpose other than creating the users.db
file.
STEP 2) FLASK SERVER
2A) INITIALIZE
# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
import sqlite3
# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
DBFILE = "users.db"
app = Flask(__name__)
# app.debug = True
The first section of the Flask server script should be pretty self-explanatory. We are just importing the required modules and specifying some settings.
2B) SQLITE GET USERS
# (B) HELPER - GET ALL USERS FROM DATABASE
def getusers():
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
cursor.execute("SELECT * FROM `users`")
results = cursor.fetchall()
conn.close()
return results
Next, we have a simple helper function to connect to the user database and get all the users.
2C) OUTPUT USERS IN HTML TABLE
# (C) DEMO PAGE - SHOW USERS IN TABLE
@app.route("/")
def index():
# (C1) GET ALL USERS
users = getusers()
# print(users)
# (C2) RENDER HTML PAGE
return render_template("S3_users.html", usr=users)
# (D) START
if __name__ == "__main__":
app.run(HOST_NAME, HOST_PORT)
Lastly, Captain Obvious is at your service once again. Get all the users from the database, and pass it into the HTML page for rendering.
STEP 3) DATA TO HTML TABLE
<table id="demo">
{% for u in usr %}
<tr>
<td>{{ u[0] }}</td>
<td>{{ u[1] }}</td>
<td>{{ u[2] }}</td>
</tr>
{% endfor %}
</table>
Finally, we only have to loop through the users and generate an HTML table.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
SQLITE WORKS BUT IS NOT GREAT
Yes, SQLite is simple and convenient. It is great for learning and tutorials, but not good for real-life applications.
- SQLite is file-based.
- In a cluster or cloud server setup, this becomes a restriction – Are we going to clone the database file across all servers?
- The performance is not good for massive datasets either.
So yes, please don’t be lazy. Follow up with your studies and pick up a better database – MYSQL, MongoDB, PostgreSQL, MSSQL, etc…
LINKS & REFERENCES
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!