rds/sagemaker-aurora_mysql/ipython-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "worse-track",
   "metadata": {},
   "source": [
    "### Load ipython-sql library\n",
    "\n",
    "Load **ipython-sql** library to access RDBMS via IPython"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "protective-relevance",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "logical-pavilion",
   "metadata": {},
   "source": [
    "### Configuration\n",
    "\n",
    "Check default configuration such as *autolimit*, *displaylimit*, *autocommit*, etc.\n",
    "\n",
    "There is no autolimit by default. However, *autolimit* (if set) limits the size of the result set (usually with a LIMIT clause in the SQL). *displaylimit* is similar, but the entire result set is still pulled into memory (for later analysis); only the screen display is truncated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "advance-intermediate",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SqlMagic options\n",
      "--------------\n",
      "SqlMagic.autocommit=<Bool>\n",
      "    Current: True\n",
      "    Set autocommit mode\n",
      "SqlMagic.autolimit=<Int>\n",
      "    Current: 0\n",
      "    Automatically limit the size of the returned result sets\n",
      "SqlMagic.autopandas=<Bool>\n",
      "    Current: False\n",
      "    Return Pandas DataFrames instead of regular result sets\n",
      "SqlMagic.column_local_vars=<Bool>\n",
      "    Current: False\n",
      "    Return data into local variables from column names\n",
      "SqlMagic.displaycon=<Bool>\n",
      "    Current: True\n",
      "    Show connection string after execute\n",
      "SqlMagic.displaylimit=<Int>\n",
      "    Current: None\n",
      "    Automatically limit the number of rows displayed (full result set is still\n",
      "    stored)\n",
      "SqlMagic.dsn_filename=<Unicode>\n",
      "    Current: 'odbc.ini'\n",
      "    Path to DSN file. When the first argument is of the form [section], a\n",
      "    sqlalchemy connection string is formed from the matching section in the DSN\n",
      "    file.\n",
      "SqlMagic.feedback=<Bool>\n",
      "    Current: True\n",
      "    Print number of rows affected by DML\n",
      "SqlMagic.short_errors=<Bool>\n",
      "    Current: True\n",
      "    Don't display the full traceback on SQL Programming Error\n",
      "SqlMagic.style=<Unicode>\n",
      "    Current: 'DEFAULT'\n",
      "    Set the table printing style to any of prettytable's defined styles\n",
      "    (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)\n"
     ]
    }
   ],
   "source": [
    "%config SqlMagic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "narrow-surge",
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autolimit=1000 # limit the size of result set\n",
    "#%config SqlMagic.autocommit=False # for engines that do not support autommit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "smooth-casting",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%config SqlMagic.autolimit"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "opposite-arthritis",
   "metadata": {},
   "source": [
    "### Preparation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "allied-satin",
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import json\n",
    "import urllib\n",
    "import os\n",
    "\n",
    "# Prettifies Python exception output to make it legible\n",
    "import pretty_errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "italian-union",
   "metadata": {},
   "outputs": [],
   "source": [
    "AWS_REGION = os.environ['AWS_REGION']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "subject-nomination",
   "metadata": {},
   "outputs": [],
   "source": [
    "cf_stack_name = \"SMAuroraMySQLStack\" # name of CloudFormation stack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "opening-backup",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_secret_name(stack_name):\n",
    "    cf_client = boto3.client('cloudformation', region_name=AWS_REGION)\n",
    "    response = cf_client.describe_stacks(StackName=cf_stack_name)\n",
    "    outputs = response[\"Stacks\"][0][\"Outputs\"]\n",
    "\n",
    "    secrets = [e for e in outputs if e['ExportName'].endswith('DBSecretName')][0]\n",
    "    secret_name = secrets['OutputValue']\n",
    "    return secret_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "committed-turkish",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_secret(secret_name):\n",
    "\n",
    "    # Create a Secrets Manager client\n",
    "    client = boto3.client('secretsmanager', region_name=AWS_REGION)\n",
    "    get_secret_value_response = client.get_secret_value(SecretId=secret_name)\n",
    "    secret = get_secret_value_response['SecretString']\n",
    "\n",
    "    return json.loads(secret)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regular-greensboro",
   "metadata": {},
   "source": [
    "### Connecting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "finished-badge",
   "metadata": {},
   "outputs": [],
   "source": [
    "secret_name = get_secret_name(cf_stack_name)\n",
    "secret = get_secret(secret_name)\n",
    "\n",
    "db_username = secret['username']\n",
    "db_password = urllib.parse.quote_plus(secret['password'])\n",
    "db_port = secret['port']\n",
    "db_host = secret['host']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "green-hanging",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_uri_str = f\"mysql+pymysql://{db_username}:{db_password}@{db_host}:{db_port}/?charset=utf8\"\n",
    "%sql $conn_uri_str"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "external-timber",
   "metadata": {},
   "source": [
    "### Runing queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "academic-kernel",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Database</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mysql</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>performance_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sys</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('information_schema',), ('mysql',), ('performance_schema',), ('sys',)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql show databases;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "recognized-herald",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>admin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mysql.sys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rdsadmin</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('admin',), ('mysql.sys',), ('rdsadmin',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select user\n",
    "from mysql.user;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "published-shoulder",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "1 rows affected.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Database</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>mysql</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>performance_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>test</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('information_schema',),\n",
       " ('mysql',),\n",
       " ('performance_schema',),\n",
       " ('sys',),\n",
       " ('test',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create database test;\n",
    "show databases;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "appointed-balloon",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "0 rows affected.\n",
      "0 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Tables_in_test</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>pet</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('pet',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "use test;\n",
    "\n",
    "create table if not exists pet (\n",
    "    name varchar(20),\n",
    "    owner varchar(20),\n",
    "    species varchar(20),\n",
    "    sex char(1),\n",
    "    birth date,\n",
    "    death date);\n",
    "\n",
    "show tables;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "diagnostic-knock",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>name</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>owner</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>species</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sex</td>\n",
       "        <td>char(1)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>birth</td>\n",
       "        <td>date</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>death</td>\n",
       "        <td>date</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('name', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('owner', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('species', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('sex', 'char(1)', 'YES', '', None, ''),\n",
       " ('birth', 'date', 'YES', '', None, ''),\n",
       " ('death', 'date', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql describe test.pet;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "separated-highway",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into test.pet (name, owner, species, sex, birth, death)\n",
    "values (\"Fluffy\", \"Harold\", \"cat\", \"f\", \"1993-02-04\", NULL),\n",
    "(\"Claws\", \"Gwen\", \"cat\", \"m\", \"1994-03-17\", NULL),\n",
    "(\"Buffy\", \"Harold\", \"dog\", \"f\", \"1989-05-13\", NULL),\n",
    "(\"Fang\", \"Benny\", \"dog\", \"m\", \"1990-08-27\", NULL),\n",
    "(\"Bowser\", \"Diane\", \"dog\", \"m\", \"1979-08-31\", \"1995-07-29\"),\n",
    "(\"Chirpy\", \"Gwen\", \"bird\", \"f\", \"1998-09-11\", NULL),\n",
    "(\"Whistler\", \"Gwen\", \"bird\", \"\", \"1997-12-09\", NULL),\n",
    "(\"Slim\", \"Benny\", \"snake\", \"m\", \"1996-04-29\", NULL);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "broadband-scientist",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://admin:***@sm-mysql.cluster-cnrh6fettief.us-east-1.rds.amazonaws.com:3306/?charset=utf8\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>owner</th>\n",
       "        <th>species</th>\n",
       "        <th>sex</th>\n",
       "        <th>birth</th>\n",
       "        <th>death</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fluffy</td>\n",
       "        <td>Harold</td>\n",
       "        <td>cat</td>\n",
       "        <td>f</td>\n",
       "        <td>1993-02-04</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Claws</td>\n",
       "        <td>Gwen</td>\n",
       "        <td>cat</td>\n",
       "        <td>m</td>\n",
       "        <td>1994-03-17</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Buffy</td>\n",
       "        <td>Harold</td>\n",
       "        <td>dog</td>\n",
       "        <td>f</td>\n",
       "        <td>1989-05-13</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fang</td>\n",
       "        <td>Benny</td>\n",
       "        <td>dog</td>\n",
       "        <td>m</td>\n",
       "        <td>1990-08-27</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bowser</td>\n",
       "        <td>Diane</td>\n",
       "        <td>dog</td>\n",
       "        <td>m</td>\n",
       "        <td>1979-08-31</td>\n",
       "        <td>1995-07-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chirpy</td>\n",
       "        <td>Gwen</td>\n",
       "        <td>bird</td>\n",
       "        <td>f</td>\n",
       "        <td>1998-09-11</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Whistler</td>\n",
       "        <td>Gwen</td>\n",
       "        <td>bird</td>\n",
       "        <td></td>\n",
       "        <td>1997-12-09</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Slim</td>\n",
       "        <td>Benny</td>\n",
       "        <td>snake</td>\n",
       "        <td>m</td>\n",
       "        <td>1996-04-29</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Fluffy', 'Harold', 'cat', 'f', datetime.date(1993, 2, 4), None),\n",
       " ('Claws', 'Gwen', 'cat', 'm', datetime.date(1994, 3, 17), None),\n",
       " ('Buffy', 'Harold', 'dog', 'f', datetime.date(1989, 5, 13), None),\n",
       " ('Fang', 'Benny', 'dog', 'm', datetime.date(1990, 8, 27), None),\n",
       " ('Bowser', 'Diane', 'dog', 'm', datetime.date(1979, 8, 31), datetime.date(1995, 7, 29)),\n",
       " ('Chirpy', 'Gwen', 'bird', 'f', datetime.date(1998, 9, 11), None),\n",
       " ('Whistler', 'Gwen', 'bird', '', datetime.date(1997, 12, 9), None),\n",
       " ('Slim', 'Benny', 'snake', 'm', datetime.date(1996, 4, 29), None)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from test.pet;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}