Forum Post with Python and Postgres

Introduction

In this part 4 of a multi-part series for building a messaging system, we set up the ability for the user to make a forum post with Python and Postgres as the primary tools. We will utilize the Flask and Psycopg2 frameworks for request, render_template, and other database-related functions. In this article, the main database-related function we use is to execute an “INSERT INTO” SQL command to add a post to the database.

Prerequisites

Following are some articles you may want to study that will make things easier for you for this lesson and most probably all your Python development.

Overview

We’ll begin by building an HTML page that will have an HTML form in it that the user can use to type in a message they want to be posted. Next, we will build a Python application that uses Request and Render_template to send the user to the HTML page as well as get the user’s submitted data. Finally, we will insert their new message into tbl_messages.

Flask HTML page

Below is the HTML for a posting screen for our forum messaging system. Name this file “post.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
<html>
<head>
    <link rel="shortcut icon" href="favicon.ico">
    <title>Forum Message Post</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; padding-right: 4px;}
    .form-field-input {text-align: left; padding-left: 1px;}
    .form-button-save {text-align: center;}
    </style>
</head>
<body style='background-color: #E0E0E0;'>
<h1>{{t_message}}</h1>
<div>
    <form id='form_post' name='form_post' action='/replyPost?id_user={{id_user}}&id_parent={{id_message}}' method='post' onsubmit='return true;'>

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

    <div class="form-row">
        <div class="form-field-label">Your message:</div>
        <div class="form-field-input"><textarea name="t_post"></textarea></div>
    </div>

    <div>
      <input type="submit" class="form-button-save" name="btn_submit_add_user" value='Register'>
    </div>

    </form>
</div>
</body>
</html>

The form above is displayed via Flask’s render_template function and the data is retrieved via Flask’s Request function.

Python Render_Template

Python’s Render_Template function (from the Flask library) sends an HTML page to the user’s view. Render_Template can use dynamic content by sending parameters with the function. The server will incorporate the dynamic content into the page and send the resulting HTML on to the client browser. PHP and Classic ASP work in an almost identical manner.

Python Render_Template Example

1
2
3
from flask import Flask
from flask import render_template
return render_template("post.html", t_message = "Text to send for the h1 tag")

Analysis

  • t_message: The text we placed in the “t_message” parameter is sent to post.html, the html page we built above, that receives and displays t_message in the h1 tag. Note: rather than use “t_message”, you can call this variable anything and even include multiple parameters within the render_template Flask function.

Python request from HTML

The “request” function is used to pull data that was submitted either by form “post” or by a querystring “get”. In this lesson we are using it to request from a form post.

Python Request Syntax

1
2
3
4
# In the program we are building, we are not using the querystring version of request but are showing it here for you to see how it works and so you can compare it to the version that gets form data.
value_gotten_from_querystring = request.args.get(field_name_from_get, default value when empty)
# THIS is the version of request we are using for our forum system:
value_gotten_from_form_post = request.form.get(field_name_from_post, default value when empty)

Analysis

  • field_name_from_get: This is the name of the field submitted; how it was named in a querystring or where method = ‘get’ in the form. GET is added to the end of a URL.
  • field_name_from_post: This is the field name submitted via the form where method = ‘post’ like you see in the form tag of the HTML above.
  • default value when empty: If an empty or null value was submitted by the user for the field because they left the field empty, this is the value that will be received in the value_gotten_from_form_post part we showed you in the syntax above.

Let’s take a look at how that works with an applicable example:

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_topic = request.form.get("t_topic", "")
t_post = request.form.get("t_post", "")

Analysis

  • t_topic: Retrieves the value the user entered into the HTML input tag called “t_topic” and places that value in the Python variable we also named “t_topic”.
  • t_post: Retrieves the value the user entered into the HTML textarea tag called “t_post” and places that value in the Python variable we also named “t_post”.

Now for a quick look at inserting this data into Postgres:

Postgres insert into

1
2
3
4
5
6
7
8
9
10
11
12
s = ""
s += "INSERT INTO tbl_messages "
s += "("
s += " id_user"
s += ", t_topic"
s += ", t_message"
s += ") VALUES ("
s += " (%id_user)"
s += ", '(%t_topic)'"
s += ", '(%t_post)'"
s += ")"
db_cursor.execute(s, [id_user, t_topic, t_post])

Because we care about security, we used a parameterized query for our SQL and used psycopg2’s cursor object and execute method.

Finally, let’s write the full Python source code for our 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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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

# NEW: This is the new function that handles a new post.
def replyPost():
    return RENDER_TEMPLATE("post.html", id_message=id_message, id_user=id_user, t_text_for_h1 = "Forum Post")
    id_user = request.args.get('id_user', 0)
    id_parent = request.args.get('id_parent', 0)
    t_topic = request.form.get('t_topic', '')
    t_post = request.form.get('t_post', '')

    s = ""
    s += "INSERT INTO tbl_messages "
    s += "("
    s += " id_user"
    s += ", id_parent"
    s += ", t_topic"
    s += ", t_message"
    s += ") VALUES ("
    s += " (%id_user)"
    s += ", (%id_parent)"
    s += ", '(%t_topic)'"
    s += ", '(%t_post)'"
    s += ")"
    db_cursor.execute(s, [id_user, id_parent, t_topic, t_post])
    db_cursor.close()

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 4 of a multi-part series for creating a messaging application, we enabled the user to make a forum post with Python and Postgres as the primary tools. We utilized the Flask and Psycopg2 frameworks for request, render_template, and some other database-related features. In this article, the main database-related function we used was to execute an “INSERT INTO” SQL command to add a post to 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.