Python Templates with Postgres SQL

Introduction

In this article we learn how to use Python Templates with Postgres SQL to create web pages and send data to be displayed in those web pages via use of the render_template function. Here’s how we’ll do it:

  • What? What is the syntax of the render_template function?
  • How? How do we build the HTML template, how do we call it using Render_template(), how do we pass it parameters, and how do we receive the data in those parameters in the template?
  • Why? Why and when do we need Python template functionality in SQL?

Prerequisites

  • Knowledge of how to write basic queries in PostgreSQL, using PG’s PGadmin tool, DBeaver, or other database administration tool. And/or writing applications with programming languages like PHP, Python, Javascript, C#, Java, ASP.Net, VB.Net, Note.js, Ruby, etc. Including some experience writing simple SQL statements, including SELECT, FROM, and WHERE.
  • Familiarity with HTML.
  • Understanding of what the following means: text types, string types, and integer types.
  • Optional: Article on 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 “tphrase_original” 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 the RENDER_TEMPLATE function do in PostgreSQL and what’s the syntax?

We use PostgreSQL’s Render_Template() function to display an HTML page for the user, often filled with dynamic content we control via parameters. Not to be confused with the redirect function, which will send the user away without waiting for a return.

Syntax of the RENDER_TEMPLATE() function

RETURN render_template("page_name.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 user, 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)

Back on track with Python templates:

IMPORTANT PYTHON CODE

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

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

Build an HTML template

Now let’s look at the syntax for a basic HTML page set up to receive and display the parameter.

<html>
  <head>
    <link rel="shortcut icon" href="favicon.ico" />
    <title>Test Python Template</title>
  </head>
  <body>
    <!-- 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>{{message}}</h1>
    <!-- div to contain form -->
    <div>
      <!-- Set up form type and the file to process user input -->
      <form
       id="frmSignIn"
       name="frmSignIn"
       action=""
       method="post"
       onsubmit="return true;"
     >
        <!-- button for user to submit the form -->
        <div class="form-row">
          <input type="submit" id="btn_submit_sign_in" value="Return" />
        </div>

        <!-- Close the form -->
      </form>
      <!-- Close the container div -->
    </div>
    <!-- Close the body of the HTML document -->
  </body>
</html>

Send more than one variable to the render_template function

What if we want to render our template and send it more than one parameter or variable? 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.

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

<div>Contents of var_1: {{var_1}}</div>
<div>Contents of var_2: {{var_2}}</div>

Analysis

  • <div>: Div is merely a way of encapsulating content in HTML documents. Often times we use divs to assign styles to content via an inline style tag or with a class tag pointing to some predefined CSS.
  • {{var_1}} and {{var_2}}: Double curly brackets are used to tell the server, “Hey web server – When you serve up this HTML page, in this spot put in the data associated with the variable mentioned here via the parameters part of the render_templates function used to call this page.”

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 an application: from Postgres to HTML

Let’s begin by setting up access to a Postgres database.

import psycopg2
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()

Analysis

  • import psycopg2: This library gives us an easy way to connect to, pull data from, and send data to a PostgreSQL database.
  • db_cursor: We set up this object to point to our database. We’ll use it later to get data from that database.

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

s = ""
s += "SELECT t_name FROM tbl_users"
s += " WHERE"
s += " ("
s += " i_age > 18"
s += " AND"
s += " b_enabled = true"
s += " )"
db_cursor.execute(s)

Analysis

  • SELECT...: We are asking Postgres to supply us with the contents of the “t_name” column FROM the table called “tbl_users”.
  • WHERE...: This is how we tell the interpreter to only give us records from users who are over 18 years of age and who’s account has been enabled.
  • db_cursor.execute(s): Forward our SQL command on to PostgreSQL.

Next, we can build an array with the user names returned and send that array to a simple HTML template.

array_users = db_cursor.fetchall()
return render_template("page.html", array_users = array_users)

Analysis

  • array_users...: This copies all the user names into the “array_users” array.
  • render_template: This sends the user to “page.html” and sends the above array along with them.

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 “page.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.

<div>
  {% for t_name in array_users: %}
  <div>
    user: {{ t_name }}
    <div>
      {% # next %}
    </div>
  </div>
</div>

Analysis

  • for t_name in array_users: Loop through every item in the “array_users” array, which was passed to this template. Temporarily name each item “t_name”.
  • <div>user: {{ t_name }}<div>: 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_name”.
  • # next: NOTE this is commented because in Python we don’t need a “next” to delineate the bottom “frame” for a “for” statement. We are putting it here so that it is (a) clear to the server this is the end of our loop – NOT because Python needs or understands “next” but because that command, even if a comment (“#”) or NON-command, provides a delineation; and (b) Gives you, the developer, an easy and clear way of seeing that delineation.

OK now that you know how it all works, let’s put it all together and add a bit of 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 = ""
  s += "SELECT t_name FROM tbl_users"
  s += " WHERE"
  s += " ("
  s += " i_age > 18"
  s += " AND"
  s += " 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_users = db_cursor.fetchall()
  except psycopg2.Error as e:
      t_message = "Database error: " + e + "/n SQL: " + s
      # Tidbit: the "/n" we see above is a carriage return.
      return render_template("error_report.html", message = t_message)

  return render_template("page.html", array_users = array_users)

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.