<commit_before># -*- coding: utf-8 -*-
import pandas as pd
import pyodbc
import datetime
import re 
import numpy as np
import codecs
import configparser as cp


def get_file_paths(path):
    """ Creates dictionary of paths to files in a specific directory where keys are the file name (before first period).
    Paths can then be passed to DataComp initialization like sqls["left"]

    Parameters
    ----------
    path : Directory path where scripts are stored 
    
    Returns
    -------
    get_file_paths : dict
        Dictionary of file paths in directory with file names (before first period) 
        as keys.
    
    Examples
    --------
    >>> sqls = "C:/mysqlfiles/"
    """   
    import os
    file_paths = {}
    file_list = os.listdir(path)
    for file in file_list:
        file_name = file.rsplit(".",1)
        file_paths[file_name[0]] = path + file
    return file_paths

## DataComp class

class DataComp(object):
    """ Container for comparison of two of data-sets a left and right, from SQL, txt or DataFrame.
    On initialization loads left data from either sql or txt. To complete comparison use add_right_data and compare_data methods.

    Parameters
    ----------
    cnxn_path : Directory path to an ini file with connection information. See cnxn_example.ini in github repo for example.
    left_cnxn_name : The connection name of the left data set in the ini file
    left_script_path: Directory path to SQL script. If None, then expected is txt file and therefore no script
        default None
    datetofrom: Two-element tuple containing date from and date to, to insert into SQL script. Will be ignored for txt source. Must be YYYY-MM-DD format.
        default ("2015-04-01","2015-04-02")
    sql_timeout: Number of seconds after which a timeout will occur and the query will be cancelled
        default 30
        
    Examples
    --------
    >>> dc1 = DataComp("C:/cnxn.ini","sales",sqls["left"],("2015-04-01","2015-05-01"))
    """

    def __init__(self, cnxn_path,left_cnxn_name,left_script_path = None,datetofrom=("2015-04-01","2015-04-02"),sql_timeout = 30,**kwargs):
        """Gets connection information from ini file and loads left data into class attribute"""
        self.cnxn_path = cnxn_path
        self.sql_timeout = sql_timeout
        self.left_cnxn_name = left_cnxn_name
        self.left_script_path = left_script_path
        self.datetofrom = datetofrom
        self.left_data, self.left_sql = self._get_data(cnxn_name = self.left_cnxn_name, script_path = self.left_script_path,**kwargs)
        self.right_cnxn_name = None
        self.right_script_path = None
        self.right_sql = None
        self.right_data = None

    def add_right_data(self,right_cnxn_name,right_script_path = None, DataFrame = None,**kwargs):
        """Add right data for comparison, unlike left data, right can be from DataFrame.
        For DataFrame set right_cnxn_name = None and set DataFrame to a named DataFrame object in scope

        Parameters
        ----------
        right_cnxn_name : The connection name of the right data set in the ini file
            set right_cnxn_name = None for DataFrame
        right_script_path: Directory path to SQL script. If None, then expected is txt file and therefore no script
            default None
        DataFrame: A pandas DataFrame, can be used if right_cnxn_name = None

        Examples
        --------
        >>> dc1.add_right_data(right_cnxn_name="sales_sql",right_script_path = sqls["sales"])
        >>> dc1.add_right_data(right_cnxn_name="sales_txt")
        >>> dc1.add_right_data(right_cnxn_name=None,DataFrame = mydf)

        """
        self.right_cnxn_name = right_cnxn_name
        self.right_script_path = right_script_path
        if right_cnxn_name is None:
            self.right_data = self._index_data(df=DataFrame)
        else:
            self.right_data, self.right_sql = self._get_data(cnxn_name = self.right_cnxn_name, script_path = self.right_script_path,**kwargs)

    def _get_data(self,cnxn_name,script_path,**kwargs):
        """Gets data from either text source or SQL source based on what is stored in ini file for specified name
        Also returns the actual sql for inspection, troubleshooting
        """
        if script_path is None:
            expected_type = 'txt'
        else: 
            expected_type = 'sql'
        cnxn_info = self._get_cnxn_info(cnxn_name = cnxn_name,expected_type = expected_type)

        if script_path is None:
            sql_script = None
            data = self._populate_dataframe_from_txt(txt_path = cnxn_info[1],**kwargs)
        else:
            sql_script = self._load_sql_script(path=script_path,datetofrom=self.datetofrom)
            data = self._populate_dataframe_from_sql(cnxn_string = cnxn_info[1],cnxn_name = cnxn_name, sql_script = sql_script)
        return data, sql_script

    def _get_cnxn_info(self,cnxn_name,expected_type):
        """Gets sql connection string or txt path from ini file"""
        parser = cp.ConfigParser()
        parser.read(self.cnxn_path)
        assert parser.has_section(cnxn_name), "The cnxn_name provided is not in the ini file specified." + self.cnxn_path
        cnxn_type = parser.get(cnxn_name,'cnxn_type')
        assert cnxn_type == expected_type, "The expected type, " + expected_type + ", is not the same as the cnxn_type, " + cnxn_type +\
            ".\nExpected type is txt if script_path is None, otherwise sql"
        cnxn_string =  parser.get(cnxn_name,'cnxn_string')
        return (cnxn_type,cnxn_string)

    def _load_sql_script(self,path,datetofrom=("2015-04-01","2015-04-02")):
        '''Load SQL and changes all date occurrence pairs to to and from in datetofrom parameter'''
        if not isinstance(path,str):
            raise TypeError('sql path must be string')
        if not isinstance(datetofrom,tuple) or not len(datetofrom) == 2:
            raise TypeError('dates to and from must be in tuple of length 2')
        for d in datetofrom:
            try:
                datetime.datetime.strptime(d,'%Y-%m-%d')
            except ValueError:
                raise ValueError("incorrect date format, should be YYYY-MM-DD")
        # SQL scripts worked with in SSMS may be saved with utf-8 BOM mark which must be replaced with empty string for pyodbc execution
        with open(path,'rb') as f:
            sql_script_bytes = f.read()
        sql_script_bytes = sql_script_bytes.replace(codecs.BOM_UTF8,b'')
        sql_script = sql_script_bytes.decode(encoding='utf-8')
        # Find all occurrences of a date in YYYY-MM-DD format
        ms = list(re.finditer('\\d{4}-\\d{2}-\\d{2}',sql_script))
        # For each pair of date occurrences, insert in first date of tuple then second
        for i, m in enumerate(ms):
            sql_script = sql_script[:m.start()] + datetofrom[i%2] + sql_script[m.end():]    
        return sql_script        

    def _populate_dataframe_from_sql(self,cnxn_string,cnxn_name,sql_script):
        '''With a valid SQL script and connection, loads SQL data into dataframe'''
        with pyodbc.connect(cnxn_string) as cnxn:
            try:
                cnxn.timeout = self.sql_timeout
                df = pd.read_sql(sql_script,cnxn)
                assert df.shape[0] > 0, "The SQL executed does not return any rows, check " + sql_script
                ## Basic assumption is left most column is primary key for comparison. This can be set later
                df = self._index_data(df)
                return df
            except TypeError:
                raise AssertionError("SQL script loaded but could not return a dataframe, check to make sure a result-set is actually returned")
    
    def _populate_dataframe_from_txt(self,txt_path,**kwargs):
        """Creates dataframe from txt file, **kwargs for read_table keyword arguments such as non-tab sep"""
        df = pd.read_table(txt_path,**kwargs)
        df = self._index_data(df)
        return df    
           
    def compare_data(self):
        """ Completely compare the loaded left and right data sets.
        Displaying both testing message and returning six item dictionary for exploration including:
            left_data: The left data loaded, but subset to include only shared columns with right
            right_data: The right data loaded, but subset to include only shared columns with left
            left_not_right_data: If any, rows in left data not found in right based on shared primary key (default is left most in each data set, unless set_key method used)
            right_not_left_data: If any, rows in right data not found in left based on shared primary key
            diff_summary: Summary level difference at a data-set/column level
            diff_values: Matched rows where at least one value is different, columns with at least one different are moved to left of PK, with different values delimited by pipe
            
        Returns
        -------
        compare_data : dict
            Dictionary of data frames described above
            <commit_msg>Changed sql_timeout default to None until further testing on ODBCs that do not accept<commit_after># -*- coding: utf-8 -*-
import pandas as pd
import pyodbc
import datetime
import re 
import numpy as np
import codecs
import configparser as cp


def get_file_paths(path):
    """ Creates dictionary of paths to files in a specific directory where keys are the file name (before first period).
    Paths can then be passed to DataComp initialization like sqls["left"]

    Parameters
    ----------
    path : Directory path where scripts are stored 
    
    Returns
    -------
    get_file_paths : dict
        Dictionary of file paths in directory with file names (before first period) 
        as keys.
    
    Examples
    --------
    >>> sqls = "C:/mysqlfiles/"
    """   
    import os
    file_paths = {}
    file_list = os.listdir(path)
    for file in file_list:
        file_name = file.rsplit(".",1)
        file_paths[file_name[0]] = path + file
    return file_paths

## DataComp class

class DataComp(object):
    """ Container for comparison of two of data-sets a left and right, from SQL, txt or DataFrame.
    On initialization loads left data from either sql or txt. To complete comparison use add_right_data and compare_data methods.

    Parameters
    ----------
    cnxn_path : Directory path to an ini file with connection information. See cnxn_example.ini in github repo for example.
    left_cnxn_name : The connection name of the left data set in the ini file
    left_script_path: Directory path to SQL script. If None, then expected is txt file and therefore no script
        default None
    datetofrom: Two-element tuple containing date from and date to, to insert into SQL script. Will be ignored for txt source. Must be YYYY-MM-DD format.
        default ("2015-04-01","2015-04-02")
    sql_timeout: Number of seconds after which a timeout will occur and the query will be cancelled
        default None
        
    Examples
    --------
    >>> dc1 = DataComp("C:/cnxn.ini","sales",sqls["left"],("2015-04-01","2015-05-01"))
    """

    def __init__(self, cnxn_path,left_cnxn_name,left_script_path = None,datetofrom=("2015-04-01","2015-04-02"),sql_timeout = None,**kwargs):
        """Gets connection information from ini file and loads left data into class attribute"""
        self.cnxn_path = cnxn_path
        self.sql_timeout = sql_timeout
        self.left_cnxn_name = left_cnxn_name
        self.left_script_path = left_script_path
        self.datetofrom = datetofrom
        self.left_data, self.left_sql = self._get_data(cnxn_name = self.left_cnxn_name, script_path = self.left_script_path,**kwargs)
        self.right_cnxn_name = None
        self.right_script_path = None
        self.right_sql = None
        self.right_data = None

    def add_right_data(self,right_cnxn_name,right_script_path = None, DataFrame = None,**kwargs):
        """Add right data for comparison, unlike left data, right can be from DataFrame.
        For DataFrame set right_cnxn_name = None and set DataFrame to a named DataFrame object in scope

        Parameters
        ----------
        right_cnxn_name : The connection name of the right data set in the ini file
            set right_cnxn_name = None for DataFrame
        right_script_path: Directory path to SQL script. If None, then expected is txt file and therefore no script
            default None
        DataFrame: A pandas DataFrame, can be used if right_cnxn_name = None

        Examples
        --------
        >>> dc1.add_right_data(right_cnxn_name="sales_sql",right_script_path = sqls["sales"])
        >>> dc1.add_right_data(right_cnxn_name="sales_txt")
        >>> dc1.add_right_data(right_cnxn_name=None,DataFrame = mydf)

        """
        self.right_cnxn_name = right_cnxn_name
        self.right_script_path = right_script_path
        if right_cnxn_name is None:
            self.right_data = self._index_data(df=DataFrame)
        else:
            self.right_data, self.right_sql = self._get_data(cnxn_name = self.right_cnxn_name, script_path = self.right_script_path,**kwargs)

    def _get_data(self,cnxn_name,script_path,**kwargs):
        """Gets data from either text source or SQL source based on what is stored in ini file for specified name
        Also returns the actual sql for inspection, troubleshooting
        """
        if script_path is None:
            expected_type = 'txt'
        else: 
            expected_type = 'sql'
        cnxn_info = self._get_cnxn_info(cnxn_name = cnxn_name,expected_type = expected_type)

        if script_path is None:
            sql_script = None
            data = self._populate_dataframe_from_txt(txt_path = cnxn_info[1],**kwargs)
        else:
            sql_script = self._load_sql_script(path=script_path,datetofrom=self.datetofrom)
            data = self._populate_dataframe_from_sql(cnxn_string = cnxn_info[1],cnxn_name = cnxn_name, sql_script = sql_script)
        return data, sql_script

    def _get_cnxn_info(self,cnxn_name,expected_type):
        """Gets sql connection string or txt path from ini file"""
        parser = cp.ConfigParser()
        parser.read(self.cnxn_path)
        assert parser.has_section(cnxn_name), "The cnxn_name provided is not in the ini file specified." + self.cnxn_path
        cnxn_type = parser.get(cnxn_name,'cnxn_type')
        assert cnxn_type == expected_type, "The expected type, " + expected_type + ", is not the same as the cnxn_type, " + cnxn_type +\
            ".\nExpected type is txt if script_path is None, otherwise sql"
        cnxn_string =  parser.get(cnxn_name,'cnxn_string')
        return (cnxn_type,cnxn_string)

    def _load_sql_script(self,path,datetofrom=("2015-04-01","2015-04-02")):
        '''Load SQL and changes all date occurrence pairs to to and from in datetofrom parameter'''
        if not isinstance(path,str):
            raise TypeError('sql path must be string')
        if not isinstance(datetofrom,tuple) or not len(datetofrom) == 2:
            raise TypeError('dates to and from must be in tuple of length 2')
        for d in datetofrom:
            try:
                datetime.datetime.strptime(d,'%Y-%m-%d')
            except ValueError:
                raise ValueError("incorrect date format, should be YYYY-MM-DD")
        # SQL scripts worked with in SSMS may be saved with utf-8 BOM mark which must be replaced with empty string for pyodbc execution
        with open(path,'rb') as f:
            sql_script_bytes = f.read()
        sql_script_bytes = sql_script_bytes.replace(codecs.BOM_UTF8,b'')
        sql_script = sql_script_bytes.decode(encoding='utf-8')
        # Find all occurrences of a date in YYYY-MM-DD format
        ms = list(re.finditer('\\d{4}-\\d{2}-\\d{2}',sql_script))
        # For each pair of date occurrences, insert in first date of tuple then second
        for i, m in enumerate(ms):
            sql_script = sql_script[:m.start()] + datetofrom[i%2] + sql_script[m.end():]    
        return sql_script        

    def _populate_dataframe_from_sql(self,cnxn_string,cnxn_name,sql_script):
        '''With a valid SQL script and connection, loads SQL data into dataframe'''
        with pyodbc.connect(cnxn_string) as cnxn:
            try:
                if self.sql_timeout is not None:
                    cnxn.timeout = self.sql_timeout
                df = pd.read_sql(sql_script,cnxn)
                assert df.shape[0] > 0, "The SQL executed does not return any rows, check " + sql_script
                ## Basic assumption is left most column is primary key for comparison. This can be set later
                df = self._index_data(df)
                return df
            except TypeError:
                raise AssertionError("SQL script loaded but could not return a dataframe, check to make sure a result-set is actually returned")
    
    def _populate_dataframe_from_txt(self,txt_path,**kwargs):
        """Creates dataframe from txt file, **kwargs for read_table keyword arguments such as non-tab sep"""
        df = pd.read_table(txt_path,**kwargs)
        df = self._index_data(df)
        return df    
           
    def compare_data(self):
        """ Completely compare the loaded left and right data sets.
        Displaying both testing message and returning six item dictionary for exploration including:
            left_data: The left data loaded, but subset to include only shared columns with right
            right_data: The right data loaded, but subset to include only shared columns with left
            left_not_right_data: If any, rows in left data not found in right based on shared primary key (default is left most in each data set, unless set_key method used)
            right_not_left_data: If any, rows in right data not found in left based on shared primary key
            diff_summary: Summary level difference at a data-set/column level
            diff_values: Matched rows where at least one value is different, columns with at least one different are moved to left of PK, with different values delimited by pipe
            
        Returns
        -------
        compare_data : dict
            Dictionary of data frames described above
            
{'fraction_non_alphanumeric': 0.08055601160181688, 'fraction_numerical': 0.009412794833907952, 'mean_word_length': 2.63878932696137, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '566773', 'n_tokens_mistral': 5422, 'n_tokens_neox': 5215, 'n_words': 1758}