{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "

Connect to an ODM2 database and create a variable

\n", "

1) This first block of code sets up the python environment by loading standard python classes as well as IPython display classes and widgets

" ] }, { "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": [ "

2) Next load ODM2API database connection class and models

\n", "

  a) each model is mapped to an ODM2 table, ODM2API is available on github and the ODM2API models are here:

\n", "

  https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py

" ] }, { "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": [ "

3) Now create text widgets and a container for database connection info.

" ] }, { "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": [ "

4) Now enter the password for the user.

" ] }, { "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": [ "

6) Here we create a new ODM2 variable, to do this we fill in the variables fields and save it to the database.


\n", "   a) In order to determine how to create a variable we will want to look at the fields that need to be populated
\n", "    i- Those fields can be found in the variable model here
\n", "    ii-https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L392
\n", "   b) The first field, VariableID is the primary key which is automatically populated by the database. You
\n", "   shouldn't need to worry about fields ending in ID.
\n", "   c) As a result, we first need to specify the variable type. Variable type is part of the ODM2 controlled
\n", "   vocabulary. ODM2 Controlled vocabularies can be found here: http://vocabulary.odm2.org/
\n", "   c) The specific controlled vocabulary for variable type is here: http://vocabulary.odm2.org/variabletype/
\n", "   d) Next, we specify the variable code which can be anything we like up to 50 characters.
\n", "   e) Now, we specify the the variable name which is also from a controlled vocabulary
\n", "   http://vocabulary.odm2.org/variablename/
\n", "   f) Next, we specify the variable definition which we can describe however we like, up to 500 characters
\n", "   g) Now, we can define the speciation but it can also be left blank this is also a controlled vocabulary.
\n", "   http://vocabulary.odm2.org/speciation/
\n", "   h) Finally, we set the NoDataValue field, some analysis may be done for certain specimens but not others,
\n", "   in which case it maybe desirable to indicate that by recording a no data value.
\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": [ "\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": [ "

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.

" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\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 }