test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@sparkifydb'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://postgres:abc@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@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>6</td>\n",
       "        <td>1542761878796</td>\n",
       "        <td>80</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>774</td>\n",
       "        <td>Portland-South Portland, ME</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1542761921796</td>\n",
       "        <td>97</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>671</td>\n",
       "        <td>Lansing-East Lansing, MI</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1542762125796</td>\n",
       "        <td>97</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>671</td>\n",
       "        <td>Lansing-East Lansing, MI</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1542762193796</td>\n",
       "        <td>80</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>774</td>\n",
       "        <td>Portland-South Portland, ME</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1542762329796</td>\n",
       "        <td>97</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>671</td>\n",
       "        <td>Lansing-East Lansing, MI</td>\n",
       "        <td>&quot;Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6, 1542761878796, 80, 'paid', None, None, 774, 'Portland-South Portland, ME', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (7, 1542761921796, 97, 'paid', None, None, 671, 'Lansing-East Lansing, MI', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " (8, 1542762125796, 97, 'paid', None, None, 671, 'Lansing-East Lansing, MI', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"'),\n",
       " (9, 1542762193796, 80, 'paid', None, None, 774, 'Portland-South Portland, ME', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (11, 1542762329796, 97, 'paid', None, None, 671, 'Lansing-East Lansing, MI', '\"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.94 Safari/537.36\"')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@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>80</td>\n",
       "        <td>Tegan</td>\n",
       "        <td>Levine</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>97</td>\n",
       "        <td>Kate</td>\n",
       "        <td>Harrell</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>40</td>\n",
       "        <td>Tucker</td>\n",
       "        <td>Garrison</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65</td>\n",
       "        <td>Amiya</td>\n",
       "        <td>Davidson</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>Ryan</td>\n",
       "        <td>Smith</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(80, 'Tegan', 'Levine', 'F', 'paid'),\n",
       " (97, 'Kate', 'Harrell', 'F', 'paid'),\n",
       " (40, 'Tucker', 'Garrison', 'M', 'free'),\n",
       " (65, 'Amiya', 'Davidson', 'F', 'paid'),\n",
       " (26, 'Ryan', 'Smith', 'M', 'free')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@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>SOBAYLL12A8C138AF9</td>\n",
       "        <td>Sono andati? Fingevo di dormire</td>\n",
       "        <td>ARDR4AC1187FB371A1</td>\n",
       "        <td>0</td>\n",
       "        <td>511.16363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOBCOSW12A8C13D398</td>\n",
       "        <td>Rumba De Barcelona</td>\n",
       "        <td>AR7SMBG1187B9B9066</td>\n",
       "        <td>0</td>\n",
       "        <td>218.38322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOLEYHO12AB0188A85</td>\n",
       "        <td>Got My Mojo Workin</td>\n",
       "        <td>ARAGB2O1187FB3A161</td>\n",
       "        <td>0</td>\n",
       "        <td>338.23302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOGVQGJ12AB017F169</td>\n",
       "        <td>Ten Tonne</td>\n",
       "        <td>AR62SOJ1187FB47BB5</td>\n",
       "        <td>2005</td>\n",
       "        <td>337.68444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SOBKWDJ12A8C13B2F3</td>\n",
       "        <td>Wild Rose (Back 2 Basics Mix)</td>\n",
       "        <td>AR36F9J1187FB406F1</td>\n",
       "        <td>0</td>\n",
       "        <td>230.71302</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOBAYLL12A8C138AF9', 'Sono andati? Fingevo di dormire', 'ARDR4AC1187FB371A1', 0, 511.16363),\n",
       " ('SOBCOSW12A8C13D398', 'Rumba De Barcelona', 'AR7SMBG1187B9B9066', 0, 218.38322),\n",
       " ('SOLEYHO12AB0188A85', 'Got My Mojo Workin', 'ARAGB2O1187FB3A161', 0, 338.23302),\n",
       " ('SOGVQGJ12AB017F169', 'Ten Tonne', 'AR62SOJ1187FB47BB5', 2005, 337.68444),\n",
       " ('SOBKWDJ12A8C13B2F3', 'Wild Rose (Back 2 Basics Mix)', 'AR36F9J1187FB406F1', 0, 230.71302)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@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>lattitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARDR4AC1187FB371A1</td>\n",
       "        <td>Montserrat Caballé;Placido Domingo;Vicente Sardinero;Judith Blegen;Sherrill Milnes;Georg Solti</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR7SMBG1187B9B9066</td>\n",
       "        <td>Los Manolos</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARAGB2O1187FB3A161</td>\n",
       "        <td>Pucho &amp; His Latin Soul Brothers</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR62SOJ1187FB47BB5</td>\n",
       "        <td>Chase &amp; Status</td>\n",
       "        <td></td>\n",
       "        <td>nan</td>\n",
       "        <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AR36F9J1187FB406F1</td>\n",
       "        <td>Bombay Rockers</td>\n",
       "        <td>Denmark</td>\n",
       "        <td>56.27609</td>\n",
       "        <td>9.51695</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ARDR4AC1187FB371A1', 'Montserrat Caballé;Placido Domingo;Vicente Sardinero;Judith Blegen;Sherrill Milnes;Georg Solti', '', nan, nan),\n",
       " ('AR7SMBG1187B9B9066', 'Los Manolos', '', nan, nan),\n",
       " ('ARAGB2O1187FB3A161', 'Pucho & His Latin Soul Brothers', '', nan, nan),\n",
       " ('AR62SOJ1187FB47BB5', 'Chase & Status', '', nan, nan),\n",
       " ('AR36F9J1187FB406F1', 'Bombay Rockers', 'Denmark', 56.27609, 9.51695)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@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-21</td>\n",
       "        <td>0</td>\n",
       "        <td>21</td>\n",
       "        <td>47</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-07</td>\n",
       "        <td>0</td>\n",
       "        <td>7</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-19</td>\n",
       "        <td>1</td>\n",
       "        <td>19</td>\n",
       "        <td>47</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-23</td>\n",
       "        <td>0</td>\n",
       "        <td>23</td>\n",
       "        <td>47</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-27</td>\n",
       "        <td>0</td>\n",
       "        <td>27</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2018, 11, 21), 0, 21, 47, 11, 2018, '2'),\n",
       " (datetime.date(2018, 11, 7), 0, 7, 45, 11, 2018, '2'),\n",
       " (datetime.date(2018, 11, 19), 1, 19, 47, 11, 2018, '0'),\n",
       " (datetime.date(2018, 11, 23), 0, 23, 47, 11, 2018, '4'),\n",
       " (datetime.date(2018, 11, 27), 0, 27, 48, 11, 2018, '1')]"
      ]
     },
     "execution_count": 7,
     "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)."
   ]
  }
 ],
 "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": 2
}