Functions for CockroachDB

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

Introduction

We will learn to use Functions for CockroachDB Applications from Python to increase efficiency, modularity, and ease of maintenance of your database or web-database application. This is how:

  • We will learn the ins and outs of how to create and use Python functions for web database applications that have a CockroachDB database by focusing on syntax, followed by “business reality” examples.
  • In addition to examining and creating functions, We will learn about and use Cockroach’s “CREATE TABLE” and “INSERT INTO” commands, error trapping, render_template, and execute.

Prerequisites

  • Solid understanding how to write SQL for Cockroach. We like to use the dBeaver IDE because of its number of features and ease of use.
  • Use of the “INSERT” SQL command to add a record of data to Cockroach via a Python application. If you are new to CockroachDB’s “INSERT” statement, no worries because you get to see it used more than once in this instructional article.

Python Functions Syntax

In Python, a function is a script which runs when called by another part of your Python or Cockroach applications. You can send data into functions when you call them. That data is known as a “parameter” or “parameters”. A function can, but doesn’t have to, return some data. We use functions for various purposes, including:

  • Readability. Increase script readability for yourself and anyone who inherits your scripts by creating your function with a name that clearly identifies its purpose, like “GetUserInfo()”, for example.
  • Efficiency. Instead of having the ten lines of scripts required to populate variables with user data from Cockroach, those ten lines of scripts 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 scripts 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

1
2
def FunctionName(parameters here):
    # scripts run by the function

Analysis

  • def: With Python we use the “def” keyword to initiate and designate a function.
  • FunctionName: This is where we name the Python or CockroachDB 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 SOMETIMES OPTIONAL data you “hand” your function.

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

Example of use of a Python function

1
2
3
4
5
6
def Pluralize(txtSourceString):
    return txtSourceString + "s"

print (Pluralize("Carb"))

# results: "Carbs"

Now let’s examine passing of multiple parameters.

1
2
3
4
5
6
7
8
9
10
11
12
13
def FixString(txtSourceString, intDegree):
    if intDegree == 1:
        txtSourceString = txtSourceString.strip()
        return txtSourceString
    else:
        txtSourceString = txtSourceString.strip()
        txtSourceString = txtSourceString.title()
        return txtSourceString

print (FixString(" flower ",1))
# results: "flower"
print (FixString(" flower ",2))
# results: "Flower"

Analysis

  • FixString: We named our function “FixString” because it fixes any string we seed it with.
  • txtSourceString: 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 ‘txtSourceString'”. We named it with the “t_” at the starting point for a few reasons, including to designate that this is a text-type variable.
  • intDegree: To what degree do we want the function to “fix” our source text?
  • if intDegree = 1: We’re using Python’s “if else” branching to determine what operations to perform on the source string, depending on the value of “intDegree” that was passed into our function.
  • strip: As you can see, if the program sent ” flower ” 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 ” flower ” to our function, it first removed spaces using strip() and then capitalized the first letter using Python’s built-in “title” function.

How would we use a function like we investigated above in conjunction with CockroachDB? In many applications, you will find yourself wanting to INSERT data into a Cockroach table. Let’s investigate a situation where you want to add a user name but you want to be sure that user name is “cleaned” or “fixed” before adding it to the tblUsers Cockroach table.

Create user table in CockroachDB

First we will create a user table now, with a few typical fields.

1
2
3
4
5
6
7
8
9
10
CREATE TABLE public.tblUsers (
    id serial NOT NULL,
    idSession int4 NULL DEFAULT 0,
    txtName_first VARCHAR(32) NULL,
    txtName_last VARCHAR(32) NULL,
    txtEmailAddr VARCHAR(64) NULL,
    dateJoined DATE NULL DEFAULT now(),
    CONSTRAINT tblUsers_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX tblUsers_id_idx ON public.tblLogUserActions USING btree (id);

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

Now, we create our new function:

1
2
3
4
5
6
7
def FixMyString(txtSource, intType):
    if intType == 1:
        txtSource = txtSource.strip()
    else:
        txtSource = txtSource.strip()
        txtSource = txtSource.title()
    return txtSource

Notice we made a few minor changes to this function compared to 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 “intType”, the “return” parameter is the same.

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

SQL for logging user

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
txtName_first = FixMyString(txtName_first, 2)
txtName_last = FixMyString(txtName_last, 2)
txtEmailAddr = FixMyString(txtEmailAddr, 1)

s = ""
s += "INSERT INTO tblUsers"
s += "("
s += " txtName_first"
s += ", txtName_last"
s += ", txtEmailAddr"
s += ") VALUES ("
s += " '(%txtName_first)'"
s += ", '(%txtName_last)'"
s += ", '(%txtEmailAddr)'"
s += ", {"
s += "'txtName_first': txtName_first"
s += ",'txtName_last': txtName_last"
s += ",'txtEmailAddr': txtEmailAddr"
s += "}"
s += ")"
cursorCRDB.execute(s)

Analysis:

  • FixMyString(txtName_first, 2): We’re sending the second parameter as “2” with “txtName_first” because we want both removal of spaces and capitalization of the first character because this is a name.
  • FixMyString(txtName_last, 2): Same as above.
  • FixMyString(txtEmailAddr, 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(txtEmailAddr)”.
  • Parameterized SQL: We are using parameterized SQL to INSERT a row into “tblUsers”. For that record, we are populating three columns: “txtName_first”, “txtName_last”, and “txtEmailAddr”.

Now that we get how to create Python functions for CockroachDB apps, we can examine the source scripts for the entire application:

Source to demo Python Functions for Cockroach Insert

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
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
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 = "21857"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

def setUpMainVariables():
    txtName_first = " pengo "
    txtName_last = "rocket man "
    txtEmailAddr = " spaceman@500rockets.com "

def LogUserToDB():
    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += " txtName_first"
    s += ", txtName_last"
    s += ", txtEmailAddr"
    s += ") VALUES ("
    s += " '(%txtName_first)'"
    s += ", '(%txtName_last)'"
    s += ", '(%txtEmailAddr)'"
    s += ", {
    s += "
'txtName_first': txtName_first"
    s += "
,'txtName_last': txtName_last"
    s += "
,'txtEmailAddr': txtEmailAddr"
    s += "
}"
    s += "
)"
    try:
        cursorCRDB.execute(s)
    except psycopg2.Error as e:
        txtMessage = "
SQL insertion error: " + e + "/n SQL: " + s
        return render_template("
err_web-page.html", txtMessage = txtMessage)
    cursorCRDB.close

def add_user():
    # manually set a user name and email addr for testing this app.
    setUpMainVariables()
    # add the user
    LogUserToDB()

add_user()

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

Conclusion

We learned in this tutorial document how to create and use Python functions for Cockroach 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 scripts. While building this web application for Python and Cockroach, we used some built in Python and CockroachDB functions and features, including try, execute, CREATE TABLE, and INSERT. Finally, we listed all of the source here for you to use.

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.