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

Download & Notes SQLite Table Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create your own project folder, e.g. D:\table, unzip the code inside this folder.
  • Open the terminal (or command line), navigate to your project folder cd D:\table.
  • As usual, create a virtual environment if you don’t want to 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 s2_create.py
  • Launch python s3_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.

 

 

TABLE FROM SQLITE DATABASE

All right, let us now walk through the steps of displaying an HTML table from an SQLite database.

 

PART 1) DUMMY USERS TABLE

s1_users.sql
-- (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.

 

 

PART 2) CREATE SQLITE TABLE

s2_create.py
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error

# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "s1_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.

 

PART 3) FLASK SERVER

3A) INITIALIZE

s3_server.py
# (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.

 

 

3B) SQLITE GET USERS

s3_server.py
# (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.

 

3C) OUTPUT USERS IN HTML TABLE

s3_server.py
# (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("s4_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.

 

 

PART 4) DATA TO HTML TABLE

templates/s4_users.html
<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.

 

USEFUL 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.

 

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!

Leave a Comment

Your email address will not be published.