test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>songplay_id</th>\n",
       "        <th>start_time</th>\n",
       "        <th>user_id</th>\n",
       "        <th>level</th>\n",
       "        <th>song_id</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>session_id</th>\n",
       "        <th>location</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2018-11-01 21:01:46.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2018-11-01 21:05:52.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2018-11-01 21:08:16.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>2018-11-01 21:11:13.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>2018-11-01 21:17:33.796000</td>\n",
       "        <td>8</td>\n",
       "        <td>free</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>139</td>\n",
       "        <td>Phoenix-Mesa-Scottsdale, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, datetime.datetime(2018, 11, 1, 21, 1, 46, 796000), 8, 'free', None, None, 139, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"'),\n",
       " (4, datetime.datetime(2018, 11, 1, 21, 5, 52, 796000), 8, 'free', None, None, 139, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"'),\n",
       " (5, datetime.datetime(2018, 11, 1, 21, 8, 16, 796000), 8, 'free', None, None, 139, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"'),\n",
       " (6, datetime.datetime(2018, 11, 1, 21, 11, 13, 796000), 8, 'free', None, None, 139, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"'),\n",
       " (7, datetime.datetime(2018, 11, 1, 21, 17, 33, 796000), 8, 'free', None, None, 139, 'Phoenix-Mesa-Scottsdale, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.153 Safari/537.36\"')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Summers</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Summers</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Summers</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Summers</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Kaylee</td>\n",
       "        <td>Summers</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(8, 'Kaylee', 'Summers', 'F', 'free'),\n",
       " (8, 'Kaylee', 'Summers', 'F', 'free'),\n",
       " (8, 'Kaylee', 'Summers', 'F', 'free'),\n",
       " (8, 'Kaylee', 'Summers', 'F', 'free'),\n",
       " (8, 'Kaylee', 'Summers', 'F', 'free')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>title</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>year</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOMZWCG12A8C13C480</td>\n",
       "        <td>I Didn&#x27;t Mean To</td>\n",
       "        <td>ARD7TVE1187B99BFB1</td>\n",
       "        <td>0</td>\n",
       "        <td>218.93179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOCIWDW12A8C13D406</td>\n",
       "        <td>Soul Deep</td>\n",
       "        <td>ARMJAGH1187FB546F3</td>\n",
       "        <td>1969</td>\n",
       "        <td>148.03546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOXVLOJ12AB0189215</td>\n",
       "        <td>Amor De Cabaret</td>\n",
       "        <td>ARKRRTF1187B9984DA</td>\n",
       "        <td>0</td>\n",
       "        <td>177.47546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SONHOTT12A8C13493C</td>\n",
       "        <td>Something Girls</td>\n",
       "        <td>AR7G5I41187FB4CE6C</td>\n",
       "        <td>1982</td>\n",
       "        <td>233.40363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOFSOCN12A8C143F5D</td>\n",
       "        <td>Face the Ashes</td>\n",
       "        <td>ARXR32B1187FB57099</td>\n",
       "        <td>2007</td>\n",
       "        <td>209.60608</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOMZWCG12A8C13C480', \"I Didn't Mean To\", 'ARD7TVE1187B99BFB1', 0, 218.93179),\n",
       " ('SOCIWDW12A8C13D406', 'Soul Deep', 'ARMJAGH1187FB546F3', 1969, 148.03546),\n",
       " ('SOXVLOJ12AB0189215', 'Amor De Cabaret', 'ARKRRTF1187B9984DA', 0, 177.47546),\n",
       " ('SONHOTT12A8C13493C', 'Something Girls', 'AR7G5I41187FB4CE6C', 1982, 233.40363),\n",
       " ('SOFSOCN12A8C143F5D', 'Face the Ashes', 'ARXR32B1187FB57099', 2007, 209.60608)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>latitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARD7TVE1187B99BFB1</td>\n",
       "        <td>Casual</td>\n",
       "        <td>California - LA</td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARMJAGH1187FB546F3</td>\n",
       "        <td>The Box Tops</td>\n",
       "        <td>Memphis, TN</td>\n",
       "        <td>35.14968</td>\n",
       "        <td>-90.04892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARKRRTF1187B9984DA</td>\n",
       "        <td>Sonora Santanera</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR7G5I41187FB4CE6C</td>\n",
       "        <td>Adam Ant</td>\n",
       "        <td>London, England</td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARXR32B1187FB57099</td>\n",
       "        <td>Gob</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ARD7TVE1187B99BFB1', 'Casual', 'California - LA', 'NaN', 'NaN'),\n",
       " ('ARMJAGH1187FB546F3', 'The Box Tops', 'Memphis, TN', '35.14968', '-90.04892'),\n",
       " ('ARKRRTF1187B9984DA', 'Sonora Santanera', '', 'NaN', 'NaN'),\n",
       " ('AR7G5I41187FB4CE6C', 'Adam Ant', 'London, England', 'NaN', 'NaN'),\n",
       " ('ARXR32B1187FB57099', 'Gob', '', 'NaN', 'NaN')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_time</th>\n",
       "        <th>hour</th>\n",
       "        <th>day</th>\n",
       "        <th>week</th>\n",
       "        <th>month</th>\n",
       "        <th>year</th>\n",
       "        <th>weekday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:01:46.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:05:52.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:08:16.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:11:13.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-01 21:17:33.796000</td>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 1, 21, 1, 46, 796000), 21, 1, 3, 11, 2018, '3'),\n",
       " (datetime.datetime(2018, 11, 1, 21, 5, 52, 796000), 21, 1, 3, 11, 2018, '3'),\n",
       " (datetime.datetime(2018, 11, 1, 21, 8, 16, 796000), 21, 1, 3, 11, 2018, '3'),\n",
       " (datetime.datetime(2018, 11, 1, 21, 11, 13, 796000), 21, 1, 3, 11, 2018, '3'),\n",
       " (datetime.datetime(2018, 11, 1, 21, 17, 33, 796000), 21, 1, 3, 11, 2018, '3')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## REMEMBER: Restart this notebook to close connection to `sparkifydb`\n",
    "Each time you run the cells above, remember to restart this notebook to close the connection to your database. Otherwise, you won't be able to run your code in `create_tables.py`, `etl.py`, or `etl.ipynb` files since you can't make multiple connections to the same database (in this case, sparkifydb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}