pyodbc Module

Previous Lesson
Next Lesson

Running SQL Insert query in Python code

pyodbc Module Running SQL Insert query in Python code

In this example, the Power BI REST API is accessed to embed Power BI content from a specific company. The Power BI REST APIs provide service endpoints for embedding, administration, governance and user resources.

Learn more about Power BI REST API: https://learn.microsoft.com/en-us/rest/api/power-bi/

Note:

Some of the Power BI APIs refer to workspaces as groups. Any reference to groups means that you’re working with workspaces. When accessing Power BI REST API, your request and response content and data may be processed by data centers in regions other than the home region of your Power BI tenant.

First, declare the following Azure connection strings:

  • client_id = ID der Azure App Registration
  • client_secret = Value of Client secret of App Registration
  • authority_url = https://login.microsoftonline.com/<tenant-id>
  • url_datasets_as_admin = Power BI REST API 
client_id = 'a89df55c-459e-4258-45t6-17b455bc26g5'
client_secret = '5ztg~peNslHSDFGES5dFo-vNDFDGVQuG7fZBcc2r'
authority_url = 'https://login.microsoftonline.com/3900f56z-c3b8-sdf5-6742-2b2449328afa'
scope = ["https://analysis.windows.net/powerbi/api/.default"]
redirectUri = "urn:ietf:wg:oauth:2.0:oob"

url_datasets_as_admin = 'https://api.powerbi.com/v1.0/myorg/admin/datasets'

Then check access token:

# --------------------------------------------------
# check access token
#   url: power bi api url
#   return: jsonOutput
# --------------------------------------------------
def checkAccessToken(url):
    # --------------------------------------------------
    # Use MSAL to grab a token
    # --------------------------------------------------
    app = msal.ConfidentialClientApplication(
       client_id, authority=authority_url, client_credential=client_secret)
    result = app.acquire_token_for_client(scopes=scope)

    if 'access_token' in result:
        access_token = result['access_token']
        header = {'Content-Type': 'application/json',
                  'Authorization': f'Bearer {access_token}'}
        api_out = requests.get(url=url, headers=header)
        jsonOutput = api_out.json()
        return jsonOutput
    else:
        print(result.get("error"))
        print(result.get("error_description"))
        return result.get("error")

Then query the data you need via the Power BI REST API:

# --------------------------------------------------
# get datasets as admin
# --------------------------------------------------
def getDatasetsAsAdmin():
    jsonOutput = checkAccessToken(url_datasets_as_admin)
    df = pd.DataFrame(jsonOutput['value'])
    df = df.fillna(0)
    print(df)

    # renamed name column, because it is a reserved word in row
    df.rename(columns={'name': 'datasetName'}, inplace=True)

    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server +
                          ';DATABASE='+database+';UID='+username+';PWD=' + password)
    cursor = cnxn.cursor()

    # Insert Dataframe into SQL Server:
    for index, row in df.iterrows():
        cursor.execute("INSERT INTO DatasetsAdmin (id,name,addRowsAPIEnabled,configuredBy,createdDate,isRefreshable) values(?, ?, ?, ?, ?, ?)",
                       row.id, row.datasetName, row.addRowsAPIEnabled, row.configuredBy, row.createdDate, row.isRefreshable)

    cnxn.commit()
    cursor.close()

Previous Lesson
Back to Tutorial
Next Lesson