class MySQL(object): INSTANCE = None def __new__(cls, *args, **kwargs): if not cls.INSTANCE: cls.INSTANCE = super(MySQL, cls).__new__( cls, *args, **kwargs) return cls.INSTANCE def __init__(self): '''Check all global variables have been initialized''' # Configuration Manager self.CONFIG = ConfigManager() # Instantiate logging logging.config.fileConfig(self.CONFIG.getConfigFilePath()) self.LOGGER = logging.getLogger("energyathome.datalogger.database.mysql") # Connection variable self.CONNECTION = None # Number of transaction retries before failing self.RETRIES = self.CONFIG.getIntConfig("Database", "retries") # Number in seconds to wait before re-attempting failed transaction self.WAIT = self.CONFIG.getFloatConfig("Database", "wait") sys.path.append("..") if self.CONFIG is None: print "Unable to get configuration manager. Exit" sys.exit(1) if self.RETRIES is None: print "No DB retry value set. Using default value" self.RETRIES = 3 if self.WAIT is None: print "No DB wait value set. Using default value" self.WAIT = 60 # Connect to MySQL using settings from DatabaseConfig.py def connect(self): '''Connect to database''' attempts = 1 databaseSettings = self.CONFIG.getConfigCategory("Database") if self.LOGGER is None: __init__() while attempts <= self.CONFIG.getIntConfig("Database", "retries") and self.CONNECTION is None: try: self.LOGGER.info("Attempting DB connection") self.CONNECTION = MySQLdb.connect( host=databaseSettings["url"], user=databaseSettings["username"], passwd=databaseSettings["password"], db=databaseSettings["database"]) # Turn ping on to automatically connect to DB when idle and disconnects self.CONNECTION.ping(True) self.LOGGER.info("DB connection successful") except(MySQLdb.Error) as e: # Failed to connect self.LOGGER.error("Unable to connect to database:'" + str(databaseSettings["url"]) + "|" + str(e.args[0])) self.LOGGER.error("Attempt " + str(attempts) + " failed. Retrying in " + str(databaseSettings["wait"]) + " secs") # Wait for n seconds before attempting to reconnect time.sleep(self.WAIT) # Continue in loop pass # Increment number of times it tried to connect attempts += 1 # If CONNECTION is None then it was unable to connect if self.CONNECTION is None: raise ConnectionException("Database connection error") # Close DB CONNECTION def disconnect(self): '''Close CONNECTION to database''' try: # Only close CONNECTION if CONNECTION exists if self.CONNECTION is not None: self.LOGGER.info("Attempting to close DB connection") self.CONNECTION.close() self.CONNECTION = None self.LOGGER.info("Close DB connection successful") return True except MySQLdb.Error as e: self.LOGGER.error("Unable to disconnect from DB:") self.LOGGER.error(e.args[0], e.args[1]) return False # Execute query with no return results def executeNonUpdate(self, statement, values): '''Execute SQL with no return results. Accepts an SQL statement with %s parameters which will be replaced with the values parameter. If no %s is used, values should be set to None. Returns last inserted row ID reguardless of SQL type.''' id = None attempts = 1 while attempts <= self.RETRIES: try: cursor = self.CONNECTION.cursor() # Check if values need replacing in SQL statement if values is None: cursor.execute(statement) else: cursor.execute(statement, values) self.CONNECTION.commit() id = cursor.lastrowid cursor.close() # Exit retry loop and return value break except MySQLdb.Error as e: print "Caught error:" print e.args[0], e.args[1] print "Retrying in " + str(self.WAIT) + " secs" # Increment number of times it has tried to execute this function attempts += 1 time.sleep(self.WAIT) try: # Try disconnecting first to avoid holding a CONNECTION open self.disconnect() # Try reconnecting to DB self.connect() except MySQLdb.Error as e: # Ignore connect error and gracefully exit function print "DB reconnect failed:" print e.args[0], e.args[1] raise pass except AttributeError as ae: # Caught when an object such as the connection has not been instantiated. # This may happen if connection is lost whils trying to get a cursor raise ConnectionException("Attribute error in DB") # return id from insert return id # Execute query and return results def executeUpdate(self, statement, values): '''Execute query and return all results.''' attempts = 1 while attempts <= self.RETRIES: try: cursor = self.CONNECTION.cursor() # Check if values need replacing in SQL statement if values is None: cursor.execute(statement) else: cursor.execute(statement, values) results = cursor.fetchall() cursor.close() return results except MySQLdb.Error as e: print "Caught error:" print e.args[0], e.args[1] print "Retrying in " + str(self.WAIT) + " secs" # Increment number of times it has tried to execute this function attempts += 1 time.sleep(self.WAIT) try: # Try disconnecting first to avoid holding a CONNECTION open self.disconnect() # Try reconnecting to DB self.connect() except MySQLdb.Error as e: # Ignore connect error and gracefully exit function print "DB reconnect failed:" print e.args[0], e.args[1] raise except ConnectionException as ce: raise ce pass except AttributeError as ae: # Caught when an object such as the connection has not been instantiated. # This may happen if connection is lost whils trying to get a cursor raise ConnectionException("Attribute error in DB") # Fail after retry return None # Execute query and return one result def executeOneUpdate(self, statement, values): '''Execute SQL and returns one result. Accepts an SQL statement with %s parameters which will be replaced with the values parameter. If no %s is used, values should be set to None. Returns the first result only.''' attempts = 1 while attempts <= self.RETRIES: try: cursor = self.CONNECTION.cursor() # Check if values need replacing in SQL statement if values is None: cursor.execute(statement) else: cursor.execute(statement, values) result = cursor.fetchone() cursor.close() return result except MySQLdb.Error as e: print "Caught error:" print e.args[0], e.args[1] print "Retrying in " + str(self.WAIT) + " secs" # Increment number of times it has tried to execute this function attempts += 1 time.sleep(self.WAIT) try: # Try disconnecting first to avoid holding a CONNECTION open self.disconnect() # Try reconnecting to DB self.connect() except ConnectionException as ce: # Ignore connect error and gracefully exit function print "DB reconnect failed:" print e.args[0], e.args[1] raise ce except AttributeError as ae: # Caught when an object such as the connection has not been instantiated. # This may happen if connection is lost whils trying to get a cursor raise ConnectionException("Attribute error in DB") # Fail after retry return None
class CheckLiveTriggers: def __init__(self): # Instantiate config manager self.CONFIG = ConfigManager() # Get logger instance self.LOGGER = Debug.getLogger("energyathome.datalogger.datatrigger") def checkTriggers(self, historicalData): '''Check if data received meets any one trigger conditions. Returns true if it's met''' trigger = False # Get last recorded data point. Used for trigger information try: previousDataPoint = HistoricalData.getLastHistoricalData(historicalData) except ConnectionException as ce: raise ce # Check if there was data in DB if previousDataPoint is not None: try: # Get all channel data channel = "" for key, value in previousDataPoint.energy.iteritems(): channel += key + "=" + str(value) + "w " self.LOGGER.info("Last data point for " + previousDataPoint.name +\ " app_id=" + str(previousDataPoint.applianceId) + " type=" +\ str(previousDataPoint.sensorType) + " " + "at " +\ str(previousDataPoint.time) + " was " + channel +\ str(previousDataPoint.temperature) + "c") # Check timeout timeout = self.checkTimeTrigger(previousDataPoint) if timeout is True: trigger = True # Check energy variation energy = self.checkEnergyTrigger(historicalData, previousDataPoint) if energy is True: trigger = True # Check temperature variation temp = self.checkTemperatureTrigger(historicalData, previousDataPoint) if temp is True: trigger = True except AttributeError as ae: self.LOGGER.error("Caught Error:" + str(ae)) trigger = False else: # No previous data point found self.LOGGER.info("No data history for device " + historicalData.name +\ " on app_id=" + str(historicalData.applianceId) +\ " type=" + str(historicalData.sensorType)) trigger = True # Historial data existed but no conditions were met. return trigger def checkTimeTrigger(self, previousDataPoint): '''Returns true if time from last datapoint exceeded the maximum''' # Check timeout trigger condition first as it's most common condition to # trigger a save point. Ignoring device time so using system time if (datetime.today() - previousDataPoint.time) >= timedelta(seconds = self.CONFIG.getIntConfig("Trigger", "timeout")): self.LOGGER.info("Timeout trigger condition met with " +\ str(datetime.today() - previousDataPoint.time) + " delta") return True else: return False def checkEnergyTrigger(self, historicalData, previousDataPoint): '''Returns true if the energy difference from the previous value is exceeded''' # Check energy variation. Get absolute value reguardless of positive / negative value # Must loop through each channel for key, value in historicalData.energy.iteritems(): if previousDataPoint.energy.get(key, None) is not None: # Calculate the difference from last data point and the new one energyDiff = math.fabs(value) - previousDataPoint.energy[key] if energyDiff >= self.CONFIG.getFloatConfig("Trigger", "energyvariation"): self.LOGGER.info("Energy trigger condition met with " + str(key) + " " +\ str(energyDiff) + "w delta") return True else: # energy value did not exist in previous reading return True # Deliberate fall through to return false return False def checkTemperatureTrigger(self, historicalData, previousDataPoint): '''Returns true if the temperature difference from the previous value is exceeded''' # Check temperature variation. Get absolute value reguardless of positive / negative value tempDiff = math.fabs(historicalData.temperature - previousDataPoint.temperature) if tempDiff > self.CONFIG.getFloatConfig("Trigger", "temperatureVariation"): self.LOGGER.info("Temperature trigger condition met with " +\ str(tempDiff) + "c delta") return True else: return False