Error in DataLoggerFiles and DataLoggerFileColumns

[miguelcleon]

@lsetiawan

I'm trying to run a JupyterNotebook with ODM2 PythonAPI and I'm getting errors acessing DataLoggerFiles and DataLoggerFileColumns
When I run:

from odm2api.ODMconnection import dbconnection
import odm2api.ODM2.services.readService as odm2
from odm2api.ODM2.models import *
session_factory = dbconnection.createConnection('postgresql', 'x.x.x.x', 'db', 
                                                'user', 'pwd')
DBSession = session_factory.getSession()

template_dataloggerfile =  DBSession.query(DataLoggerFiles).filter_by(DataLoggerFileID=9).one()
print(template_dataloggerfile)

I get:

ProgrammingErrorTraceback (most recent call last)
<ipython-input-5-3dd04b4287d7> in <module>()
----> 1 template_dataloggerfile =  DBSession.query(DataLoggerFiles).filter_by(DataLoggerFileID=9).one()
      2 print(template_dataloggerfile)
      3 # dlfcs = DBSession.query(DataLoggerFileColumns).filter_by(DataLoggerFileID=9)
      4 # for dlfc in dlfcs:
      5 #    print(dlfc)

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in one(self)
   2812         """
   2813         try:
-> 2814             ret = self.one_or_none()
   2815         except orm_exc.MultipleResultsFound:
   2816             raise orm_exc.MultipleResultsFound(

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in one_or_none(self)
   2782 
   2783         """
-> 2784         ret = list(self)
   2785 
   2786         l = len(ret)

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __iter__(self)
   2853         if self._autoflush and not self._populate_existing:
   2854             self.session._autoflush()
-> 2855         return self._execute_and_instances(context)
   2856 
   2857     def __str__(self):

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   2876             close_with_result=True)
   2877 
-> 2878         result = conn.execute(querycontext.statement, self._params)
   2879         return loading.instances(querycontext.query, result, querycontext)
   2880 

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    943             raise exc.ObjectNotExecutableError(object)
    944         else:
--> 945             return meth(self, multiparams, params)
    946 
    947     def _execute_function(self, func, multiparams, params):

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in _execute_on_connection(self, connection, multiparams, params)
    261     def _execute_on_connection(self, connection, multiparams, params):
    262         if self.supports_execution:
--> 263             return connection._execute_clauseelement(self, multiparams, params)
    264         else:
    265             raise exc.ObjectNotExecutableError(self)

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
   1051             compiled_sql,
   1052             distilled_params,
-> 1053             compiled_sql, distilled_params
   1054         )
   1055         if self._has_events or self.engine._has_events:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1187                 parameters,
   1188                 cursor,
-> 1189                 context)
   1190 
   1191         if self._has_events or self.engine._has_events:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1392                 util.raise_from_cause(
   1393                     sqlalchemy_exception,
-> 1394                     exc_info
   1395                 )
   1396             else:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1180                         statement,
   1181                         parameters,
-> 1182                         context)
   1183         except BaseException as e:
   1184             self._handle_dbapi_exception(

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    468 
    469     def do_execute(self, cursor, statement, parameters, context=None):
--> 470         cursor.execute(statement, parameters)
    471 
    472     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.ProgrammingError) column dataloggerfiles.actionid does not exist
LINE 1: ...rfileid AS odm2_dataloggerfiles_dataloggerfileid, odm2.datal...
                                                             ^
 [SQL: 'SELECT odm2.dataloggerfiles.dataloggerfileid AS odm2_dataloggerfiles_dataloggerfileid, odm2.dataloggerfiles.actionid AS odm2_dataloggerfiles_actionid, odm2.dataloggerfiles.dataloggerfilename AS odm2_dataloggerfiles_dataloggerfilename, odm2.dataloggerfiles.dataloggeroutputfiledescription AS odm2_dataloggerfiles_dataloggeroutputfiledescription, odm2.dataloggerfiles.dataloggeroutputfilelink AS odm2_dataloggerfiles_dataloggeroutputfilelink \nFROM odm2.dataloggerfiles \nWHERE odm2.dataloggerfiles.dataloggerfileid = %(dataloggerfileid_1)s'] [parameters: {'dataloggerfileid_1': 9}]

When I then run:

dlfcs = DBSession.query(DataLoggerFileColumns).filter_by(DataLoggerFileID=9)
for dlfc in dlfcs:
    print(dlfc)

I get:

ProgrammingErrorTraceback (most recent call last)
<ipython-input-12-27db1134ef98> in <module>()
      2 #print(template_dataloggerfile)
      3 dlfcs = DBSession.query(DataLoggerFileColumns).filter_by(DataLoggerFileID=9)
----> 4 for dlfc in dlfcs:
      5     print(dlfc)

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __iter__(self)
   2853         if self._autoflush and not self._populate_existing:
   2854             self.session._autoflush()
-> 2855         return self._execute_and_instances(context)
   2856 
   2857     def __str__(self):

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   2876             close_with_result=True)
   2877 
-> 2878         result = conn.execute(querycontext.statement, self._params)
   2879         return loading.instances(querycontext.query, result, querycontext)
   2880 

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    943             raise exc.ObjectNotExecutableError(object)
    944         else:
--> 945             return meth(self, multiparams, params)
    946 
    947     def _execute_function(self, func, multiparams, params):

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in _execute_on_connection(self, connection, multiparams, params)
    261     def _execute_on_connection(self, connection, multiparams, params):
    262         if self.supports_execution:
--> 263             return connection._execute_clauseelement(self, multiparams, params)
    264         else:
    265             raise exc.ObjectNotExecutableError(self)

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
   1051             compiled_sql,
   1052             distilled_params,
-> 1053             compiled_sql, distilled_params
   1054         )
   1055         if self._has_events or self.engine._has_events:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1187                 parameters,
   1188                 cursor,
-> 1189                 context)
   1190 
   1191         if self._has_events or self.engine._has_events:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1392                 util.raise_from_cause(
   1393                     sqlalchemy_exception,
-> 1394                     exc_info
   1395                 )
   1396             else:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1180                         statement,
   1181                         parameters,
-> 1182                         context)
   1183         except BaseException as e:
   1184             self._handle_dbapi_exception(

/opt/conda/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    468 
    469     def do_execute(self, cursor, statement, parameters, context=None):
--> 470         cursor.execute(statement, parameters)
    471 
    472     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.ProgrammingError) column dataloggerfilecolumns.measurmentequation does not exist
LINE 1: ... AS odm2_dataloggerfilecolumns_columndescription, odm2.datal...
                                                             ^
HINT:  Perhaps you meant to reference the column "dataloggerfilecolumns.measurementequation".
 [SQL: 'SELECT odm2.dataloggerfilecolumns.dataloggerfilecolumnid AS odm2_dataloggerfilecolumns_dataloggerfilecolumnid, odm2.dataloggerfilecolumns.resultid AS odm2_dataloggerfilecolumns_resultid, odm2.dataloggerfilecolumns.dataloggerfileid AS odm2_dataloggerfilecolumns_dataloggerfileid, odm2.dataloggerfilecolumns.instrumentoutputvariableid AS odm2_dataloggerfilecolumns_instrumentoutputvariableid, odm2.dataloggerfilecolumns.columnlabel AS odm2_dataloggerfilecolumns_columnlabel, odm2.dataloggerfilecolumns.columndescription AS odm2_dataloggerfilecolumns_columndescription, odm2.dataloggerfilecolumns.measurmentequation AS odm2_dataloggerfilecolumns_measurmentequation, odm2.dataloggerfilecolumns.scaninterval AS odm2_dataloggerfilecolumns_scaninterval, odm2.dataloggerfilecolumns.scanintervalunitsid AS odm2_dataloggerfilecolumns_scanintervalunitsid, odm2.dataloggerfilecolumns.recordinginterval AS odm2_dataloggerfilecolumns_recordinginterval, odm2.dataloggerfilecolumns.recordingintervalunitsid AS odm2_dataloggerfilecolumns_recordingintervalunitsid, odm2.dataloggerfilecolumns.aggregationstatisticcv AS odm2_dataloggerfilecolumns_aggregationstatisticcv \nFROM odm2.dataloggerfilecolumns \nWHERE odm2.dataloggerfilecolumns.dataloggerfileid = %(dataloggerfileid_1)s'] [parameters: {'dataloggerfileid_1': 9}]

It looks like in the first one odm2.dataloggerfiles.actionid is not a column.

In the second one dataloggerfilecolumns.measurementequation is misspelled I'm trying to run this on jupyter.cuahsi.org it has odm2api v0.6.0.a0 that appears to be the latest release. I also tried setting up a new conda environment but I got the same result.

[lsetiawan]

I see that the first one, this is caused by Dataloggerfiles model, mapping to the wrong column within the database: https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L458.

For the second one, measurementequation is indeed misspelled: https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L514.

Thanks for catching these @miguelcleon. I won't be able to implement the fix probably until tomorrow.

[miguelcleon]

found two more problems with dataloggerfiles model
There is no column dataloggeroutputfiledescription instead it is dataloggerfiledescription
https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L460

There is no column dataloggeroutputfilelink instead it is dataloggerfilelink
https://github.com/ODM2/ODM2PythonAPI/blob/master/odm2api/ODM2/models.py#L461

[lsetiawan]

@miguelcleon Can you verify that this is all fixed now and close the issue? Thanks!

[miguelcleon]

yes, I installed from development and can confirm these issues are fixed.

[lsetiawan]

Great! Thanks for the fix 😄