Forum Messages with Python and Postgres

Introduction

In this part 3 of a multi-part series, we continue building a messaging system. In this part we build a way to display forum messages with Python and Postgres. In this part, we use the Flask and Psycopg2 frameworks for render_template, request, and a few database-related functions. In this part, the database function we use is to execute two different “SELECT” SQL commands to view both topics and messages in separate dynamic HTML pages. In the next part in this multi-part series, we’ll set up posting messages.

Prerequisites

See part 1, where we built the underlying Postgres tables, a registration form, and the SQL and Python application needed for user registration: Forum Registration with Python and Postgres.

Forum messages lesson overview

In part 1 we built a registration screen. In part 2, we created a dynamic HTML login form where the user typed in email address and password to sign in to the application and give us the id_user value to use in this part 3 to view topics and messages.

Because we learned how the render_template and request functions work in part 1, we’ll briefly go over those functions in this part below.

Flask Request Input

Syntax of Python Request Input

1
VALUE_RETURNED = request.form.get(field_name, a default value to use if field_name is empty)

Example of Python Request Input

1
VALUE_RETURNED = request.args.get("id_user", "")

Example of Python Render_Template

1
return render_template("page.html", t_msg = "Some text to show on the dynamic page.")

Flask HTML templates

We’re going to build two pages here, one being a page to display topics and another to display message detail when a user clicks on the topic for that message.

Here’s a reminder of what tbl_messages looks like:

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

Name this first file “topics.html”:

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
<html>
<head>
    <link rel="shortcut icon" href="favicon.ico">
    <title>Forum Topics</title>
    <style>
    body {background-color: #BFBFBF;}
    h1 {font-size: 32pt;}
    .div-main-outer {margin: auto; padding-left: 20px; padding-right: 20px;}
    .table-column-num {text-align: right; padding-right: 2px;}
    .table-column-txt {text-align: left; padding-left: 2px;}
    </style>
</head>
<body style='background-color: #BFBFBF;'>
<!-- With "t_text_for_h1" in double brackets below: -->
<!-- This HTML page is considered dynamic because: -->
<!-- The server fills in at the placeholder where {{t_text_for_h1}} is, -->
<!-- with the param value conveyed via Flask's -->
<!-- render_template("topics.html", t_text_for_h1 = "...message...") -->
<h1>{{t_text_for_h1}}</h1>
<div class='div-main-outer'>
    <div class="form-row">
        <div class="table-column-txt">
        <a href='/replyPost?id_user={{id_user}}&id_message=0'>Post new message</a>
        </div>
    </div>
{%
# The three recordset columns for this page:
# 0: id, 1: t_topic, 2: d_edited
for db_row in db_rows:
    id = db_row[0]
    t_topic = db_row[1]
    d_edited = db_row[2]
}
    <div class="form-row">
        <!-- Column 1 -->
        <div class="table-column-txt">
        <!-- User clicks this link to take them to Python with id_user and id_message, -->
        <!-- Python then queries the database to get the full message and sends the -->
        <!-- user on to message.html -->
        <a href='/showMessage?id_user={{id_user}}&id_message={{id}}'>{{t_topic}}</a>
        </div>
        <!-- Column 2 -->
        <div class="table-column-num">
        {{d_edited}}
        </div>
    </div>
{%
endfor
%}
</div>
</body>
</html>

Now let’s build message.html:

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
<html>
<head>
    <link rel="shortcut icon" href="favicon.ico">
    <title>Forum Message</title>
    <style>
    body {background-color: #BFBFBF;}
    h1 {font-size: 32pt;}
    .div-main-outer {margin: auto; padding-left: 20px; padding-right: 20px;}
    .table-column-num {text-align: right; padding-right: 2px;}
    .table-column-txt {text-align: left; padding-left: 2px;}
    </style>
</head>
<body style='background-color: #BFBFBF;'>
<h1>{{t_text_for_h1}}</h1>
<div class='div-main-outer'>
    <div class="form-row">
        <div class="table-column-txt">
        <a href='/showTopics?id_user={{id_user}}&id_message={{id}}'>Back</a>
        </div>
    </div>
    <div class="form-row">
        <div class="table-column-txt">
        <a href='/replyPost?id_user={{id_user}}&id_message={{id}}'>Post reply</a>
        </div>
    </div>
    <div class="form-row">
        <div class="table-column-txt">
        {{t_message_full}}
        </div>
    </div>
</div>
</body>
</html>

Now that we have studied the more complex parts of the sign in portion of our overall project, let’s put it all together into a comprehensive Python application.

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
65
66
from flask import Flask
from flask import request
from flask import render_template
import psycopg2
import hashlib

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

# Connection credentials for Postgres
t_host = "database host address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
# Using Psycopg2 to create a connection object
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
# Using Psycopg2 to create a cursor object
db_cursor = db_conn.cursor()

# Set up globals
id_user = 23 # Once you have incorporated registration from part 1 of this series of lessons, you won't need to hard code the value for id_user.
id_message = 0

def replyPost():
    # Coming in next part.
    # Will be used to create new posts (id_message = 0)
    #   and reply to existing messages (id_message <> 0).

def showTopics():
    s = ""
    s += "SELECT"
    s += " id"
    s += ", t_topic"
    s += ", d_edited"
    s += "FROM tbl_messages"
    s += "ORDER BY"
    s += " d_edited DESC"
    try:
        db_cursor.execute(s)
        # Create list of columns for the row returned:
        db_rows = db_cursor.fetchall()
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)
    db_cursor.close()

    return RENDER_TEMPLATE("topics.html", db_rows=db_rows, id_user=id_user, t_text_for_h1 = "Forum Message Topics")
    id_user = request.args.get('id_user', 0)
    id_message = request.args.get('id_message', 0)

def showMessage():
    s = ""
    s += "SELECT"
    s += " t_message"
    s += "FROM tbl_messages"
    try:
        db_cursor.execute(s)
        # Create list of columns for the row returned:
        db_row = db_cursor.fetchone()
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)
    db_cursor.close()

    return render_template("message.html", db_row=db_row, t_text_for_h1 = "Forum Full Message Detail")

Conclusion

In this part 3 of a forum-building multi-part series, we continued building the messaging system. In this part we created a way to display forum messages with Python and Postgres. We used the Flask and Psycopg2 frameworks for render_template, request, and other database functions. In this part, the database function we used to execute two different “SELECT” SQL commands was execute() to view topics and message content in separate dynamic HTML pages. In the next part in this multi-part series, we’ll build a feature where the user can post messages to the forum.

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.