Effy L.H.

Posted on Apr 6, 2022 • Edited on May 11, 2022

Display recent queries on Web Page from PostgreSql database with Flask+psycopg2+Bootstrap

#webdev #tutorial #python #postgres

This is a simple tutorial for people who is just stepping into Flask framework and trying to display database queries on the screen.

GitHub repo for this tutorial:Display recent queries on Web Page from PostgreSql database with Flask+psycopg2+Bootstrap

Sample dataset

Here we have a sample database contains a table called ‘game’ which stores 18 games and their last visit records.

Goal

Our goal is to display the 5 most recent queries on the screen

Preview of the result

Preparations

Create an HTML file called index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Display recent database queries</title>
<body>
    <h1>Hello</h1>

</body>
</html>

Create a file called run.py, then define a route to render the index.html file we just created

from flask import Flask,render_template

app=Flask(__name__)

############################################
        # Routes
##########################################
@app.route('/')
def index():
    return render_template('index.html')

if __name__=='__main__':
    app.run(debug=True)

Run in the command line and the result is shown as below:

python3 run.py

Let’s get started!

Back-End

Create a file called **database.ini **in order to safely store database configurations:

[postgresql]
host=localhost
database=games
user=postgres
password=123456
port=5432

Create a file called config.py in order to safely call the configurations defined inside the database.ini

from configparser import ConfigParser

def config(filename='/Users/Project/DisplayQueries_With_Flask_jQuery_Bootstrap_PostgreSQL/database.ini', section='postgresql'):
    parser = ConfigParser()
    parser.read(filename)

    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return db

Let’s update the run.py

Import needed modules: config(we just created in step 1) , psycopg2 (psycopg2 is a popular PostgreSQL database adapter )
Connect with our local database by using the config module we defined before, then execute a SQL statement to extract the 5 most recent rows from the database(sort the records according to the ‘last_visit’ column in descending order)

from flask import Flask,render_template
#1.
from config import config
import psycopg2
app=Flask(__name__)


###########################################
        #Route
#########################################

@app.route('/')
def index():
    conn=None
    rowResults=None
    #2.
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur=conn.cursor()
        order_statement='SELECT * FROM game ORDER BY last_visit DESC LIMIT 5;'
        cur.execute(order_statement)
        rowResults=cur.fetchall()
        print(rowResults)
    except(Exception, psycopg2.Error) as error:
        print(error)

    finally:
        if conn is not None: 
            cur.close()
            conn.close()
            print("database connection is now closed")
    return render_template('index.html')

if __name__=='__main__':
    app.run(debug=True)

Check the results we got

The 5 records we need are as below:

The 5 records we actually got:

(Which means our SQL operations successfully executed!)

Outputs:
[
(4, 'The Legend of Zelda: Breath of the Wild', datetime.datetime(2021, 4, 14, 22, 6, 12)), 
(3, 'Horizon Forbidden West', datetime.datetime(2021, 4, 5, 2, 43, 2)), 
(8, 'The Elder Scrolls V: Skyrim', datetime.datetime(2021, 3, 27, 21, 31, 4)), 
(5, 'The Witcher 3: Wild Hunt', datetime.datetime(2021, 3, 25, 6, 8, 23)), 
(16, 'Final Fantasy', datetime.datetime(2021, 3, 21, 17, 52, 47))
]

Since now, we’ve finished all the back-end scripts, let’s move on to the front-end and display the results on the web page:

Front-End

Add a line at the end of the index() function in the run.py, the render_template method will help us to render the defined index.html on the web page, meanwhile, we pass in a variable called “recentRecords” and assign the ‘rowResults’ (query results we retreived from the databse) onto it.

from flask import Flask,render_template
from config import config
import psycopg2
app=Flask(__name__)


###########################################
        #Route
#########################################

@app.route('/')
def index():
    conn=None
    rowResults=None
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur=conn.cursor()
        order_statement='SELECT * FROM game ORDER BY last_visit DESC LIMIT 5;'
        cur.execute(order_statement)
        rowResults=cur.fetchall()

    except(Exception, psycopg2.Error) as error:
        print(error)

    finally:
        if conn is not None: 
            cur.close()
            conn.close()
            print("database connection is now closed")
    return render_template('index.html',recentRecords=rowResults)

if __name__=='__main__':
    app.run(debug=True)

Render query results “recentRecords” by using Jinja’s for loops syntax
Control flow: if the “recentRecords” is equals to an empty array [], displaying a text “Not enough data” on the screen. Otherwise, displaying a bootstrap table with values we got from the database.
About the style of table, I just simply copy&paste the bootstrap default CSS classes: “table-hover table-dark table-striped”, or you can reference the official document to choose the style you like: https://getbootstrap.com/docs/5.1/content/tables/

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Display recent database queries</title>
    <!-- CSS stylesheet -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">
<body>
    <table class="table table-hover table-dark table-striped" cellspacing="0">
        <thead>
        <tr>
            <th scope="col">#</th>
            <th scope="col" style="text-align:left">Game</th>
            <th scope="col">Visit Date</th>
        </tr>
        </thead>
        <tbody>
        {% if recentRecords==[] %}
        <p>
            Not enough data
        </p>
        {% else %}
        {% for i in range(0,5) %}
        <tr>
            <th scope="row">{{i+1}}</th>
            <td align="left">
                <p>{{recentRecords[i][1]}}</p>
            </td>
            <td>
                <p>{{recentRecords[i][2]}}</p>
            </td>
        </tr>
        {% endfor %}
        {% endif %}

        </tbody>
    </table>
</body>
</html>

Local Test

Let’s run the script and access the address http://127.0.0.1:5000 on your browser:
python3 run.py

It works!🥳

The Real Project Example

Another motivation I wrote this tutorial is: I just extended my launched Web App with the same functionality mentioned in this tutorial, which you can check it at:

App link: link
And I wrote an article about the Web App: link

That's all for this tutorial. Thanks!
If you have any questions, feel free to comment.

DEV Community

Display recent queries on Web Page from PostgreSql database with Flask+psycopg2+Bootstrap

Sample dataset

Goal

Preview of the result

Preparations

Let’s get started!

Back-End

Front-End

Local Test

It works!🥳

The Real Project Example

Top comments (0)

Read next

How to Create a Dynamic Popover with CSS and JavaScript 🚀

From Web2 to Web3: A Developer’s Guide to the Shift

How to Set Git Username and Password in Git Bash?

Synchronous Applications