How to Build an ETL App for Outlook Data in Python with CData

The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData API Driver for Python and the petl framework, you can build Outlook-connected applications and pipelines for extracting, transforming, and loading Outlook data. This article shows how to connect to Outlook with the CData Python Connector and use petl and pandas to extract, transform, and load Outlook data.

With built-in, optimized data processing, the CData Python Connector offers unmatched performance for interacting with live Outlook data in Python. When you issue complex SQL queries from Outlook, the driver pushes supported SQL operations, like filters and aggregations, directly to Outlook and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to Outlook Data

Connecting to Outlook data looks just like connecting to any relational data source. Create a connection string using the required connection properties. For this article, you will pass the connection string as a parameter to the create_engine function.

Using OAuth Authentication

Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

Obtaining OAuth Credentials

1. Log in to the Azure Portal.
2. Navigate to Azure Active Directory > App registrations.
3. Click New registration to create a new application.
4. Enter an application name and select the appropriate account types.
5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
6. Click Register to create the application.
7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
8. Navigate to Certificates & secrets and create a new client secret.
9. Copy the client secret value - this is your OAuthClientSecret.
10. Navigate to API permissions and add the required Microsoft Graph API permissions:
    • Mail.Read - For accessing email messages
    • Contacts.Read - For accessing contacts
    • Calendars.Read - For accessing calendar events
    • Tasks.Read - For accessing To Do tasks
    • offline_access - For obtaining refresh tokens
11. Click Grant admin consent to grant these permissions.

Connecting with OAuth

After setting the following connection properties, you are ready to connect:

• AuthScheme: Set this to OAuth.
• InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
• OAuthClientId: Set this to the Application (client) ID from Azure Portal.
• OAuthClientSecret: Set this to the client secret value from Azure Portal.
• TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
• CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

Example connection string

Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

After installing the CData Outlook Connector, follow the procedure below to install the other required modules and start accessing Outlook through Python objects.

Install Required Modules

Use the pip utility to install the required modules and frameworks:

pip install petl
pip install pandas

Build an ETL App for Outlook Data in Python

Once the required modules and frameworks are installed, we are ready to build our ETL app. Code snippets follow, but the full source code is available at the end of the article.

First, be sure to import the modules (including the CData Connector) with the following:

import petl as etl
import pandas as pd
import cdata.api as mod

You can now connect with a connection string. Use the connect function for the CData Outlook Connector to create a connection for working with Outlook data.

cnxn = mod.connect("Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;")

Create a SQL Statement to Query Outlook

Use SQL to create a statement for querying Outlook. In this article, we read data from the CalendarGroupCalendars entity.

sql = "SELECT ,  FROM CalendarGroupCalendars WHERE CalendarGroupId = 'group_id'"

Extract, Transform, and Load the Outlook Data

With the query results stored in a DataFrame, we can use petl to extract, transform, and load the Outlook data. In this example, we extract Outlook data, sort the data by the column, and load the data into a CSV file.

Loading Outlook Data into a CSV File

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

etl.tocsv(table2,'calendargroupcalendars_data.csv')

With the CData API Driver for Python, you can work with Outlook data just like you would with any database, including direct access to data in ETL packages like petl.

Free Trial & More Information

Download a free, 30-day trial of the CData API Driver for Python to start building Python apps and scripts with connectivity to Outlook data. Reach out to our Support Team if you have any questions.

Full Source Code

import petl as etl
import pandas as pd
import cdata.api as mod

cnxn = mod.connect("Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;")

sql = "SELECT ,  FROM CalendarGroupCalendars WHERE CalendarGroupId = 'group_id'"

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

etl.tocsv(table2,'calendargroupcalendars_data.csv')