def __init__(self,
series_id,
connection=None,
connection_string="",
debug=False):
# print "Series id: ", series_id
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):
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
print "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 DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
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:
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
print "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 DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
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.SessionMaker' in repr(session)
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.SessionMaker' in repr(session)
class EditService():
# Mutual exclusion: cursor, or connection_string
def __init__(self, series_id, connection=None, connection_string="", debug=False):
# print "Series id: ", series_id
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):
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
print "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 DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
def _populate_series(self):
# [(ID, value, datetime), ...]
self._cursor.execute("SELECT ValueID, DataValue, LocalDateTime FROM DataValuesEdit ORDER BY LocalDateTime")
results = self._cursor.fetchall()
self._series_points = results
self.reset_filter()
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):
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):
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 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 id_list != None:
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 toggle_filter_previous(self):
self._filter_from_selection = not 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):
return self._series_points
def get_filtered_points(self):
tmp = []
for i in range(len(self._series_points)):
if self._filter_list[i]:
tmp.append(self._series_points[i])
return tmp
def get_filter_list(self):
return self._filter_list
def get_qcl(self, qcl_id):
return self._series_service.get_qcl_by_id(qcl_id)
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):
print 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 DataValuesEdit 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):
print points
query = "INSERT INTO DataValuesEdit (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()
def delete_points(self):
query = "DELETE FROM DataValuesEdit 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()
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 DataValuesEdit 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 DataValuesEdit 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 DataValuesEdit 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()
def save(self, var=None, method=None, qcl=None):
dvs = []
is_new_series = False
if var is not None:
self._cursor.execute("UPDATE DataValuesEdit SET VariableID = %s" % (var.id))
is_new_series = True
if method is not None:
print method.id
self._cursor.execute("UPDATE DataValuesEdit SET MethodID = %s" % (method.id))
is_new_series = True
# check that the code is not zero
if qcl is not None and qcl.code > 0:
self._cursor.execute("UPDATE DataValuesEdit SET QualityControlLevelID = %s" % (qcl.id))
is_new_series = True
else:
raise ValueError("Quality Control Level cannot be zero")
self._cursor.execute("SELECT * FROM DataValuesEdit 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)
if is_new_series:
series = series_module.copy(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)
if not is_new_series:
# delete old dvs
old_dvs = series.data_values
self._series_service.delete_dvs(old_dvs)
series.data_values = dvs
self._series_service.save_series(series, dvs)
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, sample_medium,
value_type, is_regular, time_support, time_unit, data_type, general_category, no_data_value):
return self._series_service.create_variable(code, name, speciation, variable_unit, sample_medium,
value_type, is_regular, time_support, time_unit, data_type, general_category, no_data_value)
def reconcile_dates(self, parent_series_id):
# append new data to this series
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 DataValuesEdit
(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)
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 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): session = self._session_factory.get_session() result = session.query(VerticalDatumCV).order_by(VerticalDatumCV.term).all() session.close() return result def get_samples(self): session = self._session_factory.get_session() result = session.query(Sample).order_by(Sample.lab_sample_code).all() session.close() return result def get_qualifiers(self): result = self._edit_session.query(Qualifier).order_by(Qualifier.code).all() return result def create_qualifier(self, qualifier): session = self._session_factory.get_session() self._edit_session.add(qualifier) self._edit_session.commit() def get_site_type_cvs(self): session = self._session_factory.get_session() result = session.query(SiteTypeCV).order_by(SiteTypeCV.term).all() session.close() return result def get_variable_name_cvs(self): session = self._session_factory.get_session() result = session.query(VariableNameCV).order_by(VariableNameCV.term).all() session.close() return result def get_offset_type_cvs(self): session = self._session_factory.get_session() result = session.query(OffsetType).order_by(OffsetType.id).all() session.close() return result def get_speciation_cvs(self): session = self._session_factory.get_session() result = session.query(SpeciationCV).order_by(SpeciationCV.term).all() session.close() return result def get_sample_medium_cvs(self): session = self._session_factory.get_session() result = session.query(SampleMediumCV).order_by(SampleMediumCV.term).all() session.close() return result def get_value_type_cvs(self): session = self._session_factory.get_session() result = session.query(ValueTypeCV).order_by(ValueTypeCV.term).all() session.close() return result def get_data_type_cvs(self): session = self._session_factory.get_session() result = session.query(DataTypeCV).order_by(DataTypeCV.term).all() session.close() return result def get_general_category_cvs(self): session = self._session_factory.get_session() result = session.query(GeneralCategoryCV).order_by(GeneralCategoryCV.term).all() session.close() return result def get_censor_code_cvs(self): session = self._session_factory.get_session() result = session.query(CensorCodeCV).order_by(CensorCodeCV.term).all() session.close() return result def get_sample_type_cvs(self): session = self._session_factory.get_session() result = session.query(SampleTypeCV).order_by(SampleTypeCV.term).all() session.close() return result def get_units(self): session = self._session_factory.get_session() result = self._edit_session.query(Unit).all() session.close() return result # return a single cv def get_unit_by_name(self, unit_name): session = self._session_factory.get_session() result = self._edit_session.query(Unit).filter_by(name=unit_name).one() session.close() return result def get_unit_by_id(self, unit_id): session = self._session_factory.get_session() result = self._edit_session.query(Unit).filter_by(id=unit_id).one() session.close() return result
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_site_by_id(self, site_id): try: return self._edit_session.query(Site).filter_by(id=site_id).one() except: return None # Variables methods 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).one() 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).one()) 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).one() except: return None def get_unit_by_id(self, unit_id): try: return self._edit_session.query(Unit).filter_by(id=unit_id).one() 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): return self._edit_session.query(Series).order_by(Series.id).all() def get_series_by_id(self, series_id): try: return self._edit_session.query(Series).filter_by(id=series_id).order_by(Series.id).one() 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).one() 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, data_values): if self.series_exists(series.site_id, series.variable_id, series.method_id, series.source_id, series.quality_control_level_id): return False else: self._edit_session.add(series) self._edit_session.add_all(data_values) self._edit_session.commit() return True 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).one() 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).one() 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).one() except: result = None return result # Edit/delete methods def delete_dvs(self, dv_list): for dv in dv_list: merged_dv = self._edit_session.merge(dv) self._edit_session.delete(merged_dv) self._edit_session.commit() 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(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 True except: return False
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 get_test_data(self):
return self._edit_session.query(ODMVersion).first()
# Sites methods
def get_sites(self, site_code=""):
result = None
if (site_code):
result = self._edit_session.query(
distinct(Series.site_id), Series.site_code,
Series.site_name).filter_by(site_code=site_code).one()
else:
result = self._edit_session.query(
distinct(Series.site_id), Series.site_code,
Series.site_name).order_by(Series.site_code).all()
return result
def get_site(self, site_id):
return self._edit_session.query(Site).filter_by(id=site_id).one()
# Variables methods
def get_variables(self, site_code=""): # covers NoDV, VarUnits, TimeUnits
result = None
if (site_code):
result = self._edit_session.query(
distinct(Series.variable_id), Series.variable_code,
Series.variable_name).filter_by(site_code=site_code).order_by(
Series.variable_code).all()
else:
result = self._edit_session.query(
distinct(Series.variable_id), Series.variable_code,
Series.variable_name).order_by(Series.variable_code).all()
return result
def get_vars(self):
try:
result = self._edit_session.query(Variable).all()
except:
result = None
return result
def get_variable_by_id(self, variable_id):
return self._edit_session.query(Variable).filter_by(id=variable_id)
def get_no_data_value(self, variable_id):
return self._edit_session.query(
Variable.no_data_value).filter_by(id=variable_id).one()
# Unit methods
def get_unit_by_name(self, unit_name):
return self._edit_session.query(Unit).filter_by(name=unit_name).one()
def get_unit_by_id(self, unit_id):
return self._edit_session.query(Unit).filter_by(id=unit_id).one()
def get_units(self):
return self._edit_session.query(Unit).all()
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()
def get_unit_abbrev_by_name(self, unit_name):
try:
result = self._edit_session.query(
Unit.abbreviation).filter_by(name=unit_name).one()[0]
except:
result = None
return result
# Series Catalog methods
def get_series(self, site_code="", var_code=""):
result = None
if (site_code and var_code):
result = self._edit_session.query(Series).filter_by(
site_code=site_code,
variable_code=var_code).order_by(Series.id).all()
elif (site_code):
result = self._edit_session.query(Series).filter_by(
site_code=site_code).order_by(Series.id).all()
elif (var_code):
result = self._edit_session.query(Series).filter_by(
variable_code=var_code).order_by(Series.id).all()
else:
result = self._edit_session.query(Series).order_by(Series.id).all()
return result
def get_series_by_id(self, series_id):
try:
result = self._edit_session.query(Series).filter_by(
id=series_id).order_by(Series.id).one()
except:
result = None
return result
def get_series_by_id_quint(self, site_id, var_id, method_id, source_id,
qcl_id):
try:
result = self._edit_session.query(Series).filter_by(
site_id=site_id,
var_id=var_id,
method_id=method_id,
source_id=source_id,
qcl_id=qcl_id)
except:
result = None
return result
def get_all_series(self):
return self._edit_session.query(Series).all()
def get_all_series_tuples(self):
return self._edit_session.query(
Series.id, Series.site_id, Series.site_code, Series.site_name,
Series.variable_id, Series.variable_code, Series.variable_name,
Series.speciation, Series.variable_units_id,
Series.variable_units_name, Series.sample_medium,
Series.value_type, Series.time_support, Series.time_units_id,
Series.time_units_name, Series.data_type, Series.general_category,
Series.method_id, Series.method_description, Series.source_id,
Series.organization, Series.source_description, Series.citation,
Series.quality_control_level_id, Series.quality_control_level_code,
Series.begin_date_time, Series.end_date_time,
Series.begin_date_time_utc, Series.end_date_time_utc,
Series.value_count).order_by(Series.id).all()
def get_series_from_filter(self):
# Pass in probably a Series object, match it against the database
pass
def save_series(self, series, data_values):
if self.series_exists(series.site_id, series.variable_id,
series.method_id, series.source_id,
series.quality_control_level_id):
print "series exists"
else:
self._edit_session.add(series)
self._edit_session.add_all(data_values)
self._edit_session.commit()
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()
print result.id
return True
except:
return False
def get_data_value_by_id(self, id):
try:
result = self._edit_session.query(DataValue).filter_by(id=id).one()
except:
result = None
return result
def get_qcl_by_id(self, qcl_id):
try:
result = self._edit_session.query(QualityControlLevel).filter_by(
id=qcl_id).one()
except:
result = None
return result
def get_qcls(self):
try:
result = self._edit_session.query(QualityControlLevel).all()
except:
result = None
return result
# Method methods
def get_method_by_id(self, method_id):
try:
result = self._edit_session.query(Method).filter_by(
id=method_id).one()
except:
result = None
return result
def get_methods(self):
try:
result = self._edit_session.query(Method).all()
except:
result = None
return result
# Edit/delete methods
def delete_dvs(self, dv_list):
for dv in dv_list:
merged_dv = self._edit_session.merge(dv)
self._edit_session.delete(merged_dv)
self._edit_session.commit()
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, variable_id, site_id, method_id,
source_id, qcl_id):
self.update_dvs(data_values)
series = Series()
series.variable_id = variable_id
series.site_id = site_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()
def create_qualifier(self, code, description):
qualifier = Qualifier()
qualifier.code = code
qualifier.description = description
self._edit_session.add(qualifier)
self._edit_session.commit()
def update_series_catalog(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()
print description, " ", link
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(self, code, name, speciation, variable_unit,
sample_medium, value_type, is_regular, time_support,
time_unit, data_type, general_category, no_data_value):
var = Variable()
var.code = code
var.name = name
var.speciation = speciation
var.variable_unit = variable_unit
var.sample_medium = sample_medium
var.value_type = value_type
var.is_regular = is_regular
var.time_support = time_support
var.time_unit = time_unit
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 True
except:
return False
def setup(self):
self.connection_string = "sqlite:///:memory:"
self.session_factory = SessionFactory(self.connection_string,
echo=True)
class EditService():
# Mutual exclusion: cursor, or connection_string
def __init__(self,
series_id,
connection=None,
connection_string="",
debug=False):
# print "Series id: ", series_id
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):
self._session_factory = factory
service_manager = ServiceManager()
self._series_service = service_manager.get_series_service()
else:
# One or the other must be set
print "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 DataValuesEdit VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
DataValues)
self._connection.commit()
self._cursor = self._connection.cursor()
self._populate_series()
def _populate_series(self):
# [(ID, value, datetime), ...]
self._cursor.execute(
"SELECT ValueID, DataValue, LocalDateTime FROM DataValuesEdit ORDER BY LocalDateTime"
)
results = self._cursor.fetchall()
self._series_points = results
self.reset_filter()
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):
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):
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 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 id_list != None:
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 toggle_filter_previous(self):
self._filter_from_selection = not 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):
return self._series_points
def get_filtered_points(self):
tmp = []
for i in range(len(self._series_points)):
if self._filter_list[i]:
tmp.append(self._series_points[i])
return tmp
def get_filter_list(self):
return self._filter_list
def get_qcl(self, qcl_id):
return self._series_service.get_qcl_by_id(qcl_id)
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):
print 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 DataValuesEdit 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):
print points
query = "INSERT INTO DataValuesEdit (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()
def delete_points(self):
query = "DELETE FROM DataValuesEdit 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()
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 DataValuesEdit 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 DataValuesEdit 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 DataValuesEdit 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()
def save(self, var=None, method=None, qcl=None):
dvs = []
is_new_series = False
if var is not None:
self._cursor.execute("UPDATE DataValuesEdit SET VariableID = %s" %
(var.id))
is_new_series = True
if method is not None:
print method.id
self._cursor.execute("UPDATE DataValuesEdit SET MethodID = %s" %
(method.id))
is_new_series = True
# check that the code is not zero
if qcl is not None and qcl.code > 0:
self._cursor.execute(
"UPDATE DataValuesEdit SET QualityControlLevelID = %s" %
(qcl.id))
is_new_series = True
else:
raise ValueError("Quality Control Level cannot be zero")
self._cursor.execute(
"SELECT * FROM DataValuesEdit 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)
if is_new_series:
series = series_module.copy(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)
if not is_new_series:
# delete old dvs
old_dvs = series.data_values
self._series_service.delete_dvs(old_dvs)
series.data_values = dvs
self._series_service.save_series(series, dvs)
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,
sample_medium, value_type, is_regular, time_support,
time_unit, data_type, general_category, no_data_value):
return self._series_service.create_variable(
code, name, speciation, variable_unit, sample_medium, value_type,
is_regular, time_support, time_unit, data_type, general_category,
no_data_value)
def reconcile_dates(self, parent_series_id):
# append new data to this series
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 DataValuesEdit
(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))
""")
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 def get_vertical_datum_cvs(self): session = self._session_factory.get_session() result = session.query(VerticalDatumCV).order_by(VerticalDatumCV.term).all() session.close() return result def get_samples(self): session = self._session_factory.get_session() result = session.query(Sample).order_by(Sample.lab_sample_code).all() session.close() return result def get_qualifiers(self): result = self._edit_session.query(Qualifier).order_by(Qualifier.code).all() return result def create_qualifier(self, qualifier): session = self._session_factory.get_session() self._edit_session.add(qualifier) self._edit_session.commit() def get_site_type_cvs(self): session = self._session_factory.get_session() result = session.query(SiteTypeCV).order_by(SiteTypeCV.term).all() session.close() return result def get_variable_name_cvs(self): session = self._session_factory.get_session() result = session.query(VariableNameCV).order_by(VariableNameCV.term).all() session.close() return result def get_offset_type_cvs(self): session = self._session_factory.get_session() result = session.query(OffsetType).order_by(OffsetType.id).all() session.close() return result def get_speciation_cvs(self): session = self._session_factory.get_session() result = session.query(SpeciationCV).order_by(SpeciationCV.term).all() session.close() return result def get_sample_medium_cvs(self): session = self._session_factory.get_session() result = session.query(SampleMediumCV).order_by(SampleMediumCV.term).all() session.close() return result def get_value_type_cvs(self): session = self._session_factory.get_session() result = session.query(ValueTypeCV).order_by(ValueTypeCV.term).all() session.close() return result def get_data_type_cvs(self): session = self._session_factory.get_session() result = session.query(DataTypeCV).order_by(DataTypeCV.term).all() session.close() return result def get_general_category_cvs(self): session = self._session_factory.get_session() result = session.query(GeneralCategoryCV).order_by(GeneralCategoryCV.term).all() session.close() return result def get_censor_code_cvs(self): session = self._session_factory.get_session() result = session.query(CensorCodeCV).order_by(CensorCodeCV.term).all() session.close() return result def get_sample_type_cvs(self): session = self._session_factory.get_session() result = session.query(SampleTypeCV).order_by(SampleTypeCV.term).all() session.close() return result def get_units(self): session = self._session_factory.get_session() result = self._edit_session.query(Unit).all() session.close() return result def get_unit_by_name(self, unit_name): session = self._session_factory.get_session() result = self._edit_session.query(Unit).filter_by(name=unit_name).one() session.close() return result def get_unit_by_id(self, unit_id): session = self._session_factory.get_session() result = self._edit_session.query(Unit).filter_by(id=unit_id).one() session.close() return result