PostgreSQL Flask Database- Display Data in a Web Page

In the previous post you learned about how to insert data in PostgreSQL database from a Flask web application. In this post we will take another step in developing web application with Flask. We will fetch Flask Database stored in PostgreSQL and display it in a webpage.

For this task we need to have basic understanding of SELECT SQL statement. We have already inserted data in the booklist table in Books database. We will use same data to display it in an HTML table.

Follow the following steps to complete this interesting task of developing a web application using Flask Database PostgreSQL

Create a Route in application.py to fetch Flask Database data

The first step is to create a route in application.py file to fetch data from the database table. For this we will use the well acquainted SELECT SQL statement. In this route the select statement will be used to fill a list of tuples with table field names and stored data as key and value pairs. This list will be passed as a parameter to render_template function along with the name of the template file saved as HTML page.

@app.route("\bookList")
Def booklist():
	books=db.execute("SELECT * FROM booklist order by bookid")
    return render_template("BookList.html", books=books)

Create an HTML page to Render Fetched Data

When the route is typed in the address bar of the browser, the associated function will execute and return the list of tuples of books in key-value pairs of the column names of table and the stored values.

The BookList HTML file must be stored in the templates folder of your application. You have to access the list of books with a loop using Jinja syntax for looping statement. The for loop allows you to browse through the list of tuples returned by execute method and display in an HTML table.

The HTML table is used to display data. Every time the for loop in Jinja syntax {% for book in books %} is executed, the next row is fetched from list of tuples called books and stores in book tuple.

Every attribute of a book stored in table- isbn,title,author and year, can be accessed as book.isbn, book.title, book.author and book.year.  This is used as the text value to be displayed within <TD></TD> tags to display in the table cell.

<table class="table">
  <thead>
    <tr>
      <th scope="col">#</th>
      <th scope="col">ISBN</th>
      <th scope="col">Title</th>
      <th scope="col">Author</th>
	  <th scope="col">Year</th>
	  <th scope="col">Star Rating</th>
    </tr>
  </thead>
	{% for book in books %}
	<tr>
	<th scope="row">{{book.bookid}}</th>
	<td>{{book.isbn}}
	</td>
	<td>{{book.title}}
	</td>
	<td>{{book.author}}
	</td>
	<td>{{book.year}}
	</td>
	</tr>
	{% endfor %}
</table>

We have simply displayed the book list data from Flask Database in HTML table. In the next post we will modify the above code to Modify and Delete buttons in each table row. On clicking these buttons book details of the selected book will be displayed to update or get deleted.

admin