Forum Login with Python and Postgres

Introduction

In this part 2 of a multi-part series of lessons, we continue building a messaging system. In this part we build a forum login with Python and Postgres. In this section, we use the Flask and Psycopg2 frameworks for render_template, request, and some other database-related functions. In this part, the database function we use is to execute a “SELECT” SQL command to compare the data the user submitted for login to the data in a row in the database. In the next part in this multi-part series, we’ll create an HTML page for the user to see a list of message topics and another page to see the content of any message the user clicks on. After that, part 4 will be posting messages to the forum.

Prerequisites

See part 1, where we created the PostgreSQL tables, a registration form, and the Python and SQL code needed for user registration: Forum Registration with Python and Postgres.

A few other potentially useful articles:

Python Postgres Forum Project overview

In part 1 we created a registration screen. In this part 2, we’ll begin by creating a dynamic HTML login form where the user could type in their email address and password. Then we will write a Python application using the Render_template and Request functions for the purpose of retrieving their login information to TRY to log them in.

Since we learned how the render_template and request functions work in part 1, we’ll only briefly go over those functions here.

Flask Request From Form

Syntax of Python Request

my_variable = request.form.get(fieldname, a default value to use if fieldname has no value)

Example of Python Request

t_value_received = request.form.get("t_value_sent", "")

NOTE: t_value_sent is encapsulated in quotes for good reason.

Example of Python Render_Template

return render_template("a_page.html", t_msg = "Some data to show.")

Flask HTML template

Below is the HTML for a login screen. This will display nearly identical on all web browsers, including Chrome, Edge, Safari, and Firefox. Name this file “login.html” and save it in a directory called “templates”:

<html>
<head>
    <link rel="shortcut icon" href="favicon.ico">
    <title>Forum Login</title>
    <style>
    body {background-color: #C1C1C1;}
    h1 {font-size: 32pt;}
    .div-prime {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-login {text-align: left;}
    </style>
</head>
<body style='background-color: #C1C1C1;'>
<!-- With "t_message" in double brackets below: -->
<!-- It is why we call this page a "template" or "dynamic" -->
<!-- This HTML page is considered dynamic because: -->
<!-- The server fills in at the placeholder where you see {{t_message}} -->
<!-- with a parameter value carried along with Flask's -->
<!-- render_template("forum_register.html", t_message = "A message") -->
<h1>{{t_message}}</h1>
<div class='div-prime'>
    <!-- Build input form for user input. -->
    <!-- method=post sends the inputted data as a 'post' type -->
    <!-- for Flask's Request() function to receive. -->
    <form id='form_login' name='form_login' action='' method='post' onsubmit='return true;'>

    <!-- Input text field for the forum user to input their email address. -->
    <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>

    <!-- Input text field for the forum user to input their password. -->
    <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 forum user to attempt a login. -->
    <div>
      <input type="submit" class="form-button-login" name="btn_submit_login" value='Log in'>
    </div>

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

Request with Flask

Next, we will use Flask’s render_template function to show the user our dynamic HTML we built above, followed by requesting the data submitted by the user.

@app.route("/login", methods=["POST","GET"])
def register():
    return RENDER_TEMPLATE("login.html", t_msg = "Login Here")
    t_email = REQUEST.form.get("t_email", "")
    t_password = REQUEST.form.get("t_password", "")

HASHLIB Syntax

    t_after_hashing = hashlib.sha3_512(t_pre_hashed.encode())
    t_after_hexing = t_after_hashing.hexdigest()

Above: We began with the text the user put into “t_password”, use the hashlib and hexdigest functions to get a hexed and hashed value, so that next we can compare the resulting value to the one stored in PostgreSQL.

SELECT with Python

s = ""
s += "SELECT"
s += " id"
s += "FROM tbl_users"
s += "WHERE"
s += "("
s += " t_email = '(%t_email)'"
s += " AND"
s += " t_password = '(%t_password)'"
s += ")"
db_cursor.execute(s, [t_email, t_password])

Analysis: Here we built a parameterized query with SQL for increased security. We are looking for a match in the database for any records equal to BOTH email and password using the WHERE clause.

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

from flask import Flask
from flask import request
from flask import render_template
import psycopg2
import hashlib

app = Flask(__name__)
@app.route("/login", 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()

def login():
    return render_template("login.html", t_msg = "Log in to use the forum.")
    t_email = request.form.get("t_email", "")
    t_password = request.form.get("t_password", "")

    # Make sure email address was filled in
    if t_email == "":
        t_msg = "Empty field: Fill in an email address."
        # If they left the field empty, send the user back to the form with a message.
        return render_template("login.html", t_msg = t_msg)
    # Make sure user password was filled in
    if t_password == "":
        t_msg = "Please fill in a password"
        # If they left the password field empty, send the user back to the forum with error message.
        return render_template("login.html", t_msg = t_msg)

    # Here is the hash part we studied up on.
    t_value_hashed = hashlib.sha256(t_password.encode())
    t_password = t_value_hashed.hexdigest()

    # Validate by checking the database.
    s = ""
    s += "SELECT"
    s += " id"
    s += "FROM tbl_users"
    s += "WHERE"
    s += "("
    s += " t_email = '(%t_email)'"
    s += " AND"
    s += " t_password = '(%t_password)'"
    s += ")"
    try:
        db_cursor.execute(s, [t_email, t_password])
        # Create list of columns for the row returned:
        db_row = db_cursor.fetchone()
        # Was a row was returned?
        # If 'none', set value of id_user to zero
        if db_row == None:
            id_user = 0
        else:
            # A row match was found!
            # Fill in id_user from column 0 in the db_row list
            id_user = db_row[0]
    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()

    # If no match, send the user back to the form, along with a message.
    if id_user == 0:
        t_msg = "Email/Password combination not found in the database."
        return render_template("login.html", t_msg = t_msg)

Conclusion

In this article, we continued a multi-part series of lessons to build a forum with Python and Postgres. In this part, we used the Flask and Psycopg2 libraries for render_template, request, and other database-related functions. In this part, the database function we use is to execute a “SELECT” SQL command to compare the data submitted by the user for login (sign in) to the database. In the next part of this series, we’ll build an HTML page for the user to view a list of message topics and another page to see the detail of a message the user clicks on.

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.