def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string, echo=False)
self.session = self.session_factory.get_session()
self.series_service = SeriesService(connection_string=self.connection_string)
engine = self.session_factory.engine
test_util.build_db(engine)
class TestSessionFactory:
def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string, echo=True)
def test_create_session_factory(self):
assert repr(self.session_factory) == "<SessionFactory('Engine(%s)')>" % self.connection_string
assert self.session_factory.Session != None
def test_get_session(self):
session = self.session_factory.get_session()
assert 'sqlalchemy.orm.session.Session' in repr(session)
class TestPandasMemoryDB:
"""
Test to Load up a series from a dataframe and load it into an in memory database
"""
def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string, echo=False)
self.session = self.session_factory.get_session()
self.series_service = SeriesService(connection_string=self.connection_string)
engine = self.session_factory.engine
test_util.build_db(engine)
def test_build_series(self):
dvs = 100
self.series = test_util.add_series_bulk_data(self.session, dvs_size=dvs)
assert self.series
assert len(self.series.data_values) == dvs
def __init__(self, series_id, connection=None, connection_string="", debug=False):
self._connection = connection
self._series_id = series_id
self._filter_from_selection = False
self._debug = debug
if (connection_string is not ""):
self._session_factory = SessionFactory(connection_string, debug)
self._series_service = SeriesService(connection_string, debug)
elif (factory is not None):
#TODO code has changed to no longer use a session factory, refactor so it is correct SR
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
logger.debug("Must have either a connection string or session factory")
#
# TODO throw an exception
self._edit_session = self._session_factory.get_session()
if self._connection == None:
series_service = SeriesService(connection_string, False)
series = series_service.get_series_by_id(series_id)
DataValues = [(dv.id, dv.data_value, dv.value_accuracy, dv.local_date_time, dv.utc_offset, dv.date_time_utc,
dv.site_id, dv.variable_id, dv.offset_value, dv.offset_type_id, dv.censor_code,
dv.qualifier_id, dv.method_id, dv.source_id, dv.sample_id, dv.derived_from_id,
dv.quality_control_level_id) for dv in series.data_values]
self._connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
tmpCursor = self._connection.cursor()
self.init_table(tmpCursor)
tmpCursor.executemany("INSERT INTO DataValues VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
self.reset_filter()
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
class SeriesService():
# Accepts a string for creating a SessionFactory, default uses odmdata/connection.cfg
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
def reset_session(self):
self._edit_session = self._session_factory.get_session() # Reset the session in order to prevent memory leaks
def get_db_version(self):
return self._edit_session.query(ODMVersion).first().version_number
#####################
#
# Get functions
#
#####################
# Site methods
def get_all_sites(self):
"""
:return: List[Sites]
"""
return self._edit_session.query(Site).order_by(Site.code).all()
def get_used_sites(self):
"""
Return a list of all sites that are being referenced in the Series Catalog Table
:return: List[Sites]
"""
try:
site_ids = [x[0] for x in self._edit_session.query(distinct(Series.site_id)).all()]
except:
site_ids = None
if not site_ids:
return None
Sites = []
for site_id in site_ids:
Sites.append(self._edit_session.query(Site).filter_by(id=site_id).first())
return Sites
def get_site_by_id(self, site_id):
"""
return a Site object that has an id=site_id
:param site_id: integer- the identification number of the site
:return: Sites
"""
try:
return self._edit_session.query(Site).filter_by(id=site_id).first()
except:
return None
# Variables methods
def get_used_variables(self):
"""
#get list of used variable ids
:return: List[Variables]
"""
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).all()]
except:
var_ids = None
if not var_ids:
return None
Variables = []
#create list of variables from the list of ids
for var_id in var_ids:
Variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return Variables
def get_all_variables(self):
"""
:return: List[Variables]
"""
return self._edit_session.query(Variable).all()
def get_variable_by_id(self, variable_id):
"""
:param variable_id: int
:return: Variables
"""
try:
return self._edit_session.query(Variable).filter_by(id=variable_id).first()
except:
return None
def get_variable_by_code(self, variable_code):
"""
:param variable_code: str
:return: Variables
"""
try:
return self._edit_session.query(Variable).filter_by(code=variable_code).first()
except:
return None
def get_variables_by_site_code(self, site_code): # covers NoDV, VarUnits, TimeUnits
"""
Finds all of variables at a site
:param site_code: str
:return: List[Variables]
"""
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).filter_by(
site_code=site_code).all()]
except:
var_ids = None
variables = []
for var_id in var_ids:
variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return variables
# Unit methods
def get_all_units(self):
"""
:return: List[Units]
"""
return self._edit_session.query(Unit).all()
def get_unit_by_name(self, unit_name):
"""
:param unit_name: str
:return: Units
"""
try:
return self._edit_session.query(Unit).filter_by(name=unit_name).first()
except:
return None
def get_unit_by_id(self, unit_id):
"""
:param unit_id: int
:return: Units
"""
try:
return self._edit_session.query(Unit).filter_by(id=unit_id).first()
except:
return None
def get_all_qualifiers(self):
"""
:return: List[Qualifiers]
"""
result = self._edit_session.query(Qualifier).order_by(Qualifier.code).all()
return result
def get_qualifiers_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.qualifier_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.qualifier_id != None).distinct().subquery()
return self._edit_session.query(Qualifier).join(subquery).distinct().all()
#QCL methods
def get_all_qcls(self):
return self._edit_session.query(QualityControlLevel).all()
def get_qcl_by_id(self, qcl_id):
try:
return self._edit_session.query(QualityControlLevel).filter_by(id=qcl_id).first()
except:
return None
def get_qcl_by_code(self, qcl_code):
try:
return self._edit_session.query(QualityControlLevel).filter_by(code=qcl_code).first()
except:
return None
# Method methods
def get_all_methods(self):
return self._edit_session.query(Method).all()
def get_method_by_id(self, method_id):
try:
result = self._edit_session.query(Method).filter_by(id=method_id).first()
except:
result = None
return result
def get_method_by_description(self, method_code):
try:
result = self._edit_session.query(Method).filter_by(description=method_code).first()
except:
result = None
return result
def get_offset_types_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.offset_type_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.offset_type_id != None).distinct().subquery()
return self._edit_session.query(OffsetType).join(subquery).distinct().all()
def get_samples_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.sample_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.sample_id != None).distinct().subquery()
return self._edit_session.query(Sample).join(subquery).distinct().all()
# Series Catalog methods
def get_all_series(self):
"""
Returns all series as a modelObject
:return: List[Series]
"""
#logger.debug("%s" % self._edit_session.query(Series).order_by(Series.id).all())
return self._edit_session.query(Series).order_by(Series.id).all()
def get_series_by_site(self , site_id):
"""
:param site_id: int
:return: List[Series]
"""
try:
selectedSeries = self._edit_session.query(Series).filter_by(site_id=site_id).order_by(Series.id).all()
return selectedSeries
except:
return None
def get_series_by_id(self, series_id):
"""
:param series_id: int
:return: Series
"""
try:
return self._edit_session.query(Series).filter_by(id=series_id).first()
except Exception as e:
print e
return None
def get_series_by_id_quint(self, site_id, var_id, method_id, source_id, qcl_id):
"""
:param site_id:
:param var_id:
:param method_id:
:param source_id:
:param qcl_id:
:return: Series
"""
try:
return self._edit_session.query(Series).filter_by(
site_id=site_id, variable_id=var_id, method_id=method_id,
source_id=source_id, quality_control_level_id=qcl_id).first()
except:
return None
def get_series_from_filter(self):
# Pass in probably a Series object, match it against the database
pass
#Data Value Methods
def get_values_by_series(self, series_id):
'''
:param series_id: Series id
:return: pandas dataframe
'''
series= self.get_series_by_id(series_id)
if series:
q = self._edit_session.query(DataValue).filter_by(
site_id=series.site_id,
variable_id=series.variable_id,
method_id=series.method_id,
source_id=series.source_id,
quality_control_level_id=series.quality_control_level_id)
query=q.statement.compile(dialect=self._session_factory.engine.dialect)
data= pd.read_sql_query(sql= query,
con = self._session_factory.engine,
params = query.params )
#return data.set_index(data['LocalDateTime'])
return data
else:
return None
def get_all_values_df(self):
"""
:return: Pandas DataFrame object
"""
q = self._edit_session.query(DataValue).order_by(DataValue.local_date_time)
query = q.statement.compile(dialect=self._session_factory.engine.dialect)
data = pd.read_sql_query(sql=query, con=self._session_factory.engine,
params=query.params)
columns = list(data)
columns.insert(0, columns.pop(columns.index("DataValue")))
columns.insert(1, columns.pop(columns.index("LocalDateTime")))
columns.insert(2, columns.pop(columns.index("QualifierID")))
data = data.ix[:, columns]
return data.set_index(data['LocalDateTime'])
def get_all_values_list(self):
"""
:return:
"""
result = self._edit_session.query(DataValue).order_by(DataValue.local_date_time).all()
return [x.list_repr() for x in result]
def get_all_values(self):
return self._edit_session.query(DataValue).order_by(DataValue.local_date_time).all()
@staticmethod
def calcSeason(row):
month = int(row["Month"])
if month in [1, 2, 3]:
return 1
elif month in[4, 5, 6]:
return 2
elif month in [7, 8, 9]:
return 3
elif month in [10, 11, 12]:
return 4
def get_all_plot_values(self):
"""
:return:
"""
q = self._edit_session.query(DataValue.data_value.label('DataValue'),
DataValue.local_date_time.label('LocalDateTime'),
DataValue.censor_code.label('CensorCode'),
func.strftime('%m', DataValue.local_date_time).label('Month'),
func.strftime('%Y', DataValue.local_date_time).label('Year')
#DataValue.local_date_time.strftime('%m'),
#DataValue.local_date_time.strftime('%Y'))
).order_by(DataValue.local_date_time)
query = q.statement.compile(dialect=self._session_factory.engine.dialect)
data = pd.read_sql_query(sql=query,
con=self._session_factory.engine,
params=query.params)
data["Season"] = data.apply(self.calcSeason, axis=1)
return data.set_index(data['LocalDateTime'])
def get_plot_values(self, seriesID, noDataValue, startDate = None, endDate = None ):
"""
:param seriesID:
:param noDataValue:
:param startDate:
:param endDate:
:return:
"""
series = self.get_series_by_id(seriesID)
DataValues = [
(dv.data_value, dv.local_date_time, dv.censor_code, dv.local_date_time.strftime('%m'),
dv.local_date_time.strftime('%Y'))
for dv in series.data_values
if dv.data_value != noDataValue if dv.local_date_time >= startDate if dv.local_date_time <= endDate
]
data = pd.DataFrame(DataValues, columns=["DataValue", "LocalDateTime", "CensorCode", "Month", "Year"])
data.set_index(data['LocalDateTime'], inplace=True)
data["Season"] = data.apply(self.calcSeason, axis=1)
return data
def get_data_value_by_id(self, id):
"""
:param id:
:return:
"""
try:
return self._edit_session.query(DataValue).filter_by(id=id).first()
except:
return None
#####################
#
#Update functions
#
#####################
def update_series(self, series):
"""
:param series:
:return:
"""
merged_series = self._edit_session.merge(series)
self._edit_session.add(merged_series)
self._edit_session.commit()
def update_dvs(self, dv_list):
"""
:param dv_list:
:return:
"""
merged_dv_list = map(self._edit_session.merge, dv_list)
self._edit_session.add_all(merged_dv_list)
self._edit_session.commit()
#####################
#
#Create functions
#
#####################
def save_series(self, series, dvs):
""" Save to an Existing Series
:param series:
:param data_values:
:return:
"""
if self.series_exists(series):
try:
self._edit_session.add(series)
self._edit_session.commit()
self.save_values(dvs)
except Exception as e:
self._edit_session.rollback()
raise e
logger.debug("Existing File was overwritten with new information")
return True
else:
logger.debug("There wasn't an existing file to overwrite, please select 'Save As' first")
# there wasn't an existing file to overwrite
raise Exception("Series does not exist, unable to save. Please select 'Save As'")
def save_new_series(self, series, dvs):
""" Create as a new catalog entry
:param series:
:param data_values:
:return:
"""
# Save As case
if self.series_exists(series):
msg = "There is already an existing file with this information. Please select 'Save' or 'Save Existing' to overwrite"
logger.debug(msg)
raise Exception(msg)
else:
try:
self._edit_session.add(series)
self._edit_session.commit()
self.save_values(dvs)
#self._edit_session.add_all(dvs)
except Exception as e:
self._edit_session.rollback()
raise e
logger.debug("A new series was added to the database, series id: "+str(series.id))
return True
def save_values(self, values):
"""
:param values: pandas dataframe
:return:
"""
values.to_sql(name="datavalues", if_exists='append', con=self._session_factory.engine, index=False)
def create_new_series(self, data_values, site_id, variable_id, method_id, source_id, qcl_id):
"""
:param data_values:
:param site_id:
:param variable_id:
:param method_id:
:param source_id:
:param qcl_id:
:return:
"""
self.update_dvs(data_values)
series = Series()
series.site_id = site_id
series.variable_id = variable_id
series.method_id = method_id
series.source_id = source_id
series.quality_control_level_id = qcl_id
self._edit_session.add(series)
self._edit_session.commit()
return series
def create_method(self, description, link):
"""
:param description:
:param link:
:return:
"""
meth = Method()
meth.description = description
if link is not None:
meth.link = link
self._edit_session.add(meth)
self._edit_session.commit()
return meth
def create_variable_by_var(self, var):
"""
:param var: Variable Object
:return:
"""
try:
self._edit_session.add(var)
self._edit_session.commit()
return var
except:
return None
def create_variable(
self, code, name, speciation, variable_unit_id, sample_medium,
value_type, is_regular, time_support, time_unit_id, data_type,
general_category, no_data_value):
"""
:param code:
:param name:
:param speciation:
:param variable_unit_id:
:param sample_medium:
:param value_type:
:param is_regular:
:param time_support:
:param time_unit_id:
:param data_type:
:param general_category:
:param no_data_value:
:return:
"""
var = Variable()
var.code = code
var.name = name
var.speciation = speciation
var.variable_unit_id = variable_unit_id
var.sample_medium = sample_medium
var.value_type = value_type
var.is_regular = is_regular
var.time_support = time_support
var.time_unit_id = time_unit_id
var.data_type = data_type
var.general_category = general_category
var.no_data_value = no_data_value
self._edit_session.add(var)
self._edit_session.commit()
return var
def create_qcl(self, code, definition, explanation):
"""
:param code:
:param definition:
:param explanation:
:return:
"""
qcl = QualityControlLevel()
qcl.code = code
qcl.definition = definition
qcl.explanation = explanation
self._edit_session.add(qcl)
self._edit_session.commit()
return qcl
def create_qualifier_by_qual(self, qualifier):
self._edit_session.add(qualifier)
self._edit_session.commit()
return qualifier
def create_qualifier(self, code, description):
"""
:param code:
:param description:
:return:
"""
qual = Qualifier()
qual.code = code
qual.description = description
return self.create_qualifier_by_qual(qual)
#####################
#
# Delete functions
#
#####################
def delete_series(self, series):
"""
:param series:
:return:
"""
self.delete_values_by_series(series)
delete_series = self._edit_session.merge(series)
self._edit_session.delete(delete_series)
self._edit_session.commit()
def delete_values_by_series(self, series):
"""
:param series:
:return:
"""
try:
return self._edit_session.query(DataValue).filter_by(site_id = series.site_id,
variable_id = series.variable_id,
method_id = series.method_id,
source_id = series.source_id,
quality_control_level_id = series.quality_control_level_id).delete()
except:
return None
def delete_dvs(self, id_list):
"""
:param id_list: list of ids
:return:
"""
self._edit_session.query(DataValue).filter(DataValue.local_date_time.in_(id_list)).delete(False)
#####################
#
#Exist functions
#
#####################
def series_exists(self, series):
"""
:param series:
:return:
"""
return self.series_exists_quint(
series.site_id,
series.variable_id,
series.method_id,
series.source_id,
series.quality_control_level_id
)
def series_exists_quint(self, site_id, var_id, method_id, source_id, qcl_id):
"""
:param site_id:
:param var_id:
:param method_id:
:param source_id:
:param qcl_id:
:return:
"""
try:
result = self._edit_session.query(Series).filter_by(
site_id=site_id,
variable_id=var_id,
method_id=method_id,
source_id=source_id,
quality_control_level_id=qcl_id
).one()
return True
except:
return False
def qcl_exists(self, q):
"""
:param q:
:return:
"""
try:
result = self._edit_session.query(QualityControlLevel).filter_by(code=q.code, definition=q.definition).one()
return True
except:
return False
def method_exists(self, m):
"""
:param m:
:return:
"""
try:
result = self._edit_session.query(Method).filter_by(description=m.description).one()
return True
except:
return False
def variable_exists(self, v):
"""
:param v:
:return:
"""
try:
result = self._edit_session.query(Variable).filter_by(code=v.code,
name=v.name, speciation=v.speciation,
variable_unit_id=v.variable_unit_id,
sample_medium=v.sample_medium,
value_type=v.value_type, is_regular=v.is_regular,
time_support=v.time_support,
time_unit_id=v.time_unit_id, data_type=v.data_type,
general_category=v.general_category,
no_data_value=v.no_data_value).one()
return result
except:
return None
class EditService():
# Mutual exclusion: cursor, or connection_string
def __init__(self, series_id, connection=None, connection_string="", debug=False):
self._connection = connection
self._series_id = series_id
self._filter_from_selection = False
self._debug = debug
if (connection_string is not ""):
self._session_factory = SessionFactory(connection_string, debug)
self._series_service = SeriesService(connection_string, debug)
elif (factory is not None):
#TODO code has changed to no longer use a session factory, refactor so it is correct SR
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
logger.debug("Must have either a connection string or session factory")
#
# TODO throw an exception
self._edit_session = self._session_factory.get_session()
if self._connection == None:
series_service = SeriesService(connection_string, False)
series = series_service.get_series_by_id(series_id)
DataValues = [(dv.id, dv.data_value, dv.value_accuracy, dv.local_date_time, dv.utc_offset, dv.date_time_utc,
dv.site_id, dv.variable_id, dv.offset_value, dv.offset_type_id, dv.censor_code,
dv.qualifier_id, dv.method_id, dv.source_id, dv.sample_id, dv.derived_from_id,
dv.quality_control_level_id) for dv in series.data_values]
self._connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
tmpCursor = self._connection.cursor()
self.init_table(tmpCursor)
tmpCursor.executemany("INSERT INTO DataValues VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
self.reset_filter()
def get_series_service(self):
return self._series_service
def _populate_series(self):
# [(ID, value, datetime), ...]
self._cursor.execute("SELECT ValueID, DataValue, LocalDateTime FROM DataValues ORDER BY LocalDateTime")
results = self._cursor.fetchall()
self._series_points = results
def _test_filter_previous(self):
if not self._filter_from_selection:
self.reset_filter()
###################
# Filters
###################
# operator is a character, either '<' or '>'
def filter_value(self, value, operator):
self._test_filter_previous()
if operator == '<': # less than
for i in range(len(self._series_points)):
# If it's not already in the selection, skip it
if (self._filter_from_selection and not self._filter_list[i]):
continue
if self._series_points[i][1] < value:
self._filter_list[i] = True
else:
self._filter_list[i] = False
if operator == '>': # greater than
for i in range(len(self._series_points)):
if (self._filter_from_selection and not self._filter_list[i]):
continue
if self._series_points[i][1] > value:
self._filter_list[i] = True
else:
self._filter_list[i] = False
def filter_date(self, before, after):
self._test_filter_previous()
previous_date_filter = False
if before != None:
tmp = []
for i in range(len(self._series_points)):
if (self._filter_from_selection and not self._filter_list[i]):
continue
if self._series_points[i][2] < before:
self._filter_list[i] = True
else:
self._filter_list[i] = False
previous_date_filter = True # We've done a previous date filter
if after != None:
for i in range(len(self._series_points)):
if ((previous_date_filter or self._filter_from_selection)
and not self._filter_list[i]):
continue
if self._series_points[i][2] > after:
self._filter_list[i] = True
else:
self._filter_list[i] = False
# Data Gaps
def data_gaps(self, value, time_period):
self._test_filter_previous()
length = len(self._series_points)
value_sec = 0
if time_period == 'second':
value_sec = value
if time_period == 'minute':
value_sec = value * 60
if time_period == 'hour':
value_sec = value * 60 * 60
if time_period == 'day':
value_sec = value * 60 * 60 * 24
tmp = {}
for i in xrange(length):
if (self._filter_from_selection and
not self._filter_list[i]):
continue
if i + 1 < length: # make sure we stay in bounds
point1 = self._series_points[i]
point2 = self._series_points[i + 1]
interval = point2[2] - point1[2]
interval_total_sec = interval.total_seconds()
if interval_total_sec >= value_sec:
tmp[i] = True
tmp[i + 1] = True
self.reset_filter()
for key in tmp.keys():
self._filter_list[key] = True
def value_change_threshold(self, value, operator):
self._test_filter_previous()
length = len(self._series_points)
tmp = {}
for i in xrange(length):
if (self._filter_from_selection and
not self._filter_list[i]):
continue
if i + 1 < length: # make sure we stay in bounds
point1 = self._series_points[i]
point2 = self._series_points[i + 1]
if operator == '>':
if abs(point1[1] - point2[1]) >= value:
tmp[i] = True
tmp[i + 1] = True
if operator == '<':
if abs(point1[1] - point2[1]) <= value:
tmp[i] = True
tmp[i + 1] = True
self.reset_filter()
for key in tmp.keys():
self._filter_list[key] = True
def select_points_tf(self, tf_list):
self._filter_list = tf_list
def select_points(self, id_list=[], datetime_list=[]):
self.reset_filter()
# This should be either one or the other. If it's both, id is used first.
# If neither are set this function does nothing.
if len(id_list)>0:
for i in range(len(self._series_points)):
if self._series_points[i][0] in id_list:
self._filter_list[i] = True
elif datetime_list != None:
for i in range(len(self._series_points)):
if self._series_points[i][2] in datetime_list:
self._filter_list[i] = True
else:
pass
def reset_filter(self):
self._filter_list = [False] * len(self._series_points)
def filter_from_previous(self, value):
self._filter_from_selection = value
def get_toggle(self):
return self._filter_from_selection
###################
# Gets
###################
def get_series(self):
return self._series_service.get_series_by_id(self._series_id)
def get_series_points(self):
#all point in the series
return self._series_points
def get_filtered_points(self):
#list of selected points
tmp = []
for i in range(len(self._series_points)):
if self._filter_list[i]:
tmp.append(self._series_points[i])
return tmp
def get_filtered_dates(self):
return [x[2] for x in self.get_filtered_points()]
def get_filter_list(self):
#true or false list the length of the entire series. true indicate the point is selected
return self._filter_list
def get_qcl(self, qcl_id):
return self._series_service.get_qcl_by_id(qcl_id)
def get_method(self, method_id):
return self._series_service.get_method_by_id(method_id)
def get_variable(self, variable_id):
logger.debug(variable_id)
return self._series_service.get_variable_by_id(variable_id)
#################
# Edits
#################
def change_value(self, value, operator):
filtered_points = self.get_filtered_points()
tmp_filter_list = self._filter_list
query = "UPDATE DataValues SET DataValue = "
if operator == '+':
query += " DataValue + %s " % (value)
if operator == '-':
query += " DataValue - %s " % (value)
if operator == '*':
query += " DataValue * %s " % (value)
if operator == '=':
query += "%s " % (value)
query += "WHERE ValueID IN ("
for i in range(len(filtered_points) - 1):
query += "%s," % (filtered_points[i][0])
query += "%s)" % (filtered_points[-1][0])
self._cursor.execute(query)
self._populate_series()
self._filter_list = tmp_filter_list
def add_points(self, points):
#todo: add the ability to send in multiple datetimes to a single 'point'
query = "INSERT INTO DataValues (DataValue, ValueAccuracy, LocalDateTime, UTCOffset, DateTimeUTC, OffsetValue, OffsetTypeID, "
query += "CensorCode, QualifierID, SampleID, SiteID, VariableID, MethodID, SourceID, QualityControlLevelID) "
query += "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
self._cursor.executemany(query, points)
self._populate_series()
self.reset_filter()
def delete_points(self):
query = "DELETE FROM DataValues WHERE ValueID IN ("
filtered_points = self.get_filtered_points()
num_filtered_points = len(filtered_points)
if num_filtered_points > 0:
for i in range(num_filtered_points - 1): # loop through the second-to-last active point
query += "%s," % (filtered_points[i][0]) # append its ID
query += "%s)" % (filtered_points[-1][0]) # append the final point's ID and close the set
# Delete the points from the cursor
self._cursor.execute(query)
self._populate_series()
self.reset_filter()
def interpolate(self):
tmp_filter_list = self._filter_list
groups = self.get_selection_groups()
for group in groups:
# determine first and last point for the interpolation
first_index = group[0] - 1
last_index = group[-1] + 1
# ignore this group (which is actually the whole set)
# if it includes the first or last point of the series
if first_index <= 0 or last_index == len(self._series_points):
continue
first_point = self._series_points[first_index]
last_point = self._series_points[last_index]
a = 0
c = (last_point[2] - first_point[2]).total_seconds()
f_a = first_point[1]
f_c = last_point[1]
update_list = []
for i in group:
b = (self._series_points[i][2] - first_point[2]).total_seconds()
# linear interpolation formula: f(b) = f(a) + ((b-a)/(c-a))*(f(c) - f(a))
new_val = f_a + ((b - a) / (c - a)) * (f_c - f_a)
point_id = self._series_points[i][0]
update_list.append((new_val, point_id))
query = "UPDATE DataValues SET DataValue = ? WHERE ValueID = ?"
self._cursor.executemany(query, update_list)
self._populate_series()
self._filter_list = tmp_filter_list
def drift_correction(self, gap_width):
tmp_filter_list = self._filter_list
groups = self.get_selection_groups()
# only perform a drift correction if there's a single group
if len(groups) == 1:
group = groups[0]
first_index = group[0]
last_index = group[-1]
first_point = self._series_points[first_index]
last_point = self._series_points[last_index]
x_l = (last_point[2] - first_point[2]).total_seconds()
update_list = []
for i in group:
point = self._series_points[i]
x_i = (point[2] - first_point[2]).total_seconds()
# y_n = y_0 + G(x_i / x_l)
new_val = point[1] + gap_width * (x_i / x_l)
update_list.append((new_val, point[0]))
query = "UPDATE DataValues SET DataValue = ? WHERE ValueID = ?"
self._cursor.executemany(query, update_list)
self._populate_series()
self._filter_list = tmp_filter_list
return True
else:
return False
def get_selection_groups(self):
length = len(self._series_points)
found_group = False
groups = []
cur_group = []
for i in range(length):
if self._filter_list[i]:
if not found_group:
found_group = True
cur_group.append(i) # Append the actual index to the point
if i == length - 1:
groups.append(cur_group)
elif not self._filter_list[i] and found_group:
found_group = False
groups.append(cur_group)
cur_group = []
else:
continue
return groups
def flag(self, qualifier_id):
filtered_points = self.get_filtered_points()
query = "UPDATE DataValues SET QualifierID = %s WHERE ValueID = ?" % (qualifier_id)
self._cursor.executemany(query, [(str(x[0]),) for x in filtered_points])
###################
# Save/Restore
###################
def restore(self):
self._connection.rollback()
self._populate_series()
self.reset_filter()
def updateSeries(self, var=None, method=None, qcl=None, is_new_series=False):
"""
:param var:
:param method:
:param qcl:
:param is_new_series:
:return:
"""
dvs = []
if var is not None:
logger.debug(var.id)
self._cursor.execute("UPDATE DataValues SET VariableID = %s" % (var.id))
if method is not None:
logger.debug(method.id)
self._cursor.execute("UPDATE DataValues SET MethodID = %s" % (method.id))
# check that the code is not zero
# if qcl is not None and qcl.code != 0:
if qcl is not None:
self._cursor.execute("UPDATE DataValues SET QualityControlLevelID = %s" % (qcl.id))
#else:
# raise ValueError("Quality Control Level cannot be zero")
self._cursor.execute("SELECT * FROM DataValues ORDER BY LocalDateTime")
results = self._cursor.fetchall()
# ValueID, DataValue, ValueAccuracy, LocalDateTime, UTCOffset, DateTimeUTC, SiteID, VariableID,
# OffsetValue, OffsetTypeID, CensorCode, QualifierID, MethodID, SourceID, SampleID, DerivedFromID, QualityControlLevelID
for row in results:
dv = self._build_dv_from_tuple(row)
if is_new_series:
dv.id = None
dvs.append(dv)
series = self._series_service.get_series_by_id(self._series_id)
logging.debug("original editing series id: %s"%str(series.id))
# testseries = self._series_service.get_series_by_id_quint(series.site_id, var if var else series.var_id
# , method if method else series.method_id, series.source_id
# , qcl if qcl else series.qcl_id)
# print "test query series id:",testseries.id
#print a if b else 0
if (var or method or qcl ):
tseries = self._series_service.get_series_by_id_quint(site_id=int(series.site_id),
var_id=var.id if var else int(series.variable_id),
method_id=method.id if method else int(series.method_id),
source_id= series.source_id,
qcl_id=qcl.id if qcl else int(series.quality_control_level_id))
if tseries:
logging.debug( "Save existing series ID: %s"% str(series.id))
series = tseries
else:
print "Series doesn't exist (if you are not, you should be running SaveAs)"
if is_new_series:
series = series_module.copy_series(series)
if var:
series.variable_id = var.id
series.variable_code = var.code
series.variable_name = var.name
series.speciation = var.speciation
series.variable_units_id = var.variable_unit.id
series.variable_units_name = var.variable_unit.name
series.sample_medium = var.sample_medium
series.value_type = var.value_type
series.time_support = var.time_support
series.time_units_id = var.time_unit.id
series.time_units_name = var.time_unit.name
series.data_type = var.data_type
series.general_category = var.general_category
if method:
series.method_id = method.id
series.method_description = method.description
if qcl:
series.quality_control_level_id = qcl.id
series.quality_control_level_code = qcl.code
series.begin_date_time = dvs[0].local_date_time
series.end_date_time = dvs[-1].local_date_time
series.begin_date_time_utc = dvs[0].date_time_utc
series.end_date_time_utc = dvs[-1].date_time_utc
series.value_count = len(dvs)
## Override previous save
if not is_new_series:
# delete old dvs
self._series_service.delete_values_by_series(series)
series.data_values = dvs
#logger.debug("series.data_values: %s" % ([x for x in series.data_values]))
return series
def save(self):
""" Save to an existing catalog
:param var:
:param method:
:param qcl:
:return:
"""
series = self.updateSeries(is_new_series=False)
if self._series_service.save_series(series):
logger.debug("series saved!")
return True
else:
logger.debug("Crap happened")
return False
def save_as(self, var=None, method=None, qcl=None):
"""
:param var:
:param method:
:param qcl:
:return:
"""
series = self.updateSeries(var, method, qcl, is_new_series=True)
if self._series_service.save_new_series(series):
logger.debug("series saved!")
return True
else:
logger.debug("Crap happened")
return False
def save_existing(self, var=None, method=None, qcl=None):
"""
:param var:
:param method:
:param qcl:
:return:
"""
series = self.updateSeries(var, method, qcl, is_new_series=False)
if self._series_service.save_series(series):
logger.debug("series saved!")
return True
else:
logger.debug("The Save As Existing Function was Unsuccsesful")
return False
def create_qcl(self, code, definition, explanation):
return self._series_service.create_qcl(code, definition, explanation)
def create_method(self, description, link):
return self._series_service.create_method(description, link)
def create_variable(self, code, name, speciation, variable_unit_id, sample_medium,
value_type, is_regular, time_support, time_unit_id, data_type, general_category, no_data_value):
return self._series_service.create_variable(code, name, speciation, variable_unit_id, sample_medium,
value_type, is_regular, time_support, time_unit_id, data_type,
general_category, no_data_value)
def reconcile_dates(self, parent_series_id):
# FUTURE FEATURE: pull in new field data from another series and add to this series
# (i.e one series contains new field data of an edited series at a higher qcl)
pass
def _build_dv_from_tuple(self, dv_tuple):
dv = DataValue()
dv.id_list = dv_tuple[0]
dv.data_value = dv_tuple[1]
dv.value_accuracy = dv_tuple[2]
dv.local_date_time = dv_tuple[3]
dv.utc_offset = dv_tuple[4]
dv.date_time_utc = dv_tuple[5]
dv.site_id = dv_tuple[6]
dv.variable_id = dv_tuple[7]
dv.offset_value = dv_tuple[8]
dv.offset_type_id = dv_tuple[9]
dv.censor_code = dv_tuple[10]
dv.qualifier_id = dv_tuple[11]
dv.method_id = dv_tuple[12]
dv.source_id = dv_tuple[13]
dv.sample_id = dv_tuple[14]
dv.derived_from_id = dv_tuple[15]
dv.quality_control_level_id = dv_tuple[16]
return dv
def init_table(self, cursor):
cursor.execute("""CREATE TABLE DataValues
(ValueID INTEGER NOT NULL,
DataValue FLOAT NOT NULL,
ValueAccuracy FLOAT,
LocalDateTime TIMESTAMP NOT NULL,
UTCOffset FLOAT NOT NULL,
DateTimeUTC TIMESTAMP NOT NULL,
SiteID INTEGER NOT NULL,
VariableID INTEGER NOT NULL,
OffsetValue FLOAT,
OffsetTypeID INTEGER,
CensorCode VARCHAR(50) NOT NULL,
QualifierID INTEGER,
MethodID INTEGER NOT NULL,
SourceID INTEGER NOT NULL,
SampleID INTEGER,
DerivedFromID INTEGER,
QualityControlLevelID INTEGER NOT NULL,
PRIMARY KEY (ValueID)
UNIQUE (DataValue, LocalDateTime, SiteID, VariableID, MethodID, SourceID, QualityControlLevelID))
""")
def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string, echo=True)
class CVService():
# Accepts a string for creating a SessionFactory, default uses odmdata/connection.cfg
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
# Controlled Vocabulary get methods
#return a list of all terms in the cv
def get_vertical_datum_cvs(self):
result = self._edit_session.query(VerticalDatumCV).order_by(VerticalDatumCV.term).all()
return result
def get_samples(self):
result = self._edit_session.query(Sample).order_by(Sample.lab_sample_code).all()
return result
def get_site_type_cvs(self):
result = self._edit_session.query(SiteTypeCV).order_by(SiteTypeCV.term).all()
return result
def get_variable_name_cvs(self):
result = self._edit_session.query(VariableNameCV).order_by(VariableNameCV.term).all()
return result
def get_offset_type_cvs(self):
result = self._edit_session.query(OffsetType).order_by(OffsetType.id).all()
return result
def get_speciation_cvs(self):
result = self._edit_session.query(SpeciationCV).order_by(SpeciationCV.term).all()
return result
def get_sample_medium_cvs(self):
result = self._edit_session.query(SampleMediumCV).order_by(SampleMediumCV.term).all()
return result
def get_value_type_cvs(self):
result = self._edit_session.query(ValueTypeCV).order_by(ValueTypeCV.term).all()
return result
def get_data_type_cvs(self):
result = self._edit_session.query(DataTypeCV).order_by(DataTypeCV.term).all()
return result
def get_general_category_cvs(self):
result = self._edit_session.query(GeneralCategoryCV).order_by(GeneralCategoryCV.term).all()
return result
def get_censor_code_cvs(self):
result = self._edit_session.query(CensorCodeCV).order_by(CensorCodeCV.term).all()
return result
def get_sample_type_cvs(self):
result = self._edit_session.query(SampleTypeCV).order_by(SampleTypeCV.term).all()
return result
def get_units(self):
result = self._edit_session.query(Unit).all()
return result
def get_units_not_uni(self):
result = self._edit_session.query(Unit).filter(not_(Unit.name.contains('angstrom'))).all()
return result
def get_units_names(self):
result = self._edit_session.query(Unit.name).all()
return result
# return a single cv
def get_unit_by_name(self, unit_name):
result = self._edit_session.query(Unit).filter_by(name=unit_name).first()
return result
def get_unit_by_id(self, unit_id):
result = self._edit_session.query(Unit).filter_by(id=unit_id).first()
return result
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
class SeriesService():
# Accepts a string for creating a SessionFactory, default uses odmdata/connection.cfg
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
def reset_session(self):
self._edit_session = self._session_factory.get_session() # Reset the session in order to prevent memory leaks
def get_db_version(self):
return self._edit_session.query(ODMVersion).first().version_number
#####################
#
# Get functions
#
#####################
# Site methods
def get_all_sites(self):
"""
:return: List[Sites]
"""
return self._edit_session.query(Site).order_by(Site.code).all()
def get_used_sites(self):
"""
Return a list of all sites that are being referenced in the Series Catalog Table
:return: List[Sites]
"""
try:
site_ids = [x[0] for x in self._edit_session.query(distinct(Series.site_id)).all()]
except:
site_ids = None
if not site_ids:
return None
Sites = []
for site_id in site_ids:
Sites.append(self._edit_session.query(Site).filter_by(id=site_id).first())
return Sites
def get_site_by_id(self, site_id):
"""
return a Site object that has an id=site_id
:param site_id: integer- the identification number of the site
:return: Sites
"""
try:
return self._edit_session.query(Site).filter_by(id=site_id).first()
except:
return None
# Variables methods
def get_used_variables(self):
"""
#get list of used variable ids
:return: List[Variables]
"""
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).all()]
except:
var_ids = None
if not var_ids:
return None
Variables = []
#create list of variables from the list of ids
for var_id in var_ids:
Variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return Variables
def get_all_variables(self):
"""
:return: List[Variables]
"""
return self._edit_session.query(Variable).all()
def get_variable_by_id(self, variable_id):
"""
:param variable_id: int
:return: Variables
"""
try:
return self._edit_session.query(Variable).filter_by(id=variable_id).first()
except:
return None
def get_variable_by_code(self, variable_code):
"""
:param variable_code: str
:return: Variables
"""
try:
return self._edit_session.query(Variable).filter_by(code=variable_code).first()
except:
return None
def get_variables_by_site_code(self, site_code): # covers NoDV, VarUnits, TimeUnits
"""
Finds all of variables at a site
:param site_code: str
:return: List[Variables]
"""
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).filter_by(
site_code=site_code).all()]
except:
var_ids = None
variables = []
for var_id in var_ids:
variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return variables
# Unit methods
def get_all_units(self):
"""
:return: List[Units]
"""
return self._edit_session.query(Unit).all()
def get_unit_by_name(self, unit_name):
"""
:param unit_name: str
:return: Units
"""
try:
return self._edit_session.query(Unit).filter_by(name=unit_name).first()
except:
return None
def get_unit_by_id(self, unit_id):
"""
:param unit_id: int
:return: Units
"""
try:
return self._edit_session.query(Unit).filter_by(id=unit_id).first()
except:
return None
def get_all_qualifiers(self):
"""
:return: List[Qualifiers]
"""
result = self._edit_session.query(Qualifier).order_by(Qualifier.code).all()
return result
def get_qualifier_by_code(self, code):
"""
:return: Qualifiers
"""
result = self._edit_session.query(Qualifier).filter(Qualifier.code==code).first()
return result
def get_qualifiers_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.qualifier_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.qualifier_id != None).distinct().subquery()
return self._edit_session.query(Qualifier).join(subquery).distinct().all()
#QCL methods
def get_all_qcls(self):
return self._edit_session.query(QualityControlLevel).all()
def get_qcl_by_id(self, qcl_id):
try:
return self._edit_session.query(QualityControlLevel).filter_by(id=qcl_id).first()
except:
return None
def get_qcl_by_code(self, qcl_code):
try:
return self._edit_session.query(QualityControlLevel).filter_by(code=qcl_code).first()
except:
return None
# Method methods
def get_all_methods(self):
return self._edit_session.query(Method).all()
def get_method_by_id(self, method_id):
try:
result = self._edit_session.query(Method).filter_by(id=method_id).first()
except:
result = None
return result
def get_method_by_description(self, method_code):
try:
result = self._edit_session.query(Method).filter_by(description=method_code).first()
except:
result = None
logger.error("method not found")
return result
def get_offset_types_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.offset_type_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.offset_type_id != None).distinct().subquery()
return self._edit_session.query(OffsetType).join(subquery).distinct().all()
def get_samples_by_series_id(self, series_id):
"""
:param series_id:
:return:
"""
subquery = self._edit_session.query(DataValue.sample_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.sample_id != None).distinct().subquery()
return self._edit_session.query(Sample).join(subquery).distinct().all()
# Series Catalog methods
def get_all_series(self):
"""
Returns all series as a modelObject
:return: List[Series]
"""
#logger.debug("%s" % self._edit_session.query(Series).order_by(Series.id).all())
return self._edit_session.query(Series).order_by(Series.id).all()
def get_series_by_site(self , site_id):
"""
:param site_id: int
:return: List[Series]
"""
try:
selectedSeries = self._edit_session.query(Series).filter_by(site_id=site_id).order_by(Series.id).all()
return selectedSeries
except:
return None
def get_series_by_id(self, series_id):
"""
:param series_id: int
:return: Series
"""
try:
return self._edit_session.query(Series).filter_by(id=series_id).first()
except Exception as e:
print e
return None
def get_series_by_id_quint(self, site_id, var_id, method_id, source_id, qcl_id):
"""
:param site_id:
:param var_id:
:param method_id:
:param source_id:
:param qcl_id:
:return: Series
"""
try:
return self._edit_session.query(Series).filter_by(
site_id=site_id, variable_id=var_id, method_id=method_id,
source_id=source_id, quality_control_level_id=qcl_id).first()
except:
return None
def get_series_from_filter(self):
# Pass in probably a Series object, match it against the database
pass
#Data Value Methods
def get_values_by_series(self, series_id):
'''
:param series_id: Series id
:return: pandas dataframe
'''
series= self.get_series_by_id(series_id)
if series:
q = self._edit_session.query(DataValue).filter_by(
site_id=series.site_id,
variable_id=series.variable_id,
method_id=series.method_id,
source_id=series.source_id,
quality_control_level_id=series.quality_control_level_id)
query=q.statement.compile(dialect=self._session_factory.engine.dialect)
data= pd.read_sql_query(sql= query,
con = self._session_factory.engine,
params = query.params )
#return data.set_index(data['LocalDateTime'])
return data
else:
return None
def get_all_values_df(self):
"""
:return: Pandas DataFrame object
"""
q = self._edit_session.query(DataValue).order_by(DataValue.local_date_time)
query = q.statement.compile(dialect=self._session_factory.engine.dialect)
data = pd.read_sql_query(sql=query, con=self._session_factory.engine,
params=query.params)
columns = list(data)
columns.insert(0, columns.pop(columns.index("DataValue")))
columns.insert(1, columns.pop(columns.index("LocalDateTime")))
columns.insert(2, columns.pop(columns.index("QualifierID")))
data = data.ix[:, columns]
return data.set_index(data['LocalDateTime'])
def get_all_values_list(self):
"""
:return:
"""
result = self._edit_session.query(DataValue).order_by(DataValue.local_date_time).all()
return [x.list_repr() for x in result]
def get_all_values(self):
return self._edit_session.query(DataValue).order_by(DataValue.local_date_time).all()
@staticmethod
def calcSeason(row):
month = int(row["Month"])
if month in [1, 2, 3]:
return 1
elif month in[4, 5, 6]:
return 2
elif month in [7, 8, 9]:
return 3
elif month in [10, 11, 12]:
return 4
def get_all_plot_values(self):
"""
:return:
"""
q = self._edit_session.query(DataValue.data_value.label('DataValue'),
DataValue.local_date_time.label('LocalDateTime'),
DataValue.censor_code.label('CensorCode'),
func.strftime('%m', DataValue.local_date_time).label('Month'),
func.strftime('%Y', DataValue.local_date_time).label('Year')
#DataValue.local_date_time.strftime('%m'),
#DataValue.local_date_time.strftime('%Y'))
).order_by(DataValue.local_date_time)
query = q.statement.compile(dialect=self._session_factory.engine.dialect)
data = pd.read_sql_query(sql=query,
con=self._session_factory.engine,
params=query.params)
data["Season"] = data.apply(self.calcSeason, axis=1)
return data.set_index(data['LocalDateTime'])
def get_plot_values(self, seriesID, noDataValue, startDate = None, endDate = None ):
"""
:param seriesID:
:param noDataValue:
:param startDate:
:param endDate:
:return:
"""
series = self.get_series_by_id(seriesID)
DataValues = [
(dv.data_value, dv.local_date_time, dv.censor_code, dv.local_date_time.strftime('%m'),
dv.local_date_time.strftime('%Y'))
for dv in series.data_values
if dv.data_value != noDataValue if dv.local_date_time >= startDate if dv.local_date_time <= endDate
]
data = pd.DataFrame(DataValues, columns=["DataValue", "LocalDateTime", "CensorCode", "Month", "Year"])
data.set_index(data['LocalDateTime'], inplace=True)
data["Season"] = data.apply(self.calcSeason, axis=1)
return data
def get_data_value_by_id(self, id):
"""
:param id:
:return:
"""
try:
return self._edit_session.query(DataValue).filter_by(id=id).first()
except:
return None
#####################
#
#Update functions
#
#####################
def update_series(self, series):
"""
:param series:
:return:
"""
merged_series = self._edit_session.merge(series)
self._edit_session.add(merged_series)
self._edit_session.commit()
def update_dvs(self, dv_list):
"""
:param dv_list:
:return:
"""
merged_dv_list = map(self._edit_session.merge, dv_list)
self._edit_session.add_all(merged_dv_list)
self._edit_session.commit()
#####################
#
#Create functions
#
#####################
def save_series(self, series, dvs):
""" Save to an Existing Series
:param series:
:param data_values:
:return:
"""
if self.series_exists(series):
try:
self._edit_session.add(series)
self._edit_session.commit()
self.save_values(dvs)
except Exception as e:
self._edit_session.rollback()
raise e
logger.info("Existing File was overwritten with new information")
return True
else:
logger.debug("There wasn't an existing file to overwrite, please select 'Save As' first")
# there wasn't an existing file to overwrite
raise Exception("Series does not exist, unable to save. Please select 'Save As'")
def save_new_series(self, series, dvs):
""" Create as a new catalog entry
:param series:
:param data_values:
:return:
"""
# Save As case
if self.series_exists(series):
msg = "There is already an existing file with this information. Please select 'Save' or 'Save Existing' to overwrite"
logger.info(msg)
raise Exception(msg)
else:
try:
self._edit_session.add(series)
self._edit_session.commit()
self.save_values(dvs)
#self._edit_session.add_all(dvs)
except Exception as e:
self._edit_session.rollback()
raise e
logger.info("A new series was added to the database, series id: "+str(series.id))
return True
def save_values(self, values):
"""
:param values: pandas dataframe
:return:
"""
values.to_sql(name="datavalues", if_exists='append', con=self._session_factory.engine, index=False)
def create_new_series(self, data_values, site_id, variable_id, method_id, source_id, qcl_id):
"""
:param data_values:
:param site_id:
:param variable_id:
:param method_id:
:param source_id:
:param qcl_id:
:return:
"""
self.update_dvs(data_values)
series = Series()
series.site_id = site_id
series.variable_id = variable_id
series.method_id = method_id
series.source_id = source_id
series.quality_control_level_id = qcl_id
self._edit_session.add(series)
self._edit_session.commit()
return series
def create_method(self, description, link):
"""
:param description:
:param link:
:return:
"""
meth = Method()
meth.description = description
if link is not None:
meth.link = link
self._edit_session.add(meth)
self._edit_session.commit()
return meth
def create_variable_by_var(self, var):
"""
:param var: Variable Object
:return:
"""
try:
self._edit_session.add(var)
self._edit_session.commit()
return var
except:
return None
def create_variable(
self, code, name, speciation, variable_unit_id, sample_medium,
value_type, is_regular, time_support, time_unit_id, data_type,
general_category, no_data_value):
"""
:param code:
:param name:
:param speciation:
:param variable_unit_id:
:param sample_medium:
:param value_type:
:param is_regular:
:param time_support:
:param time_unit_id:
:param data_type:
:param general_category:
:param no_data_value:
:return:
"""
var = Variable()
var.code = code
var.name = name
var.speciation = speciation
var.variable_unit_id = variable_unit_id
var.sample_medium = sample_medium
var.value_type = value_type
var.is_regular = is_regular
var.time_support = time_support
var.time_unit_id = time_unit_id
var.data_type = data_type
var.general_category = general_category
var.no_data_value = no_data_value
self._edit_session.add(var)
self._edit_session.commit()
return var
def create_qcl(self, code, definition, explanation):
"""
:param code:
:param definition:
:param explanation:
:return:
"""
qcl = QualityControlLevel()
qcl.code = code
qcl.definition = definition
qcl.explanation = explanation
self._edit_session.add(qcl)
self._edit_session.commit()
return qcl
def create_qualifier_by_qual(self, qualifier):
self._edit_session.add(qualifier)
self._edit_session.commit()
return qualifier
def create_qualifier(self, code, description):
"""
:param code:
:param description:
:return:
"""
qual = Qualifier()
qual.code = code
qual.description = description
return self.create_qualifier_by_qual(qual)
#####################
#
# Delete functions
#
#####################
def delete_series(self, series):
"""
:param series:
:return:
"""
try:
self.delete_values_by_series(series)
delete_series = self._edit_session.merge(series)
self._edit_session.delete(delete_series)
self._edit_session.commit()
except Exception as e:
message = "series was not successfully deleted: %s" % e
print message
logger.error(message)
raise e
def delete_values_by_series(self, series, startdate = None):
"""
:param series:
:return:
"""
try:
q= self._edit_session.query(DataValue).filter_by(site_id = series.site_id,
variable_id = series.variable_id,
method_id = series.method_id,
source_id = series.source_id,
quality_control_level_id = series.quality_control_level_id)
if startdate is not None:
#start date indicates what day you should start deleting values. the values will delete to the end of the series
return q.filter(DataValue.local_date_time >= startdate).delete()
else:
return q.delete()
except Exception as ex:
message = "Values were not successfully deleted: %s" % ex
print message
logger.error(message)
raise ex
def delete_dvs(self, id_list):
"""
:param id_list: list of datetimes
:return:
"""
try:
self._edit_session.query(DataValue).filter(DataValue.local_date_time.in_(id_list)).delete(False)
except Exception as ex:
message = "Values were not successfully deleted: %s" % ex
print message
logger.error(message)
raise ex
#####################
#
#Exist functions
#
#####################
def series_exists(self, series):
"""
:param series:
:return:
"""
return self.series_exists_quint(
series.site_id,
series.variable_id,
series.method_id,
series.source_id,
series.quality_control_level_id
)
def series_exists_quint(self, site_id, var_id, method_id, source_id, qcl_id):
"""
:param site_id:
:param var_id:
:param method_id:
:param source_id:
:param qcl_id:
:return:
"""
try:
result = self._edit_session.query(Series).filter_by(
site_id=site_id,
variable_id=var_id,
method_id=method_id,
source_id=source_id,
quality_control_level_id=qcl_id
).one()
return True
except:
return False
def qcl_exists(self, q):
"""
:param q:
:return:
"""
try:
result = self._edit_session.query(QualityControlLevel).filter_by(code=q.code, definition=q.definition).one()
return True
except:
return False
def method_exists(self, m):
"""
:param m:
:return:
"""
try:
result = self._edit_session.query(Method).filter_by(description=m.description).one()
return True
except:
return False
def variable_exists(self, v):
"""
:param v:
:return:
"""
try:
result = self._edit_session.query(Variable).filter_by(code=v.code,
name=v.name, speciation=v.speciation,
variable_unit_id=v.variable_unit_id,
sample_medium=v.sample_medium,
value_type=v.value_type, is_regular=v.is_regular,
time_support=v.time_support,
time_unit_id=v.time_unit_id, data_type=v.data_type,
general_category=v.general_category,
no_data_value=v.no_data_value).one()
return result
except:
return None
def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string,
echo=True)
class SeriesService():
# Accepts a string for creating a SessionFactory, default uses odmdata/connection.cfg
def __init__(self, connection_string="", debug=False):
self._session_factory = SessionFactory(connection_string, debug)
self._edit_session = self._session_factory.get_session()
self._debug = debug
def get_db_version(self):
return self._edit_session.query(ODMVersion).first().version_number
# Site methods
def get_all_sites(self):
return self._edit_session.query(Site).order_by(Site.code).all()
def get_all_used_sites(self):
try:
site_ids = [x[0] for x in self._edit_session.query(distinct(Series.site_id)).all()]
except:
site_ids = None
if not site_ids:
return None
Sites = []
for site_id in site_ids:
Sites.append(self._edit_session.query(Site).filter_by(id=site_id).first())
return Sites
def get_site_by_id(self, site_id):
try:
return self._edit_session.query(Site).filter_by(id=site_id).first()
except:
return None
# Variables methods
def get_all_used_variables(self):
#get list of used variable ids
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).all()]
except:
var_ids = None
Variables = []
#create list of variables from the list of ids
for var_id in var_ids:
Variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return Variables
def get_all_variables(self):
return self._edit_session.query(Variable).all()
def get_variable_by_id(self, variable_id):
try:
return self._edit_session.query(Variable).filter_by(id=variable_id).first()
except:
return None
def get_variable_by_code(self, variable_code):
try:
return self._edit_session.query(Variable).filter_by(code=variable_code).first()
except:
return None
def get_variables_by_site_code(self, site_code): # covers NoDV, VarUnits, TimeUnits
try:
var_ids = [x[0] for x in self._edit_session.query(distinct(Series.variable_id)).filter_by(
site_code=site_code).all()]
except:
var_ids = None
variables = []
for var_id in var_ids:
variables.append(self._edit_session.query(Variable).filter_by(id=var_id).first())
return variables
# Unit methods
def get_all_units(self):
return self._edit_session.query(Unit).all()
def get_unit_by_name(self, unit_name):
try:
return self._edit_session.query(Unit).filter_by(name=unit_name).first()
except:
return None
def get_unit_by_id(self, unit_id):
try:
return self._edit_session.query(Unit).filter_by(id=unit_id).first()
except:
return None
def get_offset_types_by_series_id(self, series_id):
subquery = self._edit_session.query(DataValue.offset_type_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.offset_type_id != None).distinct().subquery()
return self._edit_session.query(OffsetType).join(subquery).distinct().all()
def get_qualifiers_by_series_id(self, series_id):
subquery = self._edit_session.query(DataValue.qualifier_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.qualifier_id != None).distinct().subquery()
return self._edit_session.query(Qualifier).join(subquery).distinct().all()
def get_samples_by_series_id(self, series_id):
subquery = self._edit_session.query(DataValue.sample_id).outerjoin(
Series.data_values).filter(Series.id == series_id, DataValue.sample_id != None).distinct().subquery()
return self._edit_session.query(Sample).join(subquery).distinct().all()
# Series Catalog methods
def get_all_series(self):
"""Returns all series as a modelObject"""
#logger.debug("%s" % self._edit_session.query(Series).order_by(Series.id).all())
return self._edit_session.query(Series).order_by(Series.id).all()
def reset_session(self):
self._edit_session = self._session_factory.get_session() # Reset the session in order to prevent memory leaks
def get_series_by_site(self , site_id):
try:
selectedSeries = self._edit_session.query(Series).filter_by(site_id=site_id).order_by(Series.id).all()
return selectedSeries
except:
return None
def get_series_by_id(self, series_id):
try:
selectedSeries = self._edit_session.query(Series).filter_by(id=series_id).order_by(Series.id).first()
return selectedSeries
except:
return None
def get_series_by_id_quint(self, site_id, var_id, method_id, source_id, qcl_id):
try:
return self._edit_session.query(Series).filter_by(
site_id=site_id, variable_id=var_id, method_id=method_id,
source_id=source_id, quality_control_level_id=qcl_id).first()
except:
return None
def get_series_from_filter(self):
# Pass in probably a Series object, match it against the database
pass
def save_series(self, series):
""" Save to an Existing Series
:param series:
:param data_values:
:return:
"""
if self.does_exist(series):
self._edit_session.add(series)
self._edit_session.add_all(series.data_values)
self._edit_session.commit()
logger.debug("Existing File was overwritten with new information")
return True
else:
logger.debug("There wasn't an existing file to overwrite, please select 'Save As' first")
# there wasn't an existing file to overwrite
raise Exception("Series does not exist, unable to save. Please select 'Save As'")
def save_new_series(self, series):
""" Create as a new catalog entry
:param series:
:param data_values:
:return:
"""
# Save As case
if self.does_exist(series):
msg = "There is already an existing file with this information. Please select 'Save' or 'Save Existing' to overwrite"
logger.debug(msg)
raise Exception(msg)
else:
self._edit_session.add(series)
self._edit_session.add_all(series.data_values)
self._edit_session.commit()
logger.debug("A new series was added to the database")
return True
def does_exist(self, series):
return self.series_exists(
series.site_id,
series.variable_id,
series.method_id,
series.source_id,
series.quality_control_level_id
)
def series_exists(self, site_id, var_id, method_id, source_id, qcl_id):
try:
result = self._edit_session.query(Series).filter_by(
site_id=site_id,
variable_id=var_id,
method_id=method_id,
source_id=source_id,
quality_control_level_id=qcl_id
).one()
return True
except:
return False
def get_data_value_by_id(self, id):
try:
return self._edit_session.query(DataValue).filter_by(id=id).first()
except:
return None
def get_all_qcls(self):
return self._edit_session.query(QualityControlLevel).all()
def get_qcl_by_id(self, qcl_id):
try:
return self._edit_session.query(QualityControlLevel).filter_by(id=qcl_id).first()
except:
return None
def get_qcl_by_code(self, qcl_code):
try:
return self._edit_session.query(QualityControlLevel).filter_by(code=qcl_code).first()
except:
return None
# Method methods
def get_all_methods(self):
return self._edit_session.query(Method).all()
def get_method_by_id(self, method_id):
try:
result = self._edit_session.query(Method).filter_by(id=method_id).first()
except:
result = None
return result
def get_method_by_description(self, method_code):
try:
result = self._edit_session.query(Method).filter_by(description=method_code).first()
except:
result = None
return result
# Edit/delete methods
def delete_dvs(self, dv_list):
dlg = wx.ProgressDialog("Delete Progress", "Deleting %s values" % len(dv_list), maximum=len(dv_list),
parent=None,
style=0 | wx.PD_APP_MODAL | wx.PD_ELAPSED_TIME |
wx.PD_ESTIMATED_TIME | wx.PD_REMAINING_TIME | wx.PD_AUTO_HIDE)
length = len(dv_list)
for i, dv in enumerate(dv_list):
dlg.Update(i, "%s/%s Objects Deleted" % (i, length))
merged_dv = self._edit_session.merge(dv)
self._edit_session.delete(merged_dv)
dlg.Update(length, "Commiting to database")
self._edit_session.commit()
dlg.Destroy()
def delete_values_by_series(self, series):
try:
return self._edit_session.query(DataValue).filter_by(site_id = series.site_id,
variable_id = series.variable_id,
method_id = series.method_id,
source_id = series.source_id,
quality_control_level_id = series.quality_control_level_id).delete()
except:
return None
def update_dvs(self, dv_list):
merged_dv_list = map(self._edit_session.merge, dv_list)
self._edit_session.add_all(merged_dv_list)
self._edit_session.commit()
def create_new_series(self, data_values, site_id, variable_id, method_id, source_id, qcl_id):
self.update_dvs(data_values)
series = Series()
series.site_id = site_id
series.variable_id = variable_id
series.method_id = method_id
series.source_id = source_id
series.quality_control_level_id = qcl_id
self._edit_session.add(series)
self._edit_session.commit()
return series
def update_series(self, series):
merged_series = self._edit_session.merge(series)
self._edit_session.add(merged_series)
self._edit_session.commit()
def create_method(self, description, link):
meth = Method()
meth.description = description
if link is not None:
meth.link = link
self._edit_session.add(meth)
self._edit_session.commit()
return meth
def create_variable_by_var(self, var):
try:
self._edit_session.add(var)
self._edit_session.commit()
return var
except:
return None
def create_variable(
self, code, name, speciation, variable_unit_id, sample_medium,
value_type, is_regular, time_support, time_unit_id, data_type,
general_category, no_data_value):
var = Variable()
var.code = code
var.name = name
var.speciation = speciation
var.variable_unit_id = variable_unit_id
var.sample_medium = sample_medium
var.value_type = value_type
var.is_regular = is_regular
var.time_support = time_support
var.time_unit_id = time_unit_id
var.data_type = data_type
var.general_category = general_category
var.no_data_value = no_data_value
self._edit_session.add(var)
self._edit_session.commit()
return var
def create_qcl(self, code, definition, explanation):
qcl = QualityControlLevel()
qcl.code = code
qcl.definition = definition
qcl.explanation = explanation
self._edit_session.add(qcl)
self._edit_session.commit()
return qcl
def delete_series(self, series):
self.delete_dvs(series.data_values)
delete_series = self._edit_session.merge(series)
self._edit_session.delete(delete_series)
self._edit_session.commit()
def qcl_exists(self, q):
try:
result = self._edit_session.query(QualityControlLevel).filter_by(code=q.code, definition=q.definition).one()
return True
except:
return False
def method_exists(self, m):
try:
result = self._edit_session.query(Method).filter_by(description=m.description).one()
return True
except:
return False
def variable_exists(self, v):
try:
result = self._edit_session.query(Variable).filter_by(code=v.code,
name=v.name, speciation=v.speciation,
variable_unit_id=v.variable_unit_id,
sample_medium=v.sample_medium,
value_type=v.value_type, is_regular=v.is_regular,
time_support=v.time_support,
time_unit_id=v.time_unit_id, data_type=v.data_type,
general_category=v.general_category,
no_data_value=v.no_data_value).one()
return result
except:
return None
