Cleanup source metadata.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This script can be thrown away. It accounted for the fact that I started adding more source metadata to the SGCN items. The current processing code for all source items takes care of this. This was just a cleanup step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import requests,configparser\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Get API keys and any other config details from a file that is external to the code.\n",
    "config = configparser.RawConfigParser()\n",
    "config.read_file(open(r'../config/stuff.py'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Build base URL with API key using input from the external config.\n",
    "def getBaseURL():\n",
    "    gc2APIKey = config.get('apiKeys','apiKey_GC2_BCB').replace('\"','')\n",
    "    apiBaseURL = \"https://gc2.mapcentia.com/api/v1/sql/bcb?key=\"+gc2APIKey\n",
    "    return apiBaseURL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "blanks = requests.get(getBaseURL()+\"&q=SELECT DISTINCT sourceid FROM sgcn.sgcn WHERE sourcefileurl IS NULL\").json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'success': True, '_execution_time': 0.074, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 404}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 773}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 346}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 538}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 289}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 268}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 211}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 115}\n",
      "{'success': True, '_execution_time': 0.111, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 336}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 614}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 274}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 210}\n",
      "{'success': True, '_execution_time': 0.076, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 925}\n",
      "{'success': True, '_execution_time': 0.071, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 240}\n",
      "{'success': True, '_execution_time': 0.067, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 52}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 530}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 292}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 475}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 974}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 276}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 610}\n",
      "{'success': True, '_execution_time': 0.108, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 141}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 517}\n",
      "{'success': True, '_execution_time': 0.116, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 557}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 839}\n",
      "{'success': True, '_execution_time': 0.071, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 208}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 405}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 213}\n",
      "{'success': True, '_execution_time': 0.075, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 1000}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 364}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 417}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 90}\n",
      "{'success': True, '_execution_time': 0.107, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 301}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 570}\n",
      "{'success': True, '_execution_time': 0.108, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 502}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 366}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 602}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 297}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 310}\n",
      "{'success': True, '_execution_time': 0.071, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 664}\n",
      "{'success': True, '_execution_time': 0.1, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 459}\n",
      "{'success': True, '_execution_time': 0.109, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 121}\n",
      "{'success': True, '_execution_time': 0.074, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 1212}\n",
      "{'success': True, '_execution_time': 0.105, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 458}\n",
      "{'success': True, '_execution_time': 0.083, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 152}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 806}\n",
      "{'success': True, '_execution_time': 0.109, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 468}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 286}\n",
      "{'success': True, '_execution_time': 0.067, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 100}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 378}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 311}\n",
      "{'success': True, '_execution_time': 0.113, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 1307}\n",
      "{'success': True, '_execution_time': 0.068, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 101}\n",
      "{'success': True, '_execution_time': 0.074, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 883}\n",
      "{'success': True, '_execution_time': 0.07, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 316}\n",
      "{'success': True, '_execution_time': 0.072, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 377}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 405}\n",
      "{'success': True, '_execution_time': 0.071, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 285}\n",
      "{'success': True, '_execution_time': 0.097, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 47}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 196}\n",
      "{'success': True, '_execution_time': 0.073, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 1036}\n",
      "{'success': True, '_execution_time': 0.069, 'auth_check': {'success': True, 'auth_level': None, 'session': None}, 'affected_rows': 256}\n"
     ]
    }
   ],
   "source": [
    "for feature in blanks[\"features\"]:\n",
    "    thisItemSB = requests.get(feature[\"properties\"][\"sourceid\"]+\"?format=json&fields=files\").json()\n",
    "    thisItemUpdate = {}\n",
    "    thisItemUpdate[\"sourceid\"] = feature[\"properties\"][\"sourceid\"]\n",
    "    for file in thisItemSB[\"files\"]:\n",
    "        if file[\"title\"] == \"Process ready version of original file\":\n",
    "            thisItemUpdate[\"sourcefilename\"] = file[\"name\"]\n",
    "            thisItemUpdate[\"sourcefileurl\"] = file[\"url\"]\n",
    "            break\n",
    "    print (requests.get(getBaseURL()+\"&q=UPDATE sgcn.sgcn SET sourcefilename='\"+thisItemUpdate[\"sourcefilename\"]+\"',sourcefileurl='\"+thisItemUpdate[\"sourcefileurl\"]+\"' WHERE sourceid='\"+thisItemUpdate[\"sourceid\"]+\"'\").json())"
   ]
  },
  {
   "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}