{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import elasticsearch\n",
    "import elasticsearch_dsl as ed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "es = elasticsearch.Elasticsearch([{'host': 'localhost', 'port': 9200}])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '1',\n",
       " '_index': 'sw',\n",
       " '_primary_term': 1,\n",
       " '_seq_no': 3,\n",
       " '_shards': {'failed': 0, 'successful': 1, 'total': 2},\n",
       " '_type': 'people',\n",
       " '_version': 4,\n",
       " 'result': 'updated'}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.index(index='sw', doc_type='people', id=1, body={\n",
    "    \"name\": \"Luke Skywalker\",\n",
    "    \"height\": \"172\",\n",
    "    \"mass\": \"77\",\n",
    "    \"hair_color\": \"blond\",\n",
    "    \"birth_year\": \"19BBY\",\n",
    "    \"gender\": \"male\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '2',\n",
       " '_index': 'sw',\n",
       " '_primary_term': 1,\n",
       " '_seq_no': 0,\n",
       " '_shards': {'failed': 0, 'successful': 1, 'total': 2},\n",
       " '_type': 'people',\n",
       " '_version': 1,\n",
       " 'result': 'created'}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.index(index='sw', doc_type='people', id=2, body={\n",
    "    \"name\": \"Darth Vader\",\n",
    "    \"height\": \"172\",\n",
    "    \"mass\": \"77\",\n",
    "    \"hair_color\": \"blond\",\n",
    "    \"birth_year\": \"19BBY\",\n",
    "    \"gender\": \"male\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '3',\n",
       " '_index': 'sw',\n",
       " '_primary_term': 1,\n",
       " '_seq_no': 0,\n",
       " '_shards': {'failed': 0, 'successful': 1, 'total': 2},\n",
       " '_type': 'people',\n",
       " '_version': 1,\n",
       " 'result': 'created'}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.index(index='sw', doc_type='people', id=3, body={\n",
    "    \"name\": \"Leia Organa\",\n",
    "    \"height\": \"162\",\n",
    "    \"mass\": \"57\",\n",
    "    \"hair_color\": \"brown\",\n",
    "    \"birth_year\": \"19BBY\",\n",
    "    \"gender\": \"female\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s = ed.Search().using(es)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response: [<Hit(sw/people/2): {'name': 'Darth Vader', 'height': '172', 'mass': '77', 'hair...}>, <Hit(sw/people/1): {'name': 'Luke Skywalker', 'height': '172', 'mass': '77', 'h...}>, <Hit(sw/people/3): {'name': 'Leia Organa', 'height': '162', 'mass': '57', 'hair...}>]>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.query().execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "r = _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Darth Vader'},\n",
       " {'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Luke Skywalker'},\n",
       " {'birth_year': '19BBY',\n",
       "  'gender': 'female',\n",
       "  'hair_color': 'brown',\n",
       "  'height': '162',\n",
       "  'mass': '57',\n",
       "  'name': 'Leia Organa'}]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[h.to_dict() for h in r.hits]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Darth Vader'},\n",
       " {'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Luke Skywalker'},\n",
       " {'birth_year': '19BBY',\n",
       "  'gender': 'female',\n",
       "  'hair_color': 'brown',\n",
       "  'height': '162',\n",
       "  'mass': '57',\n",
       "  'name': 'Leia Organa'}]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[h.to_dict() for h in s.query('query_string', query=\"*a*\").execute()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "qry = s.query('query_string', query=\"blond\").execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Darth Vader'},\n",
       " {'birth_year': '19BBY',\n",
       "  'gender': 'male',\n",
       "  'hair_color': 'blond',\n",
       "  'height': '172',\n",
       "  'mass': '77',\n",
       "  'name': 'Luke Skywalker'}]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[h.to_dict() for h in qry.hits]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'tracker'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-52-fb050ea6ba5f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mtracker\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdb\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'tracker'"
     ]
    }
   ],
   "source": [
    "from tracker import db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sys.path.append('/Users/joel/src/thundr/tracker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from tracker import db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.setup_session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(postgres://joel:***@localhost:5432/test)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Session.get_bind()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<YtVideo {'ytid': 'spy12', 'title': 'The Inspectors Drink (feat. ) - Albert', 'published': datetime.datetime(2019, 3, 28, 14, 18, 11, 703681, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'first_seen': datetime.datetime(2019, 4, 10, 19, 18, 11, 567850), 'last_updated': datetime.datetime(2019, 4, 10, 19, 18, 11, 567850), 'data': {'id': 'spy12', 'etag': '\"XI7nbFXulYBIpL0ayR_gDh3eu1k/gNjS4mz0Ga8zXt-mupyWt0g4fuk\"', 'kind': 'youtube#video', 'player': {'embedHtml': '<iframe width=\"480\" height=\"270\" src=\"//www.youtube.com/embed/CBq0MeOMnoI\" frameborder=\"0\" allow=\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen></iframe>'}, 'status': {'license': 'youtube', 'embeddable': True, 'uploadStatus': 'processed', 'privacyStatus': 'public', 'publicStatsViewable': True}, 'snippet': {'tags': ['Cub', 'Sport', 'Summer', 'Lover'], 'title': 'The Inspectors Drink (feat. ) - Albert', 'channelId': 'UCesjY7salfBXuUIMI_MnedQ', 'localized': {'title': 'The Inspectors Drink (feat. ) - Albert', 'description': 'Provided to YouTube by Believe SAS\\n\\nSummer Lover · Cub Sport\\n\\nSummer Lover\\n\\n℗ Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.'}, 'categoryId': '10', 'thumbnails': {'high': {'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/hqdefault.jpg', 'width': 480, 'height': 360}, 'maxres': {'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/maxresdefault.jpg', 'width': 1280, 'height': 720}, 'medium': {'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/mqdefault.jpg', 'width': 320, 'height': 180}, 'default': {'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/default.jpg', 'width': 120, 'height': 90}, 'standard': {'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/sddefault.jpg', 'width': 640, 'height': 480}}, 'description': 'Provided to YouTube by Believe SAS\\n\\nSummer Lover · Cub Sport\\n\\nSummer Lover\\n\\n℗ Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.', 'publishedAt': '2019-03-28T14:18:11.703681Z', 'channelTitle': 'Soft Capriccio', 'liveBroadcastContent': 'none'}, 'statistics': {'likeCount': 1000, 'viewCount': 10000, 'commentCount': 10, 'dislikeCount': 100, 'favoriteCount': 50}, 'topicDetails': {'topicCategories': ['https://en.wikipedia.org/wiki/Independent_music', 'https://en.wikipedia.org/wiki/Music'], 'relevantTopicIds': ['/m/04rlf', '/m/05rwpb', '/m/04rlf', '/m/05rwpb']}, 'contentDetails': {'caption': 'false', 'duration': 'PT2M17S', 'dimension': '2d', 'definition': 'hd', 'projection': 'rectangular', 'licensedContent': True, 'regionRestriction': {'allowed': ['JE', 'AW', 'RS', 'JM', 'JO', 'JP', 'RO', 'AT']}}}, 'tags': [], 'track_spyid': '12', 'artist_spyid': '1', 'artist_channel_ytid': None, 'removed_at': None}>"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.Session.query(db.YtVideo).first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "y1 = _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'artist_channel_ytid': None,\n",
       " 'artist_spyid': '1',\n",
       " 'data': {'contentDetails': {'caption': 'false',\n",
       "   'definition': 'hd',\n",
       "   'dimension': '2d',\n",
       "   'duration': 'PT2M17S',\n",
       "   'licensedContent': True,\n",
       "   'projection': 'rectangular',\n",
       "   'regionRestriction': {'allowed': ['JE',\n",
       "     'AW',\n",
       "     'RS',\n",
       "     'JM',\n",
       "     'JO',\n",
       "     'JP',\n",
       "     'RO',\n",
       "     'AT']}},\n",
       "  'etag': '\"XI7nbFXulYBIpL0ayR_gDh3eu1k/gNjS4mz0Ga8zXt-mupyWt0g4fuk\"',\n",
       "  'id': 'spy12',\n",
       "  'kind': 'youtube#video',\n",
       "  'player': {'embedHtml': '<iframe width=\"480\" height=\"270\" src=\"//www.youtube.com/embed/CBq0MeOMnoI\" frameborder=\"0\" allow=\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen></iframe>'},\n",
       "  'snippet': {'categoryId': '10',\n",
       "   'channelId': 'UCesjY7salfBXuUIMI_MnedQ',\n",
       "   'channelTitle': 'Soft Capriccio',\n",
       "   'description': 'Provided to YouTube by Believe SAS\\n\\nSummer Lover · Cub Sport\\n\\nSummer Lover\\n\\n℗ Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.',\n",
       "   'liveBroadcastContent': 'none',\n",
       "   'localized': {'description': 'Provided to YouTube by Believe SAS\\n\\nSummer Lover · Cub Sport\\n\\nSummer Lover\\n\\n℗ Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.',\n",
       "    'title': 'The Inspectors Drink (feat. ) - Albert'},\n",
       "   'publishedAt': '2019-03-28T14:18:11.703681Z',\n",
       "   'tags': ['Cub', 'Sport', 'Summer', 'Lover'],\n",
       "   'thumbnails': {'default': {'height': 90,\n",
       "     'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/default.jpg',\n",
       "     'width': 120},\n",
       "    'high': {'height': 360,\n",
       "     'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/hqdefault.jpg',\n",
       "     'width': 480},\n",
       "    'maxres': {'height': 720,\n",
       "     'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/maxresdefault.jpg',\n",
       "     'width': 1280},\n",
       "    'medium': {'height': 180,\n",
       "     'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/mqdefault.jpg',\n",
       "     'width': 320},\n",
       "    'standard': {'height': 480,\n",
       "     'url': 'https://i.ytimg.com/vi/CBq0MeOMnoI/sddefault.jpg',\n",
       "     'width': 640}},\n",
       "   'title': 'The Inspectors Drink (feat. ) - Albert'},\n",
       "  'statistics': {'commentCount': 10,\n",
       "   'dislikeCount': 100,\n",
       "   'favoriteCount': 50,\n",
       "   'likeCount': 1000,\n",
       "   'viewCount': 10000},\n",
       "  'status': {'embeddable': True,\n",
       "   'license': 'youtube',\n",
       "   'privacyStatus': 'public',\n",
       "   'publicStatsViewable': True,\n",
       "   'uploadStatus': 'processed'},\n",
       "  'topicDetails': {'relevantTopicIds': ['/m/04rlf',\n",
       "    '/m/05rwpb',\n",
       "    '/m/04rlf',\n",
       "    '/m/05rwpb'],\n",
       "   'topicCategories': ['https://en.wikipedia.org/wiki/Independent_music',\n",
       "    'https://en.wikipedia.org/wiki/Music']}},\n",
       " 'first_seen': datetime.datetime(2019, 4, 10, 19, 18, 11, 567850),\n",
       " 'last_updated': datetime.datetime(2019, 4, 10, 19, 18, 11, 567850),\n",
       " 'published': datetime.datetime(2019, 3, 28, 14, 18, 11, 703681, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),\n",
       " 'removed_at': None,\n",
       " 'tags': [],\n",
       " 'title': 'The Inspectors Drink (feat. ) - Albert',\n",
       " 'track_spyid': '12',\n",
       " 'ytid': 'spy12'}"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y1.__json__()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from tracker import json_utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "  \"ytid\": \"spy12\",\n",
      "  \"title\": \"The Inspectors Drink (feat. ) - Albert\",\n",
      "  \"published\": {\n",
      "    \"__type__\": \"datetime\",\n",
      "    \"val\": \"2019-03-28T14:18:11.703681+00:00\"\n",
      "  },\n",
      "  \"first_seen\": {\n",
      "    \"__type__\": \"datetime\",\n",
      "    \"val\": \"2019-04-10T19:18:11.567850\"\n",
      "  },\n",
      "  \"last_updated\": {\n",
      "    \"__type__\": \"datetime\",\n",
      "    \"val\": \"2019-04-10T19:18:11.567850\"\n",
      "  },\n",
      "  \"data\": {\n",
      "    \"id\": \"spy12\",\n",
      "    \"etag\": \"\\\"XI7nbFXulYBIpL0ayR_gDh3eu1k/gNjS4mz0Ga8zXt-mupyWt0g4fuk\\\"\",\n",
      "    \"kind\": \"youtube#video\",\n",
      "    \"player\": {\n",
      "      \"embedHtml\": \"<iframe width=\\\"480\\\" height=\\\"270\\\" src=\\\"//www.youtube.com/embed/CBq0MeOMnoI\\\" frameborder=\\\"0\\\" allow=\\\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\\\" allowfullscreen></iframe>\"\n",
      "    },\n",
      "    \"status\": {\n",
      "      \"license\": \"youtube\",\n",
      "      \"embeddable\": true,\n",
      "      \"uploadStatus\": \"processed\",\n",
      "      \"privacyStatus\": \"public\",\n",
      "      \"publicStatsViewable\": true\n",
      "    },\n",
      "    \"snippet\": {\n",
      "      \"tags\": [\n",
      "        \"Cub\",\n",
      "        \"Sport\",\n",
      "        \"Summer\",\n",
      "        \"Lover\"\n",
      "      ],\n",
      "      \"title\": \"The Inspectors Drink (feat. ) - Albert\",\n",
      "      \"channelId\": \"UCesjY7salfBXuUIMI_MnedQ\",\n",
      "      \"localized\": {\n",
      "        \"title\": \"The Inspectors Drink (feat. ) - Albert\",\n",
      "        \"description\": \"Provided to YouTube by Believe SAS\\n\\nSummer Lover \\u00b7 Cub Sport\\n\\nSummer Lover\\n\\n\\u2117 Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.\"\n",
      "      },\n",
      "      \"categoryId\": \"10\",\n",
      "      \"thumbnails\": {\n",
      "        \"high\": {\n",
      "          \"url\": \"https://i.ytimg.com/vi/CBq0MeOMnoI/hqdefault.jpg\",\n",
      "          \"width\": 480,\n",
      "          \"height\": 360\n",
      "        },\n",
      "        \"maxres\": {\n",
      "          \"url\": \"https://i.ytimg.com/vi/CBq0MeOMnoI/maxresdefault.jpg\",\n",
      "          \"width\": 1280,\n",
      "          \"height\": 720\n",
      "        },\n",
      "        \"medium\": {\n",
      "          \"url\": \"https://i.ytimg.com/vi/CBq0MeOMnoI/mqdefault.jpg\",\n",
      "          \"width\": 320,\n",
      "          \"height\": 180\n",
      "        },\n",
      "        \"default\": {\n",
      "          \"url\": \"https://i.ytimg.com/vi/CBq0MeOMnoI/default.jpg\",\n",
      "          \"width\": 120,\n",
      "          \"height\": 90\n",
      "        },\n",
      "        \"standard\": {\n",
      "          \"url\": \"https://i.ytimg.com/vi/CBq0MeOMnoI/sddefault.jpg\",\n",
      "          \"width\": 640,\n",
      "          \"height\": 480\n",
      "        }\n",
      "      },\n",
      "      \"description\": \"Provided to YouTube by Believe SAS\\n\\nSummer Lover \\u00b7 Cub Sport\\n\\nSummer Lover\\n\\n\\u2117 Cub Sport\\n\\nReleased on: 2018-12-24\\n\\nAuthor: Tim Nelson\\nComposer: Tim Nelson\\n\\nAuto-generated by YouTube.\",\n",
      "      \"publishedAt\": \"2019-03-28T14:18:11.703681Z\",\n",
      "      \"channelTitle\": \"Soft Capriccio\",\n",
      "      \"liveBroadcastContent\": \"none\"\n",
      "    },\n",
      "    \"statistics\": {\n",
      "      \"likeCount\": 1000,\n",
      "      \"viewCount\": 10000,\n",
      "      \"commentCount\": 10,\n",
      "      \"dislikeCount\": 100,\n",
      "      \"favoriteCount\": 50\n",
      "    },\n",
      "    \"topicDetails\": {\n",
      "      \"topicCategories\": [\n",
      "        \"https://en.wikipedia.org/wiki/Independent_music\",\n",
      "        \"https://en.wikipedia.org/wiki/Music\"\n",
      "      ],\n",
      "      \"relevantTopicIds\": [\n",
      "        \"/m/04rlf\",\n",
      "        \"/m/05rwpb\",\n",
      "        \"/m/04rlf\",\n",
      "        \"/m/05rwpb\"\n",
      "      ]\n",
      "    },\n",
      "    \"contentDetails\": {\n",
      "      \"caption\": \"false\",\n",
      "      \"duration\": \"PT2M17S\",\n",
      "      \"dimension\": \"2d\",\n",
      "      \"definition\": \"hd\",\n",
      "      \"projection\": \"rectangular\",\n",
      "      \"licensedContent\": true,\n",
      "      \"regionRestriction\": {\n",
      "        \"allowed\": [\n",
      "          \"JE\",\n",
      "          \"AW\",\n",
      "          \"RS\",\n",
      "          \"JM\",\n",
      "          \"JO\",\n",
      "          \"JP\",\n",
      "          \"RO\",\n",
      "          \"AT\"\n",
      "        ]\n",
      "      }\n",
      "    }\n",
      "  },\n",
      "  \"tags\": [],\n",
      "  \"track_spyid\": \"12\",\n",
      "  \"artist_spyid\": \"1\",\n",
      "  \"artist_channel_ytid\": null,\n",
      "  \"removed_at\": null\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "print(json_utils.dumps(y1, indent=2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ts1 = y1.published.replace(tzinfo=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sync_to_es_yt():\n",
    "    qry = '''select ytid, title, published from yt_videos order by last_updated desc'''\n",
    "    for row in db.Session.execute(qry):\n",
    "        doc = {\n",
    "            'id': row[0],\n",
    "            'title': row[1],\n",
    "            'published': row[2].replace(tzinfo=None).isoformat()\n",
    "        }\n",
    "        es.index(index='yt_videos', doc_type='yt_videos', id=row[0], body=doc)\n",
    "        print(row[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sync_to_es_spy_track():\n",
    "    qry = '''select spyid, name, coalesce(album_data->>'release_date', (first_seen::date)::text) from spy_tracks order by last_updated desc'''\n",
    "    for row in db.Session.execute(qry):\n",
    "        doc = {\n",
    "            'id': row[0],\n",
    "            'title': row[1],\n",
    "            'published': row[2]\n",
    "        }\n",
    "        es.index(index='spy_tracks', doc_type='spy_tracks', id=row[0], body=doc)\n",
    "        print(row[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sync_to_es_sc_track():\n",
    "    qry = '''select scid, name, created_at from sc_tracks order by last_updated desc'''\n",
    "    for row in db.Session.execute(qry):\n",
    "        doc = {\n",
    "            'id': row[0],\n",
    "            'title': row[1],\n",
    "            'published': row[2]\n",
    "        }\n",
    "        es.index(index='sc_tracks', doc_type='sc_tracks', id=row[0], body=doc)\n",
    "        print(row[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.Session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Fate of Sand\n",
      "Baby, I Love You\n",
      "No Crazy (feat. )\n",
      "Baby, I Love You\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "No One Organises Above Wraiths (feat. Counterfeit Shutup)\n",
      "Dying Order (feat. )\n",
      "Go Go Churra\n",
      "He Heard She's Bad\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Tunage On Danger\n",
      "Tower Hear Cure (feat. Starship Internet)\n",
      "\n",
      "Sensitive Rose\n",
      "Spitting Thoughts\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "The Golden Mountain\n",
      "The Fate of Sand\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "The Naked Toasters\n",
      "Revolutionaries Kneeling\n",
      "The Inspectors Drink (feat. )\n",
      "Child\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Trouble Falls Of a Outsider\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Lose My Touch (feat. Dandruff Of Stadium)\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "Preaching Princesss\n",
      "Trouble Falls Of a Outsider\n",
      "Free Games\n",
      "Hurt\n",
      "Sense Combs In a Act\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "Reply Daddy\n",
      "The Inspectors Drink (feat. )\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "Broken (feat. Integral Form)\n",
      "The Lethal Mystery (feat. )\n",
      "Through I Brain\n",
      "Sensitive Rose\n",
      "Duplicate (feat. Snake Hex)\n",
      "The Egyptians of Female\n",
      "The Inspectors Drink (feat. )\n",
      "The Lethal Mystery (feat. )\n",
      "Go Go Churra\n",
      "The Golden Mountain\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "The Egyptians of Female\n",
      "Trouble Falls Of a Outsider\n",
      "The Hard Grunties\n",
      "First Rampage\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "Trouble Falls Of a Outsider\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "Spitting Thoughts\n",
      "No Crazy (feat. )\n",
      "Center From the Flame\n",
      "He Haves Under Minute\n",
      "The Bare Waker\n",
      "Talk (feat. Enchanter Domain)\n",
      "Broken (feat. Integral Form)\n",
      "The Female Feel (feat. Starship Internet)\n",
      "Serpent Beyond the Telephone\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Elongating Storms\n",
      "Hurt\n",
      "Hurt\n",
      "Free-Flowing\n",
      "The Smile's Angels\n",
      "Go Go Churra\n",
      "Expectations Beyond the Machinations\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "Floundering Controls\n",
      "The Golden Mountain\n",
      "Expectations Beyond the Machinations\n",
      "The Colony of Legacy\n",
      "Through I Brain\n",
      "The Bare Waker\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "Snickering Patterns\n",
      "Ear After Us (feat. Beautiful Pretension)\n",
      "Spitting Thoughts\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "The Fate of Sand\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "The Grass's Diamonds\n",
      "Letter Shining (feat. Enchanter Domain)\n",
      "Hurt\n",
      "The Inspectors Drink (feat. )\n",
      "Letter Shining (feat. Enchanter Domain)\n",
      "The Lock Do\n",
      "The Female Feel (feat. Starship Internet)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "The Hard Grunties\n",
      "Fair\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "At The Analytical Fair\n",
      "At The Analytical Fair\n",
      "Child\n",
      "Tower Hear Cure (feat. Starship Internet)\n",
      "No Crazy (feat. )\n",
      "The Bare Waker\n",
      "Many Fits Its\n",
      "Serpent Beyond the Telephone\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "The Smile's Angels\n",
      "Reply Daddy\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "Baby, I Love You\n",
      "The Golden Mountain\n",
      "Soft Capriccio (feat. Beautiful Pretension)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "He Haves Under Minute\n",
      "Talk (feat. Enchanter Domain)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "The Inspectors Drink (feat. )\n",
      "Hurt\n",
      "Serpent Beyond the Telephone\n",
      "Go Go Churra\n",
      "He Heard She's Bad\n",
      "Comrade (feat. Counterfeit Shutup)\n",
      "Enjoy My Love (feat. Enchanter Domain)\n",
      "She Puts She (feat. Dandruff Of Stadium)\n",
      "Go Go Churra\n",
      "The Egyptians of Female\n",
      "The Golden Mountain\n",
      "The Grass's Diamonds\n",
      "Snickering Patterns\n",
      "Go Go Churra\n",
      "The Hard Grunties\n",
      "Elongating Storms\n",
      "Dance Savior\n",
      "Spitting Thoughts\n",
      "The Bare Waker\n",
      "Reply Daddy\n",
      "Dance Savior\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "The Bare Waker\n",
      "The Inspectors Drink (feat. ) - Albert\n",
      "Hurt - Albert\n",
      "Serpent Beyond the Telephone - Albert\n",
      "Go Go Churra - Albert\n",
      "Preaching Princesss Serpent Beyond the Telephone (feat. Red Heart)\n",
      "Sensitive Rose He Haves Under Minute (feat. Starship Internet)\n",
      "Serpent Beyond the Telephone Floundering Controls\n",
      "Soft Capriccio (feat. Beautiful Pretension) Sensitive Rose (feat. Snake Hex)\n",
      "Serpent Beyond the Telephone Expectations Beyond the Machinations (feat. Red Heart)\n",
      "Dying Order (feat. ) Enchanted Intensity (feat. Enchanter Domain)\n",
      "At The Analytical Fair Like The Unwilling Nothing (feat. Snake Hex) (feat. Starship Internet)\n",
      "Legacy Smiles Of a Drone (feat. Integral Form) Spitting Thoughts (feat. Counterfeit Shutup)\n",
      "He Heard She's Bad - Albert\n",
      "Enjoy My Love (feat. Enchanter Domain) - Albert\n",
      "Baby, I Love You - Breakneck\n",
      "The Fate of Sand - Dundurns\n",
      "The Bare Waker Ear After Us (feat. Beautiful Pretension) (feat. Pudding Progress)\n",
      "Trouble Falls Of a Outsider Sense Combs In a Act (feat. Snake Hex)\n",
      " Trouble Falls Of a Outsider (feat. Sophisticated Epoch With The Holding Deed)\n",
      "The Lock Do Free-Flowing\n",
      "The Naked Toasters No Crazy (feat. ) (feat. Sophisticated Epoch With The Holding Deed)\n",
      "Elongating Storms Many Fits Its\n",
      "Floundering Controls Child (feat. Integral Form)\n",
      "Endeavors Sell Surgery The Golden Mountain (feat. Beautiful Pretension)\n",
      "Baby, I Love You - Dundurns\n",
      "The Factory's Relationships (feat. Counterfeit Shutup) - Spornicaty\n",
      "Tunage On Danger - Spornicaty\n",
      "Tower Hear Cure (feat. Starship Internet) - Spornicaty\n",
      "Chomping Procedures (feat. Dandruff Of Stadium) Enchanted Intensity\n",
      "On The Original Factory (feat. Beautiful Pretension) Fair (feat. Enchanter Domain)\n",
      "Enjoy My Love (feat. Enchanter Domain) Enjoy My Love (feat. Enchanter Domain) (feat. Pudding Progress)\n",
      "The Female Feel (feat. Starship Internet) Expectations Beyond the Machinations (feat. Pudding Progress)\n",
      "First Rampage Letter Shining (feat. Enchanter Domain)\n",
      " - Spornicaty\n",
      "Sensitive Rose - Spornicaty\n",
      "Spitting Thoughts - Spornicaty\n",
      "Above Its Stage (feat. Beautiful Pretension) - Spornicaty\n",
      "The Fate of Sand - Spornicaty\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed) - Spornicaty\n",
      "Revolutionaries Kneeling - Spornicaty\n",
      "The Factory's Relationships (feat. Counterfeit Shutup) - Spornicaty\n",
      "Trouble Falls Of a Outsider - Spornicaty\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed) - Spornicaty\n",
      "The Factory's Relationships (feat. Counterfeit Shutup) - Spornicaty\n",
      "Chomping Procedures (feat. Dandruff Of Stadium) - Spornicaty\n",
      "Preaching Princesss - Spornicaty\n",
      "Trouble Falls Of a Outsider - Spornicaty\n",
      "Hurt - Spornicaty\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium) - Spornicaty\n",
      "Reply Daddy - Spornicaty\n",
      "The Inspectors Drink (feat. ) - Spornicaty\n",
      "On The Original Factory (feat. Beautiful Pretension) - Spornicaty\n",
      "Broken (feat. Integral Form) - Spornicaty\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed) - Spornicaty\n",
      "Through I Brain - Spornicaty\n",
      "Sensitive Rose - Spornicaty\n",
      "The Egyptians of Female - Spornicaty\n",
      "The Inspectors Drink (feat. ) - Spornicaty\n",
      "The Lethal Mystery (feat. ) - Spornicaty\n",
      "Go Go Churra - Spornicaty\n",
      "Nobody Succeeds Without Humans (feat. ) - Spornicaty\n",
      "The Egyptians of Female - Spornicaty\n",
      "Trouble Falls Of a Outsider - Spornicaty\n",
      "The Hard Grunties - Spornicaty\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium) - Spornicaty\n",
      "Nobody Succeeds Without Humans (feat. ) - Spornicaty\n",
      "Spitting Thoughts - Spornicaty\n",
      "No Crazy (feat. ) - Spornicaty\n",
      "Center From the Flame - Spornicaty\n",
      "He Haves Under Minute - Spornicaty\n",
      "Talk (feat. Enchanter Domain) - Spornicaty\n",
      "The Female Feel (feat. Starship Internet) - Spornicaty\n",
      "Serpent Beyond the Telephone - Spornicaty\n",
      "Hurt - Spornicaty\n",
      "The Smile's Angels - Spornicaty\n",
      "Above Its Stage (feat. Beautiful Pretension) - Spornicaty\n",
      "Floundering Controls - Spornicaty\n",
      "Expectations Beyond the Machinations - Spornicaty\n",
      "The Colony of Legacy - Spornicaty\n",
      "Through I Brain - Spornicaty\n",
      "No One Organises Above Wraiths (feat. Counterfeit Shutup) - Spornicaty\n",
      "The Bare Waker - Spornicaty\n",
      "Above Its Stage (feat. Beautiful Pretension) - Spornicaty\n",
      "Snickering Patterns - Spornicaty\n",
      "Ear After Us (feat. Beautiful Pretension) - Spornicaty\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Spitting Thoughts - Spornicaty\n",
      "Chomping Procedures (feat. Dandruff Of Stadium) - Spornicaty\n",
      " - Spornicaty\n",
      "Nobody Succeeds Without Humans (feat. ) - Spornicaty\n",
      "The Fate of Sand - Spornicaty\n",
      "Chomping Procedures (feat. Dandruff Of Stadium) - Spornicaty\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed) - Spornicaty\n",
      "The Grass's Diamonds - Spornicaty\n",
      "Letter Shining (feat. Enchanter Domain) - Spornicaty\n",
      "Hurt - Spornicaty\n",
      "The Inspectors Drink (feat. ) - Spornicaty\n",
      "Letter Shining (feat. Enchanter Domain) - Spornicaty\n",
      "The Lock Do - Spornicaty\n",
      "The Female Feel (feat. Starship Internet) - Spornicaty\n",
      "Song Organise Inhabitant (feat. Starship Internet) - Spornicaty\n",
      "The Hard Grunties - Spornicaty\n",
      "Fair - Spornicaty\n",
      "At The Analytical Fair - Spornicaty\n",
      "At The Analytical Fair - Spornicaty\n",
      "Child - Spornicaty\n",
      "Go Go Churra - Sp. Me. Degulour\n",
      "Elongating Storms - Sp. Me. Degulour\n",
      "Dance Savior - Sp. Me. Degulour\n",
      "Spitting Thoughts - Sp. Me. Degulour\n",
      "The Bare Waker - Sp. Me. Degulour\n",
      "Dance Savior - Sp. Me. Degulour\n",
      "The Factory's Relationships (feat. Counterfeit Shutup) - Sp. Me. Degulour\n",
      "Song Organise Inhabitant (feat. Starship Internet) - Sp. Me. Degulour\n",
      "Legacy Smiles Of a Drone (feat. Integral Form) - Sp. Me. Degulour\n",
      "Tower Hear Cure (feat. Starship Internet) - Speak Neezy\n",
      "No Crazy (feat. ) - Speak Neezy\n",
      "The Bare Waker - Speak Neezy\n",
      "Many Fits Its - Speak Neezy\n",
      "The Smile's Angels - Speak Neezy\n",
      "Reply Daddy - Speak Neezy\n",
      "Like The Unwilling Nothing (feat. Snake Hex) - Speak Neezy\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium) - Speak Neezy\n",
      " - Speak Neezy\n",
      "Baby, I Love You - Speak Neezy\n",
      "The Golden Mountain - Speak Neezy\n",
      "Soft Capriccio (feat. Beautiful Pretension) - Speak Neezy\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed) - Speak Neezy\n",
      "Talk (feat. Enchanter Domain) - Speak Neezy\n",
      "Song Organise Inhabitant (feat. Starship Internet) - Speak Neezy\n",
      " Serpent Beyond the Telephone\n",
      "Free Games Elongating Storms\n",
      "Soft Capriccio (feat. Beautiful Pretension) Soft Capriccio (feat. Beautiful Pretension)\n",
      "Annoyed Souls (feat. Dandruff Of Stadium) Reply Daddy (feat. Pudding Progress)\n",
      "Song Organise Inhabitant (feat. Starship Internet) Ear After Us (feat. Beautiful Pretension)\n",
      "The Colony of Legacy The Egyptians of Female\n",
      "Letter Shining (feat. Enchanter Domain) Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "The Inspectors Drink (feat. )\n",
      "Hurt\n",
      "Serpent Beyond the Telephone\n",
      "Go Go Churra\n",
      "He Heard She's Bad\n",
      "Comrade (feat. Counterfeit Shutup)\n",
      "Enjoy My Love (feat. Enchanter Domain)\n",
      "No Crazy (feat. )\n",
      "Baby, I Love You\n",
      "The Fate of Sand\n",
      "Baby, I Love You\n",
      "Free-Flowing\n",
      "Revolutionaries Kneeling\n",
      "The Fate of Sand\n",
      "The Lethal Mystery (feat. )\n",
      "Dance Savior\n",
      "No Crazy (feat. )\n",
      "The Lethal Mystery (feat. )\n",
      "Serpent Beyond the Telephone\n",
      "Talk (feat. Enchanter Domain)\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "Sense Combs In a Act\n",
      "Citizen Paddling\n",
      "Child\n",
      "He Heard She's Bad\n",
      "Tower Hear Cure (feat. Starship Internet)\n",
      "Reply Daddy\n",
      "Expectations Beyond the Machinations\n",
      "Duplicate (feat. Snake Hex)\n",
      "\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "The Grass's Diamonds\n",
      "Visions Through Expert (feat. Snake Hex)\n",
      "First Rampage\n",
      "\n",
      "Enchanted Intensity\n",
      "Through I Brain\n",
      "Free-Flowing\n",
      "At The Analytical Fair\n",
      "Reply Daddy\n",
      "Endeavors Sell Surgery\n",
      "Annoyed Souls (feat. Dandruff Of Stadium)\n",
      "Snickering Patterns\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "Through I Brain\n",
      "Citizen Paddling\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "The Bare Waker\n",
      "Expectations Beyond the Machinations\n",
      "Broken (feat. Integral Form)\n",
      "Sense Combs In a Act\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "Free-Flowing\n",
      "At The Analytical Fair\n",
      "I'll Be There For You\n",
      "Spitting Thoughts\n"
     ]
    }
   ],
   "source": [
    "sync_to_es_spy_track()\n",
    "sync_to_es_yt()\n",
    "sync_to_es_sc_track()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Fate of Sand\n",
      "Baby, I Love You\n",
      "No Crazy (feat. )\n",
      "Baby, I Love You\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "No One Organises Above Wraiths (feat. Counterfeit Shutup)\n",
      "Dying Order (feat. )\n",
      "Go Go Churra\n",
      "He Heard She's Bad\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Tunage On Danger\n",
      "Tower Hear Cure (feat. Starship Internet)\n",
      "\n",
      "Sensitive Rose\n",
      "Spitting Thoughts\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "The Golden Mountain\n",
      "The Fate of Sand\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "The Naked Toasters\n",
      "Revolutionaries Kneeling\n",
      "The Inspectors Drink (feat. )\n",
      "Child\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Trouble Falls Of a Outsider\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Lose My Touch (feat. Dandruff Of Stadium)\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "Preaching Princesss\n",
      "Trouble Falls Of a Outsider\n",
      "Free Games\n",
      "Hurt\n",
      "Sense Combs In a Act\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "Reply Daddy\n",
      "The Inspectors Drink (feat. )\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "Broken (feat. Integral Form)\n",
      "The Lethal Mystery (feat. )\n",
      "Through I Brain\n",
      "Sensitive Rose\n",
      "Duplicate (feat. Snake Hex)\n",
      "The Egyptians of Female\n",
      "The Inspectors Drink (feat. )\n",
      "The Lethal Mystery (feat. )\n",
      "Go Go Churra\n",
      "The Golden Mountain\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "The Egyptians of Female\n",
      "Trouble Falls Of a Outsider\n",
      "The Hard Grunties\n",
      "First Rampage\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "Trouble Falls Of a Outsider\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "Spitting Thoughts\n",
      "No Crazy (feat. )\n",
      "Center From the Flame\n",
      "He Haves Under Minute\n",
      "The Bare Waker\n",
      "Talk (feat. Enchanter Domain)\n",
      "Broken (feat. Integral Form)\n",
      "The Female Feel (feat. Starship Internet)\n",
      "Serpent Beyond the Telephone\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Elongating Storms\n",
      "Hurt\n",
      "Hurt\n",
      "Free-Flowing\n",
      "The Smile's Angels\n",
      "Go Go Churra\n",
      "Expectations Beyond the Machinations\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "Floundering Controls\n",
      "The Golden Mountain\n",
      "Expectations Beyond the Machinations\n",
      "The Colony of Legacy\n",
      "Through I Brain\n",
      "The Bare Waker\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "Snickering Patterns\n",
      "Ear After Us (feat. Beautiful Pretension)\n",
      "Spitting Thoughts\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "\n",
      "Nobody Succeeds Without Humans (feat. )\n",
      "The Fate of Sand\n",
      "Chomping Procedures (feat. Dandruff Of Stadium)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "Above Its Stage (feat. Beautiful Pretension)\n",
      "The Grass's Diamonds\n",
      "Letter Shining (feat. Enchanter Domain)\n",
      "Hurt\n",
      "The Inspectors Drink (feat. )\n",
      "Letter Shining (feat. Enchanter Domain)\n",
      "The Lock Do\n",
      "The Female Feel (feat. Starship Internet)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "The Hard Grunties\n",
      "Fair\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "At The Analytical Fair\n",
      "At The Analytical Fair\n",
      "Child\n",
      "Tower Hear Cure (feat. Starship Internet)\n",
      "No Crazy (feat. )\n",
      "The Bare Waker\n",
      "Many Fits Its\n",
      "Serpent Beyond the Telephone\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "The Smile's Angels\n",
      "Reply Daddy\n",
      "Like The Unwilling Nothing (feat. Snake Hex)\n",
      "Behind Nobody Agent (feat. Dandruff Of Stadium)\n",
      "\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "Baby, I Love You\n",
      "The Golden Mountain\n",
      "Soft Capriccio (feat. Beautiful Pretension)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "He Haves Under Minute\n",
      "Talk (feat. Enchanter Domain)\n",
      "Bring Gravitas (feat. Sophisticated Epoch With The Holding Deed)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "On The Original Factory (feat. Beautiful Pretension)\n",
      "The Inspectors Drink (feat. )\n",
      "Hurt\n",
      "Serpent Beyond the Telephone\n",
      "Go Go Churra\n",
      "He Heard She's Bad\n",
      "Comrade (feat. Counterfeit Shutup)\n",
      "Enjoy My Love (feat. Enchanter Domain)\n",
      "She Puts She (feat. Dandruff Of Stadium)\n",
      "Go Go Churra\n",
      "The Egyptians of Female\n",
      "The Golden Mountain\n",
      "The Grass's Diamonds\n",
      "Snickering Patterns\n",
      "Go Go Churra\n",
      "The Hard Grunties\n",
      "Elongating Storms\n",
      "Dance Savior\n",
      "Spitting Thoughts\n",
      "The Bare Waker\n",
      "Reply Daddy\n",
      "Dance Savior\n",
      "The Factory's Relationships (feat. Counterfeit Shutup)\n",
      "Song Organise Inhabitant (feat. Starship Internet)\n",
      "Legacy Smiles Of a Drone (feat. Integral Form)\n",
      "The Bare Waker\n"
     ]
    }
   ],
   "source": [
    "sync_to_es_spy_track()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s = ed.Search().using(es)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def query(q):\n",
    "    return [dict(h.to_dict(), type=h.meta['doc_type']) for h in s.query('match', title=q).execute().hits]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def to_obj(hit) {\n",
    "    return dict(hit.to_dict(), type=hit.meta['doc_type'])\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response: [<Hit(spy_tracks/spy_tracks/11095): {'id': '11095', 'title': 'The Hard Grunties', 'published': '...}>, <Hit(spy_tracks/spy_tracks/13007): {'id': '13007', 'title': \"The Smile's Angels\", 'published': ...}>, <Hit(sc_tracks/sc_tracks/28002): {'id': 28002, 'title': 'The Bare Waker', 'published': '2019-...}>, <Hit(sc_tracks/sc_tracks/21003): {'id': 21003, 'title': 'The Fate of Sand', 'published': '201...}>, <Hit(sc_tracks/sc_tracks/22003): {'id': 22003, 'title': 'Serpent Beyond the Telephone', 'publ...}>, <Hit(sc_tracks/sc_tracks/25001): {'id': 25001, 'title': \"The Grass's Diamonds\", 'published': ...}>, <Hit(spy_tracks/spy_tracks/11010): {'id': '11010', 'title': 'The Fate of Sand', 'published': '2...}>, <Hit(spy_tracks/spy_tracks/11043): {'id': '11043', 'title': 'The Egyptians of Female', 'publish...}>, <Hit(spy_tracks/spy_tracks/11099): {'id': '11099', 'title': 'At The Analytical Fair', 'publishe...}>, <Hit(sc_tracks/sc_tracks/12): {'id': 12, 'title': 'The Inspectors Drink (feat. )', 'publis...}>]>"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.query('match', title='the').execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<elasticsearch_dsl.search.Search at 0x10f608048>"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.index('wl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': '11095',\n",
       "  'published': '2019-02-19',\n",
       "  'title': 'The Hard Grunties',\n",
       "  'type': 'spy_tracks'},\n",
       " {'id': '13007',\n",
       "  'published': '2018-12-31',\n",
       "  'title': \"The Smile's Angels\",\n",
       "  'type': 'spy_tracks'},\n",
       " {'id': 28002,\n",
       "  'published': '2019-03-12',\n",
       "  'title': 'The Bare Waker',\n",
       "  'type': 'sc_tracks'},\n",
       " {'id': 21003,\n",
       "  'published': '2019-04-05',\n",
       "  'title': 'The Fate of Sand',\n",
       "  'type': 'sc_tracks'},\n",
       " {'id': 22003,\n",
       "  'published': '2019-02-27',\n",
       "  'title': 'Serpent Beyond the Telephone',\n",
       "  'type': 'sc_tracks'},\n",
       " {'id': 25001,\n",
       "  'published': '2019-04-09',\n",
       "  'title': \"The Grass's Diamonds\",\n",
       "  'type': 'sc_tracks'},\n",
       " {'id': '11010',\n",
       "  'published': '2019-03-21',\n",
       "  'title': 'The Fate of Sand',\n",
       "  'type': 'spy_tracks'},\n",
       " {'id': '11043',\n",
       "  'published': '2019-04-05',\n",
       "  'title': 'The Egyptians of Female',\n",
       "  'type': 'spy_tracks'},\n",
       " {'id': '11099',\n",
       "  'published': '2019-02-19',\n",
       "  'title': 'At The Analytical Fair',\n",
       "  'type': 'spy_tracks'},\n",
       " {'id': 12,\n",
       "  'published': '2019-01-02',\n",
       "  'title': 'The Inspectors Drink (feat. )',\n",
       "  'type': 'sc_tracks'}]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query('the')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ix = ed.Index('wl', using=es)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'acknowledged': True}"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ix.delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'yt_videos'"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_.meta['doc_type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query('h')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests_aws4auth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import boto3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cr = boto3.Session().get_credentials()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'AKIAJ3GHLYDB6OHKPVWA'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cr.access_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "host = 'search-whtlst-4gg7jvjvs3m7caz7da3dkl5ody.eu-west-1.es.amazonaws.com' # For example, my-test-domain.us-east-1.es.amazonaws.com\n",
    "region = 'eu-west-1' # e.g. us-west-1\n",
    "service = 'es'\n",
    "awsauth = requests_aws4auth.AWS4Auth(cr.access_key, cr.secret_key, region, service)\n",
    "es_prod = elasticsearch.Elasticsearch(\n",
    "    hosts = [{'host': host, 'port': 443}],\n",
    "    http_auth = awsauth,\n",
    "    use_ssl = True,\n",
    "    verify_certs = True,\n",
    "    connection_class = elasticsearch.RequestsHttpConnection\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'cluster_name': '322697034778:whtlst',\n",
       " 'cluster_uuid': 'Nu2blWdgR0WCT8KGQ70VYA',\n",
       " 'name': 'xxkExCU',\n",
       " 'tagline': 'You Know, for Search',\n",
       " 'version': {'build_date': '2019-04-01T17:24:35.943541Z',\n",
       "  'build_flavor': 'oss',\n",
       "  'build_hash': 'd2ef93d',\n",
       "  'build_snapshot': False,\n",
       "  'build_type': 'zip',\n",
       "  'lucene_version': '7.5.0',\n",
       "  'minimum_index_compatibility_version': '5.0.0',\n",
       "  'minimum_wire_compatibility_version': '5.6.0',\n",
       "  'number': '6.5.4'}}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es_prod.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '1',\n",
       " '_index': 'starwars',\n",
       " '_primary_term': 1,\n",
       " '_seq_no': 1,\n",
       " '_shards': {'failed': 0, 'successful': 2, 'total': 2},\n",
       " '_type': 'people',\n",
       " '_version': 2,\n",
       " 'result': 'updated'}"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es_prod.index(index='starwars', doc_type='people', id=1, body={\n",
    "    \"name\": \"Darth Vader\",\n",
    "    \"height\": \"172\",\n",
    "    \"mass\": \"77\",\n",
    "    \"hair_color\": \"blond\",\n",
    "    \"birth_year\": \"19BBY\",\n",
    "    \"gender\": \"male\",\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps = ed.Search(using=es_prod)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Hit(spy_track/spy_track/01DOm09B2AbiTARAbXVI6g): {'spyid': '01DOm09B2AbiTARAbXVI6g', 'title': 'CHopstix (with...}>]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ps.query('match', title=\"CHopstix\").execute().hits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "hits = _"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'published': '2019-04-08',\n",
       " 'spyid': '01DOm09B2AbiTARAbXVI6g',\n",
       " 'title': 'CHopstix (with Travis Scott)'}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hits[0].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import base64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import uuid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1516fd063faa'"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(uuid.uuid4()).split('-')[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}