POSTgREST API Request is success however no data is returned

[soorajiyer]

Server : nginx/1.18.0 (Ubuntu)

Description of issue

I am trying to fetch data from postgresql database using REST API called say https://api.xxxxx.dev/iot-db using a python code. A JWT token has to be generated from microsoft and pass it as bearer token. Everything is alright however the api returns [ ] . When i checked the response status code it is 200, which means that the request is success, however the api is not returning any data from the postgresql database. The entire code is below.

import requests from msal import ConfidentialClientApplication from typing import List, Dict, Any, Union

TENANT_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' CLIENT_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' CLIENT_SECRET = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
SCOPE = ['api://xxxxxxxxxxxxxxxxxxxxxxx/.default']

data_endpoint_config = { 'projects': { 'fields': 'projects?select=id,name,route,imeis,subline,ws10Icon,routeSecondLayer', 'order': '&order=name.asc', }, 'latestDeviceData': { 'fields': 'get_latest_device_data', 'order': '&order=timestamp.desc', }, 'data': { 'fields': 'data?select=id,version,imei,timestamp,network,data,project,location,latest', 'order': '&order=timestamp.desc', }, }

def get_access_token(tenant_id: str, client_id: str, client_secret: str, scope: List[str]) -> Union[str, None]: authority = f"https://login.microsoftonline.com/{tenant_id}" app = ConfidentialClientApplication( client_id, authority=authority, client_credential=client_secret, ) result = app.acquire_token_for_client(scopes=scope) if 'access_token' in result: return result['access_token'] else: print('Failed to retrieve access token:', result.get('error_description', 'Unknown error')) return None

def call_api_data(access_token: str, query: str) -> Union[Dict[str, Any], List[Dict[str, Any]]]: headers = { 'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json', } url = f"https://api.xxxxx.dev/iot-db/{query}"

print(f"Requesting URL: {url}")  # Debug: Print URL
print(f"Request Headers: {headers}")  # Debug: Print headers

try:
    response = requests.get(url, headers=headers)
    response.raise_for_status()  # Raise an error for bad status codes

    # Debug: Print response status code and full response
    print(f"Response Status Code: {response.status_code}")
    print("Response Content:", response.text)

    return response.json()
except requests.exceptions.HTTPError as http_err:
    print(f"HTTP error occurred: {http_err}")
    print("Response content:", response.text)  # Print full response content
except Exception as err:
    print(f"Other error occurred: {err}")

return []

def get_device_data(query_name: str) -> Union[List[Dict[str, Any]], Dict[str, Any]]: try: access_token = get_access_token(TENANT_ID, CLIENT_ID, CLIENT_SECRET, SCOPE) if access_token: query_config = data_endpoint_config.get(query_name, {}) query_fields = query_config.get('fields', '') query_order = query_config.get('order', '') query = f"{query_fields}{query_order}" # Combine fields and order if query: return call_api_data(access_token, query) else: print(f'No query found for query name: {query_name}') else: print('Failed to retrieve access token.') except Exception as e: print(f'Error retrieving data: {str(e)}')

return []

if name == 'main':

query_name = 'data'

device_data = get_device_data(query_name)
print('Device Data:', device_data)

Out put of the code :

Requesting URL: https://api.xxxxx.dev/iot-db/data?select=id,version,imei,timestamp,network,data,project,location,latest&order=timestamp.desc Response Status Code: 200 Response Content: [] Device Data: []

I have decoded the token and this is how the payload of the token looks like

{ "aud": "api://xxxxxxxxxxxxxxxxxxxxxxxxxxxx", "iss": "https://sts.windows.net/82360567-02b6-4a1f-a4b2-910a811b8131/", "iat": 1721720388, "nbf": 1721720377, "exp": 1721727299, "aio": "E2dgYPCykjl65cyWyabKid9FhddlAAA=", "appid": "xxxxxxxxxxxxxxxxxxxxxxxxxx", "appidacr": "1", "idp": "https://sts.windows.net/8990567-02b6-4a1f-88b2-910a811b7331/", "oid": "b4022076-068e-4f1e-8c91-56a78be78308", "rh": "0.AXMAZwU2grYCH0qkspEKgRuBMceM2KOJBzhCvTv4l66wcIRzAAA.", "sub": "b4022076-068e-4f1e-8c91-56a78be78308", "tid": "82360567-068e-4f1f-8cb2-910a811b8131", "uti": "IsPMXlG0w0WOl7PgZu9vAA", "ver": "1.0" }

I am stuck with this and i have no idea how to resolve this issue.