How to connect Python with OData

ODBC PowerPack API Driver
1

Introduction

This guide will walk you through connecting Python to OData using the ZappySys ODBC API Driver for efficient data integration. This setup makes managing and analyzing OData data across various applications seamless.

Prerequisites

  • ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or use the trial version.
  • pyodbc Module: This Python library enables easy access to ODBC databases.

Steps

Step 1: Install pyodbc in Python

To connect Python to OData, you need to install the pyodbc module:

  1. Ensure Python is installed: Download and install Python from the official website if it is not already available on your system.

  2. Install pyodbc: Open your terminal or command prompt and run the following command:

    python -m pip install pyodbc

    This installs the pyodbc library, which is crucial for ODBC database connections in Python.

Why Install pyodbc?

  • Enables seamless connection between Python and ODBC-supported databases like SQL Server, PostgreSQL, and more.
  • Facilitates querying and managing databases directly from Python.
  • Supports multiple platforms, including Windows, macOS, and Linux.

If pyodbc is not installed, running a script that uses it will result in this error:

ModuleNotFoundError: No module named 'pyodbc'

Step 2: Create a New ODBC Driver

  1. Open ODBC Data Source Administrator by searching for “ODBC” on your system.
    Open ODBC Data Source

  2. Select User DSN or System DSN. For a system-wide configuration, go to System DSN and click Add.
    ZappySys ODBC Driver - Open UI

  3. Choose ZappySys API Driver from the list, then select OData as the connector. Click Continue.

    Select OData Connector
    Select OData Connector625×506 50.3 KB

Step 3: Configure the OData Connector

  1. Set the Authentication Type (e.g., Token Authentication) and provide the API Base URL. For more details, refer to the Authentication Guide.

    OData Connector
    OData Connector806×706 50.1 KB

  2. Preview the data by selecting a table in the Preview Tab. If everything looks correct, click OK to save.

    Preview Data
    Preview Data806×706 53.3 KB

Step 4: Read Data in Python Using ODBC DSN

  1. Create a Python script, e.g., ODataExample.py:

    import pyodbc

# Connect to OData using DSN
conn = pyodbc.connect('DSN=API Driver - OData')
cursor = conn.cursor()

# Execute query to fetch data
cursor.execute("SELECT * FROM Orders")

print("------------OData & Python----------------")
for row in cursor:
    print(row)

  2. Run the script. The output will display the fetched data:

    Example Output
    Example Output981×514 24.6 KB

Step 5: Use a Full Connection String

To avoid creating multiple DSNs for different systems (x86, x64), use a connection string:

  1. Open the ODBC Data Source Administrator, configure your DSN, and click Copy Settings to retrieve the connection string.

    Copy Settings
    Copy Settings804×704 42.1 KB

  2. Replace the DSN in your Python script with the connection string:

    conn = pyodbc.connect('DRIVER={ZappySys API Driver};ServiceUrl=https://yourservice.provider.com/api/xxxx....;AuthName=Http;')

Video tutorial

Conclusion

Following this guide, you can integrate Python with OData using the ZappySys ODBC API Driver. This allows you to automate tasks such as retrieving, processing, and analyzing data. For more assistance, contact the ZappySys support team.

References