Tracking Actions with CockroachDB

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

Introduction

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

  • 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 data to the CockroachDB table we created, and send an email message to the Cockroach developer if the user actions warrant the message.
  • We will use Cockroach’s “CREATE TABLE” and “INSERT INTO” commands, Python’s error trapping, execute function, and how to create and use Python functions.

Prerequisites

  • SQL: Solid understanding of the basics of how to write SQL for CockroachDB. We use dBeaver because of its ease and variety of useful features.
  • INSERT: Use of the “INSERT” SQL command to add a record of data to Cockroach 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.

Before we get into the meat of it, we will 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 scripts 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 scripts. Within one module or page, you may want to run a certain set of actions more than once. Rather than write that set of actions more than once, introducing redundancy, difficulty of maintenance, 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 scripts required to populate some variables with user data from Cockroach, those 10 lines of scripts 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 scripts over and over again.

Syntax

1
2
def functionName(parameters):
    # scripts run by this function

Example

1
2
3
4
5
6
def ReverseNumPolarity(intNumToReverse):
    intNumToReverse = intNumToReverse * -1

print (ReverseNumPolarity(32))

# results: -32

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

Now we will take a high level examine error trapping.

What is error trapping?

Error trapping, also known as “error checking”, is the creation of a “checker” into your application at key spots where you intuit errors may occur. Including error trapping in Python and Cockroach applications has benefits, including but not limited to:

  • Glean important data 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 security 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 data, 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 TRY EXCEPT

1
2
3
4
5
6
try:
    # scripts to test go in this spot.
except psycopg2.Error as txtErrorMsg:
    # if an error happened, scripts to execute go here.
else:
    # if NO error, scripts to execute go here.

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

Example of TRY EXCEPT

1
2
3
4
5
6
try:
    cursorCRDB.execute(txtSQL)
except psycopg2.Error as txtErrorMsg:
    print("Error: " + s + " SQL tried: " + txtSQL)
else:
    print("No errors!")

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

Cockroach table to track actions

To start out, we will build a table to use for saving user activity in a CockroachDB database.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE public.tblLogUserActions (
    id serial NOT NULL,
    idSession int4 NULL DEFAULT 0,
    idUser int4 NULL DEFAULT 0,
    txtSQLtried VARCHAR(4096) NULL,
    txtNotes VARCHAR(4096) NULL,
    boolEmailSent bool NULL DEFAULT FALSE,
    dateWhen DATE NULL DEFAULT now(),
    CONSTRAINT tblLogUserActions_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tblLogUserActions_id_idx ON public.tblLogUserActions USING btree (id);

Note: After creating the above tblLogUserActions CockroachDB table, if the default value for the id column wasn’t set to “nextval(‘tblLogUserActions_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 record, the “id” column will increase by one and always be a unique index for the “tblLogUserActions” table.

Now it is time to write the SQL needed to insert data into the “tblLogUserActions” table:

Create SQL for logging user activity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
s = ""
s += "INSERT INTO tblLogUserActions"
s += "("
s += "idUser"
s += ", txtSQLtried"
s += ", txtNotes"
s += ", boolEmailSent"
s += ") VALUES ("
s += "(%idUser)"
s += ", '(%txtSQLtried)'"
s += ", '(%txtNotes)'"
s += ", (%boolEmailSent)"
s += ", {
s += "
'idUser': idUser"
s += "
,'txtSQLtried': txtSQLtried"
s += "
,'txtNotes': txtNotes"
s += "
,'boolEmailSent': boolEmailSent"
s += "
}"
s += "
)"
cursorCRDB.execute(s)

Analysis: As you can see, we have used parameterized SQL to INSERT a row into the “tblLogUserActions” table. For that record, we are sending four values: “idUser”, “txtSQLtried”, “txtNotes”, and “boolEmailSent”. The names of these columns explains that column’s purpose.

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

Mail to developer

Next stage is for us to initialize a Mail object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
    EMAIL_SERVER = myConfigData['EMAIL_SERVER']
    EMAIL_PORT = myConfigData['EMAIL_PORT']
    EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]
    mail = Mail()

The script above creates and sets up a mail object that we will use to send emails about some user actions to the developer.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
s = ""
s += "Dear Developer Master Awesomeness<br>"
s += "<br>"
s += "An action occurred that you may need to know of.<br>"
s += "Here are the relevant details:<br>"
s += "User: " + idUser + "<br>"
s += "SQL: " + txtSQLtried  + "<br>"
s += "Memo: " + txtNotes  + "<br>"
s += "<br>"
msg = Message(
    body = s,
    subject = "User Action",
    recipients = txtEmailAddr_admin,
    sender = txtSender,
    reply_to = txtSender
    )
mail.send(msg)

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

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

Now that we see the most complex parts required to build our “logging of user activity” CockroachDB-Python application, we can examine the full source script listing:

Source of a Python app to log activity into CockroachDB

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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
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 connection setup
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()

@app.route("/")

def smtp_config(myConfigName, smtp=1):
    with open(myConfigName) as f:
            myConfigData = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][1]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][1]
    else:
        EMAIL_USERNAME = myConfigData['EMAIL_USERNAME'][0]
        EMAIL_PASSWORD = myConfigData['EMAIL_PASSWORD'][0]
        EMAIL_SERVER = myConfigData['EMAIL_SERVER']
        EMAIL_PORT = myConfigData['EMAIL_PORT']
        EMAIL_USE_TLS = bool(myConfigData['EMAIL_USE_TLS'])
    return [EMAIL_USERNAME, EMAIL_PASSWORD, EMAIL_SERVER, EMAIL_PORT, EMAIL_USE_TLS]

def setUpMainVariables():
    idUser = 2
    txtSQLtried = ''
    txtNotes = ''
    boolEmailSent = False

def tblLogUserActions_to_db():
    s = ""
    s += "INSERT INTO tblLogUserActions"
    s += "("
    s += "idUser"
    s += ", txtSQLtried"
    s += ", txtNotes"
    s += ", boolEmailSent"
    s += ") VALUES ("
    s += "(%idUser)"
    s += ", '(%txtSQLtried)'"
    s += ", '(%txtNotes)'"
    s += ", (%boolEmailSent)"
    s += ", {
    s += "
'idUser': idUser"
    s += "
,'txtSQLtried': txtSQLtried"
    s += "
,'txtNotes': txtNotes"
    s += "
,'boolEmailSent': boolEmailSent"
    s += "
}"
    s += "
)"
    try:
        cursorCRDB.execute(s)
    except psycopg2.Error as e:
        txtMessage = "
Cockroach Database error: " + e + "/n SQL: " + s
        return render_template("
errors.html", txtMessage = txtMessage)
    cursorCRDB.close

def mailSend():
    smtp_config("
config_01")
    txtSender = "
db_admin@yomamma.net"
    mail = Mail()
    # build an email message
    s = "
"
    s += "
Dear Mommy<br>"
    s += "
<br>"
    s += "
An error happened in your app." + "<br>"
    s += "
What we know at this point:<br>"
    s += "
User: " + idUser + "<br>"
    s += "
Error: " + txtErrorMsg  + "<br>"
    s += "
SQL: " + txtSQLtried  + "<br>"
    s += "
Notes: " + txtNotes  + "<br>"
    s += "
<br>"
    # send it
    msg = Message(
        body = s,
        subject = "
User Error",
        recipients = txtEmailAddr_admin,
        sender = txtSender,
        reply_to = txtSender
        )
    mail.send(msg)

def report_action():
    setUpMainVariables()
    # some action occurs that you want to log
    tblLogUserActions_to_db()
    mailSend()

report_action()

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

Conclusion

In this instructional article your knowledge grew about how tracking user actions with Python and Cockroach 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 Cockroach, we used many Python and CockroachDB functions and features, including execute, render template, try, CREATE TABLE, and INSERT. We shared all the Python source scripts 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.