{ "cells": [ { "cell_type": "code", "execution_count": 32, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import sys\n", "sys.path.insert(0, '/Users/joel/src/thundr/tracker')" ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import prettytable" ] }, { "cell_type": "code", "execution_count": 103, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from datetime import datetime\n", "from dateutil import parser\n", "import gspread\n", "from oauth2client.service_account import ServiceAccountCredentials\n", "\n", "from tracker import config\n", "\n", "\n", "scope = ['https://spreadsheets.google.com/feeds']\n", "\n", "credentials = ServiceAccountCredentials.from_json_keyfile_dict(\n", " config.google_auth_dict,\n", " scopes=scope\n", ")\n", "\n", "client = gspread.authorize(credentials)" ] }, { "cell_type": "code", "execution_count": 104, "metadata": { "collapsed": true }, "outputs": [], "source": [ "s = client.get_spreadsheets_feed()\n" ] }, { "cell_type": "code", "execution_count": 105, "metadata": { "collapsed": true }, "outputs": [], "source": [ "de = client.open('Data Exports')" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from tracker import db" ] }, { "cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Creating session....\n" ] } ], "source": [ "db.setup_session()" ] }, { "cell_type": "code", "execution_count": 114, "metadata": { "collapsed": true }, "outputs": [], "source": [ "db.Session.rollback()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Engine(postgres://bot:***@whitelist.cbn1zk7uet6r.eu-west-1.rds.amazonaws.com:5432/whitelist)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.Session.get_bind()" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def array_to_sheet(arr, sheet, start_row=0, start_col=0):\n", " mapping = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'\n", " range_str = '{start_col}{start_row}:{end_col}{end_row}'.format(\n", " start_col=mapping[start_col],\n", " start_row=1,\n", " end_col=mapping[len(arr[0])+start_col-1],\n", " end_row=len(arr)\n", " )\n", " # Select a range\n", " cell_list = sheet.range(range_str)\n", " \n", " cell_stack = list(reversed(cell_list))\n", " \n", " for row in arr:\n", " for field in row:\n", " cell = cell_stack.pop()\n", " cell.value = field\n", "\n", " # Update in batch\n", " sheet.update_cells(cell_list)" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def query_to_sheet(query, sheet, params=None, start_col=0):\n", " result_proxy = db.Session.execute(query, params)\n", " names = [a.name for a in result_proxy.cursor.description]\n", " \n", " arr = [names] + result_proxy.fetchall()\n", " \n", " array_to_sheet(arr, sheet, start_col=start_col)" ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "collapsed": true }, "outputs": [], "source": [ "def pretty_query(sql, params=None):\n", " result_proxy = db.Session.execute(sql, params)\n", " print(prettytable.from_db_cursor(result_proxy.cursor))\n", " " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "s" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "collapsed": true }, "outputs": [], "source": [ "sql1 = '''\n", "WITH \n", "\n", "days as (\n", " select generate_series(current_date-30, current_date, '1 day') as d\n", "),\n", "\n", "pops as (\n", " select as_of::date d, count(*) c\n", " from spy_track_popularity\n", " group by 1\n", "),\n", "tracks as (\n", " select first_seen::date d, count(*) c\n", " from spy_tracks \n", " group by 1\n", "),\n", "\n", "follows as (\n", " select seen_at::date d, count(*) as f from sc_followings group by 1\n", "),\n", "\n", "likes as (\n", " select seen_at::date d, count(*) f from sc_likes group by 1\n", ")\n", "\n", "\n", "select \n", " days.d::date,\n", " pops.c spy_popularities, \n", " tracks.c spy_new_tracks,\n", " follows.f sc_new_follows,\n", " likes.f sc_new_likes\n", "from days\n", "left join pops using(d) \n", "left join tracks using(d)\n", "left join likes using(d)\n", "left join follows using(d)\n", "\n", "order by days.d desc\n", "\n", "limit 15\n", "'''\n" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+------------+------------------+----------------+----------------+--------------+\n", "| d | spy_popularities | spy_new_tracks | sc_new_follows | sc_new_likes |\n", "+------------+------------------+----------------+----------------+--------------+\n", "| 2017-09-21 | None | None | 409 | 613 |\n", "| 2017-09-20 | None | None | None | None |\n", "| 2017-09-19 | None | None | None | None |\n", "| 2017-09-18 | None | None | None | None |\n", "| 2017-09-17 | 1898 | 29 | 34 | 57 |\n", "| 2017-09-16 | 1893 | 372 | 64 | 144 |\n", "| 2017-09-15 | 1904 | 96 | 76 | 154 |\n", "| 2017-09-14 | 1912 | 4 | 115 | 153 |\n", "| 2017-09-13 | 1913 | 33 | 87 | 143 |\n", "| 2017-09-12 | 1903 | 7 | 111 | 191 |\n", "| 2017-09-11 | 1903 | 5 | 41 | 72 |\n", "| 2017-09-10 | 1901 | 19 | 53 | 86 |\n", "| 2017-09-09 | 1910 | 418 | 70 | 173 |\n", "| 2017-09-08 | 1966 | 81 | 119 | 157 |\n", "| 2017-09-07 | 1932 | 7 | 110 | 162 |\n", "| 2017-09-06 | 1939 | 331 | 95 | 171 |\n", "| 2017-09-05 | 1935 | 14 | 69 | 100 |\n", "| 2017-09-04 | 1935 | 5 | 67 | 98 |\n", "| 2017-09-03 | 1939 | 22 | 47 | 88 |\n", "| 2017-09-02 | 1947 | 117 | 73 | 129 |\n", "| 2017-09-01 | 1933 | 48 | 97 | 150 |\n", "| 2017-08-31 | 1913 | 11 | 146 | 198 |\n", "| 2017-08-30 | 1911 | 56 | 83 | 135 |\n", "| 2017-08-29 | 1905 | 15 | 60 | 83 |\n", "| 2017-08-28 | 1907 | None | 36 | 75 |\n", "| 2017-08-27 | 1907 | 1 | 46 | 87 |\n", "| 2017-08-26 | 1907 | 393 | 63 | 119 |\n", "| 2017-08-25 | 1942 | 59 | 89 | 260 |\n", "| 2017-08-24 | 1918 | 6 | 92 | 152 |\n", "| 2017-08-23 | 1916 | 58 | 66 | 158 |\n", "+------------+------------------+----------------+----------------+--------------+\n" ] } ], "source": [ "db.Session.rollback()\n", "pretty_query(sql1)" ] }, { "cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [], "source": [ "import requests\n", "from tracker import config\n", "\n" ] }, { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Creating session....\n" ] } ], "source": [ "from importlib import reload\n", "reload(config)\n", "reload(db)\n", "db.setup_session()" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "collapsed": true }, "outputs": [], "source": [ "checks = db.Session.execute(sql1).fetchall()" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'2017-09-21: None/None/409/613'" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "headline = \"{0}: {1}/{2}/{3}/{4}\".format(*checks[0])" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [], "source": [ "def get_pretty_query(sql, params=None):\n", " result_proxy = db.Session.execute(sql, params)\n", " return prettytable.from_db_cursor(result_proxy.cursor)" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": [ "\n", "def send_simple_message(txt, subject):\n", " return requests.post(\n", " \"https://api.mailgun.net/v3/\" + config.mailgun_domain + \"/messages\",\n", " auth=(\"api\", config.mailgun_api_key),\n", " data={\"from\": \"Excited User \",\n", " \"to\": [\"ops@whtlst.in\"],\n", " \"subject\": subject,\n", " \"text\": txt})" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "a = send_simple_message(txt=get_pretty_query(sql1), subject='Daily Stats')" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'{\\n \"id\": \"<20170921121645.96121.9EA32FD721491206@sandbox7ec09df3f68d4e1ba387b8fbeb1e9592.mailgun.org>\",\\n \"message\": \"Queued. Thank you.\"\\n}'" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "a.text" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'https://api.mailgun.net/v3/sandbox7ec09df3f68d4e1ba387b8fbeb1e9592.mailgun.org/messages'" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "config.mailgun_base_url + \"/messages\"" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Creating session....\n" ] } ], "source": [ "from tracker import db\n", "db.setup_session()\n", "\n", "qry = '''\n", "WITH \n", "\n", "days as (\n", " select generate_series(current_date-30, current_date, '1 day') as d\n", "),\n", "\n", "pops as (\n", " select as_of::date d, count(*) c\n", " from spy_track_popularity\n", " group by 1\n", "),\n", "tracks as (\n", " select first_seen::date d, count(*) c\n", " from spy_tracks \n", " group by 1\n", "),\n", "\n", "follows as (\n", " select seen_at::date d, count(*) as f from sc_followings group by 1\n", "),\n", "\n", "likes as (\n", " select seen_at::date d, count(*) f from sc_likes group by 1\n", ")\n", "\n", "\n", "select \n", " days.d::date,\n", " pops.c spy_popularities, \n", " tracks.c spy_new_tracks,\n", " follows.f sc_new_follows,\n", " likes.f sc_new_likes\n", "from days\n", "left join pops using(d) \n", "left join tracks using(d)\n", "left join likes using(d)\n", "left join follows using(d)\n", "\n", "order by days.d desc\n", "\n", "limit 15\n", "\n", "'''\n", "\n", "headline = \"{0}: {1}/{2}/{3}/{4}\".format(*db.Session.execute(qry).fetchone())\n", "\n", "\n", "\n", "table = db.get_pretty_query(qry)\n" ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from IPython.core.display import display, HTML\n" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "
dspy_popularitiesspy_new_trackssc_new_followssc_new_likes
2017-09-21NoneNone409613
2017-09-20NoneNoneNoneNone
2017-09-19NoneNoneNoneNone
2017-09-18NoneNoneNoneNone
2017-09-171898293457
2017-09-16189337264144
2017-09-1519049676154
2017-09-1419124115153
2017-09-1319133387143
2017-09-1219037111191
2017-09-11190354172
2017-09-101901195386
2017-09-09191041870173
2017-09-08196681119157
2017-09-0719327110162
\n" ] } ], "source": [ "print (db.html_table_query(qry))" ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
dspy_popularitiesspy_new_trackssc_new_followssc_new_likes
2017-09-21NoneNone409613
2017-09-20NoneNoneNoneNone
2017-09-19NoneNoneNoneNone
2017-09-18NoneNoneNoneNone
2017-09-171898293457
2017-09-16189337264144
2017-09-1519049676154
2017-09-1419124115153
2017-09-1319133387143
2017-09-1219037111191
2017-09-11190354172
2017-09-101901195386
2017-09-09191041870173
2017-09-08196681119157
2017-09-0719327110162
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "display(HTML(db.html_table_query(qry)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Alice Merton" ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from datetime import *" ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "collapsed": true }, "outputs": [], "source": [ "twelve_hours_ago = datetime.today() - timedelta(hours=12)" ] }, { "cell_type": "code", "execution_count": 79, "metadata": {}, "outputs": [], "source": [ " schem = db.Session.query(db.SpyTrack).filter(db.SpyTrack.last_updated < twelve_hours_ago).order_by(db.SpyTrack.first_seen.desc()).limit(10)" ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[, , , , , , , , , ]\n" ] } ], "source": [ "print(schem.all())\n" ] }, { "cell_type": "code", "execution_count": 31, "metadata": { "collapsed": true }, "outputs": [], "source": [ "alice_spyid = '7f0OLhGgBMX9fUjm1dcPip'" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+------------------------+------------------------+----------------------------+-------------------------------+\n", "| spyid | name | first_seen | Label |\n", "+------------------------+------------------------+----------------------------+-------------------------------+\n", "| 7GSuJlP3XN550RQD8t88mj | No Roots | 2016-12-05 08:24:12.167711 | 2016 Paper Plane Records Int. |\n", "| 1L6Sl0eq9rb4cZwEv9OWd8 | Hit The Ground Running | 2017-02-03 03:00:26.123265 | None |\n", "| 4bPWDXpN6MlTZBedxega3X | No Roots | 2017-04-29 03:00:11.571033 | None |\n", "+------------------------+------------------------+----------------------------+-------------------------------+\n" ] } ], "source": [ "pretty_query('''\n", "select spyid, name, first_seen, album_data->'copyrights'->0->'text' as \"Label\" from spy_tracks \n", "where primary_artist_spyid = '7f0OLhGgBMX9fUjm1dcPip'\n", "order by first_seen asc\n", "''')\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Alice Merton's first track was _'No Roots'_" ] }, { "cell_type": "code", "execution_count": 96, "metadata": { "collapsed": true }, "outputs": [], "source": [ "popularity_sql = '''\n", "select as_of, value from spy_track_popularity where track_spyid = :track_spyid\n", "'''" ] }, { "cell_type": "code", "execution_count": 99, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+---------------------+-------+\n", "| as_of | value |\n", "+---------------------+-------+\n", "| 2016-12-05 08:00:00 | 6 |\n", "| 2016-12-06 08:00:00 | 13 |\n", "| 2016-12-07 08:00:00 | 16 |\n", "| 2016-12-08 08:00:00 | 43 |\n", "| 2016-12-09 08:00:00 | 47 |\n", "| 2016-12-10 08:00:00 | 47 |\n", "| 2016-12-12 09:00:00 | 52 |\n", "| 2016-12-13 09:00:00 | 54 |\n", "| 2016-12-14 08:00:00 | 55 |\n", "| 2016-12-16 08:00:00 | 56 |\n", "| 2016-12-17 08:00:00 | 57 |\n", "| 2016-12-18 08:00:00 | 57 |\n", "| 2016-12-20 03:00:00 | 57 |\n", "| 2016-12-21 03:00:00 | 57 |\n", "| 2016-12-22 03:00:00 | 57 |\n", "| 2016-12-23 03:00:00 | 57 |\n", "| 2016-12-24 03:00:00 | 58 |\n", "| 2016-12-25 03:00:00 | 58 |\n", "| 2016-12-26 03:00:00 | 58 |\n", "| 2016-12-27 03:00:00 | 58 |\n", "| 2016-12-28 03:00:00 | 58 |\n", "| 2016-12-29 03:00:00 | 58 |\n", "| 2016-12-30 03:00:00 | 58 |\n", "| 2016-12-31 03:00:00 | 58 |\n", "| 2017-01-01 03:00:00 | 58 |\n", "| 2017-01-02 03:00:00 | 58 |\n", "| 2017-01-03 03:00:00 | 58 |\n", "| 2017-01-04 03:00:00 | 58 |\n", "| 2017-01-05 03:00:00 | 59 |\n", "| 2017-01-06 03:00:00 | 59 |\n", "| 2017-01-07 03:00:00 | 59 |\n", "| 2017-01-08 03:00:00 | 59 |\n", "| 2017-01-09 03:00:00 | 60 |\n", "| 2017-01-10 03:00:00 | 60 |\n", "| 2017-01-11 03:00:00 | 60 |\n", "| 2017-01-12 03:00:00 | 60 |\n", "| 2017-01-13 03:00:00 | 60 |\n", "| 2017-01-14 03:00:00 | 60 |\n", "| 2017-01-15 03:00:00 | 61 |\n", "| 2017-01-16 03:00:00 | 61 |\n", "| 2017-01-17 03:00:00 | 61 |\n", "| 2017-01-18 03:00:00 | 61 |\n", "| 2017-01-19 03:00:00 | 61 |\n", "| 2017-01-20 03:00:00 | 61 |\n", "| 2017-01-21 03:00:00 | 60 |\n", "| 2017-01-22 03:00:00 | 60 |\n", "| 2017-01-23 03:00:00 | 60 |\n", "| 2017-01-24 03:00:00 | 60 |\n", "| 2017-01-25 03:00:00 | 60 |\n", "| 2017-01-26 03:00:00 | 60 |\n", "| 2017-01-27 03:00:00 | 60 |\n", "| 2017-01-28 03:00:00 | 60 |\n", "| 2017-01-31 03:00:00 | 60 |\n", "| 2017-02-08 22:00:00 | 61 |\n", "| 2017-02-10 03:00:00 | 61 |\n", "| 2017-02-11 03:00:00 | 61 |\n", "| 2017-02-12 03:00:00 | 61 |\n", "| 2017-02-13 03:00:00 | 61 |\n", "| 2017-02-14 03:00:00 | 61 |\n", "| 2017-02-15 03:00:00 | 61 |\n", "| 2017-02-16 03:00:00 | 61 |\n", "| 2017-02-17 03:00:00 | 61 |\n", "| 2017-02-18 03:00:00 | 61 |\n", "| 2017-02-19 03:00:00 | 61 |\n", "| 2017-02-20 03:00:00 | 61 |\n", "| 2017-02-21 03:00:00 | 60 |\n", "| 2017-02-22 03:00:00 | 60 |\n", "| 2017-02-23 03:00:00 | 59 |\n", "| 2017-02-24 03:00:00 | 59 |\n", "| 2017-02-25 03:00:00 | 59 |\n", "| 2017-02-26 03:00:00 | 59 |\n", "| 2017-02-27 03:00:00 | 59 |\n", "| 2017-02-28 03:00:00 | 57 |\n", "| 2017-03-12 03:00:00 | 54 |\n", "| 2017-03-13 03:00:00 | 53 |\n", "| 2017-03-19 03:00:00 | 52 |\n", "| 2017-03-27 03:00:00 | 50 |\n", "| 2017-04-03 03:00:00 | 49 |\n", "| 2017-04-17 03:00:00 | 46 |\n", "| 2017-04-23 03:00:00 | 45 |\n", "| 2017-04-28 03:00:00 | 44 |\n", "| 2017-05-01 03:00:00 | 43 |\n", "| 2017-05-21 03:00:00 | 36 |\n", "| 2017-05-25 03:00:00 | 35 |\n", "| 2017-05-26 03:00:00 | 34 |\n", "| 2017-05-27 03:00:00 | 34 |\n", "| 2017-05-28 03:00:00 | 34 |\n", "| 2017-05-29 03:00:00 | 34 |\n", "+---------------------+-------+\n" ] } ], "source": [ "pretty_query(popularity_sql, params=dict(track_spyid='7GSuJlP3XN550RQD8t88mj'))" ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "collapsed": true }, "outputs": [], "source": [ "query_to_sheet(popularity_sql, de.add_worksheet('Alice Merton', 300, 30))" ] }, { "cell_type": "code", "execution_count": 116, "metadata": { "collapsed": true }, "outputs": [], "source": [ "playlists_sql = '''\n", "select \n", " first_seen::date \"First Seen\", \n", " last_seen::date \"Off List\", \n", " last_seen::date-first_seen::date as \"days on list\", \n", " first_position \"First Position\", \n", " last_position \"Last Position\",\n", " name \"Playlist\" \n", "from spy_playlist_track \n", "join spy_playlist on playlist_spyid = spy_playlist.spyid where track_spyid = :track_spyid\n", "order by first_seen\n", "'''" ] }, { "cell_type": "code", "execution_count": 78, "metadata": { "collapsed": true }, "outputs": [], "source": [ "query_to_sheet(playlists_sql, de.add_worksheet('Alice Merton3', 300, 30))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Soundcloud" ] }, { "cell_type": "code", "execution_count": 124, "metadata": { "collapsed": true }, "outputs": [], "source": [ "sc_find_artist_sql = '''select scid, name from sc_users where name like :name_like '''" ] }, { "cell_type": "code", "execution_count": 125, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----------+--------------+\n", "| scid | name |\n", "+----------+--------------+\n", "| 12765548 | Alice Merton |\n", "+----------+--------------+\n" ] } ], "source": [ "pretty_query(sc_find_artist_sql, params=dict(name_like='Alice Merton'))" ] }, { "cell_type": "code", "execution_count": 126, "metadata": { "collapsed": true }, "outputs": [], "source": [ "sc_tracks_sql = '''select scid, name from sc_tracks where artist_scid = :artist_scid '''" ] }, { "cell_type": "code", "execution_count": 127, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------+----------+\n", "| scid | name |\n", "+-----------+----------+\n", "| 295580498 | No Roots |\n", "+-----------+----------+\n" ] } ], "source": [ "pretty_query(sc_tracks_sql, params=dict(artist_scid='12765548'))" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+-----------------+-------------+----------------------------+----------------+\n", "| influencer_scid | artist_scid | seen_at | name |\n", "+-----------------+-------------+----------------------------+----------------+\n", "| 29399459 | 12765548 | 2016-11-08 01:00:08.818096 | Mattia Villa |\n", "| 3430403 | 12765548 | 2017-01-31 21:40:07.965930 | Sam Maroste |\n", "| 14114104 | 12765548 | 2017-06-07 22:33:31.166794 | Mirco Niemeier |\n", "+-----------------+-------------+----------------------------+----------------+\n" ] } ], "source": [ "pretty_query('''\n", "select \n", "sc_followings.*,\n", "sc_users.name\n", "from sc_followings join sc_users on influencer_scid = sc_users.scid where artist_scid = '12765548'\n", "''')" ] }, { "cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----------+-----------------+-----------------+------------+----------------------------+\n", "| track | influencer | influencer_scid | track_scid | seen_at |\n", "+----------+-----------------+-----------------+------------+----------------------------+\n", "| No Roots | New Music Talks | 110083093 | 295580498 | 2016-12-05 15:00:19.964230 |\n", "| No Roots | Pause Musicale | 9913758 | 295580498 | 2016-12-11 14:59:41.930706 |\n", "+----------+-----------------+-----------------+------------+----------------------------+\n" ] } ], "source": [ "pretty_query('''\n", "select \n", "st.name track, \n", "sc_users.name influencer,\n", "sc_likes.*\n", "from sc_tracks st join sc_likes on sc_likes.track_scid = st.scid \n", "join sc_users on sc_users.scid = sc_likes.influencer_scid \n", "where st.artist_scid = '12765548'\n", "''')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### San Holo" ] }, { "cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+------------------------+\n", "| spyid |\n", "+------------------------+\n", "| 0jNDKefhfSbLR9sFvcPLHo |\n", "+------------------------+\n" ] } ], "source": [ "pretty_query('''\n", "select spyid from spy_artists where name = 'San Holo'\n", "''')" ] }, { "cell_type": "code", "execution_count": 92, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+------------------------+--------------------------+----------------------------+--------------+\n", "| spyid | name | first_seen | Label |\n", "+------------------------+--------------------------+----------------------------+--------------+\n", "| 74Ru27B7Jx8mBt5MGvGLLv | Light | 2016-11-25 08:19:26.845269 | 2016 bitbird |\n", "| 6jq6rcOikCZAmjliAgAmfT | Light | 2017-02-10 03:00:31.093143 | None |\n", "| 2rkCYgvzyHp1AESIlJcqqY | Light - GOSLO Remix | 2017-04-22 03:00:05.578047 | None |\n", "| 2OEwpiyu1J0fwUtf7zc0Ou | Light - Loosid Remix | 2017-06-24 03:00:06.791492 | None |\n", "| 4eqlSNbFyPM8EuaqhcC39r | The Future | 2017-06-30 03:00:48.734875 | None |\n", "| 0TERWVCH2clPTfg0HCtsTc | Light - BeauDamian Remix | 2017-08-26 03:00:07.498055 | None |\n", "| 3R7Y4q7kECcZkT5LCn8QP5 | I Still See Your Face | 2017-09-15 03:00:05.304671 | None |\n", "+------------------------+--------------------------+----------------------------+--------------+\n" ] } ], "source": [ "pretty_query('''\n", "select spyid, name, first_seen, album_data->'copyrights'->0->'text' as \"Label\" from spy_tracks \n", "where primary_artist_spyid = '0jNDKefhfSbLR9sFvcPLHo'\n", "order by first_seen asc\n", "''')" ] }, { "cell_type": "code", "execution_count": 106, "metadata": { "collapsed": true }, "outputs": [], "source": [ "sh1 = de.add_worksheet('San Holo1', 400, 30)" ] }, { "cell_type": "code", "execution_count": 107, "metadata": { "collapsed": true }, "outputs": [], "source": [ "query_to_sheet(popularity_sql, sheet=sh1, params=dict(track_spyid='74Ru27B7Jx8mBt5MGvGLLv'))" ] }, { "cell_type": "code", "execution_count": 123, "metadata": { "collapsed": true }, "outputs": [], "source": [ "query_to_sheet(playlists_sql, sheet=sh1, start_col=4, params=dict(track_spyid='74Ru27B7Jx8mBt5MGvGLLv'))" ] }, { "cell_type": "code", "execution_count": 131, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+------+------+\n", "| scid | name |\n", "+------+------+\n", "+------+------+\n" ] } ], "source": [ "pretty_query(sc_find_artist_sql, params=dict(name_like='sanholobeats'))" ] }, { "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 }