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.

Connect to Database and Create a new table

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')]