Login in TimescaleDB

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

Introduction

In this tutorial document, we will learn how to create a login in TimescaleDB for a user to sign in to our application, whether it be a business app or a game. We’ll cover creation of a dyanmic HTML page to be called up via Flask’s render_template function; back in Python we’ll use Flask’s request function to receive the user’s input; hash their password using hashlib; and check their email and hashed password combo against what exists in our database.

The first thing we’ll do is set up a database instance. We prefer using Objectrocket because we like their value and efficiency.

Create a TimescaleDB database instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Next we will create a user sign in screen with HTML and CSS.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

Script for login page

Below is a registration screen developed in HTML. This will look and work the same in all the major web browsers, including Chrome, Firefox, Brave, Opera, and Safari. Name this page “app-login.html” and put it in a folder named “templates” under your project folder root:

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
<html>
  <head>
    <link rel="site icon" href="favicon.ico" />
    <title>Application Login</title>
    <style>
      body {
        background-color: #222222;
      }
      h1 {
        font-size: 30pt;
      }
      .div-prime {
        margin: auto;
        padding-left: 10px;
        padding-right: 10px;
      }
      .my-field-label {
        text-align: right;
        margin-right: 3px;
      }
      .my-field-input {
        text-align: left;
      }
      .form-button-login {
        background-color: #444444;
        color: #eeeeee;
      }
    </style>
  </head>
  <body>
    <!-- The "txtMsgLogin" you see below is piece of this -->
    <!-- page being a "template". Since this page is "dynamic", -->
    <!-- the server can fill in at the placeholder of -->
    <!-- {{txtMsgLogin}} with the value you sent with: -->
    <!-- render_template("app-login.html", txtMsgLogin = -->
    <!--  "String value to be written by the server into the h1 below") -->
    <h1>{{txtMsgLogin}}</h1>
    <!-- Container DIV start. -->
    <div>
      <!-- Form for login -->
      <!-- method='post' sends the 2 values back to Python as a post for the Flask request function to acquire. -->
      <form
       id="loginform"
       name="loginform"
       method="post"
       onsubmit="return true;"
     >
        <!-- Input field for the user to type their email. -->
        <!-- Input has a 'name' parameter of 'txtUserEmail'. -->
        <!-- So our request function in Python will use this -->
        <!--   to get data from this field. -->
        <div class="formrecord">
          <div class="my-field-label">User Name:</div>
          <div class="my-field-input">
            <input name="txtUserEmail" type="text" />
          </div>
        </div>
        <div class="formrecord">
          <div class="my-field-label">Password:</div>
          <div class="my-field-input">
            <input name="txtUserPW" type="text" />
          </div>
        </div>
        <!-- button to submit the form. -->
        <div class="formrecord">
          <input
           name="buttonUserLogin"
           value="Login to the application"
           type="submit"
           class="form-button-login"
         />
        </div>
        <!-- Close the input form -->
      </form>
      <!-- Container DIV end. -->
    </div>
  </body>
</html>

The next thing to grasp is how we run that “app-login.html” page we created above from within the Python script.

Open browser from Python

To open the user’s web browser to use as a GUI for login, in Python we use the Render_Template function. The dyanmic page opened up can be filled with dynamic content by using parameters in render template. The web server renders the page, filling in the spots set aside for dynamic content with those values sent as parameters, then sends the completed HTML to the browser. Here’s how:

1
return render_template("/templates/app-login.html", txtMsgLogin = "Welcome to the application login!")

Analysis

  • txtMsgLogin: The value assigned to “txtMsgLogin” above will be sent, along with the user, to the server, which renders the app-login.html, the page we developed above. That page is dynamic, so it can receive and use txtMsgLogin. Note: instead of “txtMsgLogin”, you can call your variable anything within typical variable-naming conventions and you can send more parameters and values, if you want to. You can even send Python lists.

Next, we’ll write some Python script to request the email and password the user submitted via the HTML page we created.

Request form data in Python

We will start by using the render_template() function we looked at above to send the user to the HTML page created above and then request the data submitted by the registered user.

1
2
3
4
5
6
7
# Routing
@app.route("/login", methods=["POST","GET"])
# Set up a function for login
def login():
    return RENDER_TEMPLATE("app-login.html", txtMsgLogin = "Zombie Feeder Registration")
    txtUserEmail = REQUEST.form.get("txtUserEmail", "")
    txtUserPW = REQUEST.form.get("txtUserPW", "")

After we receive the user inputs into variables, we want to hash the password:

1
2
txtValue_hashed = hashlib.sha256(txtUserPW.encode())
txtUserPW = txtValue_hashed.hexdigest()

The next thing to do is to check our database for a match with what the user entered.

Find user in TimescaleDB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
s = ""
s += "SELECT"
s += " id"
s += "FROM tblAppUsers"
s += "WHERE"
s += "("
s += " txtUserEmail = '(%txtUserEmail)'"
s += " AND"
s += " txtUserPW = '(%txtUserPW)'"
s += ")"
db_cursor.execute(s, [txtUserEmail, txtUserPW])
db_record = db_cursor.fetchone()
# Check if a row was returned
# If no row returned (no match), set user id to zero
if db_record == None:
    IDuser = 0
else:
    # a record was found!
    IDuser = db_record[0]

In the above code, we developed a parameterized query, where the parameters are txtUserEmail and txtUserPW (hashed), both to be SELECTed FROM tblAppUsers in TimescaleDB.

Now that we have investigated the primary functionalities, let’s examine the full source of our login application.

Python script source

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# Be sure to install the following libraries with PIP
from flask import Flask
from flask import request
from flask import render_template
import psycopg2
import hashlib

# -------------------------
# Database objects creation
# -------------------------
import psycopg2
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

# Route
app = Flask(__name__)
@app.route("/login", methods=["POST","GET"])

def login():
    return render_template("app-login.html", txtMsgLogin = "Login")
    txtUserEmail = request.form.get("txtUserEmail", "")
    txtUserPW = request.form.get("txtUserPW", "")

    # Be sure the user email entered has a non-empty value.
    if txtUserEmail == "":
        txtMsgLogin = "Email field is empty."
        # Send the user back to the dynamic html template with a message.
        return render_template("app-login.html", txtMsgLogin = txtMsgLogin)
    # Make sure user entered a password on the HTML form.
    if txtUserPW == "":
        txtMsgLogin = "Plsimplicity enter a password."
        # Send the user back to the HTML form with a message.
        return render_template("app-login.html", txtMsgLogin = txtMsgLogin)

    # Encrypt the user-submitted password to check
    # for match in our database.
    txtValue_hashed = hashlib.sha256(txtUserPW.encode())
    txtUserPW = txtValue_hashed.hexdigest()
    # Create a query with parameters.
    s = ""
    s += "SELECT"
    s += " id"
    s += "FROM tblAppUsers"
    s += "WHERE"
    s += "("
    s += " txtUserEmail = '(%txtUserEmail)'"
    s += " AND"
    s += " txtUserPW = '(%txtUserPW)'"
    s += ")"
    # Handle errors and send error message with SQL to the user.
    try:
        db_cursor.execute(s, [txtUserEmail, txtUserPW])
        db_record = db_cursor.fetchone()
        # Check to see if a record was returned
        # If not then we set the user id to zero.
        if db_record == None:
            IDuser = 0
        else:
            # a record was found!
            IDuser = db_record[0]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("app-login.html", t_msg = t_msg)
    db_cursor.close()
    # Check user id to see if we want to send the user back to the form.
    if IDuser == 0:
        t_msg = "Your profile using that user/pw combo was not found. Plsimplicity try a different email and/or password."
        return render_template("app-login.html", t_msg = t_msg)

Conclusion

In this tutorial document, your knowledge grew about how to build a login system with HTML, Python, and TimescaleDB for a user to sign in with, whether the application is a game or business app. We’ll cover creation of an HTML page to be called up via Flask’s render_template function. Back in Python we’ll use Flask’s request function to get the user’s input into local variables; hash their password using the hashlib library; and check their email and hashed password combination for a match with what exists in our database.

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.