Error Logging in Python and Postgres

Introduction

We will learn to incorporate error logging into Python and Postgres to increase efficiency in tracking bugs, responding to user issues, and maintain applications. This is how the lesson will go:

  • How? We will learn how to trap errors by creating a table for error logging, use Python to save the relevant error parameters to that PostgreSQL table, and even send an email to the database admin (dba) to let the person know an error occurred.
  • What? We will use “try”, “except”, execute, and concatenation from Python and CREATE TABLE and INSERT from Postgres, including a few other features from both technologies.

Prerequisites

  • SQL: Novice-to-beginner understanding of writing Postgres SQL, using the PGadmin tool or other database admin tool like dBeaver.
  • INSERT: Executing the “INSERT” SQL command to add a row of data to Postgres via a Python application.
  • Helpful: Article on naming conventions showing why we prefix our variables, column names, table names, etc. as you see done in this article. For example, naming “t_variablename” 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.

Part 1: What is error trapping and how does it work?

Error trapping, or “error checking”, is where you build a sort of “check and balance” into your application at key points where you surmise errors may occur. Including error trapping in your Python-Postgres application has the following benefits:

  • Increased elegance and trust for/from the user. Rather than an app just stopping or causing other unpredictable or even scary issues, you can gracefully handle the error and/or inform the user what has happened.
  • Gather data that could be important for debugging.
  • Have information to help with determining responsibility for the error if more than one user, admin, dba, or coder is involved in application creation and/or testing.

Syntax

try:
    # code here to test
except psycopg2.Error as t_err_msg:
    # action to take if an error occurred
else:
    # action to take if NO error

Analysis

  • try: This is where we TRY executing some Python code for the interpreter to assess.
  • except: If there was an error, the error message is returned. Here we tell it to place that error message into the “t_err_msg” variable.
  • else: What to do if no error occurred.

Now to increase clarity by plugging some real code into the syntax we used above:

try:
    db_cursor.execute(t_sql)
except psycopg2.Error as t_err_msg:
    print("Error! Description: " + s + " SQL tried: " + t_sql)
else:
    print("No error! Smooth sailing!")

Part 2: Create Postgres table for error logging

The second part of this four piece puzzle is logging the error with Python to a PostgreSQL database. We’ll start with creating a table we’ll use to store our error log.

CREATE TABLE public.log_errors (
    id serial NOT NULL,
    id_session int4 NULL DEFAULT 0,
    id_user int4 NULL DEFAULT 0,
    t_err_msg VARCHAR(4096) NULL,
    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_errors_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX log_errors_id_idx ON public.log_errors USING btree (id);

Note: After creating the above table, if default value for the id column wasn’t set to “nextval(‘log_errors_id_seq’::regclass)” then copy this value in quotes (without the quotes) into that field in your Postgres database. Why? We are creating that column to be an auto-incrementing 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_errors” table.

Part three is to build the SQL needed to insert data into our new “log_errors” table:

Part 3: Create SQL for error logging

s = ""
s += "INSERT INTO log_errors"
s += "("
s += "id_user"
s += ", t_err_msg"
s += ", t_sql_tried"
s += ", t_notes"
s += ", b_email_sent"
s += ") VALUES ("
s += "(%id_user)"
s += ", '(%t_err_msg)'"
s += ", '(%t_sql_tried)'"
s += ", '(%t_notes)'"
s += ", (%b_email_sent)"
s += ", {
s += "
'id_user': id_user"
s += "
, 't_err_msg': t_err_msg"
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 a parameterized query to INSERT a row into the table we’ve named “log_errors”. For that row, we are sending five values: “id_user”, “t_err_msg”, “t_sql_tried”, “t_notes”, and “b_email_sent”. The names of these columns should explain their purpose.

The final piece of the puzzle is to send an email to the dba so he knows an error occurred.

Part 4: Initialize mail object and send email to database admin

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 above code serves to create and set up a mail object called “mail” that we can use below to send out emails.

    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>"
    msg = Message(
        body = s,
        subject = "Application Error",
        recipients = t_email_admin,
        sender = t_sender,
        reply_to = t_sender
        )
    mail.send(msg)

This part will send an email to the admin. Here is the detailed analysis:

  • msg = Message(): Plug in all our variables in order to create a Flask email message object.
  • mail.send(msg): Send the message object we built above.

Now that we have explained the most complex parts required to build our error logging application, it’s time to look at a full listing of our source code:

Part “Done”: Build a Python web app to trap and log errors in 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 vars():
    # id_user = ...
    # t_err_msg = '...'
    # t_sql_tried = '...'
    # t_notes = '...'
    # b_email_sent = true

def main():
    s = ""
    s += "INSERT INTO log_errors"
    s += "("
    s += "id_user"
    s += ", t_err_msg"
    s += ", t_sql_tried"
    s += ", t_notes"
    s += ", b_email_sent"
    s += ") VALUES ("
    s += "(%id_user)"
    s += ", '(%t_err_msg)'"
    s += ", '(%t_sql_tried)'"
    s += ", '(%t_notes)'"
    s += ", (%b_email_sent)"
    s += ", {
    s += "
'id_user': id_user"
    s += "
, 't_err_msg': t_err_msg"
    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
        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)
        # show error page
        return render_template("
error.html", t_message = t_message)

    db_cursor.close()

# For command line testing:
if __name__ == "
__main__":
    app.run(debug = True)

One thing to note about the application above. It’s really a response to errors more than showing the original error get trapped that would supplied us with the id of the user who set off the error, the sql that set the error off, it it was that, etc. We build our error logging table so that it could handle most kinds of situations that might come up, including ones that don’t involve SQL going awry. But yes, WITHIN our application created to deal with errors, we do have one example of error trapping you can study.

Conclusion

In this article we showed how to build an application for error logging in Python and Posgres to check for errors, insert a row into a table, and send the admin an email with the details known about the error. Part of that process involved using some other Python and PostgreSQL functions and features, including but not limited to CREATE TABLE, INSERT, string concatenation, execute, and render_template. Finally, 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.