How to Fetch API Data into Spotfire Using Python: Sample DXP, Code, and Authentication

Keywords: Spotfire, Python Data Function, REST API, API Key, Dynamic URL, Sample Dashboard

Description

This article provides a step-by-step guide to load data from a public or private API into Spotfire using a Python data function. It includes:

• A sample DXP dashboard (attached).
• Python code for API calls with authentication (API keys, username/password).
• Instructions for dynamic URLs with Additional Options and Advanced Options.

Solution:

1. Python Data Function Script

Basic Template for Public API:

# Import libraries
import requests
import pandas as pd

# Define API endpoint (dynamic URL example)
url = "https://jsonplaceholder.typicode.com/posts" # Replace with your API

try:
 # Fetch data
 response = requests.get(url)
 response.raise_for_status() # Check for HTTP errors

 # Convert JSON to DataFrame
 data = response.json()
 df = pd.DataFrame(data)

except Exception as e:
 df = pd.DataFrame() # Return empty table on error
 print(f"Error: {e}")

# SPOTFIRE_OUTPUT
output = df

2. Add Authentication

Option 1: API Key in Headers

api_key = "YOUR_API_KEY" # Replace with your key
headers = {"Authorization": f"Bearer {api_key}"}
response = requests.get(url, headers=headers)

Option 2: Username/Password (Basic Auth)

from requests.auth import HTTPBasicAuth
response = requests.get(url, auth=HTTPBasicAuth("user", "pass"))

Option 3: Query Parameter API Key

params = {"api_key": "YOUR_API_KEY"}
response = requests.get(url, params=params)

3. Configure the Data Function in Spotfire

1. Register the Data Function:

• Go to Tools > Data Functions Properties.
• Copy-paste the Python script above.
• Define output: output (Type: Table).

1. Dynamic Inputs (Optional):

• To let users input custom URLs or API keys:
  

  # SPOTFIRE_INPUT
  custom_url = "https://api.example.com/data" # Passed from Spotfire
  api_key = Document_Property1 # Fetch from Document Property (Add as Input Variable)

• Set up Document Properties in Spotfire (File > Document Properties) to store credentials and API.

Advanced Options

1. Pagination Handling

Loop through API pages to fetch all data:

all_data = []
page = 1

while True:
 response = requests.get(f"{url}?page={page}", headers=headers)
 page_data = response.json()
 if not page_data:
  break
 all_data.extend(page_data)
 page += 1
df = pd.DataFrame(all_data)

2. Nested JSON Parsing

Use json_normalize for nested data:

from pandas import json_normalize
df = json_normalize(data, "nested_field", ["id"])

Please find the attached sample dashboard: