notebook_examples/sql_magic-commands-with-autonomous_database.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "@notebook{sql_magic-commands-with-autonomous_database.ipynb,\n",
    "    title: Introduction to SQL Magic,\n",
    "    summary: Use SQL Magic commands to work with a database within a Jupyter notebook. This notebook shows how to to use both line and cell magics.,\n",
    "    developed_on: generalml_p38_cpu_v1,\n",
    "    keywords: sql magic, autonomous database,\n",
    "    license: Universal Permissive License v 1.0\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Upgrade Oracle ADS to pick up latest features and maintain compatibility with Oracle Cloud Infrastructure.\n",
    "\n",
    "!pip install -U oracle-ads"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oracle Data Science service sample notebook.\n",
    "\n",
    "Copyright (c) 2020, 2022 Oracle, Inc. All rights reserved. Licensed under the [Universal Permissive License v 1.0](https://oss.oracle.com/licenses/upl).\n",
    "\n",
    "---\n",
    "\n",
    "# <font color=\"red\">Introduction to SQL Magic</font>\n",
    "<p style=\"margin-left:10%; margin-right:10%;\">by the <font color=\"teal\">Oracle Cloud Infrastructure Data Science Service.</font></p>\n",
    "\n",
    "---\n",
    "\n",
    "# Overview:\n",
    "\n",
    "This notebook demonstrates how to use SQL Magic to work with a database.  Magic commands are a set of functions which are not valid Python code but can be run and executed in Jupyter Notebooks.  There are two types of magic commands, line magics and cell magics.  Line magics start with `%` and operate on a single line of input. Cell magics start with `%%`, and they work on multiple lines in a call.  \n",
    "\n",
    "IPython SQL magic extension allows you to directly write SQL queries in Jupyter notebook cells.\n",
    "\n",
    "Compatible conda pack: [General Machine Learning](https://docs.oracle.com/en-us/iaas/data-science/using/conda-gml-fam.htm) for CPU on Python 3.8 (version 1.0)\n",
    "\n",
    "## Contents:\n",
    "\n",
    " - <a href='#setup'>Setting Up `ipython-sql`</a>\n",
    " - <a href='#sql_DML'>Data Manipulation Language Commands</a>\n",
    " - <a href='#sql_DQL'>Data Query Language Commands</a>\n",
    " - <a href='#sql_var'>Variable Bindings</a>\n",
    " - <a href='#sql_viz'>Data Visualizations</a>\n",
    " - <a href='#reference'>References</a>\n",
    "\n",
    "---\n",
    "\n",
    "\n",
    "Datasets are provided as a convenience.  Datasets are considered third-party content and are not considered materials \n",
    "under your agreement with Oracle.\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython import get_ipython"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='setup'></a>\n",
    "# Setting Up `ipython-sql`\n",
    "\n",
    "`ipython-sql` uses a number of ipython magic commands to interact directly with the database. The following sections will cover the following magic commands\n",
    "* `%config SqlMagic`\n",
    "* `load_ext sql`\n",
    "* `%sql`\n",
    "* `%%sql`\n",
    "\n",
    "In the following cell, the `ipython-sql` package is loaded with the magic command `%load_ext sql`. Note, it is not loaded with an `import` statement. `ipython-sql` supports a variety of databases. The command `%sql sqlite://` makes a connection to an in-memory SQLite database. This database is now bound to the notebook and future `%sql` command will be performed on this database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can configure the database using the `%config SqlMagic` magic command. This prints the current configuration information, descriptions of the options, current values, and what values can be set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `%config SQLMagic` command also allows options to be set. These would be in the form of `%config SQLMagic.<option>=<value>` where `<option>` is the name of the option that is to be set and `<value>` is the value that is to be set. The `%config SQLMagic` command lists the options and valid values.\n",
    "\n",
    "The command `%config SQLMagic.<option>` will return the current value of the option."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autocommit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autocommit=False\n",
    "%config SqlMagic.autocommit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='sql_DML'></a>\n",
    "# Data Manipulation Language Commands\n",
    "\n",
    "A data manipulation language (DML) command can be issued with the `%%sql` command once a database is bound to the `ipython-sql` module. The DML statements in the next cell create a table called `writer` and populates it with three authors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS author;\n",
    "CREATE TABLE author (given_name, family_name, year_of_death);\n",
    "INSERT INTO author VALUES ('William', 'Shakespeare', 1616);\n",
    "INSERT INTO author VALUES ('Bertold', 'Brecht', 1956);\n",
    "INSERT INTO author VALUES ('Virginia', 'Woolf', 1941);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `--persists <variable>` can be used to copy a dataset into a new table. The name of the table will be the same as the same of the variable. In the following cells, a Pandas DataFrame will be created. Then several `ipython-sql` commands will be issued. The first one will drop the table animals, if it exists. If animals already exists it will create an error. Then the `--persists` command will be used to copy the DataFrame into the database as a new table. The final command will query all the records in the newly created animals table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "animals = pd.DataFrame(\n",
    "    {\n",
    "        \"num_legs\": [2, 4, 8, 0],\n",
    "        \"num_wings\": [2, 0, 0, 0],\n",
    "        \"num_specimen_seen\": [10, 2, 1, 8],\n",
    "    },\n",
    "    index=[\"falcon\", \"dog\", \"spider\", \"fish\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql DROP TABLE IF EXISTS 'animals'\n",
    "%sql --persist animals\n",
    "%sql SELECT * FROM animals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='sql_DQL'></a>\n",
    "# Data Query Language Commands\n",
    "\n",
    "A data query language (DQL) command can be used to obtain records from the database. \n",
    "If your query is short, you can use oneline of code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM author WHERE year_of_death >=1950;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The previous cell printed the results of the query into the notebook. It is also possible to capture the results into a Python object. If the query can fit on a single line then the `<variable> = %sql <DQL>` command can be used. This will store the results in the specified variable. In the following cell, this approach is used to obtain authors that died before 1950."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas=False\n",
    "old_author = %sql SELECT * FROM author WHERE year_of_death < 1950;\n",
    "old_author"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For longer SQL commands use\n",
    "```\n",
    "%%sql <variable> << \n",
    "<DQL>\n",
    "``` \n",
    "The result is stored in the `<variable>` variable. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql author << \n",
    "SELECT given_name, family_name, year_of_death \n",
    "FROM author;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "author"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the preceding cell, `author` is an object of class `sql.run.ResultSet`. It can be converted to a Pandas DataFrame using the `DataFrame()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = author.DataFrame()\n",
    "type(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To have `ipython-sql` return record sets in a Pandas DataFrame, set the `autopandas` option to `True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas=True\n",
    "author = %sql SELECT given_name, family_name, year_of_death FROM author\n",
    "type(author)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='sql_var'></a>\n",
    "# Variable Bindings\n",
    "\n",
    "Python variables can be bound to the SQL commands with the `:<variable>`, `'{variable}'` or `$variable` syntax. In the next cell, the variable `name` is set to William. The command is issued to return any results where the `first_name` is equal to the value of `name`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_name = \"William\"\n",
    "last_name = \"Shakespeare\"\n",
    "death_century = 1600"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "SELECT * \n",
    "FROM author \n",
    "WHERE \n",
    "    given_name LIKE :first_name \n",
    "    AND family_name LIKE '{last_name}'\n",
    "    AND year_of_death >= CAST('$death_century' AS INT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='sql_viz'></a>\n",
    "# Data Visualization\n",
    "\n",
    "Record sets that are of the class `sql.run.ResultSet` have the methods `.plot()`, `.pie()`, and `.bar()`. These are convient for making quick plots."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_author.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_author.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "old_author.pie()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"reference\"></a>\n",
    "# References\n",
    "\n",
    "- [ADS Library Documentation](https://accelerated-data-science.readthedocs.io/en/latest/index.html)\n",
    "- [Data Science YouTube Videos](https://www.youtube.com/playlist?list=PLKCk3OyNwIzv6CWMhvqSB_8MLJIZdO80L)\n",
    "- [ipython-sql](https://pypi.org/project/ipython-sql/)\n",
    "- [OCI Data Science Documentation](https://docs.cloud.oracle.com/en-us/iaas/data-science/using/data-science.htm)\n",
    "- [Oracle Data & AI Blog](https://blogs.oracle.com/datascience/)\n",
    "- [SQLite Tutorial](https://www.sqlitetutorial.net/)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:generalml_p37_gpu_v1]",
   "language": "python",
   "name": "conda-env-generalml_p37_gpu_v1-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}