An SQL solution for Jupyter
xeus-sql: A xeus-sqlite sequel
A few months ago we released xeus-sqlite, a Jupyter kernel that allows users to make SQLite queries directly from the notebook. With the needs of the Jupyter data science community in mind we decided to expand on this project.
Today, we’re happy to announce the next step of this endeavour, bringing you xeus-sql a general-purpose database access tool for Jupyter, which allows you to make SQL queries on many different databases:
- MySQL
- PostgreSQL
- SQLite3
- DB2
- Oracle
- Firebird
- and any database with an ODBC driver.
To offer all these integrations we rely on the SOCI library as the backbone of the project. SOCI abstracts all the different database connections and query details behind a unified C++ API. xeus-sql uses SOCI and xeus to expose SQL functionalities to Jupyter.
Xeus-sql, as well as the MySQL, PostgreSQL and SQLite3 backends of SOCI are available on conda-forge, meaning you can install them easily with conda or mamba:
# Installing xeus-sql and the mysql backend
mamba install xeus-sql soci-mysql -c conda-forge# Installing xeus-sql and the postgresql backend
mamba install xeus-sql soci-postgresql -c conda-forge# Installing xeus-sql and the sqlite backend
mamba install xeus-sql soci-sqlite -c conda-forge
There are idiosyncrasies between these different SQL backends which we detail in the xeus-sql documentation and examples.
Visualizing your queries
You’re probably familiar with visualizing your SQL tables and query results in the form of… tables, of course. Jupyter’s rich display system offers the option of showing them in rich-text display or plain-text display depending on which application you’re using.
Beyond displaying queries with tables, you can now easily create Vega-Lite graphs from your query results directly in the notebook:
To support this feature xeus-sql relies on xvega, a C++ backend to vega, and custom Jupyter magics for plotting through a mini-language implemented in the xvega-bindings utility library. Besides using the mini-language you can directly provide the JSON specification for your visualization, thanks to contributions by Wang Fenjin.
Vega-Lite is a powerful library and there are many different visualizations you can create from your relational data using xeus-sql. You can see some other examples below:
We’re looking forward to exchange with users and learn more about their use cases for xeus-sql and xvega, so if you have any questions please drop them on the public QuantStack chat, or open an issue at the project’s GitHub page. As always, contributions are welcome!
Try it online!
You can try the stable version on Binder without the need of installing anything on your computer just by clicking on the image below:
Acknowledgements
We are grateful to Wang Fenjin for his contributions with respect to the use of vega specs in xeus-sql. The plotting mini-language was built upon the xvega library by Madhur Tandon. We are also grateful to the SOCI contributors for their awesome package that made this work possible.
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.
