Connecting to CockroachDB cluster from Python

Here we will connect to CockroachDB database using Python.
Specifically we will be using psycopg2 library

Create a Virtual Environment and install psycopg2 library
$ python3 -m venv env
$ source env/bin/activate
(env) $ pip3 install psycopg2-binary

Collecting psycopg2-binary
  Using cached psycopg2_binary-2.9.3-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB)
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.3

Getting the database connection parameters

-To get the connection details, go the cluster details page & take the parameters.

alter-text

Connect to Database and Create a new table

-here the database name is “defaultdb”.

import psycopg2

conn = psycopg2.connect(
    dbname="tester-1760.defaultdb",
    user="user01",
    password="L-fGdxFbgqQ6srSrYIqRxw",
    host="free-tier8.aws-ap-southeast-1.cockroachlabs.cloud",
    port="26257",
    options="--cluster=tester-1760"
    )

cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS STUDENT (ID INT PRIMARY KEY, NAME STRING)")
cursor.close()
conn.close()
Insert data
import psycopg2
conn = psycopg2.connect(
    dbname="tester-1760.defaultdb",
    user="user01",
    password="L-fGdxFbgqQ6srSrYIqRxw",
    host="free-tier8.aws-ap-southeast-1.cockroachlabs.cloud",
    port="26257",
    options="--cluster=tester-1760"
    )

cursor = conn.cursor()
cursor.execute("INSERT INTO STUDENT (ID, NAME) VALUES (1, 'John Doe')")
cursor.close()
conn.close()
Query data from table
import psycopg2
conn = psycopg2.connect(
    dbname="tester-1760.defaultdb",
    user="user01",
    password="L-fGdxFbgqQ6srSrYIqRxw",
    host="free-tier8.aws-ap-southeast-1.cockroachlabs.cloud",
    port="26257",
    options="--cluster=tester-1760"
    )

cursor = conn.cursor()
cursor.execute("SELECT * FROM STUDENT")
rows = cursor.fetchall()
>>> print(rows)
[(1, 'JOHN DOE')]


Related Posts

Installing CockroachDB client on Ubuntu 20.04

Here we will install the CockroachDB database & client on Ubuntu 20.04 Operating system You will need to use sudo if you get any permission errors.

Read more

Creating a CockroachDB cluster

CockroachDB is a distributed SQL database management system, developed by Cockroach Labs. It is relatively new entrant to the database space & designed for speed, scale, and survival.

Read more

Top Free Jupyter Runtimes to get started with Machine Learning journey

Jupyter notebooks are a great tool for data analysis due to rich interface features it offers. But for training fairly good models require good amount CPU, GPU, RAM and storage.

Read more