Templates for CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial document we learn how to use Templates with CockroachDB to create web pages and send data to be displayed in those web pages via use of the render_template function. Another way templates can be useful is to get data from a user and send that data into your Cockroach database. For now we’ll start out looking only at pulling data from CockroachDB and displaying it on a web page. Here’s how we will do it:

  • What? What is the syntax of Python’s render_template function?
  • How? How do we build an HTML template for displaying our data from Cockroach, how do we call it using Render_template, how do we pass it parameters, and how do we show the data in those parameters in our HTML template?
  • Why? Why and when do we need Python template functionality in Cockroach SQL?

Prerequisites

  • Understanding of how to write basic SQL in CockroachDB, using DBeaver or other database administration tool. And/or writing applications with coding languages like PHP, Python, C#, Java, ASP.Net, VB.Net, Node.js, Ruby, etc. Including some experience writing simple SQL statements, including SELECT, FROM, and WHERE.
  • Familiarity with HTML.
  • Solid understanding of what the following means: text types, string types, and integer types.

What does the RENDER_TEMPLATE function do in CockroachDB and what is the syntax?

We use CockroachDB’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.

RENDER_TEMPLATE syntax

1
RETURN render_template("name_of_web_web-page.html", txtMessage = txtMessage)

We supply the RENDER_TEMPLATE function with this parameter:

txtMessage: The contents of “txtMessage” will be sent, along with the user, to name_of_web_web-page.html, an html template you create, that is set up to receive and use txtMessage.

Brief side track: Syntax of the REDIRECT() function

1
RETURN redirect("https://your-site-address-here", code=302)

Back on track with Python templates:

IMPORTANT PYTHON CODE

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

1
2
from flask import Flask
from flask import render_template

Build an HTML template

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>Test Python page for displaying Cockroach data</title>
    </head>
<body>
<!-- The message you see below is why we call this -->
<!-- a template. "txtMessage" is filled in by the -->
<!-- Python file as it calls up this HTML. -->
<!-- Later you may wish to separate page title from -->
<!-- txtMessage -->
<h1>{{txtMessage}}</h1>
<!-- div to contain an input 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-input">
      <input type="submit" id="btnSubmit_sign_in" value='Return'>
    </div>

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

Send variables to render_template

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

  • return render_template(“name_of_web_web-page.html”, locals()): This will send all local variables to your template for rendering!
  • return render_template(“name_of_web_web-page.html”, var_1 = “wonderlicious”, var_2 = “jamtastic”): This will send var_1 and var_2.

Here’s the HTML you will have to use for accessing those variables:

1
2
<div>Value of var_1: {{var_1}}</div>
<div>Value of var_2: {{var_2}}</div>

Analysis

  • : Div is a way of encapsulating content in HTML documents. Often times we may use divs to assign styles to content via a css 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, “Dear powerful web server – When you send this HTML page to a client, in this spot put in the data associated with the variable mentioned here via the parameters section of the render_templates function used to call this web 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 a Cockroach Python application

Let’s start out by setting up access to a Cockroach database.

1
2
3
4
5
6
7
8
9
10
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

Analysis

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

The next step is to get some data from a CockroachDB table called tblUsers.

1
2
3
4
5
6
7
8
9
s = ""
s += "SELECT txtName FROM tblUsers"
s += " WHERE"
s += " ("
s += " intAge > 19"
s += " AND"
s += " b_enabled = true"
s += " );"
cursorCRDB.execute(s)

Analysis

  • SELECT: We are asking Cockroach to supply us with the contents of the “txtName” column FROM the table called “tblUsers”.
  • WHERE…: This is how we tell the interpreter to only give us rows from users who are over 19 years of age and who’s account has been enabled.
  • cursorCRDB.execute(s): Forward our SQL command on to CockroachDB.

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

1
2
arrayTxtUsers = cursorCRDB.fetchall()
return render_template("web-page.html", arrayTxtUsers = arrayTxtUsers)

Analysis

  • arrayTxtUsers: This copies all the user names from CockroachDB into the “arrayTxtUsers” array.
  • render_template: This sends the user to “web-page.html” and sends the above array data along with them.

The final step is to build an HTML Flask 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 instructional article) as “web-page.html”. Note: In our testing, we discovered we needed to place our HTML template files in a folder on the server named “/templates”. This may be a setting you can find in your Python config files.

1
2
3
4
5
6
7
{%
for txtName in arrayTxtUsers:
%}
  <div>user: {{ txtName }}<div>
{%
# next
%}

Analysis

  • for txtName in arrayTxtUsers: Loop through every item in the “arrayTxtUsers” array, which was passed to this template. Temporarily name each item “txtName”.
  • user: {{ txtName }}: 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 “txtName”.
  • # next: NOTE this is commented because in Python we don’t require 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 gets “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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
from flask import Flask
from flask import render_template # to render the html form
import psycopg2 # for database connection

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

txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

def MainCode():
  s = ""
  s += "SELECT txtName FROM tblUsers"
  s += " WHERE"
  s += " ("
  s += " intAge > 19"
  s += " AND"
  s += " b_enabled = true"
  s += " );"
  cursorCRDB.execute(s)

  # Here we catch and display any errors
  # that occur, while TRYing to commit the
  # execute our SQL code.
  try:
      arrayTxtUsers = cursorCRDB.fetchall()
  except psycopg2.Error as e:
      txtMessage = "SQL error: " + e + "/n SQL: " + s
      # Tidbit: the "/n" we see above is a carriage return.
      return render_template("errors_reporting.html", txtMessage = txtMessage)

  return render_template("web-page.html", arrayTxtUsers = arrayTxtUsers)

Bonus Templates Uses

Building on the lessons we ingested above, we can add even more use to our HTML templates with Python code that can be inserted into our HTML by encapsulating our Python scripts between “{%” and “%}” symbols, like this:

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

Conclusion

In this tutorial document your knowledge grew about how to use Python Templates with Cockroach SQL to create web pages and pass data to the server to be displayed in a web page by using the render_template function from Python. We built a small application using Python, CockroachDB SQL with SQL, and HTML. Finally, we provided all of the Python source scripts you have 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.