Using SQL View for integration

Implementation - Implémentation Interoperability
1

Is it possible to extract aggregate data element data values (with all category combination) using SQL and then using API to extract from executed SQL query to Excel using r, python or powerB1.

Regards
Shepherd

@Gassim out of interest .

1 Like
2

Interesting question! Yes, you can create the SQL View and then using curl it’s possible to get the SQL View, please check the API documentation.

Here’s an simple and basic Python request which downloads the query as an xls file. (Note that this is insecure but it’s the play instance:)

import requests

url = "https://play.dhis2.org/2.39.0.1/api/sqlViews/eywt9oKe9pp/data.xls"

file = requests.get(url, allow_redirects=True) (see corrected code below)

username = "admin"
password = "district"

response = requests.request("GET", url, auth=(username,password))

print(response)

open('SQLViewresult.xls','wb').write(file.content)

This gets us to Interoperability discussion, so how about we start by moving this post to the Interoperability sub-category?

2 Likes
3

@Gassim thank you . Very interesting . In this case , have you tried to view the output on the excel file . Cant able to view the results

4

You’re welcome!

You’re right! It seems like we’re being redirected to login when the request is being sent either we are not using the authentication properly or we’re missing some headers; however, if you download directly from the SQL View, you will be able to open it:

image
image2187Ă—770 37.6 KB

The request needs more testing and probably the issue is with the authentication.

5

I wanted also to be able to download using API in excel format

1 Like
6

Hi @Shapr0019 & @Gassim ,

I have made changes to the script you have provided.

import requests

url = "https://play.dhis2.org/2.39.0.1/api/sqlViews/jaChwoXyY1N/data.xls"

username = "admin"
password = "district"

response = requests.request("GET", url, auth=(username,password))
open('SQLViewresult.xls','wb').write(response.content)

This should work.

Thanks,
Rithvik

3 Likes
7

Thanks @rithvik! :grin:

Was print(response) causing it to not work? :thinking:

Hmm… I see, probably the “allow redirects” part!

8

Hi @Gassim ,

If you see the old code carefully, it’s making two requests.
1-> file = requests.get()
2-> response = requests.request

In the 1st request, auth information is not passed → so it’s redirected to login screen and that would be present in file variable.

The 2nd request is the correct one but in the last line, it’s generating the excel based on content in file variable (which has login screen content). So, removed the 1st request and replaced file.content with response.content.

This may sound a bit confusing.

1 Like
9

:heart_eyes: Yes!! Thanks! I am happy you noticed. I also didn’t have time to test the code so thank you for the correction. :+1::+1: