Display A Table From SQLite Database In 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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

 

QUICK NOTES

• Create a project folder, e.g. D:\table, unzip the code inside this folder.
• Navigate to the project folder in the command line cd D:\table, create a virtual environment to not mess up your other projects.
  • virtualenv venv
  • Windows – venv\scripts\activate
  • Mac/Linux – venv/bin/activate
• Get all the required packages – pip install flask
• Create the database python S1B_create.py
• Launch python S2_server.py and access http://localhost.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

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