Connect to an ODM2 database and create a variable.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1> Connect to an ODM2 database and create a variable</h1>\n",
    "<p> 1) This first block of code sets up the python environment by loading standard python classes as well as IPython display classes and widgets </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import getpass\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>\n",
    "<p>&emsp;   a) each model is mapped to an ODM2 table, ODM2API is available on github and the ODM2API models are here:</p>\n",
    "<p>&emsp; https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from odm2api.ODMconnection import dbconnection\n",
    "from odm2api.ODM2.models import *\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "<p>3) Now create text widgets and a container for database connection info.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "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\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>4) Now enter the password for the user. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "enter your password: \n",
      "········\n"
     ]
    }
   ],
   "source": [
    "print(\"enter your password: \")\n",
    "p = getpass.getpass()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5) Now we will use the information entered to establish a database connection. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#createConnection(self, engine, address, db=None, user=None, password=None, dbtype = 2.0, echo=False)\n",
    "session_factory = dbconnection.createConnection('postgresql', database_address_text.value, database_text.value, \n",
    "                                                username_text.value, p)  \n",
    "DBSession = session_factory.getSession()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1> 6) Here we create a new ODM2 variable, to do this we fill in the variables fields and save it to the database. </h1><br>\n",
    "&emsp;&emsp; a) In order to determine how to create a variable we will want to look at the fields that need to be populated <br>\n",
    "&emsp;&emsp;&emsp; i- Those fields can be found in the variable model here <br> \n",
    "&emsp;&emsp;&emsp; ii-https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L392 <br>\n",
    "&emsp;&emsp; b) The first field, VariableID is the primary key which is automatically populated by the database. You <br>\n",
    "&emsp;&emsp; shouldn't need to worry about fields ending in ID. <br>\n",
    "&emsp;&emsp; c) As a result, we first need to specify the variable type. Variable type is part of the ODM2 controlled <br>\n",
    "&emsp;&emsp; vocabulary. ODM2 Controlled vocabularies can be found here: http://vocabulary.odm2.org/ <br>\n",
    "&emsp;&emsp; c) The specific controlled vocabulary for variable type is here: http://vocabulary.odm2.org/variabletype/ <br>\n",
    "&emsp;&emsp; d) Next, we specify the variable code which can be anything we like up to 50 characters. <br>\n",
    "&emsp;&emsp; e) Now, we specify the the variable name which is also from a controlled vocabulary <br>\n",
    "&emsp;&emsp; http://vocabulary.odm2.org/variablename/<br>\n",
    "&emsp;&emsp; f) Next, we specify the variable definition which we can describe however we like, up to 500 characters<br>\n",
    "&emsp;&emsp; g) Now, we can define the speciation but it can also be left blank this is also a controlled vocabulary. <br>\n",
    "&emsp;&emsp; http://vocabulary.odm2.org/speciation/ <br>\n",
    "&emsp;&emsp; h) Finally, we set the NoDataValue field, some analysis may be done for certain specimens but not others, <br>\n",
    "&emsp;&emsp; in which case it maybe desirable to indicate that by recording a no data value. <br>\n",
    "7) now print out the variable, it's variable code and save it to the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Variables('None', 'Tl-particulate', 'Thalium, particulate')>\n",
      "Tl-particulate\n",
      "the ID value for our new variable\n",
      "106\n"
     ]
    }
   ],
   "source": [
    "\n",
    "variable = Variables(VariableTypeCV='Chemistry', VariableCode = 'Tl-particulate', VariableNameCV='Thalium, particulate',\n",
    "                    VariableDefinition='particulate thallium quantified by ICP MS', SpeciationCV= 'Tl', NoDataValue=-6999)\n",
    "print(variable)\n",
    "print(variable.VariableCode)\n",
    "DBSession.add(variable)\n",
    "DBSession.commit()\n",
    "print(\"the ID value for our new variable\")\n",
    "print(variable.VariableID)\n",
    "variable_id = variable.VariableID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p>8) To finish things off let's retrieve the new variable from the database, display it, and delete it so this script can be run again. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Variables('106', 'Tl-particulate', 'Thalium, particulate')>\n"
     ]
    }
   ],
   "source": [
    "retreived_variable = DBSession.query(Variables).get(variable_id)\n",
    "print(retreived_variable)\n",
    "DBSession.delete(retreived_variable)\n",
    "DBSession.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}