def populateRoomTypes(session): """Add Some Default Room Types :param session: Session to be used if not the global database session """ LOG.debug("Populating Room Types") if not session: session = meta.Session() roomTypes = [ ["Bedroom", ["Master Bedroom", "Second Bedroom", "Third Bedroom"]], ["Living Area", ["Living Room", "Dining Room"]], ["Wet Room", ["Bathroom", "WC", "Kitchen"]], [ "Unocupied", ["Hallway", "Upstairs Hallway", "Utility Room", "Spare Room"] ] ] with transaction.manager: for roomType, rooms in roomTypes: theType = session.query(RoomType).filter_by(name=roomType).first() if theType is None: theType = RoomType(name=roomType) session.add(theType) session.flush() for item in rooms: theQry = session.query(Room).filter_by(name=item).first() if theQry is None: session.add(Room(name=item, roomTypeId=theType.id)) session.flush() session.flush()
def calibratePairs(theQuery): """Generator object to calibrate readings and return in JSON friendly format :param theQuery: SQLA query object containing readings """ #Dictionary to hold all sensor paramters session = meta.Session() sensorParams = {} for reading in theQuery: theSensor = sensorParams.get((reading.nodeId, reading.typeId), None) LOG.debug("Original Reading {0} Sensor is {1}".format( reading, theSensor)) if not theSensor: theSensor = session.query(sensor.Sensor) theSensor = theSensor.filter_by( nodeId=reading.nodeId, sensorTypeId=reading.typeId).first() if theSensor is None: theSensor = sensor.Sensor(calibrationSlope=1.0, calibrationOffset=0.0) sensorParams[(reading.nodeId, reading.typeId)] = theSensor theTime = time.mktime(reading.time.timetuple()) * 1000.0 #theTime = reading.time.isoformat() theValue = (theSensor.calibrationSlope * reading.value) theValue += theSensor.calibrationOffset #theValue = reading.value yield (theTime, theValue)
def getCalibValues(self): """ Return the Reading as a tuple of (time,calibratedValue) This returns the same as GetRawValues but will calibrate the data against stored values .. warning: This will issue an SQL statement for each reading we call the function on, Therefore it is only useful as a shortcut when dealing with a limited amount of readings :return DateTime time: Time that this reading was taken :return Float value: Value of the reading at this Time Calibrated against the sensor values """ #pass #Find the Sensor session = meta.Session() thesensor = session.query(sensor.Sensor) thesensor = thesensor.filter_by(sensorTypeId=self.typeId, nodeId=self.nodeId).first() if thesensor is None: return self.time, self.value #Otherwise Calibrate value = (self.value * thesensor.calibrationSlope) value += thesensor.calibrationOffset return (self.time, value)
def calibrateReadings(theQuery): """Generator object to calibate all readings, hopefully this gathers all calibration based readings into one area :param theQuery: SQLA query object containing Reading values""" #Dictionary to hold all sensor paramters session = meta.Session() sensorParams = {} for reading in theQuery: theSensor = sensorParams.get((reading.nodeId, reading.typeId), None) LOG.debug("Orig Reading {0} Sensor is {1}".format(reading, theSensor)) if not theSensor: theSensor = session.query(sensor.Sensor).filter_by( nodeId=reading.nodeId, sensorTypeId=reading.typeId).first() if theSensor is None: theSensor = sensor.Sensor(calibrationSlope=1.0, calibrationOffset=0.0) sensorParams[(reading.nodeId, reading.typeId)] = theSensor #Then add the offset etc cReading = Reading( time=reading.time, nodeId=reading.nodeId, typeId=reading.typeId, locationId=reading.locationId, value=theSensor.calibrationOffset + (theSensor.calibrationSlope * reading.value), ) yield cReading
def init_data(session=False): """Populate the database with some initial data :param session: Session to use if not the default """ if not session: session = meta.Session() log.info("Populating Initial Data using session {0}".format(session)) populateSummaryTypes(session = session) populateSensorTypes(session = session) populateRoomTypes(session = session) populateCalibration(session = session)
def init_data(session=False, docalib=False): """Populate the database with some initial data :param session: Session to use if not the default """ if not session: session = meta.Session() populateSensorTypes(session=session) populateNodeTypes(session=session) populateRoomTypes(session=session) if docalib: populateCalibration(session=session) LOG.debug("Database Population Complete")
def calibPandas(theQuery): """Generator object to calibate all readings, hopefully this gathers all calibration based readings into one area :param theQuery: SQLA query object containing Reading values""" #Dictionary to hold all sensor paramters session = meta.Session() sensorParams = {} for reading in theQuery: theSensor = sensorParams.get((reading.nodeId, reading.typeId), None) #LOG.debug("Original Reading {0} Sensor is {1}".format(reading,theSensor)) if not theSensor: #theSensor = "FOO" theSensor = session.query(sensor.Sensor).filter_by( nodeId=reading.nodeId, sensorTypeId=reading.typeId).first() if theSensor is None: theSensor = sensor.Sensor(calibrationSlope=1.0, calibrationOffset=0.0) sensorParams[(reading.nodeId, reading.typeId)] = theSensor #Then add the offset etc cReading = { "time": reading.time, "nodeId": reading.nodeId, "typeId": reading.typeId, "locationId": reading.locationId, "locationStr": reading.location.room.name, "value": theSensor.calibrationOffset + (theSensor.calibrationSlope * reading.value), "location": "Node {0}: {1} {2}".format(reading.nodeId, reading.location.room.name, reading.sensorType.name), } yield cReading
def timeit(*args, **kwargs): session = meta.Session() starttime = time.time() result = function(*args, **kwargs) endtime = time.time() total = endtime - starttime timeobject = Timings(function=str(function.__name__), text=theText, args=str(args), kwargs=str(kwargs), time=total) session.add(timeobject) session.flush() # #session.close() LOG.debug(timeobject) #LOG.info(timeobject) return result
def populateSummaryTypes(session=False): log.info("Populating SensorTypes") if not session: session = meta.Session() #Check if the Daly Count is in the DB #typeExists = session.query(SummaryType).filter_by(name="Day Count").first() #if typeExists is None: log.debug("Adding Summary Types") theType = SummaryType(id=1, name="Day Count") session.merge(theType) theType = SummaryType(id=2, name="Day Count (Clean)") session.merge(theType) theType = SummaryType(id=3, name="Yield") session.merge(theType) theType = SummaryType(id=4, name="Yield (Clean)") session.merge(theType) theType = SummaryType(id=5, name="Min") session.merge(theType) theType = SummaryType(id=6, name="Max") session.merge(theType) theType = SummaryType(id=7, name="Avg") session.merge(theType) theType = SummaryType(id=8, name="Daily KwH") session.merge(theType) theType = SummaryType(id=9, name="Daily KwH/DD") session.merge(theType) session.flush() session.commit() session.close()
def calibJSON(theQuery): """Generator object to calibate all readings, hopefully this gathers all calibration based readings into one area :param theQuery: SQLA query object containing Reading values""" #Dictionary to hold all sensor paramters session = meta.Session() sensorParams = {} for reading in theQuery: theSensor = sensorParams.get((reading.nodeId, reading.typeId), None) if not theSensor: #theSensor = "FOO" theSensor = session.query(sensor.Sensor) theSensor = theSensor.filter_by( nodeId=reading.nodeId, sensorTypeId=reading.typeId).first() if theSensor is None: theSensor = sensor.Sensor(calibrationSlope=1.0, calibrationOffset=0.0) sensorParams[(reading.nodeId, reading.typeId)] = theSensor #Then add the offset etc cvalue = (theSensor.calibrationSlope * reading.value) cvalue += theSensor.calibrationOffset cReading = { "time": reading.time.isoformat(), "nodeId": reading.nodeId, "typeId": reading.typeId, "locationId": reading.locationId, "value": cvalue } yield cReading
def populateNodeTypes(session=False): """Populate the database with default node types, if they do not already exist. (Added due to alembic revision 1f9a02a1b28 """ LOG.debug("Populating SensorTypes") if not session: session = meta.Session() # nodelist = [] nodelist = [ { 'id': 0, 'name': "Base", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 1, 'name': "Current Cost", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,5' }, { 'id': 2, 'name': "CO2", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '63,4' }, { 'id': 3, 'name': "Air Quality", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '255,4' }, { 'id': 4, 'name': "Heat Meter", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 5, 'name': "EnergyBoard", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 6, 'name': "TempADC0 Node", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 7, 'name': "Gas Node", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 8, 'name': "Window Sensor", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 10, 'name': "ClusterHead CO2", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 11, 'name': "ClusterHead AQ", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 12, 'name': "ClusterHead CC", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, { 'id': 13, 'name': "ClusterHead BB", 'time': "2011-07-10 00:00:00", 'seq': 1, 'updated_seq': 0., 'period': 307200., 'blink': 0., 'configured': '31,4' }, ] with transaction.manager: for item in nodelist: thisNode = NodeType() thisNode.from_dict(item) #Dict based update session.merge(thisNode) # with transaction.manager: # for item in nodelist: # LOG.debug("Adding NodeType {0}".format(item.name)) # session.merge(item) session.flush()
def populateSensorTypes(session=False): """Populate the database with default sensing types, if they do not already exist. """ LOG.debug("Populating SensorTypes") if not session: session = meta.Session() sensorList = [ SensorType(id=0, name="Temperature", code="T", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=1, name="Delta Temperature", code="dT", units="deg.C/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=2, name="Humidity", code="RH", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=3, name="Delta Humidity", code="dRH", units="%/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=4, name="Light PAR", code="PAR", units="Lux", c0=0., c1=1., c2=0., c3=0.), SensorType(id=5, name="Light TSR", code="TSR", units="Lux", c0=0., c1=1., c2=0., c3=0.), SensorType(id=6, name="Battery Voltage", code="BAT", units="V", c0=0., c1=1., c2=0., c3=0.), SensorType(id=7, name="Delta Battery Voltage", code="dBT", units="V/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=8, name="CO2", code="CO2", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=9, name="Air Quality", code="AQ", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=10, name="VOC", code="VOC", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=11, name="Power", code="POW", units="W", c0=0., c1=1., c2=0., c3=0.), SensorType(id=12, name="Heat", code="HET", units="W", c0=0., c1=1., c2=0., c3=0.), SensorType(id=13, name="Duty cycle", code="DUT", units="ms", c0=0., c1=1., c2=0., c3=0.), SensorType(id=14, name="Error", code="ERR", units="", c0=0., c1=1., c2=0., c3=0.), SensorType(id=15, name="Power Min", code="PMI", units="w", c0=0., c1=1., c2=0., c3=0.), SensorType(id=16, name="Power Max", code="PMA", units="w", c0=0., c1=1., c2=0., c3=0.), SensorType(id=17, name="Power Consumption", code="CON", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=18, name="Heat Energy", code="HEN", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=19, name="Heat Volume", code="HVO", units="L", c0=0., c1=1., c2=0., c3=0.), #!-- RENUMBERED --- SensorType(id=20, name="Delta CO2", code="dCO2", units="ppm/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=21, name="Delta VOC", code="dVOC", units="ppm/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=22, name="Delta AQ", code="dAQ", units="v/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=23, name="Temperature Health", code="TH", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=24, name="Temperature Cold", code="TC", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=25, name="Temperature Comfort", code="TM", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=26, name="Temperature Warm", code="TW", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=27, name="Temperature Over", code="TO", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=28, name="Humidity Dry", code="HD", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=29, name="Humidity Comfort", code="HD", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=30, name="Humidity Damp", code="HD", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=31, name="Humidity Risk", code="HR", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=32, name="CO2 Acceptable", code="CA", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=33, name="CO2 Minor", code="Cmin", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=34, name="CO2 Medium", code="CMED", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=35, name="CO2 Major", code="CMAJ", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=36, name="VOC Acceptable", code="VA", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=37, name="VOC Poor", code="VP", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=38, name="AQ Acceptable", code="AA", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=39, name="AQ Poor", code="AP", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=40, name="Opti Smart Count", code="imp", units="imp"), SensorType(id=41, name="Temperature ADC0", code="T", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id="42", name="Delta Temperature ADC0", code="dT", units="deg.C/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=43, name="Gas Pulse Count", code="imp", units="imp", c0=0., c1=1., c2=0., c3=0.), SensorType(id=44, name="Delta Opti", code="imp", units="imp", c0=0., c1=1., c2=0., c3=0), SensorType(id=45, name="Window State", code="ste", units="ste", c0=0., c1=1., c2=0., c3=0.), SensorType(id=46, name="Black Bulb", code="bb", units="v", c0=0., c1=0., c2=0., c3=0.), SensorType(id=47, name="Delta Black Bulb", code="d/bb", units="v/s", c0=0., c1=0., c2=0., c3=0.), SensorType(id=99, name="Gas Consumption", code="Gas", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=102, name="Outside Temperature", code="ws_temp_out", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=103, name="Outside Humidity", code="ws_hum_out", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=104, name="WS Inside Temperature", code="ws_temp_in", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=105, name="WS Inside Humidity", code="ws_hum_in", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=106, name="Dew Point", code="ws_dew", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=107, name="Apparent Temperature", code="ws_apparent_temp", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=108, name="Wind Gust", code="ws_wind_gust", units="mph", c0=0., c1=1., c2=0., c3=0.), SensorType(id=109, name="Average Wind Speed", code="ws_wind_ave", units="mph", c0=0., c1=1., c2=0., c3=0.), SensorType(id=110, name="Wind Direction", code="ws_wind_dir", units="", c0=0., c1=1., c2=0., c3=0.), SensorType(id=111, name="Wind Chill", code="ws_wind_chill", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=112, name="Rain Fall", code="ws_rain", units="mm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=113, name="Absolute Pressure", code="ws_abs_pressure", units="hpa", c0=0., c1=1., c2=0., c3=0.), ] with transaction.manager: for item in sensorList: LOG.debug("Adding Sensor {0}".format(item.name)) session.merge(item) session.flush()
def populateSensorTypes(session = False): """Populate the database with default sensing types, if they do not already exist. """ log.info("Populating SensorTypes") if not session: session = meta.Session() sensorList = [SensorType(id=0,name="Temperature", code="T", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=1,name="Delta Temperature", code="dT", units="deg.C/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=2,name="Humidity", code="RH", units="%", c0=0., c1=1., c2=0., c3=0.), SensorType(id=3,name="Delta Humidity", code="dRH", units="%/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=4,name="Light PAR", code="PAR", units="Lux", c0=0., c1=1., c2=0., c3=0.), SensorType(id=5,name="Light TSR", code="TSR", units="Lux", c0=0., c1=1., c2=0., c3=0.), SensorType(id=6,name="Battery Voltage", code="BAT", units="V", c0=0., c1=1., c2=0., c3=0.), SensorType(id=7,name="Delta Battery Voltage", code="dBT", units="V/s", c0=0., c1=1., c2=0., c3=0.), SensorType(id=8,name="CO2", code="CO2", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=9,name="Air Quality", code="AQ", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=10,name="VOC", code="VOC", units="ppm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=11,name="Power", code="POW", units="W", c0=0., c1=1., c2=0., c3=0.), SensorType(id=12,name="Heat", code="HET", units="W", c0=0., c1=1., c2=0., c3=0.), SensorType(id=13,name="Duty cycle", code="DUT", units="ms", c0=0., c1=1., c2=0., c3=0.), SensorType(id=14, name="Error", code="ERR", c1 = 1.0), SensorType(id=15,name="Power Min", code="PMI", units="w", c0=0., c1=1., c2=0., c3=0.), SensorType(id=16,name="Power Max", code="PMA", units="w", c0=0., c1=1., c2=0., c3=0.), SensorType(id=17,name="Power Consumption", code="CON", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=18,name="Heat Energy", code="HEN", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=19,name="Heat Volume", code="HVO", units="L", c0=0., c1=1., c2=0., c3=0.), SensorType(id=20,name="Power pulses", code="POP", units="p", c0=0., c1=1., c2=0., c3=0.), SensorType(id=21,name="Window Temperature 1", code="WT1", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=22,name="Window Temperature 2", code="WT2", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=23,name="Black Bulb", code="BBT", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=50,name="Plogg Power", code="plogg_kwh", units="kWh"), SensorType(id=51,name="Plogg Current", code="plogg_a", units="A"), SensorType(id=51,name="Plogg Wattage", code="plogg_w", units="W"), SensorType(id=99,name="Gas Consumption", code="Gas", units="kWh", c0=0., c1=1., c2=0., c3=0.), SensorType(id=102,name="Outside Temperature", code="ws_temp_out", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=103,name="Outside Humidity", code="ws_hum_out", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=104,name="WS Inside Temperature", code="ws_temp_in", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=105,name="WS Inside Humidity", code="ws_hum_in", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=106,name="Dew Point", code="ws_dew", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=107,name="Apparent Temperature", code="ws_apparent_temp", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=108,name="Wind Gust", code="ws_wind_gust", units="mph", c0=0., c1=1., c2=0., c3=0.), SensorType(id=109,name="Average Wind Speed", code="ws_wind_ave", units="mph", c0=0., c1=1., c2=0., c3=0.), SensorType(id=110,name="Wind Direction", code="ws_wind_dir", units="", c0=0., c1=1., c2=0., c3=0.), SensorType(id=111,name="Wind Chill", code="ws_wind_chill", units="deg.C", c0=0., c1=1., c2=0., c3=0.), SensorType(id=112,name="Rain Fall", code="ws_rain", units="mm", c0=0., c1=1., c2=0., c3=0.), SensorType(id=113,name="Absolute Pressure", code="ws_abs_pressure", units="hpa", c0=0., c1=1., c2=0., c3=0.)] for item in sensorList: session.merge(item) session.flush() session.commit() session.close()