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 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 insert():
interface = TSQLInterface('nj1qasql13', 'MetricsDyetl')
data = DataReader.Read(
'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GS.SecurityExceptions.v1\\SecurityExceptionsComp.csv'
)
interface.Insert(data, 'tbl_CyberSecurity_Exceptions_GS')
out = interface.Select(
"SELECT * FROM tbl_CyberSecurity_Exceptions_GS where fileDate = '2020-04-13'"
)
return out
def comparetablecols():
interface = TSQLInterface('.', 'MetricsDyetl')
kwargs = {}
kwargs[
'path'] = 'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\New Dataset\\TradeRequestAllStatus_07102020.csv'
kwargs['delim'] = '|'
data = DataReader.Read(**kwargs)
cols = set(data.columns)
attrs = interface.GetColumnAttributes('RegulatoryTradeRequests')
attrs = set(attrs['ColumnName'])
diff = cols - attrs
with TSQLInterface('abc', 'nowhere') as f:
pass
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 comparelocal():
interface = TSQLInterface('.', 'MetricsDyetl')
query = "SELECT * FROM [dbo].[RegulatoryTradeRequests];"
data_test = interface.Select(query)
data_valid = DataReader.Read(
"C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\TradeRequests_2020_0608.xlsx"
)
ignoreCols = ['FileDate', 'RunDate']
pkey = ['User Name', 'First Name', 'Requested Date Time']
#pkey = TSQLInterface.PrimaryKeys(data_valid, 4, ignoreCols = ignoreCols, findFirst = True)
DataComparer.GenerateComparisonReport('TradeRequestDiff.xlsx',
data_test,
data_valid,
ignoreCols=ignoreCols,
pKey=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 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