Len for CockroachDB

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

Introduction

In this tutorial document we will learn how to use Len for CockroachDB Insert of rows, with some help from Python’s range function and CockroachDB’s “INSERT” SQL command. Here’s how the lesson will progress:

  • The first step in this tutorial document is to get how a single and multidimensional Python List (Array) works. An important additional aspect to this lesson is to get how Python Lists and Python Arrays are different. we will use a parameterized Cockroach SQL code to INSERT data from a list, which is where the Python LEN and RANGE functions will come in handy in order to determine the size of our list for easier insertion into our relational database table. During this lesson, we will also practice use of the Python for loop. We will also make use of the render_template Python function to show a template (error page) to the user.
  • We will build – and share the source code for – a complete web application in Python to get both high level (simple) and detailed views.

Prerequisites

We recommend you have at least novice-level understanding of how to use CockroachDB’s SQL, including Cockroach’s Insert SQL command. We recommend use of the free “dBeaver” tool or another database administration tool. We are using dBeaver to manage our Cockroach database and Visual Studio Code to code and debug Python scripts.

Before learning what the Python version of Len is and does and writing a full blown Python and Cockroach web app, we will learn how the List data type works in Python because Len is a crucial and oft-used function directly related to the Python List data type, which – as you will see – is almost identical to the Array data type. After learning this, we will write some scripts using these Python functions to create a portion of our web application.

Python List

Short answer: It’s an array, but with a bit more flexibility. In case you don’t know what an array is, we will describe a list for you on a high level:

In Python, a List is a group or array of values, each value usually called an “item”, which contains a value. Lists increase efficiency because they allow us to store, reference, and sort small and large groups of values. You can call “-8, 38, 12, -4, 3” a list of integers. You can call “Len, Cockroach, Humpty Dumpty, Virus” a list or array of text items. In some languages and when using Python array instead of list, your set of values must be all of the same type. In Python’s case, you can have mixed types, like this: “45, ‘Jack and Jill’, -25, 3.9, ‘Bif and Jane'”. The Python List type is quite a bit like a CockroachDB table or Excel spreadsheet.

Important to keep in mind: The Python List, just like the CockroachDB Array, are a data type that has multi-dimensional functionality. Given the complexity that comes with that, we will first build an understanding of how a list with a single dimension (single dimensional list) works.

List Syntax

1
listTxtName = [item no 1, item no 2, item no 3, etc.]

Analysis

  • listTxtName: the variable name we choose to give our list.
  • [item no 1, etc]: the individual items in our list. Note strings have to be encapsulated by single quotes or double quotes.
1
listHeatFood = ["Sweet", "Savory", "Spicy"]

Analysis: The above script does two things at once: (1) creates a list called “listHeatFood”; and (2) adds three text items to the new list. How do we reference data from the list (array) above? We will loop through “listHeatFood” to retrieve all values:

1
2
3
listHeatFood = ["Sweet", "Savory", "Spicy"]
for txtHeatOfFoodItem in listHeatFood
    print(txtHeatOfFoodItem)

Analysis

  • for: This is a Python loop for cycling sequentially through each item in “listHeatFood”, designating a variable called “txtHeatOfFoodItem” to store the current item.
  • print(): Python’s “print” function outputs data for us to examine.

See the results of the above “for” loop:


GeSHi Error: GeSHi could not find the language js (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Note: it’s also possible to use the “for” loop to go backwards by changing the above loop to look like this:

1
2
3
listHeatFood = ["Sweet", "Savory", "Spicy"]
for i in range(len(listHeatFood), 0, -1)
    print(listHeatFood[i])

The output then looks like:


GeSHi Error: GeSHi could not find the language js (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Did you notice in the above example we used the Python Len function? So let’s analyze the scripts. We will start out by learn abouting the Range function’s syntax.

Range syntax

1
listItems = range(start, stop, step)
  • listItems: The resulting list of values returned by the Python Range function.
  • start: Whether you are working with lists or arrays, you’ll usually want to start this at zero. Some other systems start at 1.
  • stop: Which item do you want to stop at?
  • step: Optional. Which direction are you counting in? From 0 to x or from x to 0? If counting “down” from x to 0, you want to put -1 here. Or you could even put something like -2 if you want to count down two at a time.

Now that you are armed with an understanding of the List type, your brain is primed to learn multidimensional Lists. For the lesson, we will add a bit of complexity to our food paradigm by adding a specific food to go with each food heat. For the example to make more sense, we will change the name of our list from “listHeatFood” to “listFoods”.

Range example

1
2
3
listFoods = [["Spicy", "Savory", "Sweet"], ["Burrito", "Stew", "Ice cream"]]
for i in range(len(listFoods))
    print("A " + listFoods[0][i] + " food is " + listFoods[1][i])

Analysis – Range: The “range” function creates a mini-list of, in this case, 0 to 2 because we are supplying it with a 3 with the Python Len function described below. – Len: Python’s Len function gives us the exact number of items in the list.

The results:

1
2
3
A Spicy food is Burrito
A Savory food is Stew
A Sweet food is Ice cream

Now that we get how Python’s list type works, we can move on to how to copy a single dimension list into Cockroach. We will start out by using Cockroach’s INSERT, along with Python’s Len and Range functions to add a row to the Cockroach database.

Copy Array into CockroachDB

1
2
3
4
5
6
7
8
9
10
listUsers = ['Francesca', 'Timothy', 'Alberto']
for i in range(len(listUsers))
    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += "txtNameUser"
    s += ") VALUES ("
    s += "(%param)"
    s += ")"
    cursorCRDB.execute(s, listUsers[i])

Analysis

NOTE: We have removed the “for” loop and placed the entire list as a parameter to be fed to the Cockroach SQL to run ONE “insert into” command:

1
2
3
4
5
6
7
8
9
listUsers = ['Francesca', 'Timothy', 'Alberto']
s = ""
s += "INSERT INTO tblUsers"
s += "("
s += "txtNameUser"
s += ") VALUES ("
s += "(%param)"
s += ")"
cursorCRDB.execute(s, listUsers)

Full Source Code

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
from flask import Flask
from flask import render_template
import psycopg2

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

# Connection credentials to Cockroach here

@app.route("/copy_list")
def copy_list():
    listUsers = ['Francesca', 'Timothy', 'Alberto']
    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += "txtNameUser"
    s += ") VALUES ("
    s += "(%param)"
    s += ")"
    try:
        cursorCRDB.execute(s, [listUsers])
        cursorCRDB.commit()
    except psycopg2.Error as e:
        txtMsg = "SQL error: " + e + "/n SQL: " + s
        return render_template("errors.html", txtMsg = txtMsg)
    cursorCRDB.close()
    connCRDB.close()

Conclusion

In this tutorial document your knowledge grew about how to use Python Len for Cockroach Insert of rows from a List into a relational database table. We learned that the Python “List” is the same as “Array” with some added flexibility with data type mixing. We used Python’s “for” loop a couple times. We included the “INSERT” SQL command to place data into a new row. A few functions and commands we also used were: range, print, range, and render_template. In addition, we listed the full source scripts for the Python web application.

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.