Python Functions for Postgres Apps

Introduction

We will learn to use Python Functions for Postgres Apps to increase efficiency, modularity, and ease of maintenance. This is how:

  • How? We will learn the ins and outs of how to create and use Python functions for web database applications that have a PostgreSQL database by looking at syntax, followed by real world examples.
  • What else? In addition to examining and creating functions, We will study and use Postgres’ “CREATE TABLE” and “INSERT INTO” commands, error trapping, render_template, and execute.

Prerequisites

  • SQL: Understanding how to write SQL for Postgres. We like to use the dBeaver IDE because of its number of features and ease of use.
  • INSERT: Use of the “INSERT” SQL command to add a row of data to Postgres via a Python application. If you are new to PostgreSQL’s “INSERT” statement, no worries because you get to see it used more than once in this tutorial.
  • How and why to use 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.

Python Functions Syntax

In Python, a function is code which runs when called by another part of Python or Postgres apps. You can hand/push data to functions when you call them. That data is known as “parameters”. A function can, but doesn’t have to, return some data. We use functions for various purposes, including:

  • Readability. Increase code readability for yourself and anyone who inherits your code by creating your function with a name that identifies its purpose, like “GetUserInfo()”, for example.
  • Efficiency. Instead of having the ten lines of code required to populate variables with user data from Postgres, those ten lines of code can be placed in a .py module that you reference from every module that requires it and every time, you see “GetUserInfo()” instead of those 10 lines of code over and over again.
  • Modularize the application. 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 one time and sits in one spot. Or you may place a function in a file all by itself so you can refer to it from different modules or pages.

Syntax for functions

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

Analysis

  • def: With Python we use the “def” keyword to designate a function.
  • function_name: This is where we name our function. As mentioned above in the introduction, it is important to give the function a name that reflects how the function will be used or what problem it will solve.
  • parameters: This is the OPTIONAL data you give your function.

Note: Functions can return data or merely accomplish an operation.

Example of use of a Python function

def Pluralize(t_source_string):
    return t_source_string + "s"

print (Pluralize("Mom"))

# results: "Moms"

Now let’s look at passing multiple parameters.

def FixIt(t_source_string, i_degree):
    if i_degree = 1:
        t_source_string = t_source_string.strip()
        return t_source_string
    else:
        t_source_string = t_source_string.strip()
        t_source_string = t_source_string.title()
        return t_source_string

print (FixIt(" mom ",1))
# results: "mom"
print (FixIt(" mom ",2))
# results: "Mom"

Analysis

  • FixIt: We named our function “FixIt” because it fixes any string we seed it with.
  • t_source_string: This is a parameter, our source string (text), the string we want to “fix”. This tells Python “Hey we are handing you text (string) for you to work on. Name it ‘tsource_string'”. We named it with the “t” at the beginning for a few reasons, including to designate that this is a text-type variable.
  • i_degree: To what degree do we want the function to “fix” our source text?
  • if i_degree = 1: We’re using Python’s “if else” branching to determine what operations to perform on the source string, depending on the value of “i_degree” that was passed into our function.
  • strip: As you can see, if the program sent ” mom ” along with “1”, our Python function merely removed spaces from front and back using the “strip” function.
  • title: If the user passed a “2” in addition to ” mom ” to our function, it first removed spaces using strip() and then capitalized the first letter using the “title” function.

How might we use a function like we explored above in conjunction with PostgreSQL? In many applications, you will find yourself wanting to INSERT data into a Postgres table. Let’s explore a situation where you want to add a user name but you want to be sure that username is “cleaned” or “fixed” before adding it to our user table.

Create table in Postgres for users

Let’s create a user table now, with a few typical fields.

CREATE TABLE public.tbl_users (
    id serial NOT NULL,
    id_session int4 NULL DEFAULT 0,
    t_name_first VARCHAR(32) NULL,
    t_name_last VARCHAR(32) NULL,
    t_email VARCHAR(64) NULL,
    d_joined DATE NULL DEFAULT now(),
    CONSTRAINT tbl_users_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tbl_users_id_idx ON public.log_activity USING btree (id);

Important: After creating this table, if the default value for the id column wasn’t automatically set by your Postgres database management tool to “nextval(‘log_activity_id_seq’::regclass)“, copy this value (without quotes) into the “id” field’s “default” parameter for that field. We want that column to be a auto-increment indexed column so each time you INSERT a record, the “id” column will increase by one and always be a unique index for “tbl_users”.

Next, let’s create our function:

def Fix_It(t_source, i_type):
    if i_type = 1:
        t_source = t_source.strip()
    else:
        t_source = t_source.strip()
        t_source = t_source.title()
    return t_source

Notice we made a few minor changes to this function vs the one we used before it as an example. Other than variable name changes for brevity, we realized only one “return” was needed because – whether we pass a “1” or “2” via “i_type”, the “return” parameter is the same.

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

Create SQL for logging user activity

t_name_first = Fix_It(t_name_first, 2)
t_name_last = Fix_It(t_name_last, 2)
t_email = Fix_It(t_email, 1)

s = ""
s += "INSERT INTO tbl_users"
s += "("
s += " t_name_first"
s += ", t_name_last"
s += ", t_email"
s += ") VALUES ("
s += " '(%t_name_first)'"
s += ", '(%t_name_last)'"
s += ", '(%t_email)'"
s += ", {"
s += "'t_name_first': t_name_first"
s += ",'t_name_last': t_name_last"
s += ",'t_email': t_email"
s += "}"
s += ")"
db_cursor.execute(s)

Analysis:

  • Fix_It(t_name_first, 2): We’re sending the second parameter as “2” with “t_name_first” because we want both removal of spaces and capitalization of the first character because this is a name.
  • Fix_It(t_name_last, 2): Same as above.
  • Fix_It(t_email, 2): Here we only want removal of extra spaces because we don’t care what case (capitalization) the email address is. If we were to go further here, we would write a function just for dealing with email addresses and name it something like, “IsValidEmailAddress(t_email)”.
  • Parameterized SQL: We are using parameterized SQL to INSERT a record into “tbl_users”. For that row, we are populating three columns: “t_name_first”, “t_name_last”, and “t_email”.

Now that we understand how to create Python functions for Postgres apps, we can look at the source code for the entire application:

Source code to demonstrate a Python Function for Postgres Insertion

from flask import Flask
import psycopg2 # for database connectivity
from flask import render_template # to render the error page

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

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

def setUpMainVariables():
    t_name_first = " jimbob "
    t_name_last = "jujumaster "
    t_email = " jimsawonk@funkytown.com "

def log_user_to_db():
s = ""
s += "INSERT INTO tbl_users"
s += "("
s += " t_name_first"
s += ", t_name_last"
s += ", t_email"
s += ") VALUES ("
s += " '(%t_name_first)'"
s += ", '(%t_name_last)'"
s += ", '(%t_email)'"
s += ", {"
s += "'t_name_first': t_name_first"
s += ",'t_name_last': t_name_last"
s += ",'t_email': t_email"
s += "}"
s += ")"
    try:
        db_cursor.execute(s)
    except psycopg2.Error as e:
        t_message = "SQL insertion error: " + e + "/n SQL: " + s
        return render_template("err_page.html", t_message = t_message)
    db_cursor.close

def add_user():
    # manually set user name and email for testing
    setUpMainVariables()
    # add the user
    log_user_to_db()

add_user()

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

Conclusion

We learned in this article how to create and use Python functions for Postgress apps so as to increase efficiency, readability, modularity, and ease of maintenance. We also learned how to use parameters in our functions. We even touched on some error trapping in our final code. While building this web application for Python and Postgres, we used some built in Python and PostgreSQL functions and features, including try, execute, CREATE TABLE, and INSERT. Finally, we listed all of the source code here for you.

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.