Interface SQL dans JupyterLab

Interface SQL pour JupyterLab

La plupart du temps, les données avec lesquelles nous travaillons sont stockées dans des bases de données. Une mission essentielle du data scientist consiste à y accéder puis à les analyser. Disposer d’une interface fluide entre les bases SQL et Jupyter Notebook/Lab facilite grandement l’accès et la manipulation des données, tout en gagnant en efficacité. Cette interface peut être mise en place de deux façons :

1. Utiliser l’extension IPython SQL Magic

Les commandes magic sont un ensemble de fonctions pratiques dans Jupyter Notebooks, conçues pour résoudre des problèmes courants en analyse de données. Vous pouvez lister toutes les magics disponibles avec %lsmagic.

L’extension IPython SQL magic permet d’écrire des requêtes SQL directement dans des cellules de code et de charger instantanément les résultats dans des DataFrames pandas (Source). Cela fonctionne aussi bien avec les notebooks classiques qu’avec JupyterLab.

Installer le module SQL dans le notebook

 !pip install ipython-sql

Charger le module SQL

 %load_ext sql

La commande magic ci-dessus charge l’extension ipython-sql. Nous pouvons nous connecter à toute base prise en charge par SQLAlchemy. Ici, nous allons nous connecter à une base SQLite. Saisissez la commande suivante dans une cellule :

%sql sqlite://

Si vous voyez ‘Connected: @None', la connexion est bien établie.

• Créer une base de démonstration

Créons une table de test nommée EMPLOYEE pour illustrer le fonctionnement.

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

Nous pouvons maintenant exécuter des requêtes sur notre base.

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

La requête ci-dessus renvoie le tableau suivant.

• Travailler avec une base existante

Nous pouvons également nous connecter à une base existante avec la fonction magic. Pour ce tutoriel, nous allons utiliser la base SQL_SAFI (Studying African Farmer-led Irrigation, base SAFI). « Le projet SAFI étudie les méthodes d’irrigation et de culture employées par des agriculteurs en Tanzanie et au Mozambique. Ce jeu de données rassemble des enquêtes relatives aux ménages et à l’agriculture » (Source). Vous pouvez la télécharger ici.

 #Spécifier le chemin de la base
 %sql sqlite:////Users/Parul/Desktop/SQL_SAFI.sqlite
 -----------------------------------------------------------

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

Cette instruction ouvre la base nommée SQL_SAFI.sqlite située dans le chemin : Users/Parul/Desktop. Sélectionnons la table Crops et affichons son contenu.

%sql select * from Crops

Affichage des premières lignes de la table Crops

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

Le résultat peut aussi être converti en DataFrame pandas, comme suit :

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

dataframe.info()

2. L’extension jupyterlab-sql

Avant d’aller plus loin, parlons brièvement des extensions Jupyter en général.

Extensions JupyterLab

Les extensions comptent parmi les fonctionnalités les plus utiles de JupyterLab et améliorent nettement l’expérience utilisateur. JupyterLab lui-même est un environnement extensible : notebook, terminal et éditeur sont déjà fournis sous forme d’extensions.

« Les extensions JupyterLab sont des paquets npm (le format standard des paquets en développement JavaScript) » (Source). Elles permettent de personnaliser et d’enrichir JupyterLab avec, par exemple, de nouveaux thèmes, visionneuses, raccourcis clavier ou options avancées. De nombreuses extensions communautaires sont disponibles sur GitHub : cherchez le sujet jupyterlab-extension.

Pour installer des extensions JupyterLab, vous devez avoir Node.js installé, soit depuis leur site, soit comme suit.

conda install -c conda-forge nodejs
or
brew install node #pour les utilisateurs macOS

L’extension jupyterlab-sql ajoute une interface SQL à JupyterLab. Deux avantages majeurs :

• Explorer les tables SQL en quelques clics.
• Modifier et lire les tables via des requêtes personnalisées.

Plus d’informations sur le référentiel GitHub officiel.

Installation

Pour installer jupyterlab-sql, exécutez ces commandes dans l’ordre :

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

Vous devrez ensuite redémarrer les serveurs Jupyter en cours d’exécution.

Notez également que jupyterlab-sql fonctionne uniquement avec Python 3.5 et versions ultérieures.

Premiers pas

Après le redémarrage de JupyterLab, une nouvelle icône SQL apparaît dans le lanceur.

Cela signifie que tout est correctement installé et que vous pouvez connecter votre instance à une base de données.

Connexion

Pour vous connecter à une base, vous devez disposer d’une URL valide. jupyterlab-sql a été largement testé avec les bases SQLite, PostgreSQL et MySQL (Source). Reportez-vous au guide SQLAlchemy sur les URLs. L’URL doit être une URL de base de données, par exemple :

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

Connectons-nous à la base SQL_SAFI utilisée précédemment. Cliquez sur l’icône SQL dans le lanceur et saisissez l’URL de la base. Appuyez sur enter pour vous connecter.

Une fois la connexion établie, vous pouvez afficher toutes les tables de la base.

Vous pouvez ensuite écrire des requêtes SQL personnalisées pour extraire les données souhaitées.

Conclusion

Utiliser SQL avec JupyterLab fait passer l’analyse de données au niveau supérieur. L’interface jupyter-sql facilite la connexion d’un serveur SQL à l’écosystème Jupyter et l’extraction directe des données, sans quitter l’interface.

Pour aller plus loin en SQL, suivez le cours Data-Driven Decision Making in SQL de DataCamp.

Consultez aussi notre tutoriel Remote Python and R in SQL.