03. Accessing Databases with SQL Magic.ipynb

Kernel: Python 3

Accessing Databases with SQL Magic

Objectives

After completing this lab you will be able to:

• Perform simplified database access using SQL "magic"

To communicate with SQL Databases from within a JupyterLab notebook, we can use the SQL "magic" provided by the ipython-sql extension. "Magic" is JupyterLab's term for special commands that start with "%". Below, we'll use the load___ext magic to load the ipython-sql extension. In the lab environemnt provided in the course the ipython-sql extension is already installed and so is the ibm_db_sa driver.

!pip install sqlalchemy==1.4.17
!pip install ibm_db_sa

!pip install ipython-sql

Collecting sqlalchemy==1.4.17
  Downloading SQLAlchemy-1.4.17-cp38-cp38-win_amd64.whl (1.5 MB)
Collecting greenlet!=0.4.17; python_version >= "3"
  Downloading greenlet-1.1.0-cp38-cp38-win_amd64.whl (96 kB)
Installing collected packages: greenlet, sqlalchemy
  Attempting uninstall: greenlet
    Found existing installation: greenlet 0.4.17
    Uninstalling greenlet-0.4.17:
      Successfully uninstalled greenlet-0.4.17
  Attempting uninstall: sqlalchemy
    Found existing installation: SQLAlchemy 1.3.9
    Uninstalling SQLAlchemy-1.3.9:
      Successfully uninstalled SQLAlchemy-1.3.9
Successfully installed greenlet-1.1.0 sqlalchemy-1.4.17
Requirement already satisfied: ibm_db_sa in c:\users\dabarnes\anaconda3\lib\site-packages (0.3.6)
Requirement already satisfied: sqlalchemy>=0.7.3 in c:\users\dabarnes\anaconda3\lib\site-packages (from ibm_db_sa) (1.4.17)
Requirement already satisfied: ibm-db>=2.0.0 in c:\users\dabarnes\anaconda3\lib\site-packages (from ibm_db_sa) (3.0.4)
Requirement already satisfied: greenlet!=0.4.17; python_version >= "3" in c:\users\dabarnes\anaconda3\lib\site-packages (from sqlalchemy>=0.7.3->ibm_db_sa) (1.1.0)
Requirement already satisfied: ipython-sql in c:\users\dabarnes\anaconda3\lib\site-packages (0.4.0)
Requirement already satisfied: sqlparse in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (0.4.1)
Requirement already satisfied: sqlalchemy>=0.6.7 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (1.4.17)
Requirement already satisfied: ipython>=1.0 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (7.19.0)
Requirement already satisfied: ipython-genutils>=0.1.0 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: six in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (1.15.0)
Requirement already satisfied: prettytable<1 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython-sql) (0.7.2)
Requirement already satisfied: greenlet!=0.4.17; python_version >= "3" in c:\users\dabarnes\anaconda3\lib\site-packages (from sqlalchemy>=0.6.7->ipython-sql) (1.1.0)
Requirement already satisfied: jedi>=0.10 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.17.1)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (3.0.8)
Requirement already satisfied: setuptools>=18.5 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (50.3.1.post20201107)
Requirement already satisfied: traitlets>=4.2 in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (5.0.5)
Requirement already satisfied: pickleshare in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.7.5)
Requirement already satisfied: colorama; sys_platform == "win32" in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.4.4)
Requirement already satisfied: backcall in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.2.0)
Requirement already satisfied: pygments in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (2.7.2)
Requirement already satisfied: decorator in c:\users\dabarnes\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (4.4.2)
Requirement already satisfied: parso<0.8.0,>=0.7.0 in c:\users\dabarnes\anaconda3\lib\site-packages (from jedi>=0.10->ipython>=1.0->ipython-sql) (0.7.0)
Requirement already satisfied: wcwidth in c:\users\dabarnes\anaconda3\lib\site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Now we have access to SQL magic. With our first SQL magic command, we'll connect to a Db2 database. However, in order to do that, you'll first need to retrieve or create your credentials to access your Db2 database.

This image shows the location of your connection string if you're using Db2 on IBM Cloud. If you're using another host the format is: username:password@hostname:port/database-name

# Enter your Db2 credentials in the connection string below
# Recall you created Service Credentials in Part III of the first lab of the course in Week 1
# i.e. from the uri field in the Service Credentials copy everything after db2:// (but remove the double quote at the end)
# for example, if your credentials are as in the screenshot above, you would write:
# %sql ibm_db_sa://my-username:[email protected]:50000/BLUDB
# Note the ibm_db_sa:// prefix instead of db2://
# This is because JupyterLab's ipython-sql extension uses sqlalchemy (a python SQL toolkit)
# which in turn uses IBM's sqlalchemy dialect: ibm_db_sa
%sql ibm_db_sa://kfm42587:[e-mail]:50000/BLUDB

DB2/LINUXX8664

For convenience, we can use %%sql (two %'s instead of one) at the top of a cell to indicate we want the entire cell to be treated as SQL. Let's use this to create a table and fill it with some test data for experimenting.

%%sql

CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (
	country VARCHAR(50),
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	test_score INT
);
INSERT INTO INTERNATIONAL_STUDENT_TEST_SCORES (country, first_name, last_name, test_score)
VALUES
('United States', 'Marshall', 'Bernadot', 54),
('Ghana', 'Celinda', 'Malkin', 51),
('Ukraine', 'Guillermo', 'Furze', 53),
('Greece', 'Aharon', 'Tunnow', 48),
('Russia', 'Bail', 'Goodwin', 46),
('Poland', 'Cole', 'Winteringham', 49),
('Sweden', 'Emlyn', 'Erricker', 55),
('Russia', 'Cathee', 'Sivewright', 49),
('China', 'Barny', 'Ingerson', 57),
('Uganda', 'Sharla', 'Papaccio', 55),
('China', 'Stella', 'Youens', 51),
('Poland', 'Julio', 'Buesden', 48),
('United States', 'Tiffie', 'Cosely', 58),
('Poland', 'Auroora', 'Stiffell', 45),
('China', 'Clarita', 'Huet', 52),
('Poland', 'Shannon', 'Goulden', 45),
('Philippines', 'Emylee', 'Privost', 50),
('France', 'Madelina', 'Burk', 49),
('China', 'Saunderson', 'Root', 58),
('Indonesia', 'Bo', 'Waring', 55),
('China', 'Hollis', 'Domotor', 45),
('Russia', 'Robbie', 'Collip', 46),
('Philippines', 'Davon', 'Donisi', 46),
('China', 'Cristabel', 'Radeliffe', 48),
('China', 'Wallis', 'Bartleet', 58),
('Moldova', 'Arleen', 'Stailey', 38),
('Ireland', 'Mendel', 'Grumble', 58),
('China', 'Sallyann', 'Exley', 51),
('Mexico', 'Kain', 'Swaite', 46),
('Indonesia', 'Alonso', 'Bulteel', 45),
('Armenia', 'Anatol', 'Tankus', 51),
('Indonesia', 'Coralyn', 'Dawkins', 48),
('China', 'Deanne', 'Edwinson', 45),
('China', 'Georgiana', 'Epple', 51),
('Portugal', 'Bartlet', 'Breese', 56),
('Azerbaijan', 'Idalina', 'Lukash', 50),
('France', 'Livvie', 'Flory', 54),
('Malaysia', 'Nonie', 'Borit', 48),
('Indonesia', 'Clio', 'Mugg', 47),
('Brazil', 'Westley', 'Measor', 48),
('Philippines', 'Katrinka', 'Sibbert', 51),
('Poland', 'Valentia', 'Mounch', 50),
('Norway', 'Sheilah', 'Hedditch', 53),
('Papua New Guinea', 'Itch', 'Jubb', 50),
('Latvia', 'Stesha', 'Garnson', 53),
('Canada', 'Cristionna', 'Wadmore', 46),
('China', 'Lianna', 'Gatward', 43),
('Guatemala', 'Tanney', 'Vials', 48),
('France', 'Alma', 'Zavittieri', 44),
('China', 'Alvira', 'Tamas', 50),
('United States', 'Shanon', 'Peres', 45),
('Sweden', 'Maisey', 'Lynas', 53),
('Indonesia', 'Kip', 'Hothersall', 46),
('China', 'Cash', 'Landis', 48),
('Panama', 'Kennith', 'Digance', 45),
('China', 'Ulberto', 'Riggeard', 48),
('Switzerland', 'Judy', 'Gilligan', 49),
('Philippines', 'Tod', 'Trevaskus', 52),
('Brazil', 'Herold', 'Heggs', 44),
('Latvia', 'Verney', 'Note', 50),
('Poland', 'Temp', 'Ribey', 50),
('China', 'Conroy', 'Egdal', 48),
('Japan', 'Gabie', 'Alessandone', 47),
('Ukraine', 'Devlen', 'Chaperlin', 54),
('France', 'Babbette', 'Turner', 51),
('Czech Republic', 'Virgil', 'Scotney', 52),
('Tajikistan', 'Zorina', 'Bedow', 49),
('China', 'Aidan', 'Rudeyeard', 50),
('Ireland', 'Saunder', 'MacLice', 48),
('France', 'Waly', 'Brunstan', 53),
('China', 'Gisele', 'Enns', 52),
('Peru', 'Mina', 'Winchester', 48),
('Japan', 'Torie', 'MacShirrie', 50),
('Russia', 'Benjamen', 'Kenford', 51),
('China', 'Etan', 'Burn', 53),
('Russia', 'Merralee', 'Chaperlin', 38),
('Indonesia', 'Lanny', 'Malam', 49),
('Canada', 'Wilhelm', 'Deeprose', 54),
('Czech Republic', 'Lari', 'Hillhouse', 48),
('China', 'Ossie', 'Woodley', 52),
('Macedonia', 'April', 'Tyer', 50),
('Vietnam', 'Madelon', 'Dansey', 53),
('Ukraine', 'Korella', 'McNamee', 52),
('Jamaica', 'Linnea', 'Cannam', 43),
('China', 'Mart', 'Coling', 52),
('Indonesia', 'Marna', 'Causbey', 47),
('China', 'Berni', 'Daintier', 55),
('Poland', 'Cynthia', 'Hassell', 49),
('Canada', 'Carma', 'Schule', 49),
('Indonesia', 'Malia', 'Blight', 48),
('China', 'Paulo', 'Seivertsen', 47),
('Niger', 'Kaylee', 'Hearley', 54),
('Japan', 'Maure', 'Jandak', 46),
('Argentina', 'Foss', 'Feavers', 45),
('Venezuela', 'Ron', 'Leggitt', 60),
('Russia', 'Flint', 'Gokes', 40),
('China', 'Linet', 'Conelly', 52),
('Philippines', 'Nikolas', 'Birtwell', 57),
('Australia', 'Eduard', 'Leipelt', 53)

 * ibm_db_sa://kfm42587:***@dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net:50000/BLUDB
Done.
99 rows affected.

[]

Using Python Variables in your SQL Statements

You can use python variables in your SQL statements by adding a ":" prefix to your python variable names.

For example, if I have a python variable country with a value of "Canada", I can use this variable in a SQL query to find all the rows of students from Canada.

country = "Canada"
%sql select * from INTERNATIONAL_STUDENT_TEST_SCORES where country = :country

 * ibm_db_sa://kfm42587:***@dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net:50000/BLUDB
Done.

Assigning the Results of Queries to Python Variables

You can use the normal python assignment syntax to assign the results of your queries to python variables.

For example, I have a SQL query to retrieve the distribution of test scores (i.e. how many students got each score). I can assign the result of this query to the variable test_score_distribution using the = operator.

#for some reason in Jupyter notebooks, the stuff here in quotation marks MUST be in double quotes, not single quotes
#and it removes all case sensitivity and doesn't like spaces either, so it has to be 1 word, no spaces

test_score_distribution = %sql SELECT test_score, count(*) as "Frequency" from INTERNATIONAL_STUDENT_TEST_SCORES GROUP BY test_score;
test_score_distribution

 * ibm_db_sa://kfm42587:***@dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net:50000/BLUDB
Done.

Converting Query Results to DataFrames

You can easily convert a SQL query result to a pandas dataframe using the DataFrame() method. Dataframe objects are much more versatile than SQL query result objects. For example, we can easily graph our test score distribution after converting to a dataframe.

dataframe = test_score_distribution.DataFrame()

%matplotlib inline
# uncomment the following line if you get an module error saying seaborn not found
# !pip install seaborn
import seaborn

plot = seaborn.barplot(x='test_score',y='frequency', data=dataframe)

Now you know how to work with Db2 from within JupyterLab notebooks using SQL "magic"!

%%sql 

-- Feel free to experiment with the data set provided in this notebook for practice:
SELECT country, first_name, last_name, test_score FROM INTERNATIONAL_STUDENT_TEST_SCORES;    

 * ibm_db_sa://kfm42587:***@dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net:50000/BLUDB
Done.