pbugnion / jupyterlab-sql

SQL GUI for JupyterLab
MIT License
417 stars 52 forks source link

add support for cockroachdb #139

Open dbist opened 4 years ago

dbist commented 4 years ago

With connection string cockroachdb://maxroach:maxroach@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt&port=26257

I get the following error

Can't load plugin: sqlalchemy.dialects:cockroachdb

The following code works in Jupyter once sqlalchemy-cockroachdb is installed.

# handling transaction error that we talk about in the blog and requiring to reconnect
# https://docs.sqlalchemy.org/en/13/orm/tutorial.html
# the entire sqlalchemy tutorial
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from cockroachdb.sqlalchemy import run_transaction
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String

import getpass
password = getpass.getpass()
sql_engine = create_engine('cockroachdb://maxroach:'+password+'@roach-0:26257/movr?sslcert=/certs/client.maxroach.crt&sslkey=/certs/client.maxroach.key&sslmode=verify-full&sslrootcert=/certs/ca.crt&port=26257')

Base = declarative_base()

class Vehicles(Base):
    __tablename__ = 'vehicles'
    city = Column(String, primary_key=True)
    type = Column(String)

session = sessionmaker(bind=sql_engine)
def run_select(session):
    for row in session.query(Vehicles.city, Vehicles.type).all():
        print(row.city, row.type)

run_transaction(session, run_select)