How to use Python 3 to get data from a SAP HANA OData endpoint

If you have SAP HANA, you can quickly build OData endpoints to back the data needed for your application.

Given that, this post shows how you can use Python 3 to get data from a SAP Hana OData endpoint.

A sample RFID labelling use case

Through SAP HANA XS Advanced, I was able to expose my database table as an OData endpoint.

Table definition: SampleProject.data::tag.TagType

COLUMN TABLE "SAMPLE_DB"."SampleProject.data::tag.TagType"(
	"id" NVARCHAR(20) NOT NULL,
	"tag_id" NVARCHAR(20),
	"label" NVARCHAR(10),
	PRIMARY KEY (
		"id"
	)
);

Service definition: SampleProject/service/dataservice.xsodata

service { 
	"SampleProject.data::tag.TagType" as "TagType";
 }

Given the table definition and service definitions, suppose that my OData service is available at https://sampleusecaseserver.jp1.hana.ondemand.com/SampleProject/service/dataservice.xsodata/TagType that enables me to perform basic CRUD on the TagType table. In addition to that, we can also use a user, with my_project_user and ABcD1234, as the credentials that has privileges to perform CRUD on the table.

After figuring out how to read RFID tags from my SparkFun RFID reader with Python 3, I was able to populate the TagType table with some tag ids from my RFID cards:

id	tag_id	label
1577437534275	041A49B920CE	can
1577437536208	041A4A6F5368	can
1577437538381	041A49AA6499	can
1577437540602	041A49A93CC2	can
1577437542248	041A49927EBB	can
1577437544069	041A49A2D520	can
1577437545970	041A498FD40C	can
1577437548111	041A4979341A	can
1577437549613	041A49B8907F	can
1577437551626	041A49721F3A	can
1577437613746	041A498D4F95	paper
1577437616957	041A49A5B446	paper
1577437618826	041A497AF1DC	paper
1577437620584	041A49A5DA28	paper
1577437622997	041A4993CD09	paper
1577437625153	041A49B27491	paper
1577437626895	041A49730226	paper
1577437628717	041A49C20297	paper
1577437630187	041A4994EB28	paper
1577437632503	041A49B58664	paper
1577437668285	041A497788A8	plastic
1577437670458	041A49B851BE	plastic
1577437672263	041A49A18076	plastic
1577437674324	041A49942BE8	plastic
1577437676305	041A4985EA38	plastic
1577437677919	041A49714066	plastic
1577437679725	041A49999B55	plastic
1577437685061	041A499317D3	plastic
1577437686851	041A498C914A	plastic
1577437689088	041A4974E4C7	plastic
1577437721370	041A49777F5F	general
1577437723543	041A497E0821	general
1577437725748	041A496E83BA	general
1577437727729	041A49A54FBD	general
1577437729790	041A49874494	general
1577437732139	041A49A558AA	general
1577437734328	041A49869948	general
1577437736357	041A49B3AC48	general
1577437738466	041A4971AA8C	general
1577437741150	041A4969EED0	general

Writing the Python 3 codes to get data from a SAP HANA OData endpoint with column matching a particular value

Now that we have a clear picture of what kind of data the table contains, we can then look at some Python 3 codes to get data from a SAP HANA OData endpoint with column matching a particular value.

Typically in order to interact with SAP HANA OData endpoint, we will construct a HTTP request to the endpoint with HTTP Basic Authentication. Luckily, we can use requests library to send an HTTP request to a HTTP Basic Authentication endpoint in Python 3 easily.

Since it is easier to deal with JSON data, we will also want to include the “Accept: application/json” HTTP header to tell our OData endpoint to return JSON data.

In order to find data with column matching a particular value, we will need to use Filter Expressions in OData URIs.

When there is a record matching our query, we should expect something like the following in the HTTP response:

{
    d: {
        results: [
        {
            __metadata: {
                type: "SampleProject.service.dataservice.TagTypeType",
                uri: "https://sampleusecaseserver.jp1.hana.ondemand.com:443/SampleProject/service/dataservice.xsodata/TagType('1577437534275')"
            },
            id: "1577437534275",
            tag_id: "041A49B920CE",
            label: "can"
        }
        ]
    }
}

Given all these points, the following Python 3 codes define a function that will return us the label for a particular tag id:

TAG_TYPE_ENDPOINT='https://sampleusecaseserver.jp1.hana.ondemand.com/SampleProject/service/dataservice.xsodata/TagType'
USERNAME='my_project_user'
PASSWORD='ABcD1234'
REQUEST_HEADERS = {
    'Accept': 'application/json'
}

def get_tag_type(tag_id=''):

    url = "{}?$filter=tag_id eq '{}'".format(TAG_TYPE_ENDPOINT, tag_id)
    response = requests.get(url=url
                            ,auth=(USERNAME,PASSWORD)
                            ,headers=REQUEST_HEADERS
                            )
    if response.status_code == 200:
        tag_type_d = response.json().get('d', {})
        tag_type_results = tag_type_d.get('results', {})
        if len(tag_type_results):
            tag_type = tag_type_results[0]
            return tag_type.get('label', 'unknown')

    return 'unknown'