"Need to connect your Python script to an Exchange server? We'll show you an easy way to do it by using a tool called Connect Bridge."
Introduction
This article describes how to make Python connect to Exchange, that is, how to read mail from a Microsoft Exchange Server (Exchange 2010, 2013, 2016, 2019, Online) using a Python language script.
The data transfers are made via the ODBC layer, so from the scripting side it is exactly the same as if you were using python to access Microsoft SQL Server or to access any database via ODBC. You simply use the pyodbc module (we used python version 3.7 and pyodbc version 4.0.26.). The actual API integration is done by a tool called Connect Bridge and then on your Python script you read the data as if you were reading a database.
Please note that Connect Bridge is a commercial product. You can get a free trial for it, so you can try this approach for yourself at no cost.
What is this “Connect Bridge”?
Connect Bridge is an integration platform developed by Connecting Software. It allows your script to connect any software through ODBC drivers, JDBC drivers, or Web Services...avoiding the need to study and learn the API of the program you are trying to connect to!
In this specific article, we are using Connect Bridge to connect to Microsoft Exchange but you can also grab data from Microsoft SharePoint or a CRM like Salesforce or Dynamics among many others. And data transfers can actually be bidirectional. That is, you can also put data to these systems, although this time we are focusing on reading data.
Where do I start?
Our goal is to create a simple Python script that accesses an Exchange server and reads e-mail from it. We assume the Exchange instance already exists. These are simple steps you need to follow:
1. Make sure you have your Exchange login credentials at hand
2. Request a free trial and install Connect Bridge
3. Install Python for Windows ver. 3.7+. You can use the editor of your choice for writing the script.
4. Install pyodbc module 4.0.26+
5. Run Connect Bridge Management Studio and:
5.1. Add an account for Exchange (Accounts - Add account). For adding the account, you should select the connector MGEXPlugin2010 and use the credentials mentioned on point 1.
5.2. Open the New Query option and then the Connection Browser. Find the Exchange Connector and expand it until you see the DefaultConnection. Right click the DefaultConnection and choose Get Connection string. Copy the ODBC connection string, as you will need it to pass it on to the script.
5.3. Use the New Query option to test out a query that will access what you need in Exchange.
We will do an example query here, but this is where you should put in what it is that you are looking for in Exchange. Once you have clicked New Query, open the Connection Browser, on the left. Find the Exchange Connector (MGEXPlugin2010) and open until the Tables option is visible. We can see that the schema contains a “table” called Message so we can construct our query as SELECT * FROM Message WHERE CreationDate >= '2019-01-01 00:00:00' LIMIT 10; to select 10 entries from the Exchange's email messages list that were created after Jan 1, 2019. Again, please note that although it looks as if we are using a database directly, that is not the case. Connect Bridge is accessing the API and then presenting it as if it were a database. Once you have your query, copy it, as you will need to pass it on to the script.
Hands on scripting!
Our solution only has one file: CBExchange.py. The full source code is below. Please focus on lines 70-92 which depict the core solution. A complete description of how this script works is below.
#!/usr/local/bin/python3.7
# encoding: utf-8
'''
CBExchange -- query data from, write data to Exchange
CBExchange is a script that allows to read Exchange mail using SQL queries via Connect Bridge's ODBC driver
@author: Ana Neto
@copyright: 2019
@contact: ana@connecting-software.com
@deffield updated: 22.07.2019
'''
import sys
import os
import pyodbc
from argparse import ArgumentParser
from argparse import RawDescriptionHelpFormatter
__all__ = []
__version__ = 0.2
__date__ = '2019-07-22'
__updated__ = '2019-07-22'
DEBUG = 1
TESTRUN = 0
PROFILE = 0
class CLIError(Exception):
'''Generic exception to raise and log different 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 warranties
or conditions 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')
# Process 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 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 = 'CBExchange_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())
Here is a little on the variables we are using:
• parser is used to parse the arguments we get from the command line
• args holds those arguments
• query holds the query we want to run and that comes in as an argument
• connstr holds the ODBC connection string that comes in as an argument and that we pass into the pyodbc module to create an ODBC connection
• conn is the ODBC connection
And here is what our script is doing
• In lines 70 to 78, the goal is to get the variables connstr and query from the command line arguments input. pydev’s argparse script template was used for simplicity sake
• In line 80, we open a database cursor using conn
• In line 82, we execute the SQL query we got from the command line
• In lines 83 to 92, we loop through the results and read them from the cursor row by row.
•When cursor.fetchone returns None, we break the loop
• If an exception occurs during the ODBC fetch, we also break the loop and print the problem to output
• If the fetchone method succeeds and returns a data row, we print the raw data row as it is to the output. This is done as it is enough for demo purposes, but naturally in a real-world situation you could format as xml, json, csv… or any other kind of data-interchange format. Or you could also simply use the raw data row object to perform custom tasks in subsequent code.
How do I run the CBExchange.py script?
The CBExchange.py script accepts two positional command line arguments: connstr and query.
We need to copy these from Connect Bridge Management Studio as explained above (point 5.2). When you have them at hand, open a command line, cd to the folder you saved the script to and run your Python script passing it the 2 parameters.
What else can I do? Exploring further…
As mentioned before, more sophisticated queries can be built so that you are getting the e-mails you want. For example, if you want only to get the e-mails with attachments and with a specific creation date, the query would be
SELECT * FROM Message
WHERE [HasAttachment] = true and [CreationDate] >= '2019-07-01 00:00:00';
You can also explore the "Stored Procedures" available. Find the Exchange Connector (MGEXPlugin2010) and expand until you can see the "Stored Procedures" option. Again, these Stored Procedures are Connect Bridge's API integration, not actual database stored procedures. But the way to work with them is the same as if they were.
Some of the available Stored Procedures are visible on this screenshot:
Constraints
The Connect Bridge tool is only available for Windows operating systems, because no ODBC Linux client library is available. So, for this to work you will need a Windows machine.
Conclusion
We have seen how accessing Exchange data in Python can be easily done using the Connect Bridge integration platform. Now imagine that what you have seen in this article is possible also with Microsoft Dynamics CRM and Microsoft Exchange! Yes, with Connect Bridge this is possible and the way to do it is similar... you just need to pick the connector for the specific software you want and go with it.
If you want to explore the connectors currently available at Connect Bridge, the place to go is https://www.connecting-software.com/connectors/
If you are curious about which are the newest connectors in this big set, have a look at Connect Bridges’s Dynamics 365 Finance & Operations and Dynamics 365 Business Central connectors, both built on the OData Protocol. Or have a look at the OPC UA Connector. Yes, I know that seems like a totally different use case.... But the truth is you can access OPC UA servers using same tool. Just to give you an idea of what can result of this type of integration, have a look at Connect Bridge’s IIoT showcase.
We hope this article was helpful to you!
Here are some more posts we have on our blog discussing other integration possibilities:
