Display SQLite Data In HTML Table With Python Flask

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

 

 

 

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

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.

 

 

 

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.

 

 

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

• Python Flask
• SQLite