def testjiraapidata(): jira_data_path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRAData_07102020.csv" jira_data = DataReader.Read(jira_data_path) pkey = TSQLInterface.PrimaryKeys(jira_data, 4, findFirst=True) comp = DataComparer() # Test locally: #interface = TSQLInterface('.', 'MetricsDyetl') #interface.Insert(jira_data, 'JIRAData') #compare_data = interface.Select("SELECT * FROM JIRAData") #comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_LOCAL.xlsx', compare_data, jira_data, pKey = pkey) # Test QA: #interface = TSQLInterface('nj1qasql13', 'MetricsDyetl') #interface.Insert(jira_data, 'JIRAData') #compare_data = interface.Select("SELECT * FROM JIRAData") #comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_QA.xlsx', compare_data, jira_data, pKey = pkey) # Test UAT: #interface = TSQLInterface('nj1uatsql13', 'MetricsDyetl') #interface.Insert(jira_data, 'JIRAData') #compare_data = interface.Select("SELECT * FROM JIRAData") #comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_UAT.xlsx', compare_data, jira_data, pKey = pkey) # Test STG: interface = TSQLInterface('nj1stgsql13', 'MetricsDyetl') interface.Insert(jira_data, 'JIRAData') compare_data = interface.Select("SELECT * FROM JIRAData") comp.GenerateComparisonReport( 'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_STG.xlsx', compare_data, jira_data, pKey=pkey)
def comparetraderequests(): interface = TSQLInterface('.', 'MetricsDyetl') kwargs = {} kwargs[ 'path'] = 'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\New Dataset\\TradeRequestAllStatus_07122020.csv' kwargs['delim'] = '|' data_true = DataReader.Read(**kwargs) query = "SELECT * FROM [dbo].[RegulatoryTradeRequests] WHERE [FileDate] = '7-12-2020'" data_compare = interface.Select(query) ignoreCols = ['FileDate', 'RunDate'] pKey = TSQLInterface.PrimaryKeys(data_true, 4, ignoreCols, True) comparer = DataComparer() comparer.GenerateComparisonReport('TradeRequests_Local.xlsx', data_compare, data_true, ignoreCols, pKey)
def compare(): interface = TSQLInterface('nj1qasql13', 'MetricsDyetl') query = ['select * '] query.append("from tbl_CyberSecurity_Exceptions_GS ") query.append(" where fileDate = '2020-4-13'") query.append(" order by [Employee ID] asc, [Group] asc") query = ''.join(query) data_valid = interface.Select(query) ignoreCols = ['ID', 'fileDate', 'RunDate'] pKey = TSQLInterface.PrimaryKeys(data_valid, 4, ignoreCols=ignoreCols, findFirst=True) interface = TSQLInterface('.', 'MetricsDyetl') data_test = interface.Select(query) DataComparer.GenerateComparisonReport( 'CyberSecurityExceptionsDiff_Post.xlsx', data_test, data_valid, ignoreCols, pKey)
def GetDatasets(args, argTup, interface, waittime): """ Query server to get test data, pull valid data from local file. """ try: query = "SELECT * FROM [%s] WHERE [%s] = '%s'" % (argTup[3], argTup[4], argTup[0]) print( 'Waiting %d seconds to allow data to be pulled and transformed...' % waittime) # Keep pulling from server until data has been uploaded: Countdown(waittime) data_test = interface.Select(query) if len(data_test) == 0: raise Exception( 'No data uploaded to server after dropping to etlfolder.') except Exception as ex: print('Could not query %s::%s::%s' % (argTup[1], argTup[2], argTup[3])) print('Reason: %s' % str(ex)) input('Press enter to exit.') os._exit(0) # Pull data from test file: compareFile = args['testetlargs']['comparefile'] if 'comparefile' in args[ 'testetlargs'] else args['testetlargs']['samplefile'] data_valid = DataReader.Read(compareFile, delim=args['testetlargs']['delim']) # Compare test file data versus output etl data: print('Generating comparison report...') ignorecols = ['%s' % argTup[4], 'RunDate'] if 'ignorecols' in args['testetlargs']: ignorecols.extend(args['testetlargs']['ignorecols']) ignorecols = set([col.strip() for col in ignorecols if col.strip()]) if 'pkey' in args['testetlargs']: pkeys = args['testetlargs']['pkey'] else: print( "Finding appropriate primary key(s) to compare datasets using input file..." ) pkeys = TSQLInterface.PrimaryKeys(data_valid, 4, ignorecols, True) print("Using: {%s} as primary key(s)..." % ', '.join(pkeys)) return pkeys, ignorecols, data_test, data_valid
def genreportwitherrors(): path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_20200115.xlsx" errpath = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_20200115_Errs.xlsx" insertdata = DataReader.Read(path) testdata = DataReader.Read(errpath) interface = TSQLInterface('.', 'MetricsDyetl') interface.Insert(insertdata, 'InternalAudit_GSIBPhysicalAccessKnownExceptions') tabledata = interface.Select( "SELECT * FROM InternalAudit_GSIBPhysicalAccessKnownExceptions") comp = DataComparer() path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_ERRS.xlsx" pKey = TSQLInterface.PrimaryKeys(insertdata, findFirst=True) ignoreCols = ['FileDate', 'RunDate'] comp.GenerateComparisonReport(path, tabledata, testdata, ignoreCols, pKey=pKey)
def getpkey(): data = DataReader.Read( "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\TradeRequests_2020_0608.xlsx" ) pkey = TSQLInterface.PrimaryKeys(data, 5, findFirst=False) return pkey