使用Beatbox在Pandas DataFrame中解析Salesforce报表

Question

有没有人尝试过用Beatbox将SalesForce报告解析成Pandas DataFrame？在SO上有几个例子，但没有一个提供全面的解决方案，或者至少我认为它没有。

#!/usr/bin/env python3

import beatbox
import pandas as pd


sf = beatbox._tPartnerNS
service = beatbox.Client()
service.serverUrl = 'https://login.salesforce.com/services/Soap/u/38.0'
service.login('my-username', 'my-password')

report_id = '00myreport4G3V'

query = "SELECT Name FROM Report where id = '{}'".format(report_id)
query_result = service.query(query)

这只是选择名称，但理想情况下，我希望将报告内容加载到DataFrame中。有什么需要帮忙的吗？

Answer 1

我对BeatBox并不熟悉，但是使用simple-salesforce拉取csv然后将其转换为DataFrame是非常容易的。

#-*-coding:utf-8-*-
import pandas as pd
import numpy as np
from simple_salesforce import Salesforce 
import requests
###login to SF
sf = Salesforce(username='xxxxx', 
                password='xxxx', 
                security_token='', 
                organizationId='xxxxxx')
def readReport(reportid):
    with requests.session() as s:
        d = s.get("https://ap1.salesforce.com/{}?export=1&enc=UTF-8&xf=csv".format(reportid), 
                  headers=sf.headers, 
                  cookies={'sid': sf.session_id})
    import sys
    if sys.version_info[0] < 3:
        from StringIO import StringIO
    else:
        from io import StringIO
    return pd.read_csv(StringIO(d.text), sep=",")

df = readReport('your report id')

Answer 2

可以通过Salesforce Reports and Dashboards REST API检索报告数据。这适用于Salesforce‘15(版本34.0)以来的版本。

由于REST API，我用Simple-salesforce包写了一个例子。(不过，可以在不使用simple-salesforce的情况下重写它，并使用Beatbox的api会话，编写至少10行额外的代码，并且只安装requests包。)

通用代码

from collections import OrderedDict
from simple_salesforce import Salesforce
import pandas as pd
import json

class SfReportsApi(Salesforce):
    def __init__(self, *args, **kwargs):
        super(SfReportsApi, self).__init__(*args, **kwargs)

    def describe_report(self, report_id):
        return self._call_report(report_id, command='/describe')

    def to_pandas_dataframe(self, report_id, metadata=None):
        """SF report details exported to DataFrame, can be modified by metadata"""
        resp = self._call_report(report_id, metadata=metadata)
        if not resp['allData']:
            print("Detailed data have been truncated to the usual report limit (2000).")
        columns = []
        converters = []
        get_label = lambda x: x['label']
        sf_pandas_map = {
            'boolean': lambda x: x['value'],
            'currency': lambda x: x['value']['amount'],
            'date': lambda x: pd.Timestamp(x['value']),
            'datetime': lambda x: pd.Timestamp(x['value']),
            'double': lambda x: x['value'],
            'picklist': get_label,
            'string': get_label,
            'textarea': get_label,
        }
        for col in resp['reportExtendedMetadata']['detailColumnInfo'].values():
            columns.append(col['label'])
            converters.append(sf_pandas_map.get(col['dataType'], get_label))
        data = [[conv(cell) for conv, cell in zip(converters, row['dataCells'])]
                for sect_key, section in resp['factMap'].items()
                    if sect_key != 'T!T'
                        for row in section['rows']
                ]
        df = pd.DataFrame(data, columns=columns)
        return df

    def _call_report(self, report_id, metadata=None, command=None):
        url = '{}analytics/reports/{}{}'.format(self.base_url, report_id, command or '')
        data = json.dumps({'reportMetadata': metadata}) if metadata else None
        resp = self._call_salesforce('POST' if metadata else 'GET', url, data=data)
        return resp.json(object_pairs_hook=OrderedDict)

用法示例

report_id = '00O24000004qtI4EAI'
# set Salesforce session_id some way (by login or reused from other app)
sf = SfReportsApi(username='me@example.com', password='password', security_token='token')
# sf = SfReportsApi(instance_url='https://na1.salesforce.com', session_id='')
# get report metadata if useful
metadata = sf.describe_report(report_id)['reportMetadata']
# modify them or write only the modified keys, e.g. change filters or remove subtotals etc.
metadata = {
    'orderBy': ['ACCOUNT.NAME'],
    'reportFilters': [{'value': 'W', 'column': 'ACCOUNT.NAME', 'operator': greaterOrEqual'}]
}
df = sf.to_pandas_dataframe(report_id, metadata)

可以动态添加列、过滤器、排序等(有关report Execute synchronous的文档)。方法to_pandas_dataframe适用于包含详细信息的普通表格报表，并且可以选择使用一个总计，但不能超过一个级别的小计。可以从更复杂的报告中检索数据(请参阅关于Decode the Fact Map或cheatsheet的文档)，但它没有实现，因为在运行之前通过元数据参数动态删除它们更容易。

只能报告2000个详细数据行。可以使用几个带有过滤器的请求来查看所有数据。