From Postgres to Python to HTML

Introduction

In this tutorial we will go step by step through how to retrieve and move data from Postgres for Python to an HTML page. During this learning process, we will learn how to use various functions and features in PostgreSQL and Python, including:

  • import: How to install libraries for Python via PIP. For this exercise you will need to have Flask and Psycopg2 installed.
  • render_template: This Python Flask function is used to show a dynamic html page to the user. We’ll explore use of this function in depth.

Prerequisites

  • Knowledge of how to write simple PostgreSQL queries, including use of SELECT FROM and WHERE statements.
  • Some beginner experience with HTML, including use of div tags.
  • Understanding of what the following means: text types, string types, and integer types.

Read from Postgres to Python

Connection to Postgres

First we need to establish a connection to the Postgres database. We’ll use Python’s psycopg2 library, which provides us with a connection method and cursor method.

import psycopg2
t_host = "database address"
t_port = "5432" #default postgres port
t_dbname = "database name"
t_software = "database software name"
t_pw = "database software password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, software=t_software, password=t_pw)
db_cursor = db_conn.cursor()

Analysis

  • import psycopg2: This is a Python library that provides an easy way to connect to and pull data from a Postgres database table.
  • db_cursor: We created this object as a pointer to our database. We’ll use it later with SQL to retrieve data from the database connection established above.

Query Postgres for data

The next step is to get some data from a PostgreSQL table.

s = ""
s += "SELECT t_pkg_name FROM tbl_software"
s += " WHERE"
s += " ("
s += " i_learning_curve < 70"
s += " AND b_enabled = true"
s += " );"
db_cursor.execute(s)

Analysis

  • SELECT…: We are asking Postgres to supply us with the contents of the “t_pkg_name” column FROM the table called “tbl_software”.
  • WHERE…: Command Postgres to filter the recordset from tbl_software where the learning curve is under 70 and the record has the b_enabled column set to true.
  • db_cursor.execute(s): Send the query we built above to PostgreSQL.

Python array example

Now we can build an array with the software names returned so later we can send that array to a simple HTML page that happens to be dynamic, in Python called a template. We are getting ahead of ourselves. Let’s focus for now in our path from PostgreSQL to Python to HTML on the part where we are pulling the recordset we retrieved from Postgres into a Python list.

array_softwares = db_cursor.fetchall()
return render_template("results.html", array_softwares = array_softwares)

Analysis

  • array_softwares…: This copies all the software names from Postgres into the “array_softwares” array.
  • render_template: This sends the data stored in the array_softwares list to “results.html” using the same variable name in order to avoid confusion.

From Python to HTML

The final step is to build an HTML template that has a loop in it, so as to display all the array items. Save this page (along with header and footer info like you see in our first HTML example at the top of this tutorial) as “results.html”. Note: In our testing, we discovered we needed to place our HTML template files in a folder on the server called “templates”. This may be a setting you can find in your Python config files.

Render_template syntax

PostgreSQL’s Render_Template() function displays a dynamic HTML page, often filled with content we control via sending parameters through the render_template function. This differs from the redirect function, which sends the user away to a different part of the application.

RETURN render_template("page_name_to_display.html", message = t_message)

We supply the RENDER_TEMPLATE function with this parameter:

t_message: The contents of “t_message” will be sent, along with the software, to page_name.html, an html template you create, that is set up to receive and use t_message.

Quick side track: Syntax of the REDIRECT() function

RETURN redirect("http://your-URL-here", code=302)

Render_template example

<div class='div_outer'>
{%
for t_pkg_name in array_softwares:
%}
  <div class='div_inner'>software: {{ t_pkg_name }}</div>
{%
endfor
%}
</div>

Analysis

  • for t_pkg_name in array_softwares: Loop through every item in the “array_softwares” array (called “list” in Python), which was passed to this template. Also naming each item in the loop as “t_pkg_name”.
  • div: Used in HTML for encapsulation so that styles, javascript, and other treatments can be done to the content inside the div and close div tages.
  • software: {{ t_pkg_name }}: There’s a lot going on here. First, we encapsulate the line in divs. This is for a few reasons, including (a) provide ability to style the output; and (b) provide each line of output with a carriage return. Next are those curly brackets. As you can see, two of them together tell the server to look for some value to place in that spot. In this case, we are filling that spot with the contents of “t_pkg_name”.
  • endfor: Normally, we don’t need a “next” or “endfor” statement to delineate the bottom “frame” for a “for” statement. Flask templates need this because we are no longer relying on indenting to tell Python when something ends. We need it here so that it is apparent to the server that this is the end of our for loop.

Back on track.

Render_template prerequisites

You will need to install and reference the following Python flask libraries in order to use the render_template function in Python:

from flask import Flask
from flask import render_template # to render the html form

Full HTML template

Now let’s look at the syntax for a basic HTML page with the necessary top (header) and other tags, created to receive and display two parameters.

<!-- results.html -->
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>Test Python Template</title>
    </head>
<body>
<!-- The text msg you see below as t_title is why we call this a template -->
<!-- t_title is filled in by Python as it calls up this HTML -->
<h1>{{t_title}}</h1>
<!-- div to contain form -->
<div class='div_outer'>
{%
for t_pkg_name in array_softwares:
%}
  <div class='div_inner'>software: {{ t_pkg_name }}</div>
{%
endfor
%}
</div>
<!-- Close the body of the HTML document -->
</body>
</html>

Send multiple variable to render_template

What if we want to render our template and send it more than one parameter? There are a few ways to do this.

  • *return render_template(“page_name.html”, **locals())*: This will send all local variables to your template for rendering!
  • return render_template(“page_name.html”, var_1 = “lalala”, var_2 = “wawawa”): This will send var_1 and var_2. We’re going to use this method to send t_title and array_softwares.

Here’s the HTML you will need to access those variables:

<div>Contents of variable_01: {{variable_01}}</div>
<div>Contents of variable_02: {{variable_02}}</div>
<!-- etc -->

Analysis

  • div: Div is an HTML tag for encapsulating content in HTML-based documents. Often times we use divs to assign styles to content via an inline style tag, a class tag pointing to some predefined CSS, or even Javascript to show/hide the div contents or do tasks based on the content encapsulated.
  • variable_01 and variable_02: Double curly brackets you see in the code example above are used to tell the server, “In preparation to send this HTML page to the client, in this spot put in the data stored in the variable mentioned here via the parameters of the render_templates function used to call this page.”

Now that we have gone through every aspect of how to transfer data from Postgres to Python to HTML using a dynamic HTML page, we can write the fully working web database application. We’ll also check for errors using try except functionality.

Python web database application source code

from flask import Flask
from flask import render_template # to render the html form
import psycopg2 # for database connection

app = Flask(__name__)
@app.route("/")

t_host = "database address"
t_port = "5432" #default postgres port
t_dbname = "database name"
t_software = "database software name"
t_pw = "database software password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, software=t_software, password=t_pw)
db_cursor = db_conn.cursor()

def MainCode():
  s = ""
  s += "SELECT t_pkg_name FROM tbl_software"
  s += " WHERE"
  s += " ("
  s += " i_learning_curve < 70"
  s += " AND b_enabled = true"
  s += " );"
  db_cursor.execute(s)

  # Here we catch and display any errors that occur
  #   while TRYing to commit the execute our SQL script.
  try:
      array_softwares = db_cursor.fetchall()
  except psycopg2.Error as e:
      t_error_message = "Database error: " + e + "/n SQL: " + s
      # The "/n" we see above is a carriage return.
      # The "+ s" above tacks the SQL onto the error report, giving
      #   the developer potentially useful information on what
      #   may have caused the error.
      # NOTE: we did not supply the HTML to "error_report.html"
      #   You can build that page yourself using what you learned in studying results.html

      return render_template("error_report.html", t_error_message = t_error_message)

  return render_template("results.html", t_title = t_title, array_softwares = array_softwares)

Advanced bonus knowledge

Building on what we learned above, we can add even more complexity to our HTML templates with Python script that can be inserted into our HTML by encapsulating our Python code between “{%” and “%}” symbols. like so:

{% if [condition] %}
  <div>Condition was met: {{ [parameter variable] }}</div>
{% endif %}

Conclusion

In this article we learned how to use Python Templates with Postgres SQL to create web pages and pass data to the server to be displayed in a web page by using the render_template function. We built a small application using Python, PostgreSQL queries with SQL, and HTML. Finally, we provided all of the Python source code you need to try and modify this project for your own purposes.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.