Consuming Business Central APIs from Python

Consuming Business Central APIs from Python

Scenario: Reading data from Business Central APIs and read and write to SQL Server from Python. Python is an alternative way to Visual Studio (.NET, C#), PHP and others, is also available “as extension” in VS Code.

 

#1 – About Python

”Python is a programming language that lets you work quickly and integrate systems more effectively”

Python can be run on Apache (Linux) and IIS (Windows) and in Visual Studio Code… sure!

Python.org https://www.python.org/

Python for Windows Download https://www.python.org/downloads/windows/

Great Free Course @Harvard.edu https://online-learning.harvard.edu/course/cs50s-web-programming-python-and-javascript?delta=0

 

 

#2 – About consuming Dynamics NAV / Business Central Web Services and Odata 4.0

Scenario and Technology

• Consuming Web Services
• Consuming OData 4.0 Services

In the examples below the ODATAV4 are used to retrieve the data and display it on the XHTML table, trivially receiving the data in Odata format, making a “parse“, putting them in a recordset and then scrolling as if it were the result of a SELECT.

Then, when you try to edit the record and save it, the writing of the changed data is done through the WS, with the page Customers or Items or any page published by Dynamics NAV / Business Central, which is free behind the various methods of

• Read

• Write

• Delete

Web Service like this https://api.businesscentral.dynamics.com/tenant/WS/CRONUS/ and Odata / OdataV4 services like this https://api.businesscentral.dynamics.com/v1.0/tenant/ODataV4/Company(‘CRONUS IT’)/

can then be used in theory from any language, such as

• All .NET languages (Vb .NET, C #)

• Python

• Php

• Others…

• VS Code..sure.

In addition, even from a client, such as Postman or SOAP / UI, which are free and you can download.

…in this scenario: “Python Only”

 

#3 – Python with Business Central Odata 4.0 Methods

Install Python for Windows

PYTHON for WINDOWS

PYTHON SHELL

 

 

EXAMPLE 1

“Consuming Business Central Web Services from Python”

# Install REQUESTS module (if they are missing)

# LOAD Python Modules

import requests

from requests.auth import HTTPDigestAuth

from requests_ntlm import HttpNtlmAuth

from requests.auth import HTTPBasicAuth

import json

# CONNECT TO WS

uid= ‘***************+’

pwd= ‘*********************’

uid= ‘USER

pwd= ‘PASSWORD’ SERVICE KEY

url = https://api.businesscentral.dynamics.com/v1.0/tenant/WS/CRONUS%20IT/Page/CustomerCard

Customer Card API

SERVICE KEY

payload = ”’

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:sch=”urn:microsoft-dynamics-schemas/page/customercard”>

    <soapenv:Header/>

    <soapenv:Body>

       <sch:Read>

           <sch:No>RS</sch:No>

       </sch:Read>

    </soapenv:Body>

</soapenv:Envelope>

”’

# REQUESTS MODULE WITH POST METHOD

r = requests.post(url,auth=HTTPBasicAuth(uid,pwd),

    headers = {

        ‘User-agent’  : ‘Internet Explorer/2.0’,

        ‘SOAPAction’  : ‘Read’,

        ‘Content-type’: ‘text/xml’

    },

    data = payload,

)

#PRINT

print(r.headers)

print(r.text)

 

EXAMPLE 2

“Business Central and SQL Server from Python”

–Import Modules

import pypyodbc *** (ODBC MODULE)

import requests

from requests.auth import HTTPDigestAuth

from requests_ntlm import HttpNtlmAuth

from requests.auth import HTTPBasicAuth

import json

# READ API FROM PYTHON

–Authentication\url

uid= ‘*********’

pwd= ‘*’***’

url = https://api.businesscentral.dynamics.com/v1.0/tenant/WS/CRONUS%20IT/Page/CustomerCard

payload = ”’

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:sch=”urn:microsoft-dynamics-schemas/page/customercard”>

<soapenv:Header/>

<soapenv:Body>

<sch:Create>

<sch:customercard>

<sch:No>No</sch:No>

<sch:Name>Description</sch:Name>

</sch: customercard >

</sch:Create>

</soapenv:Body>

</soapenv:Envelope>

”’

#pypyodbc – ODBC Connect to Sql Server

cnxn = pypyodbc.connect(“Driver={ODBC Driver 17 for SQL Server};”

“Server=IP, PORT”

“Database=xxxxxxxxxxxxxx;”

“uid=user;pwd=password”)

cursor = cnxn.cursor()

#CONNECTION SAMPLE

cursor.execute(“SELECT TOP 10 CODCLI,RAGSOC FROM dbo.CUSYOMER_TABLE ORDER BY CODCLI “) example of reading data from SQL Server-Customer Table

while True:

row = cursor.fetchone()

if not row:

break

print(‘CODCLI:’, row[0])

dati=payload.replace(‘No’, row[0])

dati=dati.replace(‘Description’, row[1])

#Call REQUESTS.POST Method

r = requests.post(url,auth=HTTPBasicAuth(uid,pwd),

headers = {

‘User-agent’ : ‘Internet Explorer/2.0’,

‘SOAPAction’ : ‘Create’,

‘Content-type’: ‘text/xml’

},

data = dati,

)

print(r.headers)

print(r.text)

 

EXAMPLE 3

“Consuming BC SaaS OData 4.0 – Read and Print Data from Python”

# LOAD PHYTON MODULES

import requests

from requests.auth import HTTPDigestAuth

from requests_ntlm import HttpNtlmAuth

from requests.auth import HTTPBasicAuth

import json

# AUTH MODE – LOGIN\PASSWORD (USERS AND WEB SERVICES KEY)

auth=HTTPBasicAuth(username,userpassword) -> Autentication Module

username = ‘USER’ #username

userpassword = ‘PASSWORD’ # Service KEY

# SET these values to query your Dynamics 365 Business Central Company-OData (Page or Query exposed as Odata 4.0)

BCapi = ‘https://api.businesscentral.dynamics.com/v1.0/tenant/ODataV4/Company(‘”CRONUS_IT'”‘)’ #full path to web api endpoint

#web api query part(include leading /): example – Item List No, Description

BCapiquery = ‘/ItemList?$select=No,Description, Inventory’

# GET DATA (BY REQUEST METHOD)

r = requests.get(BCapi+BCapiquery, auth=HTTPBasicAuth(username,userpassword),

headers = {

‘OData-MaxVersion’: ‘4.0’,

‘OData-Version’: ‘4.0’,

‘Accept’: ‘application/json’,

‘Content-Type’: ‘application/json; charset=utf-8’,

‘Prefer’: ‘odata.maxpagesize=500’,

‘Prefer’: ‘odata.include-annotations=OData.Community.Display.V1.FormattedValue’

})

try:

#get the response (Json object)

getData = r.json()

#loop in response (No, Description)

for x in getData[‘value’]:

print (x[‘No’] + ‘ – ‘ + x[‘Description’] + ‘ – ‘ + str(x[‘Inventory’]))

except KeyError:

#handle any missing key errors

# PRINT DATA

print(‘Errors retreiving data from Business Central’)

RESULTS

EXAMPLE SOURCE https://github.com/rstefanetti/AL-Samples/blob/Python-OData/BCSaaS-ReadFromData40-Python.py

You can write\redirect output also in:

• Database
• Web & OData Services
• File
• HTML Output

 

SHOW DATA ON WINDOW GRID

For example you can show data on data grid (in this case on Tkinter Grid or others… Canvas., etc.)

# TKINTER GRID

from tkinter import *

window = Tk()

window.title(“Data Grid”)

lbl = Label(window, text=x[‘No’] + ‘ – ‘ + x[‘Description’] + ‘ – ‘ + str(x[‘Inventory’]))

lbl.grid(column=0, row=0)

window.mainloop()

# TKINTER WINDOW

The Last record is showed in this case… end of loop.

You can also print data to Web Page (Apache or IIS are needed)

 

Example of technology

Import wsgiref

from wsgiref.simple_server import make_server

def ShowApp(environ, start_response):

status = ‘200 OK’ # HTTP Status

headers = [(‘Content-type’, ‘text/plain’)] # HTTP Headers

start_response(status, headers)

# The returned object is going to be printed

return [“Bingo!”]

httpd = make_server(”, 8000, hello_world_app)

print “Serving on port 8000…”

# Serve until process is killed

httpd.serve_forever()

… and you can also SAVETOPDF, SAVETOCSV, SENDTOMAIL from Python.

 

Python for VS Code

Yes.. you can use Python (and a lot of other great estension….) also in VS Code!

 

#4 – Dynamics NAV and Business Central WS from PHP

Old posts (but nice posts to date..) about integration between Dynamics NAV (and BC sure..) and PHP.

https://blogs.msdn.microsoft.com/freddyk/2010/01/19/connecting-to-nav-web-services-from-php/

https://blogs.msdn.microsoft.com/freddyk/2016/11/06/connecting-to-nav-web-services-from-php-take-2/

 

MUST TO READ! Phyton VS Php in 2019 https://hackr.io/blog/python-vs-php-in-2019

Have a nice day guys!