Data Science & AI Workbench enables you to connect to a CockroachDB SQL database within your sessions, whether the database is running on-premises or on a cloud server.

To access CockroachDB, you’ll need to conda install the required driver using the following command:

conda install --channel anaconda psycopg2

Any packages you install from the command line are available during the current session only. If you want them to persist, add them to the project’s anaconda-project.yml file. For more information, see Project configurations.

After you’ve installed the psycopg2 driver, you can then use code such as this to access CockroachDB from within a notebook session:

import psycopg2
import json

"""
Get credentials from Kubernetes. The credentials were set up as a dictionary. For example:
{
    "username": "USERNAME",
    "password": "PASSWORD"
}
"""
credentials = None
with open('/var/run/secrets/user_credentials/cockroach_credentials') as f:
    credentials = json.load(f)

# Verify the credentials were pulled correctly
if credentials:
    # Connect to the database
    conn = None
    try:
        conn = psycopg2.connect(
            dbname=credentials.get('db_name'),
            host=credentials.get('host_name'),
            user=credentials.get('username'),
            password=credentials.get('password')
        )
    except:
        print("I am unable to connect to the database")

    # Get a cursor and execute select statement
    cur = conn.cursor()
    cur.execute("""SELECT * from playground""")
    rows = cur.fetchall()

    # Print out the results
    for row in rows:
        print(row)

    # Close the connection when finished
    conn.close()

See Secrets for information about adding credentials to the platform, to make them available in your projects. Any secrets you add will be available across all sessions and deployments associated with your user account.

Was this page helpful?