airflow/contrib/hooks/salesforce_hook.py
def write_object_to_file(self,
                             query_results,
                             filename,
                             fmt="csv",
                             coerce_to_timestamp=False,
                             record_time_added=False):
        """
        Write query results to file.

        Acceptable formats are:
            - csv:
                comma-separated-values file. This is the default format.
            - json:
                JSON array. Each element in the array is a different row.
            - ndjson:
                JSON array but each element is new-line delimited instead of comma delimited like in `json`

        This requires a significant amount of cleanup.
        Pandas doesn't handle output to CSV and json in a uniform way.
        This is especially painful for datetime types.
        Pandas wants to write them as strings in CSV, but as millisecond Unix timestamps.

        By default, this function will try and leave all values as they are represented in Salesforce.
        You use the `coerce_to_timestamp` flag to force all datetimes to become Unix timestamps (UTC).
        This is can be greatly beneficial as it will make all of your datetime fields look the same,
        and makes it easier to work with in other database environments

        :param query_results: the results from a SQL query
        :type query_results: list of dict
        :param filename: the name of the file where the data should be dumped to
        :type filename: str
        :param fmt: the format you want the output in. Default:  'csv'
        :type fmt: str
        :param coerce_to_timestamp: True if you want all datetime fields to be converted into Unix timestamps.
            False if you want them to be left in the same format as they were in Salesforce.
            Leaving the value as False will result in datetimes being strings. Default: False
        :type coerce_to_timestamp: bool
        :param record_time_added: True if you want to add a Unix timestamp field
            to the resulting data that marks when the data was fetched from Salesforce. Default: False
        :type record_time_added: bool
        :return: the dataframe that gets written to the file.
        :rtype: pd.Dataframe
        """
        fmt = fmt.lower()
        if fmt not in ['csv', 'json', 'ndjson']:
            raise ValueError("Format value is not recognized: {}".format(fmt))

        # this line right here will convert all integers to floats
        # if there are any None/np.nan values in the column
        # that's because None/np.nan cannot exist in an integer column
        # we should write all of our timestamps as FLOATS in our final schema
        df = pd.DataFrame.from_records(query_results, exclude=["attributes"])

        df.columns = [column.lower() for column in df.columns]

        # convert columns with datetime strings to datetimes
        # not all strings will be datetimes, so we ignore any errors that occur
        # we get the object's definition at this point and only consider
        # features that are DATE or DATETIME
        if coerce_to_timestamp and df.shape[0] > 0:
            # get the object name out of the query results
            # it's stored in the "attributes" dictionary
            # for each returned record
            object_name = query_results[0]['attributes']['type']

            self.log.info("Coercing timestamps for: %s", object_name)

            schema = self.describe_object(object_name)

            # possible columns that can be converted to timestamps
            # are the ones that are either date or datetime types
            # strings are too general and we risk unintentional conversion
            possible_timestamp_cols = [
                field['name'].lower()
                for field in schema['fields']
                if field['type'] in ["date", "datetime"] and field['name'].lower() in df.columns
            ]
            df[possible_timestamp_cols] = df[possible_timestamp_cols].apply(self._to_timestamp)

        if record_time_added:
            fetched_time = time.time()
            df["time_fetched_from_salesforce"] = fetched_time

        # write the CSV or JSON file depending on the option
        # NOTE:
        #   datetimes here are an issue.
        #   There is no good way to manage the difference
        #   for to_json, the options are an epoch or a ISO string
        #   but for to_csv, it will be a string output by datetime
        #   For JSON we decided to output the epoch timestamp in seconds
        #   (as is fairly standard for JavaScript)
        #   And for csv, we do a string
        if fmt == "csv":
            # there are also a ton of newline objects that mess up our ability to write to csv
            # we remove these newlines so that the output is a valid CSV format
            self.log.info("Cleaning data and writing to CSV")
            possible_strings = df.columns[df.dtypes == "object"]
            df[possible_strings] = df[possible_strings].apply(
                lambda x: x.str.replace("\r\n", "").str.replace("\n", "")
            )
            # write the dataframe
            df.to_csv(filename, index=False)
        elif fmt == "json":
            df.to_json(filename, "records", date_unit="s")
        elif fmt == "ndjson":
            df.to_json(filename, "records", lines=True, date_unit="s")

        return df
def write_object_to_file(self,
                             query_results,
                             filename,
                             fmt="csv",
                             coerce_to_timestamp=False,
                             record_time_added=False):
        fmt = fmt.lower()
        if fmt not in ['csv', 'json', 'ndjson']:
            raise ValueError("Format value is not recognized: {}".format(fmt))

        # this line right here will convert all integers to floats
        # if there are any None/np.nan values in the column
        # that's because None/np.nan cannot exist in an integer column
        # we should write all of our timestamps as FLOATS in our final schema
        df = pd.DataFrame.from_records(query_results, exclude=["attributes"])

        df.columns = [column.lower() for column in df.columns]

        # convert columns with datetime strings to datetimes
        # not all strings will be datetimes, so we ignore any errors that occur
        # we get the object's definition at this point and only consider
        # features that are DATE or DATETIME
        if coerce_to_timestamp and df.shape[0] > 0:
            # get the object name out of the query results
            # it's stored in the "attributes" dictionary
            # for each returned record
            object_name = query_results[0]['attributes']['type']

            self.log.info("Coercing timestamps for: %s", object_name)

            schema = self.describe_object(object_name)

            # possible columns that can be converted to timestamps
            # are the ones that are either date or datetime types
            # strings are too general and we risk unintentional conversion
            possible_timestamp_cols = [
                field['name'].lower()
                for field in schema['fields']
                if field['type'] in ["date", "datetime"] and field['name'].lower() in df.columns
            ]
            df[possible_timestamp_cols] = df[possible_timestamp_cols].apply(self._to_timestamp)

        if record_time_added:
            fetched_time = time.time()
            df["time_fetched_from_salesforce"] = fetched_time

        # write the CSV or JSON file depending on the option
        # NOTE:
        #   datetimes here are an issue.
        #   There is no good way to manage the difference
        #   for to_json, the options are an epoch or a ISO string
        #   but for to_csv, it will be a string output by datetime
        #   For JSON we decided to output the epoch timestamp in seconds
        #   (as is fairly standard for JavaScript)
        #   And for csv, we do a string
        if fmt == "csv":
            # there are also a ton of newline objects that mess up our ability to write to csv
            # we remove these newlines so that the output is a valid CSV format
            self.log.info("Cleaning data and writing to CSV")
            possible_strings = df.columns[df.dtypes == "object"]
            df[possible_strings] = df[possible_strings].apply(
                lambda x: x.str.replace("\r\n", "").str.replace("\n", "")
            )
            # write the dataframe
            df.to_csv(filename, index=False)
        elif fmt == "json":
            df.to_json(filename, "records", date_unit="s")
        elif fmt == "ndjson":
            df.to_json(filename, "records", lines=True, date_unit="s")

        return df
def write_object_to_file ( self , query_results , filename , fmt = "csv" , coerce_to_timestamp = False , record_time_added = False ) : fmt = fmt . lower ( ) if fmt not in [ 'csv' , 'json' , 'ndjson' ] : raise ValueError ( "Format value is not recognized: {}" . format ( fmt ) ) # this line right here will convert all integers to floats # if there are any None/np.nan values in the column # that's because None/np.nan cannot exist in an integer column # we should write all of our timestamps as FLOATS in our final schema df = pd . DataFrame . from_records ( query_results , exclude = [ "attributes" ] ) df . columns = [ column . lower ( ) for column in df . columns ] # convert columns with datetime strings to datetimes # not all strings will be datetimes, so we ignore any errors that occur # we get the object's definition at this point and only consider # features that are DATE or DATETIME if coerce_to_timestamp and df . shape [ 0 ] > 0 : # get the object name out of the query results # it's stored in the "attributes" dictionary # for each returned record object_name = query_results [ 0 ] [ 'attributes' ] [ 'type' ] self . log . info ( "Coercing timestamps for: %s" , object_name ) schema = self . describe_object ( object_name ) # possible columns that can be converted to timestamps # are the ones that are either date or datetime types # strings are too general and we risk unintentional conversion possible_timestamp_cols = [ field [ 'name' ] . lower ( ) for field in schema [ 'fields' ] if field [ 'type' ] in [ "date" , "datetime" ] and field [ 'name' ] . lower ( ) in df . columns ] df [ possible_timestamp_cols ] = df [ possible_timestamp_cols ] . apply ( self . _to_timestamp ) if record_time_added : fetched_time = time . time ( ) df [ "time_fetched_from_salesforce" ] = fetched_time # write the CSV or JSON file depending on the option # NOTE: #   datetimes here are an issue. #   There is no good way to manage the difference #   for to_json, the options are an epoch or a ISO string #   but for to_csv, it will be a string output by datetime #   For JSON we decided to output the epoch timestamp in seconds #   (as is fairly standard for JavaScript) #   And for csv, we do a string if fmt == "csv" : # there are also a ton of newline objects that mess up our ability to write to csv # we remove these newlines so that the output is a valid CSV format self . log . info ( "Cleaning data and writing to CSV" ) possible_strings = df . columns [ df . dtypes == "object" ] df [ possible_strings ] = df [ possible_strings ] . apply ( lambda x : x . str . replace ( "\r\n" , "" ) . str . replace ( "\n" , "" ) ) # write the dataframe df . to_csv ( filename , index = False ) elif fmt == "json" : df . to_json ( filename , "records" , date_unit = "s" ) elif fmt == "ndjson" : df . to_json ( filename , "records" , lines = True , date_unit = "s" ) return df
Write query results to file.

        Acceptable formats are:
            - csv:
                comma-separated-values file. This is the default format.
            - json:
                JSON array. Each element in the array is a different row.
            - ndjson:
                JSON array but each element is new-line delimited instead of comma delimited like in `json`

        This requires a significant amount of cleanup.
        Pandas doesn't handle output to CSV and json in a uniform way.
        This is especially painful for datetime types.
        Pandas wants to write them as strings in CSV, but as millisecond Unix timestamps.

        By default, this function will try and leave all values as they are represented in Salesforce.
        You use the `coerce_to_timestamp` flag to force all datetimes to become Unix timestamps (UTC).
        This is can be greatly beneficial as it will make all of your datetime fields look the same,
        and makes it easier to work with in other database environments

        :param query_results: the results from a SQL query
        :type query_results: list of dict
        :param filename: the name of the file where the data should be dumped to
        :type filename: str
        :param fmt: the format you want the output in. Default:  'csv'
        :type fmt: str
        :param coerce_to_timestamp: True if you want all datetime fields to be converted into Unix timestamps.
            False if you want them to be left in the same format as they were in Salesforce.
            Leaving the value as False will result in datetimes being strings. Default: False
        :type coerce_to_timestamp: bool
        :param record_time_added: True if you want to add a Unix timestamp field
            to the resulting data that marks when the data was fetched from Salesforce. Default: False
        :type record_time_added: bool
        :return: the dataframe that gets written to the file.
        :rtype: pd.Dataframe
Write query results to file .