Interfaccia SQL in JupyterLab

Interfaccia SQL per JupyterLab

Nella maggior parte dei casi, i dati con cui lavoriamo sono archiviati in file chiamati database, e un compito essenziale di un Data Scientist è saper accedere ai dati dai database e poi analizzarli. Perciò è un’ottima idea avere un’interfaccia fluida tra database SQL e Jupyter Notebook/Lab, così da rendere l’accesso e la manipolazione dei dati più semplici ed efficienti. Questa interfaccia può essere ottenuta in due modi:

1. Usare l’estensione IPython SQL Magic

I comandi magic sono un insieme di funzioni pratiche in Jupyter Notebook pensate per risolvere alcuni problemi comuni nell’analisi dati standard. Puoi vedere tutte le magic disponibili con %lsmagic.

L’estensione IPython SQL magic rende possibile scrivere query SQL direttamente nelle celle di codice e leggere i risultati in pandas DataFrame (Fonte). Funziona sia con i notebook tradizionali sia con i moderni Jupyter Lab.

Installare il modulo SQL nel notebook

 !pip install ipython-sql
Offerto da

Caricare il modulo SQL

 %load_ext sql
Offerto da

Il comando magic sopra carica l’estensione ipython-sql. Possiamo connetterci a qualsiasi database supportato da SQLAlchemy. Qui ci connetteremo a un database SQLite. Inserisci il seguente comando nella cella di codice:

%sql sqlite://
Offerto da

Se ottieni l’output ‘Connected: @None', significa che la connessione è stata stabilita.

• Creare un database

Infine, creiamo una tabella demo chiamata EMPLOYEES per mostrare la funzionalità.

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

Ora possiamo eseguire query sul nostro database.

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

La query sopra restituisce la seguente tabella.

• Lavorare con un database esistente

Possiamo anche connetterci a un database esistente usando la magic. Per questo tutorial useremo il database SQL_SAFI (Studying African Farmer-led Irrigation (SAFI) database). "Il Progetto SAFI è un progetto di ricerca che studia le pratiche agricole e di irrigazione utilizzate dagli agricoltori in Tanzania e Mozambico. Questo dataset è composto da dati di survey relativi a nuclei familiari e agricoltura" (Fonte). Puoi scaricarlo da qui.

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

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

L’istruzione sopra ha aperto il database chiamato SQL_SAFI.sqlite che si trova nel percorso: Users/Parul/Desktop. Selezioniamo la tabella Crops e visualizziamone il contenuto.

%sql select * from Crops
Offerto da

Visualizzazione delle prime righe della tabella Crops

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

Il risultato può anche essere convertito in un pandas dataframe, come segue:

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

dataframe.info()Offerto da

2. L’estensione jupyterlab-sql

Prima di proseguire, parliamo brevemente delle estensioni Jupyter in generale.

Estensioni JupyterLab

Le estensioni sono una delle funzionalità più utili di Jupyter Lab e possono migliorare l’esperienza d’uso. Jupyter Lab è essenzialmente un ambiente estensibile: notebook, terminale ed editor sono tutti presenti come estensioni.

"Le estensioni JupyterLab sono pacchetti npm (il formato standard dei pacchetti nello sviluppo Javascript)" (Fonte). Le estensioni offrono un modo per personalizzare e potenziare l’esperienza JupyterLab fornendo diverse opzioni utili come nuovi temi, viewer, scorciatoie da tastiera, opzioni avanzate delle impostazioni, e altro ancora. Su GitHub ci sono molte estensioni sviluppate dalla comunità. Puoi cercare l’argomento GitHub jupyterlab-extension per trovarle.

Per installare le estensioni JupyterLab, devi avere installato Node.js, che può essere installato dal loro sito o come segue.

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

L’estensione jupyterlab-sql è una di queste utili estensioni che ti consente di aggiungere un’interfaccia utente SQL a Jupyter Lab. Questo ha due vantaggi principali:

• Le tabelle SQL possono essere esplorate con un semplice punta e clicca.
• Le tabelle possono anche essere modificate e lette con query personalizzate.

Puoi saperne di più sul repository Github ufficiale.

Installazione

Per installare jupyterlab-sql, esegui i seguenti comandi nell’ordine indicato:

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

Successivamente, dovrai riavviare eventuali server Jupyter in esecuzione.

È inoltre importante notare che jupyterlab-sql funziona solo con Python 3.5 e versioni successive.

Per iniziare

Dopo aver riavviato Jupyter Lab, nel launcher appare una nuova icona SQL

Questo significa che è tutto installato correttamente e sei pronto a connettere la tua istanza a un database.

Connessione

Per connetterti al database, ti serve un URL di database valido. jupyterlab-sql è stato testato ampiamente con database SQLite, PostgreSQL e MySQL (Fonte). Segui la guida di SQLAlchemy sugli URL. L’URL deve essere un URL di database, per esempio:

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

Connettiamoci al database SQL_SAFI esistente che abbiamo usato prima. Clicca sull’icona SQL nel launcher e digita l’URL del database. Premi invio per connetterti.

Non appena il database viene connesso, puoi visualizzare tutte le tue tabelle nel database.

Successivamente, possiamo anche scrivere query SQL personalizzate per ottenere i dati desiderati dalle tabelle.

Conclusione

Usare SQL e JupyterLab insieme porta l’analisi dei dati al livello successivo. L’interfaccia jupyter-sql rende molto semplice connettere SQL Server all’ecosistema Jupyter ed estrarre i dati direttamente al suo interno, senza dover lasciare l’interfaccia di Jupyter.

Se vuoi saperne di più su SQL, segui il corso Data-Driven Decision Making in SQL di DataCamp.

Dai un’occhiata al nostro tutorial Remote Python and R in SQL.