Forum Registration with Python and Postgres

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

Introduction

In this part 1 of a multi-part series for building a message board, we are starting with the Forum Registration with Python and Postgres as the primary tools. We will utilize the Flask and Psycopg2 frameworks for request, render_template, and various other database-related functions. In this article, the database-related function we use is to execute an “INSERT INTO” SQL command.

Prerequisites

Following are a few articles you can look at that will make things easier for you for this lesson and most probably all related development you do.

Overview

We’ll begin by creating two tables in Postgres, tbl_users and tbl_messages. Next, we’ll write up an HTML page with a form on it for the user to enter their email address (as username) and password. Then we will build an application in Python to use the Render_template and Request functions in the Flask library to send the user to the HTML page and retrieve the user’s submitted data. Finally, we will add a hashed version of their password to the database.

Postgres tables for forum

Create tbl_users:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE public.tbl_users (
    id serial NOT NULL,
    id_session int4 NULL,
    t_email VARCHAR(128) NULL,
    t_password VARCHAR(256) NULL,
    t_name_user VARCHAR(64) NULL,
    t_name_first VARCHAR(64) NULL,
    t_name_last VARCHAR(64) NULL,
    t_ip_address VARCHAR(32) NULL,
    d_visit_first DATE NULL DEFAULT now(),
    d_visit_last DATE NULL,
    b_enabled bool NULL,
    CONSTRAINT tbl_users_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tbl_users_id_idx ON public.tbl_users USING btree (id);

NOTE: In the above table creation SQL, we went just a little bit overkill but recommend – even though we won’t be using many of these columns in the current simple forum system we are building, you will probably want those columns later if you choose to enhande the forum system you build with more features.

Create tbl_messages:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE public.tbl_messages (
    id serial NOT NULL,
    id_session int4 NULL DEFAULT 0,
    id_parent int4 NULL DEFAULT 0,
    id_user int4 NULL DEFAULT 0,
    t_topic VARCHAR(255) NULL,
    t_message VARCHAR(255) NULL,
    t_note VARCHAR(255) NULL,
    i_order int4 NULL DEFAULT 0,
    i_plusses int4 NULL DEFAULT 0,
    d_created DATE NULL DEFAULT now(),
    d_edited DATE NULL DEFAULT now()
);

NOTES: As with tbl_users, we have included some columns in tbl_messages that won’t be used in our simple messaging system but you will probably want to have later if you expand this system to have more features. One such column is id_parent. If you want messages to be able to refer to other messages, then you want this field.

Flask HTML template

Below is a minimal representation of HTML for a registration screen for our simple forum system. Name this file “forum_register.html” and save it in your “templates” 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
41
42
43
44
45
46
47
48
49
50
<html>
<head>
    <link rel="shortcut icon" href="favicon.ico">
    <title>Forum Registration</title>
    <style>
    body {background-color: #E0E0E0;}
    h1 {font-size: 32pt;}
    .div-primary {margin: auto; padding-left: 20px; padding-right: 20px;}
    .form-field-label {text-align: right; margin-right: 4px;}
    .form-field-input {text-align: left;}
    .form-button-save {text-align: left;}
    </style>
</head>
<body style='background-color: #E0E0E0;'>
<!-- With "t_message" in curly brackets below: -->
<!-- It is part of why we call this page "dynamic" or a "template" -->
<!-- This page is considered dynamic because: -->
<!-- The server can fill in at the spot where you see {{t_message}} -->
<!-- with the parameter value you sent when we used Flask's -->
<!-- render_template("forum_register.html", t_message = "A message") -->
<h1>{{t_message}}</h1>
<div>
    <!-- Set up the input form to receive user input -->
    <!-- method='post' sends the data as a post for Flask's Request() function to receive. -->
    <form id='form_register' name='form_register' action='' method='post' onsubmit='return true;'>

    <!-- Input box for the forum user to type their email address. -->
    <!-- The Input tag has a 'name' parameter of 't_email'. -->
    <!-- So our request function back in Python can use 't_email' -->
    <!--   to receive data the user typed in this field. -->
    <div class="form-row">
    <div class="form-field-label">Email address:</div>
    <div class="form-field-input"><input type="text"  name="t_email"></div>
    </div>

    <div class="form-row">
    <div class="form-field-label">Password:</div>
    <div class="form-field-input"><input type="text"  name="t_password"></div>
    </div>

    <!-- Submit button for the user to send the form -->
    <div>
      <input type="submit" class="form-button-save" name="btn_submit_add_user" value='Register'>
    </div>

    <!-- Close form tag -->
    </form>
</div>
</body>
</html>

In the form above we mentioned Flask’s Request function. Now we will learn how to use it.

Flask request from HTML

The first function we will study is the “request” function, which is used to pull data that was submitted either by form “post” or by a querystring “get”.

Flask Request Syntax

1
2
3
4
# In this application, we are not using the querystring version of request but are leaving it here for you to see how it works.
value_from_querystring = request.args.get(field_in_querystring, default value if empty)
# THIS is the version of request we are using for our forum system:
value_from_form_post = request.form.get(field_in_form_post, default value if empty)

Analysis

  • field_in_querystring: This determines the name of the field being submitted; how it was named in a querystring or where method=get in the HTML form. GETs are tacked on to the URL.
  • field_in_form_post: This is the field name being submitted via the HTML form where “method” is set to “post” like you see in the form tag of the HTML we wrote above.
  • default value if empty: If no value was submitted by the user for that field because they left the input text field empty, this is the value that will be received in the value_from_form_post part of the equation we showed you in the syntax above. For strings, we might put an empty string here, like “”. For numeric, we might put a zero in this spot.

Let’s look at how it works:

Flask Request Examples

An example of how we might use Flask’s Request function can be used in the “real world”, like for example, in creating a forum system or message board:

1
2
t_email = request.form.get("t_email", "")
t_password = request.form.get("t_password", "")

Analysis

  • t_email: This retrieves (requests) the value the user entered into the HTML input tag called “t_email” and places that value in the Python variable we also named “t_email”.

Flask Render_Template

Python Flask’s Render_Template function sends an HTML page to the user’s browser. Render_Template can be filled with dynamic content by sending parameters with the function.

Flask Render_Template Example

Note: Render_template requires the Flask library, so you need to add the two import lines you see in the example code below.

1
2
3
from flask import Flask
from flask import render_template
return render_template("forum_register.html", t_message = "Text to send to be displayed as dynamic")

Analysis

  • t_message: The value we placed in “t_message” is sent to forum_register.html, the dynamic html page we built above, that receives and displays t_message. Note: instead of using “t_message”, you can name this variable anything you want and even include more parameters within the render_template function.

Hash in Python

Hashes are a string type the result of calculating using a hash algorithm. We’ll use SHA3-512 here. A use of Hashing is to determine the integrity of different kinds of data; in this case, a password. The integrity test comes in the next article in this series where a user attempts to log in and we use Python’s IF statement to compare the hash of their password we store in the database with a hash of the password they typed into the form above.

Hashlib Syntax

1
2
3
    import hashlib
    t_hashed = hashlib.sha3_512(t_pre_hash.encode())
    t_password = t_hashed.hexdigest()

Python Hash example

Now let’s do the hashing of t_password:

1
2
3
    import hashlib
    t_hashed = hashlib.sha3_512(t_password.encode())
    t_password = t_hashed.hexdigest()

Analysis

  • We begin with the value, “t_password”, hash it, and store the resulting value in a variable we called “t_hashed” above.
  • Next, we use the hexdigest function to convert the bytes returned by hashlib() into hex type data.

Psycopg2 insert into

1
2
3
4
5
6
7
8
9
10
s = ""
s += "INSERT INTO tbl_users "
s += "("
s += " t_email"
s += ",t_password"
s += ") VALUES ("
s += " '(%t_email)'"
s += ",'(%t_password)'"
s += ")"
db_cursor.execute(s, [t_email, t_password])

Here above we built a parameterized query with SQL. Now let’s look at the full Python source code for a Forum-type messaging system.

Full Source Code in Python

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
# Libraries to import
from flask import Flask
from flask import render_template
from flask import request
import hashlib
import psycopg2

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

# Database connection code
t_host = "database host address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "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()

# Function for registration
def register():
    # Show user the template we built
    return render_template("forum_register.html", t_message = "Register to use the forum")
    t_email = request.form.get("t_email", "")
    t_password = request.form.get("t_password", "")

    # Check for empty in user name field
    if t_email == "":
        t_message = "Empty field: Fill in an email address."
        # If empty: Send them back to the html page with a message
        return render_template("forum_register.html", t_message = t_message)

    # Check for empty in password field
    if t_password == "":
        t_message = "Please fill in a password"
        return render_template("forum_register.html", t_message = t_message)

    # Password hashing like we went over above
    t_hashed = hashlib.sha256(t_password.encode())
    t_password = t_hashed.hexdigest()

    # Add the user to PostgreSQL
    s = ""
    s += "INSERT INTO tbl_users "
    s += "("
    s += " t_email"
    s += ",t_password"
    s += ") VALUES ("
    s += " '(%t_email)'"
    s += ",'(%t_password)'"
    s += ")"
    # Error checking/trapping
    try:
        db_cursor.execute(s, [t_email, t_password])
        db_conn.commit()
    except psycopg2.Error as e:
        t_message = "SQL error: " + e + "/n SQL: " + s
        # Again using templates to show the user a message.
        # This particular one helps the developer figure out
        #    any database errors that might occur upon trying to
        #    add a user to the database.
        return render_template("forum_register.html", t_message = t_message)
    db_cursor.close()

Conclusion

In this part 1 of a multi-article series for creating a message board, we built a Forum Registration with Python and Postgres. We used the Flask and Psycopg2 libraries for request, render_template, and various database-related functions.

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.