Python Session For Postgres Record

Introduction

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

  • What? We’ll first study the libraries a Python session variable requires, gain understanding of Flask session variables, and play with an example of using session variables in Python. We’ll 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 web application. This will 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’ll also do some parameterized queries for PostgreSQL and Python error trapping.
  • How? The best way! At the end of this tutorial, we will build a Python web app to consolidate all we have learned along the way.

Prerequisites

  • Some basic knowledge of Postgres use, whether you prefer the “PGadmin” free tool or a different database admin tool.
  • In this case, Python is crucial. In this case, we are using Visual Studio Code to write Python and Dbeaver to manage our Postgres database.
  • We will be using some HTML here. If you have no experience with HTML, this should not be too difficult to follow as we will comment our HTML heavily.
  • Optional but useful: Tutorial on naming conventions explaining why we prefix our variables, column names, table names, etc. as you see us doing in this article. For example, naming “t_variablename” with the “t” you see at the beginning in order to delineate it as a “text” (string) object and “tbl_” before table names in order to clearly distinguish those objects as tables.

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

The Python Session Variable

Quick answer – A great way to ameliorate the issue with web requests being stateless. For example, if you don’t want to explicitely work with cookies, and you have an application with multiple concurrent users, 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.
  • Sessionize your variable: First, we made up the word “sessionize” but don’t worry about that. We’ll see how any variable you have that is specific to a user can be copied into a Python session variable. In this case we’ll store our “ID_user” variable in a session variable of the same name.

Note: Session vars are specific to a user, so you can’t trade information between users. You’d want to use Postgres or your favorite database to do the data swap.

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

  • SECRET_KEY: Session requires a secret key; a random string of characters known only to you.
  • SESSION_TYPE: Set this to “SESSION_TYPE=redis”.
  • SESSION_REDIS: The URI of our cloud-hosted Redis instance. Redis URIs are structured like: redis://:[password]@[host_url]:[port]. Example of config using URI: “SESSION_REDIS = redis.from_url(environ.get(‘SESSION_REDIS’))”

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

from flask import Flask
from flask import request
from flask import Blueprint, render_template, session
from uuid import uuid4
import psycopg2

t_host = "PostgreSQL database host address"
t_port = "5432"
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.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 PostgreSQL users table.

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

ID_session = str(uuid4())
session['SessionID'] = ID_session
return None

Analysis – We assign a local variable, “ID_session” 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 “ID_session”.

Next step:

t_msg = "Python Session for Postgres Record Insert"
return render_template("register.html", t_msg = t_msg)

Analysis

  • First we set up a local variable called “t_msg” 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 “t_msg” variable.

The HTML template:

<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>{{t_msg}}</title>
    </head>
<body>
<!-- The message above encapsulated in an "title" tag is why this is a template -->
<!-- It's what makes this page dynamic, as the server fills -->
<!-- in data at the spot where you see {{t_msg}} with data you sent when you used -->
<!-- the "render_template()" function. -->
<h1>Register</h1>
<!-- div to contain form -->
<div>
    <!-- Set up form and the file to process user input -->
    <form id='frmTest' name='frmTest' action='' method='post' onsubmit='return true;'>

    <!-- input box for the user to enter their user name -->
    <!-- Notice our input has a 'name' (parameter of the HTML input type) of 't_name_user'. -->
    <!-- This is how our request function chooses which data to retrieve. -->
    <div class="form-row">
      User Name: <input type="text"  name="t_name_user">
    </div>

    <!-- You will probably want to add the following field for getting password -->
    <div class="form-row">
      Password: <input type="text"  name="t_password">
    </div>

    <!-- button for user to submit form -->
    <div>
      <input type="submit" name="btn_submit_add_user" value='Registeroo'>
    </div>

    <!-- Close the form -->
    </form>
<!-- Close the container div -->
</div>
<!-- Close the body -->
</body>
</html>

We’ve heavily commented the above HTML, in case you are not super experienced writing HTML script.

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

    t_name_user = request.form.get("t_name_user", "")

    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "ID_session"
    s += ", t_name_user"
    s += ") VALUES ("
    s += "%(ID_session)"
    s += ", %(t_name_user)"
    s += ", {
    s += "
'ID_session': ID_session"
    s += "
,'t_name_user': t_name_user"
    s += "
}"
    s += "
)"
    db_cursor.execute(s)

Analysis

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

Nearly done. Now that we have added a new record to our user table, we want to find the record we added, so that we can get the ID of the that user and save IT in a session variable.

    s = ""
    s += "SELECT ID_user FROM tbl_users"
    s += "WHERE"
    s += "("
    s += " ID_session = %ID_session"
    s += ", {
    s += "
'ID_session': ID_session"
    s += "
}"
    s += "
)"
    db_cursor.execute(s)
    list_row = cur.fetchone()
    ID_user = list_row(0)
    session['ID_user'] = ID_user

Analysis

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

That’s it! Now let’s look at the full source code:

# 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.
t_host = "PostgreSQL database host address" # either "localhost", a domain name, or an IP address.
t_port = "5432" # default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

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

def showForm():
    # Show our template to the user so they can give us a username.
    t_msg = "Python Session for Postgres Record Insert"
    return render_template("register.html", t_msg = t_msg)

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

def register():
    # Get the user's name from the data submitted by the user on the template above
    # We're keeping it simple so as to stay as focused as possible on SESSION.
    # You may want to add password acquisition and hashing.
    t_name_user = request.form.get("t_name_user", "")

    # Add user to database
    s = ""
    s += "INSERT INTO tbl_users"
    s += "("
    s += "ID_session"
    s += ", t_name_user"
    s += ") VALUES ("
    s += "%(ID_session)"
    s += ", %(t_name_user)"
    s += ", {
    s += "
'ID_session': ID_session"
    s += "
,'t_name_user': t_name_user"
    s += "
}"
    s += "
)"
    # Use TRY EXCEPT error checking
    try:
        # Execute our SQL query
        db_cursor.execute(s)
        db_cursor.commit()
    except psycopg2.Error as e:
        t_msg = "
Database error: " + e + "/n SQL: " + s
        return render_template("
error.html", t_msg = t_msg)

    # Get User ID from PostgreSQL users table using our session ID
    s = "
"
    s += "
SELECT ID_user FROM tbl_users"
    s += "
WHERE"
    s += "
("
    s += "
ID_session = %ID_session"
    s += "
, {
    s += " 'ID_session': ID_session"
    s += "}"
    s += ")"
    # Execute the query
    db_cursor.execute(s)
    # Trapping and displaying any errors that occur
    #   while TRYing to commit the execute our SQL script.
    try:
        list_row = cur.fetchone()
    except psycopg2.Error as e:
        t_message = "SQL error: " + e + "/n SQL: " + s
        return render_template("error.html", t_msg = t_msg)

    # Now we know the ID of the user record we just created in Postgres.
    ID_user = list_row(0)
    # We'll also save it in a session variable
    session['ID_user'] = ID_user

    # Close database cursor and connection
    db_cursor.close()
    db_conn.close()

    # Now send the user off to the next part of your app...

INCREASE SECURITY EVEN MORE: Use Stored Procedures

Protect your user data by learning about

Conclusion

In this in-depth tutorial we were learned how to use the Python session for Postgres record creation and retrieval, where each record in our PostgreSQL table represents a unique user account. A few functions and commands we touched on include: render_template, execute, fetchone, and a brief use of list, which is basically an array in Postgres. We also played with some HTML. Finally, we listed the source script 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.