Python Template Parameters with Postgres

Introduction

In this tutorial we instruct on how to use Python Template Parameters with Postgres SQL to build a dynamic web page and retrieve data from Postgres to show in the template (web page) using the render_template function. This is how:

  • What? What is the Python “render_template” function’s syntax?
  • How? How do we build the Python template, how do we call it using Render_template(), how do we pass it parameters from Postgres, and how do we receive and use the values in those parameters in the template?

Prerequisites

  • Understand writing basic queries in PostgreSQL, using PG’s PGadmin tool, DBeaver, or other database administration tool.
  • Experience creating SQL statements for retrieving data from a Postgres database table, including “SELECT”, “FROM”, and “WHERE”.
  • Optional but very helpful: Ease with using HTML.
  • Knowledge of what the following means: text types, string types, and integer types.
  • Optional: Article on importance of and how to use naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects) as you see us doing here. For example, naming “t_phraseoriginal” with the “t” you see at the beginning in order to delineate it as a “text” object and “tbl_” before table names in order to clearly mark those objects as tables.

What does Python’s RENDER_TEMPLATE function do in PostgreSQL and what’s the syntax?

We use PostgreSQL’s Render_Template() to show a dynamic HTML page to the user, usually filled with content we control via Python template parameters.

RENDER_TEMPLATE() syntax

RETURN render_template(path_and_page_name, parameter = VALUE, parameter2 = VALUE, parameter3 = VALUE)

Analysis

  • path_and_page_name: This is the name of the template; usually something like “page_name.html” and by default needs to be in a folder named templates, like so: “root/my_page.html”.
  • parameter = value: This is what makes our template dynamic. Whatever value we put in for the parameter gets passed on to the html page template. Later we’ll look at how this gets used.

Much like render_template is Python’s REDIRECT function. Let’s take a look at how it works:

RETURN redirect(path_and_page_name)

Now back to the scheduled program:

Build an HTML template

Here’s the HTML of a Python Template with parameters.

<html>
    <head>
        <title>{{t_title}}</title>
    </head>
<body>
{% extends "menu.html" %}
<!-- The message you see below is why we call this a template -->
<!-- message is filled in by the Python file as it calls up this HTML -->
<!-- Later you may wish to separate page title from message -->
<h1>{{t_header}}</h1>
<!-- div to contain form -->
<div>I see you picked the base product.<br>
{% if t_data = "option 1" %}
  <div>This product has options!</div>
{% endif %}
</div>
<!-- close body -->
</body>
</html>

Analysis:

  • {{ and }}: We use double curly brackets like so “{{” and “}}” to tell the server to put the value contained in a parameter variable into that spot.
  • {% and %}: These brackets tell the server “inside here is where we run server side Python code”.
  • extends: This is a way to include another file. In this case we are including our menu. This is to increase modularity. In other words, imagine the page we are building is merely one of many. Rather than write and maintain our navigation menu in every page, we build it just once, name it “menu.html”, and refer to it from every page with this one line of code.
  • : Div is merely a way of encapsulating content in HTML documents. We often use divs to assign styles to content via a style tag or with a class tag referencing to predefined CSS classes.

Now that we know how to send the user, along with some data, to a dynamic HTML page, let’s map out the whole path from SQL to HTML using Python templates.

Build a simple CMS application: from Postgres to HTML

We’ll start by setting up access to a Postgres database.

import psycopg2
t_host = "database address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "database user password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

Next we get some data from the Postgres table called “tbl_pages”

s = ""
s += "SELECT t_title, t_header, t_data FROM tbl_pages"
s += " WHERE"
s += " ("
s += " b_visible = true"
s += " )"
db_cursor.execute(s)

Analysis

  • SELECT...: Supply us with the value in the t_title, t_header, and t_data columns FROM the table called “tbl_pages”.
  • WHERE...: This is where we tell the SQL engine to only give us records from tbl_pages where the page is marked as visible via the column called “b_visible”.
  • db_cursor.execute(s): Send our SQL command (“s”) on to PostgreSQL.

Next, we can build a Python list (array) with the data returned and send that array to a simple HTML template.

array_pages = db_cursor.fetchall()
t_title = array_pages[0]
t_header = array_pages[1]
t_data = array_pages[2]
return render_template("page.html", t_title=t_title, t_header=t_header, t_data=t_data)

Analysis

  • array_pages: This copies all returned records into the “array_pages” list.
  • render_template: This sends the user to “page.html” and sends the above array along with them.
  • NOTE this only renders the first page in the recordset. In the full code listing below, we’ll loop thru every record returned.

Now that you know how the various parts work, let’s combine and add some error checking.

Full Python 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_user = "database user name"
t_pw = "database user password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

def MainCode():
s = ""
# Take note: t_title is the 0th column listed, t_header is 1, and t_data is 2.
# This will be useful for later
s += "SELECT t_title, t_header, t_data FROM tbl_pages"
s += " WHERE"
s += " ("
s += " b_visible = true"
s += " )"
db_cursor.execute(s)

  # Catch and show any errors that occur
  #   while TRYing to get all data from our recordset into an array.
  try:
      array_pages = db_cursor.fetchall()
  except psycopg2.Error as e:
      t_msg = "SQL cursor array fetch error: " + e + "/n SQL: " + s
      # Tidbit: the "/n" we see above is a carriage return.
      return render_template("error_report.html", t_msg = t_msg)

  for page_columns in array_pages:
    t_title = page_columns[0]
    t_header = page_columns[1]
    t_data = page_columns[2]
    return render_template("page.html", t_title=t_title, t_header=t_header, t_data=t_data)

Conclusion

In this tutorial we learned and practiced how to use Python Template Parameters with Postgres SQL to create web template pages with mixed HTML and Python server-side code passed from a server query and displayed in a web page by using Python’s render_template function. We built a web app using Python and Template Parameters, PostgreSQL queries with SQL, and HTML. Finally, we gifted you with the source code to try this project on your own.

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.