Help requested: support additional database types

[pbugnion]

Help requested If anybody is willing to actively support database types, reply to this issue.

Currently, jupyterlab-sql actively supports:

• PostgreSQL
• MySQL
• sqlite

Other database types may work if SQLAlchemy supports them. See here for a list of database types supported by SQLAlchemy.

It would be good to extend active support to more database types. In particular, the following have come up:

• SQL Server
• Athena
• Oracle
• Presto

Since the current maintainers don't have easy access to database servers of these types, it is unlikely that they will extend support to these by themselves. Actively supporting a database type involves being willing to QA jupyterlab-sql releases against that database type and generally being willing to answer issues related to that.

If you are interested, reply to this issue.

[dbdean]

I would be interested in seeing if any changes need to be made to make MS SQL work. I've managed to get it to login using a connection string of the form:

mssql+pyodbc://<username>:<password>@<host>:1433/<dbname>?driver=ODBC+Driver+17+for+SQL+Server

I can run queries, but tables don't show up.

I get similar behaviour with a pymssql connection string:

mssql+pymssql://<username>:<password>@<host>:1433/<dbname>

As with the pyodbc string, I can run queries, but tables don't show up.

Any ideas where I could look into the code to make that happen?

[randomrandom]

I would be interested in seeing if any changes need to be made to make MS SQL work. I've managed to get it to login using a connection string of the form:

mssql+pyodbc://<username>:<password>@<host>:1433/<dbname>?driver=ODBC+Driver+17+for+SQL+Server

I can run queries, but tables don't show up.

I get similar behaviour with a pymssql connection string:

mssql+pymssql://<username>:<password>@<host>:1433/<dbname>

As with the pyodbc string, I can run queries, but tables don't show up.

Any ideas where I could look into the code to make that happen?

I have the same case when I connect to a Postgres databases

postgresql+psycopg2://username:password@host:5432/db_name

I can run queries, but don't see the tables

[JohnTheLearner]

@pbugnion please add clickhouse. I'd be happy to help with this project's support of it.

Clickhouse is not expressly supported by many things including native sqlalchemy, but is supported reasonably well via the sqlalchemy-clickhouse module found over at https://github.com/cloudflare/sqlalchemy-clickhouse. As I've mentioned in other GH issues, it does work somewhat well in jupyterlab-sql with a good combination of it and jupyterlab versions.

[dbist]

please add support for CockroachDB, it is supported through SQLAlchemy with the following dialect sqlalchemy-cockroachdb. The following connection string fails in SQL launcher 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 with the following error Can't load plugin: sqlalchemy.dialects:cockroachdb. Is there a way to pass pypi package to the launcher?

opened: #139

[edgBR]

I would be interested into seeing support for Snowflake.

Snowflake uses ANSI compliant SQL and also as already developed an SQL alchemy package.

BR
/Edgar

[pjasielski]

My team at work is very much interested in implementing your extension. We currently use Oracle and are experiencing the same problem mentioned in issue #132

We found the bug causing it - limit() does not work for Oracle.

Therefore in function get_table_summary() in executor.py you'd need to add a logic to facilitate this type of database. Below code worked for us:

from sqlalchemy.sql import select, table, text, column

rowonum_col = column('rownum')     
query = select([text("*")]).select_from(table(table_name)).where(rownum_col <= 1000)

As this may not work for other types of databases (we haven't tested that), you should probably pass the db type to the function and based on that choose the appropriate handling. Unless you extract it directly from connection_url, but I'm not that familiar with this particular functionality as I construct connection string in a different way, so I'm not sure if this would work for all cases (perhaps it would, so the change of parameters would not be required).
def get_table_summary(self, connection_url, table_name, db_type):

I'd appreciate if you'd let me know once the change is implemented.

[saravanans200]

Hi Pascal Bugnion, Jupyterlab-sql is not working. I tried version down of python and jupyterlab. I am able to connect database but I am able to see table but I am not able to open those tables. can you suggest possibility to fetch the data