Import CSV into CockroachDB

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

Introduction

In this tutorial document we learn how to import a CSV into CockroachDB by using Flask’s psycopg2 Open function for comma-delineated text files and the Copy_from function from that same library. Here’s how we will do it:

  • What? We will learn about two functions to use for importing a text file and copying that data into a CockroachDB table. We will also learn how to use the Flask Render_template function so we can show an error page to the user.
  • How? Finally, we will build a Python application to use both the “open()” and “copy_from()” psycopg2 functions for our Cockroach applications.

Prerequisites

  • You will want to have some experience with the basics of using Cockroach, whether you are using the free dBeaver tool. There aren’t many others out yet for Cockroach. And/or writing applications with languages like Python, PHP, C#, Java, ASP.Net, VB.Net, Node.js, Ruby, etc. to write to a CockroachDB database. Here, we have used Visual Studio Code to write Python and Dbeaver to manage our Cockroach database.

Before writing our Python application, we will learn how to use the functions we mentioned above. After learning how each of those functions work, we will then build a chunk of scripts using that function to create a portion of our import application.

Psycopg2 and Open

This function requires use of the “psycop2g” library for Python. Prerequisite and syntax:

1
2
import psycopg2 # The open function and Cockroach access depend on this library.
fileContents = open('[path and file name]', '[r OR w]')

Analysis

  • “import psycopg2”: Python needs this library for the open function and CockroachDB access.
  • “open(‘path/text-file.csv’, ‘[r OR w]’)”: We supply the “open” function with a file name and either “r” or “w” (read or write). The open function fills a Python array type with the text delineated in the file designated as the “path and file name”.

Connecting to CockroachDB

1
2
3
4
5
6
7
8
9
10
11
from flask import render_template
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 = "26312"
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”: Later in our scripts we will error check. In order to send the user to a dynamic HTML page we built, we need the “render_template” function.
  • “txtHostURL…t_pw”: This is where we set up our Cockroach database credentials (parameters) like database host address, port, database name, user name, and password.
  • “connCRDB”: Here we are simultaneously creating a database object and handing it the parameters it needs.
  • “cursorCRDB”: This is where we create a “database cursor”, which we will use for database operations.

Psycopg2 and Copy_From

The copy_from function also requires the Python “psycop2g” library. Another library we are adding is flask’s “render_template” so we can show an HTML template to the user for various reasons, including conveyance of data, requesting data (like login), and showing errors. We also have to connect to the database and set up a “cursor” object.

Copy_from reads data from a file, adding that data to a CockroachDB table. Here is some script so you can see this function used in context and to start building our application:

1
2
fileContents = open('data/people.csv', 'r')
cursorCRDB.copy_from(fileContents, "tblUsers", columns=('txtNameUser', 'txtEmailAddr'), sep=",")

Analysis

  • “fileContents”: We use Python’s “open” function to create a two dimensional array, “fileContents”, and fill it with data.
  • “open”: Here we open a comma-delineated text file of users (people.csv).
  • “copy_from”: Here we copy “fileContents” into the “txtNameUser” and “txtEmailAddr” columns in the “tblUsers” table.
  • “sep=’,'”: This tells Python the delineator in the text file is a comma.

Render_template

Flask’s “Render_Template” function allows us to display an HTML page for a user, which can be filled with dynamic content we control with parameters sent with the function to the server that renders the web page and sends it to the client browser as HTML. Be sure not to confuse this function with “redirect”, which will send the user away without awaiting a return.

Render_Template syntax

1
RETURN render_template(txtName_page, txtMessage = "some data")

We feed the RENDER_TEMPLATE function with these parameters:

  • “txtName_page”: The name of our template. Be aware that you will have to store your HTML templates in a folder named “/templates”.
  • “txtMessage”: The contents of “txtMessage” will be sent, along with the user, to the page designated in “txtName_page”, an html page you build, that is set up to receive and use the value stored in “txtMessage”.

Here’s an HTML error page template we created so you don’t have to:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<html>
  <head>
    <title>HTML Template for web/CockroachDB project</title>
  </head>
  <body>
    <!-- The message you see below is why we -->
    <!-- call this a template. txtMessage is -->
    <!-- filled in by the Python file as it -->
    <!-- calls up this HTML. Later you may -->
    <!-- wish to separate page title from -->
    <!-- txtMessage. -->
    <h1>Import CSV Error Page</h1>
    <!-- div to contain form -->
    <div>
      <!-- Open the div container -->
      {{txtMessage}}
      <!-- Close the div container -->
    </div>
  </body>
</html>

A thing to note about the HTML code you see above is “{{txtMessage}}”. The error message you will see in the Python scripts below will get sent to this page, “error_web-page.html”, and the spot where you see {{txtMessage}} will be filled with the value in the “txtMessage” parameter in the render_template function so the developer (you) and user can easily see the error message.

Now that we have built and scrutinized the critical and potentially new functions needed for our application, let’s examine a full listing of the source scripts you can copy directly into your Cockroach web/database projects:

Cockroach Python 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
28
29
30
31
32
33
34
35
36
from flask import Flask
from flask import render_template # to render the error page
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 = "26312"
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("/import")
def csv_import():
    # Trap errors for opening the file
    try:
        txtPathAndFile = "data/people.csv"
        fileContents = open(txtPathAndFile, 'r')
    except psycopg2.Error as e:
        txtMessage = "SQL error: " + e + "/n open() text file: " + txtPathAndFile
        return render_template("error_web-page.html", txtMessage = txtMessage)
    # Trap errors for copying the list to our database
    try:
        cursorCRDB.copy_from(fileContents, "tblUsers", columns=('txtNameUser', 'txtEmailAddr'), sep=",")
    except psycopg2.Error as e:
        txtMessage = "Database error: " + e + "/n copy_from"
        return render_template("error_web-page.html", txtMessage = txtMessage)
    # Got to here: Success!
    cursorCRDB.close()
    connCRDB.close()

INCREASE SECURITY: Use Stored Procedures

Protect the integrity of your users’ data by learning about Cockroach Procedures and other ways to protect against the most popular kind of hacks:

Conclusion

In this instructional article your knowledge grew about how to use Python to import a CSV file into two columns in a Cockroach table. We used a Python array, the open and copy_from functions from the psycopg2 library, and the “render_template” function from the Flask framework, to show error reports to the user. We took it step by step to build an “import from CSV into CockroachDB” web database application using Python and CockroachDB. Finally, we shared all the source code for this Python application here for you to copy into your applicable 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.