修复Qlik Cloud连接401错误及数据绘图异常问题

问题现象 #

用Python脚本从Qlik Cloud条形图读取JSON数据并导出PDF时，一直返回401错误：Failed to fetch data. Status code: 401，导致无法获取数据生成空DataFrame，执行plt.bar(df['Customer'], df['Sum(Sales)'])时触发KeyError: 'Customer'。

错误输出及栈信息 #

Failed to fetch data. Status code: 401

完整错误回溯：

Traceback (most recent call last):
  File "c:\Users\Sunday\Documents\ClikModules\appv3ntpj.py", line 41, in <module>
    plt.bar(df['Customer'], df['Sum(Sales)'])
            ~~^^^^^^^^^^^^
  File "C:\Users\Sunday\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py", line 3893, in __getitem__
    indexer = self.columns.get_loc(key)
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Sunday\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\range.py", line 418, in get_loc
    raise KeyError(key)
KeyError: 'Customer'

原代码 #

import requests
import pandas as pd
import matplotlib.pyplot as plt
import base64

# Qlik Sense Configuration
qlik_base_url = "https://ioco2.eu.qlikcloud.com/"
qlik_app_id = "d0c200c8-b3bb-4157-81b7-4d18d44856a3"
qlik_table_object_id = "tNfNKkC"

# Authentication credentials (replace with your actual credentials)
qlik_username = "my username"
qlik_password = "password"

# Fetch data from Qlik Sense with authentication headers
qlik_data_url = f"{qlik_base_url}/api/v1/apps/{qlik_app_id}/tables/{qlik_table_object_id}/data"
headers = {
    "Authorization": "Basic " + base64.b64encode(f"{qlik_username}:{qlik_password}".encode()).decode()
}
response = requests.get(qlik_data_url, headers=headers)

# Check for successful response status
if response.status_code == 200:
    try:
        table_data = response.json()
    except requests.exceptions.JSONDecodeError as e:
        print(f"Error decoding JSON: {e}")
        table_data = None
else:
    print(f"Failed to fetch data. Status code: {response.status_code}")
    table_data = None

# Continue with the rest of the script...

# Convert Qlik table data to a Pandas DataFrame
df = pd.DataFrame(table_data) # So this will be empty

# Data processing or analysis (replace this with your specific requirements)
# For this example, let's just plot the data and save it as a PDF
plt.figure(figsize=(10, 6))
plt.bar(df['Customer'], df['Sum(Sales)']) # raises the error
plt.xlabel('Customer')
plt.ylabel('Sum(Sales)')
plt.title('Top Customers by Sales in Qlik Sense')

修复方案 #

核心问题：Qlik Cloud API不支持用户名+密码的Basic认证，必须用API密钥或OAuth2令牌认证。

方案1：API密钥认证（推荐） #

1. 在Qlik Cloud控制台生成API密钥：
   • 登录Qlik Cloud，进入管理 > API密钥
   • 创建新密钥，保存好生成的密钥值
2. 修改代码认证部分：

# 替换原headers代码块
qlik_api_key = "你的API密钥"
headers = {
    "Authorization": f"Bearer {qlik_api_key}"
}

方案2：OAuth2令牌认证（需用户上下文场景） #

如果需要以特定用户身份访问数据，通过OAuth2获取令牌：

# 先获取OAuth2令牌
token_url = f"{qlik_base_url}/oauth/token"
payload = {
    "grant_type": "password",
    "username": qlik_username,
    "password": qlik_password,
    "client_id": "qlik-cli" # 也可使用自定义客户端ID
}
token_response = requests.post(token_url, data=payload)
access_token = token_response.json()["access_token"]

# 用令牌请求数据
headers = {
    "Authorization": f"Bearer {access_token}"
}
response = requests.get(qlik_data_url, headers=headers)

额外防护：避免空DataFrame报错 #

在绘图前添加判断，防止空数据触发KeyError：

if df is not None and not df.empty:
    plt.figure(figsize=(10, 6))
    plt.bar(df['Customer'], df['Sum(Sales)'])
    plt.xlabel('Customer')
    plt.ylabel('Sum(Sales)')
    plt.title('Top Customers by Sales in Qlik Sense')
    plt.savefig('sales_report.pdf') # 补充保存PDF的代码
else:
    print("无数据可绘制，请检查API认证或数据对象ID是否正确")

内容的提问来源于stack exchange，提问作者Sunday David