test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "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>1</td>\n",
       "        <td>2018-11-29 00:00:57.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>954</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2018-11-29 00:01:30.796000</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>2018-11-29 00:04:01.796000</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2018-11-29 00:04:55.796000</td>\n",
       "        <td>73</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>954</td>\n",
       "        <td>Tampa-St. Petersburg-Clearwater, FL</td>\n",
       "        <td>&quot;Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2018-11-29 00:07:13.796000</td>\n",
       "        <td>24</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>984</td>\n",
       "        <td>Lake Havasu City-Kingman, AZ</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, datetime.datetime(2018, 11, 29, 0, 0, 57, 796000), 73, 'paid', None, None, 954, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " (2, datetime.datetime(2018, 11, 29, 0, 1, 30, 796000), 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"'),\n",
       " (3, datetime.datetime(2018, 11, 29, 0, 4, 1, 796000), 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"'),\n",
       " (4, datetime.datetime(2018, 11, 29, 0, 4, 55, 796000), 73, 'paid', None, None, 954, 'Tampa-St. Petersburg-Clearwater, FL', '\"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.78.2 (KHTML, like Gecko) Version/7.0.6 Safari/537.78.2\"'),\n",
       " (5, datetime.datetime(2018, 11, 29, 0, 7, 13, 796000), 24, 'paid', None, None, 984, 'Lake Havasu City-Kingman, AZ', '\"Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36\"')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "27 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>73</td>\n",
       "        <td>Jacob</td>\n",
       "        <td>Klein</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Robinson</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Alivia</td>\n",
       "        <td>Terrell</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>Layla</td>\n",
       "        <td>Griffin</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>Kaleb</td>\n",
       "        <td>Cook</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Theodore</td>\n",
       "        <td>Harris</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>98</td>\n",
       "        <td>Jordyn</td>\n",
       "        <td>Powell</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55</td>\n",
       "        <td>Martin</td>\n",
       "        <td>Johnson</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>78</td>\n",
       "        <td>Chloe</td>\n",
       "        <td>Roth</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Jayden</td>\n",
       "        <td>Fox</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Sylvie</td>\n",
       "        <td>Cruz</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>52</td>\n",
       "        <td>Theodore</td>\n",
       "        <td>Smith</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Austin</td>\n",
       "        <td>Rosales</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49</td>\n",
       "        <td>Chloe</td>\n",
       "        <td>Cuevas</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Wyatt</td>\n",
       "        <td>Scott</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39</td>\n",
       "        <td>Walter</td>\n",
       "        <td>Frye</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>75</td>\n",
       "        <td>Joseph</td>\n",
       "        <td>Gutierrez</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>Sean</td>\n",
       "        <td>Wilson</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>Lily</td>\n",
       "        <td>Burns</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>79</td>\n",
       "        <td>James</td>\n",
       "        <td>Martin</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74</td>\n",
       "        <td>Braden</td>\n",
       "        <td>Parker</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>Aleena</td>\n",
       "        <td>Kirby</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",
       "    <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>89</td>\n",
       "        <td>Kynnedi</td>\n",
       "        <td>Sanchez</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82</td>\n",
       "        <td>Avery</td>\n",
       "        <td>Martinez</td>\n",
       "        <td>F</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Rylan</td>\n",
       "        <td>George</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(73, 'Jacob', 'Klein', 'M', 'paid'),\n",
       " (50, 'Ava', 'Robinson', 'F', 'free'),\n",
       " (4, 'Alivia', 'Terrell', 'F', 'free'),\n",
       " (24, 'Layla', 'Griffin', 'F', 'paid'),\n",
       " (54, 'Kaleb', 'Cook', 'M', 'free'),\n",
       " (14, 'Theodore', 'Harris', 'M', 'free'),\n",
       " (98, 'Jordyn', 'Powell', 'F', 'free'),\n",
       " (55, 'Martin', 'Johnson', 'M', 'free'),\n",
       " (78, 'Chloe', 'Roth', 'F', 'free'),\n",
       " (101, 'Jayden', 'Fox', 'M', 'free'),\n",
       " (10, 'Sylvie', 'Cruz', 'F', 'free'),\n",
       " (52, 'Theodore', 'Smith', 'M', 'free'),\n",
       " (12, 'Austin', 'Rosales', 'M', 'free'),\n",
       " (49, 'Chloe', 'Cuevas', 'F', 'paid'),\n",
       " (9, 'Wyatt', 'Scott', 'M', 'free'),\n",
       " (39, 'Walter', 'Frye', 'M', 'free'),\n",
       " (75, 'Joseph', 'Gutierrez', 'M', 'free'),\n",
       " (22, 'Sean', 'Wilson', 'F', 'free'),\n",
       " (32, 'Lily', 'Burns', 'F', 'free'),\n",
       " (79, 'James', 'Martin', 'M', 'free'),\n",
       " (74, 'Braden', 'Parker', 'M', 'free'),\n",
       " (44, 'Aleena', 'Kirby', 'F', 'paid'),\n",
       " (26, 'Ryan', 'Smith', 'M', 'free'),\n",
       " (80, 'Tegan', 'Levine', 'F', 'paid'),\n",
       " (89, 'Kynnedi', 'Sanchez', 'F', 'free'),\n",
       " (82, 'Avery', 'Martinez', 'F', 'paid'),\n",
       " (16, 'Rylan', 'George', 'M', 'paid')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 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>SOINLJW12A8C13314C</td>\n",
       "        <td>City Slickers</td>\n",
       "        <td>AR8IEZO1187B99055E</td>\n",
       "        <td>2008</td>\n",
       "        <td>149.86404</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SOINLJW12A8C13314C', 'City Slickers', 'AR8IEZO1187B99055E', 2008, Decimal('149.86404'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 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>AR8IEZO1187B99055E</td>\n",
       "        <td>Marc Shaiman</td>\n",
       "        <td></td>\n",
       "        <td>NaN</td>\n",
       "        <td>NaN</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AR8IEZO1187B99055E', 'Marc Shaiman', '', Decimal('NaN'), Decimal('NaN'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "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-29 00:00:57.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>29</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-29 00:01:30.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>29</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-29 00:04:01.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>29</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-29 00:04:55.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>29</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-29 00:07:13.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>29</td>\n",
       "        <td>48</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 29, 0, 0, 57, 796000), Decimal('0'), Decimal('29'), Decimal('48'), Decimal('11'), Decimal('2018'), Decimal('3')),\n",
       " (datetime.datetime(2018, 11, 29, 0, 1, 30, 796000), Decimal('0'), Decimal('29'), Decimal('48'), Decimal('11'), Decimal('2018'), Decimal('3')),\n",
       " (datetime.datetime(2018, 11, 29, 0, 4, 1, 796000), Decimal('0'), Decimal('29'), Decimal('48'), Decimal('11'), Decimal('2018'), Decimal('3')),\n",
       " (datetime.datetime(2018, 11, 29, 0, 4, 55, 796000), Decimal('0'), Decimal('29'), Decimal('48'), Decimal('11'), Decimal('2018'), Decimal('3')),\n",
       " (datetime.datetime(2018, 11, 29, 0, 7, 13, 796000), Decimal('0'), Decimal('29'), Decimal('48'), Decimal('11'), Decimal('2018'), Decimal('3'))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "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": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}