Consuming OData service based on ODP extractor in Python

This blog is next step of Exposing SAP BW Extractors via ODP as an OData Service.

Before we begin, a quick summary of what was discussed in the previous blog:

In order to read SAP Extractor data from third-party systems without SAP BW system, following steps were performed,

1. Activated the Extractor in SAP system
2. Ensure ODP is enabled for extractor
3. Created OData service on top of ODP extractor

With OData service extractor data is exposed and available for consumption by any external application.

Consuming OData Service in Python

OData Service details:

With below code a call to OData service is made using python. The JSON response is parsed and the data is dumped into csv file.

import requests
import pyodata
import pandas as pd
import io
import json
from datetime import datetime

# Assign absolute URL to variable SERVICE_URL and call the get request with authentication details with header Prefer: odata.maxpagesize=500,odata.track-changes
# Header Prefer: odata.track-changes only for delta capable extractors

SERVICE_URL = 'http://<server>:<port>/sap/opu/odata/sap/ZBW_ODP_ODATA_SRV/EntityOf0UCINSTALLA_ATTR_2/?$format=json'
response = requests.get(SERVICE_URL,auth = (<user>, <password>), headers = {"Prefer": "odata.maxpagesize=500","Prefer": "odata.track-changes"})

# load JSON response Python dictionary object 
init_json = json.loads(response.content)


# Detrmine the length of Python dictionary 
length = len(init_json['d']['results'])


# Declare two list l_record to capture indivisual record & l_output to capture complete output.
l_output = []
l_record = []

# Create & append header row
l_record = ('MANDT','ANLAGE','SPARTE','VSTELLE','ABLSPERR','BAPERTYP','ANSCHREI','SPEBENE','DRCKSTUF','ANLART','BEZUG','ABLESARTST','NODISCONCT','SERVICE','DEREGSTAT','INFOREL','ETIMEZONE','OUCONT','ERDAT','ERNAM','AEDAT','AENAM','BEGRU','LOEVM','UPDMOD','ODQ_CHANGEMODE','ODQ_ENTITYCNTR')
l_output.append(l_record)


# loop through dictionary to read each record and append it to output list l_output
i = 0
while i < length:
    l_record = (init_json['d']['results'][i]['MANDT'], init_json['d']['results'][i]['ANLAGE'], init_json['d']['results'][i]['SPARTE'], init_json['d']['results'][i]['VSTELLE'], init_json['d']['results'][i]['ABLSPERR'], init_json['d']['results'][i]['BAPERTYP'], init_json['d']['results'][i]['ANSCHREI'], init_json['d']['results'][i]['SPEBENE'], init_json['d']['results'][i]['DRCKSTUF'], init_json['d']['results'][i]['ANLART'], init_json['d']['results'][i]['BEZUG'], init_json['d']['results'][i]['ABLESARTST'], init_json['d']['results'][i]['NODISCONCT'], init_json['d']['results'][i]['SERVICE'], init_json['d']['results'][i]['DEREGSTAT'], init_json['d']['results'][i]['INFOREL'], init_json['d']['results'][i]['ETIMEZONE'], init_json['d']['results'][i]['OUCONT'], init_json['d']['results'][i]['ERDAT'], init_json['d']['results'][i]['ERNAM'], init_json['d']['results'][i]['AEDAT'], init_json['d']['results'][i]['AENAM'], init_json['d']['results'][i]['BEGRU'], init_json['d']['results'][i]['LOEVM'], init_json['d']['results'][i]['UPDMOD'], init_json['d']['results'][i]['ODQ_CHANGEMODE'], init_json['d']['results'][i]['ODQ_ENTITYCNTR'])
    l_output.append(l_record)
    i += 1

# Printing delta URL for reference  
print(init_json['d']['__delta'])

# Create a dynamic file name: result_YYYY_MM_DD_HH_MM_SS.csv
file_name = 'result_' + str(datetime.now().strftime('%Y_%m_%d_%H_%M_%S')) + '.csv'

# Copy data from output list l_output into data frame df_file and write to file 
df_file = pd.DataFrame(l_output)
df_file.to_csv(file_name, index = False, header = False)

The above code inserts data as full load into csv file. Same code can be enhanced to write the parsed data from OData service into any external cloud or on-premise application tables after creating a connection to external system.

In case, if the extractor is delta capable the delta token from the response can be captured and passed to request subsequent delta data at regular interval.

Delta URL would look like below

http://<server>:<port>/sap/opu/odata/sap/ZBW_ODP_ODATA_SRV/EntityOf0UCINSTALLA_ATTR_2/?$format=json&!deltatoken='D20201104135616_000044000'

Finally to conclude, in this scenario I have used python code to read OData service and write into csv file as I have basic knowledge in Python. Same scenario can be implemented in JavaScript or any other program language that can read OData service.

Hope this blog provides basic understanding on how to consume OData service and the same block of code can be enhanced to build more advanced scenarios.