Using the Datasources API to Execute SQL Query and Download Data into a Python Dataframe

[taras]

Using the Datasources API to Execute SQL Query and Download Data into a Python Dataframe

Summary

This article guides how to use the Datasources API to execute an SQL query and download data from a cube into a Python data frame. The issue arises when a user cannot find the API in the REST API tab and encounters difficulties in downloading the data into a Python data frame.

Main Content

Step-by-step instructions:

To use the Datasources API and execute an SQL query, you can refer to the following documentation link for more details on this endpoint usage: Sisense SQL API Documentation

To obtain a data frame from the Sisense SQL endpoint response, you typically follow these steps:

1. Make the API Call: Use a library like `requests` to send the SQL query to the Sisense API endpoint.
2. Parse the Response: Convert the API response (usually in JSON format) to a format suitable for creating a data frame.
3. Create the DataFrame: Use the `pandas` library to create a DataFrame from the parsed response.

Here’s a step-by-step example in Python:

Step 1: Import Libraries

First, ensure you have the necessary libraries installed:

pip install requests pandas

Then, import them in your Python script:

import requests
import pandas as pd

Step 2: Define the API Call

Set up the API endpoint, headers, and SQL query:

url = "https://example.com/api/datasources/laptopsales/sql"
query = "select sales.city, count(*) from sales group by sales.city"

# If authentication is needed, you can add headers. For example:
headers = {
"Content-Type": "application/json",
"Authorization": "Bearer YOUR_ACCESS_TOKEN" # replace with your actual token
}

# Send the request
response = requests.get(url, headers=headers, params={"query": query})

# Check if the request was successful
if response.status_code != 200:
raise Exception(f"Error: {response.status_code}, {response.text}")

Step 3: Parse the Response

Assume the response is in JSON format:

data = response.json()

Step 4: Convert to DataFrame

Assuming `data` contains a list of dictionaries, you can directly convert it to a DataFrame:

# Check the structure of the data
print(data)

# Convert to DataFrame
df = pd.DataFrame(data)

# Display the DataFrame
print(df)

Full Script Example

Combining all the steps into one script:

import requests
import pandas as pd

# Define the endpoint and query
url = "https://example.com/api/datasources/laptopsales/sql"
query = "select sales.city, count(*) from sales group by sales.city"

# Define headers if needed
headers = {
"Content-Type": "application/json",
"Authorization": "Bearer YOUR_ACCESS_TOKEN" # replace with your actual token
}

# Send the request
response = requests.get(url, headers=headers, params={"query": query})

# Check if the request was successful
if response.status_code != 200:
raise Exception(f"Error: {response.status_code}, {response.text}")

# Parse the response
data = response.json()

# Convert to DataFrame
df = pd.DataFrame(data)

# Display the DataFrame
print(df)

Notes:

• Authorization: Adjust the headers to include any necessary authentication tokens or credentials.
• Error Handling: Enhance error handling as per your requirements.
• Data Structure: The structure of the JSON response might vary, so adjust the DataFrame creation accordingly.

Troubleshooting tips or alternative solutions

If you encounter any issues with the above steps, ensure that:

• The URL in the Python code matches the URL of your Sisense platform.
• The SQL query is correctly formatted and valid.
• The access token in the headers is valid and has the necessary permissions to access the data.

Supplementary Information

The Datasources API allows you to interact with your data sources and execute SQL queries directly. This can be particularly useful for automating data extraction and manipulation tasks. The Python code provided in this article uses the requests library to send a GET request to the API and the pandas library to convert the response into a DataFrame for easier data manipulation.

Disclaimer: Please note that this blog post contains one possible custom workaround solution for users with similar use cases. We cannot guarantee that the custom code solution described in this post will work in every scenario or with every Sisense software version. As such, we strongly advise users to test solutions in their environment before deploying them to ensure that the solutions proffered function as desired in their environment. For the avoidance of doubt, the content of this blog post is provided to you "as-is" and without warranty of any kind, express, implied, or otherwise, including without limitation any warranty of security and or fitness for a particular purpose. The workaround solution described in this post incorporates custom coding, which is outside the Sisense product development environment and is, therefore, not covered by Sisense warranty and support services.