Connect to an ODM2 database and create a variable

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

In [22]:
import sys
import os
import getpass
from IPython.display import display, HTML
import ipywidgets as widgets

2) Next load ODM2API database connection class and models

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

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

In [23]:
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.models import *
  

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

In [27]:
#print("Enter your ODM2 username") 
container = widgets.Box() # would be nice If I could get a container to hold the 
# user name and password prompt, getpass doesn't seem to play well with the other 
# widgets though
username_text = widgets.Text(
    value='', placeholder='Enter username',
    description='', disabled=False)
username_output_text = widgets.Text(
    value='', placeholder='Enter username',
    description='Username',disabled=False)
database_address_text = widgets.Text(
    value='', placeholder='Enter database address',
    description='',disabled=False)
database_address_output_text = widgets.Text(
    value='',placeholder='Enter database address',
    description='database address',disabled=False)
database_text = widgets.Text(
    value='', placeholder='Enter database name',
    description='', disabled=False)
database_output_text = widgets.Text(
    value='', placeholder='Enter database name',
    description='database name', disabled=False)
def bind_username_to_output(sender):
    username_output_text.value = username_text.value
def bind_database_address_to_output(sender):
    database_address_output_text.value = database_address_text.value
def bind_database_to_output(sender):
    database_output_text.value = database_text.value     
    
def login(sender):
    #print('Database address : %s, Username: %s, database name: %s' % (
    #    database_address_text.value, username_text.value, database_text.value))
    container.close()    
    
username_text.on_submit(bind_username_to_output)
login_btn = widgets.Button(description="Login")
login_btn.on_click(login)
container.children = [username_text,database_address_text, database_text, login_btn]
container
#username_text

4) Now enter the password for the user.

In [28]:
print("enter your password: ")
p = getpass.getpass()
enter your password: 
········

5) Now we will use the information entered to establish a database connection.

In [29]:
#createConnection(self, engine, address, db=None, user=None, password=None, dbtype = 2.0, echo=False)
session_factory = dbconnection.createConnection('postgresql', database_address_text.value, database_text.value, 
                                                username_text.value, p)  
DBSession = session_factory.getSession()

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


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

In [33]:
variable = Variables(VariableTypeCV='Chemistry', VariableCode = 'Tl-particulate', VariableNameCV='Thalium, particulate',
                    VariableDefinition='particulate thallium quantified by ICP MS', SpeciationCV= 'Tl', NoDataValue=-6999)
print(variable)
print(variable.VariableCode)
DBSession.add(variable)
DBSession.commit()
print("the ID value for our new variable")
print(variable.VariableID)
variable_id = variable.VariableID
<Variables('None', 'Tl-particulate', 'Thalium, particulate')>
Tl-particulate
the ID value for our new variable
106

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.

In [36]:
retreived_variable = DBSession.query(Variables).get(variable_id)
print(retreived_variable)
DBSession.delete(retreived_variable)
DBSession.commit()
<Variables('106', 'Tl-particulate', 'Thalium, particulate')>
In [ ]: