You’re offline. This is a read only version of the page.
Per the documentation it says that there is a "CreatedDate" (its not labeled that but anecdotally that's what it means) in each table. When I try and query the tables via API, those columns are not included.
my question is how do I get access to those fields? Below is the code i use for reference. The code works fine with presculpted urls. I just dont know what i need to change in the url to get the system meta data.
here is a sample URL: api.businesscentral.dynamics.com/.../G_LEntries
import pyodbc
import requests
import json
import sys
import os
from mymodules import ODBC, SendEmail, MyLogging, SSAS
from dotenv import load_dotenv, find_dotenv
import datetime
import time
package_name = os.path.basename(__file__)
load_dotenv(find_dotenv())
logger = MyLogging.NewLogger(__file__, use_cd=True)
logger.info(f'Beginning package D365process.py')
timestamp = str(datetime.datetime.now().time()) # Throw away the date information
CLIENT_ID = ""
CLIENT_SECRET = ""
REDIRECT_URI = 'localhost:8080/login'
AUTHORIZE_URL = ""
ACCESS_TOKEN_URL = ""
def getToken(CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, AUTHORIZE_URL, ACCESS_TOKEN_URL):
requests.get('{}?response_type=code&client_id={}&redirect_uri={}'.format(AUTHORIZE_URL, CLIENT_ID, REDIRECT_URI))
TokenObject = requests.post(
ACCESS_TOKEN_URL,
data={
'grant_type': 'client_credentials',
'scope': 'api.businesscentral.dynamics.com/.default',
'client_id': CLIENT_ID,
'client_secret': CLIENT_SECRET
}
)
return TokenObject
def extractTokenValue(token_to_dict):
tokenValue = token_to_dict.get("access_token")
return tokenValue
def extractTokenExpiration(token_to_dict):
tokenExpiration = datetime.datetime.now() + datetime.timedelta(seconds=token_to_dict.get("expires_in"))
return tokenExpiration
def getBusinessCentralAPIData(token, url):
payload={}
headers = {'Authorization': 'Bearer ' + token}
response = requests.request("GET", url, headers=headers, data=payload)
return response
def ConvertAndFormatApiData(response):
data = json.loads(str(response.text))
data = json.dumps(data)
data = data.replace("'", "''")
return data
def sendBusinessCentralData(token, url, Obj, Grp):
response = getBusinessCentralAPIData(token, url)
resJson = response.json()
data = ConvertAndFormatApiData(response)
ODBC.RunSQL("", "INSERT INTO dbo.ApiResults2 ([Object], [Group], json) VALUES ('"+Obj+"','"+Grp+"','"+data+"');")
return resJson
if timestamp>='05:00:00'and timestamp <='19:00:00':
try:
cnxn = pyodbc.connect(driver="{ODBC Driver 17 for SQL Server}",server="",database="",uid="",pwd="")
cursor = cnxn.cursor()
storedProc = "exec GetGroupsAndCompanies"
for irow in cursor.execute(storedProc):
token_response = getToken(CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, AUTHORIZE_URL, ACCESS_TOKEN_URL)
token_to_dict = token_response.json()
token = extractTokenValue(token_to_dict)
token_expiration = extractTokenExpiration(token_to_dict)
resJson = sendBusinessCentralData(token, irow[2], str(irow[0]), str(irow[1]))
while "@odata.nextLink" in resJson:
if datetime.datetime.now() > token_expiration:
token_response = getToken(CLIENT_ID, CLIENT_SECRET, REDIRECT_URI, AUTHORIZE_URL, ACCESS_TOKEN_URL)
token_to_dict = token_response.json()
token = extractTokenValue(token_to_dict)
token_expiration = extractTokenExpiration(token_to_dict)
resJson = sendBusinessCentralData(token, resJson["@odata.nextLink"], str(irow[0]), str(irow[1]))
cnxn.commit()
cnxn.close()
logger.info('Done! No problems.\n')
except Exception as e:
logger.info(str(e) + '\n')
if 'verdetabular'not in str(e) :
to_emails = os.getenv('Error_ToEmail').split(',')
body = f'Error running package {package_name}\n \
Error message: ' + str(e)
SendEmail.SendEmail(to_emails, 'Python Error', body)
