Login in Python and Cockroach

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

Introduction

In this article, we learn how to create a login in Python and CockroachDB for a user to sign in to our application, whether it be a business app or a game. We will cover creation of a dyanmic HTML page to be called up via Flask’s render_template function; back in Python we will use Flask’s request function to receive the user’s input; hash their password using hashlib; and check their email and hashed password combo against what exists in the database.

We’ll begin by creating a user sign in screen with HTML and CSS.

Script for login screen

Below is a registration screen built in HTML. This will look and work the same in all the major web browsers, including Chrome, Firefox, Brave, Opera, and Safari. Name this page “login.html” and put it in a folder named “templates” under your project folder root:

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
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>Zombie Feeder Login</title>
        <style>
        body {background-color: #000000;}
        h1 {font-size: 32pt;}
        .div-prime {margin: auto; padding-left: 20px; padding-right: 20px;}
        .my-field-label {text-align: right; margin-right: 4px;}
        .my-field-input {text-align: left;}
        .form-button-login {background-color:#555555; color:#FFFFFF;}
        </style>
    </head>
<body>
<!-- The "t_login_message" you see below is part of -->
<!-- this page being a "template". Since this page is -->
<!-- "dynamic", the server can fill in at the placeholder -->
<!-- of {{t_login_message}} with the value you sent with -->
<!-- render_template("login.html", t_login_message = -->
<!--  "String value to be written by the server into the h1 below") -->
<h1>{{t_login_message}}</h1>
<!-- Container DIV OPENING. -->
<div>
    <!-- Form for login -->
    <!-- method='post' sends the two values back to Python as a post for the Flask request function to acquire. -->
    <form id='loginform' name='loginform' method='post' onsubmit='return true;'>
    <!-- Input field for the user to type their email. -->
    <!-- Input has a 'name' parameter of 't_user_mail'. -->
    <!-- So our request function in Python will use this -->
    <!--   to get data from this field. -->
    <div class="formrow">
        <div class="my-field-label">User Name:</div>
        <div class="my-field-input"><input name="t_user_mail" type="text"></div>
    </div>
    <div class="formrow">
        <div class="my-field-label">Password:</div>
        <div class="my-field-input"><input name="t_user_pass" type="text"></div>
    </div>
    <!-- button to submit the form. -->
    <div class="formrow">
      <input name="buttonUserLogin" value='Login to Zombie Feeder' type="submit" class="form-button-login">
    </div>
    <!-- Close the form -->
    </form>
<!-- Container div CLOSE. -->
</div>
</body>
</html>

The next thing to understand is how we run that “login.html” page we built above from within the Python script.

Open browser from Python

To open the user’s web browser to use as a GUI for login, in Python we use the Render_Template function. The dyanmic page opened up can be filled with dynamic content by using parameters in render template. The web server renders the page, filling in the spots set aside for dynamic content with those values sent as parameters, then sends the completed HTML to the browser. Here’s how:

1
return render_template("/templates/login.html", t_login_message = "Welcome to the Zombie Feeder Login!")

Analysis

  • t_login_message: The value assigned to “t_login_message” above will be sent, along with the user, to the server, which renders login.html, the page we built above. That page is dynamic, so it can receive and use t_login_message. Note: instead of “t_login_message”, you can call your variable anything within typical variable-naming conventions and you can send more parameters and values, if you need to. You can even send Python lists.

Next, we will write some Python script to request the email and password the user submitted via the HTML page we built.

Request form data in Python

We’ll start by using the render_template() function we looked at above to send the user to the HTML page created above and then request the data submitted by the registered user.

1
2
3
4
5
6
7
# Routing
@app.route("/login", methods=["POST","GET"])
# Set up a function for login
def login():
    return RENDER_TEMPLATE("login.html", t_login_message = "Zombie Feeder Registration")
    t_user_mail = REQUEST.form.get("t_user_mail", "")
    t_user_pass = REQUEST.form.get("t_user_pass", "")

After we receive the user inputs into variables, we need to hash the password:

1
2
t_value_hashed = hashlib.sha256(t_user_pass.encode())
t_user_pass = t_value_hashed.hexdigest()

The next thing to do is to check the database for a match with what the user entered.

Find user in CockroachDB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
s = ""
s += "SELECT"
s += " id"
s += "FROM tbl_app_users"
s += "WHERE"
s += "("
s += " t_user_mail = '(%t_user_mail)'"
s += " AND"
s += " t_user_pass = '(%t_user_pass)'"
s += ")"
cdb_cursor.execute(s, [t_user_mail, t_user_pass])
db_row = cdb_cursor.fetchone()
# Check if a record was returned
# If no record returned (no match), set user id to zero
if db_row == None:
    id_user = 0
else:
    # a row was found!
    id_user = db_row[0]

In the above code, we built a parameterized query, where the parameters are t_user_mail and t_user_pass (hashed), both to be SELECTed FROM tbl_app_users in CockroachDB.

Now that we have explored the primary functionalities, let’s look at the full source of our login application.

Python script 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
71
72
73
74
75
76
# Be sure to install the following libraries with PIP
from flask import Flask
from flask import request
from flask import render_template
import psycopg2
import hashlib

# -------------------------
# Database objects creation
# -------------------------
import psycopg2
t_dbname = "db name"
t_name_user = "db user name"
t_sslmode = "auto"
t_sslrootcert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
t_host = "localhost"
t_port = "26251"
cdb_conn = psycopg2.connect(database=t_dbname, user=t_name_user, sslmode=t_sslmode, sslrootcert=t_sslrootcert, sslkey=t_sslkey, sslcert=t_sslcert, host=t_host, port=t_port)
ccdb_cursor = cdb_conn.cursor()

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

def login():
    return render_template("login.html", t_login_message = "Login")
    t_user_mail = request.form.get("t_user_mail", "")
    t_user_pass = request.form.get("t_user_pass", "")

    # Be sure the user's email entered has a non-empty value.
    if t_user_mail == "":
        t_login_message = "Email field is empty."
        # Send the user back to the dynamic html template with a message.
        return render_template("login.html", t_login_message = t_login_message)
    # Make sure user entered a password on the HTML form.
    if t_user_pass == "":
        t_login_message = "Please enter a password."
        # Send the user back to the HTML form with a message.
        return render_template("login.html", t_login_message = t_login_message)

    # Encrypt the user-submitted password to check
    # for match in the database.
    t_value_hashed = hashlib.sha256(t_user_pass.encode())
    t_user_pass = t_value_hashed.hexdigest()
    # Create a query with parameters.
    s = ""
    s += "SELECT"
    s += " id"
    s += "FROM tbl_app_users"
    s += "WHERE"
    s += "("
    s += " t_user_mail = '(%t_user_mail)'"
    s += " AND"
    s += " t_user_pass = '(%t_user_pass)'"
    s += ")"
    # Handle potential errors and send error message with SQL to the user.
    try:
        cdb_cursor.execute(s, [t_user_mail, t_user_pass])
        cdb_row = cdb_cursor.fetchone()
        # Check to see if a row was returned
        # If not then we set the user id to zero.
        if cdb_row == None:
            id_user = 0
        else:
            # a row was found!
            id_user = cdb_row[0]
    except psycopg2.Error as e:
        t_msg = "SQL error: " + e + "/n SQL: " + s
        return render_template("login.html", t_msg = t_msg)
    cdb_cursor.close()
    # Check user id to see if we need to send the user back to the form.
    if id_user == 0:
        t_msg = "Your profile using that user/pw combo was not found. Please try a different email and/or password."
        return render_template("login.html", t_msg = t_msg)

Conclusion

In this article, we learned how to build a login system with HTML, Python, and CockroachDB for a user to sign in with, whether the application is a game or business app. We will cover creation of an HTML page to be called up via Flask’s render_template function. Back in Python we will use Flask’s request function to retrieve the user’s input into local variables; hash their password using the hashlib library; and check their email and hashed password combination for a match with what exists 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.