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
-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 moreCreating 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 moreTop 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