CockroachDB to CSV

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

Introduction

In this tutorial document we learn about how to export data from CockroachDB into a CSV using Python’s psycopg2 “OPEN” and “COPY_EXPERT” functions for creating a comma-delineated text file and moving data into that file, along with CockroachDB’s “COPY TO” function to get the data we want out of a Cockroach SQL code. Here is how the lesson will go:

  • We will learn about the following functions to use for exporting a text file and copying that data into a CockroachDB table: Open(), Copy_Expert(), “Copy To”, render_template, and Format().
  • After learning how the primary pieces (functions) here work, we will code a Python application that uses all of these Cockroach and Python functions to extract data from a database into a CSV file.

Prerequisites

  • You will want to have a basic understanding of Cockroach, whether you’re utilizing the “dBeaver” tool (free) or another database admin tool. Cockroach doesn’t have many others available as of this writing.
  • Ability to write applications with languages like Python, PHP, C#, Java, ASP.Net, VB.Net, and Node.js to write to a CockroachDB database. Here, we have used Visual Studio Code to write Python. It is not necessary for you to use the same tools we do.
  • Optional and probably useful: why prefix variables, column names, table names, etc. as you see us doing in this tutorial document. For example, naming “txtVariableName” with the “txt” you see at the starting point in order to delineate it as a “text” or “string” type and “tbl” before table names in order to mark those objects as tables.

Before writing our Cockroach/Python application for importing files into CockroachDB, we will learn how to use the functions mentioned above. After grasping how each function works, we will then build a chunk of scripts using that function to create a portion of our import application.

CockroachDB COPY TO

1
COPY tblData (optional col01, col02, etc) | optional txtSQL TO optional filePathAndFile | optional STDOUT WITH parameters
  • “tblData”: This refers to the Cockroach source table you want to pull data from.
  • “col01”: Optional column names to be read. If no columns are specified, all columns will be copied.
  • “txtSQL”: You can plug some SQL into this spot instead of column names.
  • “txtPathAndFile”: A path and file name can go here, which would tell CockroachDB where to save the file.
  • “STDOUT”: You can specify the data pulled from Cockroach to be sent as output for Python to gather up.
  • “parameters”: This optional field can have any of the following:
  • And many others, including: FORMAT, OIDS, DELIMITER, NULL, HEADER, QUOTE, ESCAPE, FORCE_QUOTE, FORCE_NOT_NULL, and ENCODING.

Psycopg2’s OPEN function

This Psycopg2 function can be used to create a file (w), open for write (w), or open a file for reading (r). It requires the installation and import of the “psycop2g” library. Here’s the syntax:

1
2
import psycopg2
open('[path/MyFileName]', '[r OR w]') AS fileOutput

Analysis

  • “import psycopg2”: Python requires psycopg2 for use of the “open” function.
  • “open(‘path/MyFileName’, ‘[r OR w]’)”: We supply the “open” function with a file name and either “r” or “w”, which means read or write. The open function either fills a Python array type with the text delineated in the file designated as the “path/filename.extension” OR, if we chose “w” (write), creates and/or writes to that file.

Now to put our newfound understanding of the Cockroach “COPY” function and Python’s “Open” and “Copy_expert” functions into operation! We are going to write the following Python scripts so that ease of understanding and readability are prioritized vs what all is needed for your application. Not to worry because at the end of this instructional article we will provide you with the full, working source scripts.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# create a SQL code to specify which values we want from the database.
s = ""
s += "SELECT * FROM "
s += "tblUsers"

# set up database connection.
txtDBname = "Gimme My Data"
txtNameUser = "Master of this database"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "23541"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

# Use COPY on the SQL we created above.
SQLforFileOutput = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(s)

# Initialize a variable to store our file path and name.
txtPathAndFile = "c:\data\peoples.csv"
WITH Open(txtPathAndFile, 'w') as f_output:
    cursorCRDB.copy_expert(SQLforFileOutput, f_output)

Analysis

  • “SELECT *”: We’re asking for all available columns from the “tblUsers” table.
  • “No semicolon at the end?”: Yes. Cockroach’s documentation recommends this for this use case; “COPY TO”.
  • “SQLforFileOutput”: Create an object and fill it with data. This object will be used next in the “Open” and “Copy_Expert” functions.
  • “WITH open”: Create a CSV file and open it up for writing, using “txtPathAndFile”.
  • “copy_expert “: Use the database cursor we created earlier, the “SQLforFileOutput” data, and point it to the CSV file that is standing by using “f_output”.

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 projects:

Cockroach 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
37
38
39
40
41
from flask import Flask
import psycopg2

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

txtDBname = "Name of my database"
txtNameUser = "CockroachDB user with permissions for Python to get to"
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("/export")
def csv_export():
    s = "'"
    s += "SELECT *"
    s += " FROM "
    s += "tblUsers"
    s += "'"

    # Use the COPY function on the SQL we created above.
    SQLforFileOutput = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(s)

    # Set up a variable to store our file path and name.
    txtPathAndFile = "c:\data\peoples.csv"

    # Trap potential errors for file opening
    try:
    WITH Open(txtPathAndFile, 'w') as f_output:
        cursorCRDB.copy_expert(SQLforFileOutput, f_output)
    except psycopg2.Error as e:
        txtMessage = "Error: " + e + "/n SQL code we ran: " + s + "/n txtPathAndFile: " + txtPathAndFile
        return render_template("errors.html", txtMessage = txtMessage)

    cursorCRDB.close()
    connCRDB.close()

Conclusion

In this instructional article your knowledge grew about how to export data using Cockroach, CSV, and Python. We combined the use of the “COPY” Cockroach function with the format, open, and copy_expert Python functions. Some other functions we used here were render_template so that we could do some error reporting. This required use of some “try and except” error checking. We explained the parts first, and then dove in and created a fully operational Python application for exporting data from Cockroach into a file. Finally, we shared all the source code here for you to use in 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.