Search & Display Results From Database (Python Flask)

Welcome to a tutorial on how to search and display results from the database using Python Flask and SQLite. So you need to add a search box to your website? Well, it actually a 2-steps process in the simplest design.

  1. Add an HTML search form to the page – Text box and submit button.
  2. Once submitted, do a SELECT * FROM `TABLE` WHERE `FIELD` LIKE '%SEARCH%' SQL query and output the results in HTML.

Yep, that’s all. But just how is this done exactly? Let us walk through an example in this guide – Read on!

ⓘ 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 Python Search 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:\search, unzip the code inside this folder.
  • Open the terminal (or command line), navigate to your project folder cd D:\search.
  • 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 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 the 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.

 

 

 

PART 1) DUMMY USERS TABLE

s1_users.sql
-- (A) USERS TABLE
CREATE TABLE users (
  uid INTEGER,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  tel TEXT NOT NULL,
  PRIMARY KEY("uid" AUTOINCREMENT)
);
 
CREATE INDEX `idx_name`
  ON `users` (`name`);

CREATE UNIQUE INDEX `idx_email`
  ON `users` (`email`);
-- (B) DUMMY DATA
INSERT INTO "users" VALUES
(1,'Jo Doe','jo@doe.com','465785'),
(2,'Joa Doe','joa@doe.com','123456'),
(3,'Job Doe','job@doe.com','234567'),
(4,'Joe Doe','joe@doe.com','345678'),
(5,'Jog Doe','jog@doe.com','578456'),
(6,'Joh Doe','joh@doe.com','378945'),
(7,'Joi Doe','joi@doe.com','456789'),
(8,'Jon Doe','jon@doe.com','987654'),
(9,'Jor Doe','jor@doe.com','754642'),
(10,'Joy Doe','joy@doe.com','124578');

For a start, here is a simple dummy users table to work with in this example.

  • uid User ID. Primary key and auto-increment.
  • name The user’s name, indexed.
  • email The user’s email, unique.
  • tel Telephone number.

 

 

PART 2) CREATE THE DATABASE

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!")

Not much of a mystery here. We read s1_users.sql and create the actual database file users.db.

 

PART 3) FLASK SERVER

3A) MODULES & SETTINGS

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
 
# (B) HELPER FUNCTION - SEARCH USERS
def getusers(search):
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute(
    "SELECT * FROM `users` WHERE `name` LIKE ? OR `email` LIKE ?",
    ["%"+search+"%", "%"+search+"%"]
  )
  results = cursor.fetchall()
  conn.close()
  return results

The top half of the Flask server script shouldn’t need a lot of explanation.

  • (A) Load the required modules and define a bunch of server settings.
  • (B) A helper function to do a search on the user database.

 

 

3B) VIEWS

s3_server.py
# (C) DEMO SEARCH PAGE
@app.route("/", methods=["GET", "POST"])
def index():
  # (C1) SEARCH FOR USERS
  if request.method == "POST":
    data = dict(request.form)
    users = getusers(data["search"])
  else:
    users = []
 
  # (C2) RENDER HTML PAGE
  return render_template("s4_users.html", usr=users)
 
# (D) START
if __name__ == "__main__":
  app.run(HOST_NAME, HOST_PORT)

No need to panic, we are only serving a single page at / here. Take a look at how this works.

  • (C1) When the HTML search form is submitted, we will use the helper function getusers() to do the database search.
  • (C2) We pass the search results into the HTML template for rendering.
  • (D) Start the Flask server. Captain Obvious at your service.

 

PART 4) HTML SEARCH PAGE

templates/s4_users.html
<!-- (A) SEARCH FORM -->
<form method="post">
  <input type="text" name="search" required/>
  <input type="submit" value="Search"/>
</form>
 
<!-- (B) OUTPUT SEARCH RESULTS -->
{% if usr | length != 0 %}
<table id="demo">
  {% for u in usr %}
  <tr>
    <td>{{ u[0] }}</td>
    <td>{{ u[1] }}</td>
    <td>{{ u[2] }}</td>
  </tr>
  {% endfor %}
</table>
{% else %}
<div id="demo">No search results.</div>
{% endif %}

Finally, the HTML page itself. It’s nothing more than a “regular search form”, and we generate the search results in a table.

 

 

USEFUL BITS & LINKS

That’s all for the search example, and here are a few extra bits that may be useful to you.

 

INDEX YOUR SEARCHABLE FIELDS!

As you can see, we have created an index on the name and email columns in this example – CREATE INDEX `NAME` ON `TABLE` (`COL`). Many beginners will probably brush this aside and not think too much about the “confusing index”.

To set things straight – Indexes are important when it comes to search performance. In layman’s terms, it’s like creating a book catalog for a library. Is search book-by-book faster? Or referring to the catalog faster? The answer is obvious, so always index your searchable columns.

 

ADOPT A “BETTER” DATABASE

Yes, SQLite works great. It is convenient and does not require any installation. But it will eventually run into performance and security issues in a cloud (Internet/Intranet) environment. So take your time to pick up a “professional database” – MySQL, MSSQL, PostgreSQL, MongoDB, and whatever else. It is not as difficult as some think, and it is beneficial for you in the long run.

 

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.