class MECODataAutoloader(object): """ Provide automated loading of MECO energy data from exports in gzip-compressed XML source data. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__) self.configer = MSGConfiger() self.fileUtil = MSGFileUtil() def newDataExists(self): """ Check the data autoload folder for the presence of new data. :returns: True if new data exists. """ autoloadPath = self.configer.configOptionValue( 'MECO Autoload', 'meco_autoload_new_data_path') if not self.fileUtil.validDirectory(autoloadPath): raise Exception('InvalidDirectory', '%s' % autoloadPath) patterns = ['*.gz'] matchCnt = 0 for root, dirs, filenames in os.walk(autoloadPath): for pat in patterns: for filename in fnmatch.filter(filenames, pat): print filename matchCnt += 1 if matchCnt > 0: return True else: return False def loadNewData(self): """ Load new data contained in the new data path. """ autoloadPath = self.configer.configOptionValue( 'MECO Autoload', 'meco_autoload_new_data_path') command = self.configer.configOptionValue('MECO Autoload', 'meco_autoload_command') os.chdir(autoloadPath) try: subprocess.check_call(command, shell=True) except subprocess.CalledProcessError, e: self.logger.log("An exception occurred: %s" % e, 'error')
class MECODataAutoloader(object): """ Provide automated loading of MECO energy data from exports in gzip-compressed XML source data. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__) self.configer = MSGConfiger() self.fileUtil = MSGFileUtil() def newDataExists(self): """ Check the data autoload folder for the presence of new data. :returns: True if new data exists. """ autoloadPath = self.configer.configOptionValue("MECO Autoload", "meco_autoload_new_data_path") if not self.fileUtil.validDirectory(autoloadPath): raise Exception("InvalidDirectory", "%s" % autoloadPath) patterns = ["*.gz"] matchCnt = 0 for root, dirs, filenames in os.walk(autoloadPath): for pat in patterns: for filename in fnmatch.filter(filenames, pat): print filename matchCnt += 1 if matchCnt > 0: return True else: return False def loadNewData(self): """ Load new data contained in the new data path. """ autoloadPath = self.configer.configOptionValue("MECO Autoload", "meco_autoload_new_data_path") command = self.configer.configOptionValue("MECO Autoload", "meco_autoload_command") os.chdir(autoloadPath) try: subprocess.check_call(command, shell=True) except subprocess.CalledProcessError, e: self.logger.log("An exception occurred: %s" % e, "error")
class MSGWeatherDataDupeChecker(object): """ Determine if a duplicate record exists based on the tuple (WBAN, Date, Time, StationType). """ def __init__(self, testing = False): """ Constructor. :param testing: Flag for testing mode. """ self.logger = SEKLogger(__name__, 'debug') self.dbUtil = MSGDBUtil() def duplicateExists(self, dbCursor, wban, datetime, recordType): """ Check for the existence of a duplicate record. :param dbCursor :param wban :param datetime :param recordType :returns: True if a duplicate record exists, otherwise False. """ tableName = "WeatherNOAA" sql = """SELECT wban, datetime, record_type FROM \"%s\" WHERE wban = '%s' AND datetime = '%s' AND record_type = '%s'""" % ( tableName, wban, datetime, recordType) self.logger.log("sql=%s" % sql, 'debug') self.logger.log("wban=%s, datetime=%s, record_type=%s" % ( wban, datetime, recordType), 'debug') self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False
class MSGWeatherDataDupeChecker(object): """ Determine if a duplicate record exists based on the tuple (WBAN, Date, Time, StationType). """ def __init__(self, testing=False): """ Constructor. :param testing: Flag for testing mode. """ self.logger = SEKLogger(__name__, 'debug') self.dbUtil = MSGDBUtil() def duplicateExists(self, dbCursor, wban, datetime, recordType): """ Check for the existence of a duplicate record. :param dbCursor :param wban :param datetime :param recordType :returns: True if a duplicate record exists, otherwise False. """ tableName = "WeatherNOAA" sql = """SELECT wban, datetime, record_type FROM \"%s\" WHERE wban = '%s' AND datetime = '%s' AND record_type = '%s'""" % ( tableName, wban, datetime, recordType) self.logger.log("sql=%s" % sql, 'debug') self.logger.log( "wban=%s, datetime=%s, record_type=%s" % (wban, datetime, recordType), 'debug') self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False
class MSGTimeUtilTester(unittest.TestCase): def setUp(self): self.logger = SEKLogger(__name__, 'debug') self.timeUtil = MSGTimeUtil() def test_concise_now(self): conciseNow = self.timeUtil.conciseNow() self.logger.log(conciseNow) pattern = '\d+-\d+-\d+_\d+' result = re.match(pattern, conciseNow) self.assertTrue(result is not None, "Concise now matches the regex pattern.") def test_split_dates(self): start = dt(2014, 01, 07) end = dt(2014, 04, 04) print self.timeUtil.splitDates(start, end) self.assertEqual(len(self.timeUtil.splitDates(start, end)), 4, 'Unexpected date count.')
class SIDataUtilTester(unittest.TestCase): """ """ def setUp(self): self.dataUtil = SIDataUtil() self.logger = SEKLogger(__name__) def test_find_max_timestamp(self): filePath = 'data/test-meter/log.csv' self.assertEquals(self.dataUtil.maxTimeStamp(filePath), datetime.strptime('2014-03-10 23:59:00', '%Y-%m-%d %H:%M:%S')) def test_find_max_timestamp_db(self): # @todo test with a static testing DB meter = '001EC6051A0D' self.logger.log(self.dataUtil.maxTimeStampDB(meter))
class WeatherDataLoadingTester(unittest.TestCase): def setUp(self): self.weatherUtil = MSGWeatherDataUtil() self.logger = SEKLogger(__name__, 'DEBUG') self.dbConnector = MSGDBConnector() self.cursor = self.dbConnector.conn.cursor() self.configer = MSGConfiger() def testLoadDataSinceLastLoaded(self): """ Data should be loaded since the last data present in the database. """ pass def testRetrieveDataSinceLastLoaded(self): """ Data since the last loaded date is retrieved. """ pass def testGetLastLoadedDate(self): myDate = self.weatherUtil.getLastDateLoaded(self.cursor).strftime( "%Y-%m-%d %H:%M:%S") pattern = '^(\d+-\d+-\d+\s\d+:\d+:\d+)$' match = re.match(pattern, myDate) assert match and (match.group(1) == myDate), "Date format is valid." def testWeatherDataPattern(self): myPattern = self.configer.configOptionValue('Weather Data', 'weather_data_pattern') testString = """<A HREF="someURL">QCLCD201208.zip</A>""" match = re.match(myPattern, testString) self.logger.log("pattern = %s" % myPattern, 'info') if match: self.logger.log("match = %s" % match) self.logger.log("match group = %s" % match.group(1)) else: self.logger.log("match not found") assert match and match.group( 1) == 'QCLCD201208.zip', "Download filename was matched." def testWeatherDataURL(self): myURL = self.configer.configOptionValue('Weather Data', 'weather_data_url') pass
class SIConfiger(object): """ Supports site-level config for the Smart Grid PV Inverter project. The default path is ~/.smart-inverter.cfg. Usage: configer = SIConfiger() """ 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 configOptionValue(self, section, option): """ Get a configuration value from the local configuration file. :param section: String of section in config file. :param option: String of option in config file. :returns: The value contained in the configuration file. """ try: configValue = self._config.get(section, option) if configValue == "True": return True elif configValue == "False": return False else: return configValue except: self.logger.log( "Failed when getting configuration option {} in section {" "}.".format(option, section), 'error') sys.exit(-1)
class MSGDBExporterTester(unittest.TestCase): """ Unit tests for the MSG Cloud Exporter. """ def setUp(self): self.logger = SEKLogger(__name__, 'DEBUG') self.configer = MSGConfiger() self.exporter = MSGDBExporter() self.testDir = 'db_exporter_test' self.uncompressedTestFilename = 'meco_v3_test_data.sql' self.compressedTestFilename = 'meco_v3_test_data.sql.gz' self.exportTestDataPath = self.configer.configOptionValue( 'Testing', 'export_test_data_path') self.fileUtil = MSGFileUtil() self.fileChunks = [] self.testDataFileID = '' self.pyUtil = MSGPythonUtil() self.timeUtil = MSGTimeUtil() conn = None try: conn = MSGDBConnector().connectDB() except Exception as detail: self.logger.log("Exception occurred: {}".format(detail), 'error') exit(-1) self.logger.log("conn = {}".format(conn), 'debug') self.assertIsNotNone(conn) # Create a temporary working directory. try: os.mkdir(self.testDir) except OSError as detail: self.logger.log( 'Exception during creation of temp directory: %s' % detail, 'ERROR') def tearDown(self): """ Delete all test items. """ REMOVE_TEMPORARY_FILES = True if REMOVE_TEMPORARY_FILES: try: self.logger.log( "Removing local test files {}, {}.".format( self.uncompressedTestFilename, self.compressedTestFilename), 'debug') os.remove( os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) os.remove( os.path.join(os.getcwd(), self.testDir, self.compressedTestFilename)) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'SILENT') try: os.remove( os.path.join(os.getcwd(), self.testDir, self.compressedTestFilename)) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'SILENT') try: for f in self.fileChunks: os.remove(f) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'DEBUG') try: os.rmdir(self.testDir) except OSError as detail: self.logger.log( 'Exception while removing directory: {}'.format(detail), 'ERROR') # Keep deleting from the cloud until there are no more to delete. def deleteFromCloud(): self.logger.log("deleting from cloud", 'debug') try: fileIDToDelete = self.exporter.fileIDForFileName( self.compressedTestFilename) if fileIDToDelete is None: return self.logger.log("file ID to delete: {}".format(fileIDToDelete), 'DEBUG') self.exporter.driveService.files().delete( fileId='{}'.format(fileIDToDelete)).execute() deleteFromCloud() except (TypeError, http.HttpError) as e: self.logger.log('Delete not successful: {}'.format(e), 'DEBUG') deleteFromCloud() def _upload_test_data_to_cloud(self): """ Provide an upload of test data that can be used in other tests. Side effect: Store the file ID as an ivar. """ self.logger.log("Uploading test data for caller: {}".format( self.pyUtil.callerName())) filePath = "{}/{}".format(self.exportTestDataPath, self.compressedTestFilename) self.logger.log('Uploaded {}.'.format(filePath), 'info') uploadResult = self.exporter.uploadFileToCloudStorage(filePath) self.logger.log('upload result: {}'.format(uploadResult)) self.testDataFileID = self.exporter.fileIDForFileName( self.compressedTestFilename) self.logger.log("Test file ID is {}.".format(self.testDataFileID)) def test_markdown_list_of_downloadable_files(self): """ Match the Markdown line entry for the uploaded file. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals( len( filter( lambda x: self.testDataFileID in x, self.exporter.markdownListOfDownloadableFiles().splitlines( ))), 1) def test_get_md5_sum_from_cloud(self): """ Test retrieving the MD5 sum from the cloud. """ # @REVIEWED self.logger.log('Testing getting the MD5 sum.', 'info') self._upload_test_data_to_cloud() testFileMD5 = filter( lambda x: x['id'] == self.testDataFileID, self.exporter.cloudFiles['items'])[0]['md5Checksum'] self.assertEquals(len(testFileMD5), 32) self.assertTrue(re.match(r'[0-9A-Za-z]+', testFileMD5)) def test_get_file_id_for_nonexistent_file(self): """ Test getting a file ID for a nonexistent file. """ # @REVIEWED fileIDs = self.exporter.fileIDForFileName('nonexistent_file') self.logger.log("file ids = {}".format(fileIDs), 'info') self.assertIsNone(fileIDs) def test_upload_test_data(self): """ Upload a test data file for unit testing of DB export. The unit test data file is a predefined set of test data stored in the test data path of the software distribution. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertGreater(len(self.testDataFileID), 0) self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID)) def test_adding_reader_permissions(self): """ Add reader permissions to a file that was uploaded. """ # @REVIEWED self.logger.log("Testing adding reader permissions.") self._upload_test_data_to_cloud() email = self.configer.configOptionValue('Testing', 'tester_email_address') service = self.exporter.driveService try: id_resp = service.permissions().getIdForEmail( email=email).execute() print id_resp except errors.HttpError as detail: print 'Exception while getting ID for email: {}'.format(detail) new_permission = {'value': email, 'type': 'user', 'role': 'reader'} try: self.logger.log('Adding reader permission', 'INFO') fileIDToAddTo = self.testDataFileID # The permission dict is being output to stdout here. resp = service.permissions().insert(fileId=fileIDToAddTo, sendNotificationEmails=False, body=new_permission).execute() except errors.HttpError as detail: self.logger.log( 'Exception while adding reader permissions: {}'.format(detail), 'error') def permission_id(email): try: id_resp = service.permissions().getIdForEmail( email=email).execute() return id_resp['id'] except errors.HttpError as error: self.logger.log("HTTP error: {}".format(error)) permission = {} try: permission = service.permissions().get( fileId=self.testDataFileID, permissionId=permission_id(email)).execute() except errors.HttpError as error: self.logger.log("HTTP error: {}".format(error)) self.assertEquals(permission['role'], 'reader') def test_create_compressed_archived(self): """ * Copy test data to a temp directory (self.testDir). * Create a checksum for test data. * Create a gzip-compressed archive. * Extract gzip-compressed archive. * Create a checksum for the uncompressed data. * Compare the checksums. """ # @REVIEWED self.logger.log('Testing verification of a compressed archive.') self.logger.log('cwd {}'.format(os.getcwd())) fullPath = '{}'.format( os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) shutil.copyfile( '{}/{}'.format(self.exportTestDataPath, self.uncompressedTestFilename), fullPath) md5sum1 = self.fileUtil.md5Checksum(fullPath) self.exporter.fileUtil.gzipCompressFile(fullPath) try: os.remove( os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) except OSError as detail: self.logger.log('Exception while removing: {}'.format(detail), 'ERROR') # Extract archived data and generate checksum. src = gzip.open('{}{}'.format(fullPath, '.gz'), "rb") uncompressed = open(fullPath, "wb") decoded = src.read() uncompressed.write(decoded) uncompressed.close() md5sum2 = self.fileUtil.md5Checksum(fullPath) self.assertEqual( md5sum1, md5sum2, 'Checksums are not equal for original and new ' 'decompressed archive.') def test_export_db(self): """ Perform a quick test of the DB export method using Testing Mode. This requires sudo authorization to complete. """ # @REVIEWED self.logger.log('Testing exportDB using the testing DB.') # @todo handle case where testing db does not exist. dbs = ['test_meco'] ids = self.exporter.exportDBs(databases=dbs, toCloud=True, localExport=True) self.logger.log('Count of exports: {}'.format(len(ids))) self.assertEquals(len(ids), 1, "Count of exported files is wrong.") map(self.exporter.deleteFile, ids) def test_split_archive(self): """ Test splitting an archive into chunks. """ # @REVIEWED self.logger.log('Testing archive splitting.') fullPath = '{}/{}'.format(self.exportTestDataPath, self.compressedTestFilename) self.logger.log('fullpath: {}'.format(fullPath)) shutil.copyfile( fullPath, '{}/{}'.format(self.testDir, self.compressedTestFilename)) fullPath = '{}/{}'.format(self.testDir, self.compressedTestFilename) self.fileChunks = self.fileUtil.splitLargeFile(fullPath=fullPath, numChunks=3) self.assertEquals(len(self.fileChunks), 3) def test_get_file_size(self): """ Test retrieving local file sizes. """ # @REVIEWED fullPath = '{}/{}'.format(self.exportTestDataPath, self.compressedTestFilename) fSize = self.fileUtil.fileSize(fullPath) self.logger.log('size: {}'.format(fSize)) self.assertEqual(fSize, 12279, 'File size is correct.') def test_upload_export_files_list(self): """ TBW """ pass def test_checksum_after_upload(self): """ TBW """ pass def test_dump_exclusions_dictionary(self): """ Verify the exclusions dictionary by its type. :return: """ # @REVIEWED exclusions = self.exporter.dumpExclusionsDictionary() if exclusions: self.assertEquals(type({}), type(exclusions)) def test_move_to_final(self): """ Test moving a file to the final destination path. """ # @REVIEWED self.logger.log('Testing moving to final path {}.'.format( self.configer.configOptionValue('Export', 'db_export_final_path'))) origCompressedFile = '{}/{}'.format( self.configer.configOptionValue('Export', 'export_test_data_path'), self.compressedTestFilename) newCompressedFile = '{}/{}'.format( self.configer.configOptionValue('Export', 'export_test_data_path'), 'temp_test_file') shutil.copyfile(origCompressedFile, newCompressedFile) self.exporter.moveToFinalPath(compressedFullPath=newCompressedFile) self.assertTrue( os.path.isfile('{}/{}'.format( self.configer.configOptionValue('Export', 'db_export_final_path'), 'temp_test_file'))) # Remove the test file. os.remove('{}/{}'.format( self.configer.configOptionValue('Export', 'db_export_final_path'), 'temp_test_file')) def test_log_successful_export(self): """ Test logging of export results to the export history table. """ # @REVIEWED self.assertTrue( self.exporter.logSuccessfulExport(name='test_export', url='http://test_url', datetime=0, size=100)) conn = MSGDBConnector().connectDB() cursor = conn.cursor() dbUtil = MSGDBUtil() self.assertTrue( dbUtil.executeSQL( cursor, 'select * from "ExportHistory" where ' 'timestamp = ' 'to_timestamp(0)')) self.assertEqual(len(cursor.fetchall()), 1, "There should only be one result row.") self.assertTrue( dbUtil.executeSQL( cursor, 'delete from "ExportHistory" where ' 'timestamp = to_timestamp(0)')) conn.commit() def test_metadata_of_file_id(self): """ Test getting the metadata for a file ID. """ # @REVIEWED self._upload_test_data_to_cloud() self.logger.log('metadata: {}'.format( self.exporter.metadataOfFileID(self.testDataFileID))) self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID)) def test_filename_for_file_id(self): """ Test returning a file name given a file ID. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals( self.exporter.filenameForFileID(fileID=self.testDataFileID), self.compressedTestFilename) def test_outdated_files(self): # @REVIEWED self._upload_test_data_to_cloud() time.sleep(1) self.logger.log("outdated:") # For debugging: for item in self.exporter.outdatedFiles( daysBeforeOutdated=datetime.timedelta(days=-1)): self.logger.log( "name: {}, created date: {}".format(item['originalFilename'], item['createdDate']), 'debug') # Get all the outdated files where outdated is equal to anything # uploaded today or later. self.assertTrue( self.exporter.outdatedFiles(daysBeforeOutdated=datetime.timedelta( days=-1))[0]['id'] == self.testDataFileID) self.logger.log('-----') def test_delete_outdated(self): """ TBW """ pass def test_list_of_downloadable_files(self): """ Test the list of downloadable files used by the available files page. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals( len( filter(lambda row: row['id'] == self.testDataFileID, self.exporter.listOfDownloadableFiles())), 1, "Test file not present.") def test_count_of_db_exports(self): count = self.exporter.countOfDBExports(EARLIEST_DATE) self.logger.log(count, 'DEBUG') self.assertTrue(int(count) or int(count) == int(0)) def test_count_of_cloud_files(self): count = self.exporter.countOfCloudFiles() self.assertTrue(int(count) or int(count) == int(0)) def test_plaintext_list_of_downloadable_files(self): """ This test handles content both with content links and without content links. """ content = self.exporter.plaintextListOfDownloadableFiles() self.assertRegexpMatches( content, '\d+-\d+-\d+.*\,' '\s+\d+-\d+-\d+T\d+:\d+:\d+\.\d+Z\,\s+\d+\sB') def test_last_report_date(self): last_report = self.exporter.notifier.lastReportDate( types=MSGNotificationHistoryTypes, noticeType=MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY) self.assertTrue(last_report is None or last_report > EARLIEST_DATE) def test_current_export_summary(self): self.assertRegexpMatches( self.exporter.currentExportSummary(), re.compile('last.*databases.*free.*currently.*accessed.*', flags=re.IGNORECASE | re.DOTALL))
class MSGDataAggregatorTester(unittest.TestCase): """ Unit tests for MSG Data Aggregator. """ def setUp(self): """ Constructor. """ self.logger = SEKLogger(__name__, "DEBUG") self.aggregator = MSGDataAggregator() self.testStart = "2014-01-02 11:59" self.testEnd = "2014-01-02 12:14" self.rawTypes = ["weather", "egauge", "circuit", "irradiance"] def testIrradianceFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = "timestamp" rows = [] for row in self.aggregator.rawData( dataType="irradiance", orderBy=[timeCol, "sensor_id"], timestampCol=timeCol, startDate=self.testStart, endDate=self.testEnd, ): rows.append(row) self.assertIsNotNone(rows, "Rows are present.") def testWeatherFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = "timestamp" rows = [] for row in self.aggregator.rawData( dataType="weather", orderBy=[timeCol], timestampCol=timeCol, startDate=self.testStart, endDate=self.testEnd ): rows.append(row) self.assertIsNotNone(rows, "Rows are present.") def testCircuitFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = "timestamp" rows = [] for row in self.aggregator.rawData( dataType="circuit", orderBy=[timeCol, "circuit"], timestampCol=timeCol, startDate=self.testStart, endDate=self.testEnd, ): rows.append(row) self.assertIsNotNone(rows, "Rows are present.") def testEgaugeFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = "datetime" rows = [] for row in self.aggregator.rawData( dataType="egauge", orderBy=[timeCol, "egauge_id"], timestampCol=timeCol, startDate=self.testStart, endDate=self.testEnd, ): rows.append(row) self.assertIsNotNone(rows, "Rows are present.") def testEgaugeAggregation(self): """ Perform aggregation over the testing time interval. :return: """ self.logger.log("Testing Egauge aggregation.") rowCnt = 0 agg = self.aggregator.aggregatedData( dataType="egauge", aggregationType="agg_egauge", timeColumnName="datetime", subkeyColumnName="egauge_id", startDate=self.testStart, endDate=self.testEnd, ) print [col for col in agg.columns]
msgBody += 'The new data count for type {} is {} readings' \ '.\n'.format(result[i].keys()[0], result[i].values()[0]) msgBody += '\n\n' msgBody += 'The last report date was %s.' % lastReportDate msgBody += '\n\n' self.notifier.sendNotificationEmail(msgBody, testing = testing) self.notifier.recordNotificationEvent( MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR) def aggregateNewData(self): """ :return: list of dicts obtained from MSGDataAggregator::aggregateNewData. """ result = map(self.aggregator.aggregateNewData, self.rawTypes) self.logger.log('result {}'.format(result)) return result if __name__ == '__main__': aggregator = NewDataAggregator() logger = SEKLogger(__name__) logger.log('Last report date {}'.format(aggregator.notifier.lastReportDate( MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR))) result = aggregator.aggregateNewData() aggregator.sendNewDataNotification(result = result, testing = False)
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 MSGFileUtil(object): """ Utilities related to files and directories. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'DEBUG') def validDirectory(self, path): """ Verify that the path is a valid directory. :param path: Path to check. :returns: True if path is a valid directory. """ if os.path.exists(path) and os.path.isdir(path): return True else: return False def md5Checksum(self, fullPath): """ Get the MD5 checksum for the file given by fullPath. :param fullPath: Full path of the file to generate for which to generate a checksum. :returns: MD5 checksum value as a hex digest. """ try: f = open(fullPath, mode='rb') content = hashlib.md5() for buf in iter(partial(f.read, 128), b''): content.update(buf) md5sum = content.hexdigest() f.close() return md5sum except IOError as detail: self.logger.log( 'Exception during checksum calculation: %s' % detail, 'ERROR') def gzipUncompressFile(self, srcPath, destPath): """ Gzip uncompress a file given by fullPath. @todo Need to deal with large file sizes. Stop reading into memory. :param srcPath: Full path of the file to be uncompressed. :param destPath: Full path of file to be written to. """ self.logger.log( 'Uncompressing gzip source %s to %s' % (srcPath, destPath), 'DEBUG') gzipFile = gzip.open(srcPath, "rb") uncompressedFile = open(destPath, "wb") decoded = gzipFile.read() try: uncompressedFile.write(decoded) except: self.logger.log("Exception while writing uncompressed file.") gzipFile.close() uncompressedFile.close() def gzipCompressFile(self, fullPath): """ Perform gzip compression on a file at fullPath. @todo Generalize this method. :param fullPath: Full path of the file to be compressed. :returns: Boolean: True if successful, False otherwise. """ success = False self.logger.log('Gzip compressing %s.' % fullPath) try: f_in = open('%s' % (fullPath), 'rb') f_out = gzip.open('%s.gz' % (fullPath), 'wb') f_out.writelines(f_in) f_out.close() f_in.close() success = True except IOError as detail: self.logger.log('IOError exception while gzipping: %s' % detail, 'ERROR') return success def splitFile(self, fullPath='', chunkSize=0): """ @DEPRECATED Split a file into chunks. Write output files to base path of the input file. Adapted from https://gist.github.com/mattiasostmar/7883550. :param fullPath: :param chunkSize: :returns: A list of file chunks in full path form. """ fChunks = [] basePath = os.path.dirname(fullPath) baseName = os.path.basename(fullPath) self.logger.log('basename: %s' % baseName) f = open(fullPath, 'rb') data = f.read() f.close() bytes = len(data) # Ensure splitting doesn't happen if it's not needed. if bytes <= chunkSize: return [fullPath] chunkNames = [] fCnt = 0 for i in range(0, bytes + 1, chunkSize): fn1 = "%s/%s.%s" % (basePath, baseName, fCnt) self.logger.log("Splitter writing to %s" % fn1, 'INFO') fChunks.append(fn1) chunkNames.append(fn1) try: f = open(fn1, 'wb') f.write(data[i:i + chunkSize]) f.close() except Exception as detail: print "Exception during writing split file: %s" % detail fCnt += 1 return fChunks def splitLargeFile(self, fullPath='', numChunks=0, chunkSize=0): """ Split a large file into chunks. :param fullPath: String :param numChunks: Int number of files to be split into. :param chunkSize: @DEPRECATED :return: List of file chunk names in full path form. """ fChunks = [] basePath = os.path.dirname(fullPath) baseName = os.path.basename(fullPath) self.logger.log('basename: %s' % baseName) fp = open(fullPath, 'rb') fsize = os.path.getsize(fullPath) chunkSize = int(float(fsize) / float(numChunks)) totalBytes = 0 self.logger.log('chunk size: %s' % chunkSize) if numChunks == 0 or numChunks == 1: return [fullPath] for x in range(numChunks): if x == numChunks - 1: chunkSize = fsize - totalBytes data = fp.read(chunkSize) totalBytes += len(data) fout = open("%s/%s.%s" % (basePath, baseName, x), "wb") self.logger.log('Writing %s/%s.%s' % (basePath, baseName, x), 'debug') fChunks.append("%s/%s.%s" % (basePath, baseName, x)) fout.write(data) fout.close() return fChunks def fileSize(self, fullPath=''): """ Get the size in bytes for the file at fullPath. :param fullPath: :return: size in bytes """ return os.path.getsize(fullPath)
class MSGDBExporter(object): """ Export MSG DBs as SQL scripts. Supports export to local storage and to cloud storage. Usage: from msg_db_exporter import MSGDBExporter exporter = MSGDBExporter() Public API: exportDB(databases:List, toCloud:Boolean, testing:Boolean, numChunks:Integer, deleteOutdated:Boolean): Export a list of DBs to the cloud. """ # List of cloud files. @property def cloudFiles(self): self._cloudFiles = self.driveService.files().list().execute() return self._cloudFiles @property def driveService(self): if self._driveService: return self._driveService if not self.credentialPath: raise Exception("Credential path is required.") storage = Storage( '{}/google_api_credentials'.format(self.credentialPath)) self.googleAPICredentials = storage.get() self.logger.log("Authorizing credentials.", 'info') http = httplib2.Http() http = self.googleAPICredentials.authorize(http) self.logger.log("Authorized.", 'info') self._driveService = build('drive', 'v2', http = http) return self._driveService def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'DEBUG', useColor = False) self.timeUtil = MSGTimeUtil() self.configer = MSGConfiger() self.fileUtil = MSGFileUtil() self.pythonUtil = MSGPythonUtil() # for debugging self.connector = MSGDBConnector() self.conn = self.connector.connectDB() self.cursor = self.conn.cursor() self.dbUtil = MSGDBUtil() self.notifier = SEKNotifier(connector = self.connector, dbUtil = self.dbUtil, user = self.configer.configOptionValue( 'Notifications', 'email_username'), password = self.configer.configOptionValue( 'Notifications', 'email_password'), fromaddr = self.configer.configOptionValue( 'Notifications', 'email_from_address'), toaddr = self.configer.configOptionValue( 'Notifications', 'email_recipients'), testing_toaddr = self.configer.configOptionValue( 'Notifications', 'testing_email_recipients'), smtp_server_and_port = self.configer.configOptionValue( 'Notifications', 'smtp_server_and_port')) # Google Drive parameters. self.clientID = self.configer.configOptionValue('Export', 'google_api_client_id') self.clientSecret = self.configer.configOptionValue('Export', 'google_api_client_secret') self.oauthScope = 'https://www.googleapis.com/auth/drive' self.oauthConsent = 'urn:ietf:wg:oauth:2.0:oob' self.googleAPICredentials = '' self.exportTempWorkPath = self.configer.configOptionValue('Export', 'db_export_work_path') self.credentialPath = self.configer.configOptionValue('Export', 'google_api_credentials_path') self.credentialStorage = Storage( '{}/google_api_credentials'.format(self.credentialPath)) self._driveService = None self._cloudFiles = None self.postAgent = 'Maui Smart Grid 1.0.0 DB Exporter' self.retryDelay = 10 self.availableFilesURL = '' def verifyExportChecksum(self, testing = False): """ Verify the compressed export file using a checksum. * Save the checksum of the original uncompressed export data. * Extract the compressed file. * Verify the uncompressed export data. :param testing: When set to True, Testing Mode is used. """ # Get the checksum of the original file. md5sum = self.fileUtil.md5Checksum(self.exportTempWorkPath) self.logger.log('md5sum: {}'.format(md5sum)) def db_username(self): return "postgres" # return self.configer.configOptionValue('Database', 'db_username') def db_password(self): return self.configer.configOptionValue('Database', 'db_password') def db_port(self): return self.configer.configOptionValue('Database', 'db_port') def dumpCommand(self, db = '', dumpName = ''): """ This method makes use of pg_dump -s -p ${PORT} -U ${USERNAME} [-T ${OPTIONAL_TABLE_EXCLUSIONS}] ${DB_NAME} > ${EXPORT_TEMP_WORK_PATH}/${DUMP_TIMESTAMP}_{DB_NAME}.sql :param db: String :param dumpName: String :return: String of command used to export DB. """ # For reference only: # Password is passed from ~/.pgpass. # Note that ':' and '\' characters should be escaped with '\'. # Ref: http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html # Dump databases as the superuser. This method does not require a # stored password when running under a root crontab. if not db or not dumpName: raise Exception('DB and dumpname required.') # Process exclusions. exclusions = self.dumpExclusionsDictionary() excludeList = [] if db in exclusions: excludeList = exclusions[db] excludeString = '' if len(excludeList) > 0 and exclusions != None: for e in excludeList: excludeString += """-T '"{}"' """.format(e) return 'sudo -u postgres pg_dump -p {0} -U {1} {5} {2} > {3}/{4}' \ '.sql'.format(self.db_port(), self.db_username(), db, self.exportTempWorkPath, dumpName, excludeString) def dumpExclusionsDictionary(self): """ :param db: String of DB name for which to retrieve exclusions. :return: Dictionary with keys as DBs and values as lists of tables to be excluded for a given database. """ try: if type(eval(self.configer.configOptionValue('Export', 'db_export_exclusions'))) == type( {}): return eval(self.configer.configOptionValue('Export', 'db_export_exclusions')) else: return None except SyntaxError as detail: self.logger.log( 'SyntaxError exception while getting exclusions: {}'.format( detail)) def dumpName(self, db = ''): """ :param db: String :return: String of file name used for dump file of db. """ if not db: raise Exception('DB required.') return "{}_{}".format(self.timeUtil.conciseNow(), db) def filesToUpload(self, compressedFullPath = '', numChunks = 0, chunkSize = 0): """ :param compressedFullPath: String :param numChunks: Int :param chunkSize: Int :return: List of files to be uploaded according to their split sections, if applicable. """ if numChunks != 0: self.logger.log('Splitting {}'.format(compressedFullPath), 'DEBUG') filesToUpload = self.fileUtil.splitLargeFile( fullPath = compressedFullPath, chunkSize = chunkSize, numChunks = numChunks) if not filesToUpload: raise Exception('Exception during file splitting.') else: self.logger.log('to upload: {}'.format(filesToUpload), 'debug') return filesToUpload else: return [compressedFullPath] def dumpResult(self, db = '', dumpName = '', fullPath = ''): """ :param dumpName: String of filename of dump file. :param fullPath: String of full path to dump file. :return: Boolean True if dump operation was successful, otherwise False. """ success = True self.logger.log('fullPath: {}'.format(fullPath), 'DEBUG') try: # Generate the SQL script export. # @todo check return value of dump command self.logger.log('cmd: {}'.format( self.dumpCommand(db = db, dumpName = dumpName))) subprocess.check_call( self.dumpCommand(db = db, dumpName = dumpName), shell = True) except subprocess.CalledProcessError as error: self.logger.log("Exception while dumping: {}".format(error)) sys.exit(-1) return success def exportDBs(self, databases = None, toCloud = False, localExport = True, testing = False, chunkSize = 0, deleteOutdated = False): """ Export a set of DBs to local storage. :param databases: List of database names that will be exported. :param toCloud: Boolean if set to True, then the export will also be copied to cloud storage. :param localExport: Boolean when set to True the DB is exported locally. :param testing: Boolean flag for testing mode. (@DEPRECATED) :param chunkSize: Integer size in bytes of chunk size used for splitting. :param deleteOutdated: Boolean indicating outdated files in the cloud should be removed. :returns: List of file IDs of uploaded files or None if there is an error condition. """ # @todo separate uploading and exporting functions noErrors = True uploaded = [] for db in databases: self.logger.log('Exporting {} using pg_dump.'.format(db), 'info') dumpName = self.dumpName(db = db) fullPath = '{}/{}.sql'.format(self.exportTempWorkPath, dumpName) if localExport: noErrors = self.dumpResult(db, dumpName, fullPath) # Perform compression of the file. self.logger.log("Compressing {} using gzip.".format(db), 'info') self.logger.log('fullpath: {}'.format(fullPath), 'DEBUG') gzipResult = self.fileUtil.gzipCompressFile(fullPath) compressedFullPath = '{}{}'.format(fullPath, '.gz') numChunks = self.numberOfChunksToUse(compressedFullPath) # Gzip uncompress and verify by checksum is disabled until a more # efficient, non-memory-based, uncompress is implemented. # md5sum1 = self.fileUtil.md5Checksum(fullPath) # self.md5Verification(compressedFullPath=compressedFullPath, # fullPath=fullPath,md5sum1=md5sum1) if toCloud: # Split compressed files into a set of chunks to improve the # reliability of uploads. # Upload the files to the cloud. for f in self.filesToUpload( compressedFullPath = compressedFullPath, numChunks = numChunks, chunkSize = chunkSize): self.logger.log('Uploading {}.'.format(f), 'info') fileID = self.uploadFileToCloudStorage(fullPath = f, testing = testing, retryCount = int( self.configer.configOptionValue( 'Export', 'export_retry_count'))) self.logger.log('file id after upload: {}'.format(fileID)) if fileID != None: uploaded.append(fileID) self.logger.log('uploaded: {}'.format(uploaded), 'DEBUG') if not self.addReaders(fileID, self.configer.configOptionValue( 'Export', 'reader_permission_email_addresses').split( ','), retryCount = int( self.configer.configOptionValue( 'Export', 'export_retry_count'))): self.logger.log( 'Failed to add readers for {}.'.format(f), 'error') self.logSuccessfulExport(*self.metadataOfFileID(fileID)) # Remove split sections if they exist. try: if not testing and numChunks > 1: self.logger.log('Removing {}'.format(f)) os.remove('{}'.format(f)) except OSError as error: self.logger.log( 'Exception while removing {}: {}.'.format(fullPath, error)) noErrors = False # End if toCloud. if gzipResult: self.moveToFinalPath(compressedFullPath = compressedFullPath) # Remove the uncompressed file. try: if not testing: self.logger.log('Removing {}'.format(fullPath)) os.remove('{}'.format(fullPath)) except OSError as error: self.logger.log( 'Exception while removing {}: {}.'.format(fullPath, error)) noErrors = False # End for db in databases. if deleteOutdated: self.deleteOutdatedFiles(datetime.timedelta(days = int( self.configer.configOptionValue('Export', 'export_days_to_keep')))) return uploaded if noErrors else None def moveToFinalPath(self, compressedFullPath = ''): """ Move a compressed final to the final export path. :param compressedFullPath: String for the compressed file. :return: """ self.logger.log('Moving {} to final path.'.format(compressedFullPath), 'debug') try: shutil.move(compressedFullPath, self.configer.configOptionValue('Export', 'db_export_final_path')) except Exception as detail: self.logger.log( 'Exception while moving {} to final export path: {}'.format( compressedFullPath, detail), 'error') def md5Verification(self, compressedFullPath = '', fullPath = '', md5sum1 = ''): """ Perform md5 verification of a compressed file at compressedFullPath where the original file is at fullPath and has md5sum1. :param compressedFullPath: String :param fullPath: String :param md5sum1: String of md5sum of source file. :return: """ GZIP_UNCOMPRESS_FILE = False if GZIP_UNCOMPRESS_FILE: # Verify the compressed file by uncompressing it and # verifying its # checksum against the original checksum. self.logger.log('reading: {}'.format(compressedFullPath), 'DEBUG') self.logger.log('writing: {}'.format(os.path.join( self.configer.configOptionValue('Testing', 'export_test_data_path'), os.path.splitext(os.path.basename(fullPath))[0])), 'DEBUG') self.fileUtil.gzipUncompressFile(compressedFullPath, os.path.join( self.configer.configOptionValue('Testing', 'export_test_data_path'), fullPath)) VERIFY_BY_CHECKSUM = False if VERIFY_BY_CHECKSUM: md5sum2 = self.fileUtil.md5Checksum(fullPath) self.logger.log("mtime: {}, md5sum2: {}".format( time.ctime(os.path.getmtime(fullPath)), md5sum2), 'INFO') if md5sum1 == md5sum2: self.logger.log( 'Compressed file has been validated by checksum.', 'INFO') else: noErrors = False def numberOfChunksToUse(self, fullPath): """ Return the number of chunks to be used by the file splitter based on the file size of the file at fullPath. :param fullPath: String :returns: Int Number of chunks to create. """ fsize = os.path.getsize(fullPath) self.logger.log('fullpath: {}, fsize: {}'.format(fullPath, fsize)) if (fsize >= int(self.configer.configOptionValue('Export', 'max_bytes_before_split'))): # Note that this does not make use of the remainder in the division. chunks = int(fsize / int(self.configer.configOptionValue('Export', 'max_bytes_before_split'))) self.logger.log('Will split with {} chunks.'.format(chunks)) return chunks self.logger.log('Will NOT split file.', 'debug') return 1 def uploadFileToCloudStorage(self, fullPath = '', retryCount = 0, testing = False): """ Export a file to cloud storage. :param fullPath: String of file to be exported. :param testing: Boolean when set to True, Testing Mode is used. :param retryCount: Int of number of times to retry the upload if there is a failure. :returns: String File ID on verified on upload; None if verification fails. """ success = True myFile = os.path.basename(fullPath) self.logger.log( 'full path {}'.format(os.path.dirname(fullPath), 'DEBUG')) self.logger.log("Uploading {}.".format(myFile)) result = {} try: media_body = MediaFileUpload(fullPath, mimetype = 'application/gzip-compressed', resumable = True) body = {'title': myFile, 'description': 'Hawaii Smart Energy Project gzip ' 'compressed DB export.', 'mimeType': 'application/gzip-compressed'} # Result is a Files resource. result = self.driveService.files().insert(body = body, media_body = media_body).execute() except Exception as detail: # Upload failures can result in a BadStatusLine. self.logger.log( "Exception while uploading {}: {}.".format(myFile, detail), 'error') success = False if not self.__verifyMD5Sum(fullPath, self.fileIDForFileName(myFile)): self.logger.log('Failed MD5 checksum verification.', 'INFO') success = False if success: self.logger.log('Verification by MD5 checksum succeeded.', 'INFO') self.logger.log("Finished.") return result['id'] if not success and retryCount <= 0: return None else: time.sleep(self.retryDelay) self.logger.log('Retrying upload of {}.'.format(fullPath), 'warning') self.uploadFileToCloudStorage(fullPath = fullPath, retryCount = retryCount - 1) def __retrieveCredentials(self): """ Perform authorization at the server. Credentials are loaded into the object attribute googleAPICredentials. """ flow = OAuth2WebServerFlow(self.clientID, self.clientSecret, self.oauthScope, self.oauthConsent) authorize_url = flow.step1_get_authorize_url() print 'Go to the following link in your browser: ' + authorize_url code = raw_input('Enter verification code: ').strip() self.googleAPICredentials = flow.step2_exchange(code) print "refresh_token = {}".format( self.googleAPICredentials.refresh_token) print "expiry = {}".format(self.googleAPICredentials.token_expiry) def freeSpace(self): """ Get free space from the drive service. :param driveService: Object for the drive service. :returns: Int of free space (bytes B) on the drive service. """ aboutData = self.driveService.about().get().execute() return int(aboutData['quotaBytesTotal']) - int( aboutData['quotaBytesUsed']) - int( aboutData['quotaBytesUsedInTrash']) def deleteFile(self, fileID = ''): """ Delete the file with ID fileID. :param fileID: String of a Google API file ID. """ if not len(fileID) > 0: raise Exception("File ID has not been given.") self.logger.log( 'Deleting file with file ID {} and name {}.'.format(fileID, self.filenameForFileID( fileID)), 'debug') try: # Writing the fileId arg name is required here. self.driveService.files().delete(fileId = fileID).execute() except errors.HttpError as error: self.logger.log('Exception while deleting: {}'.format(error), 'error') def deleteOutdatedFiles(self, maxAge = datetime.timedelta(weeks = 9999999)): """ Remove outdated files from cloud storage. :param minAge: datetime.timedelta of the minimum age before a file is considered outdated. :param maxAge: datetime.timedelta of the maximum age to consider for a file. :returns: Int count of deleted items. """ # @todo Return count of actual successfully deleted files. outdated = self.outdatedFiles(maxAge) """:type : dict""" for f in outdated: self.deleteFile(f['id']) return len(outdated) def outdatedFiles(self, daysBeforeOutdated = datetime.timedelta(days = 9999999)): """ Outdated files in the cloud where they are outdated if their age is greater than or equal to daysBeforeOutdated. Note: When t1 is the same day as t2, the timedelta comes back as -1. Not sure why this isn't represented as zero. Perhaps to avoid a false evaluation of a predicate on a tdelta. :param daysBeforeOutdated: datetime.timedelta where the value indicates that outdated files that have an age greater than this parameter. :return: Int count of deleted items. """ t1 = lambda x: datetime.datetime.strptime(x['createdDate'], "%Y-%m-%dT%H:%M:%S.%fZ") t2 = datetime.datetime.now() return filter(lambda x: t2 - t1(x) >= daysBeforeOutdated, self.cloudFiles['items']) def sendNotificationOfFiles(self): """ Provide a notification that lists the export files along with sharing links. """ pass def sendDownloadableFiles(self): """ Send available files via HTTP POST. :returns: None """ myPath = '{}/{}'.format(self.exportTempWorkPath, 'list-of-downloadable-files.txt') fp = open(myPath, 'wb') output = StringIO() output.write(self.markdownListOfDownloadableFiles()) fp.write(self.markdownListOfDownloadableFiles()) fp.close() headers = {'User-Agent': self.postAgent, 'Content-Type': 'text/html'} try: r = requests.post(self.configer.configOptionValue('Export', 'export_list_post_url'), output.getvalue(), headers = headers) print 'text: {}'.format(r.text) except requests.adapters.SSLError as error: # @todo Implement alternative verification. self.logger.log('SSL error: {}'.format(error), 'error') output.close() def metadataOfFileID(self, fileID = ''): """ :param fileID: String of a file ID in the cloud. :return: Tuple of metadata (name, url, timestamp, size) for a given file ID. """ item = [i for i in self.cloudFiles['items'] if i['id'] == fileID][0] return (item[u'originalFilename'], item[u'webContentLink'], item[u'createdDate'], item[u'fileSize']) def listOfDownloadableFiles(self): """ Create a list of downloadable files. :returns: List of dicts of files that are downloadable from the cloud. """ files = [] for i in reversed(sorted(self.cloudFiles['items'], key = lambda k: k['createdDate'])): item = dict() item['title'] = i['title'] item['webContentLink'] = i['webContentLink'] item['id'] = i['id'] item['createdDate'] = i['createdDate'] item['fileSize'] = i['fileSize'] files.append(item) return files def markdownListOfDownloadableFiles(self): """ Generate content containing a list of downloadable files in Markdown format. :returns: String content in Markdown format. """ content = "||*Name*||*Created*||*Size*||\n" for i in self.listOfDownloadableFiles(): content += "||[`{}`]({})".format(i['title'], i['webContentLink']) content += "||`{}`".format(i['createdDate']) content += "||`{} B`||".format(int(i['fileSize'])) content += '\n' # self.logger.log('content: {}'.format(content)) return content def plaintextListOfDownloadableFiles(self): """ Generate content containing a list of downloadable files in plaintext format. :returns: String content as plaintext. """ content = '' includeLink = False for i in reversed(sorted(self.cloudFiles['items'], key = lambda k: k['createdDate'])): if includeLink: content += "{}, {}, {}, {} B\n".format(i['title'], i['webContentLink'], i['createdDate'], int(i['fileSize'])) else: content += "{}, {}, {} B\n".format(i['title'], i['createdDate'], int(i['fileSize'])) return content def logSuccessfulExport(self, name = '', url = '', datetime = 0, size = 0): """ When an export has been successful, log information about the export to the database. The items to log include: * filename * URL * timestamp * filesize :param name: String :param url: String :param datetime: :param size: Int :return: True if no errors occurred, else False. """ def exportHistoryColumns(): return ['name', 'url', 'timestamp', 'size'] timestamp = lambda \ datetime: 'to_timestamp(0)' if datetime == 0 else "timestamp " \ "'{}'".format( datetime) sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}, {3}, {4}, {5})'.format( self.configer.configOptionValue('Export', 'export_history_table'), ','.join(exportHistoryColumns()), "'" + name + "'", "'" + url + "'", timestamp(datetime), size) conn = MSGDBConnector().connectDB() cursor = conn.cursor() dbUtil = MSGDBUtil() result = dbUtil.executeSQL(cursor, sql, exitOnFail = False) conn.commit() return result def sendExportSummary(self, summary = ''): """ Send a summary of exports via email to a preconfigured list of recipients. :param summary: String of summary content. :return: """ try: if self.notifier.sendNotificationEmail(summary, testing = False): self.notifier.recordNotificationEvent( types = MSGNotificationHistoryTypes, noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY) except Exception as detail: self.logger.log('Exception occurred: {}'.format(detail), 'ERROR') def currentExportSummary(self): """ Current summary of exports since the last summary report time. Summaries are reported with identifier MSG_EXPORT_SUMMARY in the NotificationHistory. Includes: * Number of databases exported * Total number of files in the cloud. * A report of available storage capacity. * A list of available DBs. * A link where exports can be accessed. :return: String of summary text. """ availableFilesURL = self.configer.configOptionValue('Export', 'export_list_url') lastReportDate = self.notifier.lastReportDate( types = MSGNotificationHistoryTypes, noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY) content = 'Cloud Export Summary:\n\n' content += 'Last report date: {}\n'.format(lastReportDate) # @TO BE REVIEWED: Verify time zone adjustment. content += '{} databases have been exported since the last report ' \ 'date.\n'.format(self.countOfDBExports( lastReportDate + datetime.timedelta( hours = 10)) if lastReportDate else self.countOfDBExports()) content += '{} B free space is available.\n'.format(self.freeSpace()) content += '\nCurrently available DBs:\n' content += self.plaintextListOfDownloadableFiles() content += '\n{} files can be accessed through Google Drive (' \ 'https://drive.google.com) or at {}.'.format( self.countOfCloudFiles(), availableFilesURL) return content def countOfDBExports(self, since = None): """ :param since: datetime indicating last export datetime. :return: Int of count of exports. """ myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S') if not since: since = myDatetime('1900-01-01 00:00') self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG') sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \ '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format( since.strftime('%Y-%m-%d %H:%M')) conn = MSGDBConnector().connectDB() cursor = conn.cursor() dbUtil = MSGDBUtil() rows = None if dbUtil.executeSQL(cursor, sql, exitOnFail = False): rows = cursor.fetchall() assert len(rows) == 1, 'Invalid return value.' return rows[0][0] def countOfCloudFiles(self): """ :param since: datetime indicating last trailing export datetime. :return: Int of count of exports. """ return len(self.cloudFiles['items']) def __verifyMD5Sum(self, localFilePath, remoteFileID): """ Verify that the local MD5 sum matches the MD5 sum for the remote file corresponding to an ID. This verifies that the uploaded file matches the local compressed export file. :param localFilePath: String of the full path of the local file. :param remoteFileID: String of the cloud ID for the remote file. :returns: Boolean True if the MD5 sums match, otherwise, False. """ self.logger.log('remote file ID: {}'.format(remoteFileID)) self.logger.log('local file path: {}'.format(localFilePath)) # Get the md5sum for the local file. f = open(localFilePath, mode = 'rb') fContent = hashlib.md5() for buf in iter(partial(f.read, 128), b''): fContent.update(buf) localMD5Sum = fContent.hexdigest() f.close() self.logger.log('local md5: {}'.format(localMD5Sum), 'DEBUG') def verifyFile(): # Get the MD5 sum for the remote file. for item in self.cloudFiles['items']: if (item['id'] == remoteFileID): self.logger.log( 'remote md5: {}'.format(item['md5Checksum']), 'DEBUG') if localMD5Sum == item['md5Checksum']: return True else: return False try: if verifyFile(): return True else: return False except errors.HttpError as detail: self.logger.log('HTTP error during MD5 verification.', 'error') time.sleep(10) if verifyFile(): return True else: return False def fileIDForFileName(self, filename): """ Get the file ID for the given filename. This method supports matching multiple cloud filenames but only returns the ID for a single matching filename. This can then be called recursively to obtain all the file IDs for a given filename. :param String of the filename for which to retrieve the ID. :returns: String of a cloud file ID or None if no match. """ fileIDList = filter(lambda x: x['originalFilename'] == filename, self.cloudFiles['items']) return fileIDList[0]['id'] if len(fileIDList) > 0 else None def filenameForFileID(self, fileID = ''): """ :param fileID: String of cloud-based file ID. :return: String of filename for a given file ID. """ return filter(lambda x: x['id'] == fileID, self.cloudFiles['items'])[0][ 'originalFilename'] def addReaders(self, fileID = None, emailAddressList = None, retryCount = 0): """ Add reader permission to an export file that has been uploaded to the cloud for the given list of email addresses. Email notification is suppressed by default. :param fileID: String of the cloud file ID to be processed. :param emailAddressList: List of email addresses. :returns: Boolean True if successful, otherwise False. """ # @todo Provide support for retry count success = True self.logger.log('file id: {}'.format(fileID)) self.logger.log('address list: {}'.format(emailAddressList)) for addr in emailAddressList: permission = {'value': addr, 'type': 'user', 'role': 'reader'} if fileID: try: resp = self.driveService.permissions().insert( fileId = fileID, sendNotificationEmails = False, body = permission).execute() self.logger.log( 'Reader permission added for {}.'.format(addr)) except errors.HttpError as error: self.logger.log('An error occurred: {}'.format(error)) success = False if not success and retryCount <= 0: return False elif success: return True else: time.sleep(self.retryDelay) self.logger.log('Retrying adding readers for ID {}.'.format(fileID), 'warning') self.addReaders(fileID = fileID, emailAddressList = emailAddressList, retryCount = retryCount - 1)
class MSGNotifier(object): """ Provides notification service functionality for MSG data processing. Email settings are stored in the local configuration. Usage: from msg_notifier import MSGNotifier self.notifier = MSGNotifier() Public API: sendNotificationEmail(msgBody, testing = False): Send msgBody as a notification to the mailing list defined in the config file. sendMailWithAttachments(msgBody, files = None, testing = False) Send msgBody with files attached as a notification to the mailing list defined in the config file. lastReportDate(noticeType): The last date where a notification of the given type was reported. recordNotificationEvent(noticeType): Record an event in the notification history. """ def __init__(self): """ Constructor. """ warnings.simplefilter('default') warnings.warn("This module is deprecated in favor of SEKNotifier.", DeprecationWarning) self.config = MSGConfiger() self.logger = SEKLogger(__name__, 'info') self.connector = MSGDBConnector() self.conn = self.connector.connectDB() self.cursor = self.conn.cursor() self.dbUtil = MSGDBUtil() self.noticeTable = 'NotificationHistory' self.notificationHeader = "This is a message from the Hawaii Smart " \ "Energy Project MSG Project notification " \ "system.\n\n" self.noReplyNotice = '\n\nThis email account is not monitored. No ' \ 'replies will originate from this ' \ 'account.\n\nYou are receiving this message ' \ 'because you are on the recipient list for ' \ 'notifications for the Hawaii Smart Energy ' \ 'Project.' def sendNotificationEmail(self, msgBody, testing = False): """ This method is an alternative to the multipart method in sendMailWithAttachments. :param msgBody: The body of the message to be sent. :param testing: True if running in testing mode. :returns: True for success, False for an error. """ errorOccurred = False user = self.config.configOptionValue('Notifications', 'email_username') password = self.config.configOptionValue('Notifications', 'email_password') fromaddr = self.config.configOptionValue('Notifications', 'email_from_address') if testing: toaddr = self.config.configOptionValue('Notifications', 'testing_email_recipients') else: toaddr = self.config.configOptionValue('Notifications', 'email_recipients') server = smtplib.SMTP(self.config.configOptionValue('Notifications', 'smtp_server_and_port')) try: server.starttls() except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP STARTTLS: {}".format(detail), 'ERROR') try: server.login(user, password) except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP login: %s" % detail, 'ERROR') senddate = datetime.now().strftime('%Y-%m-%d') subject = "HISEP Notification" msgHeader = "Date: {}\r\nFrom: {}\r\nTo: {}\r\nSubject: {" \ "}\r\nX-Mailer: My-Mail\r\n\r\n".format(senddate, fromaddr, toaddr, subject) msgBody = self.notificationHeader + msgBody msgBody += self.noReplyNotice try: self.logger.log("Send email notification.", 'INFO') server.sendmail(fromaddr, toaddr, msgHeader + msgBody) server.quit() except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP sendmail: {}".format(detail), 'ERROR') return errorOccurred != True def sendMailWithAttachments(self, msgBody, files = None, testing = False): """ Send email along with attachments. :param msgBody: String containing the body of the messsage to send. :param files: List of file paths. This is a mutable argument that should be handled carefully as the default is defined only once. :param testing: True if running in testing mode. :returns: True if no exceptions are raised. """ if files is None: files = [] sys.stderr.write("Sending multipart email.\n") if testing: self.logger.log("Notification testing mode is ON.\n", 'info') errorOccurred = False assert type(files) == list user = self.config.configOptionValue('Notifications', 'email_username') password = self.config.configOptionValue('Notifications', 'email_password') if testing: send_to = self.config.configOptionValue('Notifications', 'testing_email_recipients') else: send_to = self.config.configOptionValue('Notifications', 'email_recipients') send_from = self.config.configOptionValue('Notifications', 'email_from_address') msg = MIMEMultipart() msg['From'] = send_from msg['To'] = send_to msg['Date'] = formatdate(localtime = True) msg['Subject'] = "HISEP Notification" msg.attach(MIMEText(msgBody)) for f in files: sys.stderr.write("Attaching file %s.\n" % f) part = MIMEBase('application', "octet-stream") part.set_payload(open(f, "rb").read()) Encoders.encode_base64(part) part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f)) msg.attach(part) server = smtplib.SMTP(self.config.configOptionValue('Notifications', 'smtp_server_and_port')) try: server.starttls() except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP STARTTLS: %s" % detail, 'ERROR') try: server.login(user, password) except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP login: %s" % detail, 'ERROR') self.logger.log("Send email notification.", 'INFO') try: server.sendmail(send_from, send_to, msg.as_string()) except smtplib.SMTPException as detail: errorOccurred = True self.logger.log("Exception during SMTP sendmail: %s" % detail, 'ERROR') server.quit() if errorOccurred == False: self.logger.log('No exceptions occurred.\n', 'info') return errorOccurred def recordNotificationEvent(self, noticeType = None): """ Save a notification event to the notification history. :param table: String :param noticeType: <enum 'MSGNotificationHistoryTypes'> :returns: Boolean """ if not noticeType: return False if not noticeType in MSGNotificationHistoryTypes: return False cursor = self.cursor sql = """INSERT INTO "{}" ("notificationType", "notificationTime") VALUES ('{}', NOW())""".format(self.noticeTable, noticeType.name) success = self.dbUtil.executeSQL(cursor, sql) self.conn.commit() if not success: raise Exception('Exception while saving the notification time.') return success def lastReportDate(self, noticeType = None): """ Get the last time a notification was reported for the given noticeType. :param noticeType: String indicating the type of the notification. It is stored in the event history. :returns: datetime of last report date. """ if not noticeType or (not noticeType in MSGNotificationHistoryTypes): raise Exception('Invalid notice type.') cursor = self.cursor sql = 'SELECT MAX("notificationTime") FROM "{}" WHERE ' \ '"notificationType" = \'{}\''.format(self.noticeTable, noticeType.name) success = self.dbUtil.executeSQL(cursor, sql) if success: rows = cursor.fetchall() if not rows[0][0]: return None else: return rows[0][0] else: raise Exception('Exception during getting last report date.')
result = insertDataWrapper(path) pattern = 'Process-(\d+),' jobString = str(multiprocessing.current_process()) match = re.search(pattern, jobString) assert match.group(1) is not None, "Process ID was matched." returnDict[match.group(1)] = result if __name__ == '__main__': processCommandLineArguments() inserter = Inserter() if COMMAND_LINE_ARGS.testing: logger.log("Testing mode is ON.\n", 'info') if COMMAND_LINE_ARGS.email: logger.log("Email will be sent.\n", 'info') msg = '' # Used for the notification message. msgBody = '' # Used for the notification message. databaseName = '' if COMMAND_LINE_ARGS.testing: databaseName = configer.configOptionValue("Database", "testing_db_name") else: databaseName = configer.configOptionValue("Database", "db_name") msg = "Recursively inserting data to the database named %s." % databaseName print msg msgBody += msg + "\n"
""" global COMMAND_LINE_ARGS parser = argparse.ArgumentParser(description = '') parser.add_argument('--dbname', help = 'Database file to be uploaded.') parser.add_argument('--fullpath', help = 'Full path to database file to be uploaded.') parser.add_argument('--testing', action = 'store_true', default = False) COMMAND_LINE_ARGS = parser.parse_args() if __name__ == '__main__': logger = SEKLogger(__name__, 'INFO') logger.log("Exporting DBs to cloud.") processCommandLineArguments() exporter = MSGDBExporter() notifier = MSGNotifier() exporter.logger.shouldRecord = True startTime = time.time() dbs = exporter.configer.configOptionValue('Export', 'dbs_to_export').split( ',') fileIDs = exporter.exportDBs(databases = dbs, toCloud = True, testing = COMMAND_LINE_ARGS.testing, deleteOutdated = True) wallTime = time.time() - startTime wallTimeMin = int(wallTime / 60.0)
logger.log('len procs {}'.format(len(procs)), CRITICAL) # cleanup processes for i in range(len(procs)): q.put('STOP') q.close() q.join() for p in procs: p.join() except Exception as detail: logger.log("Exception {}".format(detail), ERROR) multiProcess(paths) logger.log('final row count {}'.format(counter.rowsValue())) assert counter.pathsValue() == TOTAL_PATHS else: # Single core: for p in paths: loader = SingleFileLoader(p) name = loader.meterName() if name in RESULT_CNTS: tupleResult = loader.insertDataFromFile() RESULT_CNTS[name] += tupleResult[0] EXCEPTION_CNTS[name] += tupleResult[1] else: (RESULT_CNTS[name], EXCEPTION_CNTS[name]) = loader.insertDataFromFile()
class MECOXMLParser(object): """ Parses XML for MECO data. """ tableName = '' def __init__(self, testing = False): """ Constructor. :param testing: (optional) Boolean indicating if Testing Mode is on. """ self.logger = SEKLogger(__name__, 'silent') if (testing): self.logger.log("Testing Mode is ON.", 'info') self.debug = False self.configer = MSGConfiger() if self.configer.configOptionValue("Debugging", 'debug') == True: self.debug = True self.util = MSGDBUtil() self.mapper = MECOMapper() self.connector = MSGDBConnector(testing) self.conn = self.connector.connectDB() self.filename = None self.fileObject = None self.processForInsertElementCount = 0 self.inserter = MECODBInserter() self.insertDataIntoDatabase = False # Count number of times sections in source data are encountered. self.tableNameCount = {'SSNExportDocument': 0, 'MeterData': 0, 'RegisterData': 0, 'RegisterRead': 0, 'Tier': 0, 'Register': 0, 'IntervalReadData': 0, 'Interval': 0, 'Reading': 0, 'IntervalStatus': 0, 'ChannelStatus': 0, 'EventData': 0, 'Event': 0} # Use this dictionary to track which channels were processed when # readings are being processed. this is to prevent duplicate channel # data from being inserted. self.channelProcessed = {} self.initChannelProcessed() # Tables to be inserted to. self.insertTables = self.configer.insertTables self.lastSeqVal = None self.fKeyVal = None self.lastTable = None self.fkDeterminer = MECOFKDeterminer() self.dupeChecker = MECODupeChecker() self.currentMeterName = None self.currentIntervalEndTime = None self.currentRegisterReadReadTime = None self.dupesExist = False self.channelDupeExists = False # For Reading dupes. self.numberDupeExists = False # For Register dupes. self.eventTimeDupeExists = False # For Event dupes. self.commitCount = 0 self.readingDupeOnInsertCount = 0 # For Reading dupes. self.registerDupeOnInsertCount = 0 # For Register dupes. self.eventDupeOnInsertCount = 0 # For Event dupes. self.dataProcessCount = 0 self.readingDupeCheckCount = 0 # For Reading dupes. self.registerDupeCheckCount = 0 # For Register dupes. self.eventDupeCheckCount = 0 # For Event dupes. self.insertCount = 0 self.cumulativeInsertCount = 0 self.nonProcessForInsertElementCount = 0 self.readingInsertCount = 0 self.registerInsertCount = 0 self.eventInsertCount = 0 self.totalReadingInsertCount = 0 self.totalRegisterInsertCount = 0 self.totalEventInsertCount = 0 self.totalReadingDupeOnInsertCount = 0 self.totalRegisterDupeOnInsertCount = 0 self.totalEventDupeOnInsertCount = 0 def parseXML(self, fileObject, insert = False, jobID = ''): """ Parse an XML file. :param fileObject: a file object referencing an XML file. :param insert: (optional) True to insert to the database | False to perform no inserts. :returns: String containing a concise log of parsing. """ print "parseXML:" self.commitCount = 0 self.insertDataIntoDatabase = insert parseMsg = "\nParsing XML in %s.\n" % self.filename sys.stderr.write(parseMsg) parseLog = parseMsg tree = ET.parse(fileObject) root = tree.getroot() parseLog += self.walkTheTreeFromRoot(root, jobID = jobID) return parseLog def tableNameForAnElement(self, element): """ Get the tablename for an element. :param element: Element tree element. :returns: table name """ try: name = re.search('\{.*\}(.*)', element.tag).group(1) except: name = None return name def processDataToBeInserted(self, columnsAndValues, currentTableName, fKeyValue, parseLog, pkeyCol, jobID = ''): """ This is the method that performs insertion of parsed data to the database. Duplicate checks are performed on the endpoints of the data branches. :param columnsAndValues: A dictionary containing columns and their values. :param currentTableName: The name of the current table. :param fKeyValue: The value of the foreign key. :param parseLog: String containing a concise log of operations. :param pkeyCol: Column name for the primary key. :param jobID: Identifier for multiprocessing process. :returns: A string containing the parse log. """ self.dataProcessCount += 1 # Handle a special case for duplicate reading data. # Intercept the duplicate reading data before insert. if currentTableName == "Reading": self.channelDupeExists = self.dupeChecker.readingBranchDupeExists( self.conn, self.currentMeterName, self.currentIntervalEndTime, columnsAndValues['Channel']) self.readingDupeCheckCount += 1 if currentTableName == "Register": self.numberDupeExists = self.dupeChecker.registerBranchDupeExists( self.conn, self.currentMeterName, self.currentRegisterReadReadTime, columnsAndValues['Number']) self.registerDupeCheckCount += 1 if currentTableName == "Event": self.eventTimeDupeExists = self.dupeChecker.eventBranchDupeExists( self.conn, self.currentMeterName, columnsAndValues['EventTime']) self.eventDupeCheckCount += 1 # Only perform an insert if there are no duplicate values # for the channel. if not self.channelDupeExists and not self.numberDupeExists and not \ self.eventTimeDupeExists: # *********************** # ***** INSERT DATA ***** # *********************** cur = self.inserter.insertData(self.conn, currentTableName, columnsAndValues, fKeyVal = fKeyValue, withoutCommit = 1) # The last 1 indicates don't commit. Commits are handled externally. self.insertCount += 1 self.cumulativeInsertCount += 1 # Only attempt getting the last sequence value if an insertion # took place. self.lastSeqVal = self.util.getLastSequenceID(self.conn, currentTableName, pkeyCol) # Store the primary key. self.fkDeterminer.pkValforCol[pkeyCol] = self.lastSeqVal if currentTableName == "Reading": self.readingInsertCount += 1 self.totalReadingInsertCount += 1 elif currentTableName == "Register": self.registerInsertCount += 1 self.totalRegisterInsertCount += 1 elif currentTableName == "Event": self.eventInsertCount += 1 self.totalEventInsertCount += 1 else: # Don't insert into Reading or Register table if a dupe exists. if (self.channelDupeExists): self.readingDupeOnInsertCount += 1 self.totalReadingDupeOnInsertCount += 1 if self.readingDupeOnInsertCount > 0 and self\ .readingDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite( "%s:{rd-dupe==>}" % jobID) # Also, verify the data is equivalent to the existing record. matchingValues = self.dupeChecker.readingValuesAreInTheDatabase( self.conn, columnsAndValues) assert matchingValues == True, "Duplicate check found " \ "non-matching values for meter" \ " %s," \ " endtime %s, channel %s (%s, " \ "%s)." % ( self.currentMeterName, self.currentIntervalEndTime, columnsAndValues['Channel'], columnsAndValues['RawValue'], columnsAndValues['Value']) self.channelDupeExists = False elif (self.numberDupeExists): self.registerDupeOnInsertCount += 1 self.totalRegisterDupeOnInsertCount += 1 if self.registerDupeOnInsertCount > 0 and self\ .registerDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite( "%s:{re-dupe==>}" % jobID) self.numberDupeExists = False elif (self.eventTimeDupeExists): self.eventDupeOnInsertCount += 1 self.totalEventDupeOnInsertCount += 1 if self.eventDupeOnInsertCount > 0 and self\ .eventDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite( "%s:{ev-dupe==>}" % jobID) self.eventTimeDupeExists = False else: assert True == False, "Duplicate condition does not exist." self.logger.log('Record not inserted for %s.' % columnsAndValues, 'silent') return parseLog def generateConciseLogEntries(self, jobID = '', reportType = None): """ Create log entries in the concise log. :param jobID: Identifier used to distinguish multiprocessing jobs. :returns: A concatenated string of log entries. """ # @todo Change report type to enum type. log = '' if reportType == 'FINAL': self.logger.log('Final report', 'info') if self.readingDupeOnInsertCount > 0 or self\ .registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount\ > 0: log = self.logger.logAndWrite("%s:{%srd,%sre,%sev}" % ( jobID, self.totalReadingDupeOnInsertCount, self.totalRegisterDupeOnInsertCount, self.totalEventDupeOnInsertCount)) else: log = '' log += self.logger.logAndWrite("(%s)" % self.commitCount) log += self.logger.logAndWrite( "[%s]" % self.processForInsertElementCount) log += self.logger.logAndWrite("<%srd,%sre,%sev,%s>" % ( self.totalReadingInsertCount, self.totalRegisterInsertCount, self.totalEventInsertCount, self.cumulativeInsertCount)) elif reportType == 'INTERMEDIARY': if self.readingDupeOnInsertCount > 0 or self\ .registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount\ > 0: log = self.logger.logAndWrite("%s:{%srd,%sre,%sev}" % ( jobID, self.readingDupeOnInsertCount, self.registerDupeOnInsertCount, self.eventDupeOnInsertCount)) else: log = '' log += self.logger.logAndWrite("(%s)" % self.commitCount) log += self.logger.logAndWrite( "[%s]" % self.processForInsertElementCount) log += self.logger.logAndWrite("<%srd,%sre,%sev,%s,%s>" % ( self.readingInsertCount, self.registerInsertCount, self.eventInsertCount, self.insertCount, self.cumulativeInsertCount)) return log def resetGroupCounters(self): """ Reset counters that are keeping track of groups. """ self.readingDupeOnInsertCount = 0 self.insertCount = 0 self.readingInsertCount = 0 self.registerDupeOnInsertCount = 0 self.registerInsertCount = 0 self.eventInsertCount = 0 self.eventDupeOnInsertCount = 0 def performTableBasedOperations(self, columnsAndValues, currentTableName, element): """ Perform operations that are based on the current table. :param columnsAndValues :param currentTableName :param element """ if currentTableName == "MeterData": self.currentMeterName = columnsAndValues['MeterName'] elif currentTableName == "Interval": self.currentIntervalEndTime = columnsAndValues['EndTime'] elif currentTableName == "RegisterRead": self.currentRegisterReadReadTime = columnsAndValues['ReadTime'] elif currentTableName == "Event": columnsAndValues['Event_Content'] = element.text def walkTheTreeFromRoot(self, root, jobID = ''): """ Walk an XML tree from its root node. :param root: The root node of an XML tree. :param jobID: Identifier used to distinguish multiprocessing jobs. :returns: String containing a concise log of parsing activity. """ parseLog = '' walker = root.iter() for element, nextElement in self.getNext(walker): # Process every element in the tree while reading ahead to get # the next element. currentTableName = self.tableNameForAnElement(element) nextTableName = self.tableNameForAnElement(nextElement) assert currentTableName is not None, "Current table does not exist." # Maintain a count of tables encountered. self.tableNameCount[currentTableName] += 1 columnsAndValues = {} it = iter(sorted(element.attrib.iteritems())) for item in list(it): # Create a dictionary of column names and values. columnsAndValues[item[0]] = item[1] if currentTableName in self.insertTables: # Check if the current table is one of the tables to have data # inserted. self.processForInsertElementCount += 1 if self.debug: self.logger.log("Processing table %s, next is %s." % ( currentTableName, nextTableName), 'debug') # Get the column name for the primary key. pkeyCol = self.mapper.dbColumnsForTable(currentTableName)[ '_pkey'] fkeyCol = None fKeyValue = None try: # Get the column name for the foreign key. fkeyCol = self.mapper.dbColumnsForTable(currentTableName)[ '_fkey'] except: pass if self.debug: self.logger.log("foreign key col (fkey) = %s" % fkeyCol, 'debug') self.logger.log("primary key col (pkey) = %s" % pkeyCol, 'debug') self.logger.log(columnsAndValues, 'debug') if fkeyCol is not None: # Get the foreign key value. fKeyValue = self.fkDeterminer.pkValforCol[fkeyCol] if self.debug: self.logger.log("fKeyValue = %s" % fKeyValue, 'debug') self.performTableBasedOperations(columnsAndValues, currentTableName, element) if self.insertDataIntoDatabase: # Data is intended to be inserted into the database. parseLog = self.processDataToBeInserted(columnsAndValues, currentTableName, fKeyValue, parseLog, pkeyCol, jobID = jobID) if self.debug: self.logger.log("lastSeqVal = ", self.lastSeqVal) if self.lastReading(currentTableName, nextTableName): # The last reading set has been reached. if self.debug: self.logger.log("----- last reading found -----", 'debug') parseLog += self.generateConciseLogEntries(jobID = jobID, reportType = 'INTERMEDIARY') self.resetGroupCounters() parseLog += self.logger.logAndWrite("*") self.commitCount += 1 self.conn.commit() if self.lastRegister(currentTableName, nextTableName): # The last register set has been reached. if self.debug: self.logger.log("----- last register found -----", 'debug') # Initial commit. if self.commitCount == 0: parseLog += self.generateConciseLogEntries(jobID = jobID, reportType = 'INTERMEDIARY') self.resetGroupCounters() # Final commit. parseLog += self.logger.logAndWrite("---") parseLog += self.generateConciseLogEntries(jobID = jobID, reportType = 'FINAL') self.resetGroupCounters() parseLog += self.logger.logAndWrite("*") self.commitCount += 1 self.conn.commit() sys.stderr.write("\n") self.logger.log("Data process count = %s." % self.dataProcessCount, 'info') self.logger.log( "Reading dupe check count = %s." % self.readingDupeCheckCount, 'info') return parseLog def lastReading(self, currentTable, nextTable): """ Determine if the last reading is being visited. :param currentTable: current table being processsed. :param nextTable: next table to be processed. :returns: True if last object in Reading table was read, otherwise return False. """ if currentTable == "Reading" and ( nextTable == "MeterData" or nextTable == None): return True return False def lastRegister(self, currentTable, nextTable): """ Determine if the last register is being visited. :param currentTable: current table being processsed. :param nextTable: next table to be processed. :returns: True if last object in Register table was read, otherwise return False. """ if currentTable == "Register" and ( nextTable == "MeterData" or nextTable == None): return True return False def getNext(self, somethingIterable, window = 1): """ Return the current item and next item in an iterable data structure. :param somethingIterable: Something that has an iterator. :param window: How far to look ahead in the collection. :returns: The current iterable value and the next iterable value. """ items, nexts = tee(somethingIterable, 2) nexts = islice(nexts, window, None) return izip_longest(items, nexts) def initChannelProcessed(self): """ Initialize the dictionary for channel processing. """ self.channelProcessed = {'1': False, '2': False, '3': False, '4': False} def getLastElement(self, rows): """ Get the last element in a collection. Example: rows = (element1, element2, element3) getLastElement(rows) # return element3 :param rows: Result rows from a query. :returns: The last element in the collection. """ for i, var in enumerate(rows): if i == len(rows) - 1: return var
class MSGFileUtil(object): """ Utilities related to files and directories. """ def __init__(self): """ Constructor. """ warnings.simplefilter('default') warnings.warn("This module is deprecated in favor of SEKFileUtil.", DeprecationWarning) self.logger = SEKLogger(__name__, 'DEBUG') def validDirectory(self, path): """ Verify that the path is a valid directory. :param path: Path to check. :returns: True if path is a valid directory. """ if os.path.exists(path) and os.path.isdir(path): return True else: return False def md5Checksum(self, fullPath): """ Get the MD5 checksum for the file given by fullPath. :param fullPath: Full path of the file to generate for which to generate a checksum. :returns: MD5 checksum value as a hex digest. """ try: f = open(fullPath, mode = 'rb') content = hashlib.md5() for buf in iter(partial(f.read, 128), b''): content.update(buf) md5sum = content.hexdigest() f.close() return md5sum except IOError as detail: self.logger.log( 'Exception during checksum calculation: %s' % detail, 'ERROR') def gzipUncompressFile(self, srcPath, destPath): """ Gzip uncompress a file given by fullPath. @todo Need to deal with large file sizes. Stop reading into memory. :param srcPath: Full path of the file to be uncompressed. :param destPath: Full path of file to be written to. """ self.logger.log( 'Uncompressing gzip source %s to %s' % (srcPath, destPath), 'DEBUG') gzipFile = gzip.open(srcPath, "rb") uncompressedFile = open(destPath, "wb") decoded = gzipFile.read() try: uncompressedFile.write(decoded) except: self.logger.log("Exception while writing uncompressed file.") gzipFile.close() uncompressedFile.close() def gzipCompressFile(self, fullPath): """ Perform gzip compression on a file at fullPath. @todo Generalize this method. :param fullPath: Full path of the file to be compressed. :returns: Boolean: True if successful, False otherwise. """ success = False self.logger.log('Gzip compressing %s.' % fullPath) try: f_in = open('%s' % (fullPath), 'rb') f_out = gzip.open('%s.gz' % (fullPath), 'wb') f_out.writelines(f_in) f_out.close() f_in.close() success = True except IOError as detail: self.logger.log('IOError exception while gzipping: %s' % detail, 'ERROR') return success def splitFile(self, fullPath = '', chunkSize = 0): """ @DEPRECATED Split a file into chunks. Write output files to base path of the input file. Adapted from https://gist.github.com/mattiasostmar/7883550. :param fullPath: :param chunkSize: :returns: A list of file chunks in full path form. """ fChunks = [] basePath = os.path.dirname(fullPath) baseName = os.path.basename(fullPath) self.logger.log('basename: %s' % baseName) f = open(fullPath, 'rb') data = f.read() f.close() bytes = len(data) # Ensure splitting doesn't happen if it's not needed. if bytes <= chunkSize: return [fullPath] chunkNames = [] fCnt = 0 for i in range(0, bytes + 1, chunkSize): fn1 = "%s/%s.%s" % (basePath, baseName, fCnt) self.logger.log("Splitter writing to %s" % fn1, 'INFO') fChunks.append(fn1) chunkNames.append(fn1) try: f = open(fn1, 'wb') f.write(data[i:i + chunkSize]) f.close() except Exception as detail: print "Exception during writing split file: %s" % detail fCnt += 1 return fChunks def splitLargeFile(self, fullPath = '', numChunks = 0, chunkSize = 0): """ Split a large file into chunks. :param fullPath: String :param numChunks: Int number of files to be split into. :param chunkSize: @DEPRECATED :return: List of file chunk names in full path form. """ fChunks = [] basePath = os.path.dirname(fullPath) baseName = os.path.basename(fullPath) self.logger.log('basename: %s' % baseName) fp = open(fullPath, 'rb') fsize = os.path.getsize(fullPath) chunkSize = int(float(fsize) / float(numChunks)) totalBytes = 0 self.logger.log('chunk size: %s' % chunkSize) if numChunks == 0 or numChunks == 1: return [fullPath] for x in range(numChunks): if x == numChunks - 1: chunkSize = fsize - totalBytes data = fp.read(chunkSize) totalBytes += len(data) fout = open("%s/%s.%s" % (basePath, baseName, x), "wb") self.logger.log('Writing %s/%s.%s' % (basePath, baseName, x), 'debug') fChunks.append("%s/%s.%s" % (basePath, baseName, x)) fout.write(data) fout.close() return fChunks def fileSize(self, fullPath = ''): """ Get the size in bytes for the file at fullPath. :param fullPath: :return: size in bytes """ return os.path.getsize(fullPath)
class SEKLoggerTester(unittest.TestCase): def setUp(self): self.logger = SEKLogger(__name__, level = DEBUG) print 'logger level: %s' % self.logger.loggerLevel def testInit(self): self.logger.log('Testing init.',level = INFO) self.assertIsNotNone(self.logger) def testLogRecording(self): self.logger.log('Testing log recording.', INFO) msg = "Recording test." self.logger.startRecording() self.logger.log(msg, 'info') self.logger.endRecording() self.logger.log("This should not be logged.", INFO) result = re.search(msg, self.logger.recording).group(0) self.logger.log("recording result: %s" % self.logger.recording) self.assertEqual(result, msg) def testSilentLogging(self): return self.logger.log('Testing silent logging.', INFO) msg = "Recording test." self.logger.startRecording() self.logger.log(msg, SILENT) self.logger.endRecording() self.assertEqual(self.logger.recording, '') def testDebugLogging(self): self.logger.log('Testing debug logging', DEBUG) def testDoublingOfLoggingOutput(self): self.logger.log('This is a test of doubling of logger output at the beginning of a test.')
class MSGNOAAWeatherDataInserter(object): """ Performs weather data insertion to a database. """ def __init__(self, testing=False): """ Constructor. :param testing: True if testing mode is being used. """ self.logger = SEKLogger(__name__, 'info') self.dbUtil = MSGDBUtil() self.dupeChecker = MSGWeatherDataDupeChecker() def insertDataDict(self, conn, tableName, listOfDataDicts, commit=False): """ Given a table name and a dictionary of column names and values, insert them to the db. :param conn: A database connection. :param tableName: Name of the DB table to be inserted to. :param columnsAndValues: Dictionary of columns and values to be inserted to the DB. :param (optional) commit: A flag indicated that DB transactions will be committed. :returns: Set of datetimes processed. """ cur = conn.cursor() processedDateTimes = set() for row in listOfDataDicts: # Add a creation timestamp using the SQL function. row['created'] = 'NOW()' cols = [] vals = [] for col in row.keys(): # Prepare the columns and values for insertion via SQL. cols.append(col) if (row[col] != 'NULL'): # Surround each value with single quotes... vals.append("'%s'" % row[col]) else: # Except for NULL values. vals.append("%s" % row[col]) sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % ( tableName, ','.join(cols), ','.join(vals)) if self.dupeChecker.duplicateExists(cur, row['wban'], row['datetime'], row['record_type']): self.logger.log("Dupe found, dropping dupe.", 'info') else: processedDateTimes.add( dt.datetime.strptime(row['datetime'], "%Y-%m-%d %H:%M")) if self.dbUtil.executeSQL(cur, sql, exitOnFail=False) is False: # An error occurred. for col in sorted(row.keys()): print "%s: %s" % (col, row[col]) sys.exit(-1) if commit: try: conn.commit() except: self.logger.log("ERROR: Commit failed.", 'debug') return processedDateTimes
class MSGConfiger(object): """ Supports system-specific configuration for MECO data processing. The site-level configuration file is located in ~/.meco-data-operations.cfg. Usage: configer = MSGConfiger() """ def __init__(self): """ Constructor. """ self._config = ConfigParser.ConfigParser() self.logger = SEKLogger(__name__, 'INFO') self.fileUtil = SEKFileUtil() # Define tables that will have data inserted. Data will only be inserted # to tables that are defined here. self.insertTables = ('MeterData', 'RegisterData', 'RegisterRead', 'Tier', 'Register', 'IntervalReadData', 'Interval', 'Reading', 'EventData', 'Event') # Check permissions on the config file. Refuse to run if the permissions # are not set appropriately. configFilePath = '~/.msg-data-operations.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() 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 configOptionValue(self, section, option): """ Get a configuration value from the local configuration file. :param section: String of section in config file. :param option: String of option in config file. :returns: The value contained in the configuration file. """ try: configValue = self._config.get(section, option) if configValue == "True": return True elif configValue == "False": return False else: return configValue except: self.logger.log( "Failed when getting configuration option {} in section {" "}.".format(option, section), 'error') sys.exit(-1)
class MSGEgaugeNewDataChecker(object): """ Provide notification of newly loaded MSG eGauge data. This uses notification type MSG_EGAUGE_SERVICE. """ def __init__(self): """ Constructor. """ print __name__ self.logger = SEKLogger(__name__) self.connector = MSGDBConnector() self.dbUtil = MSGDBUtil() self.notifier = MSGNotifier() self.configer = MSGConfiger() def newDataCount(self): """ Measure the amount of new data that is present since the last time new data was reported. """ cursor = self.connector.conn.cursor() tableName = 'EgaugeEnergyAutoload' lastTime = self.lastReportDate('MSG_EGAUGE_SERVICE') if lastTime is None: lastTime = '1900-01-01' sql = """SELECT COUNT(*) FROM "%s" WHERE datetime > '%s'""" % ( tableName, lastTime) success = self.dbUtil.executeSQL(cursor, sql) if success: rows = cursor.fetchall() if not rows[0][0]: return 0 else: return rows[0][0] else: # @todo Raise an exception. return None def lastReportDate(self, notificationType): """ Get the last time a notification was reported. :param notificationType: A string indicating the type of the notification. It is stored in the event history. :returns: datetime of last report date. """ cursor = self.connector.conn.cursor() sql = """SELECT MAX("notificationTime") FROM "%s" WHERE "notificationType" = '%s'""" % ( NOTIFICATION_HISTORY_TABLE, notificationType) success = self.dbUtil.executeSQL(cursor, sql) if success: rows = cursor.fetchall() if not rows[0][0]: return None else: return rows[0][0] else: # @todo Raise an exception. return None def saveNotificationTime(self): """ Save the notification event to the notification history. """ cursor = self.connector.conn.cursor() sql = """INSERT INTO "%s" ("notificationType", "notificationTime") VALUES ('MSG_EGAUGE_SERVICE', NOW())""" % NOTIFICATION_HISTORY_TABLE success = self.dbUtil.executeSQL(cursor, sql) self.connector.conn.commit() if not success: # @todo Raise an exception. self.logger.log( 'An error occurred while saving the notification time.') def sendNewDataNotification(self, testing = False): """ Sending notification reporting on new data being available since the last time new data was reported. :param testing: Use testing mode when True. """ lastReportDate = self.lastReportDate('MSG_EGAUGE_SERVICE') if not lastReportDate: lastReportDate = "never" msgBody = '\nNew MSG eGauge data has been loaded to %s.' % self\ .connector.dbName msgBody += '\n\n' msgBody += 'The new data count is %s readings.' % self.newDataCount() msgBody += '\n\n' msgBody += 'The last report date was %s.' % lastReportDate msgBody += '\n\n' self.notifier.sendNotificationEmail(msgBody, testing = testing) self.saveNotificationTime()
class MECODBInserter(object): """ Provides methods that perform insertion of MECO data. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'debug') self.mapper = MECOMapper() self.dupeChecker = MECODupeChecker() self.dbUtil = MSGDBUtil() def __call__(self, param): print "CallableClass.__call__(%s)" % param def insertData(self, conn, tableName, columnsAndValues, fKeyVal = None, withoutCommit = 0): """ Given a table name and a dictionary of column names and values, insert them to the DB. :param conn: database connection :param tableName: name of the db table :param columnsAndValues: dictionary of columns and values to be inserted to the db :param (optional) fKeyVal: an explicit foreign key value :param (optional) withoutCommit: a flag indicated that the insert will not be immediately committed :returns: A database cursor. """ cur = conn.cursor() # Get a dictionary of mapped (from DB to source data) column names. columnDict = self.mapper.getDBColNameDict(tableName) dbColsAndVals = {} if VISUALIZE_DATA: print "----------" + tableName + "----------" print columnDict print columnsAndValues for col in columnDict.keys(): # Use default as the value for the primary key so that the # private key is obtained from the predefined sequence. if col == '_pkey': if VISUALIZE_DATA: print columnDict[col], # DB col name. print 'DEFAULT' dbColsAndVals[columnDict[col]] = 'DEFAULT' # For the foreign key, set the value from the given parameter. elif col == '_fkey': if VISUALIZE_DATA: print columnDict[col], # DB col name. print fKeyVal dbColsAndVals[columnDict[col]] = fKeyVal else: if VISUALIZE_DATA: print columnDict[col], # DB col name. # The Register and Reading tables need to handle NULL # values as a special case. if tableName == 'Register' or tableName == 'Reading': try: if VISUALIZE_DATA: print columnsAndValues[col] # data source value dbColsAndVals[columnDict[col]] = columnsAndValues[col] except: if VISUALIZE_DATA: print 'NULL' dbColsAndVals[columnDict[col]] = 'NULL' # For all other cases, simply pass the value. else: if VISUALIZE_DATA: print columnsAndValues[col] # data source value dbColsAndVals[columnDict[col]] = columnsAndValues[col] # Add a creation timestamp to MeterData. if tableName == 'MeterData': dbColsAndVals['created'] = 'NOW()' cols = [] vals = [] for col in dbColsAndVals.keys(): cols.append(col) # DEFAULT, NULL and NOW() need to appear without quotes. if dbColsAndVals[col] in {'DEFAULT', 'NULL', 'NOW()'}: vals.append(dbColsAndVals[col]) else: vals.append("'%s'" % dbColsAndVals[ col]) # Surround value with single quotes. sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % ( tableName, ','.join(cols), ','.join(vals)) self.dbUtil.executeSQL(cur, sql) if withoutCommit == 0: try: conn.commit() except: self.logger.log("ERROR: Commit failed.", 'debug') return cur
class MSGDataVerifier(object): """ Perform verification procedures related to data integrity. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'DEBUG') self.cursor = MSGDBConnector().connectDB().cursor() self.dbUtil = MSGDBUtil() def mecoReadingsDupeCount(self): """ Generate counts of MECO dupe readings. """ dupes = 0 startDate = lambda y, m: '%d-%02d-%02d' % (y, m, 1) endDate = lambda y, m: '%d-%02d-%02d' % ( y, m, calendar.monthrange(y, m)[1]) for y in YEARS: startDates = [startDate(y, m) for m in map(lambda x: x + 1, range(12))] endDates = [endDate(y, m) for m in map(lambda x: x + 1, range(12))] for start in startDates: cnt = self.__mecoReadingsDupeCount(start, endDates[ startDates.index(start)]) self.logger.log('start: %s, dupe cnt: %s' % (start, cnt), 'INFO') dupes += cnt return dupes def __mecoReadingsDupeCount(self, startDate, endDate): """ :param startDate: :param endDate: :returns: DB row count. """ self.dbUtil.executeSQL(self.cursor, """SELECT "Interval".end_time, "MeterData".meter_name, "Reading".channel FROM "MeterData" INNER JOIN "IntervalReadData" ON "MeterData" .meter_data_id = "IntervalReadData".meter_data_id INNER JOIN "Interval" ON "IntervalReadData" .interval_read_data_id = "Interval".interval_read_data_id INNER JOIN "Reading" ON "Interval".interval_id = "Reading" .interval_id WHERE "Interval".end_time BETWEEN '%s' and '%s' GROUP BY "MeterData".meter_name, "Interval".end_time, "Reading".channel HAVING (COUNT(*) > 1)""" % (startDate, endDate)) return len(self.cursor.fetchall()) def egaugeAggregationCount(self): """ There should not be more than 96 15-min interval endpoints within a single calendar day for a given sub ID. :return: """ pass
class MSGDataAggregatorTester(unittest.TestCase): """ Unit tests for MSG Data Aggregator. """ def setUp(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'DEBUG') self.aggregator = MSGDataAggregator() self.testStart = '2014-01-02 11:59' self.testEnd = '2014-01-02 12:14' self.rawTypes = ['weather', 'egauge', 'circuit', 'irradiance'] def testIrradianceFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = 'timestamp' rows = [] for row in self.aggregator.rawData(dataType = 'irradiance', orderBy = [timeCol, 'sensor_id'], timestampCol = timeCol, startDate = self.testStart, endDate = self.testEnd): rows.append(row) self.assertIsNotNone(rows, 'Rows are present.') def testWeatherFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = 'timestamp' rows = [] for row in self.aggregator.rawData(dataType = 'weather', orderBy = [timeCol], timestampCol = timeCol, startDate = self.testStart, endDate = self.testEnd): rows.append(row) self.assertIsNotNone(rows, 'Rows are present.') def testCircuitFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = 'timestamp' rows = [] for row in self.aggregator.rawData(dataType = 'circuit', orderBy = [timeCol, 'circuit'], timestampCol = timeCol, startDate = self.testStart, endDate = self.testEnd): rows.append(row) self.assertIsNotNone(rows, 'Rows are present.') def testEgaugeFetch(self): """ Test raw data fetching over the testing time interval. :return: """ timeCol = 'datetime' rows = [] for row in self.aggregator.rawData(dataType = 'egauge', orderBy = [timeCol, 'egauge_id'], timestampCol = timeCol, startDate = self.testStart, endDate = self.testEnd): rows.append(row) self.assertIsNotNone(rows, 'Rows are present.') def testEgaugeAggregation(self): """ Perform aggregation over the testing time interval. :return: """ self.logger.log('Testing Egauge aggregation.') rowCnt = 0 agg = self.aggregator.aggregatedData(dataType = 'egauge', aggregationType = 'agg_egauge', timeColumnName = 'datetime', subkeyColumnName = 'egauge_id', startDate = self.testStart, endDate = self.testEnd) print [col for col in agg.columns] for row in agg.data: print row rowCnt += 1 self.logger.log('row cnt %d' % rowCnt) self.logger.log('agg cols: %d' % len(agg.columns)) self.assertEqual(rowCnt, 5, 'Row count not correct.') self.assertEqual(len(agg.columns), 37, 'Egauge columns not equal to 37.') self.aggregator.insertAggregatedData(agg = agg) def testCircuitAggregation(self): """ Test aggregation over the testing time interval. :return: """ self.logger.log('Testing circuit aggregation.') rowCnt = 0 agg = self.aggregator.aggregatedData(dataType = 'circuit', aggregationType = 'agg_circuit', timeColumnName = 'timestamp', subkeyColumnName = 'circuit', startDate = self.testStart, endDate = self.testEnd) print [col for col in agg.columns] for row in agg.data: print row rowCnt += 1 self.logger.log('row cnt %d' % rowCnt) self.logger.log('agg cols: %d' % len(agg.columns)) self.assertEqual(rowCnt, 2, 'Row count not correct.') self.assertEqual(len(agg.columns), 8, 'Circuit columns not equal to 8.') self.aggregator.insertAggregatedData(agg = agg) def testIrradianceAggregation(self): """ Test aggregation over the testing time interval. :return: """ self.logger.log('Testing irradiance aggregation.') rowCnt = 0 datatype = 'irradiance' agg = self.aggregator.aggregatedData(dataType = datatype, aggregationType = 'agg_irradiance', timeColumnName = 'timestamp', subkeyColumnName = 'sensor_id', startDate = self.testStart, endDate = self.testEnd) for row in agg.data: print '%d: %s' % (rowCnt, row) rowCnt += 1 self.logger.log('row cnt %d' % rowCnt) self.logger.log('agg cols: %d' % len(agg.columns)) self.assertEqual(rowCnt, 1, 'Row count not correct.') self.assertEqual(len(agg.columns), 3, 'Irradiance columns not equal to 3.') self.aggregator.insertAggregatedData(agg = agg) def testWeatherAggregation(self): """ Test aggregation over the testing time interval. :return: """ rowCnt = 0 agg = self.aggregator.aggregatedData(dataType = 'weather', aggregationType = 'agg_weather', timeColumnName = 'timestamp', subkeyColumnName = None, startDate = self.testStart, endDate = self.testEnd) for row in agg.data: print '%d: %s' % (rowCnt, row) rowCnt += 1 self.assertEqual(rowCnt, 1, 'Row count not correct.') self.logger.log('agg cols: %d' % len(agg.columns)) self.assertEqual(len(agg.columns), 3, 'Weather columns not equal to 3.') self.aggregator.insertAggregatedData(agg = agg) def test_month_starts_and_ends(self): """ Test retrieving the list of start and end dates for each month in a given aggregation time period. All starts except one should be at time 00:00:00. Starts and ends appear in alternating order but are joined in a tuple. """ # @REVIEWED # @todo Optimize by combine start and end tests. startCnt = 0 endCnt = 0 def test_starts(timeColName, dataType): global startCnt self.logger.log('testing {},{}'.format(timeColName, dataType)) # Take every other value from the unzipped pairs. starts = [x for x in itertools.islice( zip(*self.aggregator.monthStartsAndEnds(timeColName, dataType)), 0, None, 2)] startCnt = len(starts) # Test on the flattened start values. self.assertLessEqual(len(filter( lambda x: x.time() != datetime.strptime('00:00:00', '%H:%M:%S').time(), list(itertools.chain.from_iterable(starts)))), 1) def test_ends(timeColName, dataType): global endCnt self.logger.log('testing {},{}'.format(timeColName, dataType)) # Take every other value from the unzipped pairs. ends = [x for x in itertools.islice( zip(*self.aggregator.monthStartsAndEnds(timeColName, dataType)), 1, None, 2)] endCnt = len(ends) # Test on the flattened end values. self.assertLessEqual(len(filter( lambda x: x.time() != self.aggregator.incrementEndpoint( datetime.strptime('23:59:59', '%H:%M:%S')).time(), list(itertools.chain.from_iterable(ends)))), 1) for myType in ['weather', 'egauge', 'circuit', 'irradiance']: if myType == 'egauge': test_starts('datetime', myType) test_ends('datetime', myType) else: test_starts('timestamp', myType) test_ends('timestamp', myType) self.assertEquals(startCnt, endCnt) def testAggregateAllData(self): # @todo Revise this test so that live data is not affected. return for myType in ['weather', 'egauge', 'circuit', 'irradiance']: self.aggregator.aggregateAllData(dataType = myType) def testExistingIntervals(self): self.logger.log('Testing existing intervals.') aggType = [('agg_weather', 'timestamp'), ('agg_egauge', 'datetime'), ('agg_circuit', 'timestamp'), ('agg_irradiance', 'timestamp')] self.assertEqual(len( map(lambda x: self.aggregator.existingIntervals(x[0], x[1])[0], aggType)) == len(aggType), 'Mismatched existing aggregation intervals.') def testUnaggregatedIntervals1(self): # @todo provide static test data for this test. self.logger.log('testing unagged intervals') MINUTE_POSITION = 4 INTERVAL_DURATION = 15 weather = [] for row in self.aggregator.unaggregatedEndpoints('weather', 'agg_weather', 'timestamp'): self.logger.log('row: {}'.format(row)) def testUnaggregatedIntervals2(self): # @todo provide static test data for this test. self.logger.log('testing unagged intervals') MINUTE_POSITION = 4 INTERVAL_DURATION = 15 egauge = [] for row in self.aggregator.unaggregatedEndpoints('egauge', 'agg_egauge', 'datetime', 'egauge_id'): self.logger.log('row: {}'.format(row)) def testLastAggregationEndpoint(self): # Covered by testUnaggregatedIntervals. self.logger.log('Testing last agg endpoint') print self.aggregator.lastAggregationEndpoint(aggDataType = 'weather', timeColumnName = 'timestamp') def testUnaggregatedDataExists(self): """ :return: """ # @todo provide static test data for this test. myArgs = [('weather', 'agg_weather', 'timestamp', ''), ('egauge', 'agg_egauge', 'datetime', 'egauge_id'), ('circuit', 'agg_circuit', 'timestamp', 'circuit'), ('irradiance', 'agg_irradiance', 'timestamp', 'sensor_id')] self.logger.log(map( lambda x: self.aggregator.unaggregatedIntervalCount(dataType = x[0], aggDataType = x[ 1], timeColumnName = x[2], idColumnName = x[3]), myArgs)) def testAggregatedVsNewData(self): """ :return: """ # @todo provide static test data for this test. result = self.aggregator.aggregatedVsNewData() self.logger.log('result {}'.format(result), 'info') self.assertEqual(len(self.aggregator.dataParams.keys()), len(result.keys()), 'Result not obtained for each type.') def testAggregateNewData(self): """ @IMPORTANT Should not be run on live data. :return: """ # return map(self.aggregator.aggregateNewData, self.rawTypes) def testLastUnaggregatedAndAggregatedEndpoints(self): """ :return: """ # @todo Needs static test data. print self.aggregator.lastUnaggregatedAndAggregatedEndpoints( dataType = 'egauge') def test_endpoint_increment(self): myDT = datetime(2014, 02, 01, 23, 45) self.logger.log('dt = {}'.format(myDT)) result = self.aggregator.incrementEndpoint(endpoint = myDT) self.logger.log('result {}'.format(result)) self.assertEqual(result, datetime(2014, 02, 02, 00, 00, 00))
class MSGWeatherDataUtil(object): """ Utility methods for working with weather data. """ def __init__(self): """ Constructor. A database connection is not maintained here to keep this class lightweight. """ self.logger = SEKLogger(__name__, DEBUG) self.configer = MSGConfiger() self.url = self.configer.configOptionValue('Weather Data', 'weather_data_url') self.pattern = self.configer.configOptionValue('Weather Data', 'weather_data_pattern') self.fileList = [] self.dateList = [] # List of dates corresponding weather data files. self.fillFileListAndDateList() self.dbUtil = MSGDBUtil() def fillFileListAndDateList(self): """ Return a list of weather files obtained from the remote server used in processing weather data. """ response = urllib2.urlopen(self.url).read() self.logger.log('Filling file list:', DEBUG) for filename in re.findall(self.pattern, response): # Only examine first match group in the filename match. self.logger.log('filename {}'.format(filename[0]), DEBUG) self.fileList.append(filename[0]) self.dateList.append(self.datePart(filename[0])) def datePart(self, filename = None, datetime = None): """ Return the date part of a NOAA weather data filename. :param: String of the filename. :param: datetime object. :returns: String of the date part of the given parameter. """ assert filename == None or datetime == None, "One argument is allowed." if filename: newName = filename.replace("QCLCD", '') newName = newName.replace(".zip", '') return newName if datetime: return datetime.strftime('%Y-%m-%d') def getLastDateLoaded(self, cursor): """ Return the last date of loaded weather data. :returns: Last date. """ sql = """select wban, datetime, record_type from "%s" ORDER BY datetime desc limit 1""" % WEATHER_DATA_TABLE self.dbUtil.executeSQL(cursor, sql) row = cursor.fetchone() # self.logger.log('Date last loaded = %s' % row[1], 'info') return row[1] def getKeepList(self, fileList, cursor): """ The Keep List is the list of filenames of files containing data that are *within* the month of the last loaded date or are beyond the last loaded date. :param: fileList: A list of files containing weather data. :param: DB cursor. :returns: List of weather data filenames to process. """ keepList = [] i = 0 for date in fileList: self.logger.log('Examining date %s.' % date) # The list date should be the last day of the month. # It is the date that is compared against the last retrieved date. listDate = dt.datetime.strptime(self.datePart(filename = date), "%Y%m") lastDay = calendar.monthrange(listDate.year, listDate.month)[1] listDate = dt.datetime.strptime( '%s-%s-%s' % (listDate.year, listDate.month, lastDay), "%Y-%m-%d") self.logger.log('List date = %s.' % listDate) lastDate = self.getLastDateLoaded(cursor) self.logger.log('last date = %s' % lastDate) if lastDate <= listDate: keepList.append((i, listDate)) i += 1 if keepList: keepList.sort() return [fileList[d[0]] for d in keepList]
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() if __name__ == '__main__': processCommandLineArguments() logger = SEKLogger(__name__) inserter = SingleFileLoader(COMMAND_LINE_ARGS.filepath) if COMMAND_LINE_ARGS.skipNewDataCheck: logger.log('result = {}'.format(inserter.insertDataFromFile())) elif inserter.newDataForMeterExists(): logger.log('result = {}'.format(inserter.insertDataFromFile())) else: logger.log('no new data')
for line in csv.reader(csvFile, delimiter = ","): if lineCnt != 0: # Skip header. data = line[0:len(cols)] # Overshoot columns to get the last column. for i in range(0, len(cols)): if len(data[i]) == 0: data[i] = 'NULL' else: # Escape single quotes with double single quotes in # PostgreSQL. data[i] = data[i].replace("'", "\'\'") data[i] = "'" + data[i] + "'" sql = """INSERT INTO "MeterLocationHistory" (%s) VALUES (%s)""" % ( ','.join(cols), ','.join(data)) logger.log("SQL: %s" % sql, 'debug') success = dbUtil.executeSQL(cur, sql) if not success: anyFailure = True lineCnt += 1 conn.commit() msg = ("Processed %s lines.\n" % lineCnt) sys.stderr.write(msg) msgBody += msg if not anyFailure: msg = "Finished inserting Meter Location History records.\n" sys.stderr.write(msg)
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 MSGDBExporterTester(unittest.TestCase): """ Unit tests for the MSG Cloud Exporter. """ def setUp(self): self.logger = SEKLogger(__name__, 'DEBUG') self.configer = MSGConfiger() self.exporter = MSGDBExporter() self.testDir = 'db_exporter_test' self.uncompressedTestFilename = 'meco_v3_test_data.sql' self.compressedTestFilename = 'meco_v3_test_data.sql.gz' self.exportTestDataPath = self.configer.configOptionValue('Testing', 'export_test_data_path') self.fileUtil = MSGFileUtil() self.fileChunks = [] self.testDataFileID = '' self.pyUtil = MSGPythonUtil() self.timeUtil = MSGTimeUtil() conn = None try: conn = MSGDBConnector().connectDB() except Exception as detail: self.logger.log("Exception occurred: {}".format(detail), 'error') exit(-1) self.logger.log("conn = {}".format(conn), 'debug') self.assertIsNotNone(conn) # Create a temporary working directory. try: os.mkdir(self.testDir) except OSError as detail: self.logger.log( 'Exception during creation of temp directory: %s' % detail, 'ERROR') def tearDown(self): """ Delete all test items. """ REMOVE_TEMPORARY_FILES = True if REMOVE_TEMPORARY_FILES: try: self.logger.log("Removing local test files {}, {}.".format( self.uncompressedTestFilename, self.compressedTestFilename), 'debug') os.remove(os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) os.remove(os.path.join(os.getcwd(), self.testDir, self.compressedTestFilename)) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'SILENT') try: os.remove(os.path.join(os.getcwd(), self.testDir, self.compressedTestFilename)) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'SILENT') try: for f in self.fileChunks: os.remove(f) except OSError as detail: self.logger.log( 'Exception while removing temporary files: {}'.format( detail), 'DEBUG') try: os.rmdir(self.testDir) except OSError as detail: self.logger.log( 'Exception while removing directory: {}'.format(detail), 'ERROR') # Keep deleting from the cloud until there are no more to delete. def deleteFromCloud(): self.logger.log("deleting from cloud", 'debug') try: fileIDToDelete = self.exporter.fileIDForFileName( self.compressedTestFilename) if fileIDToDelete is None: return self.logger.log("file ID to delete: {}".format(fileIDToDelete), 'DEBUG') self.exporter.driveService.files().delete( fileId = '{}'.format(fileIDToDelete)).execute() deleteFromCloud() except (TypeError, http.HttpError) as e: self.logger.log('Delete not successful: {}'.format(e), 'DEBUG') deleteFromCloud() def _upload_test_data_to_cloud(self): """ Provide an upload of test data that can be used in other tests. Side effect: Store the file ID as an ivar. """ self.logger.log("Uploading test data for caller: {}".format( self.pyUtil.callerName())) filePath = "{}/{}".format(self.exportTestDataPath, self.compressedTestFilename) self.logger.log('Uploaded {}.'.format(filePath), 'info') uploadResult = self.exporter.uploadFileToCloudStorage(filePath) self.logger.log('upload result: {}'.format(uploadResult)) self.testDataFileID = self.exporter.fileIDForFileName( self.compressedTestFilename) self.logger.log("Test file ID is {}.".format(self.testDataFileID)) def test_markdown_list_of_downloadable_files(self): """ Match the Markdown line entry for the uploaded file. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals(len(filter(lambda x: self.testDataFileID in x, self.exporter.markdownListOfDownloadableFiles().splitlines())), 1) def test_get_md5_sum_from_cloud(self): """ Test retrieving the MD5 sum from the cloud. """ # @REVIEWED self.logger.log('Testing getting the MD5 sum.', 'info') self._upload_test_data_to_cloud() testFileMD5 = filter(lambda x: x['id'] == self.testDataFileID, self.exporter.cloudFiles['items'])[0][ 'md5Checksum'] self.assertEquals(len(testFileMD5), 32) self.assertTrue(re.match(r'[0-9A-Za-z]+', testFileMD5)) def test_get_file_id_for_nonexistent_file(self): """ Test getting a file ID for a nonexistent file. """ # @REVIEWED fileIDs = self.exporter.fileIDForFileName('nonexistent_file') self.logger.log("file ids = {}".format(fileIDs), 'info') self.assertIsNone(fileIDs) def test_upload_test_data(self): """ Upload a test data file for unit testing of DB export. The unit test data file is a predefined set of test data stored in the test data path of the software distribution. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertGreater(len(self.testDataFileID), 0) self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID)) def test_adding_reader_permissions(self): """ Add reader permissions to a file that was uploaded. """ # @REVIEWED self.logger.log("Testing adding reader permissions.") self._upload_test_data_to_cloud() email = self.configer.configOptionValue('Testing', 'tester_email_address') service = self.exporter.driveService try: id_resp = service.permissions().getIdForEmail( email = email).execute() print id_resp except errors.HttpError as detail: print 'Exception while getting ID for email: {}'.format(detail) new_permission = {'value': email, 'type': 'user', 'role': 'reader'} try: self.logger.log('Adding reader permission', 'INFO') fileIDToAddTo = self.testDataFileID # The permission dict is being output to stdout here. resp = service.permissions().insert(fileId = fileIDToAddTo, sendNotificationEmails = False, body = new_permission).execute() except errors.HttpError as detail: self.logger.log( 'Exception while adding reader permissions: {}'.format(detail), 'error') def permission_id(email): try: id_resp = service.permissions().getIdForEmail( email = email).execute() return id_resp['id'] except errors.HttpError as error: self.logger.log("HTTP error: {}".format(error)) permission = {} try: permission = service.permissions().get(fileId = self.testDataFileID, permissionId = permission_id( email)).execute() except errors.HttpError as error: self.logger.log("HTTP error: {}".format(error)) self.assertEquals(permission['role'], 'reader') def test_create_compressed_archived(self): """ * Copy test data to a temp directory (self.testDir). * Create a checksum for test data. * Create a gzip-compressed archive. * Extract gzip-compressed archive. * Create a checksum for the uncompressed data. * Compare the checksums. """ # @REVIEWED self.logger.log('Testing verification of a compressed archive.') self.logger.log('cwd {}'.format(os.getcwd())) fullPath = '{}'.format(os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) shutil.copyfile('{}/{}'.format(self.exportTestDataPath, self.uncompressedTestFilename), fullPath) md5sum1 = self.fileUtil.md5Checksum(fullPath) self.exporter.fileUtil.gzipCompressFile(fullPath) try: os.remove(os.path.join(os.getcwd(), self.testDir, self.uncompressedTestFilename)) except OSError as detail: self.logger.log('Exception while removing: {}'.format(detail), 'ERROR') # Extract archived data and generate checksum. src = gzip.open('{}{}'.format(fullPath, '.gz'), "rb") uncompressed = open(fullPath, "wb") decoded = src.read() uncompressed.write(decoded) uncompressed.close() md5sum2 = self.fileUtil.md5Checksum(fullPath) self.assertEqual(md5sum1, md5sum2, 'Checksums are not equal for original and new ' 'decompressed archive.') def test_export_db(self): """ Perform a quick test of the DB export method using Testing Mode. This requires sudo authorization to complete. """ # @REVIEWED self.logger.log('Testing exportDB using the testing DB.') # @todo handle case where testing db does not exist. dbs = ['test_meco'] ids = self.exporter.exportDBs(databases = dbs, toCloud = True, localExport = True) self.logger.log('Count of exports: {}'.format(len(ids))) self.assertEquals(len(ids), 1, "Count of exported files is wrong.") map(self.exporter.deleteFile, ids) def test_split_archive(self): """ Test splitting an archive into chunks. """ # @REVIEWED self.logger.log('Testing archive splitting.') fullPath = '{}/{}'.format(self.exportTestDataPath, self.compressedTestFilename) self.logger.log('fullpath: {}'.format(fullPath)) shutil.copyfile(fullPath, '{}/{}'.format(self.testDir, self.compressedTestFilename)) fullPath = '{}/{}'.format(self.testDir, self.compressedTestFilename) self.fileChunks = self.fileUtil.splitLargeFile(fullPath = fullPath, numChunks = 3) self.assertEquals(len(self.fileChunks), 3) def test_get_file_size(self): """ Test retrieving local file sizes. """ # @REVIEWED fullPath = '{}/{}'.format(self.exportTestDataPath, self.compressedTestFilename) fSize = self.fileUtil.fileSize(fullPath) self.logger.log('size: {}'.format(fSize)) self.assertEqual(fSize, 12279, 'File size is correct.') def test_upload_export_files_list(self): """ TBW """ pass def test_checksum_after_upload(self): """ TBW """ pass def test_dump_exclusions_dictionary(self): """ Verify the exclusions dictionary by its type. :return: """ # @REVIEWED exclusions = self.exporter.dumpExclusionsDictionary() if exclusions: self.assertEquals(type({}), type(exclusions)) def test_move_to_final(self): """ Test moving a file to the final destination path. """ # @REVIEWED self.logger.log('Testing moving to final path {}.'.format( self.configer.configOptionValue('Export', 'db_export_final_path'))) origCompressedFile = '{}/{}'.format( self.configer.configOptionValue('Export', 'export_test_data_path'), self.compressedTestFilename) newCompressedFile = '{}/{}'.format( self.configer.configOptionValue('Export', 'export_test_data_path'), 'temp_test_file') shutil.copyfile(origCompressedFile, newCompressedFile) self.exporter.moveToFinalPath(compressedFullPath = newCompressedFile) self.assertTrue(os.path.isfile('{}/{}'.format( self.configer.configOptionValue('Export', 'db_export_final_path'), 'temp_test_file'))) # Remove the test file. os.remove('{}/{}'.format( self.configer.configOptionValue('Export', 'db_export_final_path'), 'temp_test_file')) def test_log_successful_export(self): """ Test logging of export results to the export history table. """ # @REVIEWED self.assertTrue(self.exporter.logSuccessfulExport(name = 'test_export', url = 'http://test_url', datetime = 0, size = 100)) conn = MSGDBConnector().connectDB() cursor = conn.cursor() dbUtil = MSGDBUtil() self.assertTrue( dbUtil.executeSQL(cursor, 'select * from "ExportHistory" where ' 'timestamp = ' 'to_timestamp(0)')) self.assertEqual(len(cursor.fetchall()), 1, "There should only be one result row.") self.assertTrue( dbUtil.executeSQL(cursor, 'delete from "ExportHistory" where ' 'timestamp = to_timestamp(0)')) conn.commit() def test_metadata_of_file_id(self): """ Test getting the metadata for a file ID. """ # @REVIEWED self._upload_test_data_to_cloud() self.logger.log('metadata: {}'.format( self.exporter.metadataOfFileID(self.testDataFileID))) self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID)) def test_filename_for_file_id(self): """ Test returning a file name given a file ID. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals( self.exporter.filenameForFileID(fileID = self.testDataFileID), self.compressedTestFilename) def test_outdated_files(self): # @REVIEWED self._upload_test_data_to_cloud() time.sleep(1) self.logger.log("outdated:") # For debugging: for item in self.exporter.outdatedFiles( daysBeforeOutdated = datetime.timedelta( days = -1)): self.logger.log( "name: {}, created date: {}".format(item['originalFilename'], item['createdDate']), 'debug') # Get all the outdated files where outdated is equal to anything # uploaded today or later. self.assertTrue(self.exporter.outdatedFiles( daysBeforeOutdated = datetime.timedelta(days = -1))[0][ 'id'] == self.testDataFileID) self.logger.log('-----') def test_delete_outdated(self): """ TBW """ pass def test_list_of_downloadable_files(self): """ Test the list of downloadable files used by the available files page. """ # @REVIEWED self._upload_test_data_to_cloud() self.assertEquals(len( filter(lambda row: row['id'] == self.testDataFileID, self.exporter.listOfDownloadableFiles())), 1, "Test file not present.") def test_count_of_db_exports(self): count = self.exporter.countOfDBExports(EARLIEST_DATE) self.logger.log(count,'DEBUG') self.assertTrue(int(count) or int(count) == int(0)) def test_count_of_cloud_files(self): count = self.exporter.countOfCloudFiles() self.assertTrue(int(count) or int(count) == int(0)) def test_plaintext_list_of_downloadable_files(self): """ This test handles content both with content links and without content links. """ content = self.exporter.plaintextListOfDownloadableFiles() self.assertRegexpMatches(content, '\d+-\d+-\d+.*\,' '\s+\d+-\d+-\d+T\d+:\d+:\d+\.\d+Z\,\s+\d+\sB') def test_last_report_date(self): last_report = self.exporter.notifier.lastReportDate( types = MSGNotificationHistoryTypes, noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY) self.assertTrue(last_report is None or last_report > EARLIEST_DATE) def test_current_export_summary(self): self.assertRegexpMatches(self.exporter.currentExportSummary(), re.compile( 'last.*databases.*free.*currently.*accessed.*', flags = re.IGNORECASE | re.DOTALL))
class MSGDataAggregator(object): """ Use for continuous data aggregation of diverse data types relevant to the Maui Smart Grid project. Four data types are supported: 1. Irradiance 2. Temperature/Humidity (weather) 3. Circuit 4. eGauge The general data form conforms to 1. timestamp, subkey_id, val1, val2, val3, ... 2. timestamp, val1, val2, val3, ... Case (2) is handled within the same space as (1) by testing for the existence of subkeys. Current aggregation consists of averaging over **15-min intervals**. Aggregation is performed in-memory and saved to the DB. The time range is delimited by start date and end date where the values are included in the range. The timestamps for aggregation intervals are the last timestamp in a respective series. * Aggregation subkeys are values such as eGauge IDs or circuit numbers. Aggregation is being implemented externally for performance and flexibility advantages over alternative approaches such as creating a view. It may be rolled into an internal function at future time if that proves to be beneficial. Usage: from msg_data_aggregator import MSGDataAggregator aggregator = MSGDataAggregator() API: aggregateAllData(dataType = dataType) aggregateNewData(dataType = dataType) """ def __init__(self, exitOnError=True, commitOnEveryInsert=False, testing=False): """ Constructor. :param testing: if True, the testing DB will be connected instead of the production DB. """ self.logger = SEKLogger(__name__, 'info') self.configer = MSGConfiger() self.conn = MSGDBConnector().connectDB() self.cursor = self.conn.cursor() self.dbUtil = MSGDBUtil() self.notifier = MSGNotifier() self.mathUtil = MSGMathUtil() self.timeUtil = MSGTimeUtil() self.nextMinuteCrossing = {} self.nextMinuteCrossingWithoutSubkeys = None self.exitOnError = exitOnError self.commitOnEveryInsert = commitOnEveryInsert section = 'Aggregation' tableList = [ 'irradiance', 'agg_irradiance', 'weather', 'agg_weather', 'circuit', 'agg_circuit', 'egauge', 'agg_egauge' ] self.dataParams = { 'weather': ('agg_weather', 'timestamp', ''), 'egauge': ('agg_egauge', 'datetime', 'egauge_id'), 'circuit': ('agg_circuit', 'timestamp', 'circuit'), 'irradiance': ('agg_irradiance', 'timestamp', 'sensor_id') } self.columns = {} # tables[datatype] gives the table name for datatype. self.tables = { t: self.configer.configOptionValue(section, '{}_table'.format(t)) for t in tableList } for t in self.tables.keys(): self.logger.log('t:{}'.format(t), 'DEBUG') try: self.columns[t] = self.dbUtil.columnsString( self.cursor, self.tables[t]) except TypeError as error: self.logger.log( 'Ignoring missing table: Error is {}.'.format(error), 'error') def existingIntervals(self, aggDataType='', timeColumnName=''): """ Retrieve the existing aggregation intervals for the given data type. :param aggDataType: string :param timeColumnName: string :return: List of intervals. """ return [ x[0] for x in self.rows( """SELECT {0} from \"{1}\" ORDER BY {2}""".format( timeColumnName, self.tables[aggDataType], timeColumnName)) ] def unaggregatedIntervalCount(self, dataType='', aggDataType='', timeColumnName='', idColumnName=''): """ Return count of unaggregated intervals for a given data type. :param dataType: :param aggDataType: :param timeColumnName: :param idColumnName: :return: int """ return len( self.unaggregatedEndpoints(dataType, aggDataType, timeColumnName, idColumnName)) def lastAggregationEndpoint(self, aggDataType='', timeColumnName=''): """ Last aggregation endpoint for a given datatype. :param dataType: :param timeColumnName: :return: """ return self.existingIntervals(aggDataType=aggDataType, timeColumnName=timeColumnName)[-1] def unaggregatedEndpoints(self, dataType='', aggDataType='', timeColumnName='', idColumnName=''): """ Sorted (ascending) endpoints and their IDs, if available, for unaggregated intervals since the last aggregation endpoint for a given data type. This has a problem where an endpoint at 23:45:04 will be returned as 23:45:00. This makes the return value incorrect for raw data types having readings at sub-minute intervals such as data for circuit, irradiance and weather. This condition does not affect correct aggregation. Only the definition of the return value is wrong. :param dataType: string :param aggDataType: string :param timeColumnName: string :param idColName: string :return: list of datetimes. """ if idColumnName != '': # Key: # 0: raw # 1: agg # 2: time col # 3: id col # 4: last aggregated time sql = 'SELECT "{0}".{2}, "{0}".{3} FROM "{0}" LEFT JOIN "{1}" ON ' \ '"{0}".{2} = "{1}".{2} AND "{0}".{3} = "{1}".{3} WHERE "{' \ '1}".{2} IS NULL AND "{0}".{2} > \'{4}\' ORDER BY {2} ASC, ' \ '{3} ASC' self.logger.log('last agg endpoint: {}'.format( self.lastAggregationEndpoint(aggDataType, timeColumnName))) # The id column value is available in the tuple returned by # groupby but is not being used here. # @todo Exclude last endpoint if it is equal to the last # aggregation endpoint. # # The minute position filtering may be including the last # endpoint incorrectly because there are readings occurring # within the same minute as the final endpoint, e.g. 23:45:04, # 23:45:08, etc. # # This is not a problem with eGuage data due reading intervals # being every minute and zero seconds. return map(lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [ k for k, v in groupby( map( lambda y: y[0].timetuple()[0:5], filter( lambda x: x[0].timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0, [( x[0], x[1]) for x in self.rows( sql.format( self.tables[dataType], self.tables[aggDataType], timeColumnName, idColumnName, self.lastAggregationEndpoint( aggDataType, timeColumnName)))]))) ]) else: # Key: # 0: raw # 1: agg # 2: time col # 3: last aggregated time sql = 'SELECT "{0}".{2} FROM "{0}" LEFT JOIN "{1}" ON "{0}".{2}=' \ '"{1}".{2} WHERE "{1}".{2} IS NULL AND "{0}".{2} > \'{3}\' ' \ 'ORDER BY {2} ASC' self.logger.log('last agg endpoint: {}'.format( self.lastAggregationEndpoint(aggDataType, timeColumnName))) return map(lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [ k for k, v in groupby( map( lambda y: y.timetuple()[0:5], filter( lambda x: x.timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0, [(x[0]) for x in self.rows( sql.format( self.tables[dataType], self.tables[aggDataType], timeColumnName, self.lastAggregationEndpoint( aggDataType, timeColumnName)))]))) ]) def intervalCrossed(self, minute=None, subkey=None): """ Determine interval crossing. Intervals are at 0, 15, 45, 60 min. The interval size is determined by MECO source data. :param minute: The integer value of the minute. :param subkey: The name for the subkey used for aggregation. :returns: True if an interval was crossed, False otherwise. """ if not minute and minute != 0: raise Exception('Minute not defined.') intervalSize = 15 first = 0 last = 60 if subkey is not None: if minute >= self.nextMinuteCrossing[subkey] and minute <= last \ and \ self.nextMinuteCrossing[subkey] != first: self.nextMinuteCrossing[subkey] += intervalSize if self.nextMinuteCrossing[subkey] >= last: self.nextMinuteCrossing[subkey] = first self.logger.log('minute crossed at #1.', 'debug') return True elif self.nextMinuteCrossing[ subkey] == first and minute >= first and minute <= intervalSize: self.nextMinuteCrossing[subkey] = intervalSize self.logger.log('minute crossed at #2.', 'debug') return True return False else: if minute >= self.nextMinuteCrossingWithoutSubkeys and minute <= \ last and self.nextMinuteCrossingWithoutSubkeys != first: self.nextMinuteCrossingWithoutSubkeys += intervalSize if self.nextMinuteCrossingWithoutSubkeys >= last: self.nextMinuteCrossingWithoutSubkeys = first self.logger.log('minute crossed at #3.', 'debug') return True elif self.nextMinuteCrossingWithoutSubkeys == first and minute >=\ first and minute <= intervalSize: self.nextMinuteCrossingWithoutSubkeys = intervalSize self.logger.log('minute crossed at #4.', 'debug') return True return False def rows(self, sql): """ Rows from a SQL fetch. :param sql: Command to be executed. :returns: DB result set. """ self.logger.log('sql: {}'.format(sql), 'debug') self.dbUtil.executeSQL(self.cursor, sql) return self.cursor.fetchall() def rawData(self, dataType='', orderBy=None, timestampCol='', startDate='', endDate=''): """ Raw data to be aggregated. :param dataType: string :param orderBy: list :param timestampCol: string :param startDate: string :param endDate: string :returns: DB rows. """ # @todo Validate args. orderBy = filter(None, orderBy) return self.rows("""SELECT {} FROM "{}" WHERE {} BETWEEN '{}' AND '{}' ORDER BY {}""".format(self.columns[dataType], self.tables[dataType], timestampCol, startDate, endDate, ','.join(orderBy))) def subkeys(self, dataType='', timestampCol='', subkeyCol='', startDate='', endDate=''): """ The distinct subkeys for a given data type within a time range. Subkeys are fields such as egauge_id in eGauge data or sensor_id in irradiance data. :param dataType: string :param timestampCol: string :param subkeyCol: string :param startDate: string :param endDate: string :returns: List of subkeys """ return [ sk[0] for sk in self.rows("""SELECT DISTINCT({}) FROM "{}" WHERE {} BETWEEN '{}' AND '{}' ORDER BY {}""".format(subkeyCol, self.tables[dataType], timestampCol, startDate, endDate, subkeyCol)) ] def insertAggregatedData(self, agg=None): """ :param agg: MSGAggregatedData :return: None """ if not agg.columns: raise Exception('agg columns not defined.') if not agg.data: raise Exception('agg data not defined.') self.logger.log('agg data: {}'.format(agg.data)) self.logger.log('agg data type: {}'.format(type(agg.data))) def __insertData(values=''): """ Perform insert of data to the database using the given values. :param values: String containing values to be inserted. :return Nothing. """ sql = 'INSERT INTO "{0}" ({1}) VALUES( {2})'.format( self.tables[agg.aggregationType], ','.join(agg.columns), values) self.logger.log('sql: {}'.format(sql), 'debug') success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=self.exitOnError) # Used for a special case where data is reloaded. if self.commitOnEveryInsert: self.conn.commit() if not success and self.exitOnError: raise Exception('Failure during aggregated data insert.') for row in agg.data: if type(row) == type({}): # self.logger.log('row=%s' % row, 'debug') # self.logger.log('row type: %s' % type(row)) for key in row.keys(): values = '' valCnt = 0 for val in row[key]: if val == 'NULL': values += val elif type(val) == type(''): values += "'" + val.strip() + "'" elif isinstance(val, datetime): values += "'" + val.isoformat() + "'" elif type(val) == type(0): values += str(val) elif type(val) == type(0.0): values += str(val) else: values += val if valCnt < len(agg.columns) - 1: values += "," valCnt += 1 __insertData(values=values) elif type(row) == type([]): values = '' valCnt = 0 for val in row: if val == 'NULL': values += val elif type(val) == type(''): values += "'" + val.strip() + "'" elif isinstance(val, datetime): values += "'" + val.isoformat() + "'" elif type(val) == type(0): values += str(val) elif type(val) == type(0.0): values += str(val) else: values += val if valCnt < len(agg.columns) - 1: values += "," valCnt += 1 __insertData(values=values) else: self.logger.log('row = {}'.format(row), 'error') raise Exception('Row type not matched.') # End for row. self.conn.commit() def intervalAverages(self, sums, cnts, timestamp, timestampIndex, subkeyIndex=None, subkey=None): """ Aggregates all data for the current interval for the given subkey. For the case where there are no subkeys, subkeyIndex and subkey should be None. :param sums: list :param cnts: list :param timestamp: datetime :param timestampIndex: int :param subkeyIndex: int :param subkey: string :returns: Averaged data as a dict with form {subkey:data} """ if subkey is not None: myAvgs = {} reportedAgg = False myAvgs[subkey] = [] sumIndex = 0 self.logger.log('key: {}'.format(subkey), 'debug') # Iterate over sums. for s in sums[subkey]: if sumIndex == timestampIndex: myAvgs[subkey].append(timestamp) elif sumIndex == subkeyIndex: myAvgs[subkey].append(subkey) else: if cnts[subkey][sumIndex] != 0: if not reportedAgg: self.logger.log( 'Aggregating {} rows of data.'.format( cnts[subkey][sumIndex]), 'debug') reportedAgg = True myAvgs[subkey].append(s / cnts[subkey][sumIndex]) else: myAvgs[subkey].append('NULL') sumIndex += 1 return myAvgs else: myAvgs = [] reportedAgg = False sumIndex = 0 for s in sums: if sumIndex == timestampIndex: myAvgs.append(timestamp) else: if cnts[sumIndex] != 0: if not reportedAgg: self.logger.log( 'Aggregating {} rows of data.'.format( cnts[sumIndex]), 'debug') reportedAgg = True myAvgs.append(s / cnts[sumIndex]) else: myAvgs.append('NULL') sumIndex += 1 return myAvgs def dataParameters(self, dataType=''): """ Parameters for a given data type. :param dataType: string :return: (aggType, timeColName, subkeyColName) """ try: assert len(self.dataParams[dataType]) == 3 return self.dataParams[dataType] except: self.logger.log('Unmatched data type {}.'.format(dataType)) def aggregateAllData(self, dataType=''): """ Convenience method for aggregating all data for a given data type. Data is inserted to individual aggregated data tables. :param dataType: String in the list of raw data types. :return: Nothing. """ (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) for start, end in self.monthStartsAndEnds(timeColumnName=timeColName, dataType=dataType): self.logger.log('start, end: {}, {}'.format(start, end)) aggData = self.aggregatedData( dataType=dataType, aggregationType=aggType, timeColumnName=timeColName, subkeyColumnName=subkeyColName, startDate=start.strftime('%Y-%m-%d %H:%M:%S'), endDate=end.strftime('%Y-%m-%d %H:%M:%S')) self.insertAggregatedData(agg=aggData) for row in aggData.data: self.logger.log('aggData row: {}'.format(row)) def aggregateNewData(self, dataType=''): """ Convenience method for aggregating new data. :param dataType: :return: dict of {dataType: count of aggregation endpoints} """ # The new aggregation starting point is equal to the last aggregation # endpoint up to the last unaggregated endpoint. (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) (end, start) = \ self.lastUnaggregatedAndAggregatedEndpoints(dataType).items()[0][1] self.logger.log( 'datatype: {}; start, end: {}, {}; end type: {}'.format( dataType, start, end, type(end)), 'critical') if type(end) == type(None): # No available unaggregated endpoints results in an empty list # for type egauge. The reason this does not work for other types is # because the other types of fractional minute readings and the # fractional minute readings are not being handled completely but # this method is still capable of working without problem. self.logger.log('Nothing to aggregate.') return {dataType: 0} if self.incrementEndpoint(start) >= end: self.logger.log('Nothing to aggregate.') return {dataType: 0} aggData = self.aggregatedData( dataType=dataType, aggregationType=aggType, timeColumnName=timeColName, subkeyColumnName=subkeyColName, startDate=self.incrementEndpoint(start).strftime( '%Y-%m-%d %H:%M:%S'), endDate=end.strftime('%Y-%m-%d %H:%M:%S')) self.insertAggregatedData(agg=aggData) for row in aggData.data: self.logger.log('aggData row: {}'.format(row)) self.logger.log('{} rows aggregated for {}.'.format( len(aggData.data), dataType)) return {dataType: len(aggData.data)} def incrementEndpoint(self, endpoint=None): """ Increment an endpoint by one interval where endpoints are the final timestamp in an aggregation interval. :param endpoint: the endpoint to be incremented. :return: datetime object that is the given endpoint + a predefined amount of minutes. """ plusOneInterval = relativedelta(minutes=15) return endpoint + plusOneInterval def lastUnaggregatedAndAggregatedEndpoints(self, dataType=''): """ Return the endpoints for the given data type in the form {datatype: (last unaggregated endpoint, last aggregated endpoint)}. :param dataType: :return: dict with tuple. """ self.logger.log('datatype {}'.format(dataType)) (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) self.logger.log('subkey colname {}'.format(subkeyColName)) unAggregatedEndpoints = self.unaggregatedEndpoints( dataType=dataType, aggDataType=aggType, timeColumnName=timeColName, idColumnName=subkeyColName) self.logger.log('unagg endpoints: {}'.format(unAggregatedEndpoints)) return { dataType: (unAggregatedEndpoints[-1] if unAggregatedEndpoints != [] else None, self.lastAggregationEndpoint(aggDataType=aggType, timeColumnName=timeColName)) } def aggregatedVsNewData(self): """ Convenience method. :return: dict of tuples containing {datatype:(last raw datetime, last agg datetime)} """ return { x.keys()[0]: (x.values()[0]) for x in map(self.lastUnaggregatedAndAggregatedEndpoints, [k for k in self.dataParams]) } def monthStartsAndEnds(self, timeColumnName='', dataType=''): """ Return first date and last date for the given **raw** data type for each month in the data's entire time range. The end date is incremented by on aggregation period to account for the data obtained at time 00:00. :param timeColumnName: string :param dataType: string :return: List of tuples. """ self.logger.log('datatype {}'.format(dataType), 'debug') (start, end) = self.rows("""SELECT MIN({}), MAX({}) FROM \"{}\"""".format( timeColumnName, timeColumnName, self.tables[dataType]))[0] self.logger.log('start {}'.format(start)) self.logger.log('end {}'.format(end)) # End time needs transforming in split dates to extend the end of the # day to 23:59:59. splitDates = self.timeUtil.splitDates(start, end) startEndDatesTransform = [] i = 0 while i < len(splitDates): startEndDatesTransform.append( (splitDates[i][0], self.incrementEndpoint( datetime(splitDates[i][1].timetuple()[0], splitDates[i][1].timetuple()[1], splitDates[i][1].timetuple()[2], 23, 59, 59)))) i += 1 return startEndDatesTransform def aggregatedData(self, dataType='', aggregationType='', timeColumnName='', subkeyColumnName='', startDate='', endDate=''): """ *********************************************************************** Provide aggregated data. *********************************************************************** Start and end dates are used to calculate interval crossings. :param dataType: String :param aggregationType: String :param timeColumnName: String :param subkeyColumnName: String :param startDate: String :param endDate: String :returns: MSGAggregatedData """ aggData = [] ci = lambda col_name: self.columns[dataType].split(',').index(col_name) rowCnt = 0 mySubkeys = [] if subkeyColumnName: mySubkeys = self.subkeys(dataType=dataType, timestampCol=timeColumnName, subkeyCol=subkeyColumnName, startDate=startDate, endDate=endDate) self.logger.log('subkeys: {}'.format(mySubkeys), 'debug') def __initSumAndCount(subkey=None, sums=None, cnts=None): """ Initialize the sum and cnt data structures. :param subkey: string :param sums: list | dict | None :param cnts: list | dict | None """ if not sums and not cnts: sums = {} cnts = {} if not mySubkeys: sums = [] cnts = [] for i in range(len(self.columns[dataType].split(','))): sums.append(0) cnts.append(0) else: if not subkey: for i in range(len(self.columns[dataType].split(','))): for k in mySubkeys: if k not in sums.keys(): sums[k] = [] cnts[k] = [] sums[k].append(0) cnts[k].append(0) else: sums[subkey] = [] for i in range(len(self.columns[dataType].split(','))): sums[subkey].append(0) cnts[subkey] = [] for i in range(len(self.columns[dataType].split(','))): cnts[subkey].append(0) return (sums, cnts) (sum, cnt) = __initSumAndCount() def __initIntervalCrossings(): """ Perform initialization of the interval crossings used to determine when interval crossings occur. :returns None """ subkeysToCheck = copy.copy(mySubkeys) self.logger.log('subkeys to check: {}'.format(subkeysToCheck), 'debug') if mySubkeys: for row in self.rawData( dataType=dataType, orderBy=[timeColumnName, subkeyColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate): # @CRITICAL: Exit after every subkey has been visited. # This scans the raw data until each subkey is encountered # ONCE and then exits. if subkeysToCheck != []: if row[ci(subkeyColumnName)] in subkeysToCheck: subkeysToCheck.remove(row[ci(subkeyColumnName)]) minute = row[ci( timeColumnName)].timetuple()[MINUTE_POSITION] if minute <= 15: self.nextMinuteCrossing[row[ci( subkeyColumnName)]] = 15 elif minute <= 30: self.nextMinuteCrossing[row[ci( subkeyColumnName)]] = 30 elif minute <= 45: self.nextMinuteCrossing[row[ci( subkeyColumnName)]] = 45 elif minute == 0 or minute <= 59: self.nextMinuteCrossing[row[ci( subkeyColumnName)]] = 0 else: raise Exception( 'Unable to determine next minute crossing') self.logger.log( 'next min crossing for {} = {}'.format( row[ci(subkeyColumnName)], self.nextMinuteCrossing[row[ci( subkeyColumnName)]]), 'debug') else: break else: # Non-subkey case e.g. weather data. rowCnt = 0 # @todo Optimize by querying only the first row. for row in self.rawData(dataType=dataType, orderBy=[timeColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate): minute = row[ci( timeColumnName)].timetuple()[MINUTE_POSITION] if minute <= 15: self.nextMinuteCrossingWithoutSubkeys = 15 elif minute <= 30: self.nextMinuteCrossingWithoutSubkeys = 30 elif minute <= 45: self.nextMinuteCrossingWithoutSubkeys = 45 elif minute == 0 or minute <= 59: self.nextMinuteCrossingWithoutSubkeys = 0 else: raise Exception( 'Unable to determine next minute crossing') self.logger.log( 'next min crossing = {}'.format( self.nextMinuteCrossingWithoutSubkeys), 'debug') rowCnt += 1 if rowCnt > 0: break __initIntervalCrossings() for row in self.rawData(dataType=dataType, orderBy=[timeColumnName, subkeyColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate): if mySubkeys: for col in self.columns[dataType].split(','): if self.mathUtil.isNumber( row[ci(col)]) and ci(col) != ci(subkeyColumnName): sum[row[ci(subkeyColumnName)]][ci(col)] += row[ci(col)] cnt[row[ci(subkeyColumnName)]][ci(col)] += 1 minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if self.intervalCrossed(minute=minute, subkey=row[ci(subkeyColumnName)]): minuteCrossed = minute # Perform aggregation on all of the previous data including # the current data for the current subkey. self.logger.log( 'key: {}'.format(row[ci(subkeyColumnName)]), 'debug') aggData += [ self.intervalAverages(sum, cnt, row[ci(timeColumnName)], ci(timeColumnName), ci(subkeyColumnName), row[ci(subkeyColumnName)]) ] self.logger.log('minute crossed {}'.format(minuteCrossed), 'DEBUG') # Init current sum and cnt for subkey that has a completed # interval. (sum, cnt) = __initSumAndCount(subkey=row[ci(subkeyColumnName)], sums=sum, cnts=cnt) else: for col in self.columns[dataType].split(','): if self.mathUtil.isNumber(row[ci(col)]): sum[ci(col)] += row[ci(col)] cnt[ci(col)] += 1 minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if self.intervalCrossed(minute=minute): aggData += [ self.intervalAverages(sum, cnt, row[ci(timeColumnName)], ci(timeColumnName)) ] (sum, cnt) = __initSumAndCount(subkey=None, sums=sum, cnts=cnt) rowCnt += 1 self.logger.log('aggdata = {}'.format(aggData), 'debug') return MSGAggregatedData(aggregationType=aggregationType, columns=self.columns[dataType].split(','), data=aggData)
class NewDataAggregator(object): """ Perform aggregation of new data for a set of predefined data types (self .rawTypes). """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'DEBUG') self.aggregator = MSGDataAggregator() self.notifier = MSGNotifier() self.rawTypes = [x.name for x in list(MSGAggregationTypes)] self.connector = MSGDBConnector() self.conn = self.connector.connectDB() self.cursor = self.conn.cursor() self.dbUtil = MSGDBUtil() def sendNewDataNotification(self, result = None, testing = False): """ Sending notification reporting on new data being available since the last time new data was reported. :param result: list of dicts containing aggregation results as provided by MSGDataAggregator::aggregateNewData. :param testing: Use testing mode when True. """ self.logger.log('result {}'.format(result), 'debug') lastReportDate = self.notifier.lastReportDate( MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR) if not lastReportDate: lastReportDate = "never" if not result: msgBody = '\nNew data has NOT been aggregated in {}. No result ' \ 'was obtained. This is an error that should be ' \ 'investigated.'.format(self.connector.dbName) else: msgBody = '\nNew data has been aggregated in {}.'.format( self.connector.dbName) msgBody += '\n\n' for i in range(len(result)): msgBody += 'The new data count for type {} is {} readings' \ '.\n'.format(result[i].keys()[0], result[i].values()[0]) msgBody += '\n\n' msgBody += 'The last report date was %s.' % lastReportDate msgBody += '\n\n' self.notifier.sendNotificationEmail(msgBody, testing = testing) self.notifier.recordNotificationEvent( MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR) def aggregateNewData(self): """ :return: list of dicts obtained from MSGDataAggregator::aggregateNewData. """ result = map(self.aggregator.aggregateNewData, self.rawTypes) self.logger.log('result {}'.format(result)) return result
patterns = ['*.xlsx'] matchCnt = 0 for root, dirs, filenames in os.walk('.'): for pat in patterns: for filename in fnmatch.filter(filenames, pat): paths.append(os.path.join(root, filename)) matchCnt += 1 table = 'PowerMeterEvents' cnt = 0 workbookCount = 0 for path in paths: workbookCount += 1 logger.log('workbook: %s' % path) wb = xlrd.open_workbook(path) wb.sheet_names() print wb.sheet_names() sh = wb.sheet_by_index(0) print sh numRows = sh.nrows - 1 currentRow = -1 while currentRow < numRows: currentRow += 1 row = sh.row(currentRow) # Row is a dict of the col vals.
return "\n%s\n" % results return '\nNo previous retrieval results are available.\n' def fileExists(fullPath): try: with open(fullPath): return True except IOError, e: return False processCommandLineArguments() if COMMAND_LINE_ARGS.testing: logger.log("Testing mode is ON.\n", 'info') connector = MSGDBConnector(True) else: connector = MSGDBConnector(testing=COMMAND_LINE_ARGS.testing) if COMMAND_LINE_ARGS.email: logger.log("Email will be sent.\n", 'info') conn = connector.conn databaseName = '' if COMMAND_LINE_ARGS.testing: databaseName = configer.configOptionValue("Database", "testing_db_name") else: databaseName = configer.configOptionValue("Database", "db_name")
class MECOXMLParser(object): """ Parses XML for MECO data. """ tableName = "" def __init__(self, testing=False): """ Constructor. :param testing: (optional) Boolean indicating if Testing Mode is on. """ self.logger = SEKLogger(__name__, "silent") if testing: self.logger.log("Testing Mode is ON.", "info") self.debug = False self.configer = MSGConfiger() if self.configer.configOptionValue("Debugging", "debug") == True: self.debug = True self.util = MSGDBUtil() self.mapper = MECOMapper() self.connector = MSGDBConnector(testing) self.conn = self.connector.connectDB() self.filename = None self.fileObject = None self.processForInsertElementCount = 0 self.inserter = MECODBInserter() self.insertDataIntoDatabase = False # Count number of times sections in source data are encountered. self.tableNameCount = { "SSNExportDocument": 0, "MeterData": 0, "RegisterData": 0, "RegisterRead": 0, "Tier": 0, "Register": 0, "IntervalReadData": 0, "Interval": 0, "Reading": 0, "IntervalStatus": 0, "ChannelStatus": 0, "EventData": 0, "Event": 0, } # Use this dictionary to track which channels were processed when # readings are being processed. this is to prevent duplicate channel # data from being inserted. self.channelProcessed = {} self.initChannelProcessed() # Tables to be inserted to. self.insertTables = self.configer.insertTables self.lastSeqVal = None self.fKeyVal = None self.lastTable = None self.fkDeterminer = MECOFKDeterminer() self.dupeChecker = MECODupeChecker() self.currentMeterName = None self.currentIntervalEndTime = None self.currentRegisterReadReadTime = None self.dupesExist = False self.channelDupeExists = False # For Reading dupes. self.numberDupeExists = False # For Register dupes. self.eventTimeDupeExists = False # For Event dupes. self.commitCount = 0 self.readingDupeOnInsertCount = 0 # For Reading dupes. self.registerDupeOnInsertCount = 0 # For Register dupes. self.eventDupeOnInsertCount = 0 # For Event dupes. self.dataProcessCount = 0 self.readingDupeCheckCount = 0 # For Reading dupes. self.registerDupeCheckCount = 0 # For Register dupes. self.eventDupeCheckCount = 0 # For Event dupes. self.insertCount = 0 self.cumulativeInsertCount = 0 self.nonProcessForInsertElementCount = 0 self.readingInsertCount = 0 self.registerInsertCount = 0 self.eventInsertCount = 0 self.totalReadingInsertCount = 0 self.totalRegisterInsertCount = 0 self.totalEventInsertCount = 0 self.totalReadingDupeOnInsertCount = 0 self.totalRegisterDupeOnInsertCount = 0 self.totalEventDupeOnInsertCount = 0 def parseXML(self, fileObject, insert=False, jobID=""): """ Parse an XML file. :param fileObject: a file object referencing an XML file. :param insert: (optional) True to insert to the database | False to perform no inserts. :returns: String containing a concise log of parsing. """ print "parseXML:" self.commitCount = 0 self.insertDataIntoDatabase = insert parseMsg = "\nParsing XML in %s.\n" % self.filename sys.stderr.write(parseMsg) parseLog = parseMsg tree = ET.parse(fileObject) root = tree.getroot() parseLog += self.walkTheTreeFromRoot(root, jobID=jobID) return parseLog def tableNameForAnElement(self, element): """ Get the tablename for an element. :param element: Element tree element. :returns: table name """ try: name = re.search("\{.*\}(.*)", element.tag).group(1) except: name = None return name def processDataToBeInserted(self, columnsAndValues, currentTableName, fKeyValue, parseLog, pkeyCol, jobID=""): """ This is the method that performs insertion of parsed data to the database. Duplicate checks are performed on the endpoints of the data branches. :param columnsAndValues: A dictionary containing columns and their values. :param currentTableName: The name of the current table. :param fKeyValue: The value of the foreign key. :param parseLog: String containing a concise log of operations. :param pkeyCol: Column name for the primary key. :param jobID: Identifier for multiprocessing process. :returns: A string containing the parse log. """ self.dataProcessCount += 1 # Handle a special case for duplicate reading data. # Intercept the duplicate reading data before insert. if currentTableName == "Reading": self.channelDupeExists = self.dupeChecker.readingBranchDupeExists( self.conn, self.currentMeterName, self.currentIntervalEndTime, columnsAndValues["Channel"] ) self.readingDupeCheckCount += 1 if currentTableName == "Register": self.numberDupeExists = self.dupeChecker.registerBranchDupeExists( self.conn, self.currentMeterName, self.currentRegisterReadReadTime, columnsAndValues["Number"] ) self.registerDupeCheckCount += 1 if currentTableName == "Event": self.eventTimeDupeExists = self.dupeChecker.eventBranchDupeExists( self.conn, self.currentMeterName, columnsAndValues["EventTime"] ) self.eventDupeCheckCount += 1 # Only perform an insert if there are no duplicate values # for the channel. if not self.channelDupeExists and not self.numberDupeExists and not self.eventTimeDupeExists: # *********************** # ***** INSERT DATA ***** # *********************** cur = self.inserter.insertData( self.conn, currentTableName, columnsAndValues, fKeyVal=fKeyValue, withoutCommit=1 ) # The last 1 indicates don't commit. Commits are handled externally. self.insertCount += 1 self.cumulativeInsertCount += 1 # Only attempt getting the last sequence value if an insertion # took place. self.lastSeqVal = self.util.getLastSequenceID(self.conn, currentTableName, pkeyCol) # Store the primary key. self.fkDeterminer.pkValforCol[pkeyCol] = self.lastSeqVal if currentTableName == "Reading": self.readingInsertCount += 1 self.totalReadingInsertCount += 1 elif currentTableName == "Register": self.registerInsertCount += 1 self.totalRegisterInsertCount += 1 elif currentTableName == "Event": self.eventInsertCount += 1 self.totalEventInsertCount += 1 else: # Don't insert into Reading or Register table if a dupe exists. if self.channelDupeExists: self.readingDupeOnInsertCount += 1 self.totalReadingDupeOnInsertCount += 1 if self.readingDupeOnInsertCount > 0 and self.readingDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite("%s:{rd-dupe==>}" % jobID) # Also, verify the data is equivalent to the existing record. matchingValues = self.dupeChecker.readingValuesAreInTheDatabase(self.conn, columnsAndValues) assert matchingValues == True, ( "Duplicate check found " "non-matching values for meter" " %s," " endtime %s, channel %s (%s, " "%s)." % ( self.currentMeterName, self.currentIntervalEndTime, columnsAndValues["Channel"], columnsAndValues["RawValue"], columnsAndValues["Value"], ) ) self.channelDupeExists = False elif self.numberDupeExists: self.registerDupeOnInsertCount += 1 self.totalRegisterDupeOnInsertCount += 1 if self.registerDupeOnInsertCount > 0 and self.registerDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite("%s:{re-dupe==>}" % jobID) self.numberDupeExists = False elif self.eventTimeDupeExists: self.eventDupeOnInsertCount += 1 self.totalEventDupeOnInsertCount += 1 if self.eventDupeOnInsertCount > 0 and self.eventDupeOnInsertCount < 2: parseLog += self.logger.logAndWrite("%s:{ev-dupe==>}" % jobID) self.eventTimeDupeExists = False else: assert True == False, "Duplicate condition does not exist." self.logger.log("Record not inserted for %s." % columnsAndValues, "silent") return parseLog def generateConciseLogEntries(self, jobID="", reportType=None): """ Create log entries in the concise log. :param jobID: Identifier used to distinguish multiprocessing jobs. :returns: A concatenated string of log entries. """ # @todo Change report type to enum type. log = "" if reportType == "FINAL": self.logger.log("Final report", "info") if ( self.readingDupeOnInsertCount > 0 or self.registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount > 0 ): log = self.logger.logAndWrite( "%s:{%srd,%sre,%sev}" % ( jobID, self.totalReadingDupeOnInsertCount, self.totalRegisterDupeOnInsertCount, self.totalEventDupeOnInsertCount, ) ) else: log = "" log += self.logger.logAndWrite("(%s)" % self.commitCount) log += self.logger.logAndWrite("[%s]" % self.processForInsertElementCount) log += self.logger.logAndWrite( "<%srd,%sre,%sev,%s>" % ( self.totalReadingInsertCount, self.totalRegisterInsertCount, self.totalEventInsertCount, self.cumulativeInsertCount, ) ) elif reportType == "INTERMEDIARY": if ( self.readingDupeOnInsertCount > 0 or self.registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount > 0 ): log = self.logger.logAndWrite( "%s:{%srd,%sre,%sev}" % ( jobID, self.readingDupeOnInsertCount, self.registerDupeOnInsertCount, self.eventDupeOnInsertCount, ) ) else: log = "" log += self.logger.logAndWrite("(%s)" % self.commitCount) log += self.logger.logAndWrite("[%s]" % self.processForInsertElementCount) log += self.logger.logAndWrite( "<%srd,%sre,%sev,%s,%s>" % ( self.readingInsertCount, self.registerInsertCount, self.eventInsertCount, self.insertCount, self.cumulativeInsertCount, ) ) return log def resetGroupCounters(self): """ Reset counters that are keeping track of groups. """ self.readingDupeOnInsertCount = 0 self.insertCount = 0 self.readingInsertCount = 0 self.registerDupeOnInsertCount = 0 self.registerInsertCount = 0 self.eventInsertCount = 0 self.eventDupeOnInsertCount = 0 def performTableBasedOperations(self, columnsAndValues, currentTableName, element): """ Perform operations that are based on the current table. :param columnsAndValues :param currentTableName :param element """ if currentTableName == "MeterData": self.currentMeterName = columnsAndValues["MeterName"] elif currentTableName == "Interval": self.currentIntervalEndTime = columnsAndValues["EndTime"] elif currentTableName == "RegisterRead": self.currentRegisterReadReadTime = columnsAndValues["ReadTime"] elif currentTableName == "Event": columnsAndValues["Event_Content"] = element.text def walkTheTreeFromRoot(self, root, jobID=""): """ Walk an XML tree from its root node. :param root: The root node of an XML tree. :param jobID: Identifier used to distinguish multiprocessing jobs. :returns: String containing a concise log of parsing activity. """ parseLog = "" walker = root.iter() for element, nextElement in self.getNext(walker): # Process every element in the tree while reading ahead to get # the next element. currentTableName = self.tableNameForAnElement(element) nextTableName = self.tableNameForAnElement(nextElement) assert currentTableName is not None, "Current table does not exist." # Maintain a count of tables encountered. self.tableNameCount[currentTableName] += 1 columnsAndValues = {} it = iter(sorted(element.attrib.iteritems())) for item in list(it): # Create a dictionary of column names and values. columnsAndValues[item[0]] = item[1] if currentTableName in self.insertTables: # Check if the current table is one of the tables to have data # inserted. self.processForInsertElementCount += 1 if self.debug: self.logger.log("Processing table %s, next is %s." % (currentTableName, nextTableName), "debug") # Get the column name for the primary key. pkeyCol = self.mapper.dbColumnsForTable(currentTableName)["_pkey"] fkeyCol = None fKeyValue = None try: # Get the column name for the foreign key. fkeyCol = self.mapper.dbColumnsForTable(currentTableName)["_fkey"] except: pass if self.debug: self.logger.log("foreign key col (fkey) = %s" % fkeyCol, "debug") self.logger.log("primary key col (pkey) = %s" % pkeyCol, "debug") self.logger.log(columnsAndValues, "debug") if fkeyCol is not None: # Get the foreign key value. fKeyValue = self.fkDeterminer.pkValforCol[fkeyCol] if self.debug: self.logger.log("fKeyValue = %s" % fKeyValue, "debug") self.performTableBasedOperations(columnsAndValues, currentTableName, element) if self.insertDataIntoDatabase: # Data is intended to be inserted into the database. parseLog = self.processDataToBeInserted( columnsAndValues, currentTableName, fKeyValue, parseLog, pkeyCol, jobID=jobID ) if self.debug: self.logger.log("lastSeqVal = ", self.lastSeqVal) if self.lastReading(currentTableName, nextTableName): # The last reading set has been reached. if self.debug: self.logger.log("----- last reading found -----", "debug") parseLog += self.generateConciseLogEntries(jobID=jobID, reportType="INTERMEDIARY") self.resetGroupCounters() parseLog += self.logger.logAndWrite("*") self.commitCount += 1 self.conn.commit() if self.lastRegister(currentTableName, nextTableName): # The last register set has been reached. if self.debug: self.logger.log("----- last register found -----", "debug") # Initial commit. if self.commitCount == 0: parseLog += self.generateConciseLogEntries(jobID=jobID, reportType="INTERMEDIARY") self.resetGroupCounters() # Final commit. parseLog += self.logger.logAndWrite("---") parseLog += self.generateConciseLogEntries(jobID=jobID, reportType="FINAL") self.resetGroupCounters() parseLog += self.logger.logAndWrite("*") self.commitCount += 1 self.conn.commit() sys.stderr.write("\n") self.logger.log("Data process count = %s." % self.dataProcessCount, "info") self.logger.log("Reading dupe check count = %s." % self.readingDupeCheckCount, "info") return parseLog def lastReading(self, currentTable, nextTable): """ Determine if the last reading is being visited. :param currentTable: current table being processsed. :param nextTable: next table to be processed. :returns: True if last object in Reading table was read, otherwise return False. """ if currentTable == "Reading" and (nextTable == "MeterData" or nextTable == None): return True return False def lastRegister(self, currentTable, nextTable): """ Determine if the last register is being visited. :param currentTable: current table being processsed. :param nextTable: next table to be processed. :returns: True if last object in Register table was read, otherwise return False. """ if currentTable == "Register" and (nextTable == "MeterData" or nextTable == None): return True return False def getNext(self, somethingIterable, window=1): """ Return the current item and next item in an iterable data structure. :param somethingIterable: Something that has an iterator. :param window: How far to look ahead in the collection. :returns: The current iterable value and the next iterable value. """ items, nexts = tee(somethingIterable, 2) nexts = islice(nexts, window, None) return izip_longest(items, nexts) def initChannelProcessed(self): """ Initialize the dictionary for channel processing. """ self.channelProcessed = {"1": False, "2": False, "3": False, "4": False} def getLastElement(self, rows): """ Get the last element in a collection. Example: rows = (element1, element2, element3) getLastElement(rows) # return element3 :param rows: Result rows from a query. :returns: The last element in the collection. """ for i, var in enumerate(rows): if i == len(rows) - 1: return var
class MECODupeChecker(object): """ Check for duplicate data in the database. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'debug') self.mecoConfig = MSGConfiger() self.currentReadingID = 0 self.dbUtil = MSGDBUtil() def getLastElement(self, rows): """ Get the last element in a collection. Example: rows = (element1, element2, element3) getLastElement(rows) # return element3 :param rows Result froms from a query :return last element in the collection """ for i, var in enumerate(rows): if i == len(rows) - 1: return var def eventBranchDupeExists(self, conn, meterName, eventTime): """ :param conn: Database connection. :param meterName: Meter name in MeterData table. :param eventTime: Timestamp of event. :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() sql = """SELECT "Event".event_time, "MeterData".meter_data_id, "EventData".event_data_id FROM ( ( "MeterData" JOIN "EventData" ON ( ( "MeterData".meter_data_id = "EventData" .meter_data_id ) ) ) JOIN "Event" ON ( ( "EventData".event_data_id = "Event" .event_data_id ) ) ) WHERE "MeterData".meter_name = '%s' AND "Event".event_time = '%s' """ % (meterName, eventTime) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False def registerBranchDupeExists(self, conn, meterName, readTime, registerNumber, DEBUG = False): """ Determine if a register branch duplicate exists for a given meter name, read time, number tuple. :param conn: Database connection. :param meterName: Meter name in MeterData table. :param readTime: Read time in RegisterRead table. :param registerNumber: Corresponds to DB column "number". :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() sql = """SELECT "public"."MeterData".meter_name, "public"."RegisterRead".read_time, "public"."Register"."number" FROM "public"."MeterData" INNER JOIN "public"."RegisterData" ON "public" ."MeterData".meter_data_id = "public" ."RegisterData".meter_data_id INNER JOIN "public"."RegisterRead" ON "public"."RegisterData" .register_data_id = "public" ."RegisterRead".register_data_id INNER JOIN "public"."Tier" ON "public"."RegisterRead" .register_read_id = "public"."Tier" .register_read_id INNER JOIN "public"."Register" ON "public"."Tier".tier_id = "public"."Register".tier_id WHERE "public"."MeterData".meter_name = '%s' AND "public"."RegisterRead".read_time = '%s' AND "public"."Register".number = '%s' """ % (meterName, readTime, registerNumber) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False def readingBranchDupeExists(self, conn, meterName, endTime, channel = None, DEBUG = False): """ Duplicate cases: 1. Tuple (meterID, endTime) exists in the database. @DEPRECATED in favor of (2), full meterName-endTime-channel query. 2. Tuple (meterID, endTime, channel) exists in the database. :param conn: Database connection. :param meterName: Meter name in MeterData table. :param endTime: End time in Interval table. :param channel: Required parameter that was previously optional. An optional channel is now deprecated. :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() if DEBUG: print "readingBranchDupeExists():" if channel != None: sql = """SELECT "Interval".end_time, "MeterData".meter_name, "MeterData".meter_data_id, "Reading".channel, "Reading".reading_id FROM "MeterData" INNER JOIN "IntervalReadData" ON "MeterData" .meter_data_id = "IntervalReadData".meter_data_id INNER JOIN "Interval" ON "IntervalReadData" .interval_read_data_id = "Interval".interval_read_data_id INNER JOIN "Reading" ON "Interval".interval_id = "Reading" .interval_id WHERE "Interval".end_time = '%s' and meter_name = '%s' and channel = '%s'""" % ( endTime, meterName, channel) else: # deprecated query sql = """SELECT "Interval".end_time, "MeterData".meter_name, "MeterData".meter_data_id FROM "MeterData" INNER JOIN "IntervalReadData" ON "MeterData" .meter_data_id = "IntervalReadData".meter_data_id INNER JOIN "Interval" ON "IntervalReadData" .interval_read_data_id = "Interval".interval_read_data_id WHERE "Interval".end_time = '%s' and meter_name = '%s'""" % ( endTime, meterName) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: assert len( rows) < 2, "Dupes should be less than 2, found %s: %s." % ( len(rows), rows) self.currentReadingID = self.getLastElement(rows[0]) self.logger.log('Reading ID = %s.' % self.currentReadingID, 'silent') self.logger.log( "Duplicate found for meter %s, end time %s, channel %s." % ( meterName, endTime, channel), 'silent') return True else: self.logger.log( "Found no rows for meter %s, end time %s, channel %s." % ( meterName, endTime, channel), 'silent') return False def readingValuesAreInTheDatabase(self, conn, readingDataDict): """ Given a reading ID, verify that the values associated are present in the database. Values are from the columns: 1. channel 2. raw_value 3. uom 4. value :param dictionary containing reading values :return True if the existing values are the same, otherwise return False """ dbCursor = conn.cursor() sql = """SELECT "Reading".reading_id, "Reading".channel, "Reading".raw_value, "Reading".uom, "Reading"."value" FROM "Reading" WHERE "Reading".reading_id = %s""" % ( self.currentReadingID) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if self.currentReadingID == 0: return False # assert len(rows) == 1 or len(rows) == 0 assert len( rows) == 1, "Didn't find a matching reading for reading ID %s." %\ self.currentReadingID if len(rows) == 1: self.logger.log("Found %s existing matches." % len(rows), 'silent') allEqual = True if int(readingDataDict['Channel']) == int(rows[0][1]): print "channel equal," else: self.logger.log("channel not equal: %s,%s,%s" % ( int(readingDataDict['Channel']), int(rows[0][1]), readingDataDict['Channel'] == rows[0][1]), 'debug') allEqual = False if int(readingDataDict['RawValue']) == int(rows[0][2]): print "raw value equal," else: self.logger.log("rawvalue not equal: %s,%s,%s" % ( int(readingDataDict['RawValue']), int(rows[0][2]), readingDataDict['RawValue'] == rows[0][2]), 'debug') allEqual = False if readingDataDict['UOM'] == rows[0][3]: print "uom equal," else: self.logger.log("uom not equal: %s,%s,%s" % ( readingDataDict['UOM'], rows[0][3], readingDataDict['UOM'] == rows[0][3]), 'debug') allEqual = False if self.approximatelyEqual(float(readingDataDict['Value']), float(rows[0][4]), 0.001): self.logger.log("value equal", 'silent') else: self.logger.log("value not equal: %s,%s,%s" % ( float(readingDataDict['Value']), float(rows[0][4]), readingDataDict['Value'] == rows[0][4]), 'debug') allEqual = False if allEqual: return True else: return False else: return False def approximatelyEqual(self, a, b, tolerance): return abs(a - b) < tolerance
class MSGConfiger(object): """ Supports system-specific configuration for MECO data processing. The site-level configuration file is located in ~/.meco-data-operations.cfg. Usage: configer = MSGConfiger() """ def __init__(self): """ Constructor. """ self._config = ConfigParser.ConfigParser() self.logger = SEKLogger(__name__, 'INFO') self.fileUtil = SEKFileUtil() # Define tables that will have data inserted. Data will only be inserted # to tables that are defined here. self.insertTables = ( 'MeterData', 'RegisterData', 'RegisterRead', 'Tier', 'Register', 'IntervalReadData', 'Interval', 'Reading', 'EventData', 'Event') # Check permissions on the config file. Refuse to run if the permissions # are not set appropriately. configFilePath = '~/.msg-data-operations.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() 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 configOptionValue(self, section, option): """ Get a configuration value from the local configuration file. :param section: String of section in config file. :param option: String of option in config file. :returns: The value contained in the configuration file. """ try: configValue = self._config.get(section, option) if configValue == "True": return True elif configValue == "False": return False else: return configValue except: self.logger.log( "Failed when getting configuration option {} in section {" "}.".format(option, section), 'error') sys.exit(-1)
class TestMECONotifier(unittest.TestCase): """ Unit tests for the MECO Notifier. """ def setUp(self): self.logger = SEKLogger(__name__) self.notifier = MSGNotifier() self.configer = MSGConfiger() def tearDown(self): pass def testInit(self): self.assertIsNotNone(self.notifier, "Notifier has been initialized.") def testEmailServer(self): """ Test connecting to the email server. """ errorOccurred = False user = self.configer.configOptionValue("Notifications", "email_username") password = self.configer.configOptionValue("Notifications", "email_password") server = smtplib.SMTP(self.configer.configOptionValue("Notifications", "smtp_server_and_port")) try: server.starttls() except smtplib.SMTPException as detail: self.logger.log("Exception: {}".format(detail)) try: server.login(user, password) except smtplib.SMTPException as detail: self.logger.log("Exception: {}".format(detail)) self.assertFalse(errorOccurred, "No errors occurred during SMTP setup.") def testSendEmailNotification(self): """ Send a test notification by email. """ if SEND_EMAIL: success = self.notifier.sendNotificationEmail( "This is a message from testSendEmailNotification.", testing=True ) self.assertTrue(success, "Sending an email notification did not produce an" " exception.") else: self.assertTrue(True, "Email is not sent when SEND_EMAIL is False.") def testSendEmailAttachment(self): """ Send a test notification with attachment by email. """ if SEND_EMAIL: body = "Test message" testDataPath = self.configer.configOptionValue("Testing", "test_data_path") file = os.path.join(testDataPath, "graph.png") success = self.notifier.sendMailWithAttachments(body, [file], testing=True) success = success != True self.assertTrue(success, "Sending an email notification did not produce an" " exception.") else: self.assertTrue(True, "Email is not sent when SEND_EMAIL is False.")
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 ' \ '"{1}_{0}" ADD CONSTRAINT meter_id_fkey FOREIGN KEY (meter_id) ' \ 'REFERENCES "Meters"(meter_id) ON UPDATE CASCADE ON DELETE ' \ 'CASCADE; ALTER TABLE "{1}_{0}" OWNER TO {2}'.format( meterName, tableBase, tableOwner, pkey) if dbUtil.executeSQL(cursor, sql, exitOnFail = False): conn.commit() else: conn.rollback()
class MECODBInserter(object): """ Provides methods that perform insertion of MECO data. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'debug') self.mapper = MECOMapper() self.dupeChecker = MECODupeChecker() self.dbUtil = MSGDBUtil() def __call__(self, param): print "CallableClass.__call__(%s)" % param def insertData(self, conn, tableName, columnsAndValues, fKeyVal=None, withoutCommit=0): """ Given a table name and a dictionary of column names and values, insert them to the DB. :param conn: database connection :param tableName: name of the db table :param columnsAndValues: dictionary of columns and values to be inserted to the db :param (optional) fKeyVal: an explicit foreign key value :param (optional) withoutCommit: a flag indicated that the insert will not be immediately committed :returns: A database cursor. """ cur = conn.cursor() # Get a dictionary of mapped (from DB to source data) column names. columnDict = self.mapper.getDBColNameDict(tableName) dbColsAndVals = {} if VISUALIZE_DATA: print "----------" + tableName + "----------" print columnDict print columnsAndValues for col in columnDict.keys(): # Use default as the value for the primary key so that the # private key is obtained from the predefined sequence. if col == '_pkey': if VISUALIZE_DATA: print columnDict[col], # DB col name. print 'DEFAULT' dbColsAndVals[columnDict[col]] = 'DEFAULT' # For the foreign key, set the value from the given parameter. elif col == '_fkey': if VISUALIZE_DATA: print columnDict[col], # DB col name. print fKeyVal dbColsAndVals[columnDict[col]] = fKeyVal else: if VISUALIZE_DATA: print columnDict[col], # DB col name. # The Register and Reading tables need to handle NULL # values as a special case. if tableName == 'Register' or tableName == 'Reading': try: if VISUALIZE_DATA: print columnsAndValues[col] # data source value dbColsAndVals[columnDict[col]] = columnsAndValues[col] except: if VISUALIZE_DATA: print 'NULL' dbColsAndVals[columnDict[col]] = 'NULL' # For all other cases, simply pass the value. else: if VISUALIZE_DATA: print columnsAndValues[col] # data source value dbColsAndVals[columnDict[col]] = columnsAndValues[col] # Add a creation timestamp to MeterData. if tableName == 'MeterData': dbColsAndVals['created'] = 'NOW()' cols = [] vals = [] for col in dbColsAndVals.keys(): cols.append(col) # DEFAULT, NULL and NOW() need to appear without quotes. if dbColsAndVals[col] in {'DEFAULT', 'NULL', 'NOW()'}: vals.append(dbColsAndVals[col]) else: vals.append( "'%s'" % dbColsAndVals[col]) # Surround value with single quotes. sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % ( tableName, ','.join(cols), ','.join(vals)) self.dbUtil.executeSQL(cur, sql) if withoutCommit == 0: try: conn.commit() except: self.logger.log("ERROR: Commit failed.", 'debug') return cur
class MSGWeatherDataUtil(object): """ Utility methods for working with weather data. """ def __init__(self): """ Constructor. A database connection is not maintained here to keep this class lightweight. """ self.logger = SEKLogger(__name__, DEBUG) self.configer = MSGConfiger() self.url = self.configer.configOptionValue('Weather Data', 'weather_data_url') self.pattern = self.configer.configOptionValue('Weather Data', 'weather_data_pattern') self.fileList = [] self.dateList = [] # List of dates corresponding weather data files. self.fillFileListAndDateList() self.dbUtil = MSGDBUtil() def fillFileListAndDateList(self): """ Return a list of weather files obtained from the remote server used in processing weather data. """ response = urllib2.urlopen(self.url).read() self.logger.log('Filling file list:', DEBUG) for filename in re.findall(self.pattern, response): # Only examine first match group in the filename match. self.logger.log('filename {}'.format(filename[0]), DEBUG) self.fileList.append(filename[0]) self.dateList.append(self.datePart(filename[0])) def datePart(self, filename=None, datetime=None): """ Return the date part of a NOAA weather data filename. :param: String of the filename. :param: datetime object. :returns: String of the date part of the given parameter. """ assert filename == None or datetime == None, "One argument is allowed." if filename: newName = filename.replace("QCLCD", '') newName = newName.replace(".zip", '') return newName if datetime: return datetime.strftime('%Y-%m-%d') def getLastDateLoaded(self, cursor): """ Return the last date of loaded weather data. :returns: Last date. """ sql = """select wban, datetime, record_type from "%s" ORDER BY datetime desc limit 1""" % WEATHER_DATA_TABLE self.dbUtil.executeSQL(cursor, sql) row = cursor.fetchone() # self.logger.log('Date last loaded = %s' % row[1], 'info') return row[1] def getKeepList(self, fileList, cursor): """ The Keep List is the list of filenames of files containing data that are *within* the month of the last loaded date or are beyond the last loaded date. :param: fileList: A list of files containing weather data. :param: DB cursor. :returns: List of weather data filenames to process. """ keepList = [] i = 0 for date in fileList: self.logger.log('Examining date %s.' % date) # The list date should be the last day of the month. # It is the date that is compared against the last retrieved date. listDate = dt.datetime.strptime(self.datePart(filename=date), "%Y%m") lastDay = calendar.monthrange(listDate.year, listDate.month)[1] listDate = dt.datetime.strptime( '%s-%s-%s' % (listDate.year, listDate.month, lastDay), "%Y-%m-%d") self.logger.log('List date = %s.' % listDate) lastDate = self.getLastDateLoaded(cursor) self.logger.log('last date = %s' % lastDate) if lastDate <= listDate: keepList.append((i, listDate)) i += 1 if keepList: keepList.sort() return [fileList[d[0]] for d in keepList]
class MSGTimeUtil(object): """ Utilities for working with time. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'debug') def reportOfDays(self, datetimes=None): """ Return report of days processed given a set of days. :param datetimes: A set of datetimes. :returns: Report of processing as a string. """ # @todo Verify datetimes is a Set. # self.logger.log("datetimes = %s" % datetimes, 'debug') if datetimes is None: return "No days processed." myDates = set() for day in datetimes: self.logger.log('Processing day %s.' % day) myDates.add(day.date()) datetimeList = list(myDates) datetimeList.sort() countOfDays = len(datetimeList) firstDay = datetimeList[0] lastDay = datetimeList[len(datetimeList) - 1] if countOfDays == 1: return "Processed 1 day with date %s." % (firstDay) else: return "Processed %s days between %s to %s, inclusive." % ( countOfDays, firstDay, lastDay) def conciseNow(self): """ Returns the current date and time in a concise format. """ return dt.now().strftime('%Y-%m-%d_%H%M%S') def splitStringDates(self, startDate='', endDate=''): """ Break down two dates into a list containing the start and end dates for each month within the range. :param startDate: string :param endDate: string :return: List of tuples. """ # self.logger.log('start,end: %s,%s' % (startDate, endDate)) myDatetime = lambda x: dt.strptime(x, '%Y-%m-%d') firstDay = lambda x: dt.strptime(x.strftime('%Y-%m-01'), '%Y-%m-%d') startDates = map( firstDay, list( rrule.rrule(rrule.MONTHLY, dtstart=myDatetime(startDate), until=myDatetime(endDate)))) startDates[0] = myDatetime(startDate) lastDay = lambda x: dt.strptime( '%d-%d-%d' % (x.year, x.month, calendar.monthrange( x.year, x.month)[1]), '%Y-%m-%d') endDates = map(lastDay, startDates) endDates[-1] = myDatetime(endDate) assert len(startDates) == len( endDates), 'Mismatch of start and end dates.' return zip(startDates, endDates) def splitDates(self, start=None, end=None): """ Break down two dates into a list containing the start and end dates for each month within the range. :param start: datetime :param end: datetime :return: List of tuples. """ self.logger.log('start {}, end {}'.format(start, end), 'debug') # First day of the month. firstDay = lambda x: dt.strptime(x.strftime('%Y-%m-01'), '%Y-%m-%d') startDates = map( firstDay, list(rrule.rrule(rrule.MONTHLY, dtstart=firstDay(start), until=end))) # @todo add assert for verifying sorted start dates. startDates[0] = start lastDay = lambda x: dt.strptime( '%d-%d-%d' % (x.year, x.month, calendar.monthrange( x.year, x.month)[1]), '%Y-%m-%d') endDates = map(lastDay, startDates) endDates[-1] = end assert len(startDates) == len( endDates), 'Mismatch of start and end dates.' return zip(startDates, endDates) def datetimeForString(self, datetimeString): """ :param datetimeString: String :return: datetime of string in Y-m-d H:S format. """ return dt.strptime(datetimeString, '%Y-%m-%d %H:%S')
class MSGDataAggregator(object): """ Use for continuous data aggregation of diverse data types relevant to the Maui Smart Grid project. Four data types are supported: 1. Irradiance 2. Temperature/Humidity (weather) 3. Circuit 4. eGauge The general data form conforms to 1. timestamp, subkey_id, val1, val2, val3, ... 2. timestamp, val1, val2, val3, ... Case (2) is handled within the same space as (1) by testing for the existence of subkeys. Current aggregation consists of averaging over **15-min intervals**. Aggregation is performed in-memory and saved to the DB. The time range is delimited by start date and end date where the values are included in the range. The timestamps for aggregation intervals are the last timestamp in a respective series. * Aggregation subkeys are values such as eGauge IDs or circuit numbers. Aggregation is being implemented externally for performance and flexibility advantages over alternative approaches such as creating a view. It may be rolled into an internal function at future time if that proves to be beneficial. Usage: from msg_data_aggregator import MSGDataAggregator aggregator = MSGDataAggregator() API: aggregateAllData(dataType = dataType) aggregateNewData(dataType = dataType) """ def __init__(self, exitOnError=True, commitOnEveryInsert=False, testing=False): """ Constructor. :param testing: if True, the testing DB will be connected instead of the production DB. """ self.logger = SEKLogger(__name__, "info") self.configer = MSGConfiger() self.conn = MSGDBConnector().connectDB() self.cursor = self.conn.cursor() self.dbUtil = MSGDBUtil() self.notifier = MSGNotifier() self.mathUtil = MSGMathUtil() self.timeUtil = MSGTimeUtil() self.nextMinuteCrossing = {} self.nextMinuteCrossingWithoutSubkeys = None self.exitOnError = exitOnError self.commitOnEveryInsert = commitOnEveryInsert section = "Aggregation" tableList = [ "irradiance", "agg_irradiance", "weather", "agg_weather", "circuit", "agg_circuit", "egauge", "agg_egauge", ] self.dataParams = { "weather": ("agg_weather", "timestamp", ""), "egauge": ("agg_egauge", "datetime", "egauge_id"), "circuit": ("agg_circuit", "timestamp", "circuit"), "irradiance": ("agg_irradiance", "timestamp", "sensor_id"), } self.columns = {} # tables[datatype] gives the table name for datatype. self.tables = {t: self.configer.configOptionValue(section, "{}_table".format(t)) for t in tableList} for t in self.tables.keys(): self.logger.log("t:{}".format(t), "DEBUG") try: self.columns[t] = self.dbUtil.columnsString(self.cursor, self.tables[t]) except TypeError as error: self.logger.log("Ignoring missing table: Error is {}.".format(error), "error") def existingIntervals(self, aggDataType="", timeColumnName=""): """ Retrieve the existing aggregation intervals for the given data type. :param aggDataType: string :param timeColumnName: string :return: List of intervals. """ return [ x[0] for x in self.rows( """SELECT {0} from \"{1}\" ORDER BY {2}""".format( timeColumnName, self.tables[aggDataType], timeColumnName ) ) ] def unaggregatedIntervalCount(self, dataType="", aggDataType="", timeColumnName="", idColumnName=""): """ Return count of unaggregated intervals for a given data type. :param dataType: :param aggDataType: :param timeColumnName: :param idColumnName: :return: int """ return len(self.unaggregatedEndpoints(dataType, aggDataType, timeColumnName, idColumnName)) def lastAggregationEndpoint(self, aggDataType="", timeColumnName=""): """ Last aggregation endpoint for a given datatype. :param dataType: :param timeColumnName: :return: """ return self.existingIntervals(aggDataType=aggDataType, timeColumnName=timeColumnName)[-1] def unaggregatedEndpoints(self, dataType="", aggDataType="", timeColumnName="", idColumnName=""): """ Sorted (ascending) endpoints and their IDs, if available, for unaggregated intervals since the last aggregation endpoint for a given data type. This has a problem where an endpoint at 23:45:04 will be returned as 23:45:00. This makes the return value incorrect for raw data types having readings at sub-minute intervals such as data for circuit, irradiance and weather. This condition does not affect correct aggregation. Only the definition of the return value is wrong. :param dataType: string :param aggDataType: string :param timeColumnName: string :param idColName: string :return: list of datetimes. """ if idColumnName != "": # Key: # 0: raw # 1: agg # 2: time col # 3: id col # 4: last aggregated time sql = ( 'SELECT "{0}".{2}, "{0}".{3} FROM "{0}" LEFT JOIN "{1}" ON ' '"{0}".{2} = "{1}".{2} AND "{0}".{3} = "{1}".{3} WHERE "{' '1}".{2} IS NULL AND "{0}".{2} > \'{4}\' ORDER BY {2} ASC, ' "{3} ASC" ) self.logger.log("last agg endpoint: {}".format(self.lastAggregationEndpoint(aggDataType, timeColumnName))) # The id column value is available in the tuple returned by # groupby but is not being used here. # @todo Exclude last endpoint if it is equal to the last # aggregation endpoint. # # The minute position filtering may be including the last # endpoint incorrectly because there are readings occurring # within the same minute as the final endpoint, e.g. 23:45:04, # 23:45:08, etc. # # This is not a problem with eGuage data due reading intervals # being every minute and zero seconds. return map( lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [ k for k, v in groupby( map( lambda y: y[0].timetuple()[0:5], filter( lambda x: x[0].timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0, [ (x[0], x[1]) for x in self.rows( sql.format( self.tables[dataType], self.tables[aggDataType], timeColumnName, idColumnName, self.lastAggregationEndpoint(aggDataType, timeColumnName), ) ) ], ), ) ) ], ) else: # Key: # 0: raw # 1: agg # 2: time col # 3: last aggregated time sql = ( 'SELECT "{0}".{2} FROM "{0}" LEFT JOIN "{1}" ON "{0}".{2}=' '"{1}".{2} WHERE "{1}".{2} IS NULL AND "{0}".{2} > \'{3}\' ' "ORDER BY {2} ASC" ) self.logger.log("last agg endpoint: {}".format(self.lastAggregationEndpoint(aggDataType, timeColumnName))) return map( lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [ k for k, v in groupby( map( lambda y: y.timetuple()[0:5], filter( lambda x: x.timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0, [ (x[0]) for x in self.rows( sql.format( self.tables[dataType], self.tables[aggDataType], timeColumnName, self.lastAggregationEndpoint(aggDataType, timeColumnName), ) ) ], ), ) ) ], ) def intervalCrossed(self, minute=None, subkey=None): """ Determine interval crossing. Intervals are at 0, 15, 45, 60 min. The interval size is determined by MECO source data. :param minute: The integer value of the minute. :param subkey: The name for the subkey used for aggregation. :returns: True if an interval was crossed, False otherwise. """ if not minute and minute != 0: raise Exception("Minute not defined.") intervalSize = 15 first = 0 last = 60 if subkey is not None: if ( minute >= self.nextMinuteCrossing[subkey] and minute <= last and self.nextMinuteCrossing[subkey] != first ): self.nextMinuteCrossing[subkey] += intervalSize if self.nextMinuteCrossing[subkey] >= last: self.nextMinuteCrossing[subkey] = first self.logger.log("minute crossed at #1.", "debug") return True elif self.nextMinuteCrossing[subkey] == first and minute >= first and minute <= intervalSize: self.nextMinuteCrossing[subkey] = intervalSize self.logger.log("minute crossed at #2.", "debug") return True return False else: if ( minute >= self.nextMinuteCrossingWithoutSubkeys and minute <= last and self.nextMinuteCrossingWithoutSubkeys != first ): self.nextMinuteCrossingWithoutSubkeys += intervalSize if self.nextMinuteCrossingWithoutSubkeys >= last: self.nextMinuteCrossingWithoutSubkeys = first self.logger.log("minute crossed at #3.", "debug") return True elif self.nextMinuteCrossingWithoutSubkeys == first and minute >= first and minute <= intervalSize: self.nextMinuteCrossingWithoutSubkeys = intervalSize self.logger.log("minute crossed at #4.", "debug") return True return False def rows(self, sql): """ Rows from a SQL fetch. :param sql: Command to be executed. :returns: DB result set. """ self.logger.log("sql: {}".format(sql), "debug") self.dbUtil.executeSQL(self.cursor, sql) return self.cursor.fetchall() def rawData(self, dataType="", orderBy=None, timestampCol="", startDate="", endDate=""): """ Raw data to be aggregated. :param dataType: string :param orderBy: list :param timestampCol: string :param startDate: string :param endDate: string :returns: DB rows. """ # @todo Validate args. orderBy = filter(None, orderBy) return self.rows( """SELECT {} FROM "{}" WHERE {} BETWEEN '{}' AND '{}' ORDER BY {}""".format( self.columns[dataType], self.tables[dataType], timestampCol, startDate, endDate, ",".join(orderBy) ) ) def subkeys(self, dataType="", timestampCol="", subkeyCol="", startDate="", endDate=""): """ The distinct subkeys for a given data type within a time range. Subkeys are fields such as egauge_id in eGauge data or sensor_id in irradiance data. :param dataType: string :param timestampCol: string :param subkeyCol: string :param startDate: string :param endDate: string :returns: List of subkeys """ return [ sk[0] for sk in self.rows( """SELECT DISTINCT({}) FROM "{}" WHERE {} BETWEEN '{}' AND '{}' ORDER BY {}""".format( subkeyCol, self.tables[dataType], timestampCol, startDate, endDate, subkeyCol ) ) ] def insertAggregatedData(self, agg=None): """ :param agg: MSGAggregatedData :return: None """ if not agg.columns: raise Exception("agg columns not defined.") if not agg.data: raise Exception("agg data not defined.") self.logger.log("agg data: {}".format(agg.data)) self.logger.log("agg data type: {}".format(type(agg.data))) def __insertData(values=""): """ Perform insert of data to the database using the given values. :param values: String containing values to be inserted. :return Nothing. """ sql = 'INSERT INTO "{0}" ({1}) VALUES( {2})'.format( self.tables[agg.aggregationType], ",".join(agg.columns), values ) self.logger.log("sql: {}".format(sql), "debug") success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=self.exitOnError) # Used for a special case where data is reloaded. if self.commitOnEveryInsert: self.conn.commit() if not success and self.exitOnError: raise Exception("Failure during aggregated data insert.") for row in agg.data: if type(row) == type({}): # self.logger.log('row=%s' % row, 'debug') # self.logger.log('row type: %s' % type(row)) for key in row.keys(): values = "" valCnt = 0 for val in row[key]: if val == "NULL": values += val elif type(val) == type(""): values += "'" + val.strip() + "'" elif isinstance(val, datetime): values += "'" + val.isoformat() + "'" elif type(val) == type(0): values += str(val) elif type(val) == type(0.0): values += str(val) else: values += val if valCnt < len(agg.columns) - 1: values += "," valCnt += 1 __insertData(values=values) elif type(row) == type([]): values = "" valCnt = 0 for val in row: if val == "NULL": values += val elif type(val) == type(""): values += "'" + val.strip() + "'" elif isinstance(val, datetime): values += "'" + val.isoformat() + "'" elif type(val) == type(0): values += str(val) elif type(val) == type(0.0): values += str(val) else: values += val if valCnt < len(agg.columns) - 1: values += "," valCnt += 1 __insertData(values=values) else: self.logger.log("row = {}".format(row), "error") raise Exception("Row type not matched.") # End for row. self.conn.commit() def intervalAverages(self, sums, cnts, timestamp, timestampIndex, subkeyIndex=None, subkey=None): """ Aggregates all data for the current interval for the given subkey. For the case where there are no subkeys, subkeyIndex and subkey should be None. :param sums: list :param cnts: list :param timestamp: datetime :param timestampIndex: int :param subkeyIndex: int :param subkey: string :returns: Averaged data as a dict with form {subkey:data} """ if subkey is not None: myAvgs = {} reportedAgg = False myAvgs[subkey] = [] sumIndex = 0 self.logger.log("key: {}".format(subkey), "debug") # Iterate over sums. for s in sums[subkey]: if sumIndex == timestampIndex: myAvgs[subkey].append(timestamp) elif sumIndex == subkeyIndex: myAvgs[subkey].append(subkey) else: if cnts[subkey][sumIndex] != 0: if not reportedAgg: self.logger.log("Aggregating {} rows of data.".format(cnts[subkey][sumIndex]), "debug") reportedAgg = True myAvgs[subkey].append(s / cnts[subkey][sumIndex]) else: myAvgs[subkey].append("NULL") sumIndex += 1 return myAvgs else: myAvgs = [] reportedAgg = False sumIndex = 0 for s in sums: if sumIndex == timestampIndex: myAvgs.append(timestamp) else: if cnts[sumIndex] != 0: if not reportedAgg: self.logger.log("Aggregating {} rows of data.".format(cnts[sumIndex]), "debug") reportedAgg = True myAvgs.append(s / cnts[sumIndex]) else: myAvgs.append("NULL") sumIndex += 1 return myAvgs def dataParameters(self, dataType=""): """ Parameters for a given data type. :param dataType: string :return: (aggType, timeColName, subkeyColName) """ try: assert len(self.dataParams[dataType]) == 3 return self.dataParams[dataType] except: self.logger.log("Unmatched data type {}.".format(dataType)) def aggregateAllData(self, dataType=""): """ Convenience method for aggregating all data for a given data type. Data is inserted to individual aggregated data tables. :param dataType: String in the list of raw data types. :return: Nothing. """ (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) for start, end in self.monthStartsAndEnds(timeColumnName=timeColName, dataType=dataType): self.logger.log("start, end: {}, {}".format(start, end)) aggData = self.aggregatedData( dataType=dataType, aggregationType=aggType, timeColumnName=timeColName, subkeyColumnName=subkeyColName, startDate=start.strftime("%Y-%m-%d %H:%M:%S"), endDate=end.strftime("%Y-%m-%d %H:%M:%S"), ) self.insertAggregatedData(agg=aggData) for row in aggData.data: self.logger.log("aggData row: {}".format(row)) def aggregateNewData(self, dataType=""): """ Convenience method for aggregating new data. :param dataType: :return: dict of {dataType: count of aggregation endpoints} """ # The new aggregation starting point is equal to the last aggregation # endpoint up to the last unaggregated endpoint. (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) (end, start) = self.lastUnaggregatedAndAggregatedEndpoints(dataType).items()[0][1] self.logger.log( "datatype: {}; start, end: {}, {}; end type: {}".format(dataType, start, end, type(end)), "critical" ) if type(end) == type(None): # No available unaggregated endpoints results in an empty list # for type egauge. The reason this does not work for other types is # because the other types of fractional minute readings and the # fractional minute readings are not being handled completely but # this method is still capable of working without problem. self.logger.log("Nothing to aggregate.") return {dataType: 0} if self.incrementEndpoint(start) >= end: self.logger.log("Nothing to aggregate.") return {dataType: 0} aggData = self.aggregatedData( dataType=dataType, aggregationType=aggType, timeColumnName=timeColName, subkeyColumnName=subkeyColName, startDate=self.incrementEndpoint(start).strftime("%Y-%m-%d %H:%M:%S"), endDate=end.strftime("%Y-%m-%d %H:%M:%S"), ) self.insertAggregatedData(agg=aggData) for row in aggData.data: self.logger.log("aggData row: {}".format(row)) self.logger.log("{} rows aggregated for {}.".format(len(aggData.data), dataType)) return {dataType: len(aggData.data)} def incrementEndpoint(self, endpoint=None): """ Increment an endpoint by one interval where endpoints are the final timestamp in an aggregation interval. :param endpoint: the endpoint to be incremented. :return: datetime object that is the given endpoint + a predefined amount of minutes. """ plusOneInterval = relativedelta(minutes=15) return endpoint + plusOneInterval def lastUnaggregatedAndAggregatedEndpoints(self, dataType=""): """ Return the endpoints for the given data type in the form {datatype: (last unaggregated endpoint, last aggregated endpoint)}. :param dataType: :return: dict with tuple. """ self.logger.log("datatype {}".format(dataType)) (aggType, timeColName, subkeyColName) = self.dataParameters(dataType) self.logger.log("subkey colname {}".format(subkeyColName)) unAggregatedEndpoints = self.unaggregatedEndpoints( dataType=dataType, aggDataType=aggType, timeColumnName=timeColName, idColumnName=subkeyColName ) self.logger.log("unagg endpoints: {}".format(unAggregatedEndpoints)) return { dataType: ( unAggregatedEndpoints[-1] if unAggregatedEndpoints != [] else None, self.lastAggregationEndpoint(aggDataType=aggType, timeColumnName=timeColName), ) } def aggregatedVsNewData(self): """ Convenience method. :return: dict of tuples containing {datatype:(last raw datetime, last agg datetime)} """ return { x.keys()[0]: (x.values()[0]) for x in map(self.lastUnaggregatedAndAggregatedEndpoints, [k for k in self.dataParams]) } def monthStartsAndEnds(self, timeColumnName="", dataType=""): """ Return first date and last date for the given **raw** data type for each month in the data's entire time range. The end date is incremented by on aggregation period to account for the data obtained at time 00:00. :param timeColumnName: string :param dataType: string :return: List of tuples. """ self.logger.log("datatype {}".format(dataType), "debug") (start, end) = self.rows( """SELECT MIN({}), MAX({}) FROM \"{}\"""".format(timeColumnName, timeColumnName, self.tables[dataType]) )[0] self.logger.log("start {}".format(start)) self.logger.log("end {}".format(end)) # End time needs transforming in split dates to extend the end of the # day to 23:59:59. splitDates = self.timeUtil.splitDates(start, end) startEndDatesTransform = [] i = 0 while i < len(splitDates): startEndDatesTransform.append( ( splitDates[i][0], self.incrementEndpoint( datetime( splitDates[i][1].timetuple()[0], splitDates[i][1].timetuple()[1], splitDates[i][1].timetuple()[2], 23, 59, 59, ) ), ) ) i += 1 return startEndDatesTransform def aggregatedData( self, dataType="", aggregationType="", timeColumnName="", subkeyColumnName="", startDate="", endDate="" ): """ *********************************************************************** Provide aggregated data. *********************************************************************** Start and end dates are used to calculate interval crossings. :param dataType: String :param aggregationType: String :param timeColumnName: String :param subkeyColumnName: String :param startDate: String :param endDate: String :returns: MSGAggregatedData """ aggData = [] ci = lambda col_name: self.columns[dataType].split(",").index(col_name) rowCnt = 0 mySubkeys = [] if subkeyColumnName: mySubkeys = self.subkeys( dataType=dataType, timestampCol=timeColumnName, subkeyCol=subkeyColumnName, startDate=startDate, endDate=endDate, ) self.logger.log("subkeys: {}".format(mySubkeys), "debug") def __initSumAndCount(subkey=None, sums=None, cnts=None): """ Initialize the sum and cnt data structures. :param subkey: string :param sums: list | dict | None :param cnts: list | dict | None """ if not sums and not cnts: sums = {} cnts = {} if not mySubkeys: sums = [] cnts = [] for i in range(len(self.columns[dataType].split(","))): sums.append(0) cnts.append(0) else: if not subkey: for i in range(len(self.columns[dataType].split(","))): for k in mySubkeys: if k not in sums.keys(): sums[k] = [] cnts[k] = [] sums[k].append(0) cnts[k].append(0) else: sums[subkey] = [] for i in range(len(self.columns[dataType].split(","))): sums[subkey].append(0) cnts[subkey] = [] for i in range(len(self.columns[dataType].split(","))): cnts[subkey].append(0) return (sums, cnts) (sum, cnt) = __initSumAndCount() def __initIntervalCrossings(): """ Perform initialization of the interval crossings used to determine when interval crossings occur. :returns None """ subkeysToCheck = copy.copy(mySubkeys) self.logger.log("subkeys to check: {}".format(subkeysToCheck), "debug") if mySubkeys: for row in self.rawData( dataType=dataType, orderBy=[timeColumnName, subkeyColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate, ): # @CRITICAL: Exit after every subkey has been visited. # This scans the raw data until each subkey is encountered # ONCE and then exits. if subkeysToCheck != []: if row[ci(subkeyColumnName)] in subkeysToCheck: subkeysToCheck.remove(row[ci(subkeyColumnName)]) minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if minute <= 15: self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 15 elif minute <= 30: self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 30 elif minute <= 45: self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 45 elif minute == 0 or minute <= 59: self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 0 else: raise Exception("Unable to determine next minute crossing") self.logger.log( "next min crossing for {} = {}".format( row[ci(subkeyColumnName)], self.nextMinuteCrossing[row[ci(subkeyColumnName)]] ), "debug", ) else: break else: # Non-subkey case e.g. weather data. rowCnt = 0 # @todo Optimize by querying only the first row. for row in self.rawData( dataType=dataType, orderBy=[timeColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate, ): minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if minute <= 15: self.nextMinuteCrossingWithoutSubkeys = 15 elif minute <= 30: self.nextMinuteCrossingWithoutSubkeys = 30 elif minute <= 45: self.nextMinuteCrossingWithoutSubkeys = 45 elif minute == 0 or minute <= 59: self.nextMinuteCrossingWithoutSubkeys = 0 else: raise Exception("Unable to determine next minute crossing") self.logger.log("next min crossing = {}".format(self.nextMinuteCrossingWithoutSubkeys), "debug") rowCnt += 1 if rowCnt > 0: break __initIntervalCrossings() for row in self.rawData( dataType=dataType, orderBy=[timeColumnName, subkeyColumnName], timestampCol=timeColumnName, startDate=startDate, endDate=endDate, ): if mySubkeys: for col in self.columns[dataType].split(","): if self.mathUtil.isNumber(row[ci(col)]) and ci(col) != ci(subkeyColumnName): sum[row[ci(subkeyColumnName)]][ci(col)] += row[ci(col)] cnt[row[ci(subkeyColumnName)]][ci(col)] += 1 minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if self.intervalCrossed(minute=minute, subkey=row[ci(subkeyColumnName)]): minuteCrossed = minute # Perform aggregation on all of the previous data including # the current data for the current subkey. self.logger.log("key: {}".format(row[ci(subkeyColumnName)]), "debug") aggData += [ self.intervalAverages( sum, cnt, row[ci(timeColumnName)], ci(timeColumnName), ci(subkeyColumnName), row[ci(subkeyColumnName)], ) ] self.logger.log("minute crossed {}".format(minuteCrossed), "DEBUG") # Init current sum and cnt for subkey that has a completed # interval. (sum, cnt) = __initSumAndCount(subkey=row[ci(subkeyColumnName)], sums=sum, cnts=cnt) else: for col in self.columns[dataType].split(","): if self.mathUtil.isNumber(row[ci(col)]): sum[ci(col)] += row[ci(col)] cnt[ci(col)] += 1 minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION] if self.intervalCrossed(minute=minute): aggData += [self.intervalAverages(sum, cnt, row[ci(timeColumnName)], ci(timeColumnName))] (sum, cnt) = __initSumAndCount(subkey=None, sums=sum, cnts=cnt) rowCnt += 1 self.logger.log("aggdata = {}".format(aggData), "debug") return MSGAggregatedData( aggregationType=aggregationType, columns=self.columns[dataType].split(","), data=aggData )
# Get the keep list. keepList = weatherUtil.getKeepList(retriever.fileList, cursor) if keepList: msg = "Performing secondary retrieval." print msg MSG_BODY += "%s\n" % msg retriever.pool = multiprocessing.Pool(int(multiprocessingLimit)) results = retriever.pool.map(performDownloadingWithForcedDownload, keepList) retriever.pool.close() retriever.pool.join() if False in results: msg = "An error occurred during secondary retrieval." print msg MSG_BODY += "%s\n" % msg if downloadCount == 0: # Retrieve the last dated set if nothing else was retrieved. retriever.dateList.sort() logger.log("filelist {}".format(retriever.fileList), INFO) performDownloading(retriever.fileList[-1], forceDownload=True) msg = "downloadCount = %s." % downloadCount print msg MSG_BODY += "%s\n" % msg cleanUpTxtFiles() saveRetrievalResults()
result = insertDataWrapper(path) pattern = 'Process-(\d+),' jobString = str(multiprocessing.current_process()) match = re.search(pattern, jobString) assert match.group(1) is not None, "Process ID was matched." returnDict[match.group(1)] = result if __name__ == '__main__': processCommandLineArguments() inserter = Inserter() if COMMAND_LINE_ARGS.testing: logger.log("Testing mode is ON.\n", 'info') if COMMAND_LINE_ARGS.email: logger.log("Email will be sent.\n", 'info') msg = '' # Used for the notification message. msgBody = '' # Used for the notification message. databaseName = '' if COMMAND_LINE_ARGS.testing: databaseName = configer.configOptionValue("Database", "testing_db_name") else: databaseName = configer.configOptionValue("Database", "db_name") msg = "Recursively inserting data to the database named %s." % databaseName print msg
class SEKDBConnector(object): """ Make and manage a connection to a PostgreSQL database. Usage: conn = SEKDBConnector().connectDB() cursor = conn.cursor() """ def __init__(self, dbName='', dbHost='', dbPort='', dbUsername='', dbPassword='', testing=False, logLevel='silent'): """ Constructor. :param testing: Boolean indicating if Testing Mode is on. When testing mode is on, a connection to the testing database will be made instead of the production database. This is useful for unit testing. :param logLevel """ self.logger = SEKLogger(__name__, logLevel) if testing: self.logger.log("Testing Mode is ON.") self.dbName = dbName self.dbHost = dbHost self.dbPort = dbPort self.dbPassword = dbPassword self.dbUsername = dbUsername self.logger.log( "Instantiating DB connector with database {}.".format(dbName)) self.conn = self.connectDB() if not self.conn: self.logger.log('DB connection not available.', 'error') sys.exit(-1) try: self.dictCur = self.conn.cursor( cursor_factory=psycopg2.extras.DictCursor) except AttributeError as error: self.logger.log('Error while getting DictCursor: {}'.format(error)) def connectDB(self): """ Make the DB connection. :returns: DB connection object if successful, otherwise None. """ # @todo Make this method private since the init makes the connection. conn = None try: conn = psycopg2.connect( "dbname='{0}' user='******' host='{2}' port='{3}' password='******'".format(self.dbName, self.dbUsername, self.dbHost, self.dbPort, self.dbPassword)) except Exception as detail: self.logger.log( "Failed to connect to the database {}: {}.".format( self.dbName, detail), 'error') sys.exit(-1) self.logger.log("Opened DB connection to database {}.".format( self.dbName)) return conn def closeDB(self, conn): """ Close a database connection. """ self.logger.log("Closing database {}.".format(self.dbName)) conn.close() def __del__(self): """ Destructor. Close the database connection. """ self.logger.log("Closing the DB connection to database {}.".format( self.dbName)) self.conn.close()
class Inserter(object): """ Perform insertion of data contained in a single file to the MECO database specified in the configuration file. """ def __init__(self, testing=False): """ Constructor. :param testing: Flag indicating if testing mode is on. """ self.logger = SEKLogger(__name__) self.parser = MECOXMLParser(testing) self.configer = MSGConfiger() def insertData(self, filePath, testing=False, jobID=''): """ Insert data from a single file to the database. :param filePath: Full path of a data file. :param testing: Boolean flag indicating if the testing database should be used. :param jobID: An ID used to distinguish multiprocessing jobs. :returns: String containing concise log of activity. """ parseMsg = '' parseLog = '' print "Processing file %s." % filePath i = Inserter(testing) if i.configer.configOptionValue("Debugging", "debug"): print "Debugging is on" if testing: parseMsg = "\nInserting data to database %s.\n" % i.configer\ .configOptionValue( "Database", "testing_db_name") sys.stderr.write(parseMsg) parseLog += parseMsg else: parseMsg += "\nInserting data to database %s.\n" % i.configer\ .configOptionValue( "Database", "db_name") sys.stderr.write(parseMsg) parseLog += parseMsg fileObject = None # Open the file and process it. if re.search('.*\.xml$', filePath): fileObject = open(filePath, "rb") elif re.search('.*\.xml\.gz$', filePath): fileObject = gzip.open(filePath, "rb") else: print "Error: %s is not an XML file." % filePath try: with FileLock(filePath, timeout=2) as lock: self.logger.log("Locking %s " % filePath) i.parser.filename = filePath # Obtain the log of the parsing. parseLog += i.parser.parseXML(fileObject, True, jobID=jobID) fileObject.close() except TypeError: self.logger.log('Type error occurred', 'error') return parseLog
class MECODupeChecker(object): """ Check for duplicate data in the database. """ def __init__(self): """ Constructor. """ self.logger = SEKLogger(__name__, 'debug') self.mecoConfig = MSGConfiger() self.currentReadingID = 0 self.dbUtil = MSGDBUtil() def getLastElement(self, rows): """ Get the last element in a collection. Example: rows = (element1, element2, element3) getLastElement(rows) # return element3 :param rows Result froms from a query :return last element in the collection """ for i, var in enumerate(rows): if i == len(rows) - 1: return var def eventBranchDupeExists(self, conn, meterName, eventTime): """ :param conn: Database connection. :param meterName: Meter name in MeterData table. :param eventTime: Timestamp of event. :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() sql = """SELECT "Event".event_time, "MeterData".meter_data_id, "EventData".event_data_id FROM ( ( "MeterData" JOIN "EventData" ON ( ( "MeterData".meter_data_id = "EventData" .meter_data_id ) ) ) JOIN "Event" ON ( ( "EventData".event_data_id = "Event" .event_data_id ) ) ) WHERE "MeterData".meter_name = '%s' AND "Event".event_time = '%s' """ % (meterName, eventTime) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False def registerBranchDupeExists(self, conn, meterName, readTime, registerNumber, DEBUG=False): """ Determine if a register branch duplicate exists for a given meter name, read time, number tuple. :param conn: Database connection. :param meterName: Meter name in MeterData table. :param readTime: Read time in RegisterRead table. :param registerNumber: Corresponds to DB column "number". :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() sql = """SELECT "public"."MeterData".meter_name, "public"."RegisterRead".read_time, "public"."Register"."number" FROM "public"."MeterData" INNER JOIN "public"."RegisterData" ON "public" ."MeterData".meter_data_id = "public" ."RegisterData".meter_data_id INNER JOIN "public"."RegisterRead" ON "public"."RegisterData" .register_data_id = "public" ."RegisterRead".register_data_id INNER JOIN "public"."Tier" ON "public"."RegisterRead" .register_read_id = "public"."Tier" .register_read_id INNER JOIN "public"."Register" ON "public"."Tier".tier_id = "public"."Register".tier_id WHERE "public"."MeterData".meter_name = '%s' AND "public"."RegisterRead".read_time = '%s' AND "public"."Register".number = '%s' """ % (meterName, readTime, registerNumber) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: return True else: return False def readingBranchDupeExists(self, conn, meterName, endTime, channel=None, DEBUG=False): """ Duplicate cases: 1. Tuple (meterID, endTime) exists in the database. @DEPRECATED in favor of (2), full meterName-endTime-channel query. 2. Tuple (meterID, endTime, channel) exists in the database. :param conn: Database connection. :param meterName: Meter name in MeterData table. :param endTime: End time in Interval table. :param channel: Required parameter that was previously optional. An optional channel is now deprecated. :return: True if tuple exists, False if not. """ dbCursor = conn.cursor() if DEBUG: print "readingBranchDupeExists():" if channel != None: sql = """SELECT "Interval".end_time, "MeterData".meter_name, "MeterData".meter_data_id, "Reading".channel, "Reading".reading_id FROM "MeterData" INNER JOIN "IntervalReadData" ON "MeterData" .meter_data_id = "IntervalReadData".meter_data_id INNER JOIN "Interval" ON "IntervalReadData" .interval_read_data_id = "Interval".interval_read_data_id INNER JOIN "Reading" ON "Interval".interval_id = "Reading" .interval_id WHERE "Interval".end_time = '%s' and meter_name = '%s' and channel = '%s'""" % (endTime, meterName, channel) else: # deprecated query sql = """SELECT "Interval".end_time, "MeterData".meter_name, "MeterData".meter_data_id FROM "MeterData" INNER JOIN "IntervalReadData" ON "MeterData" .meter_data_id = "IntervalReadData".meter_data_id INNER JOIN "Interval" ON "IntervalReadData" .interval_read_data_id = "Interval".interval_read_data_id WHERE "Interval".end_time = '%s' and meter_name = '%s'""" % (endTime, meterName) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if len(rows) > 0: assert len( rows) < 2, "Dupes should be less than 2, found %s: %s." % ( len(rows), rows) self.currentReadingID = self.getLastElement(rows[0]) self.logger.log('Reading ID = %s.' % self.currentReadingID, 'silent') self.logger.log( "Duplicate found for meter %s, end time %s, channel %s." % (meterName, endTime, channel), 'silent') return True else: self.logger.log( "Found no rows for meter %s, end time %s, channel %s." % (meterName, endTime, channel), 'silent') return False def readingValuesAreInTheDatabase(self, conn, readingDataDict): """ Given a reading ID, verify that the values associated are present in the database. Values are from the columns: 1. channel 2. raw_value 3. uom 4. value :param dictionary containing reading values :return True if the existing values are the same, otherwise return False """ dbCursor = conn.cursor() sql = """SELECT "Reading".reading_id, "Reading".channel, "Reading".raw_value, "Reading".uom, "Reading"."value" FROM "Reading" WHERE "Reading".reading_id = %s""" % ( self.currentReadingID) self.dbUtil.executeSQL(dbCursor, sql) rows = dbCursor.fetchall() if self.currentReadingID == 0: return False # assert len(rows) == 1 or len(rows) == 0 assert len( rows) == 1, "Didn't find a matching reading for reading ID %s." %\ self.currentReadingID if len(rows) == 1: self.logger.log("Found %s existing matches." % len(rows), 'silent') allEqual = True if int(readingDataDict['Channel']) == int(rows[0][1]): print "channel equal," else: self.logger.log( "channel not equal: %s,%s,%s" % (int(readingDataDict['Channel']), int( rows[0][1]), readingDataDict['Channel'] == rows[0][1]), 'debug') allEqual = False if int(readingDataDict['RawValue']) == int(rows[0][2]): print "raw value equal," else: self.logger.log( "rawvalue not equal: %s,%s,%s" % (int(readingDataDict['RawValue']), int(rows[0][2]), readingDataDict['RawValue'] == rows[0][2]), 'debug') allEqual = False if readingDataDict['UOM'] == rows[0][3]: print "uom equal," else: self.logger.log( "uom not equal: %s,%s,%s" % (readingDataDict['UOM'], rows[0][3], readingDataDict['UOM'] == rows[0][3]), 'debug') allEqual = False if self.approximatelyEqual(float(readingDataDict['Value']), float(rows[0][4]), 0.001): self.logger.log("value equal", 'silent') else: self.logger.log( "value not equal: %s,%s,%s" % (float(readingDataDict['Value']), float( rows[0][4]), readingDataDict['Value'] == rows[0][4]), 'debug') allEqual = False if allEqual: return True else: return False else: return False def approximatelyEqual(self, a, b, tolerance): return abs(a - b) < tolerance
class MSGEgaugeNewDataChecker(object): """ Provide notification of newly loaded MSG eGauge data. This uses notification type MSG_EGAUGE_SERVICE. """ def __init__(self): """ Constructor. """ print __name__ self.logger = SEKLogger(__name__) self.connector = MSGDBConnector() self.dbUtil = MSGDBUtil() self.notifier = MSGNotifier() self.configer = MSGConfiger() def newDataCount(self): """ Measure the amount of new data that is present since the last time new data was reported. """ cursor = self.connector.conn.cursor() tableName = 'EgaugeEnergyAutoload' lastTime = self.lastReportDate('MSG_EGAUGE_SERVICE') if lastTime is None: lastTime = '1900-01-01' sql = """SELECT COUNT(*) FROM "%s" WHERE datetime > '%s'""" % ( tableName, lastTime) success = self.dbUtil.executeSQL(cursor, sql) if success: rows = cursor.fetchall() if not rows[0][0]: return 0 else: return rows[0][0] else: # @todo Raise an exception. return None def lastReportDate(self, notificationType): """ Get the last time a notification was reported. :param notificationType: A string indicating the type of the notification. It is stored in the event history. :returns: datetime of last report date. """ cursor = self.connector.conn.cursor() sql = """SELECT MAX("notificationTime") FROM "%s" WHERE "notificationType" = '%s'""" % (NOTIFICATION_HISTORY_TABLE, notificationType) success = self.dbUtil.executeSQL(cursor, sql) if success: rows = cursor.fetchall() if not rows[0][0]: return None else: return rows[0][0] else: # @todo Raise an exception. return None def saveNotificationTime(self): """ Save the notification event to the notification history. """ cursor = self.connector.conn.cursor() sql = """INSERT INTO "%s" ("notificationType", "notificationTime") VALUES ('MSG_EGAUGE_SERVICE', NOW())""" % NOTIFICATION_HISTORY_TABLE success = self.dbUtil.executeSQL(cursor, sql) self.connector.conn.commit() if not success: # @todo Raise an exception. self.logger.log( 'An error occurred while saving the notification time.') def sendNewDataNotification(self, testing=False): """ Sending notification reporting on new data being available since the last time new data was reported. :param testing: Use testing mode when True. """ lastReportDate = self.lastReportDate('MSG_EGAUGE_SERVICE') if not lastReportDate: lastReportDate = "never" msgBody = '\nNew MSG eGauge data has been loaded to %s.' % self\ .connector.dbName msgBody += '\n\n' msgBody += 'The new data count is %s readings.' % self.newDataCount() msgBody += '\n\n' msgBody += 'The last report date was %s.' % lastReportDate msgBody += '\n\n' self.notifier.sendNotificationEmail(msgBody, testing=testing) self.saveNotificationTime()
class SEKDBConnector(object): """ Make and manage a connection to a PostgreSQL database. Usage: conn = SEKDBConnector().connectDB() cursor = conn.cursor() """ def __init__(self, dbName = '', dbHost = '', dbPort = '', dbUsername = '', dbPassword = '', testing = False, logLevel = 'silent'): """ Constructor. :param testing: Boolean indicating if Testing Mode is on. When testing mode is on, a connection to the testing database will be made instead of the production database. This is useful for unit testing. :param logLevel """ self.logger = SEKLogger(__name__, logLevel) if testing: self.logger.log("Testing Mode is ON.") self.dbName = dbName self.dbHost = dbHost self.dbPort = dbPort self.dbPassword = dbPassword self.dbUsername = dbUsername self.logger.log( "Instantiating DB connector with database {}.".format(dbName)) self.conn = self.connectDB() if not self.conn: self.logger.log('DB connection not available.', 'error') sys.exit(-1) try: self.dictCur = self.conn.cursor( cursor_factory = psycopg2.extras.DictCursor) except AttributeError as error: self.logger.log('Error while getting DictCursor: {}'.format(error)) def connectDB(self): """ Make the DB connection. :returns: DB connection object if successful, otherwise None. """ # @todo Make this method private since the init makes the connection. conn = None try: conn = psycopg2.connect( "dbname='{0}' user='******' host='{2}' port='{3}' password='******'".format(self.dbName, self.dbUsername, self.dbHost, self.dbPort, self.dbPassword)) except Exception as detail: self.logger.log( "Failed to connect to the database {}: {}.".format(self.dbName, detail), 'error') sys.exit(-1) self.logger.log( "Opened DB connection to database {}.".format(self.dbName)) return conn def closeDB(self, conn): """ Close a database connection. """ self.logger.log("Closing database {}.".format(self.dbName)) conn.close() def __del__(self): """ Destructor. Close the database connection. """ self.logger.log( "Closing the DB connection to database {}.".format(self.dbName)) self.conn.close()
class MSGNOAAWeatherDataInserter(object): """ Performs weather data insertion to a database. """ def __init__(self, testing = False): """ Constructor. :param testing: True if testing mode is being used. """ self.logger = SEKLogger(__name__, 'info') self.dbUtil = MSGDBUtil() self.dupeChecker = MSGWeatherDataDupeChecker() def insertDataDict(self, conn, tableName, listOfDataDicts, commit = False): """ Given a table name and a dictionary of column names and values, insert them to the db. :param conn: A database connection. :param tableName: Name of the DB table to be inserted to. :param columnsAndValues: Dictionary of columns and values to be inserted to the DB. :param (optional) commit: A flag indicated that DB transactions will be committed. :returns: Set of datetimes processed. """ cur = conn.cursor() processedDateTimes = set() for row in listOfDataDicts: # Add a creation timestamp using the SQL function. row['created'] = 'NOW()' cols = [] vals = [] for col in row.keys(): # Prepare the columns and values for insertion via SQL. cols.append(col) if (row[col] != 'NULL'): # Surround each value with single quotes... vals.append("'%s'" % row[col]) else: # Except for NULL values. vals.append("%s" % row[col]) sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % ( tableName, ','.join(cols), ','.join(vals)) if self.dupeChecker.duplicateExists(cur, row['wban'], row['datetime'], row['record_type']): self.logger.log("Dupe found, dropping dupe.", 'info') else: processedDateTimes.add( dt.datetime.strptime(row['datetime'], "%Y-%m-%d %H:%M")) if self.dbUtil.executeSQL(cur, sql, exitOnFail = False) is False: # An error occurred. for col in sorted(row.keys()): print "%s: %s" % (col, row[col]) sys.exit(-1) if commit: try: conn.commit() except: self.logger.log("ERROR: Commit failed.", 'debug') return processedDateTimes