def get_measurement(self, system_uid, measurement, created_at): conn = self.getDBConn() cursor = conn.cursor() table_name = get_measurement_table_name(measurement, system_uid) # Declare and initialize the payload payload = {'created_at': '', 'value': ''} try: # Retrieve records in time order, descending (most recent first) query = "SELECT * FROM %s WHERE time = %%s " % table_name cursor.execute(query, [created_at]) data = cursor.fetchone() if not data or len(data) == 0: raise ValueError("No data found for the given creation time.") payload['created_at'] = data[0].strftime('%Y-%m-%d %H:%M:%S') payload['value'] = float(data[1]) except Exception as e: print e return {'error': str(e)} finally: cursor.close() conn.close() return payload
def create_measurement_query(system, measurements, time_ranges): query = "" for i in range(0, len(measurements)): query += "select \'" + measurements[i] + "\'," + measurements[i] + ".time as time," \ + measurements[i] + ".value as value from " \ + get_measurement_table_name(measurements[i], system) \ + " " + measurements[i] + " where " if time_ranges: for j in range(0, len(time_ranges)): start_time = "'" + str(time_ranges[j][0]) + "'" end_time = "'" + str(time_ranges[j][1]) + "'" if j == len(time_ranges) - 1: query += "( time > " + start_time + " and time < " + end_time + ")" else: query += "( time > " + start_time + " and time < " + end_time + ") or" else: query += "1=1" if i == len(measurements) - 1: query += " order by time " else: query += " union " return query
def get_system_measurement(self, system_uid, measurement_id): # Encode the measurement_id measurement_id_encoded = measurement_id.encode('utf-8') # Check if the encoded value is a valid number is_given_measurement_id_digit = measurement_id_encoded.isdigit() # If the given measurement_id is a valid number, convert it to an integer, # Else it is an invalid measurement_id if is_given_measurement_id_digit: measurement_id_encoded_int = int(measurement_id_encoded) else: return json.dumps({'error': 'Invalid measurement id'}) # Fetch the measurement information of all the measurements measurement_info = self.mea.get_all_measurement_info() # List that stores all the measurement ids measurement_id_list = [] # For each measurement in the measure_info, fetch the measurement id and # append to the measurement_id_list for each_measurement in measurement_info: measurement_id_info = each_measurement[0] measurement_id_list.append(measurement_id_info) # If the given measurement_id (encoded) in present in the measurement_id_list, # then it is a valid id. Else it is an invalid id. if measurement_id_encoded_int in measurement_id_list: # Fetch the name of the measurement measurement = self.mea.get_measurement_name(measurement_id) else: return json.dumps({'error': 'Invalid measurement id'}) if 'error' in measurement: return json.dumps(measurement) # Number of latest recorded to be returned # Light: 7 # All other measurements: 1 if measurement[0] == 'light': num_of_records = 7 else: num_of_records = 1 # Create the name of the table table_name = get_measurement_table_name(measurement[0], system_uid) # Get the latest value recorded in that table result = self.mea.get_latest_value(table_name, num_of_records) if 'error' in result: return json.dumps(result) values = [] for result_temp in result: measurement_value = decimal.Decimal(result_temp[1]) if measurement_value.is_normal(): normalized_measurement_value = measurement_value else: normalized_measurement_value = measurement_value.normalize() normalized_measurement_value_reduced_decimal = "%.2f" % normalized_measurement_value values_temp = { 'time': str(result_temp[0]), 'value': str(normalized_measurement_value_reduced_decimal) } values.append(values_temp) obj = {'system_uid': system_uid, 'records': values} return json.dumps(obj)
def get_system_measurements(self, system_uid): """Retrieve the latest measurements of the specified system. Returns a Python dictionary""" # Fetch names of all the measurements names = self.mea.get_all_measurement_names() # Create a list to store the name, latest time and value of all the measurements x = [] # For each measurement for measurement_name, full_name in names: # Fetch the name of the measurement using regular expression if measurement_name != 'time': # As each measurement of a system has a table on it's own, # we need to create the name of each table. table_name = get_measurement_table_name( measurement_name, system_uid) num_of_records = 1 # Get the latest value stored in the table value = self.mea.get_latest_value(table_name, num_of_records) if 'error' in value: return value # Append the value to the latest_value[] list if len(value) == 1: value_temp = value[0] measurement_value = decimal.Decimal(value_temp[1]) if measurement_value.is_normal(): normalized_measurement_value = measurement_value else: normalized_measurement_value = measurement_value.normalize( ) normalized_measurement_value_reduced_decimal = "%.2f" % normalized_measurement_value temp = { 'name': measurement_name, 'full_name': full_name, 'time': str(value_temp[0]), 'value': str(normalized_measurement_value_reduced_decimal), 'updated_at': value_temp[2].strftime('%Y-%m-%d %H:%M:%S') if value_temp[2] else None } else: temp = { 'name': measurement_name, 'full_name': full_name, 'time': None, 'value': None, 'updated_at': None } x.append(temp) return {'system_uid': str(system_uid), 'measurements': x}
def get_all_measurements(self, system_uid, measurement, page): conn = self.getDBConn() cursor = conn.cursor() # Prep some values for the query items_per_page = 20 page = int(page) start = items_per_page * (page - 1) table_name = get_measurement_table_name(measurement, system_uid) # Declare and initialize the payload payload = {'data': []} try: # Retrieve records in time order, descending (most recent first) query = "SELECT * FROM %s ORDER BY time DESC LIMIT %%s OFFSET %%s " % table_name cursor.execute(query, (items_per_page, start)) result = list(cursor.fetchall()) # Figure out total number of pages. We return this with every request total_count = self.get_data_count(system_uid, measurement) total_pages = int(floor(total_count['count'] / items_per_page) + 1) # Error if page not in range if total_pages < page: raise ValueError( 'The provided page number ' + str(page) + ' is out of range. There allowable range is [1,%d]' % total_pages) # Add the page count payload['total_pages'] = total_pages # Loop through the query result, coerce the result strings to datetime and float # Turn the results into dicts of form {time: <datetime>, value: <float>} for r in result: updated_at = r[2].strftime( '%Y-%m-%d %H:%M:%S') if r[2] else None payload['data'].append({ 'time': r[0].strftime('%Y-%m-%d %H:%M:%S'), 'value': float(r[1]), 'updated_at': updated_at }) except Exception as e: print e return {'error': str(e)} finally: cursor.close() conn.close() return payload
def update_existing_measurement(self, system_uid, measurement, data): conn = self.getDBConn() cursor = conn.cursor() table_name = get_measurement_table_name(measurement, system_uid) try: query = "UPDATE %s SET time=%%s, value=%%s, updated_at=%%s WHERE time=%%s" % table_name result = cursor.execute(query, (data['time'], data['value'], data['updated_at'], data['time'])) conn.commit() except Exception as e: print e return {'error': str(e)} finally: cursor.close() conn.close() return 'Successfully updated ' + str(result) + ' records(s)!'
def get_data_count(self, system_uid, measurement): conn = self.getDBConn() cursor = conn.cursor() table_name = get_measurement_table_name(measurement, system_uid) payload = {} try: query = "SELECT COUNT(*) FROM %s" % table_name cursor.execute(query) result = cursor.fetchone() payload['count'] = result[0] except Exception as e: return {'error': e.args[1]} finally: cursor.close() conn.close() return payload
def create_system(self, system): conn = self.dbconn() cursor = conn.cursor() userID = system['userID'] name = system['name'] systemUID = str(uuid.uuid1().hex) startDate = system['startDate'] techniqueID = system['techniqueID'] location = system['location'] locationLat = location['lat'] locationLng = location['lng'] gbMedia = system['gbMedia'] crops = system['crops'] organisms = system['organisms'] status = system['status'] systemID = 0 query1 = ( 'INSERT INTO systems (user_id, name, system_uid, start_date, aqx_technique_id, location_lat, location_lng, status)' 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)') values1 = (userID, name, systemUID, startDate, techniqueID, locationLat, locationLng, status) query2 = 'INSERT INTO system_gb_media (system_id,gb_media_id) VALUES (%s, %s)' query3 = 'INSERT INTO system_aquatic_organisms VALUES (%s, %s, %s)' query4 = 'INSERT INTO system_crops VALUES (%s, %s, %s)' # note: database table column sys_status_id is set to 100 on default query5 = 'INSERT INTO system_status (system_uid, start_time, end_time) VALUES (%s, %s, "2030-12-31 23:59:59")' values5 = (systemUID, startDate) # MySQL unfortunately has versions supporting different non-primary key timestamps # our production system (which runs on CentOS with ancient MySQL) does not accept # the default value, the newer development system requires a default value create_single_query1 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP)' create_single_query2 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP default current_timestamp)' # create multi table if the measurement type is marked as such create_multi_query1 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP, foreign_key integer not null)' create_multi_query2 = 'CREATE TABLE %s (time TIMESTAMP PRIMARY KEY NOT NULL, value DECIMAL(13,10) NOT NULL, updated_at TIMESTAMP default current_timestamp, foreign_key integer not null)' try: cursor.execute('select name, multi_table from measurement_types where id < 100000') meas_types = {name: multi_table for name, multi_table in cursor.fetchall()} cursor.execute(query1, values1) systemID = cursor.lastrowid for medium in gbMedia: cursor.execute(query2, (systemID, medium['ID'])) for organism in organisms: if organism['ID'] > 0: cursor.execute(query3, (systemID, organism['ID'], organism['count'])) for crop in crops: if crop['ID'] > 0: cursor.execute(query4, (systemID, crop['ID'], crop['count'])) cursor.execute(query5, values5) # make sure the query is creating the table in either production system # or dev system format for name, multi_table in meas_types.items(): table_name = get_measurement_table_name(name, systemUID) try: if multi_table is None: cursor.execute(create_single_query1 % table_name) else: cursor.execute(create_multi_query1 % table_name) except: if multi_table is None: cursor.execute(create_single_query2 % table_name) else: cursor.execute(create_multi_query2 % table_name) conn.commit() finally: cursor.close() conn.close() return {'userID': userID, 'systemID': systemID, 'systemUID': systemUID}