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
