def setUp(self):
self.logger = SEKLogger(__name__, 'DEBUG')
self.configer = SIConfiger()
self.conn = SEKDBConnector(
dbName=self.configer.configOptionValue('Database', 'db_name'),
dbHost=self.configer.configOptionValue('Database', 'db_host'),
dbPort=self.configer.configOptionValue('Database', 'db_port'),
dbUsername=self.configer.configOptionValue('Database',
'db_username'),
dbPassword=self.configer.configOptionValue(
'Database', 'db_password')).connectDB()
self.cursor = self.conn.cursor()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.inserter = SingleFileLoader('data/test-meter/log.csv')
self.data = '"2014-07-12 16:22:30",0,,,1187488464896.00,' \
'2322185846784.00,1134697381888.00,35184644096.00,' \
'290857353216.00,10133100822528.00,367.13,' \
'-17660932096.00,1078.01,17660934144.00,-7.86,1.80,8.06,' \
'-0.97,244.01,122.00,32.93,60.01,-7.09,1.42,7.24,8.06,' \
'3.34,8.35,-40.18,-5.68,40.52,516.72,403.12,0,' \
'8797179904.00,47518.67,0,86.03,50.23,4198.40,' \
'281475022848.00,2251868602368.00,0,6820.01,' \
'8796095488.00,0,178.83,188.30,0,620.07,505.19,' \
'288230389841920.02,12668.18,68729384.00,0,-3.68,-4.18,,' \
'1.00,0.79,,3.81,4.25,,-0.97,-0.98,,244.01,,,121.54,' \
'122.46,,31.28,34.59,'
self.testMeterName = 'test-meter'
def __init__(self):
"""
Constructor.
"""
self._config = ConfigParser.ConfigParser()
self.logger = SEKLogger(__name__, 'INFO')
self.fileUtil = SEKFileUtil()
self.dbUtil = SEKDBUtil()
self.cursor = None
configFilePath = '~/.smart-inverter.cfg'
if self.fileUtil.isMoreThanOwnerReadableAndWritable(
os.path.expanduser(configFilePath)):
self.logger.log(
"Configuration file permissions are too permissive. Operation "
"will not continue.", 'error')
sys.exit(-1)
try:
self._config.read(['site.cfg', os.path.expanduser(configFilePath)])
except:
self.logger.log(
"Critical error: The data in {} cannot be "
"accessed successfully.".format(configFilePath), 'ERROR')
sys.exit(-1)
def setUp(self):
self.logger = SEKLogger(__name__,'DEBUG')
self.configer = SIConfiger()
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
self.cursor = self.conn.cursor()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.inserter = SingleFileLoader('data/test-meter/log.csv')
self.data = '"2014-07-12 16:22:30",0,,,1187488464896.00,' \
'2322185846784.00,1134697381888.00,35184644096.00,' \
'290857353216.00,10133100822528.00,367.13,' \
'-17660932096.00,1078.01,17660934144.00,-7.86,1.80,8.06,' \
'-0.97,244.01,122.00,32.93,60.01,-7.09,1.42,7.24,8.06,' \
'3.34,8.35,-40.18,-5.68,40.52,516.72,403.12,0,' \
'8797179904.00,47518.67,0,86.03,50.23,4198.40,' \
'281475022848.00,2251868602368.00,0,6820.01,' \
'8796095488.00,0,178.83,188.30,0,620.07,505.19,' \
'288230389841920.02,12668.18,68729384.00,0,-3.68,-4.18,,' \
'1.00,0.79,,3.81,4.25,,-0.97,-0.98,,244.01,,,121.54,' \
'122.46,,31.28,34.59,'
self.testMeterName = 'test-meter'
def __init__(self, filepath=''):
"""
Constructor.
:param testing: Flag indicating if testing mode is on.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.logger.log('making new db conn for filepath {}'.format(filepath),
SILENT)
sys.stdout.flush()
try:
self.conn = SEKDBConnector(
dbName=self.configer.configOptionValue('Database', 'db_name'),
dbHost=self.configer.configOptionValue('Database', 'db_host'),
dbPort=self.configer.configOptionValue('Database', 'db_port'),
dbUsername=self.configer.configOptionValue(
'Database', 'db_username'),
dbPassword=self.configer.configOptionValue(
'Database', 'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
# An empty file path is used during creating of meter table entries.
if filepath == '':
self.filepath = None
self.meterID = None
self.meterDataTable = None
else:
self.filepath = filepath
self.meterID = self.getOrMakeMeterID(self.meterName())
assert self.meterID is not None
self.meterDataTable = "MeterData_{}".format(self.meterName())
# @todo Test existence of meter data table.
self.timestampColumn = 0 # timestamp col in the raw data
self.exceptionCount = 0
def __init__(self, filepath = ''):
"""
Constructor.
:param testing: Flag indicating if testing mode is on.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.logger.log('making new db conn for filepath {}'.format(filepath), SILENT)
sys.stdout.flush()
try:
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
# An empty file path is used during creating of meter table entries.
if filepath == '':
self.filepath = None
self.meterID = None
self.meterDataTable = None
else:
self.filepath = filepath
self.meterID = self.getOrMakeMeterID(self.meterName())
assert self.meterID is not None
self.meterDataTable = "MeterData_{}".format(self.meterName())
# @todo Test existence of meter data table.
self.timestampColumn = 0 # timestamp col in the raw data
self.exceptionCount = 0
global COMMAND_LINE_ARGS
parser = argparse.ArgumentParser(
description = 'Perform insertion of data contained in multiple files '
'to the SI database.')
parser.add_argument('--basepath', required = True,
help = 'A base path from which to process data files.')
COMMAND_LINE_ARGS = parser.parse_args()
if __name__ == '__main__':
processCommandLineArguments()
tableBase = "MeterData"
pkey = 'meter_id, time_utc'
logger = SEKLogger(__name__, 'debug')
configer = SIConfiger()
dbUtil = SEKDBUtil()
conn = SEKDBConnector(
dbName = configer.configOptionValue('Database', 'db_name'),
dbHost = configer.configOptionValue('Database', 'db_host'),
dbPort = configer.configOptionValue('Database', 'db_port'),
dbUsername = configer.configOptionValue('Database', 'db_username'),
dbPassword = configer.configOptionValue('Database',
'db_password')).connectDB()
cursor = conn.cursor()
tableOwner = configer.configOptionValue('Database', 'table_owner')
for meterName in SIUtil().meters(basepath = COMMAND_LINE_ARGS.basepath):
logger.log('creating table {}'.format(tableBase + "_" + meterName))
sql = 'CREATE TABLE "{1}_{0}" ( CHECK ( meter_id = meter_id(\'{' \
'0}\'))) INHERITS ("{1}"); ALTER TABLE ONLY "{1}_{0}" ADD ' \
'CONSTRAINT "{1}_{0}_pkey" PRIMARY KEY ({3}); ALTER TABLE ONLY ' \
class SingleFileLoaderTester(unittest.TestCase):
def setUp(self):
self.logger = SEKLogger(__name__, 'DEBUG')
self.configer = SIConfiger()
self.conn = SEKDBConnector(
dbName=self.configer.configOptionValue('Database', 'db_name'),
dbHost=self.configer.configOptionValue('Database', 'db_host'),
dbPort=self.configer.configOptionValue('Database', 'db_port'),
dbUsername=self.configer.configOptionValue('Database',
'db_username'),
dbPassword=self.configer.configOptionValue(
'Database', 'db_password')).connectDB()
self.cursor = self.conn.cursor()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.inserter = SingleFileLoader('data/test-meter/log.csv')
self.data = '"2014-07-12 16:22:30",0,,,1187488464896.00,' \
'2322185846784.00,1134697381888.00,35184644096.00,' \
'290857353216.00,10133100822528.00,367.13,' \
'-17660932096.00,1078.01,17660934144.00,-7.86,1.80,8.06,' \
'-0.97,244.01,122.00,32.93,60.01,-7.09,1.42,7.24,8.06,' \
'3.34,8.35,-40.18,-5.68,40.52,516.72,403.12,0,' \
'8797179904.00,47518.67,0,86.03,50.23,4198.40,' \
'281475022848.00,2251868602368.00,0,6820.01,' \
'8796095488.00,0,178.83,188.30,0,620.07,505.19,' \
'288230389841920.02,12668.18,68729384.00,0,-3.68,-4.18,,' \
'1.00,0.79,,3.81,4.25,,-0.97,-0.98,,244.01,,,121.54,' \
'122.46,,31.28,34.59,'
self.testMeterName = 'test-meter'
def test_columns(self):
self.assertEquals(len(self.dataUtil.dbColumns), NUM_DB_COLS)
def test_insert_data(self):
self.logger.log('testing data insert')
self.assertTrue(self.inserter.insertData(self.data))
self.conn.commit()
def test_sql_formatted_values(self):
self.logger.log('data: {}'.format(
self.dataUtil.sqlFormattedValues(self.data)))
def test_meter_id(self):
self.logger.log('testing meter id')
meter_id = self.inserter.meterID(self.testMeterName)
self.logger.log('meter id {}'.format(meter_id))
self.assertTrue(isinstance(meter_id, (int, long)))
self.logger.log('getting meter id')
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{}\''.format(
self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=True)
if success:
result = self.cursor.fetchall()
self.assertEquals(1, len(result))
else:
self.logger.log('failed to retrieve meter id', 'error')
def test_meter_name(self):
"""
Test getting the meter name.
:return:
"""
self.assertEquals(self.inserter.meterName(), self.testMeterName)
def test_insert_data_from_file(self):
self.inserter.insertDataFromFile()
sql = 'SELECT * FROM "MeterData" WHERE meter_id = {}'.format(
self.inserter.meterID(self.testMeterName))
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=True)
if success:
result = self.cursor.fetchall()
self.assertEquals(len(result), 10)
self.assertTrue(success)
def tearDown(self):
self.logger.log('teardown', 'debug')
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{}\''.format(
self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=True)
if success:
result = self.cursor.fetchall()
if len(result) == 1:
sql = 'DELETE FROM "Meters" WHERE meter_id = {}'.format(
result[0][0])
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=True)
if success:
self.conn.commit()
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{' \
'}\''.format(self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=True)
result = self.cursor.fetchall()
self.assertEquals(0, len(result))
class SingleFileLoader(object):
"""
Perform insertion of data contained in a single file to the Smart Inverter database
specified in the configuration file.
"""
def __init__(self, filepath=''):
"""
Constructor.
:param testing: Flag indicating if testing mode is on.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.logger.log('making new db conn for filepath {}'.format(filepath),
SILENT)
sys.stdout.flush()
try:
self.conn = SEKDBConnector(
dbName=self.configer.configOptionValue('Database', 'db_name'),
dbHost=self.configer.configOptionValue('Database', 'db_host'),
dbPort=self.configer.configOptionValue('Database', 'db_port'),
dbUsername=self.configer.configOptionValue(
'Database', 'db_username'),
dbPassword=self.configer.configOptionValue(
'Database', 'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
# An empty file path is used during creating of meter table entries.
if filepath == '':
self.filepath = None
self.meterID = None
self.meterDataTable = None
else:
self.filepath = filepath
self.meterID = self.getOrMakeMeterID(self.meterName())
assert self.meterID is not None
self.meterDataTable = "MeterData_{}".format(self.meterName())
# @todo Test existence of meter data table.
self.timestampColumn = 0 # timestamp col in the raw data
self.exceptionCount = 0
def newDataForMeterExists(self):
"""
:return: Boolean true if file has new data.
"""
try:
if (self.dataUtil.maxTimeStamp(self.filepath) >
self.dataUtil.maxTimeStampDB(self.meterName())):
return True
return False
except TypeError as detail:
# @todo Log the cause of the exception.
self.logger.log('Exception: {}'.format(detail), CRITICAL)
self.exceptionCount += 1
return False
def insertDataFromFile(self):
"""
Process input file as a stream from the object attribute's filepath.
:return: (Int, Int) Tuple of Int count of inserted records or None on
error and Int count of exceptions encountered.
"""
insertCnt = 0
with open(self.filepath) as dataFile:
lineCnt = 1
result = False
# @todo handle io errors
self.logger.log('loading data from {}'.format(dataFile), DEBUG)
for line in dataFile:
result = self.insertData(
line.rstrip('\n')) if lineCnt != 1 else False
if result is None:
self.logger.log('Critical insert failure', CRITICAL)
raise Exception('Insert did not complete')
# self.logger.log('insert did not complete', ERROR)
# return None
if insertCnt > 0 and insertCnt % COMMIT_INTERVAL == 0:
self.conn.commit()
self.logger.log('committing at {}'.format(insertCnt),
DEBUG)
sys.stdout.flush()
if result:
insertCnt += 1
lineCnt += 1
self.conn.commit()
self.logger.log('final commit at {}'.format(insertCnt), DEBUG)
return (insertCnt, self.exceptionCount)
def insertData(self, values, commitOnEvery=False):
"""
Insert a row of data to the database.
:param values: String of raw values from the source CSV files.
:return: Boolean indicating success or failure.
"""
if not values or self.dataUtil.badData(values):
return False
if self.removeDupe(values):
self.logger.log('duplicate found', DEBUG)
sql = 'INSERT INTO "{0}" ({1}) VALUES({2}, {3})'.format(
self.meterDataTable,
','.join("\"" + c + "\"" for c in self.dataUtil.dbColumns),
self.meterID, self.dataUtil.sqlFormattedValues(values))
self.logger.log('sql: {}'.format(sql), DEBUG)
if self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=self.exitOnError):
if commitOnEvery:
self.conn.commit()
return True
else:
return False
def removeDupe(self, values):
def deleteDupe(myMeterID, myTimeUTC):
sql = 'DELETE FROM "{0}" WHERE meter_id = {1} AND time_utc = {' \
'2}'.format(self.meterDataTable, myMeterID, myTimeUTC)
if self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=self.exitOnError):
return True
else:
return False
if not values:
return False
timeUTC = self.timeUTC(values)
# This is dependendent on the quote style used for time UTC in the
# raw data.
sql = 'SELECT time_utc FROM "{0}" WHERE meter_id = {1} AND time_utc = ' \
'{2}'.format(
self.meterDataTable, self.meterID, timeUTC)
if self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=self.exitOnError):
rows = self.cursor.fetchone()
if rows and len(rows) == 1:
if deleteDupe(self.meterID, timeUTC):
return True
else:
raise Exception(
"Unable to remove dupe for meter ID {}, time UTC {}".
format(self.meterID, timeUTC))
return False
def timeUTC(self, values):
def makeSingleQuotes(x):
return str(x).replace('"', "'")
return makeSingleQuotes(values.split(',')[self.timestampColumn])
def meterName(self):
"""
The meter name is the name of the containing folder.
:return:
"""
# @todo validate meter name
def validMeterName(name):
pass
return os.path.basename(os.path.dirname(self.filepath))
def getOrMakeMeterID(self, meterName):
"""
Given a meter name, return its meter ID.
If the meter name has no ID, create a new one and return its ID.
:param meterName: String
:return: Int of meter ID
"""
def __meterID(name):
"""
:param name: String of meter name
:return: Int or None
"""
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{' \
'}\''.format(name)
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=False)
if success:
result = self.cursor.fetchall()
assert len(result) == 1 or len(result) == 0
if result:
return int(result[0][0])
else:
return None
else:
return None
def __makeNewMeter(name):
"""
:param name: String of meter name
:return: Int or None
"""
id = __meterID(name)
if id:
return id
self.logger.log('making new meter', DEBUG)
sql = 'INSERT INTO "Meters" (meter_name) VALUES (\'{}\')'.format(
name)
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=False)
self.conn.commit()
if success:
sql = 'SELECT CURRVAL(\'meter_id_seq\')'
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=False)
if success:
return int(self.cursor.fetchall()[0][0])
else:
return None
id = __meterID(meterName)
# Python 3: if isinstance( id, int ):
if isinstance(id, (int, long)):
return int(id)
else:
return __makeNewMeter(meterName)
def __del__(self):
self.logger.log('Destroying single file inserter', DEBUG)
self.conn.close()
class SingleFileLoader(object):
"""
Perform insertion of data contained in a single file to the Smart Inverter database
specified in the configuration file.
"""
def __init__(self, filepath = ''):
"""
Constructor.
:param testing: Flag indicating if testing mode is on.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.logger.log('making new db conn for filepath {}'.format(filepath), SILENT)
sys.stdout.flush()
try:
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
# An empty file path is used during creating of meter table entries.
if filepath == '':
self.filepath = None
self.meterID = None
self.meterDataTable = None
else:
self.filepath = filepath
self.meterID = self.getOrMakeMeterID(self.meterName())
assert self.meterID is not None
self.meterDataTable = "MeterData_{}".format(self.meterName())
# @todo Test existence of meter data table.
self.timestampColumn = 0 # timestamp col in the raw data
self.exceptionCount = 0
def newDataForMeterExists(self):
"""
:return: Boolean true if file has new data.
"""
try:
if (self.dataUtil.maxTimeStamp(
self.filepath) > self.dataUtil.maxTimeStampDB(
self.meterName())):
return True
return False
except TypeError as detail:
# @todo Log the cause of the exception.
self.logger.log('Exception: {}'.format(detail), CRITICAL)
self.exceptionCount += 1
return False
def insertDataFromFile(self):
"""
Process input file as a stream from the object attribute's filepath.
:return: (Int, Int) Tuple of Int count of inserted records or None on
error and Int count of exceptions encountered.
"""
insertCnt = 0
with open(self.filepath) as dataFile:
lineCnt = 1
result = False
# @todo handle io errors
self.logger.log('loading data from {}'.format(dataFile), DEBUG)
for line in dataFile:
result = self.insertData(
line.rstrip('\n')) if lineCnt != 1 else False
if result is None:
self.logger.log('Critical insert failure', CRITICAL)
raise Exception('Insert did not complete')
# self.logger.log('insert did not complete', ERROR)
# return None
if insertCnt > 0 and insertCnt % COMMIT_INTERVAL == 0:
self.conn.commit()
self.logger.log('committing at {}'.format(insertCnt), DEBUG)
sys.stdout.flush()
if result:
insertCnt += 1
lineCnt += 1
self.conn.commit()
self.logger.log('final commit at {}'.format(insertCnt), DEBUG)
return (insertCnt, self.exceptionCount)
def insertData(self, values, commitOnEvery = False):
"""
Insert a row of data to the database.
:param values: String of raw values from the source CSV files.
:return: Boolean indicating success or failure.
"""
if not values or self.dataUtil.badData(values):
return False
if self.removeDupe(values):
self.logger.log('duplicate found', DEBUG)
sql = 'INSERT INTO "{0}" ({1}) VALUES({2}, {3})'.format(
self.meterDataTable,
','.join("\"" + c + "\"" for c in self.dataUtil.dbColumns),
self.meterID, self.dataUtil.sqlFormattedValues(values))
self.logger.log('sql: {}'.format(sql), DEBUG)
if self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = self.exitOnError):
if commitOnEvery:
self.conn.commit()
return True
else:
return False
def removeDupe(self, values):
def deleteDupe(myMeterID, myTimeUTC):
sql = 'DELETE FROM "{0}" WHERE meter_id = {1} AND time_utc = {' \
'2}'.format(self.meterDataTable, myMeterID, myTimeUTC)
if self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = self.exitOnError):
return True
else:
return False
if not values:
return False
timeUTC = self.timeUTC(values)
# This is dependendent on the quote style used for time UTC in the
# raw data.
sql = 'SELECT time_utc FROM "{0}" WHERE meter_id = {1} AND time_utc = ' \
'{2}'.format(
self.meterDataTable, self.meterID, timeUTC)
if self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = self.exitOnError):
rows = self.cursor.fetchone()
if rows and len(rows) == 1:
if deleteDupe(self.meterID, timeUTC):
return True
else:
raise Exception(
"Unable to remove dupe for meter ID {}, time UTC {}".format(
self.meterID, timeUTC))
return False
def timeUTC(self, values):
def makeSingleQuotes(x):
return str(x).replace('"', "'")
return makeSingleQuotes(values.split(',')[self.timestampColumn])
def meterName(self):
"""
The meter name is the name of the containing folder.
:return:
"""
# @todo validate meter name
def validMeterName(name):
pass
return os.path.basename(os.path.dirname(self.filepath))
def getOrMakeMeterID(self, meterName):
"""
Given a meter name, return its meter ID.
If the meter name has no ID, create a new one and return its ID.
:param meterName: String
:return: Int of meter ID
"""
def __meterID(name):
"""
:param name: String of meter name
:return: Int or None
"""
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{' \
'}\''.format(name)
success = self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = False)
if success:
result = self.cursor.fetchall()
assert len(result) == 1 or len(result) == 0
if result:
return int(result[0][0])
else:
return None
else:
return None
def __makeNewMeter(name):
"""
:param name: String of meter name
:return: Int or None
"""
id = __meterID(name)
if id:
return id
self.logger.log('making new meter', DEBUG)
sql = 'INSERT INTO "Meters" (meter_name) VALUES (\'{}\')'.format(
name)
success = self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = False)
self.conn.commit()
if success:
sql = 'SELECT CURRVAL(\'meter_id_seq\')'
success = self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = False)
if success:
return int(self.cursor.fetchall()[0][0])
else:
return None
id = __meterID(meterName)
# Python 3: if isinstance( id, int ):
if isinstance(id, ( int, long )):
return int(id)
else:
return __makeNewMeter(meterName)
def __del__(self):
self.logger.log('Destroying single file inserter', DEBUG)
self.conn.close()
def __init__(self):
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
try:
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
self.dbColumns = [
"meter_id", "time_utc", "error", "lowalarm", "highalarm",
"Accumulated Real Energy Net (kWh)",
"Real Energy Quadrants 1 & 4, Import (kWh)",
"Real Energy Quadrants 2 & 3, Export (kWh)",
"Reactive Energy Quadrant 1 (VARh)",
"Reactive Energy Quadrant 2 (VARh)",
"Reactive Energy Quadrant 3 (VARh)",
"Reactive Energy Quadrant 4 (VARh)", "Apparent Energy Net (VAh)",
"Apparent Energy Quadrants 1 & 4 (VAh)",
"Apparent Energy Quadrants 2 & 3 (VAh)",
"Total Net Instantaneous Real Power (kW)",
"Total Net Instantaneous Reactive Power (kVAR)",
"Total Net Instantaneous Apparent Power (kVA)",
"Total Power Factor", "Voltage, L-L, 3p Ave (Volts)",
"Voltage, L-N, 3p Ave (Volts)", "Current, 3p Ave (Amps)",
"Frequency (Hz)", "Total Real Power Present Demand (kW)",
"Total Reactive Power Present Demand (kVAR)",
"Total Apparent Power Present Demand (kVA)",
"Total Real Power Max Demand, Import (kW)",
"Total Reactive Power Max Demand, Import (kVAR)",
"Total Apparent Power Max Demand, Import (kVA)",
"Total Real Power Max Demand, Export (kW)",
"Total Reactive Power Max Demand, Export (kVAR)",
"Total Apparent Power Max Demand, Export (kVA)",
"Accumulated Real Energy, Phase A, Import (kW)",
"Accumulated Real Energy, Phase B, Import (kW)",
"Accumulated Real Energy, Phase C, Import (kW)",
"Accumulated Real Energy, Phase A, Export (kW)",
"Accumulated Real Energy, Phase B, Export (kW)",
"Accumulated Real Energy, Phase C, Export (kW)",
"Accumulated Q1 Reactive Energy, Phase A, Import (VARh)",
"Accumulated Q1 Reactive Energy, Phase B, Import (VARh)",
"Accumulated Q1 Reactive Energy, Phase C, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase A, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase B, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase C, Import (VARh)",
"Accumulated Q3 Reactive Energy, Phase A, Export (VARh)",
"Accumulated Q3 Reactive Energy, Phase B, Export (VARh)",
"Accumulated Q3 Reactive Energy, Phase C, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase A, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase B, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase C, Export (VARh)",
"Accumulated Apparent Energy, Phase A, Import (VAh)",
"Accumulated Apparent Energy, Phase B, Import (VAh)",
"Accumulated Apparent Energy, Phase C, Import (VAh)",
"Accumulated Apparent Energy, Phase A, Export (VAh)",
"Accumulated Apparent Energy, Phase B, Export (VAh)",
"Accumulated Apparent Energy, Phase C, Export (VAh)",
"Real Power, Phase A (kW)", "Real Power, Phase B (kW)",
"Real Power, Phase C (kW)", "Reactive Power, Phase A (kVAR)",
"Reactive Power, Phase B (kVAR)", "Reactive Power, Phase C (kVAR)",
"Apparent Power, Phase A (kVA)", "Apparent Power, Phase B (kVA)",
"Apparent Power, Phase C (kVA)", "Power Factor, Phase A",
"Power Factor, Phase B", "Power Factor, Phase C",
"Voltage, Phase A-B (Volts)", "Voltage, Phase B-C (Volts)",
"Voltage, Phase A-C (Volts)", "Voltage, Phase A-N (Volts)",
"Voltage, Phase B-N (Volts)", "Voltage, Phase C-N (Volts)",
"Current, Phase A (Amps)", "Current, Phase B (Amps)",
"Current, Phase C (Amps)"
]
class SIDataUtil(object):
"""
Utility methods for Smart Inverter data including the full list of SI
data columns.
"""
def __init__(self):
self.logger = SEKLogger(__name__, DEBUG)
self.configer = SIConfiger()
self.dbUtil = SEKDBUtil()
try:
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
except:
raise Exception("Unable to get DB connection.")
self.cursor = self.conn.cursor()
self.exitOnError = False
self.dbColumns = [
"meter_id", "time_utc", "error", "lowalarm", "highalarm",
"Accumulated Real Energy Net (kWh)",
"Real Energy Quadrants 1 & 4, Import (kWh)",
"Real Energy Quadrants 2 & 3, Export (kWh)",
"Reactive Energy Quadrant 1 (VARh)",
"Reactive Energy Quadrant 2 (VARh)",
"Reactive Energy Quadrant 3 (VARh)",
"Reactive Energy Quadrant 4 (VARh)", "Apparent Energy Net (VAh)",
"Apparent Energy Quadrants 1 & 4 (VAh)",
"Apparent Energy Quadrants 2 & 3 (VAh)",
"Total Net Instantaneous Real Power (kW)",
"Total Net Instantaneous Reactive Power (kVAR)",
"Total Net Instantaneous Apparent Power (kVA)",
"Total Power Factor", "Voltage, L-L, 3p Ave (Volts)",
"Voltage, L-N, 3p Ave (Volts)", "Current, 3p Ave (Amps)",
"Frequency (Hz)", "Total Real Power Present Demand (kW)",
"Total Reactive Power Present Demand (kVAR)",
"Total Apparent Power Present Demand (kVA)",
"Total Real Power Max Demand, Import (kW)",
"Total Reactive Power Max Demand, Import (kVAR)",
"Total Apparent Power Max Demand, Import (kVA)",
"Total Real Power Max Demand, Export (kW)",
"Total Reactive Power Max Demand, Export (kVAR)",
"Total Apparent Power Max Demand, Export (kVA)",
"Accumulated Real Energy, Phase A, Import (kW)",
"Accumulated Real Energy, Phase B, Import (kW)",
"Accumulated Real Energy, Phase C, Import (kW)",
"Accumulated Real Energy, Phase A, Export (kW)",
"Accumulated Real Energy, Phase B, Export (kW)",
"Accumulated Real Energy, Phase C, Export (kW)",
"Accumulated Q1 Reactive Energy, Phase A, Import (VARh)",
"Accumulated Q1 Reactive Energy, Phase B, Import (VARh)",
"Accumulated Q1 Reactive Energy, Phase C, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase A, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase B, Import (VARh)",
"Accumulated Q2 Reactive Energy, Phase C, Import (VARh)",
"Accumulated Q3 Reactive Energy, Phase A, Export (VARh)",
"Accumulated Q3 Reactive Energy, Phase B, Export (VARh)",
"Accumulated Q3 Reactive Energy, Phase C, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase A, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase B, Export (VARh)",
"Accumulated Q4 Reactive Energy, Phase C, Export (VARh)",
"Accumulated Apparent Energy, Phase A, Import (VAh)",
"Accumulated Apparent Energy, Phase B, Import (VAh)",
"Accumulated Apparent Energy, Phase C, Import (VAh)",
"Accumulated Apparent Energy, Phase A, Export (VAh)",
"Accumulated Apparent Energy, Phase B, Export (VAh)",
"Accumulated Apparent Energy, Phase C, Export (VAh)",
"Real Power, Phase A (kW)", "Real Power, Phase B (kW)",
"Real Power, Phase C (kW)", "Reactive Power, Phase A (kVAR)",
"Reactive Power, Phase B (kVAR)", "Reactive Power, Phase C (kVAR)",
"Apparent Power, Phase A (kVA)", "Apparent Power, Phase B (kVA)",
"Apparent Power, Phase C (kVA)", "Power Factor, Phase A",
"Power Factor, Phase B", "Power Factor, Phase C",
"Voltage, Phase A-B (Volts)", "Voltage, Phase B-C (Volts)",
"Voltage, Phase A-C (Volts)", "Voltage, Phase A-N (Volts)",
"Voltage, Phase B-N (Volts)", "Voltage, Phase C-N (Volts)",
"Current, Phase A (Amps)", "Current, Phase B (Amps)",
"Current, Phase C (Amps)"
]
def badData(self, values):
"""
:param values: String
:return: Boolean
"""
# DB cols contain an extra column for the meter ID that is not
# found in individual raw data files.
if len(self.dbColumns) - 1 != len(values.split(',')):
return True
if not re.match('^\"\d+-\d+-\d+\s\d+:\d+:\d+\"', values.split(',')[0]):
self.logger.log('bad date {}'.format(values.split(',')[0]), ERROR)
return True
return False
def sqlFormattedValues(self, values):
"""
:param values: String of raw values from the source CSV files.
:return: String of PostgreSQL compatible values.
"""
def makeNULL(x):
return x == '' and 'NULL' or str(x)
def makeSingleQuotes(x):
return str(x).replace('"', "'")
return ','.join(
map(lambda x: makeSingleQuotes(makeNULL(x)), values.split(',')))
def maxTimeStamp(self, filepath = ''):
"""
:param filepath: String
:return: datetime or None
"""
with open(filepath) as dataFile:
max = None
lineCnt = 1
for line in dataFile:
values = line.rstrip('\n') if lineCnt != 1 else False
if values and not self.badData(values):
dateString = self.sqlFormattedValues(values).split(',')[0]
if dateString.startswith("'") and dateString.endswith("'"):
dateString = dateString[1:-1]
curr = datetime.strptime(dateString, '%Y-%m-%d %H:%M:%S')
if not max or curr > max:
max = curr
lineCnt += 1
return max
def maxTimeStampDB(self, meter = ''):
sql = 'SELECT MAX(time_utc) FROM "MeterData_{}"'.format(meter)
if self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = self.exitOnError):
row = self.cursor.fetchone()
if row and len(row) == 1:
# self.logger.log('row {}'.format(row), DEBUG)
return row[0]
return None
parser = argparse.ArgumentParser(
description='Perform insertion of data contained in multiple files '
'to the SI database.')
parser.add_argument('--basepath',
required=True,
help='A base path from which to process data files.')
COMMAND_LINE_ARGS = parser.parse_args()
if __name__ == '__main__':
processCommandLineArguments()
tableBase = "MeterData"
pkey = 'meter_id, time_utc'
logger = SEKLogger(__name__, 'debug')
configer = SIConfiger()
dbUtil = SEKDBUtil()
conn = SEKDBConnector(
dbName=configer.configOptionValue('Database', 'db_name'),
dbHost=configer.configOptionValue('Database', 'db_host'),
dbPort=configer.configOptionValue('Database', 'db_port'),
dbUsername=configer.configOptionValue('Database', 'db_username'),
dbPassword=configer.configOptionValue('Database',
'db_password')).connectDB()
cursor = conn.cursor()
tableOwner = configer.configOptionValue('Database', 'table_owner')
for meterName in SIUtil().meters(basepath=COMMAND_LINE_ARGS.basepath):
logger.log('creating table {}'.format(tableBase + "_" + meterName))
sql = 'CREATE TABLE "{1}_{0}" ( CHECK ( meter_id = meter_id(\'{' \
'0}\'))) INHERITS ("{1}"); ALTER TABLE ONLY "{1}_{0}" ADD ' \
'CONSTRAINT "{1}_{0}_pkey" PRIMARY KEY ({3}); ALTER TABLE ONLY ' \
class SingleFileLoaderTester(unittest.TestCase):
def setUp(self):
self.logger = SEKLogger(__name__,'DEBUG')
self.configer = SIConfiger()
self.conn = SEKDBConnector(
dbName = self.configer.configOptionValue('Database', 'db_name'),
dbHost = self.configer.configOptionValue('Database', 'db_host'),
dbPort = self.configer.configOptionValue('Database', 'db_port'),
dbUsername = self.configer.configOptionValue('Database',
'db_username'),
dbPassword = self.configer.configOptionValue('Database',
'db_password')).connectDB()
self.cursor = self.conn.cursor()
self.dbUtil = SEKDBUtil()
self.dataUtil = SIDataUtil()
self.inserter = SingleFileLoader('data/test-meter/log.csv')
self.data = '"2014-07-12 16:22:30",0,,,1187488464896.00,' \
'2322185846784.00,1134697381888.00,35184644096.00,' \
'290857353216.00,10133100822528.00,367.13,' \
'-17660932096.00,1078.01,17660934144.00,-7.86,1.80,8.06,' \
'-0.97,244.01,122.00,32.93,60.01,-7.09,1.42,7.24,8.06,' \
'3.34,8.35,-40.18,-5.68,40.52,516.72,403.12,0,' \
'8797179904.00,47518.67,0,86.03,50.23,4198.40,' \
'281475022848.00,2251868602368.00,0,6820.01,' \
'8796095488.00,0,178.83,188.30,0,620.07,505.19,' \
'288230389841920.02,12668.18,68729384.00,0,-3.68,-4.18,,' \
'1.00,0.79,,3.81,4.25,,-0.97,-0.98,,244.01,,,121.54,' \
'122.46,,31.28,34.59,'
self.testMeterName = 'test-meter'
def test_columns(self):
self.assertEquals(len(self.dataUtil.dbColumns), NUM_DB_COLS)
def test_insert_data(self):
self.logger.log('testing data insert')
self.assertTrue(self.inserter.insertData(self.data))
self.conn.commit()
def test_sql_formatted_values(self):
self.logger.log(
'data: {}'.format(self.dataUtil.sqlFormattedValues(self.data)))
def test_meter_id(self):
self.logger.log('testing meter id')
meter_id = self.inserter.meterID(self.testMeterName)
self.logger.log('meter id {}'.format(meter_id))
self.assertTrue(isinstance(meter_id, ( int, long )))
self.logger.log('getting meter id')
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{}\''.format(
self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail = True)
if success:
result = self.cursor.fetchall()
self.assertEquals(1, len(result))
else:
self.logger.log('failed to retrieve meter id', 'error')
def test_meter_name(self):
"""
Test getting the meter name.
:return:
"""
self.assertEquals(self.inserter.meterName(), self.testMeterName)
def test_insert_data_from_file(self):
self.inserter.insertDataFromFile()
sql = 'SELECT * FROM "MeterData" WHERE meter_id = {}'.format(
self.inserter.meterID(self.testMeterName))
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail = True)
if success:
result = self.cursor.fetchall()
self.assertEquals(len(result), 10)
self.assertTrue(success)
def tearDown(self):
self.logger.log('teardown', 'debug')
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{}\''.format(
self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail = True)
if success:
result = self.cursor.fetchall()
if len(result) == 1:
sql = 'DELETE FROM "Meters" WHERE meter_id = {}'.format(
result[0][0])
success = self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = True)
if success:
self.conn.commit()
sql = 'SELECT meter_id FROM "Meters" WHERE meter_name = \'{' \
'}\''.format(self.testMeterName)
success = self.dbUtil.executeSQL(self.cursor, sql,
exitOnFail = True)
result = self.cursor.fetchall()
self.assertEquals(0, len(result))