data_to_db.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "import spotipy\n",
    "# headers = {\n",
    "#         'Authorization': 'Bearer ',\n",
    "#     }\n",
    "token = \"BQAzN34FXVMnvAnbcgwk539IeE1KSee5btXTSnlO5RGyJSP5ZTcWP4ud3hztgW4fTeryJ_m86K-CN2PhNL83kBpUpnAg6FNbW0AuTZGT3Rw31YCzfxiIP_xbLel7E5IHkF4DJmd_PnXmECWoI8qYgIaZqo3mcOWGssK5FkXHY4gw-NGpREtqJElJnm9oAOJLcsEySN4G3eFUTI2h4eoDCtbYxPMPZEkT1WxtAGldQpK9gjOfUzFTdPuWr6P77kyrAc5s7Q1cZEdyAvGVFznPKTXkeT_RhAQ\"\n",
    "spotify = spotipy.Spotify(auth=token)\n",
    "results = spotify.category_playlists(category_id='mood')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_playlist_tracks(username,playlist_id):\n",
    "    results = spotify.user_playlist_tracks(username,playlist_id)\n",
    "    tracks = results['items']\n",
    "\n",
    "    return tracks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [],
   "source": [
    "id_ =[]\n",
    "name=[]\n",
    "play = []\n",
    "url = []\n",
    "for x in results['playlists']['items'][:10]:\n",
    "    user = x['owner']['id']\n",
    "    playlist = x['id']\n",
    "   # print(user,playlist)\n",
    "    for y in get_playlist_tracks(user,playlist):\n",
    "        id_.append(y['track']['id'])\n",
    "        name.append(y['track']['name'])\n",
    "        url.append(y['track']['external_urls']['spotify'])\n",
    "        play.append(x['id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns=['id', 'song_name', 'playlist_id','track_url'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['id'] = id_\n",
    "df['song_name'] = name\n",
    "df['playlist_id'] = play\n",
    "df['track_url'] = url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.song_name = [df.song_name[i].replace(\"'\",\" \") for i in range(len(df))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>song_name</th>\n",
       "      <th>playlist_id</th>\n",
       "      <th>track_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1FHNctV68GUNLgXclG2DtR</td>\n",
       "      <td>Dancing in the Moonlight</td>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>https://open.spotify.com/track/1FHNctV68GUNLgX...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5nNmj1cLH3r4aA4XDJ2bgY</td>\n",
       "      <td>September</td>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>https://open.spotify.com/track/5nNmj1cLH3r4aA4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5XzmZjXhMjDHr7ZfJ6DELQ</td>\n",
       "      <td>FourFiveSeconds</td>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>https://open.spotify.com/track/5XzmZjXhMjDHr7Z...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2tUBqZG2AbRi7Q0BIrVrEj</td>\n",
       "      <td>I Wanna Dance with Somebody (Who Loves Me)</td>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>https://open.spotify.com/track/2tUBqZG2AbRi7Q0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4o6BgsqLIBViaGVbx5rbRk</td>\n",
       "      <td>You Make My Dreams - Remastered</td>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>https://open.spotify.com/track/4o6BgsqLIBViaGV...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       id                                   song_name  \\\n",
       "0  1FHNctV68GUNLgXclG2DtR                    Dancing in the Moonlight   \n",
       "1  5nNmj1cLH3r4aA4XDJ2bgY                                   September   \n",
       "2  5XzmZjXhMjDHr7ZfJ6DELQ                             FourFiveSeconds   \n",
       "3  2tUBqZG2AbRi7Q0BIrVrEj  I Wanna Dance with Somebody (Who Loves Me)   \n",
       "4  4o6BgsqLIBViaGVbx5rbRk             You Make My Dreams - Remastered   \n",
       "\n",
       "              playlist_id                                          track_url  \n",
       "0  37i9dQZF1DWSqmBTGDYngZ  https://open.spotify.com/track/1FHNctV68GUNLgX...  \n",
       "1  37i9dQZF1DWSqmBTGDYngZ  https://open.spotify.com/track/5nNmj1cLH3r4aA4...  \n",
       "2  37i9dQZF1DWSqmBTGDYngZ  https://open.spotify.com/track/5XzmZjXhMjDHr7Z...  \n",
       "3  37i9dQZF1DWSqmBTGDYngZ  https://open.spotify.com/track/2tUBqZG2AbRi7Q0...  \n",
       "4  37i9dQZF1DWSqmBTGDYngZ  https://open.spotify.com/track/4o6BgsqLIBViaGV...  "
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = []\n",
    "for i in range(len(spotify.categories()['categories']['items'])):\n",
    "    categories.append(spotify.categories()['categories']['items'][i]['id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist_ = []\n",
    "playlist_id = []\n",
    "results = spotify.category_playlists(category_id='mood')\n",
    "for i in range(len(results['playlists']['items'])):\n",
    "    user = results['playlists']['items'][i]['owner']['id']\n",
    "    playlist = results['playlists']['items'][i]['id']\n",
    "\n",
    "    playlist_.append(spotify.user_playlist(user, playlist)['name'])\n",
    "    playlist_id.append(spotify.user_playlist(user, playlist)['id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist_df = pd.DataFrame(columns=['playlist_id', 'playlist_name'])\n",
    "playlist_df['playlist_id'] = playlist_id\n",
    "playlist_df['playlist_name'] = playlist_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playlist_id</th>\n",
       "      <th>playlist_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37i9dQZF1DWSqmBTGDYngZ</td>\n",
       "      <td>Songs to Sing in the Shower</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37i9dQZF1DWU0ScTcjJBdj</td>\n",
       "      <td>Relax &amp; Unwind</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37i9dQZF1DX7KNKjOK0o75</td>\n",
       "      <td>Have a Great Day!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37i9dQZF1DX7gIoKXt0gmx</td>\n",
       "      <td>All The Feels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37i9dQZF1DX3rxVfibe1L0</td>\n",
       "      <td>Mood Booster</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              playlist_id                playlist_name\n",
       "0  37i9dQZF1DWSqmBTGDYngZ  Songs to Sing in the Shower\n",
       "1  37i9dQZF1DWU0ScTcjJBdj               Relax & Unwind\n",
       "2  37i9dQZF1DX7KNKjOK0o75            Have a Great Day!\n",
       "3  37i9dQZF1DX7gIoKXt0gmx                All The Feels\n",
       "4  37i9dQZF1DX3rxVfibe1L0                 Mood Booster"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playlist_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(playlist_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "def doQuery( conn ) :\n",
    "    cur = conn.cursor()\n",
    "    for i in range(len(playlist_df)):\n",
    "        cur.execute( \"INSERT INTO playlist_spotify VALUES('\"+playlist_df['playlist_name'][i]+\"','\"+playlist_df['playlist_id'][i]+\"');\")\n",
    "        cur.execute(\"COMMIT;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "myConnection = psycopg2.connect( host=hostname, user=username, password=password, dbname=database )\n",
    "doQuery( myConnection )\n",
    "myConnection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Today's Top Hits\""
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playlist_df['playlist_name'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [],
   "source": [
    "def doQuery( conn ) :\n",
    "    cur = conn.cursor()\n",
    "    for i in range(len(df)):\n",
    "        cur.execute( \"INSERT INTO track_list VALUES('\"+df['id'][i]+\"','\"+df['song_name'][i]+\"','\"+df['playlist_id'][i]+\"','\"+df['track_url'][i]+\"');\")\n",
    "        cur.execute(\"COMMIT;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [],
   "source": [
    "def doQuery( conn ) :\n",
    "    cur = conn.cursor()\n",
    "    for i in range(len(df)):\n",
    "        cur.execute( \"SELECT * from track_list;\")\n",
    "        cur.execute(\"COMMIT;\")"
   ]
  },
  {
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}