Wanted: Microsoft Dynamics data in Python scripts

I believe most Python developers think of Microsoft software as a necessary evil. When it comes to Microsoft Dynamics, the reason that will make you want to connect from Python is the wealth of information that companies have in Dynamics.

Dynamics in itself is huge (from the old CRM that is now called Dynamics 365 Customer Engagement, to the ERP side of it that is now called Dynamics 365 for Finance, and that's just the tip of the iceberg).

In short, there is plenty of exciting stuff in Dynamics that you can use for data analysis or data visualization projects that take place in big organizations (companies, universities or others). So how do you get your hands on it using a Python script? It is not that complicated.

Tech stack

We will look into how to access data of Microsoft Dynamics (Dynamics CRM 2011, 2013, 2015, 2016, Dynamics 365 Customer Engagement, including Dynamics 365 Sales, Dynamics 365 Customer Service and Dynamics 365 Field Service) from within a Python language script (we will be using version 3.7). We will make the data transfers via the ODBC layer, using the pyodbc module ver. 4.0.26.

It is worth mentioning that my script uses Connect Bridge, a commercial product that makes the Python / Dynamics integration possible. Connect Bridge allowed me to do this integration with not that much code and note that much effort. Best of all, I did it in a way that did not mess up the Dynamics side (trust me, this is VERY important).

You can try all this yourself by getting a free trial for Connect Bridge.
Connect Bridge is an integration platform you can get from Connecting Software. It enables you to connect any software through ODBC drivers, JDBC drivers, or Web Services. The architecture of the platform is client-server, and you can see it in this diagram.

As you can see in the diagram, you can use this tool to access not only Microsoft Dynamics data but also data from many other programs. These include Microsoft SharePoint and Microsoft Exchange, and also non-Microsoft software like Salesforce. Access to data can be bidirectional (read and write).

The Dynamics in Python script

Now it's finally time to start our script! We will be creating a Python script that gets data from Dynamics in the simplest possible way. Hopefully this can be a good starting point for your own project in this domain.

Let's assume the Dynamics instance already exists. You will need your Dynamics login credentials, so have them at hand.

Here is the step-by-step procedure you need to follow:

1. Request a free trial and then install Connect Bridge
2. Install Python for Windows version 3.7+ along with pyodbc module 4.0.26+
3. Run Connect Bridge Management Studio and in it:
    
   3.1. Using the credentials I mentioned earlier, add an account for Dynamics in Connect Bridge (Accounts – Add account). You need to fill the ServerUrl property with the organization service address. You get this address in the Dynamics user interface by going to Settings > Customizations > Developer Resources > Service Endpoints > Organization Service. Test the connection before proceeding.
    
   3.2. Open the option New Query and then the Connection Browser. Look for the Dynamics connector and expand it until you see DefaultConnection. Right-click it and select the option Get Connection string. Copy the ODBC connection string as you will pass it to the script further on.
    
   3.3. Start by running the stored procedure SP_UPDATE_SCHEMA. This will make the number of visible tables you can test out on your queries increase significantly. Simply choose Query > New Query and then run EXEC SP_UPDATE_SCHEMA;
    
   3.4 Use again the New Query option to test out a query that will obtain the data you need in Dynamics. I will present an example query here, but this is something you should change. Once you have clicked the New Query option, open the Connection Browser. Find the Dynamics connector and open it until you see the Tables option. We can see that the schema contains a "table" called account. We can create our query as SELECT accountid, accountnumber, name, emailaddress1 FROM account WHERE emailaddress1 IS NOT NULL LIMIT 20;
   This query will select twenty entries from Dynamics's accounts that have an e-mail address. Please note that it might look like we are using a database directly, but that is not what is happening. Connect Bridge is going through the API and then presenting the data as if it was a database. Once you have finished writing your query, copy it, as you will also need it to pass it on to the script.

Let the scripting start

The whole solution is in one script file CBQuery.py. You can go through the complete source code below. If you focus on lines 70 to 92, you will see the core of the solution. A full description of how this script works is below.

#!/usr/local/bin/python3.7
# encoding: utf-8
'''
CBQuery -- query data from, write data to Dynamics
 
CBQuery is a script that allows you to run SQL queries via Connect Bridge ODBC driver
 
@author: Ana Neto 
 
@copyright: 2019 
 
@contact: ana@connecting-software.com
@deffield updated: 18.02.2020
'''
 
import sys
import os
import pyodbc
 
from argparse import ArgumentParser
from argparse import RawDescriptionHelpFormatter
 
__all__ = []
__version__ = 0.4
__date__ = '2020-02-18'
__updated__ = '2020-02-18'
 
DEBUG = 1
TESTRUN = 0
PROFILE = 0
 
class CLIError(Exception):
   '''Generic exception to raise and log fatal errors.'''
   def __init__(self, msg):
       super(CLIError).__init__(type(self))
       self.msg = "E: %s" % msg
   def __str__(self):
       return self.msg
   def __unicode__(self):
       return self.msg
 
def main(argv=None): # IGNORE:C0111
   '''Command line options'''
 
   if argv is None:
       argv = sys.argv
   else:
       sys.argv.extend(argv)
 
   program_name = os.path.basename(sys.argv[0])
   program_version = "v%s" % __version__
   program_build_date = str(__updated__)
   program_version_message = '%%(prog)s %s (%s)' % (program_version, program_build_date)
   program_shortdesc = __import__('__main__').__doc__.split("n")[1]
   program_license = '''%s
 
 Created by Ana Neto on %s.
 
 Licensed under the Apache License 2.0
 http://www.apache.org/licenses/LICENSE-2.0
 
 Distributed on an "AS IS" basis without warranty of any kind, either express or implied.
 
USAGE
''' % (program_shortdesc, str(__date__))
 
   try:
       # Setup argument parser
       parser = ArgumentParser(description=program_license, formatter_class=RawDescriptionHelpFormatter)
       parser.add_argument('connstr')        
       parser.add_argument('query')
        
       # Get arguments
       args = parser.parse_args()
 
       query = args.query
       connstr = args.connstr
 
       conn = pyodbc.connect(connstr)
       cursor = conn.cursor()
       cursor.execute(query)
       while 1:
           row = None
           try:
               row = cursor.fetchone()
           except: 
               print(sys.exc_info()[1])
               break
           if not row:
               break                    
           print(row)
                        
            
   except KeyboardInterrupt:
       ### handle the keyboard interrupt ###
       return 0
   except:
       print(sys.exc_info()[1])
       #indent = len(program_name) * " "        
       #sys.stderr.write(program_name + ": " + repr(e) + "n")
       #sys.stderr.write(indent + " for help use --help")
       return 2
 
if __name__ == "__main__":
         
   if TESTRUN:
       import doctest
       doctest.testmod()
   if PROFILE:
       import cProfile
       import pstats
       profile_filename = 'CBQuery_profile.txt'
       cProfile.run('main()', profile_filename)
       statsfile = open("profile_stats.txt", "wb")
       p = pstats.Stats(profile_filename, stream=statsfile)
       stats = p.strip_dirs().sort_stats('cumulative')
       stats.print_stats()
       statsfile.close()
       sys.exit(0)
   sys.exit(main())

Let's breakdown what this script does:

• Lines 71 to 76 use the pydev’s argparse script template to get the variables connstr and query from the command line input.
• The variable connstr is used for the ODBC connection string which I then pass to the pyodbc module which will build the ODBC connection (that goes into the conn variable)
• The next step is opening a database cursor using the connection we had stored in conn
• I run the query (this is the SQL query that I got via the command line parameter named query, I suggest using the one we saw in the discussion above for starters)
• I cycle using a while loop to read results from the cursor row by row. When cursor.fetchone returns None, I break the loop
• I also break the loop if an exception occurs during the ODBC fetch. In this case, I also print the problem to output.
• If the fetchone method returns a data row (success!), I print that data row directly to the output. Naturally, I could have formatted it as JSON, XML, CSV, or any kind of data-interchange format I wanted. It would also be possible to use the raw data row object in further code to perform any specific tasks I needed.
  When running the script, keep in mind that the CBQuery.py script takes two command-line arguments, connstr and query. You will need to copy these from Connect Bridge Management Studio as I explained in points 3.2 and 3.4.

Constraints

No ODBC Linux client library is available so, naturally, the use of the Connect Bridge tool is limited to Windows machines.

Conclusion

We have seen how you can quickly develop a Python script that accesses Dynamics data by using the Connect Bridge integration platform. You can also use Connect Bridge to access other Microsoft software, such as SharePoint or Exchange, or different kinds of software like Salesforce or SAP.

If you have an integration challenge, Connect Bridge can really save a developer a lot of headaches. You'll need to write less code, your code will have fewer errors, and once it is done, no maintenance will be required from your side, as Connect Bridge comes with a forward compatibility warranty (that's really the beauty of it).

If you want to know if you can use Connect Bridge to get some specific information you want, try it out using a free trial or get in touch with Connecting Software's experts. They will be more than happy to give you a free demo or answer any questions you might have.

You can also Leave a Reply below, and I will do my best to help you out.