SQL-interface in JupyterLab

SQL-interface voor JupyterLab

Meestal staat de data waarmee we werken opgeslagen in zogenoemde databases, en een kerntaak van een data scientist is om data uit databases te kunnen benaderen en analyseren. Daarom is het een goed idee om een naadloze interface te hebben tussen SQL-databases en Jupyter Notebook/Lab, zodat het ophalen en bewerken van data eenvoudiger en efficiënter wordt. Deze interface kun je op twee manieren realiseren:

1. Met de IPython SQL Magic-extensie

Magic-commando's zijn een set handige functies in Jupyter Notebooks die zijn ontworpen om veelvoorkomende problemen in standaard data-analyse op te lossen. Je kunt alle beschikbare magics bekijken met %lsmagic.

De IPython SQL magic-extensie maakt het mogelijk om SQL-queries rechtstreeks in codecellen te schrijven en de resultaten direct in pandas DataFrames in te lezen (Bron). Dit werkt zowel in de traditionele notebooks als in de moderne Jupyter Labs.

SQL-module installeren in de notebook

 !pip install ipython-sql

De SQL-module laden

 %load_ext sql

Bovenstaand magic-commando laadt de ipython-sql-extensie. We kunnen verbinden met elke database die wordt ondersteund door SQLAlchemy. Hier verbinden we met een SQLite-database. Voer het volgende commando in de codecel in:

%sql sqlite://

Als je de output ‘Connected: @None' krijgt, betekent dit dat de verbinding is gemaakt.

• Een database maken

We maken ten slotte een demotabel met de naam EMPLOYEES om de werking te laten zien.

%%sql
CREATE TABLE EMPLOYEE(firstname varchar(50),lastname varchar(50));
INSERT INTO EMPLOYEE VALUES('Tom','Mitchell');
INSERT INTO EMPLOYEE VALUES('Jack','Ryan');

We kunnen nu queries uitvoeren op onze database.

 %sql SELECT * from EMPLOYEE;
 ----------------------------------
 * sqlite://
 Done. 

Bovenstaande query geeft de volgende tabel als resultaat.

• Werken met een bestaande database

We kunnen ook met de magic-functie verbinding maken met een bestaande database. Voor deze tutorial gebruiken we de SQL_SAFI-database (Studying African Farmer-led Irrigation (SAFI) database). "Het SAFI-project is een onderzoeksproject naar landbouw- en irrigatiemethoden van boeren in Tanzania en Mozambique. Deze dataset bestaat uit enquêtedata over huishoudens en landbouw" (Bron). Je kunt hem hier downloaden.

 #Specifying the path of the database
 %sql sqlite:////Users/Parul/Desktop/SQL_SAFI.sqlite
 -----------------------------------------------------------

 'Connected: @/Users/Parul/Desktop/SQL_SAFI.sqlite' 

Bovenstaande instructie opende de database SQL_SAFI.sqlite die zich bevindt in het pad: Users/Parul/Desktop. Laten we de tabel Crops selecteren en de inhoud weergeven.

%sql select * from Crops

Weergave van de eerste rijen van de tabel Crops

 %sql select * from Crops where D_curr_crop = "maize" AND ID <= 3;

Het resultaat kan ook worden omgezet naar een pandas-dataframe, als volgt:

result = %sql select * from Crops where D_curr_crop = "maize" AND ID <= 3;
dataframe = result.DataFrame()

dataframe.info()

2. De jupyterlab-sql-extensie

Voordat we verdergaan, eerst kort iets over Jupyter-extensies in het algemeen.

JupyterLab-extensies

Extensies zijn een van de nuttigste functies van Jupyter Lab en kunnen de Jupyter Lab-ervaring verbeteren. Jupyter Lab is in wezen zelf een uitbreidbare omgeving, met de notebook, terminal en editor allemaal als extensie aanwezig.

"JupyterLab-extensies zijn npm-packages (het standaardpakketformaat in Javascript-ontwikkeling)"(Bron). Extensies bieden een manier om de JupyterLab-ervaring aan te passen en uit te breiden met nuttige opties zoals nieuwe thema's, viewers, sneltoetsen en geavanceerde instellingen, om er een paar te noemen. Er zijn veel community-ontwikkelde extensies op GitHub. Je kunt zoeken op het GitHub-topic jupyterlab-extension om extensies te vinden.

Om JupyterLab-extensies te installeren, moet Node.js geïnstalleerd zijn. Dat kan via hun website of als volgt.

conda install -c conda-forge nodejs
or
brew install node #for MacOS users

De jupyterlab-sql-extensie is zo'n handige extensie waarmee je een SQL-gebruikersinterface toevoegt aan Jupyter Lab. Dit heeft twee belangrijke voordelen:

• SQL-tabellen kun je verkennen met simpelweg wijzen en klikken.
• Tabellen kun je ook bewerken en uitlezen met eigen queries.

Je leest er meer over in de officiële Github-repository.

Installatie

Om jupyterlab-sql te installeren, voer je de volgende commando's in deze volgorde uit:

pip install jupyterlab_sql
jupyter serverextension enable jupyterlab_sql --py --sys-prefix
jupyter lab build

Start daarna alle draaiende Jupyter-servers opnieuw.

Let er ook op dat jupyterlab-sql alleen werkt met Python 3.5 en hoger.

Aan de slag

Na het herstarten van Jupyter Lab verschijnt er een nieuw SQL-pictogram in de launcher

Dit betekent dat alles correct is geïnstalleerd en je klaar bent om je instance met een database te verbinden.

Verbinding

Om verbinding te maken met de database heb je een geldige database-URL nodig. jupyterlab-sql is uitgebreid getest met SQLite-, PostgreSQL- en MySQL-databases (Bron). Volg de SQLAlchemy-gids over URLs. De URL moet een database-URL zijn, bijvoorbeeld:

postgres://localhost:5432/postgres
postgres://username:password@localhost:5432/postgres
mysql://localhost/employees
sqlite://
sqlite:///myfile.db

Laten we verbinden met de bestaande SQL_SAFI-database die we eerder gebruikten. Klik op het SQL-pictogram in de launcher en typ de database-URL. Druk op enter om te verbinden.

Zodra de database is verbonden, kun je al je tabellen in de database bekijken.

Vervolgens kunnen we ook eigen SQL-queries schrijven om de gewenste data uit de tabellen op te halen.

Conclusie

SQL en JupyterLab samen tillen data-analyse naar een hoger niveau. De jupyter-sql-interface maakt het heel eenvoudig om de SQL Server met het Jupyter-ecosysteem te verbinden en de data er direct naartoe te halen, zonder de Jupyter-interface te verlaten.

Wil je meer leren over SQL? Volg de cursus Data-Driven Decision Making in SQL van DataCamp.

Bekijk ook onze tutorial Remote Python and R in SQL.