Using SQL View for integration

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 .

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)

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 #implementation:interoperability discussion, so how about we start by moving this post to the #implementation:interoperability sub-category?

1 Like

@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

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:

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