Using the CData ODBC Driver for Sage X3 Cloud in PyCharm

The CData ODBC Drivers can be used in any environment that supports loading an ODBC Driver. In this tutorial we will explore using the CData ODBC Driver for Sage X3 Cloud from within PyCharm. Included are steps for adding the CData ODBC Driver as a data source, as well as basic PyCharm code to query the data source and display results.

To begin, this tutorial will assume that you have already installed the CData ODBC Driver for Sage X3 Cloud as well as PyCharm.

Add Pyodbc to the Project

Follow the steps below to add the pyodbc module to your project.

1. Click File -> Settings to open the project settings window.
2. Click Project Interpreter from the Project: YourProjectName menu.
3. To add pyodbc, click the + button and enter pyodbc.
4. Click Install Package to install pyodbc.

Connect to Sage X3 Cloud

You can now connect with an ODBC connection string or a DSN. See the Getting Started section in the CData driver documentation for a guide to creating a DSN on your OS.

Sage X3 Cloud uses the OAuth 2.0 Client Credentials flow, and an X-API-Key is also required for API access. Set AuthScheme to OAuth and specify the following connection properties:

• URL: The base URL of your Sage X3 Cloud instance.
• OAuthAccessTokenUrl: The OAuth token endpoint (e.g., https://your-auth-domain/oauth/token).
• OAuthClientId: Your OAuth application client ID.
• OAuthClientSecret: Your OAuth application client secret.
• Audience: The API audience value for the token request.
• XAPIKey: The X-API-Key provided by your Sage X3 Cloud administrator.
• Folder: The Sage X3 folder name (e.g., SEED). This folder is used as the default schema.
• Folders (optional): A comma-separated list of Sage X3 folders (e.g., SEED,PERF). Each folder is exposed as a separate schema, so you can query across folders with the Schema.Table syntax.

The driver obtains an access token with the Client Credentials flow and sends it with the X-API-Key on every API request. With InitiateOAuth set to GETANDREFRESH (the default), the driver acquires and refreshes the token automatically.

Below is the syntax for a DSN:

[CData SageX3Cloud Source]
Driver = CData ODBC Driver for Sage X3 Cloud
Description = My Description
AuthScheme = OAuth
URL = https://x3server/
OAuthAccessTokenUrl = https://auth-domain/oauth/token
OAuthClientId = your_client_id
OAuthClientSecret = your_client_secret
Audience = https://api-audience
XAPIKey = your_api_key
Folder = SEED

Execute SQL to Sage X3 Cloud

Instantiate a Cursor and use the execute method of the Cursor class to execute any SQL statement.

import pyodbc

cnxn = pyodbc.connect('DRIVER={CData ODBC Driver for SageX3Cloud};AuthScheme = OAuth;URL = https://x3server/;OAuthAccessTokenUrl = https://auth-domain/oauth/token;OAuthClientId = your_client_id;OAuthClientSecret = your_client_secret;Audience = https://api-audience;XAPIKey = your_api_key;Folder = SEED;')
cursor = cnxn.cursor()
cursor.execute("SELECT BPCNUM, BPCNAM FROM BPCUSTOMER WHERE BPCNUM = 'MARTIN'")
rows = cursor.fetchall()
for row in rows:
  print(row.BPCNUM, row.BPCNAM)

After connecting to Sage X3 Cloud in PyCharm using the CData ODBC Driver, you will be able to build Python apps with access to Sage X3 Cloud data as if it were a standard database. If you have any questions, comments, or feedback regarding this tutorial, please contact us at [email protected].