1) This first block of code sets up the python environment by loading standard python classes as well as IPython display classes and widgets
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
from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.models import *
3) Now create text widgets and a container for database connection info.
#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.
print("enter your password: ")
p = getpass.getpass()
enter your password: ········
#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()
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.
retreived_variable = DBSession.query(Variables).get(variable_id)
print(retreived_variable)
DBSession.delete(retreived_variable)
DBSession.commit()
<Variables('106', 'Tl-particulate', 'Thalium, particulate')>