Shulou(Shulou.com)05/31 Report--

This article focuses on "how to make Jupyter support SQL processing", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to make Jupyter support SQL processing"!

Now using Jupyter for data processing is not a new thing for data workers. However, how to import a large amount of data is a tricky task. We all know that relational database is the most important carrier of data storage, so the support of database is an urgent need of Jupyter data science community.

Jupyter has previously released a kernel xeus-sqlite that allows users to query SQLite directly from notebook. A few days ago, Jupyter released a new project, xeus-sql, which is an extension of xeus-sqlite and a general database access tool for Jupyter. It can be used for SQL queries on most relational databases.

Database support

Xeus-sql supports the vast majority of data on the market, including:

MySQL

PostgreSQL's

SQLite3

DB2

Oracle

Firebird

And any database that supports the ODBC driver.

To provide all of these integrations, xeus-sql relies on the SOCI library as the backbone of the project. SOCI abstracts all the different database connection and query details after the unified C++ API. Xeus-sql exposes SQL functionality to Jupyter using SOCI and xeus.

Installation

To ensure that the installation works properly, it is best to install xeus in a new conda environment. Xeus-sql also needs to be installed using miniconda, and a full anaconda may conflict. The safest use is to create an environment named after the xeus-sqlminiconda installation:

Conda create-n xeus-sql conda activate xeus-sql

Install from conda

Conda forge provides packaged versions of MySQL,PostgreSQL and SQLite, which can be easily installed with conda or mamba and xeus-sql using the conda package manager:

Conda install xeus-sql jupyterlab-c conda-forge

Or use mamba:

Mamba install xeus-sql jupyterlab-c conda-forge

Or you can install it separately:

Mamba install xeus-sql soci-mysql-c conda-forge mamba install xeus-sql soci-postgresql-c conda-forge mamba install xeus-sql soci-mysql-c conda-forge

There are some differences between different SQL backends, you can refer to the xeus-sql detailed documentation and examples to learn.

Usage

To connect to MySQ, you need to first install xeus-sql and soci-mysql, and then load the connection database with LOAD:

% LOAD mysql db=dbname user=user1 passwordPassword123 passwordPasswordPasswordPasswordPasswordPasswordPasswordPasswordPasswordPasswordPassword123password

The above db database name, user is the user name of the linked library, and password is the user password.

After a successful connection, you can execute database commands and SQL statements, such as:

Show databases; SELECT * FROM test INSERT INTO example VALUES (2, 'Core') SELECT * FROM example INSERT INTO example VALUES (3,' Table')

Other database backends are similar:

Firebird:

% LOAD firebird service=firebird.fdb user=SYSDBA

Postgresql:

% LOAD postgresql dbname=newdvdrental

Visual query

For users familiar with visualizing SQL tables and query results in tabular form, Jupyter's rich display system provides the option to display them as rich text or plain text depending on the application being used.

In addition to displaying queries with tables, you can easily create Vega-Lite drawings based on query results directly in Notebook:

Vega-Lite is a powerful library that can use xeus-sql to create many different visualization files from relational data.

To support this feature, xeus-sql relies on xvega (the C++ back end of vega) and custom Jupyter magic to draw miniature languages implemented in the xvega-bindings utility library. In addition to using mini-languages, visual JSON specifications can be provided directly.

Summary

The launch of xeus-sql makes Jupyter more powerful, and it is very convenient for data workers to process and visualize the data. At the same time for traditional dba and database users can use Jupyter as a convenient database client.

At this point, I believe you have a deeper understanding of "how to make Jupyter support SQL processing". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.