Using ODM2API and SQLAlchemy to load time series data from a file.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<H1>Connecting to a remote database using ODM2API and adding time series values using SQLAlchemy</H1>\n",
    "<p> 1) load python classes IPython display classes and widgets </p>"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import getpass\n",
    "import csv\n",
    "from IPython.display import display, HTML\n",
    "import ipywidgets as widgets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>2) Next load ODM2api database connection class and models</p>"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "outputs": [],
   "source": [
    "from odm2api.ODMconnection import dbconnection\n",
    "from odm2api.ODM2.models import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>3) Now create text widgets and a container for database connection info.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(\"Enter your ODM2 username\") \n",
    "container = widgets.Box() # would be nice If I could get a container to hold the \n",
    "# user name and password prompt, getpass doesn't seem to play well with the other \n",
    "# widgets though\n",
    "username_text = widgets.Text(\n",
    "    value='', placeholder='Enter username',\n",
    "    description='', disabled=False)\n",
    "username_output_text = widgets.Text(\n",
    "    value='', placeholder='Enter username',\n",
    "    description='Username',disabled=False)\n",
    "database_address_text = widgets.Text(\n",
    "    value='', placeholder='Enter database address',\n",
    "    description='',disabled=False)\n",
    "database_address_output_text = widgets.Text(\n",
    "    value='',placeholder='Enter database address',\n",
    "    description='database address',disabled=False)\n",
    "database_text = widgets.Text(\n",
    "    value='', placeholder='Enter database name',\n",
    "    description='', disabled=False)\n",
    "database_output_text = widgets.Text(\n",
    "    value='', placeholder='Enter database name',\n",
    "    description='database name', disabled=False)\n",
    "def bind_username_to_output(sender):\n",
    "    username_output_text.value = username_text.value\n",
    "def bind_database_address_to_output(sender):\n",
    "    database_address_output_text.value = database_address_text.value\n",
    "def bind_database_to_output(sender):\n",
    "    database_output_text.value = database_text.value     \n",
    "    \n",
    "def login(sender):\n",
    "    #print('Database address : %s, Username: %s, database name: %s' % (\n",
    "    #    database_address_text.value, username_text.value, database_text.value))\n",
    "    container.close()    \n",
    "    \n",
    "username_text.on_submit(bind_username_to_output)\n",
    "login_btn = widgets.Button(description=\"Login\")\n",
    "login_btn.on_click(login)\n",
    "container.children = [username_text,database_address_text, database_text, login_btn]\n",
    "container\n",
    "#username_text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>4) Now prompt the user for their password. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "enter your password: \n",
      "········\n"
     ]
    }
   ],
   "source": [
    "print(\"enter your password: \")\n",
    "p = getpass.getpass()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p> 5) Now create a connection to the database </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "session_factory = dbconnection.createConnection('postgresql', database_address_text.value, database_text.value, \n",
    "                                                username_text.value, p)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p> 6) Create a database session and use SQLALchemy to pull some initial records.</p>\n",
    "<p>    a) find the feature actions for the sampling feature.</p>\n",
    "<p>    b) narrow the feature actions based on the method of the action.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Results for Pressure transducer at depth\n",
      "<Results('16644', 'ce603ba9-d03e-4a08-825d-a1a82e71bf7a', 'Time series coverage', '1', '1')>\n",
      "<Results('16650', 'e1a696ff-97fb-485a-ac30-b6418bd78651', 'Time series coverage', '1', '1')>\n",
      "<Results('16651', '44c1848e-5de0-4b86-ab6d-27d62efb996d', 'Time series coverage', '1', '1')>\n",
      "Results for Pressure transducer at surface\n",
      "<Results('16652', 'e890bd26-5321-43b5-9b40-ebd9d166f9c4', 'Time series coverage', '1', '1')>\n",
      "<Results('16653', '94fdd54f-ffd7-4384-b691-8bbe6f369722', 'Time series coverage', '1', '1')>\n",
      "<Results('16645', '0e95dfe0-0979-4993-8db8-3985e341bc41', 'Time series coverage', '1', '1')>\n",
      "<Results('16668', 'd968d77c-f13c-478e-b5db-759f2a223767', 'Time series coverage', '3', '1')>\n"
     ]
    }
   ],
   "source": [
    "DBSession = session_factory.getSession()\n",
    "#var = DBSession.query(Results).get(16650) #LGW2B well temperature\n",
    "#print(var)\n",
    "#find the feature actions for \n",
    "feature_actions = DBSession.query(FeatureActions).join(SamplingFeatures).filter(\n",
    "    SamplingFeatures.SamplingFeatureName.ilike('%LGW2B%'))\n",
    "\n",
    "feature_action_depth = feature_actions.join(Actions).join(Methods).filter(Methods.MethodName.ilike('%depth%')).one()\n",
    "feature_action_surface = feature_actions.join(Actions).join(Methods).filter(Methods.MethodName.ilike('%surface%')).one()\n",
    "\n",
    "depth_results = DBSession.query(Results).join(FeatureActions).filter_by(\n",
    "    FeatureActionID=feature_action_depth.FeatureActionID)\n",
    "\n",
    "surface_results = DBSession.query(Results).join(FeatureActions).filter_by(\n",
    "    FeatureActionID=feature_action_surface.FeatureActionID)\n",
    "\n",
    "surface_result_id = []\n",
    "depth_resultid=[]\n",
    "print(\"Results for Pressure transducer at depth\")\n",
    "for depth_result in depth_results:\n",
    "    print(depth_result)\n",
    "    depth_resultid.append(depth_result.ResultID)\n",
    "    \n",
    "print(\"Results for Pressure transducer at surface\")\n",
    "for surface_result in surface_results:\n",
    "    print(surface_result)\n",
    "    surface_result_id.append(surface_result.ResultID)\n",
    "#sf = DBSession.query(FeatureActions).get(774)\n",
    "#print(feature_action)\n",
    "\n",
    "#results = DBSession.query(Results).join(FeatureActions).filter_by(FeatureActionID=featureaction.FeatureActionID)\n",
    "#for result in results:\n",
    "#    print result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p> 7) Open a local file with time series data we want to load into the database. <p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Plot Title: LGW2BWell', 'Abs Pres kPa (LGR S/N: 10434423, SEN S/N: 10434423)', 'Temp C (LGR S/N: 10434423 SEN S/N: 10434423)', 'Abs Pres kPa (LGR S/N: 10434421 SEN S/N: 10434421)', '']\n",
      "['Date Time GMT-04:00', 'Abs Pres', 'Temp C', 'Abs Pres Barom- psi', 'Depth-meters']\n",
      "['8/13/2016 0:00', '112.712', '20.901', '93.381', '1.971267685']\n",
      "['8/13/2016 0:15', '112.728', '20.901', '93.381', '1.972899276']\n",
      "['8/13/2016 0:30', '112.712', '20.901', '93.365', '1.972899276']\n"
     ]
    }
   ],
   "source": [
    "infile = open('./data/LGW2B/LGW2BWell_8-13-2016to8-13-2016.csv', 'rt')\n",
    "reader = csv.reader(infile)\n",
    "i=0\n",
    "\n",
    "for line in reader:\n",
    "    if i<5:\n",
    "        i+=1\n",
    "        print(line)\n",
    "    else:\n",
    "        break\n",
    "infile.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "8) check if the value are already in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Results('16652', 'e890bd26-5321-43b5-9b40-ebd9d166f9c4', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "0\n",
      "<Results('16653', '94fdd54f-ffd7-4384-b691-8bbe6f369722', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "0\n",
      "<Results('16645', '0e95dfe0-0979-4993-8db8-3985e341bc41', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "0\n",
      "<Results('16668', 'd968d77c-f13c-478e-b5db-759f2a223767', 'Time series coverage', '3', '1')>\n",
      "values for this result:\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "#valueids = []\n",
    "for surface_result in surface_results:\n",
    "    resultid = surface_result.ResultID\n",
    "    print(surface_result)\n",
    "    time_series_values = DBSession.query(TimeSeriesResultValues).filter(\n",
    "        TimeSeriesResultValues.ValueDateTime >= '8/13/2016 0:00').join(TimeSeriesResults).filter_by(\n",
    "        ResultID=surface_result.ResultID)\n",
    "    print(\"values for this result:\")\n",
    "    print(time_series_values.count())\n",
    "    for time_series_value in time_series_values:\n",
    "        print(time_series_value.ValueID)\n",
    "        #valueids.append(time_series_value.ValueID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p> 9) Using SQLAlchemy load some of the new data records. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "header\n",
      "['Plot Title: LGW2BWell', 'Abs Pres kPa (LGR S/N: 10434423, SEN S/N: 10434423)', 'Temp C (LGR S/N: 10434423 SEN S/N: 10434423)', 'Abs Pres kPa (LGR S/N: 10434421 SEN S/N: 10434421)', '']\n",
      "header\n",
      "['Date Time GMT-04:00', 'Abs Pres', 'Temp C', 'Abs Pres Barom- psi', 'Depth-meters']\n",
      "[11170223L, 11170224L, 11170225L, 11170226L, 11170227L, 11170228L, 11170229L, 11170230L]\n"
     ]
    }
   ],
   "source": [
    "#  well surface pressure\n",
    "\n",
    "#well_surface_pressure_time_series_result = DBSession.query(TimeSeriesResults).filter(\n",
    "#    TimeSeriesResults.ResultID.in_(surface_result_id))\n",
    "#for wsptsr in well_surface_pressure_time_series_result:\n",
    "#    print(wsptsr)\n",
    "infile = open('./data/LGW2B/LGW2BWell_8-13-2016to8-13-2016.csv', 'rt')\n",
    "reader = csv.reader(infile)\n",
    "i=0\n",
    "valueids = []\n",
    "for line in reader:\n",
    "    if i<10:\n",
    "        i+=1\n",
    "        if i<3:\n",
    "            print('header')\n",
    "            print(line)\n",
    "            continue\n",
    "    else:\n",
    "        break\n",
    "    for surface_result in surface_results:\n",
    "        variable_id = surface_result.VariableID\n",
    "        variable = DBSession.query(Variables).get(variable_id)\n",
    "        #print(variable)\n",
    "        #print(variable.VariableCode)\n",
    "        units_id = surface_result.UnitsID\n",
    "        units = DBSession.query(Units).get(units_id)\n",
    "        #print(units)\n",
    "        if variable.VariableCode == 'Abs Pres':\n",
    "            tsrv = TimeSeriesResultValues(ResultID=surface_result.ResultID, DataValue=line[1],\n",
    "                                          ValueDateTime=line[0], ValueDateTimeUTCOffset=4, \n",
    "                                          CensorCodeCV='Not censored',QualityCodeCV='Good',\n",
    "                                          TimeAggregationInterval=15, \n",
    "                                          TimeAggregationIntervalUnitsID=5)\n",
    "            DBSession.add(tsrv)\n",
    "            DBSession.commit()\n",
    "            valueids.append(tsrv.ValueID)\n",
    "infile.close()\n",
    "print(valueids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10) check that the new values are in the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Results('16652', 'e890bd26-5321-43b5-9b40-ebd9d166f9c4', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "0\n",
      "<Results('16653', '94fdd54f-ffd7-4384-b691-8bbe6f369722', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "0\n",
      "<Results('16645', '0e95dfe0-0979-4993-8db8-3985e341bc41', 'Time series coverage', '1', '1')>\n",
      "values for this result:\n",
      "8\n",
      "11170223\n",
      "11170224\n",
      "11170225\n",
      "11170226\n",
      "11170227\n",
      "11170228\n",
      "11170229\n",
      "11170230\n",
      "<Results('16668', 'd968d77c-f13c-478e-b5db-759f2a223767', 'Time series coverage', '3', '1')>\n",
      "values for this result:\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "for surface_result in surface_results:\n",
    "    resultid = surface_result.ResultID\n",
    "    print(surface_result)\n",
    "    time_series_values = DBSession.query(TimeSeriesResultValues).filter(\n",
    "        TimeSeriesResultValues.ValueDateTime >= '8/13/2016 0:00').join(TimeSeriesResults).filter_by(\n",
    "        ResultID=surface_result.ResultID)\n",
    "    print(\"values for this result:\")\n",
    "    print(time_series_values.count())\n",
    "    for time_series_value in time_series_values:\n",
    "        print(time_series_value.ValueID)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "11) Finally delete the records we just created so this script can be run again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_series_values = DBSession.query(TimeSeriesResultValues).filter(\n",
    "        TimeSeriesResultValues.ValueID.in_(valueids))\n",
    "for time_series_value in time_series_values:\n",
    "    DBSession.delete(time_series_value)\n",
    "DBSession.commit()"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "outputs": [],
   "source": [
    ""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2.0
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}