CODEWITHSUNDEEP
pythonrestapipowerbi
David Fitz-Gerald
David Fitz-Gerald

Reputation: 31

Problem authenticating to Power BI REST API with Python

I've created a push streaming dataset (history on) and I've managed to post data to it from a Python script using the "Push URL" which I got from the API Info tab for the dataset in question. What I also need to do is to delete the historic data so as to clear out my test data and/or be able to reset the dataset and re-populate from scratch as and when necessary.

The Push Url is of the form https://api.powerbi.com/beta/xxxxxxxx/datasets/xxxxxxxxxxxx/rows?key=xxxxxxxxxxxxxxx

The following code works fine and the data is posted;

import requests 
import pyodbc as db
import pandas as pd

API_ENDPOINT = "https://api.powerbi.com/beta/xxxxxxxx/datasets/xxxxxxxxxxxx/rows?key=xxxxxxxxxxxxxxx"

dbcon = db.connect('DRIVER={SQL Server};SERVER=tcp:fxdb.database.windows.net;DATABASE=FXDatabase;UID=xxxx;PWD=xxxx')
df = pd.read_sql("select statement etc...", dbcon)
data = df.to_dict(orient='records')

response = requests.post(API_ENDPOINT, json=data)

But adding this:

response = requests.delete(API_ENDPOINT)

gives me:

404

{
  "error":{
    "code":"","message":"No HTTP resource was found that matches the request URI 'http://api.powerbi.com/beta/...

I couldn't figure this out so I started looking into OAuth2 authentication thinking that perhaps the Auth URL is only for posting data. After registering the app at https://dev.powerbi.com/apps my code now looks like this:

import requests 
import pyodbc as db
import pandas as pd

API_ENDPOINT = "https://api.powerbi.com/beta/xxxxxxxxxxxxxx/datasets/xxxxxxxxxxxxxxx/rows"

data = {
        'grant_type': 'password',
        'scope': 'openid',
        'resource': r'https://analysis.windows.net/powerbi/api',
        'client_id': 'xxxxxxxxx',
        'username': 'xxxxxxxxx',
        'password': 'xxxxxxxx'
    }
response = requests.post('https://login.microsoftonline.com/common/oauth2/token', data=data)

access_token = response.json().get('access_token')
headers = {'Authorization': 'Bearer ' + access_token}

dbcon = db.connect('DRIVER={SQL Server};SERVER=tcp:fxdb.database.windows.net;DATABASE=FXDatabase;UID=xxxx;PWD=xxxx')
df = pd.read_sql("select statement etc...", dbcon)
data = df.to_dict(orient='records')

response = requests.post(API_ENDPOINT, json=data, headers=headers)

response = requests.delete(API_ENDPOINT, headers=headers)

The authentication works, returning status code 200. The POST returns 401 (this worked with the previous method) and the DELETE still returns 404.

Upvotes: 2

Views: 2206

Answers (1)

David Fitz-Gerald
David Fitz-Gerald

Reputation: 31

Thanks to jonrsharpe who pointed me in the right direction.

Revisiting the API documentation I discovered a call to get the table names;

GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetKey}/tables

so after authenticating I ran;

response = requests.get("https://api.powerbi.com/v1.0/myorg/datasets/xxxxxxxx/tables", headers=headers)

The content of the response told me that there was a table called "RealTimeData" inside my dataset, must be a default name because I haven't knowingly created this table.

I have now updated the endpoint to;

API_ENDPOINT = "https://api.powerbi.com/v1.0/myorg/datasets/xxxxxxxxx/tables/RealTimeData/rows"

and all works perfectly.

Thanks Jon!

Upvotes: 1

Related Questions