Using Python to get data from s4 hana cloud by odata and use python pandas to handle data

[Jacky_Liu]

In some situation , we need to extract data from s4hc and do some data handle work like we do  in SAP ECC or S4HAN OP system with abap . We can realize this with BTP sdk on BTP with JAVA. But today I want to try with python pandas .

 

refered blog :[Consuming OData service based on ODP extractor in Python | SAP Blogs](https://blogs.sap.com/2020/11/04/consuming-odata-service-based-on-odp-extractor-in-python/)

 

Prerequisite: communication arrangement for sales order and outbound delivery has been set in s4 hana cloud .

 

used dev tool: Pycharm

Install python library in cmd:

pip install requests

pip install pyodata

pip install pandas

pip install openpyxl

python code :

# This is a sample Python script.



import requests

import pyodata

import pandas as pd

import io

import json

from datetime import datetime



def handleData():

    # Use a breakpoint in the code line below to debug your script.

    SERVICE_URL = 'https://myxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_OUTBOUND_DELIVERY_SRV;v=0002/A_OutbDeliveryItem/?$format=json'

    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})

    init_json = json.loads(response.content)

    length = len(init_json['d']['results'])

    print(length)

    # print(init_json['d']['results'])

    l_output = []

    l_record = []

    # l_record = ('DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','ReferenceSDDocument','ReferenceSDDocumentItem','ActualDeliveredQtyInBaseUnit','BaseUnit')

    l_output.append(l_record)

    i=0

    while i < length:

        l_record = (init_json['d']['results'][i]['DeliveryDocument'],init_json['d']['results'][i]['DeliveryDocumentItem'],init_json['d']['results'][i]['Plant'],init_json['d']['results'][i]['ReferenceSDDocumentCategory'],init_json['d']['results'][i]['ReferenceSDDocument'],init_json['d']['results'][i]['ReferenceSDDocumentItem'],init_json['d']['results'][i]['ActualDeliveredQtyInBaseUnit'],init_json['d']['results'][i]['BaseUnit'])

        l_output.append(l_record)

        i=i+1

    # print(l_output)



    df_dn = pd.DataFrame(l_output,columns=['DeliveryDocument','DeliveryDocumentItem','Plant','ReferenceSDDocumentCategory','SalesOrder','SalesOrderItem','ActualDeliveredQtyInBaseUnit','BaseUnit'])

    # delete leading 0 from so item column

    df_dn['SalesOrderItem'] = df_dn['SalesOrderItem'].str.lstrip('0')

    # change data type from string to float for actual delivery quantity column

    df_dn[['ActualDeliveredQtyInBaseUnit']] = df_dn[['ActualDeliveredQtyInBaseUnit']].astype('float')

    #subtotal the columnn ActualDeliveredQtyInBaseUnit by SalesOrder,SalesOrderItem,BaseUnit

    df_dn = df_dn[['SalesOrder','SalesOrderItem','BaseUnit','ActualDeliveredQtyInBaseUnit']].groupby(['SalesOrder','SalesOrderItem','BaseUnit']).agg('sum')



    # get so item data from S4HC

    SERVICE_URL = 'https://myxxxxxx-api.saps4hanacloud.cn/sap/opu/odata/sap/API_SALES_ORDER_SRV/A_SalesOrderItem/?$format=json'

    response = requests.get(SERVICE_URL,auth=('communication user', 'communication user password'), headers={"Prefer": "odata.maxpagesize=500", "Prefer": "odata.track-changes"})

    init_json = json.loads(response.content)

    length = len(init_json['d']['results'])

    i=0

    l_output = []

    l_record = []

    while i<length:

        l_record = (

        init_json['d']['results'][i]['SalesOrder'], init_json['d']['results'][i]['SalesOrderItem'],

        init_json['d']['results'][i]['SalesOrderItemText'], init_json['d']['results'][i]['Material'],

        init_json['d']['results'][i]['OrderQuantityUnit'], init_json['d']['results'][i]['ConfdDelivQtyInOrderQtyUnit'],

        init_json['d']['results'][i]['TransactionCurrency'], init_json['d']['results'][i]['NetAmount'])

        l_output.append(l_record)

        i = i + 1



    df_so = pd.DataFrame(l_output, columns=[ 'SalesOrder','SalesOrderItem','SalesOrderItemText','Material', 'OrderQuantityUnit','ConfdDelivQtyInOrderQtyUnit','TransactionCurrency','NetAmount'])

    # change data type from string to float for ConfdDelivQtyInOrderQtyUnit column

    df_so[['ConfdDelivQtyInOrderQtyUnit']] = df_so[['ConfdDelivQtyInOrderQtyUnit']].astype('float')

    df_so[['NetAmount']] = df_so[['NetAmount']].astype('float')



    # join the so item table and dn item subtotal table

    df_so=pd.merge(df_so,df_dn,how='inner',on=['SalesOrder','SalesOrderItem'])

    print(df_so)

    file_name = 'so_' + str(datetime.now().strftime('%Y_%m_%d_%H_%M_%S')) + '.csv'

    # save the result in local file

    df_so.to_csv(file_name, index=True, header=True,encoding='utf-8')





# Press the green button in the gutter to run the script.

if __name__ == '__main__':

    handleData()

The result is easy to save as different format

Run program:

 

 

Best regards!

 

Jacky Liu