Copy Array to CockroachDB

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

Introduction

In this tutorial document we learn how to copy an Array to CockroachDB by using the “INSERT INTO” SQL command along with some specific Python functions. Here’s how we will do it:

  • First, we will learn how single and multidimensional Python Lists work, while along the way learning the difference between a Python List and a Python Array, which are similar. We will then examine two techniques, using CockroachDB’s INSERT statement to get the data from our list into a Cockroach table. During this learning process, we will also practice use of the Python for loop and the len() and range() list-related functions. We will also make use of the render_template Python function to show a template (error page) to the user.
  • We will build a Cockroach and Python application to put it all together.

Prerequisites

We recommend you have some novice understanding of using Cockroach, whether you use the free “dBeaver” multi-database tool or another database administration tool. In this case, some knowledge of Python is important. In this case, we have used Visual Studio Code to write Python and Dbeaver to administer our CockroachDB database.

Before writing our Cockroach-Python application, we will learn all about 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.

What is a Python List and how does it work?

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

A Python List is a set or array of items. Lists can increase our efficiency because they allow us to efficiently store, reference, and sort small and large groups of values. You can call “-3, 1, 12, 23, 5”; a list of integers. You can call “Export, Import, Paste, Copy, Insert, CockroachDB, SQL, Array, Query” a list or array of strings or text items. In some languages, your list must be all of the same data type. In Python’s case, you can have mixed types, like this: “45, -13, 8.2, Corona”. We use the term “Item” to describe each individual in the set of values in a list.

Python lists are a data type that have multi-dimensional functionality. That may sound a little complex, we will first build up our understanding of how a single dimensional list or array works.

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

Analysis: The above script simultaneously creates a list called “listAutos” and fills it with three items that all happen to be text. How do we retrieve data from the list (array) above?

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

The value stored in “txtAutoMake” is now “VW” because list starts out counting at zero. So “1” means the second item in the list. That number is often called “index”.

Let’s use a FOR loop to iterate through the list to examine all the values:

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

Analysis

  • for: We are using Python’s built in “for” loop to iterate through every item in “listAutos”, designating a variable called “txtAutoMake” to store each current item.
  • print(): Python’s “print” function gives us output.

Here are the results of the loop you see above:


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

Now that you have some experience with Python lists, we will have a look at multi-dimensional lists (same concept applies to Arrays). We will assume you want to track models as well as brands of autos.

1
2
3
listAutos = [["Ford", "VW", "Audi"], ["Mustang GT 500", "Jetta Sport", "Quattro"]]
for i in range(len(listAutos))
    print("My " + listAutos[0][i] + " is a " + listAutos[1][i])

Analysis – Range: Python’s Range function creates a mini-list of, in this case, 0 to 3 because we are supplying it with a 4 via the Len function described below. – Len: Unlike how Len in many languages returns the length of a string, Python’s “len” function gives us the number of items in an array or list.

The results:

1
2
3
My Ford is a Mustang GT 500
My VW is a Jetta Sport
My Audi is a Quattro

Now that we have a fairly solid understanding of how Python’s list type works, we can move on to how to copy our list into CockroachDB. First, we will briefly go over setting up Python commands for the libraries we will need, as well as the exact scripts for connecting to your Cockroach database.

Connect to Cockroach

1
2
3
4
5
6
7
8
9
10
11
12
13
from flask import Flask
from flask import render_template # to render an error page
import psycopg2
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 = "26350"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

Analysis

  • from flask import render_template: In case our error checking (using “try” later), and in order to send the user to an HTML error page we created, we need the “render_template” Python function.
  • txtDBname…txtPortNum: We set up our Cockroach database connection parameters like database name, database user name, SSL mode, SSL root certificate, SSL key, SSL certification, host, and port.
  • connCRDB: This is where we are create a Cockroach connection object and hand it the data it needs.
  • cursorCRDB: This is to create a “database cursor” for our Cockroach database connection, to be used for most database operations.

Copy Array into CockroachDB

First, a simple method you may be tempted to use. Then we will show you a more efficient method built right into Python’s psycopg2 library.

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

What is inefficient about the scripts above? As you may have noticed, there is a “for” loop that cycles through each of the three items in our Python list. Each time (for each user), the loop runs an INSERT to create a new record in tblUsers of our Cockroach database. Now, in order to fully appreciate how inefficient that approach is, we will show you another way that is also more efficient in terms of server resources and bandwidth.

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

Analysis

With the more efficient version you see above, we have removed the “for” loop and placed the entire list as a parameter to be fed to the Cockroach SQL to run only ONE “insert into” statement, letting the database handle that ONE insertion. Simple!

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 = "26251"
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("/copy_list")
def copy_list():
    listUsers = ['Franky', 'Debra', 'Angi']
    s = ""
    s += "INSERT INTO tblUsers"
    s += "("
    s += "txtNameUser"
    s += ") VALUES ("
    s += "(%listUsers)"
    s += ")"
    try:
        cursorCRDB.execute(s, listUsers)
        cursorCRDB.commit()
    except psycopg2.Error as e:
        txtMessage = "Database error: " + e + "/n SQL: " + s
        return render_template("error_web-page.html", txtMessage = txtMessage)
    cursorCRDB.close()
    connCRDB.close()

Conclusion

In this instructional article we were schooled in how to copy a Python List into a Cockroach table. We learned that in Python terms, “List” is pretty much an “Array” with a bit of added flexibility with data typing. We used Python’s FOR loop a couple times. We also used the “INSERT INTO” database command to copy data into a new record in our CockroachDB table. A few functions and commands we touched on include: print, range, and len. Finally, we provided all of the source code for this Python application for you to use with your projects.

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.