{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract from Chartmetric API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "from string import Template\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "from snowflake.sqlalchemy import URL\n",
    "from sqlalchemy import create_engine, Table, MetaData\n",
    "from sqlalchemy.sql import select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# generate access token\n",
    "def get_token():\n",
    "    TOKEN_URL = 'https://api.chartmetric.io/api/token'\n",
    "    \n",
    "    token_data = '{\"refreshtoken\": ' \\\n",
    "        '\"Im3dJYpuzmZDPWEl8KS4Ss1PT5KmMHfqclf0bhgQnre3430iklkqXEqL3p6USGqC\"}'\n",
    "    token_headers = {'Content-Type': 'application/json'}\n",
    "    \n",
    "    token_response = requests.post(TOKEN_URL, data=token_data, headers=token_headers)\n",
    "    return token_response.json()['token']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# establish Snowflake connection\n",
    "user = 'jamesc'\n",
    "password = ''\n",
    "\n",
    "engine = create_engine(URL(\n",
    "    user=user,\n",
    "    password=password,\n",
    "    account='orchard',\n",
    "    database='dev_engineering',\n",
    "    schema='events_streams',\n",
    "    role='dev_engineering',\n",
    "    warehouse='dev_performance_warehouse'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get headers\n",
    "headers={'Authorization': 'Bearer ' + get_token()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# URLs\n",
    "SEARCH_URL = Template(\n",
    "    'https://api.chartmetric.io/api/search?q=$search_string&limit=$limit')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get list of most popular artists\n",
    "top_artists_table = Table(\n",
    "    'top_artists', MetaData(), autoload=True, autoload_with=engine)\n",
    "top_artists = pd.read_sql(select([top_artists_table]), engine)\n",
    "top_artists['chartmetric_id'] = None\n",
    "top_artists['chartmetric_artistname'] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4680\n",
      "4700\n",
      "4720\n",
      "4740\n",
      "4760\n",
      "4780\n",
      "4800\n",
      "4820\n",
      "4840\n",
      "4860\n",
      "4880\n",
      "4900\n",
      "4920\n",
      "4940\n",
      "4960\n",
      "4980\n"
     ]
    }
   ],
   "source": [
    "# search for artists\n",
    "LIMIT = 10\n",
    "\n",
    "for i in range(len(top_artists)):\n",
    "    if i % 20 == 0:\n",
    "        print(i)\n",
    "    \n",
    "    artist_org = top_artists.artistname[i]\n",
    "    \n",
    "    if '&' in artist_org:\n",
    "        artist = artist_org.split(' & ')[0]\n",
    "    else:\n",
    "        artist = artist_org\n",
    "    \n",
    "    try:\n",
    "        artist_search_response = requests.get(\n",
    "            SEARCH_URL.substitute(search_string=artist, limit=LIMIT), headers=headers)\n",
    "    except:\n",
    "        continue\n",
    "\n",
    "    if artist_search_response.status_code == 200:\n",
    "        results_list = artist_search_response.json()['obj']['artists']\n",
    "        \n",
    "        names = [res['name'].lower() for res in results_list]\n",
    "        \n",
    "        if artist_org in names:\n",
    "            idx = names.index(artist_org)\n",
    "            top_artists.loc[i, 'chartmetric_id'] = results_list[idx]['id']\n",
    "            top_artists.loc[i, 'chartmetric_artistname'] = results_list[idx]['name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write back to Snowflake\n",
    "top_artists.to_sql(\n",
    "    'top_artists_mapping2', \n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    index=False,\n",
    "    index_label=None, \n",
    "    chunksize=20000)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "kernelspec": {
   "display_name": "events_streams",
   "language": "python",
   "name": "events_streams"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}