Connect to Dataverse API using python
Dataverse is a storage within Power Apps.
It can keep relational data for apps, reports, etc.
The backend is probably sql server and/or cosmos.
A few steps for connecting to Dataverse through python.
1. Application Registration
On Azure Portal, create an application registration, so as to have tenant id, client id, client secret.
Azure Active Directory > App Registrations > + New Registration.
The App is pretty much a delegate API for accessing the resources attached to it.
This is the same as registering an app for accessing Graph API.
Actually the same app can be reused for different APIS, e.g. Graph, Dataverse, etc.
The important thing here is adding permission for accessing Dataverse, and also configuration
From API permissions click + Add a permission, choose Dynamics CRM
The Dynamics CRM is the API used for Dataverse (rebranded, renamed). This could change in the future.
Only delegated permission is available for Dynamics CRM.
Enable 'user_impersonation' for the Dynamics CRM permission.
Also "Grant admin consent for your organisation" if not granted by default.
From Authentication click Add a platform > Mobile and desktop applications
Add the url http://localhost
This enables the MSAL library's PublicClientApplication to acquire_token_interactive().
Note this is probably only needed if using the local user's account to access dataverse.
From advanced setttings, select "Allow public client flows"
This allows authentication using public client flow, e.g. logging in interactively / multi-factor
On azure portal, go to Applications > Enterprise applications
Select the app just registered.
On the side bar, click Users and Groups, and Add user/group
Add the user account that will be used for accessing dataverse to here, so that the user can visit the app.
Again this is probably only needed if using the local user's account to access dataverse.
2. Get dataverse URL from Power Apps admin
Go to make.powerapps.com, login as admin / whoever has access. From the settings, go to Admin center.
Choose the environment, dev / test / prod / abc /...
The front page of the environment has the unique URL, something looks like:
Environment URL
org12345678.crm6.dynamics.com
3. Grant access to the registered APP
This is only needed if not using a local user account, but using the client id and secret of the registered app.
On the environment page of the Power Apps admin center, go to the "S2S apps"
Click "+ New app user", then "Add an app"
Select the business unit, which is the organization code
This enables seeing all apps under that organization code.
The app just registered above should appear in the list.
Add the app.
Select the app and "Edit security roles", add proper roles, e.g. service reader , service writer
Done
4. Connect to Dataverse using local user account
This method needs "pip install msal" for managing the authentication.
The Dataverse URL forms the 'scope' and service endpoint in below.
This method needs the user account has access to the dataverse table. The app simply passes on the delegated permission.
import requests
import msal
# Microsoft Azure AD app (client) credentials
# no client secret is needed, as it will use an user account
client_id = 'yyyy'
tenant_id = 'xxxx'
# Authority for the tenant
# the authentication page is different to those using OAuth / client secret
authority = f'https://login.microsoftonline.com/{tenant_id}/'
# Scopes required by the application (delegated permissions)
# This is important as it points to the target
scopes = ["https://org12345678.crm6.dynamics.com/.default"]
############ interactive log in ############
# this pops up a login page
# this works if the account has been enforced to use MFA, multi-factor authentication
app = msal.PublicClientApplication(
client_id,
authority=authority
)
# Initialize the login flow
result = app.acquire_token_interactive(scopes) # Only works if your app is registered with redirect_uri as http://localhost
token = result['access_token']
############ username password log in ############
# Initialize the MSAL confidential client application
# this only works if the account has not been enforced to use MFA, multi-factor authentication
app = msal.ConfidentialClientApplication(
client_id,
authority=authority
)
# Acquire token using username/password
result = app.acquire_token_by_username_password(username='xxx', password='yyy', scopes=scopes)
token = result['access_token']
##access to dynamics crm
# here it uses the customeraddress as an example.
# the dataverse entity name is not necessary the same as the table name, it can be viewed from the properties
# the API syntax to read a table needs to be checked as well, here is EntityDefinitions(LogicalName='xxx')
url = "https://org12345678.crm6.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='customeraddress')"
payload = {}
headers = {
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Content-Type': 'application/json; charset=utf-8',
'Accept': 'application/json',
'Authorization': f'Bearer {token}'
}
response = requests.request("GET", url, headers=headers, data=payload)
print(response.text)
# to query the table rows, it needs a different endpoint
# firstly get the logical collection name of the entity from the previous query
# the logical collection name is the api endpoint name
logical_collection_name = response.json()['LogicalCollectionName']
url = f"https://org12345678.crm6.dynamics.com/api/data/v9.2/{logical_collection_name }"
payload = {}
headers = {
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Content-Type': 'application/json; charset=utf-8',
'Accept': 'application/json',
'Authorization': f'Bearer {token}'
}
response = requests.request("GET", url, headers=headers, data=payload)
response.json()
# to load the json file into pandas
df = pd.DataFrame(data=response.json()['value'])
5. Connect to dataverse using client id and secret
This method doesn't need msal as above, but it does need the app to be added as a user and assigned a role within Power Apps admin.
In this way, the app already has access to the dataverse, so it doesn't need a local user account who has access.
import requests
client_id = 'xxx'
client_secret = 'yyy'
tenant_id = 'zzz'
scope = "https://org12345678.crm6.dynamics.com/.default" #the scope points to the dataverse environment
#use OAuth authentication, instead of user authentication via login.microsoftonline.com
url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
payload = f'grant_type=client_credentials&client_id={client_id}&client_secret={client_secret}&scope={scope}'
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
response = requests.post(url,
data=payload,
headers=headers)
token = response.json()['access_token']
##access to dynamics crm
url = "https://org12345678.crm6.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='customeraddress')"
payload = {}
headers = {
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Content-Type': 'application/json; charset=utf-8',
'Accept': 'application/json',
'Authorization': f'Bearer {token}'
}
response = requests.request("GET", url, headers=headers, data=payload)
print(response.text)
# to query the table rows, it needs a different endpoint
# firstly get the logical collection name of the entity from the previous query
# the logical collection name is the api endpoint name
logical_collection_name = response.json()['LogicalCollectionName']
url = f"https://org12345678.crm6.dynamics.com/api/data/v9.2/{logical_collection_name }"
payload = {}
headers = {
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Content-Type': 'application/json; charset=utf-8',
'Accept': 'application/json',
'Authorization': f'Bearer {token}'
}
response = requests.request("GET", url, headers=headers, data=payload)
response.json()
# to load the json file into pandas
df = pd.DataFrame(data=response.json()['value'])