Interfaz SQL en JupyterLab

Interfaz SQL para JupyterLab

La mayoría de las veces, los datos con los que trabajamos se almacenan en bases de datos, y una tarea esencial de cualquier científico de datos es poder acceder a esos datos para analizarlos. Por eso, contar con una interfaz fluida entre bases de datos SQL y Jupyter Notebook/Lab facilita y agiliza el acceso y la manipulación de datos. Esta integración puede lograrse de dos formas:

1. Usar la extensión IPython SQL Magic

Los comandos mágicos son un conjunto de funciones prácticas en Jupyter Notebooks diseñadas para resolver problemas comunes en el análisis de datos. Puedes ver todos los magics disponibles con %lsmagic.

La extensión IPython SQL magic permite escribir consultas SQL directamente en las celdas de código y leer los resultados en DataFrames de pandas (Fuente). Funciona tanto en los notebooks clásicos como en Jupyter Lab.

Instalar el módulo SQL en el notebook

 !pip install ipython-sql

Cargar el módulo SQL

 %load_ext sql

El comando mágico anterior carga la extensión ipython-sql. Podemos conectar con cualquier base de datos compatible con SQLAlchemy. Aquí nos conectaremos a una base de datos SQLite. Escribe el siguiente comando en una celda de código:

%sql sqlite://

Si obtienes ‘Connected: @None', significa que la conexión se ha establecido.

• Crear una base de datos

Por último, creamos una tabla de demostración llamada EMPLOYEES para mostrar su funcionamiento.

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

Ahora ya podemos ejecutar consultas sobre nuestra base de datos.

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

La consulta anterior devuelve la siguiente tabla.

• Trabajar con una base de datos existente

También podemos conectar a una base de datos existente usando el magic. En este tutorial, utilizaremos la base de datos SQL_SAFI (Studying African Farmer-led Irrigation (SAFI) database). "El Proyecto SAFI es un proyecto de investigación sobre métodos de cultivo y riego utilizados por agricultores en Tanzania y Mozambique. Este conjunto de datos está compuesto por encuestas relacionadas con hogares y agricultura" (Fuente). Puedes descargarla desde aquí.

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

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

La sentencia anterior abrió la base de datos SQL_SAFI.sqlite que reside en la ruta: Users/Parul/Desktop. Seleccionemos la tabla Crops y mostremos su contenido.

%sql select * from Crops

Mostrando las primeras filas de la tabla Crops

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

El resultado también puede convertirse en un DataFrame de pandas, así:

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

dataframe.info()

2. La extensión jupyterlab-sql

Antes de continuar, hablemos un poco de las extensiones de Jupyter en general.

Extensiones de JupyterLab

Las extensiones son una de las funciones más útiles de Jupyter Lab y pueden mejorar mucho la experiencia. De hecho, Jupyter Lab es en sí mismo un entorno extensible: el notebook, el terminal y el editor existen como extensiones.

"Las extensiones de JupyterLab son paquetes npm (el formato estándar de paquetes en el desarrollo con Javascript)" (Fuente). Permiten personalizar y ampliar JupyterLab con temas nuevos, visores, atajos de teclado, opciones avanzadas de configuración, etc. Hay muchas extensiones creadas por la comunidad en GitHub. Puedes buscar el tema de GitHub jupyterlab-extension para encontrarlas.

Para instalarlas necesitas tener Node.js instalado, ya sea desde su web o de la siguiente forma.

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

La jupyterlab-sql es una extensión muy útil que añade una interfaz de usuario SQL a Jupyter Lab. Tiene dos ventajas principales:

• Explorar tablas SQL con un simple apuntar y clic.
• Modificar y leer tablas con consultas personalizadas.

Puedes leer más en el repositorio de Github oficial.

Instalación

Para instalar jupyterlab-sql, ejecuta los siguientes comandos en este orden:

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

Después tendrás que reiniciar cualquier servidor de Jupyter que esté en ejecución.

Además, es importante tener en cuenta que jupyterlab-sql solo funciona con Python 3.5 o superior.

Primeros pasos

Tras reiniciar Jupyter Lab, aparecerá un icono nuevo de SQL en el lanzador.

Esto significa que todo se ha instalado correctamente y ya puedes conectar tu instancia a una base de datos.

Conexión

Para conectarte a la base de datos, necesitas una URL de base de datos válida. jupyterlab-sql se ha probado ampliamente con bases de datos SQLite, PostgreSQL y MySQL (Fuente). Sigue la guía de SQLAlchemy sobre URLs. La URL debe ser una URL de base de datos, por ejemplo:

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

Conectémonos a la base de datos existente SQL_SAFI que usamos antes. Haz clic en el icono de SQL en el lanzador y escribe la URL de la base de datos. Pulsa enter para conectar.

En cuanto la base de datos esté conectada, podrás ver todas las tablas disponibles.

A continuación, también podemos escribir consultas SQL personalizadas para obtener los datos que necesitamos.

Conclusión

Usar SQL y JupyterLab juntos lleva el análisis de datos al siguiente nivel. La interfaz jupyter-sql facilita muchísimo conectar el servidor SQL con el ecosistema Jupyter y extraer datos directamente, sin salir de la interfaz de Jupyter.

Si quieres aprender más SQL, haz el curso Data-Driven Decision Making in SQL de DataCamp.

Echa un vistazo a nuestro tutorial Remote Python and R in SQL.