WingNews logo WingNews GitHub [2]
top | item 23539541

A Jupyter Kernel for SQLite

266 points| Tomte | 5 years ago |blog.jupyter.org | reply

45 comments

order
[+] llimllib|5 years ago|reply
If you're interested in this, you might be interested in Datasette: https://datasette.readthedocs.io/en/stable/

Which seems to me to be farther along in providing advanced querying/faceting/visualization/sharing capabilities on top of sqlite.

(I love jupyter, and this kernel seems neat; not trying to throw stones at anybody, just to link a project in a similar domain)

[+] 3l3ktr4|5 years ago|reply
None taken! This is a much more developed project indeed.

I think the SQLite kernel for Jupyter has a nice foreseeable future with graph visualizations for the query results and the whole integration with conda, mamba and pip environments, though.

[+] antipaul|5 years ago|reply
Amazing share, my data productivity just went up significantly

I watched a video on that site from the creator, and after trivial installs of datasette and csvs-to-sqlite, I was exploring and facetting my data like never before.

I used to waste some much time generating static report summaries of my data, and now its just 2 steps away: - bundle csvs into a db - datasette it - win

[+] seemslegit|5 years ago|reply
Having to rerun the query and reload the page just to change the sort order on a small dataset is very 1997
[+] seemslegit|5 years ago|reply
Cool hack, but probably wouldn't install a separate kernel or run an entire notebook just for sqlite work.

What would be useful is for the kernel of your language of choice to provide a magic for sqlite and return results of queries in language-native data structures.

Something like this: https://pypi.org/project/ipython-sql/

[+] TallGuyShort|5 years ago|reply
I wonder if this is closer to what you're suggesting, as it already allows sharing of simple data structures between cells of distinct languages: https://polynote.org/. One cell: SQLite kernel to get data. Next cell: operates on that data.
[+] seemslegit|5 years ago|reply
Alternatively: a new type of cell in jupyter itself alongside 'code' and 'markdown' for sql work available regardless of kernel choice.
[+] santafen|5 years ago|reply
I like this bit a lot. Might have to extend it for QuestDB. I did a Notebook for QuestDB earlier this week, but having this part would be great!
[+] crazygringo|5 years ago|reply
Wow. It never even occurred to me that this was missing from Jupyter -- but in hindsight seems so obvious.

Congrats to the Jupyter team on this!

[+] reallymental|5 years ago|reply
Incredible, but wasn't this kind of available though psycopg2 (postgres connector), SQLAlchemy or any other kind of database connection library ?

I realise the difference between the kernels altogether, but how is one better than the other?

[+] jedimastert|5 years ago|reply
Weird as it might sound, I can imagine a scenario where I wouldn't need anything past SQL in some form. I've got plenty of python scripts sitting around my hard drive that are just fronts for data manipulation. Especially with the cvs import mode, I could see myself using just sqlite or whatever
[+] dataminded|5 years ago|reply
Not really. You could use the python kernel and embed your SQL code within your python code but you couldn't run cells with just SQL. It made for a very poor analyst experience.

Microsoft got it right with Azure Data Studio.

[+] yellowapple|5 years ago|reply
Well, as someone who strongly dislikes Python and strongly likes SQLite, this might give me a reason to willingly use Jupyter (granted, I guess I could do it through Julia, too, which I also strongly like, but... eh).

A lot of the time when I need to work with a bunch of data I'll fire up the SQLite CLI, snarf the data with the built-in CSV import commands, and do whatever queries I wanna do. This looks like it can provide a nicer UI around that workflow, and I'm all for it.

Datasette looks interesting for this, too; I'll have to give that a look.

[+] cheez|5 years ago|reply
I keep a SQLite buffer open in emacs, might switch to this given it's nice visualizations.
[+] aghillo|5 years ago|reply
I like this. Previously I’ve used notebooks to explain a data pipeline from different perspectives. One notebook showing the ETL process going from raw data to RDF triples in a store; one SPARQL notebook showing the raw queries; and then a final decision support notebook using a Python binding to the underlying SPARQL query library. It seemed to work well.
[+] CrazyCatDog|5 years ago|reply
This is awesome! Makes sql as accessible in the classroom as python—-we waste so much time installing SQLite and trouble shooting for students (b-schools). As soon as this is accessible in colaboratory (google), or similar, super light web interface and notebooks become trivial... THANK YOU!!
[+] justinclift|5 years ago|reply
Interesting. Wonder if it'd be possible to embed it for visualisation in a Go web app?

Was adding basic online chart capabilities to our SQLite publishing website a few weeks ago (eg:

https://dbhub.io/vis/justinclift/Marine%20Litter%20Survey%20... )

But if people could do Jupyter notebooks and visualise them like this too, that could be useful.

Hmmm, should probably set up some kind of survey on our website to ask... :)

[+] gepoch|5 years ago|reply
Excellent! I usually just type up my SQL in triple quotes and execute it from python, which gives me a little bit of templating too. Excited to give this a try (and start using more views.)
[+] Koshkin|5 years ago|reply
This looks nice. Now if only somebody wrote a Jupyter kernel for (power)shell.
[+] mweatherill|5 years ago|reply
Have you seen Azure Data Studio? It uses the same code base as Visual Studio Code and has first class support for notebooks. It includes a PowerShell kernel
[+] Keyframe|5 years ago|reply
How's this different from let's say BeakerX or Zeppelin?
[+] 3l3ktr4|5 years ago|reply
Zeppelin is not Jupyter. And I'm not sure how BeakerX works tbh, but it uses a whole docker image all the time to run right? So, might be very resource consuming, this kernel was implemented on C++. I honestly have no idea of how good is the tooling around SQL in BeakerX but might be a better alternative if you don't care about the docker image and doesn't actually need SQLite, that's different from SQL, which is what they're offering.