#!/opt/app/cacheDB/python/bin/python3
"""
A script for getting data objects from Vertica
References: Vertica Python - https://github.com/uber/vertica-python
"""
import vertica_python
import logging

# Set the logging level to DEBUG
logging.basicConfig(level=logging.INFO)

conn_info = {'host': 'stg-wavert01.bodc.att.com',
             'port': 5433,
             #'user': 'ng2157',
             'user': 'STG_WEBR_OPS',
             #'password': 'password',
             'password': 'WEBR_OPS_STG',
             'database': 'STG_EDM',
             # 10 minutes timeout on queries
             'read_timeout': 600,
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False,
             'connection_timeout': 500}


conn_info_task = {'host': 'stg-wavert01.bodc.att.com',
             'port': 5433,
             #'user': 'ng2157',
             'user': 'apptasks',
             #'password': 'password',
             'password': 'tasksapp',
             'database': 'STG_EDM',
             # 10 minutes timeout on queries
             'read_timeout': 600,
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False,
             'connection_timeout': 500}

class VerticaGetter():
    """ A class to get data from Vertica"""
    TEST_QUERY="select Attuid from S08_DB.Alltasks"

    def __init__(self):
        pass

    def __str__(self):
        return " A vertica data getter for db {} on host {}".format(conn_info['database'],conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print("The row is {}".format(row))

    def get_log_parts_param(self,params=['2017-6-9','eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0]}
        logging.debug("Query params : parameter_name {} and date {}".format(params[1],params[0]))
        query = """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)
    
    def get_log_parts(self,params=['1716057041010008656']):
        """ A method which returns all records for a log part with a single recordID """
        result =[]
        #query_params = {'key':params[1],'et_log_date':params[0]}
        query_params = {'key':params[0]}
        logging.debug("Query params are: key {}".format(params[0]))
        query = """select key,parameter_name,parameter_value,'foo',source
        from stg_perf_test.wt_log_parts
        where key = :key""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':''}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern_limit(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':'%' + params[2] + '%'}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern_aggr(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':'%' + params[2] + '%'}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_logs(self,fields=['key','app_visitor_cookie','page_url','et_log_date'],date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result =[]
        query_params = {'uuid':'0000000000000000001', 'param_value':'Default'}
        logs_query = "select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value"
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(logs_query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result =[]
        query_params = {'key':'1715230983110018712', 'parameter_name':'imprId','et_log_date':'2017-06-01'}
        query = """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name""".replace('\n',' ')
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)


    def get_visitor_analysis(self,params=['2017-7-13','2017-7-15','www.att.com']):
        """ A method which returns a count of key visitor metrics filtered by the domain"""
        result =[]
        res_from_query1 = []

        query_params = {'domain_name':params[2],'start_date':params[0],'end_date':params[1]}
        logging.debug("Query params are: end date {} and start date {} for domain {}".format(params[1],params[0],params[2]))
        query1 = """select
        A.pt_log_date,
        TO_CHAR(count(*),'fm999999999.00') as total,
        ROUND(count(A.adobe_visid_high_low),2) as mcVisIdHigh,
        count(case when D.parameter_value = '0' then null else 1 end) as mcVisIdHigh,
        count(B.parameter_value) as uuid,
        count(case when instr(B.parameter_value,'-') > 0 then null else B.parameter_value end) as auth_uuid
        from wt_logs A left outer join wt_log_parts B on A.key = B.key and A.distribution_key = B.distribution_key and B.parameter_name = 'prop48' and B.pt_log_date between :start_date and :end_date
        left outer join wt_log_parts D on A.key = D.key and A.distribution_key = D.distribution_key and D.parameter_name = 'mcVisIdHigh' and D.pt_log_date between :start_date and :end_date
        where A.pt_log_date between :start_date and :end_date
        and A.domain_name = :domain_name
        group by A.pt_log_date""".replace('\n',' ')
        logging.info("The first query  is {}".format(query1))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query1,query_params)
            for row in cur.iterate():
                res_from_query1.append(row)

        return(res_from_query1)


    def get_task_details_by_id(self,params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result =[]
        query_params = {'attid':params[0]}
        query = """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid""".replace('\n',' ')
        with vertica_python.connect(**conn_info) as connection:
            logging.debug("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            logging.info("The read SQL -> {} ".format(query))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)


if __name__ == '__main__':
    vertica = VerticaGetter()
    #vertica.test_connection()
    res = vertica.get_task_details_by_id()
    #res = vertica.get_log_parts_pattern_aggr()
    for row in res:
        print(row)


a366a87bc3ab931a4326c4e61c1af7d3ad1e2072
<mask token>


class VerticaGetter:
    <mask token>
    <mask token>

    def __init__(self):
        pass

    def __str__(self):
        return ' A vertica data getter for db {} on host {}'.format(conn_info
            ['database'], conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print('Connected to {} on host{} '.format(conn_info['database'],
                conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print('The row is {}'.format(row))

    def get_log_parts_param(self, params=['2017-6-9', 'eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[0]}
        logging.debug('Query params : parameter_name {} and date {}'.format
            (params[1], params[0]))
        query = (
            """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result
    <mask token>

    def get_log_parts_pattern(self, params=['2017-6-9', 'eVar85', 'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': ''}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_limit(self, params=['2017-6-9', 'eVar85',
        'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_aggr(self, params=['2017-6-9', 'eVar85', 'pid=1']
        ):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs(self, fields=['key', 'app_visitor_cookie', 'page_url',
        'et_log_date'], date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result = []
        query_params = {'uuid': '0000000000000000001', 'param_value': 'Default'
            }
        logs_query = (
            'select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value'
            )
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(logs_query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'key': '1715230983110018712', 'parameter_name':
            'imprId', 'et_log_date': '2017-06-01'}
        query = (
            """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result
    <mask token>

    def get_task_details_by_id(self, params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'attid': params[0]}
        query = (
            """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            logging.debug('Connected to {} on host{} '.format(conn_info[
                'database'], conn_info['host']))
            logging.info('The read SQL -> {} '.format(query))
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result


<mask token>

<mask token>


class VerticaGetter:
    <mask token>
    TEST_QUERY = 'select Attuid from S08_DB.Alltasks'

    def __init__(self):
        pass

    def __str__(self):
        return ' A vertica data getter for db {} on host {}'.format(conn_info
            ['database'], conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print('Connected to {} on host{} '.format(conn_info['database'],
                conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print('The row is {}'.format(row))

    def get_log_parts_param(self, params=['2017-6-9', 'eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[0]}
        logging.debug('Query params : parameter_name {} and date {}'.format
            (params[1], params[0]))
        query = (
            """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts(self, params=['1716057041010008656']):
        """ A method which returns all records for a log part with a single recordID """
        result = []
        query_params = {'key': params[0]}
        logging.debug('Query params are: key {}'.format(params[0]))
        query = (
            """select key,parameter_name,parameter_value,'foo',source
        from stg_perf_test.wt_log_parts
        where key = :key"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern(self, params=['2017-6-9', 'eVar85', 'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': ''}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_limit(self, params=['2017-6-9', 'eVar85',
        'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_aggr(self, params=['2017-6-9', 'eVar85', 'pid=1']
        ):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs(self, fields=['key', 'app_visitor_cookie', 'page_url',
        'et_log_date'], date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result = []
        query_params = {'uuid': '0000000000000000001', 'param_value': 'Default'
            }
        logs_query = (
            'select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value'
            )
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(logs_query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'key': '1715230983110018712', 'parameter_name':
            'imprId', 'et_log_date': '2017-06-01'}
        query = (
            """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_visitor_analysis(self, params=['2017-7-13', '2017-7-15',
        'www.att.com']):
        """ A method which returns a count of key visitor metrics filtered by the domain"""
        result = []
        res_from_query1 = []
        query_params = {'domain_name': params[2], 'start_date': params[0],
            'end_date': params[1]}
        logging.debug(
            'Query params are: end date {} and start date {} for domain {}'
            .format(params[1], params[0], params[2]))
        query1 = (
            """select
        A.pt_log_date,
        TO_CHAR(count(*),'fm999999999.00') as total,
        ROUND(count(A.adobe_visid_high_low),2) as mcVisIdHigh,
        count(case when D.parameter_value = '0' then null else 1 end) as mcVisIdHigh,
        count(B.parameter_value) as uuid,
        count(case when instr(B.parameter_value,'-') > 0 then null else B.parameter_value end) as auth_uuid
        from wt_logs A left outer join wt_log_parts B on A.key = B.key and A.distribution_key = B.distribution_key and B.parameter_name = 'prop48' and B.pt_log_date between :start_date and :end_date
        left outer join wt_log_parts D on A.key = D.key and A.distribution_key = D.distribution_key and D.parameter_name = 'mcVisIdHigh' and D.pt_log_date between :start_date and :end_date
        where A.pt_log_date between :start_date and :end_date
        and A.domain_name = :domain_name
        group by A.pt_log_date"""
            .replace('\n', ' '))
        logging.info('The first query  is {}'.format(query1))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query1, query_params)
            for row in cur.iterate():
                res_from_query1.append(row)
        return res_from_query1

    def get_task_details_by_id(self, params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'attid': params[0]}
        query = (
            """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            logging.debug('Connected to {} on host{} '.format(conn_info[
                'database'], conn_info['host']))
            logging.info('The read SQL -> {} '.format(query))
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result


<mask token>

<mask token>


class VerticaGetter:
    """ A class to get data from Vertica"""
    TEST_QUERY = 'select Attuid from S08_DB.Alltasks'

    def __init__(self):
        pass

    def __str__(self):
        return ' A vertica data getter for db {} on host {}'.format(conn_info
            ['database'], conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print('Connected to {} on host{} '.format(conn_info['database'],
                conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print('The row is {}'.format(row))

    def get_log_parts_param(self, params=['2017-6-9', 'eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[0]}
        logging.debug('Query params : parameter_name {} and date {}'.format
            (params[1], params[0]))
        query = (
            """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts(self, params=['1716057041010008656']):
        """ A method which returns all records for a log part with a single recordID """
        result = []
        query_params = {'key': params[0]}
        logging.debug('Query params are: key {}'.format(params[0]))
        query = (
            """select key,parameter_name,parameter_value,'foo',source
        from stg_perf_test.wt_log_parts
        where key = :key"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern(self, params=['2017-6-9', 'eVar85', 'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': ''}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_limit(self, params=['2017-6-9', 'eVar85',
        'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_aggr(self, params=['2017-6-9', 'eVar85', 'pid=1']
        ):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs(self, fields=['key', 'app_visitor_cookie', 'page_url',
        'et_log_date'], date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result = []
        query_params = {'uuid': '0000000000000000001', 'param_value': 'Default'
            }
        logs_query = (
            'select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value'
            )
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(logs_query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'key': '1715230983110018712', 'parameter_name':
            'imprId', 'et_log_date': '2017-06-01'}
        query = (
            """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_visitor_analysis(self, params=['2017-7-13', '2017-7-15',
        'www.att.com']):
        """ A method which returns a count of key visitor metrics filtered by the domain"""
        result = []
        res_from_query1 = []
        query_params = {'domain_name': params[2], 'start_date': params[0],
            'end_date': params[1]}
        logging.debug(
            'Query params are: end date {} and start date {} for domain {}'
            .format(params[1], params[0], params[2]))
        query1 = (
            """select
        A.pt_log_date,
        TO_CHAR(count(*),'fm999999999.00') as total,
        ROUND(count(A.adobe_visid_high_low),2) as mcVisIdHigh,
        count(case when D.parameter_value = '0' then null else 1 end) as mcVisIdHigh,
        count(B.parameter_value) as uuid,
        count(case when instr(B.parameter_value,'-') > 0 then null else B.parameter_value end) as auth_uuid
        from wt_logs A left outer join wt_log_parts B on A.key = B.key and A.distribution_key = B.distribution_key and B.parameter_name = 'prop48' and B.pt_log_date between :start_date and :end_date
        left outer join wt_log_parts D on A.key = D.key and A.distribution_key = D.distribution_key and D.parameter_name = 'mcVisIdHigh' and D.pt_log_date between :start_date and :end_date
        where A.pt_log_date between :start_date and :end_date
        and A.domain_name = :domain_name
        group by A.pt_log_date"""
            .replace('\n', ' '))
        logging.info('The first query  is {}'.format(query1))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query1, query_params)
            for row in cur.iterate():
                res_from_query1.append(row)
        return res_from_query1

    def get_task_details_by_id(self, params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'attid': params[0]}
        query = (
            """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            logging.debug('Connected to {} on host{} '.format(conn_info[
                'database'], conn_info['host']))
            logging.info('The read SQL -> {} '.format(query))
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result


<mask token>

<mask token>
logging.basicConfig(level=logging.INFO)
<mask token>


class VerticaGetter:
    """ A class to get data from Vertica"""
    TEST_QUERY = 'select Attuid from S08_DB.Alltasks'

    def __init__(self):
        pass

    def __str__(self):
        return ' A vertica data getter for db {} on host {}'.format(conn_info
            ['database'], conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print('Connected to {} on host{} '.format(conn_info['database'],
                conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print('The row is {}'.format(row))

    def get_log_parts_param(self, params=['2017-6-9', 'eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[0]}
        logging.debug('Query params : parameter_name {} and date {}'.format
            (params[1], params[0]))
        query = (
            """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts(self, params=['1716057041010008656']):
        """ A method which returns all records for a log part with a single recordID """
        result = []
        query_params = {'key': params[0]}
        logging.debug('Query params are: key {}'.format(params[0]))
        query = (
            """select key,parameter_name,parameter_value,'foo',source
        from stg_perf_test.wt_log_parts
        where key = :key"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern(self, params=['2017-6-9', 'eVar85', 'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': ''}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_limit(self, params=['2017-6-9', 'eVar85',
        'pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_log_parts_pattern_aggr(self, params=['2017-6-9', 'eVar85', 'pid=1']
        ):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result = []
        query_params = {'parameter_name': params[1], 'et_log_date': params[
            0], 'parameter_value': '%' + params[2] + '%'}
        logging.debug('Query params are: key {} and date {}'.format(params[
            1], params[0]))
        query = (
            """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2"""
            .replace('\n', ' '))
        logging.debug('The log parts part is {}'.format(query))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs(self, fields=['key', 'app_visitor_cookie', 'page_url',
        'et_log_date'], date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result = []
        query_params = {'uuid': '0000000000000000001', 'param_value': 'Default'
            }
        logs_query = (
            'select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value'
            )
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(logs_query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'key': '1715230983110018712', 'parameter_name':
            'imprId', 'et_log_date': '2017-06-01'}
        query = (
            """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result

    def get_visitor_analysis(self, params=['2017-7-13', '2017-7-15',
        'www.att.com']):
        """ A method which returns a count of key visitor metrics filtered by the domain"""
        result = []
        res_from_query1 = []
        query_params = {'domain_name': params[2], 'start_date': params[0],
            'end_date': params[1]}
        logging.debug(
            'Query params are: end date {} and start date {} for domain {}'
            .format(params[1], params[0], params[2]))
        query1 = (
            """select
        A.pt_log_date,
        TO_CHAR(count(*),'fm999999999.00') as total,
        ROUND(count(A.adobe_visid_high_low),2) as mcVisIdHigh,
        count(case when D.parameter_value = '0' then null else 1 end) as mcVisIdHigh,
        count(B.parameter_value) as uuid,
        count(case when instr(B.parameter_value,'-') > 0 then null else B.parameter_value end) as auth_uuid
        from wt_logs A left outer join wt_log_parts B on A.key = B.key and A.distribution_key = B.distribution_key and B.parameter_name = 'prop48' and B.pt_log_date between :start_date and :end_date
        left outer join wt_log_parts D on A.key = D.key and A.distribution_key = D.distribution_key and D.parameter_name = 'mcVisIdHigh' and D.pt_log_date between :start_date and :end_date
        where A.pt_log_date between :start_date and :end_date
        and A.domain_name = :domain_name
        group by A.pt_log_date"""
            .replace('\n', ' '))
        logging.info('The first query  is {}'.format(query1))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query1, query_params)
            for row in cur.iterate():
                res_from_query1.append(row)
        return res_from_query1

    def get_task_details_by_id(self, params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result = []
        query_params = {'attid': params[0]}
        query = (
            """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid"""
            .replace('\n', ' '))
        with vertica_python.connect(**conn_info) as connection:
            logging.debug('Connected to {} on host{} '.format(conn_info[
                'database'], conn_info['host']))
            logging.info('The read SQL -> {} '.format(query))
            cur = connection.cursor()
            cur.execute(query, query_params)
            for row in cur.iterate():
                result.append(row)
        return result


if __name__ == '__main__':
    vertica = VerticaGetter()
    res = vertica.get_task_details_by_id()
    for row in res:
        print(row)

#!/opt/app/cacheDB/python/bin/python3
"""
A script for getting data objects from Vertica
References: Vertica Python - https://github.com/uber/vertica-python
"""
import vertica_python
import logging

# Set the logging level to DEBUG
logging.basicConfig(level=logging.INFO)

conn_info = {'host': 'stg-wavert01.bodc.att.com',
             'port': 5433,
             #'user': 'ng2157',
             'user': 'STG_WEBR_OPS',
             #'password': 'password',
             'password': 'WEBR_OPS_STG',
             'database': 'STG_EDM',
             # 10 minutes timeout on queries
             'read_timeout': 600,
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False,
             'connection_timeout': 500}


conn_info_task = {'host': 'stg-wavert01.bodc.att.com',
             'port': 5433,
             #'user': 'ng2157',
             'user': 'apptasks',
             #'password': 'password',
             'password': 'tasksapp',
             'database': 'STG_EDM',
             # 10 minutes timeout on queries
             'read_timeout': 600,
             # default throw error on invalid UTF-8 results
             'unicode_error': 'strict',
             # SSL is disabled by default
             'ssl': False,
             'connection_timeout': 500}

class VerticaGetter():
    """ A class to get data from Vertica"""
    TEST_QUERY="select Attuid from S08_DB.Alltasks"

    def __init__(self):
        pass

    def __str__(self):
        return " A vertica data getter for db {} on host {}".format(conn_info['database'],conn_info['host'])

    def test_connection(self):
        """ A test method just to check connection to Vetica"""
        with vertica_python.connect(**conn_info) as connection:
            print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(self.TEST_QUERY)
            for row in cur.iterate():
                print("The row is {}".format(row))

    def get_log_parts_param(self,params=['2017-6-9','eVar13']):
        """ A method which returns values for a parameter name for a given date"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0]}
        logging.debug("Query params : parameter_name {} and date {}".format(params[1],params[0]))
        query = """select a.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        order by b.pt_hour_id desc limit 500""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)
    
    def get_log_parts(self,params=['1716057041010008656']):
        """ A method which returns all records for a log part with a single recordID """
        result =[]
        #query_params = {'key':params[1],'et_log_date':params[0]}
        query_params = {'key':params[0]}
        logging.debug("Query params are: key {}".format(params[0]))
        query = """select key,parameter_name,parameter_value,'foo',source
        from stg_perf_test.wt_log_parts
        where key = :key""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':''}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value = :parameter_value""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern_limit(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns all records for a log part matching a value for parameter value and limts the result ordered by the date"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':'%' + params[2] + '%'}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.key,b.parameter_name,b.parameter_value,a.uuid,b.source
        from wt_logs a,wt_log_parts b
        where a.key = b.key
        and a.et_log_date = b.et_log_date
        and a.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        order by a.et_log_date limit 500""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_log_parts_pattern_aggr(self,params=['2017-6-9','eVar85','pid=1']):
        """ A method which returns aggregated records for a log part matching a value for parameter name"""
        result =[]
        query_params = {'parameter_name':params[1],'et_log_date':params[0],'parameter_value':'%' + params[2] + '%'}
        logging.debug("Query params are: key {} and date {}".format(params[1],params[0]))
        query = """select b.et_log_date,count(key),b.parameter_name,b.parameter_value,b.source
        from wt_log_parts b
        where b.et_log_date = :et_log_date
        and b.parameter_name = :parameter_name
        and b.parameter_value LIKE :parameter_value
        group by 1,3,4,5 order by 1,2""".replace('\n',' ')
        logging.debug("The log parts part is {}".format(query))
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_logs(self,fields=['key','app_visitor_cookie','page_url','et_log_date'],date='2017-06-10'):
        """ A method to query wt_logs table for a given date """
        result =[]
        query_params = {'uuid':'0000000000000000001', 'param_value':'Default'}
        logs_query = "select uuid,param_name,param_value from log_parts_backup where uuid = :uuid and param_value =:param_value"
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(logs_query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)

    def get_logs_and_parts(self):
        """ A method which joins logs and log parts table and returns a combined result"""
        result =[]
        query_params = {'key':'1715230983110018712', 'parameter_name':'imprId','et_log_date':'2017-06-01'}
        query = """select a.key,a.uuid,a.page_url,a.domain_name,a.app_visitor_cookie,a.referral_domain
        from wt_logs a, wt_log_parts b
        where a.key = b.key
        and a.et_log_date = :et_log_date
        and a.key = :key
        and b.parameter_name = :parameter_name""".replace('\n',' ')
        with vertica_python.connect(**conn_info) as connection:
            #print("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)


    def get_visitor_analysis(self,params=['2017-7-13','2017-7-15','www.att.com']):
        """ A method which returns a count of key visitor metrics filtered by the domain"""
        result =[]
        res_from_query1 = []

        query_params = {'domain_name':params[2],'start_date':params[0],'end_date':params[1]}
        logging.debug("Query params are: end date {} and start date {} for domain {}".format(params[1],params[0],params[2]))
        query1 = """select
        A.pt_log_date,
        TO_CHAR(count(*),'fm999999999.00') as total,
        ROUND(count(A.adobe_visid_high_low),2) as mcVisIdHigh,
        count(case when D.parameter_value = '0' then null else 1 end) as mcVisIdHigh,
        count(B.parameter_value) as uuid,
        count(case when instr(B.parameter_value,'-') > 0 then null else B.parameter_value end) as auth_uuid
        from wt_logs A left outer join wt_log_parts B on A.key = B.key and A.distribution_key = B.distribution_key and B.parameter_name = 'prop48' and B.pt_log_date between :start_date and :end_date
        left outer join wt_log_parts D on A.key = D.key and A.distribution_key = D.distribution_key and D.parameter_name = 'mcVisIdHigh' and D.pt_log_date between :start_date and :end_date
        where A.pt_log_date between :start_date and :end_date
        and A.domain_name = :domain_name
        group by A.pt_log_date""".replace('\n',' ')
        logging.info("The first query  is {}".format(query1))
        with vertica_python.connect(**conn_info) as connection:
            cur = connection.cursor()
            cur.execute(query1,query_params)
            for row in cur.iterate():
                res_from_query1.append(row)

        return(res_from_query1)


    def get_task_details_by_id(self,params=['ng2157']):
        """ A method which joins logs and log parts table and returns a combined result"""
        result =[]
        query_params = {'attid':params[0]}
        query = """select a.Attuid,a.Status,a.Severity,a.TaskDetails,a.Remarks,a.StartDate,a.EndDate,a.TaskFinishDate,a.InsertDate,a.InsertedBy,a.UpdateDate,a.UpdatedBy
        from s08_DB.Alltasks a
        where a.Attuid =:attid""".replace('\n',' ')
        with vertica_python.connect(**conn_info) as connection:
            logging.debug("Connected to {} on host{} ".format(conn_info['database'],conn_info['host']))
            logging.info("The read SQL -> {} ".format(query))
            cur = connection.cursor()
            cur.execute(query,query_params)
            for row in cur.iterate():
                result.append(row)
        return(result)


if __name__ == '__main__':
    vertica = VerticaGetter()
    #vertica.test_connection()
    res = vertica.get_task_details_by_id()
    #res = vertica.get_log_parts_pattern_aggr()
    for row in res:
        print(row)

