Tracking User Actions with Python and Postgres

Introduction

We will learn to do tracking of user actions with Python and Postgres to track bugs, respond quickly to user issues with accurate and fair feedback, and more efficiently maintain your application. This is how the lesson will go:

  • How? We will learn how to store a user’s action by creating a function that can be called to do this, create a table for logging user actions, use Python to save that information to the PostgreSQL table we created, and send an email message to the Postgres developer if the user actions warrant the message.
  • What? We will use Postgres’ “CREATE TABLE” and “INSERT INTO” commands, Python’s error trapping, execute function, and how to create and use Python functions.

Prerequisites

  • SQL: Understanding the basics of how to write SQL for PostgreSQL. We use dBeaver because of its ease and plethora of features.
  • INSERT: Use of the “INSERT” SQL command to add a row of data to Postgres via a Python application. If you have not yet used the INSERT command, it’s okay because we do so here in a very clear and beginner-oriented manner.
  • Tutorial on naming conventions showing why you may want to prefix your variables, column names, table names, etc. as you see done in this article. For example, naming “tvariable” with the “t” prefix to define it as a “text” (string) object and “tbl_” before table names in order to clearly distinguish those objects as tables. The tutorial goes a bit deeper, as well, talking about how to name variables based on a hierarchy system.

Before we get into it, we’ll go over two relevant foundational items, how to create and use a Python function and how to find and deal with errors.

Python Functions

A function in Python is a chunk of code which runs when called. You can hand data to functions. That data is also known as a parameter or parameters. A function can – but does not have to – return data. We use functions for various reasons, including:

  • Modularize our code. Within one module or page, you may want to do a certain set of actions more than once. Rather than write that set of actions more than once, introducing redundancy, maintenance difficulty, and potential for errors, you can refer to a function that is written once and resides in one spot. AND/OR you may have a function stored in a file all by itself so you can refer to it from different modules or pages.
  • Elegance and ease. If you give your function a meaningful name, like say “GetUserInfo()”, rather than have the ten lines of code required to populate some variables with user information from Postgres, those 10 lines of code can be placed in a file that you reference from every page/module that requires it and in your page/module you see “GetUserInfo()” instead of those 10 lines of code over and over again.

Syntax

def function_name(parameters):
    # code executed by the function

Example

def ReverseNumberPolarity(i_num_to_reverse):
    i_num_to_reverse = i_num_to_reverse * -1

print (ReverseNumberPolarity(5))

# results: -5

Note you can pass multiple parameters and even Lists. That’s as far as we’ll go into functions for now. You will get to see them in action as we build a Python/Postgres application below.

Next, we’ll take a high level look at error trapping.

What is error trapping?

Error trapping, also known as “error checking”, is the building of a “check” into your application at key spots where you guess errors may be. Including error trapping in Python and Postgres applications has benefits, including but not limited to:

  • Glean information that may be important to the debugging process.
  • Convey to the developer some data to help determine who/what is responsible for the error and how/when it occurred.
  • Increase feelings of safety and trust from users. It can be disconcerting and have an impact on trust when the application suddenly stops or exhibits other unpredictable behaviors. Error trapping allows you to not only gather information, but also redirect the user to a message crafted to help them feel safe or at least to know what is going on.

Syntax for Python’s “try” keyword

try:
    # code to test goes in this spot
except psycopg2.Error as t_err_msg:
    # if an error, code to execute
else:
    # if NO error, code to execute

Now let’s plug in some code, so as to gain additional understanding of how try/except works:

try:
    db_cursor.execute(t_sql)
except psycopg2.Error as t_err_msg:
    print("Error: " + s + " SQL tried: " + t_sql)
else:
    print("No problem. Yay!")

Now that we understand the basics of error trapping, let’s move on to start building our application.

Create Postgres table to track user action

To begin, we will build a table to use for saving user activity in a PostgreSQL database.

CREATE TABLE public.log_activity (
    id serial NOT NULL,
    id_session int4 NULL DEFAULT 0,
    id_user int4 NULL DEFAULT 0,
    t_sql_tried VARCHAR(4096) NULL,
    t_notes VARCHAR(4096) NULL,
    b_email_sent bool NULL DEFAULT FALSE,
    d_when DATE NULL DEFAULT now(),
    CONSTRAINT log_activity_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX log_activity_id_idx ON public.log_activity USING btree (id);

Note: After creating the above table, if the default value for the id column wasn’t set to “nextval(‘log_activity_id_seq’::regclass)“, copy this value (without the quotes) into the “id” field. We are creating that column to be an auto-increment indexed column so that every time you insert a row, the “id” column will increase by one and always be a unique index for the “log_activity” table.

Next we will write the SQL needed to insert data into the “log_activity” table:

Create SQL for logging user activity

s = ""
s += "INSERT INTO log_activity"
s += "("
s += "id_user"
s += ", t_sql_tried"
s += ", t_notes"
s += ", b_email_sent"
s += ") VALUES ("
s += "(%id_user)"
s += ", '(%t_sql_tried)'"
s += ", '(%t_notes)'"
s += ", (%b_email_sent)"
s += ", {
s += "
'id_user': id_user"
s += "
,'t_sql_tried': t_sql_tried"
s += "
,'t_notes': t_notes"
s += "
,'b_email_sent': b_email_sent"
s += "
}"
s += "
)"
db_cursor.execute(s)

Analysis: As you can see, we are using parameterized SQL to INSERT a record into the “log_activity” table. For that record, we are sending four values: “id_user”, “t_sql_tried”, “t_notes”, and “b_email_sent”. The names of these columns explains the column’s purpose.

Finally, we’ll see how to send email to the developer, when specified, so they know what happened.

Set up a mail object and send email to the developer

Next stage is to initialize a Mail object.

def smtp_config(config_name, smtp=1):
    with open(config_name) as f:
            config_data = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][1]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][1]
    else:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][0]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][0]
    MAIL_SERVER = config_data['MAIL_SERVER']
    MAIL_PORT = config_data['MAIL_PORT']
    MAIL_USE_TLS = bool(config_data['MAIL_USE_TLS'])
    return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]
    mail = Mail()

The code above creates and sets up a mail object that we’ll use to send emails to the developer.

    s = ""
    s += "Dear Developer Diety-like Person<br>"
    s += "<br>"
    s += "A user action occurred that you may want to know about." + "<br>"
    s += "Here is are the details:<br>"
    s += "User ID: " + id_user + "<br>"
    s += "The SQL: " + t_sql_tried  + "<br>"
    s += "Notes: " + t_notes  + "<br>"
    s += "<br>"
    msg = Message(
        body = s,
        subject = "User Action",
        recipients = t_email_admin,
        sender = t_sender,
        reply_to = t_sender
        )
    mail.send(msg)

That will send an email to the developer. Analysis of the python code above:

  • msg = Message(): Use parameters to create a Flask email “message” object.
  • mail.send(msg): Use the send function, using the message object we built above.

Now that we see the most complex parts required to build our “logging of user activity” application, we can look at the full source code listing:

Source code of a Python web app to log user activity into Postgres

from flask import Flask
import psycopg2 # for database connectivity
from flask_mail import Mail # for smtp (sending mail)
from flask_mail import Message
from flask import render_template # to render the html error page

app = Flask(__name__)
@app.route("/")

# database credentials
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()

@app.route("/")

def smtp_config(config_name, smtp=1):
    with open(config_name) as f:
            config_data = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][1]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][1]
    else:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][0]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][0]
        MAIL_SERVER = config_data['MAIL_SERVER']
        MAIL_PORT = config_data['MAIL_PORT']
        MAIL_USE_TLS = bool(config_data['MAIL_USE_TLS'])
    return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]

def setUpMainVariables():
    # id_user = ...
    # t_sql_tried = '...'
    # t_notes = '...'
    # b_email_sent = true

def log_activity_to_db():
    s = ""
    s += "INSERT INTO log_activity"
    s += "("
    s += "id_user"
    s += ", t_sql_tried"
    s += ", t_notes"
    s += ", b_email_sent"
    s += ") VALUES ("
    s += "(%id_user)"
    s += ", '(%t_sql_tried)'"
    s += ", '(%t_notes)'"
    s += ", (%b_email_sent)"
    s += ", {
    s += "
'id_user': id_user"
    s += "
,'t_sql_tried': t_sql_tried"
    s += "
,'t_notes': t_notes"
    s += "
,'b_email_sent': b_email_sent"
    s += "
}"
    s += "
)"
    try:
        db_cursor.execute(s)
    except psycopg2.Error as e:
        t_message = "
Postgres Database error: " + e + "/n SQL: " + s
        return render_template("
error.html", t_message = t_message)
    db_cursor.close

def mailSend():
    smtp_config("
config_01")
    t_sender = "
db_admin@yomamma.net"
    mail = Mail()
    # build mail message
    s = "
"
    s += "
Dear DBA<br>"
    s += "
<br>"
    s += "
An error occurred in your application." + "<br>"
    s += "
Here is what we know:<br>"
    s += "
User ID: " + id_user + "<br>"
    s += "
Error Message: " + t_err_msg  + "<br>"
    s += "
The SQL: " + t_sql_tried  + "<br>"
    s += "
Notes: " + t_notes  + "<br>"
    s += "
<br>"
    # send it
    msg = Message(
        body = s,
        subject = "
Application Error",
        recipients = t_email_admin,
        sender = t_sender,
        reply_to = t_sender
        )
    mail.send(msg)

def report_action():
    setUpMainVariables()
    # some action occurs that you want to log
    log_activity_to_db()
    mail_send()

report_action()

if __name__ == "
__report_action__":
    app.run(debug = True)

Conclusion

In this tutorial we learned how tracking user actions with Python and Posgres increase our capability to maintain applications. We also learned how to create and use functions and parameters, the importance of modularity, and how to use Python to detect errors and branch accordingly. While building this web application for Python and Postgres, we used many Python and PostgreSQL functions and features, including execute, render template, try, CREATE TABLE, and INSERT. We shared all the Python source code for this project.

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.