Get a CockroachDB Array

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

Introduction

In this tutorial document we learn how to Get a CockroachDB Array into a Python List using arrays, lists, the “INSERT” SQL command. Here’s how:

  • We will first learn how the Cockroach array type works, then we will learn about single and multidimensional Python Lists. During this process, we will see the difference between a Python List and a Python Array. We will then examine various examples using CockroachDB’s SELECT statement to get data from an array, as well as how to create and read Python Lists. you will also practice use of Python’s “for” loop and list-related functions like range and len. We will then make use of the render_template Python function to show a template (error page) to the user.
  • We will build a Python web application to finish the lesson.

Prerequisites

  • Cockroach: Basic understanding of CockroachDB, whether you use the free “dBeaver” tool or another database administration tool. And in this case, some Python familiarity is crucial. In this case, we have used Visual Studio Code to write Python and Dbeaver to manage our Cockroach database.
  • Naming conventions: Optional: Naming conventions instructional article explaining why we prefix variables, column names, table names, etc. as you see us doing in this tutorial document. For example, naming “t_variablename” with the “t” you see at the starting point in order to delineate it as a “text” (string) object and “tbl_” before table names in order to clearly mark those objects as tables.

How Arrays work

An array is essentially a group or list of items. In the programming and database world, arrays can save us a huge amount of work and increase efficiency. You can call “10, 5, -32, 14, -8, 3” an array of integers. You can call “Tim, Fred, Jane, Purple, Meg” an array of strings or array of text items. “Items” is typically used to describe each individual in the group that is an array.

Cockroach Array

Cockroach has a data type called Array. So within a “cell” (cross section between a database column and a record), you can have – instead of an integer or text data type – an array data type. Here’s an example of how that looks in a SQL script:

1
2
3
4
CREATE TABLE tblUsers (
    txtNameUser text
    , arrayIntGrade INTEGER[]
)

Analysis

In the SQL above, you may recognize the text data type that is being used for “txtNameUser”. The column we named “arrayIntGrade” is probably new for you as it is an array data type. The brackets (“[]”) are how we tell Cockroach “type this column as an array.”

Let’s examine that table filled with some data so we can get a visual perspective on the concept of how Arrays work in CockroachDB.

1
2
3
4
5
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[]
FROM
    tblUsers

Shows up as…

txtNameUserarrayIntGrade
Stevie30, 45
Billy78, 50, 75
Sharie72, 71, 79
Troy80, 76, 81, 84
Georgie50, 65
Sally67
Freddie64, 32, 63, 68

Next, we retrieve data from the “tblUsers” table, pulling only part of the array, so you can get a deeper understanding of how a Cockroach array works.

1
2
3
4
5
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[1]
FROM
    tblUsers

Returns…

txtNameUserarrayIntGrade
Stevie30
Billy78
Sharie72
Troy80
Georgie50
Sally67
Freddie64

Analysis

The “[1]” in “arrayIntGrade integer[1]” told the SQL interpretation engine to give us the first item in the “arrayIntGrade” column and that column happens to be an array. Note, as you will see later, Python (as does most languages) starts with zero instead of 1 as the index for accessing arrays.

The above array type is also called a one dimensional array because for each “item” in that array, we stored only one value. If you want to store two values, you call it a two dimensional array, which would look like:

1
2
3
4
5
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[x][y]
FROM
    tblUsers

Here’s another view on how array creation might work in CockroachDB:

1
2
DECLARE
    arr_names TEXT[] := ARRAY['Jane', 'May', 'Todd'];

That’s the basic idea of how arrays work in CockroachDB.

Before we write an application for copying data from a Cockroach Array into a Python List, we will learn how a List works in Python. After learning this, we will write some scripts using the LIST data type to create a portion of our import application.

Python Lists

A List in Python is a group of values. Lists increase efficiency because they allow you to store, reference, add, remove, and sort groups of values. You can call “-5, 16, -2, 23” a list of integers. You can call “Cockroach, Get, Array, PHP, Survive, Visual Basic” an array or list of text items or strings. In some coding languages, lists contain items where every item in the list must be the same type. In Python’s case, you can have mixed types, like this: “Pop, -2, Mustang, 10, -3.9, Array”. Most programmers use the word “Item” to describe individual values in a list.

Python lists are a data type that also have multidimension functionality. We will start out by gaining an understanding of how one dimension lists work.

1
listAutos = ["Ford", "Tesla", "Audi"]

The above scripts simultaneously creates a list called “listAutos” and fills the list with three values. How do we retrieve data from the above Python list?

1
2
txtAutoMake = listAutos[1]
print(txtAutoMake)

The value stored in “txtAutoMake” is now “Tesla” because list start outs count at zero. So, unlike how the index works in a Cockroach Array, “1” here means the second item in the list. That number is often called “index”.

Now we loop through the list to see all the values:

1
2
3
listAutos = ["Ford", "Tesla", "Audi"]
for txtAutoMake in listAutos
    print(txtAutoMake)

Analysis

  • for: We are using Python’s FOR type of loop to cycle through every item in “listAutos”, designating a variable called “txtAutoMake” to temporarily store the current item in the local variable.
  • print: Python’s “print()” function provides output.

The results from the above Python code:

1
2
3
Ford
Tesla
Audi

Now that you have some experience with Python lists, we will have a look at how a multidimensional list works. We will now track models, in addition to brands of automobiles.

1
2
3
listAutos = [["Ford", "Tesla", "Audi"], ["Mustang", "3 AWD Performance", "Quattro"]]
for i in range(len(listAutos))
    print("My " + listAutos[0][i] + " car is " + listAutos[1][i] + ".")

Analysis

  • Range: The Range function allows set up of a list of 0 to 3 because we are inputting a 4 from the len() function’s output, as shown below.
  • Len: The Len function outputs the size of the array; number of items.

The output:

1
2
3
My Ford car is Mustang.
My Tesla car is 3 AWD Performance.
My Audi car is Quattro.

Now that we have how a Python list works, we can move on to how to copy a Cockroach array into a Python list. First, let’s briefly examine how to set up Python required libraries, primarily for database management.

Copy Cockroach Array into a Python List

First, a Cockroach table where one of its columns is an array type:

txtNameUserarrayIntGrade
Stevie30, 45
Billy78, 50, 75
Sharie72, 71, 79
Troy80, 76, 81, 84
Georgie50, 65
Sally67
Freddie64, 32, 63, 68

Our task is to get that data into a Python List. Fortunately, Python’s psycopg2 library supplies us with an easy method for that.

1
2
3
s = "SELECT txtNameUser, arrayIntGrade FROM tblUsers"
cursorCRDB.execute(s)
listUsers = cursorCRDB.fetchall()

Analysis

  • SELECT: Get Cockroach to supply the values in the “txtNameUsers” column and the “arrayIntGrade” array column from “tblUsers”.
  • cursorCRDB.execute(s): Forward our SQL command on to CockroachDB.
  • listUsers =: Copy all the user names into the “listUsers” array.

Now let’s put it all together:

Full Source Code in Python

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

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

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 = "24293"
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("/get_array")
def get_array():

s = "SELECT txtNameUser, arrayIntGrade FROM tblUsers"
    try:
        cursorCRDB.execute(s)
        listUsers = cursorCRDB.fetchall()
    except psycopg2.Error as e:
        txtMessage = "Database error: " + e + "/n SQL: " + s
        return render_template("errors.html", txtMessage = txtMessage)

    # Loop through the new list:
    for i in range(len(listUsers))
        print("User " + listUsers[0][i] + " earned these grades: " + listUsers[1])

    # Close the database cursor and connection
    cursorCRDB.close()
    connCRDB.close()

Conclusion

In this tutorial document we studied and implemented how to copy a Cockroach Array into a Python List. We also learned that “List” is an extra-flexible “Array”. We used the Python “for” type loop. We also used the “SELECT” database command to read data from CockroachDB. A few functions and commands we also used: print, len, and range. We then provided commented source scripts for your ease.

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.