Beispiel #1
0
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)
Beispiel #2
0
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)
Beispiel #3
0
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
Beispiel #5
0
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)
Beispiel #6
0
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