db-insert-into-sk-one-off-extensions-only-access-type-column.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*This notebook gets reverse-dns entries from `sk_one_off_extensions` table, and searches the sample words to identify dsl and cable deployments and create a temporary file `temp.sql` with UPDATE queries that can be run on `sk_one_off_extensions` table*\n",
    "- - - "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import requests\n",
    "import ipaddress\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "DB_LOCATION = 'lastmile.db'\n",
    "SK_ONE_OFF_EXTENSIONS = 'sk_one_off_extensions'\n",
    "SK_ASN = 'sk_asn'\n",
    "METADATA_SNAPSHOT_EXTENSIONS = 'metadata_snapshot_extensions'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d_fav_rdns = {\n",
    "\n",
    "     'CABLE'     :     ['hsd1', 'cable', 'hfc']\n",
    "   , 'DSL'       :     ['adsl', 'dsl', 'dslb', 'xdsl', 'luxdsl']\n",
    "   , 'FIBRE'     :     ['fios', 'ftth', 'fiber7', 'fttb']    \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "con = sqlite3.connect(DB_LOCATION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''SELECT    asn_holder_name\n",
    "           FROM      %s\n",
    "        '''%(SK_ASN)\n",
    "\n",
    "df1 = pd.read_sql(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>asn_holder_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>COGENT-174 - Cogent Communications,US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CW Cable and Wireless Worldwide plc,GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DEMON-INTERNET Now maintained by Cable &amp; Wirel...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BT-UK-AS BT Public Internet Service,GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NTL Virgin Media Limited,GB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     asn_holder_name\n",
       "0              COGENT-174 - Cogent Communications,US\n",
       "1             CW Cable and Wireless Worldwide plc,GB\n",
       "2  DEMON-INTERNET Now maintained by Cable & Wirel...\n",
       "3             BT-UK-AS BT Public Internet Service,GB\n",
       "4                        NTL Virgin Media Limited,GB"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "holder_names = []\n",
    "for value in df1.values: holder_names.append(value[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "query = '''SELECT    ai.probeid, a.asn_access_type, oe.reverse_dns, '' as access_technology\n",
    "           FROM      %s as oe, %s as ai, %s as a\n",
    "           WHERE     oe.probeid = ai.probeid\n",
    "           AND       ai.asn =  a.asn\n",
    "        '''%(SK_ONE_OFF_EXTENSIONS, METADATA_SNAPSHOT_EXTENSIONS, SK_ASN)\n",
    "\n",
    "df = pd.read_sql(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>probeid</th>\n",
       "      <th>asn_access_type</th>\n",
       "      <th>reverse_dns</th>\n",
       "      <th>access_technology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14190</td>\n",
       "      <td>None</td>\n",
       "      <td>178-78-87-102.static.kc.net.uk</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14266</td>\n",
       "      <td>None</td>\n",
       "      <td>95.145.135.27</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14851</td>\n",
       "      <td>DSL</td>\n",
       "      <td>host86-153-18-233.range86-153.btcentralplus.com</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14880</td>\n",
       "      <td>CABLE</td>\n",
       "      <td>97e619d1.skybroadband.com</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14905</td>\n",
       "      <td>DSL</td>\n",
       "      <td>host31-53-231-115.range31-53.btcentralplus.com</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   probeid asn_access_type                                      reverse_dns  \\\n",
       "0    14190            None                   178-78-87-102.static.kc.net.uk   \n",
       "1    14266            None                                    95.145.135.27   \n",
       "2    14851             DSL  host86-153-18-233.range86-153.btcentralplus.com   \n",
       "3    14880           CABLE                        97e619d1.skybroadband.com   \n",
       "4    14905             DSL   host31-53-231-115.range31-53.btcentralplus.com   \n",
       "\n",
       "  access_technology  \n",
       "0                    \n",
       "1                    \n",
       "2                    \n",
       "3                    \n",
       "4                    "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_set_of_tlds():\n",
    "    try: res = requests.get('http://www.internic.net/domain/root.zone')\n",
    "    except Exception as e: print(e)\n",
    "    else: data = res.text\n",
    "        \n",
    "    tld_list = []\n",
    "    for line in data.split('\\n'):\n",
    "        try: tld = line.split()[0]\n",
    "        except Exception as e: print('.')\n",
    "        else:\n",
    "            tld = tld.rstrip('.')\n",
    "            tld_list.append(tld)\n",
    "\n",
    "    return set(tld_list)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ".\n"
     ]
    }
   ],
   "source": [
    "tlds = create_set_of_tlds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def split_rdns_by_word(rdns):\n",
    "    import re\n",
    "    rdns_list = re.split('\\.|-',rdns)\n",
    "    return rdns_list\n",
    "df['rdns_list'] = df['reverse_dns'].apply(split_rdns_by_word)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "d = {}\n",
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']\n",
    "    word_list = row['rdns_list']\n",
    "\n",
    "    for word in word_list: \n",
    "        if word in tlds: continue\n",
    "        try: int(word)\n",
    "        except:\n",
    "            try: d[word].append(probeid)\n",
    "            except KeyError as e: d[word] = []; d[word].append(probeid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import OrderedDict\n",
    "ordered_d = OrderedDict(sorted(d.items(), key=lambda x: len(x[1]), reverse=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "btcentralplus: 529\n",
      "virginm: 422\n",
      "cable: 417\n",
      "host86: 314\n",
      "range86: 314\n",
      "skybroadband: 176\n",
      "as13285: 161\n",
      "dyn: 133\n"
     ]
    }
   ],
   "source": [
    "for k, v in ordered_d.items():\n",
    "    if len(v) >= 100: print('%s: %d'%(k, len(v)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']; rdns_list = row['rdns_list']\n",
    "    for k,v in d_fav_rdns.items():\n",
    "        v_set = set(v); rdns_set = set(rdns_list)     \n",
    "        intersect = v_set.intersection(rdns_set)\n",
    "        if len(intersect) > 0: \n",
    "            df.ix[i, 'access_technology'] = k\n",
    "            break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>probeid</th>\n",
       "      <th>asn_access_type</th>\n",
       "      <th>reverse_dns</th>\n",
       "      <th>access_technology</th>\n",
       "      <th>rdns_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14190</td>\n",
       "      <td>None</td>\n",
       "      <td>178-78-87-102.static.kc.net.uk</td>\n",
       "      <td></td>\n",
       "      <td>[178, 78, 87, 102, static, kc, net, uk]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14266</td>\n",
       "      <td>None</td>\n",
       "      <td>95.145.135.27</td>\n",
       "      <td></td>\n",
       "      <td>[95, 145, 135, 27]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14851</td>\n",
       "      <td>DSL</td>\n",
       "      <td>host86-153-18-233.range86-153.btcentralplus.com</td>\n",
       "      <td></td>\n",
       "      <td>[host86, 153, 18, 233, range86, 153, btcentral...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14880</td>\n",
       "      <td>CABLE</td>\n",
       "      <td>97e619d1.skybroadband.com</td>\n",
       "      <td></td>\n",
       "      <td>[97e619d1, skybroadband, com]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14905</td>\n",
       "      <td>DSL</td>\n",
       "      <td>host31-53-231-115.range31-53.btcentralplus.com</td>\n",
       "      <td></td>\n",
       "      <td>[host31, 53, 231, 115, range31, 53, btcentralp...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   probeid asn_access_type                                      reverse_dns  \\\n",
       "0    14190            None                   178-78-87-102.static.kc.net.uk   \n",
       "1    14266            None                                    95.145.135.27   \n",
       "2    14851             DSL  host86-153-18-233.range86-153.btcentralplus.com   \n",
       "3    14880           CABLE                        97e619d1.skybroadband.com   \n",
       "4    14905             DSL   host31-53-231-115.range31-53.btcentralplus.com   \n",
       "\n",
       "  access_technology                                          rdns_list  \n",
       "0                              [178, 78, 87, 102, static, kc, net, uk]  \n",
       "1                                                   [95, 145, 135, 27]  \n",
       "2                    [host86, 153, 18, 233, range86, 153, btcentral...  \n",
       "3                                        [97e619d1, skybroadband, com]  \n",
       "4                    [host31, 53, 231, 115, range31, 53, btcentralp...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del df['asn_access_type']\n",
    "del df['reverse_dns']\n",
    "del df['rdns_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>probeid</th>\n",
       "      <th>access_technology</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2204</th>\n",
       "      <td>584164</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2205</th>\n",
       "      <td>584166</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2206</th>\n",
       "      <td>589646</td>\n",
       "      <td>CABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2207</th>\n",
       "      <td>601700</td>\n",
       "      <td>CABLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2208</th>\n",
       "      <td>608580</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      probeid access_technology\n",
       "2204   584164                  \n",
       "2205   584166                  \n",
       "2206   589646             CABLE\n",
       "2207   601700             CABLE\n",
       "2208   608580                  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fsock = open('temp.sql', 'w')\n",
    "fsock.write('BEGIN TRANSACTION;\\n')\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    probeid = row['probeid']; at = row['access_technology']\n",
    "    if at == '': continue\n",
    "    query = 'UPDATE %s SET access_type_technology = \"%s\" WHERE probeid = %d;'%(SK_ONE_OFF_EXTENSIONS, at, probeid)\n",
    "    fsock.write('%s\\n'%query)\n",
    "    \n",
    "    \n",
    "fsock.write('END TRANSACTION;\\n')\n",
    "fsock.close()"
   ]
  },
  {
   "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.4.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}