notebooks/sql_query_in_jupyter_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying a SQL database in a Jupyter notebook\n",
    "\n",
    "Use [ipython-sql](https://github.com/catherinedevlin/ipython-sql) which provides `%%sql` magic for IPython (and therefore Jupyter notebooks)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup\n",
    "\n",
    "import os\n",
    "\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "NOTEBOOK_DIR = os.getcwd()\n",
    "DATA_DIR = os.path.abspath(os.path.join(NOTEBOOK_DIR, os.pardir, 'data'))\n",
    "DATA_DIR_TMP = os.path.join(DATA_DIR, 'tmp')\n",
    "\n",
    "os.makedirs(DATA_DIR_TMP, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bootstrap code to create a database.\n",
    "\n",
    "DB_PATH = os.path.join(DATA_DIR_TMP, 'example.db')\n",
    "# Note that for SQLite databases with an absolute path, you need three\n",
    "# slashes after the `sqlite:`, so that's four slashes in total when\n",
    "# you count the leading `/` of the path.\n",
    "DB_URL = f\"sqlite:///{DB_PATH}\"\n",
    "\n",
    "engine = create_engine(DB_URL)\n",
    "\n",
    "df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})\n",
    "df.to_sql('users', con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to the database\n",
    "\n",
    "This uses [SQLAlchemy database URLs](http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls).\n",
    "\n",
    "Note that you can access Python variables by prefixing them with '$'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @/Users/ghing/workspace/python-data-cheatsheet/data/tmp/example.db'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $DB_URL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:////Users/ghing/workspace/python-data-cheatsheet/data/tmp/example.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>index</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>User 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>User 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>User 3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 'User 1'), (1, 'User 2'), (2, 'User 3')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM users;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save the query results to a Pandas DataFrame\n",
    "\n",
    "You can specify a variable name where the query results will be stored as part of the `%%sql` magic. Then you can conver the results object to a Pandas `DataFrame` using the `DataFrame()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:////Users/ghing/workspace/python-data-cheatsheet/data/tmp/example.db\n",
      "Done.\n",
      "Returning data to local variable users_results\n"
     ]
    }
   ],
   "source": [
    "%%sql users_results << \n",
    "SELECT * FROM users;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>User 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>User 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>User 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index    name\n",
       "0      0  User 1\n",
       "1      1  User 2\n",
       "2      2  User 3"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_results\\\n",
    "    .DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `pd.read_sql`\n",
    "\n",
    "Pandas also has a [`read_sql`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html) method for querying a SQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>User 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>User 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>User 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index    name\n",
       "0      0  User 1\n",
       "1      1  User 2\n",
       "2      2  User 3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query a SQL database using pd.read_sql\n",
    "\n",
    "# Pandas' database interactions use SQLAlchemy behind the scences, so it's\n",
    "# worth checking out their documentation, at least for database\n",
    "# connections:\n",
    "# https://docs.sqlalchemy.org/en/13/core/engines.html\n",
    "\n",
    "engine = create_engine(DB_URL)\n",
    "\n",
    "sql_query = \"\"\"\n",
    "SELECT * FROM users;\n",
    "\"\"\"\n",
    "\n",
    "results_df = pd.read_sql(\n",
    "    sql_query,\n",
    "    con=engine\n",
    ")\n",
    "\n",
    "results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other things to track\n",
    "\n",
    "I haven't used it, but [jupyterlab-sql](https://github.com/pbugnion/jupyterlab-sql) is a SQL user interface to JupyterLab."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}