SQL Magic in Notebooks in the IBM Data Science Experience

New Notebook in IBM Data Science Experience

At the recent IDUG DB2 Tech Conference in Brussels I gave a talk on using Jupyter Notebooks with IBM DB2 or dashDB. For the presentation I used a local installation of the notebooks and DB2 (never trust Internet connectivity). Part of the talk was about using SQL Magic in a notebook as simple interface to the database, e.g., for testing and prototyping.
After the conference I received a question about whether it is possible to use the SQL Magic with Jupyter Notebooks in the IBM Data Science Experience. The answer is yes and here is how.



I started by creating a new, blank notebook with a Python kernel. To add the library and the necessary DB2 / dashDB driver to the computing environment, I executed the following in one of the first cells:


!pip install --user ipython-sql 
!pip install --user ibm_db
!pip install --user ibm_db_sa

The command installs the module for SQL Magic, ipython-sql, and the DB2 drivers needed for SQLAlchemy, SQL framework underneath. The "--user" option is needed because we don't have any administrative privileges. Next I imported the DB2 driver and SQLAlchemy to my environment and loaded the SQL Magic:

import ibm_db
import ibm_db_sa
import sqlalchemy

# loads the SQL magic extensions
%load_ext sql

The "%load_ext" is needed to make the newly installed extension for "SQL Magic" known to the computing environment. The "import" for the modules is not necessarily needed, they are used by the extension. Thereafter, the environment should be ready to create a database connection and the execute SQL statements against DB2 or dashDB. The following screenshot shows the commands in the notebook which I created. I used my dashDB created via IBM Bluemix to connect to.

Using SQL Magic with DB2 and dashDB

If you should run into an error like "can't load plugin" for DB2, then restart the Python kernel and load the libraries and the SQL Magic extension again.

You can check the installed Python packages using the following command:
!pip list --isolated