Display recent queries on Web Page from PostgreSql database with Flask+psycopg2+Bootstrap
Tags: webdev, tutorial, python, postgres
Published: 2022-04-06 08:25:12


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](https://medium.com/pythonistas/display-recent-queries-on-web-page-from-postgresql-database-with-flask-psycopg2-bootstrap-467ced8583c5)

## Sample dataset 

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

![](https://cdn-images-1.medium.com/max/2240/1*hqh6rGhLOHvUPwKyZOFI-w.png)

## Goal

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

### Preview of the result

![](https://cdn-images-1.medium.com/max/4112/1*7mn0cMd1sc0NTstiJ5ScRg.png)

## Preparations

* Create an HTML file called **index.html**

```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

```py
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`

![](https://cdn-images-1.medium.com/max/3212/1*GOyxqGPJuBQycXFBmEJrkw.png)



## 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**

```py
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** 

1. Import needed modules: **config**(we just created in step 1) , **psycopg2** (psycopg2 is a popular PostgreSQL database adapter )

2. 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**)

```py
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:

![](https://cdn-images-1.medium.com/max/2156/1*Aggtqr1HuUg2DJ46B_l6BQ.png)

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.

```py
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/](https://getbootstrap.com/docs/5.1/content/tables/)

```py
<!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](http://127.0.0.1:5000) on your browser:
`python3 run.py`
    
## It works!🥳

![](https://cdn-images-1.medium.com/max/4112/1*7mn0cMd1sc0NTstiJ5ScRg.png)



## 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](http://www.syllabuscraper.com/)
**And I wrote an article about the Web App:** [link](https://medium.com/@effylh/building-an-online-course-syllabus-scraper-with-flask-udemyapi-postgresql-on-heroku-262b727e228b?source=your_stories_page----------------------------------------)

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