Getting Started with the CData Connect AI Python SDK for Odoo
The CData Connect AI Python SDK (cdata-connect-ai) is a DB-API 2.0 (PEP 249) compliant client that lets you fetch and act on live Odoo data with standard Python database code. Because Connect AI provides the connectivity: you install one package, authenticate with a Personal Access Token, and query Odoo (and every other source connected in Connect AI) using the same familiar connect() / cursor() / fetchall() pattern you already know from libraries like sqlite3 and psycopg2.
This guide walks through connecting Odoo in Connect AI, generating a Personal Access Token, installing the SDK, and reading (and, where supported, writing) live Odoo data.
Prerequisites
- An account in CData Connect AI
- Python 3.8 or higher
- An active Odoo account with valid credentials
About Odoo Data Integration
Accessing and integrating live data from Odoo has never been easier with CData. Customers rely on CData connectivity to:
- Access live data from both Odoo API 8.0+ and Odoo.sh Cloud ERP.
-
Extend the native Odoo features with intelligent handling of many-to-one, one-to-many, and many-to-many data properties. CData's connectivity solutions also intelligently handle complex data properties within Odoo. In addition to columns with simple values like text and dates, there are also columns that contain multiple values on each row. The driver decodes these kinds of values differently, depending upon the type of column the value comes from:
- Many-to-one columns are references to a single row within another model. Within CData solutions, many-to-one columns are represented as integers, whose value is the ID to which they refer in the other model.
- Many-to-many columns are references to many rows within another model. Within CData solutions, many-to-many columns are represented as text containing a comma-separated list of integers. Each value in that list is the ID of a row that is being referenced.
- One-to-many columns are references to many rows within another model - they are similar to many-to-many columns (comma-separated lists of integers), except that each row in the referenced model must belong to only one in the main model.
- Use SQL stored procedures to call server-side RFCs within Odoo.
Users frequently integrate Odoo with analytics tools such as Power BI and Qlik Sense, and leverage our tools to replicate Odoo data to databases or data warehouses.
Getting Started
Connect to Odoo in Connect AI
CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.
- Log into Connect AI, click Sources, and then click Add Connection
- Select "Odoo" from the Add Connection panel
-
Enter the necessary authentication properties to connect to Odoo.
To connect, set the Url to a valid Odoo site, User and Password to the connection details of the user you are connecting with, and Database to the Odoo database.
- Click Save & Test
- Navigate to the Permissions tab and update the user-based permissions.
Generate a Personal Access Token (PAT)
The Python SDK authenticates to Connect AI with your account email and a Personal Access Token (PAT). It is best practice to create a separate PAT for each application to maintain granularity of access.
- Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
- On the Settings page, go to the Access Tokens section and click Create PAT.
- Give the PAT a name and click Create.
- The PAT is only visible at creation, so copy it and store it securely.
Install the SDK
Install the SDK from PyPI with pip:
pip install cdata-connect-ai
Connect and Run Your First Query
Connect with your account email and PAT, then query sys_tables to discover every table available across your connected sources. Identifiers in Connect AI are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, Odoo1).
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
cur = conn.cursor()
# Discover what's available across your connected sources
cur.execute("SELECT CatalogName, SchemaName, TableName FROM sys_tables LIMIT 25")
for row in cur.fetchall():
print(row)
Pick any table from the results and query it directly:
cur.execute(
"SELECT name, email "
"FROM [Odoo1].[Odoo].[res_users] "
"LIMIT 10"
)
for row in cur.fetchall():
print(row)
Write Back to Odoo
When the data source and your connection permissions allow it, the same cursor runs INSERT, UPDATE, and DELETE statements. Bind values with pyformat (%(name)s) parameters, exactly as you would for a filtered read, and check cursor.rowcount for the number of affected rows.
# Insert a new record
cur.execute(
"INSERT INTO [Odoo1].[Odoo].[res_users] (name) "
"VALUES (%(newvalue)s)",
{"newvalue": "Example value"},
)
print(f"Rows inserted: {cur.rowcount}")
# Update existing records
cur.execute(
"UPDATE [Odoo1].[Odoo].[res_users] "
"SET email = %(newvalue)s "
"WHERE id = '1'",
{"newvalue": "Updated value"},
)
print(f"Rows updated: {cur.rowcount}")
conn.close()
Note: Even for writable sources, a read-only PAT or connection permission will reject write operations. The same parameterized pattern also covers DELETE statements and stored procedures through cursor.callproc().
That is the entire workflow: one package, a PAT, and standard DB-API calls. Because the SDK returns a normal DB-API connection, it drops straight into the rest of the Python data ecosystem. From here you can load Odoo data into pandas, build ETL pipelines with petl, or power a Dash web app, all using this same connection.
More Information and Free Trial
Now you can query live Odoo data from Python through the CData Connect AI Python SDK. For more information on connecting to Odoo (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start working with live Odoo data in Python.