A Jupyter kernel for SQLite

While it is well known in the Python scientific computing community, Jupyter is in fact a language-agnostic development environment. High-quality language kernels exist for the main languages of data sciences, such as Python, C++, R or Julia.
But another important tool for data science is the SQL family of programming languages. Today, we announce the release of a Jupyter kernel for SQLite.

This new kernel allows the user to use the complete SQLite syntax as well as some extra operations such as opening or closing a database file, or visualizing the data in different ways using Jupyter magics.

HTML and Text table visualizations for different Jupyter interfaces

To make the task of implementing a new Kernel for Jupyter easier, we make use of Xeus, a library providing a solid implementation of the Jupyter kernel protocol, so that we can focus on implementing the language-specific parts of the kernel.

As a connection between SQLite’s native C implementation and C++ we’re using SRombauts’ library SQLiteCpp.

Current state

xeus-SQLite is still under active development but it offers a fully functional SQLite interface and magics to perform higher-level operations that are outside of the scope of the SQL syntax, such as creating, opening, or closing SQLite databases. Some of the things you can do with xeus-SQLite are creating a new database, loading it, backing it up or deleting it. You can also check if a table exists, set and reset keys of a database and get information about it. You can find more about the use of these methods in SQLite’s documentation.

xeus-SQLite provides rich HTML display of tables in the Jupyter Notebook and Jupyter Lab. For the Jupyter Console we make use of the tabulate library for textual display.

Different commands being executed on xeus-SQLite

The future

For the future of xeus-SQLite we want to create an intuitive form of visualizing data: creating plots, graphs, charts, maps and much more from your SQLite query results, all in the same notebook. Currently we’re exploring the use of Vega in xeus-SQLite, a declarative language for creating interactive visualization designs and can do bar plots using jupyter magics:

SQLite query result with bar and table outputs for same query

This feature is still in very early stages and being developed in this branch.

We also plan on producing a static build of xeus-SQLite bundling xeus and the SQLite library into a single executable that can be easily distributed.

Try it online!

You can try the stable version without the need of installing anything on your computer just by clicking on the image below:

This won’t grant you access to the experimental branch where we’re testing, if you’re interested in trying it please check the installation guide and make sure you’re in the right branch.

Installation

Note that you first need to have Jupyter installed on your computer. You can install xeus-sqlite using mamba:

mamba install xeus-sqlite -c conda-forge

Or conda:

conda install xeus-sqlite -c conda-forge

About the author

My name is Mariana Meireles and I’m a software developer working for QuantStack. I care deeply about the impacts that technology has in the world and try my best to be the change I want to see by contributing to open source projects that stand upon libre and diverse standards.

Prior to QuantStack I worked as a developer on the PySide team at the Qt Company and as a web performance developer at Mozilla.