pbugnion / jupyterlab-sql Goto Github PK
View Code? Open in Web Editor NEWSQL GUI for JupyterLab
License: MIT License
SQL GUI for JupyterLab
License: MIT License
Having attempted to install I get:
(base) Robins-MBP:~ robincole$ jupyter labextension list
JupyterLab v0.35.4
Known labextensions:
app dir: /Users/robincole/anaconda3/share/jupyter/lab
@jupyter-widgets/jupyterlab-manager v0.38.1 enabled OK
@jupyterlab/plotly-extension v0.18.2 enabled OK
jupyterlab-chart-editor v1.1.0 enabled OK
jupyterlab-sql v0.3.0 enabled X
plotlywidget v0.11.0 enabled OK
"[email protected]" is not compatible with the current JupyterLab
Conflicting Dependencies:
JupyterLab Extension Package
>=0.19.1 <0.20.0 >=1.0.0 <2.0.0 @jupyterlab/application
>=0.19.1 <0.20.0 >=1.0.0 <2.0.0 @jupyterlab/launcher
At the moment, when the content refreshes in both the table explorer and the database explorer, errors in the refresh are not caught.
We should at least make sure that we reset the loading state even in the case of errors.
It looks like a spoof version of this is available on NPM: https://www.npmjs.com/package/jupyterlab-sql
I don't know what is in that package, but you should definitely not install it. Always install jupyterlab-sql from Pypi.
If you have installed it, I strongly recommend wiping your JLab environment.
This extension seems to be incompatible with the most recent releases of jupyterlab (>=1.1.0):
$ jupyter lab --version
1.1.1
$ jupyter labextension update jupyterlab-sql
No compatible version found for jupyterlab-sql!
$ jupyter labextension list
JupyterLab v1.1.1
Known labextensions:
app dir: /home/zane/miniconda3/envs/pudl/share/jupyter/lab
@jupyter-widgets/jupyterlab-manager v1.0.2 enabled OK
@jupyterlab/toc v1.0.1 enabled OK
jupyterlab-sql v0.3.0 enabled X
jupyterlab_vim v0.11.0 enabled OK
qgrid v1.1.1 disabled OK
The following extension are outdated:
jupyterlab-sql
Not sure if this is a true incompatibility, or if a list of compatible versions needs to be updated.
It's a great extension for making working with databases more convenient. Thank you for your efforts!
This link describes the process by which a user can change extension settings.
I could not find a settings file referring to this extension (something of the form<package_name>/<extension_name>.jupyterlab-settings). While a file can be created by the user I am unsure which settings would be alterable.
Are there any settings currently alterable?
At the moment, the text in the table can't be selected, copied or pasted.
I don't know what is your design limitation,
example of typical wish:
-- SQLite Memo (Demo = click on green "->" and "@" icons)
-- to CREATE a table 'items' and a table 'parts' :
DROP TABLE IF EXISTS item; DROP TABLE IF EXISTS part;
CREATE TABLE item (ItemNo, Description,Kg , PRIMARY KEY (ItemNo));
CREATE TABLE part(ParentNo, ChildNo , Description TEXT , Qty_per REAL);
-- to CREATE an index :
DROP INDEX IF EXISTS parts_id1;
CREATE INDEX parts_id1 ON part(ParentNo Asc, ChildNo Desc);
-- to CREATE a view 'v1':
DROP VIEW IF EXISTS v1;
CREATE VIEW v1 as select * from item inner join part as p ON ItemNo=p.ParentNo;
-- to INSERT datas
INSERT INTO item values("T","Ford",1000);
INSERT INTO item select "A","Merced",1250 union all select "W","Wheel",9 ;
INSERT INTO part select ItemNo,"W","needed",Kg/250 from item where Kg>250;
-- to use COMMIT and ROLLBACK :
BEGIN TRANSACTION;
UPDATE item SET Kg = Kg + 1;
COMMIT;
BEGIN TRANSACTION;
UPDATE item SET Kg = 0;
select Kg, Description from Item;
ROLLBACK;
select Kg, Description from Item;
-- to use SAVEPOINT :
SAVEPOINT remember_Neo; -- create a savepoint
UPDATE item SET Description = 'Smith'; -- do things
SELECT ItemNo, Description FROM Item; -- see things done
ROLLBACK TO SAVEPOINT remember_Neo; -- go back to savepoint state
SELECT ItemNo, Description FROM Item; -- see all is back to normal
RELEASE SAVEPOINT remember_Neo; -- free memory
I can contribute a home-made sql splitter, if it's the problem
https://github.com/stonebig/sqlite_bro/blob/master/sqlite_bro/sqlite_bro.py#L1393..L1493
I'm on jupyterlab-sql 0.1.4
with JupyterLab
0.35.4with JupyterHub
0.9.4` in Ubuntu 16.04 trying to connect to a remote PostgreSQL instance (9.6, if that matters).
I have postgres://username:[email protected]:5432/postgres
and my query is SELECT 1;
and I have a spinny wheel next to the connection string.
The Firefox console gives:
serverconnection.js:192 POST https://server.ip/jupyter/user/USERNAME/jupyterlab-sql/query?1552318333029 404 (Not Found)
handleRequest @ serverconnection.js:192
makeRequest @ serverconnection.js:75
(anonymous) @ api.js:23
(anonymous) @ api.js:7
push.ScrF.__awaiter @ api.js:3
getForQuery @ api.js:17
(anonymous) @ widget.js:64
(anonymous) @ widget.js:7
push.eeTp.__awaiter @ widget.js:3
updateGrid @ widget.js:60
JupyterLabSqlWidget.editor.execute.connect @ widget.js:38
invokeSlot @ index.js:475
emit @ index.js:433
push.qUp9.Signal.emit @ index.js:106
Editor._widget.executeCurrent.connect @ editor.js:16
invokeSlot @ index.js:475
emit @ index.js:433
push.qUp9.Signal.emit @ index.js:106
_onKeydown @ editor.js:48
EditorWidget.editor.addKeydownHandler @ editor.js:40
algorithm_1.ArrayExt.findFirstIndex.handler @ editor.js:113
findFirstIndex @ array.js:237
CodeMirrorEditor.codemirror_1.default.on @ editor.js:112
signal @ codemirror.js:1166
signalDOMEvent @ codemirror.js:1175
onKeyDown @ codemirror.js:7155
(anonymous) @ codemirror.js:3884
widget.js:5 Uncaught (in promise) SyntaxError: Unexpected token < in JSON at position 1
Promise rejected (async)
step @ widget.js:6
(anonymous) @ widget.js:7
push.eeTp.__awaiter @ widget.js:3
updateGrid @ widget.js:60
JupyterLabSqlWidget.editor.execute.connect @ widget.js:38
invokeSlot @ index.js:475
emit @ index.js:433
push.qUp9.Signal.emit @ index.js:106
Editor._widget.executeCurrent.connect @ editor.js:16
invokeSlot @ index.js:475
emit @ index.js:433
push.qUp9.Signal.emit @ index.js:106
_onKeydown @ editor.js:48
EditorWidget.editor.addKeydownHandler @ editor.js:40
algorithm_1.ArrayExt.findFirstIndex.handler @ editor.js:113
findFirstIndex @ array.js:237
CodeMirrorEditor.codemirror_1.default.on @ editor.js:112
signal @ codemirror.js:1166
signalDOMEvent @ codemirror.js:1175
onKeyDown @ codemirror.js:7155
(anonymous) @ codemirror.js:3884
What else would be helpful to post for debugging? jupyterhub -f config.py --debug
just gives the same 404 AFAICT.
Currently, the table remains white even when JupyterLab is in dark mode.
Following the CSV renderer is probably a good way to start.
The following are outstanding for release 0.3.3:
I did run as root on my ubuntu server, and everything looks good. But when I restart jupyterlab, I don't see it in the launcher options. I am on jupyterlab 0.35.6 Thanks.
"pip install jupyterlab_sql
jupyter serverextension enable jupyterlab_sql --py --sys-prefix
jupyter lab build"
And I do see :
jupyter serverextension list
config dir: /usr/etc/jupyter
jupyterlab enabled
- Validating...
jupyterlab 0.35.6 OK
jupyterlab_sql enabled
- Validating...
jupyterlab_sql OK
config dir: /usr/local/etc/jupyter
jupyterlab enabled
- Validating...
jupyterlab 0.35.6 OK
It would be useful to have unit tests for the classes in executor.py
. We can probably mock out the sqlalchemy calls?
Hi!
I've trying to query my DB while connected without success. I've tried simple queries but in some instances i get a 'SyntaxError: invalid syntax', 'java.lang.RuntimeException: No datasource', 'NameError: name 'Select' is not defined' and when using the SQL window the command doesn't execute. Any ideas how i can solve this issue?
Many thanks!
We also want to build a jupyterlab extension. I am wondering whether it is necessary to type the command "jupyter labextensions install ..." or "jupyter lab build" every time I want to debug my project (JS code). It costs a few minutes and I want to save those time. Do you know some quicker ways to debug the project? Thanks!!!
Now that we have multiple contributors, we should include a list of names.
In gmaps, we include an AUTHORS file. We can probably do the same for this.
Since there's a couple of different installation steps, it would be easier to have this on conda-forge.
I am trying to connect this extension to a presto
instance, however I have problems with two things.
First of all - is presto connection supported at this moment? I can't find anything related to the presto SQL alchemy plugin. Providing that this is supported, can I pass some connect_args
in the URL when connecting via the jupyterlab-sql extension? If so, what would be the URL for the sample params as below?
create_engine(
'presto://user@host:443/hive',
connect_args={'protocol': 'https',
'session_props': {'query_max_run_time': '1234m'}}
)
every time i use jupyter lab it will be in anaconda2' envs
Greetings
I'm using Docker for MySQL and Jupyter, and am having difficulties using this extension. I can see the SQL icon and run it, but when I enter my username, password, and database hostname, the only thing I see is 'NoneType' object has no attribute 'replace'
.
Here are the relevant parts of my docker-compose
:
version: "3.0"
services:
db:
restart: always
image: "mariadb"
volumes:
- ./db/data:/var/lib/mysql
networks:
- db_net
jupyter:
restart: always
build: ./py
networks:
- db_net
- web_internal
Here is an excerpt from my Jupyterlab Dockerfile:
FROM jupyterhub/jupyterhub
RUN pip install --upgrade pip
RUN conda install jupyterlab
RUN jupyter labextension install @jupyterlab/hub-extension
RUN pip install jupyterlab_sql pip mysqlclient
RUN jupyter serverextension enable jupyterlab_sql --py --sys-prefix
RUN jupyter lab build
Versions: Jupyterlab 1.1.4, Python 3.6.9, IPython 7.8.0
We should definitely support sqlite.
From the little I understand about sqlite, the execution model is fairly strongly dependent on the database being accessed by a single thread. This is at odds with the model we use here, where a new sqlalchemy engine is created for every query. Each of these engines could belong to a different thread in the tornado application.
I suspect one way to do this would be to push database queries through a single thread (probably just for sqlite).
While trying to connect to Oracle database by:
oracle://user:passwd@host:1521/service_name
I get the following message:
"Failed to reach server endpoints. Is the server extension installed correctly?"
Where to start solving this problem?
The Table
component does not have any tests. We could test it and e.g. mock out datagrid and the datagrid extensions?
As of now, it seems there is no way to view tables unless they are under the default schema, e.g. dbo.table
It would be useful to either as a default show all schemas and the underlying tables, or at least somehow allow the user to select the schema (something that is not possible in the connection string). I guess an implementation should be possible, as the extension builds upon sqlalchemy, where schema can easily be selected when listing table names - engine.table_names(schema=foo)
.
Best regards
Do you plan to implement database objects explorer (like File explorer) alike database IDE’s have?
Hello, when I try to fetch a table with Chinese character in it, it shows "?" instead. How can I fix it? Usually I change system NLS_LANG for this type of problem, but it's not working in this case.
For some reason, the Travis CI badge no longer shows on PRs. The tests are running fine on Travis, as far as I can tell.
At the moment, the only documentation is the README. It would be good to have something in the app.
Currently, the table explorer just displays the first 1000 rows. Having a button to fetch more rows would be useful.
In an unfamiliar database, it would be nice to be able to browse around in the various tables looking at samples of the data while constructing a custom query (i.e. on the same page) so that you can see what all the column names, table names, and values look like, without having to navigate away from the query that you're building. Being able to submit the query, and then continue browsing around as you refine it and re-submit would also be helpful, allowing easy iterative improvement and/or increasingly complex query construction.
Hi, first thanks for making this. It is quite cool.
I am having trouble getting the queries to execute, so I am hoping that I am doing something dumb that you can point out to me.
First, I'm on Ubuntu 16.04 with Python 3.6.8
$ python --version
Python 3.6.8
$ pip --version
pip 19.0.3 from /usr/lib/python3.6/site-packages/pip (python 3.6)
and I've created a virtual environment to try to test this
$ python3 -m venv jupyterlab-testing
in which I've run the following
pip install jupyterlab_sql
pip install mysqlclient # So can have MySQL with SQLAlchemy on Python 3.6
jupyter serverextension enable jupyterlab_sql --py --sys-prefix
jupyter lab build
As an example on my local machine I'm using the MySQL example employees database which I've installed
git clone [email protected]:datacharmer/test_db.git
cd test_db
mysql < employees.sql
I can then launch with jupyter lab
and navigate to the SQL tab, but then when I enter in the corresponding URL and a valid query and hit Ctrl+Enter
(like I would execute a Jupyter Notebook cell) nothing happens
It isn't clear to me how to execute the query as my keyboard commands don't seem to do anything
As a test, I've also made sure that I can actually access the database in Python with SQLAlchemy
>>> from sqlalchemy import create_engine
>>> engine = create_engine('mysql://USERNAME:PASSWORD@localhost:5432/employees)
>>> engine.connect()
<sqlalchemy.engine.base.Connection object at 0x7f96c89692e8>
>>> print(engine.table_names())
['departments', 'dept_emp', 'dept_manager', 'employees', 'salaries', 'titles']
>>> with engine.connect() as con:
... rs = con.execute('SELECT * FROM employees LIMIT 10')
... for row in rs:
... print(row)
...
(10001, datetime.date(1953, 9, 2), 'Georgi', 'Facello', 'M', datetime.date(1986, 6, 26))
(10002, datetime.date(1964, 6, 2), 'Bezalel', 'Simmel', 'F', datetime.date(1985, 11, 21))
(10003, datetime.date(1959, 12, 3), 'Parto', 'Bamford', 'M', datetime.date(1986, 8, 28))
(10004, datetime.date(1954, 5, 1), 'Chirstian', 'Koblick', 'M', datetime.date(1986, 12, 1))
(10005, datetime.date(1955, 1, 21), 'Kyoichi', 'Maliniak', 'M', datetime.date(1989, 9, 12))
(10006, datetime.date(1953, 4, 20), 'Anneke', 'Preusig', 'F', datetime.date(1989, 6, 2))
(10007, datetime.date(1957, 5, 23), 'Tzvetan', 'Zielinski', 'F', datetime.date(1989, 2, 10))
(10008, datetime.date(1958, 2, 19), 'Saniya', 'Kalloufi', 'M', datetime.date(1994, 9, 15))
(10009, datetime.date(1952, 4, 19), 'Sumant', 'Peac', 'F', datetime.date(1985, 2, 18))
(10010, datetime.date(1963, 6, 1), 'Duangkaew', 'Piveteau', 'F', datetime.date(1989, 8, 24))
Any thoughts on what the problem might be?
At the moment, we only support Shift + Enter, but apparently some users use Ctrl + Enter to execute cells. See issue #47.
It would be nice for users to be able to save connections. Initially, this could be through a configuration file?
We shouldn't support dispatching the query if the statement box is empty.
Do you plan to implement Oracle support?
When I run
jupyter serverextension enable jupyterlab_sql --py --sys-prefix
I get the following error:
ImportError: cannot import name 'Draft7Validator' from 'jsonschema' (/usr/lib/python3/dist-packages/jsonschema/init.py)
This is with jupyter 4.4.0, python 3.7.3
It seems there is only support for 1 query per SQL tab instance with no ability to save. Is this correct?
When I run - jupyter serverextension enable jupyterlab_sql --py --sys-prefix
It says ImportError: cannot import name 'Draft7Validator
.
As suggested in one of the issues, I also tried - pip install --upgrade jsonschema
. But it says Requirement already satisfied
.
Can someone help me with this?
I apologize as this seems rather simple, but do you have an example of connecting to a database? I cannon seem to get this to run after install.
I am using this format:
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
I am not sure where to put the statement. Could you please advise?
When the cursor is not explicitly in the connection string box, the password should be obscured. We could bind to the focus / blur events on the input to mediate this.
We should initially at least have auto-completion for SQL syntax. As a second stage, auto-completion on table, column and view names would be useful.
At the moment, when the page is refreshed, the state is lost. By contrast, notebooks revert to the saved state.
We should probably keep:
I've tried this with a clickhouse as well as a postgres database where both work inside a notebook after using %sql $dbString
to connect where dbString
is a string in SQL Alchemy format. Using the same dbString
, though, does NOT work in the SQL window while I was successfully connecting and querying in a notebook. As mentioned in the title of the issue, there is no error, etc. either in the window or in the terminal where jupyter lab was launched from that indicates what the issue might be.
Below is what I see indefinitely after pressing enter in the field where I entered the database URL:
I followed the instructions while installing in my conda environment. No errors during installation. I restarted jupyter lab after the install as well. Note that I couldn't pip search jupyterlab_sql
, but the hyphenated pip search jupyterlab-sql
version does show the below installed version.
Below are the installed versions:
jupyterlab 1.0.2
jupyterlab-launcher 0.10.2
jupyterlab-server 1.0.0
jupyterlab-sql 0.3.1
It may not matter, but I found the below interesting that one config file shows jupyterlab_sql, but another doesn't when cat'ing some of the config files:
...anaconda3/envs/py3/etc/jupyter/jupyter_notebook_config.d/jupyterlab.json
{
"NotebookApp": {
"nbserver_extensions": {
"jupyterlab": true
}
}
}
vs
anaconda3/envs/py3/etc/jupyter/jupyter_notebook_config.json
{
"NotebookApp": {
"nbserver_extensions": {
"jupyterlab": true,
"jupyterlab_sql": true
}
}
The extension is installed by yarn, and we commit yarn.lock
, so we should be using yarn in the CI pipeline to install dependencies, not npm
.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.