Session For CockroachDB

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

Introduction

In this tutorial document you will learn how to use Session for CockroachDB row insertion and recovery of a unique row using the Flask session framework from Python so we can find a record we had just inserted into a CockroachDB table in a multi-user web environment where we want to acquire a user’s unique ID and save that ID as a session variable for later use across an entire session. Here’s how:

  • We will first learn about the frameworks a Python session variable requires, gain understanding of Flask session variables, and play with an example of using session variables in Python. We will take a brief tour of using HTML, as well, to build a Python template, for use in gathering some basic data to be entered by a user; the front-end part of our Cockroach web-database application. This will also require us to use Python’s “render_template” function to display the HTML page for the user to input data into. Along the way, we will also do some parameterized SQL for CockroachDB and Python error trapping.
  • How? The best way! At the end of this instructional article, we will build a Python web app to consolidate all we have learned from this document.

Prerequisites

  • Some novice understanding of CockroachDB use, whether you prefer the free “dBeaver” multi-database tool or a different database administration tool.
  • For this case, Python use is crucial. In this case, we have used Visual Studio Code to write Python and Dbeaver to manage our Cockroach database.
  • We will be using some HTML here. If you have no experience with HTML, this should not be too hard to follow as we will comment our HTML heavily.

Before writing the Python application, let’s wrap our heads around the Python session variable.

Session Variable

Quick answer – A great way to ameliorate the issue with web requests typically being stateless. For example, if you don’t want to explicitly use cookies, and you have an application with multiple concurrent users (really, any web database application), you’ll want to use sessions. Here are two primary uses:

  • Session ID: It’s something we are going to create using “uuid” as an auto-incremented and auto-assigned value unique to every user. SUPER useful for distinguishing between users, including keeping variable values separate for every user of an application.
  • Sessionate your variable: First, we made up the word “sessionate” but don’t worry about that. We will see how any variable you have that is specific to a user can be copied into a Python session variable. In this case we will store our “idUser” variable in a session variable with that name.

Note: Session variables are specific to a user, so you can’t trade data between users (and you would rarely – if ever – want to!). You’d want to use Cockroach or your favorite database to do a data swap.

Now let’s begin! First, add these variables to your local config.py file:

  • SECRET_KEY: Session requires a secret key; a random string of characters, like a password. Keep it in a cool, dark place.
  • SESSION_TYPE: Set this to “SESSION_TYPE=python”.
  • SESSION_REDIS: The URI of our cloud-hosted instance. URIs are structured like: python://:[password]@[hostxtURL]:[port]. Example of config using URI: “SESSION_PYTHON = python.from_url(environ.get(‘SESSION_PYTHON’))”

Next, some “administrative goals” to get Python ready for you to use Sessions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from flask import Flask
from flask import request
from flask import Blueprint, render_template, session
from uuid import uuid4
import psycopg2

txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

Analysis

  • We start by referencing all the necessary libraries, including Flask, UUID, and psycopg2.
  • Next we set up our database address, login, and objects needed later for reading and writing from/to the CockroachDB users table.

Next step: Create a unique session variable for this current user.

1
2
sessionID = str(uuid4())
session['SessionID'] = sessionID

Analysis

  • We assign a local variable, “sessionID” a new/unique value, using the “uuid4” function from the uuid4 library.
  • We then use Flask’s session object to create a session variable named “session[‘SessionID’]” and tell it to hold the value in “sessionID”.

Next step:

1
2
txtMsg = "Python Session for Cockroach Record Insert"
return render_template("user-register.html", txtMsg = txtMsg)

Analysis

  • First we set up a local variable called “txtMsg” to store the message we want to pass on to our template.
  • Then we use the “render_template” function to display our HTML page (template) and send it the data in our “txtMsg” variable.

The HTML template:

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
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>{{txtMsg}}</title>
    </head>
<body>
<h1>Registration</h1>
<div>
    <form id='frmTest' name='frmTest' action='' method='post' onsubmit='return true;'>
    <div class="form-input">
      User Name: <input type="text"  name="txtNameUser">
    </div>

    <div class="form-input">
      Password: <input type="text"  name="txtPassword">
    </div>

    <div>
      <input type="submit" name="btnSubmit_add_user" value='Registeroo'>
    </div>

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

Now that we’ve sent the user to a page for them to put in their user name, we can acquire the user name the user submitted on that form and insert that user name, along with session ID into our Cockroach database!

1
2
3
4
5
6
7
8
9
10
11
12
    txtNameUser = request.form.get("txtNameUser", "")

    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += "sessionID"
    s += ", txtNameUser"
    s += ") VALUES ("
    s += "'(%sessionID)'"
    s += ",'(%txtNameUser)'"
    s += ")"
    cursorCRDB.execute(s, [sessionID, txtNameUser])

Analysis

  • First we fill the local variable we’ve named “txtNameUser” with data by requesting to get from the HTML template form the data the user typed in there and submitted. This uses Python’s Flask “request” function.
  • Next we built a parameterized Cockroach SQL query to INSERT one record into “tblUsers”, where that row includes our unique session ID plus the user name.
  • Finally, we executed the SQL code. Later, in the full source script layout, you will see how to use TRY in order to trap errors.

We are now nearly done. Now that you have added a new row to our user table, we want to find the row we added, so that we can get the ID of the that user and save that ID in a session variable.

1
2
3
4
5
6
7
8
9
10
s = ""
s += "SELECT idUser FROM tblUsers"
s += "WHERE"
s += "("
s += " sessionID = (%sessionID)"
s += ")"
cursorCRDB.execute(s, [sessionID])
listRecord = cur.fetchone()
idUser = listRecord(0)
session['idUser'] = idUser

Analysis

  • First we created a parameterized query with SELECT and a WHERE clause to find the row we have just created, using the “sessionID” CockroachDB column.
  • Then we used psycopg2’s Fetchone to get the one row we know our SQL returned and store that in a list called “listRecord”.
  • We know this rowset has only one column, so we get that column from our list via “listRecord(0)” where the zero denotes “first column”. We store this value in a local variable called “idUser”.
  • Finally, we assign a new session variable called “idUser” the value in the local “idUser” so that the user’s ID can now be carried and referenced with/by their session.

That’s it! Now let’s examine the full source scripts:

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
# Get the necessary libraries
# Use "pip3 install flask-session redis"
from flask import Flask
from flask import request # to get user input from form
from flask import Blueprint, render_template, session
from uuid import uuid4
import psycopg2

# Set up database credentials, connection object, and cursor.
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26521"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

# Create our first session variable.
def SetSessionID():
    session['SessionID'] = str(uuid4())
    sessionID = session['SessionID']
    return None

def showForm():
    # Show the HTML template to the user so they can submit a user name.
    txtMsg = "Session for Cockroach Record Insertion and new ID recovery"
    return render_template("user-register.html", txtMsg = txtMsg)

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

def register():
    # Get the user name from the data submitted by the user on the template above.
    # You may later wish to add password acquisition and hashing.
    txtNameUser = request.form.get("txtNameUser", "")

    # Add user to database
    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += "sessionID"
    s += ", txtNameUser"
    s += ") VALUES ("
    s += "'(%sessionID)'"
    s += ",'(%txtNameUser)'"
    s += ")"
    try:
        # Execute our SQL commands
        cursorCRDB.execute(s, [sessionID, txtNameUser])
        cursorCRDB.commit()
    except psycopg2.Error as e:
        txtMsg = "Database problem: " + e + "/n SQL: " + s
        return render_template("errors.html", txtMsg = txtMsg)

    # Get User ID from CockroachDB users table using our session ID
    s = ""
    s += "SELECT idUser FROM tblUsers"
    s += "WHERE"
    s += "("
    s += " sessionID = (%sessionID)"
    s += ")"
    # Execute the SQL
    cursorCRDB.execute(s, [sessionID])
    # Trapping and relaying any errors that occur
    #   while TRYing to commit the execute our SQL code.
    try:
        listRecord = cur.fetchone()
    except psycopg2.Error as e:
        txtMessage = "SQL error: " + e + "/n SQL: " + s
        return render_template("errors.html", txtMsg = txtMsg)

    # Now we know the ID of the user row we just created in CockroachDB.
    idUser = listRecord(0)
    # We will also save it in a session variable
    session['idUser'] = idUser

    cursorCRDB.close()
    connCRDB.close()

Conclusion

In this in-depth instructional article we were learned how to use the Python session for Cockroach row creation and retrieval, where each row in our CockroachDB table represents a unique user account. A few functions and commands we also touched on include: render_template, execute, fetchone, and a brief use of list, which is basically an array in Python. We also played with some HTML to build a “template”. Finally, we listed the source code for this Python application for you to use with your projects.

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.