Registration in TimescaleDB

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

Introduction

In this instructional article we’ll learn how to create Registration in TimescaleDB for most kinds of applications, including games. We learn step by step how to register a user using an HTML page retrieveed up by Python.

The first thing to 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.

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

Next we’ll build a simple registration gui using HTML to be called up with Python Flask’s render_template function.

Registration GUI in Python

Below is a registration GUI created with HTML. This will render identically in most web browsers. Name this file “register_user.html” and put it in a folder called “templates” off the root of your project folder:

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
<html>
    <head>
        <link rel="shortcut icon here" href="favicon.ico">
        <title>Game Register</title>
    </head>
<body>
<!-- The "txtDynamicMsg" you see below is piece of how this page is a Flask template. -->
<!-- Because this page is "dynamic", the server can fill in data at the place-holder spot below-->
<!-- designated as {{txtDynamicMsg}} with the value you sent from the following back in Python: -->
<!-- render_template("register_user.html", txtDynamicMsg = "String to be shown -->
<!--   in the h1 tag below") -->
<h1>{{txtDynamicMsg}}</h1>
<!-- Container DIV - open/start out -->
<div>
    <!-- html POST form for user inputs. -->
    <!-- 'Method = post' sends the data entered by the user -->
    <!-- and back to Python as a post for the Flask request -->
    <!-- function to receive. -->
    <form id='formRegistration' name='formRegistration' action='' method='post' onsubmit='return true;'>
    <!-- Input field for the user to input email address. -->
    <!-- Input has a 'name' of 'txtUserEmailAddress'. -->
    <!-- So our Flask request function in Python will use this -->
    <!--   to specify which data from this form to get. -->
    <div class="form-record">
    Email Addr: <input name="txtUserEmailAddress" type="text">
    </div>
    <div class="form-record">
    Password: <input name="txtUserPW" type="text">
    </div>
    <!-- button for the user to submit the form. -->
    <div>
      <input name="btnRegUserSubmit" value='Register' type="submit">
    </div>
    <!-- Close the form -->
    </form>
<!-- Container DIV - close/end -->
</div>
<!-- Close the body. -->
</body>
</html>

Next, we’ll study how we call the Flask template we created above and named “register_user.html” from Python.

Flask Render_template

Flask’s Render_Template calls up the user an HTML page in their default web browser. This page can be filled with dynamic content we determine by using parameters in the render template function and placed anywhere in the HTML within curly brackets like you see we did in the H1 tag of the HTML file we developed above.

1
2
3
4
# Render_template Needs Flask.
from flask import Flask
from flask import render_template
return render_template("/templates/register_user.html", txtDynamicMsg = "Zombie Feeder")

Flask Request Syntax

1
2
value_received = request.args.get(field_in_URL, default value) # GET, which is a querystring.
value_received = request.form.get(field_on_html_form, default value) # POST, from method=post in an HTML form.

Analysis

  • field_on_html_form and field_in_URL: These both use field name, as designated in your form POST or GET (querystring). A querystring is data added to the tail of a URL, like so: “https://adomain.com?field_name_in_URL=42”.
  • default value: If no values were sent by the user, this is the value that will be returned for value_received.

Flask Request Example

Flask examples of how the syntax will be used in applications. We will leave out “get” (request.args.get) for now as it has no use in our current project:

1
2
txtUserEmailAddress = request.form.get("txtUserEmailAddress", "")
txtUserPW = request.form.get("txtUserPW", "")

Analysis

  • txtUserEmailAddress = request.form.get(): This pulls the value the user put into the HTML field called “txtUserEmailAddress” and places that value in the local Python text variable also named “txtUserEmailAddress”.

  • txtUserPW = request.form.get(): Same concept as the above example for txtUserEmailAddress.

Finally, let’s examine Python code for requesting the user-submitted email address and password.

Python Request From Form

We will start the process by making use of the render_template function we will learned to use above to send the user to the dynamic HTML template page created above and then we’ll request the data submitted by the new user.

1
2
3
4
5
@app.route("/registration", methods=["POST","GET"])
def registration():
    return RENDER_TEMPLATE("register_user.html", txtDynamicMsg = "Game Registration")
    txtUserEmailAddress = REQUEST.form.get("txtUserEmailAddress", "")
    txtUserPW = REQUEST.form.get("txtUserPW", "")

Hashing for Increased Safety

Storing a plain text password in the TimescaleDB database leaves us – and especially the user – vulnerable to others who have (or hack) access to the users table. So we encrypt the password with a hash so that we can never again (and no one else can) read that password. We can only compare a hashed version at login to the hashed version in our database to understand if the user logging in used the correct password. This is why any time you forgot your password on most sites, instead of showing you your password, they ask you to make a new one. This is because they can’t show you your password.

1
2
txtPWhashed = hashlib.sha3_512(txtUserPW.encode())
txtUserPW = txtPWhashed.hexdigest()

Analysis

  • We begin with the value a new user typed into “txtUserPW” on the HTML form we created above, use the hashlib() function to “encrypt” it, and store that encrypted value as a byte in a variable we named “txtPWhashed”.
  • We then use the hexdigest() function to convert the byte value returned by hashlib() into hex formatted data.

Insert Into TimescaleDB

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tblPeople "
s += "("
s += " txtUserEmailAddress"
s += ", txtUserPW"
s += ") VALUES ("
s += " '(%txtUserEmailAddress)'"
s += ", '(%txtUserPW)'"
s += ")"
data_cursor.execute(s, [txtUserEmailAddress, txtUserPW])

Analysis: We used SQL to build a parameterized query for added safety, where the parameters here are txtUserEmailAddress and txtUserPW, both to be added into tblPeople in our TimescaleDB.

Python 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
# ----------------------
# Package Includes
# ----------------------
from flask import Flask
from flask import request
from flask import render_template
import psycopg2
import hashlib

# -------------------
# Routing Setup
# -------------------
app = Flask(__name__)
@app.route("/", methods=["POST","GET"])

# ----------------------
# TimescaleDB Connection
# ----------------------
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()

# ----------------------
# Primary Regis Function
# ----------------------
def registration():
    return render_template("register_user.html", txtDynamicMsg = "Register to play Zombie Feeder Now")
    txtUserEmailAddress = request.form.get("txtUserEmailAddress", "")
    txtUserPW = request.form.get("txtUserPW", "")

    # Make sure user didn't leave email field empty.
    if txtUserEmailAddress == "":
        txtDynamicMsg = "You left email field empty."
        # Send the user back to the html template with the above message.
        return render_template("register_user.html", txtDynamicMsg = txtDynamicMsg)
    # Make sure user password is not empty.
    if txtUserPW == "":
        txtDynamicMsg = "You left password field empty."
        # Send the user back to the html template with the above message.
        return render_template("register_user.html", txtDynamicMsg = txtDynamicMsg)

    # Hash-encrypt the password the user submitted.
    txtValue_hashed = hashlib.sha256(txtUserPW.encode())
    txtUserPW = txtValue_hashed.hexdigest()

    # Build a query for new user data insertion into TimescaleDB
    s = ""
    s += "INSERT INTO tblPeople "
    s += "("
    s += " txtUserEmailAddress"
    s += ",txtUserPW"
    s += ") VALUES ("
    s += " '(%txtUserEmailAddress)'"
    s += ",'(%txtUserPW)'"
    s += ")"
    # Catch possible errors with "try" and "except" commands.
    try:
        db_cursor.execute(s, [txtUserEmailAddress, txtUserPW])
        db_conn.commit()
    except psycopg2.Error as e:
        txtDynamicMsg = "Database error: " + e + "/n SQL: " + s
        return render_template("register_user.html", txtDynamicMsg = txtDynamicMsg)
    db_cursor.close()

Conclusion

In this instructional article your knowledge grew about how to create a registration form in Python and TimescaleDB for most types of applications, including games. We saw step-by-step how to register a user using an HTML page retrieveed up by Python and place the new user’s data into a table in the 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.