Get pyodbc working within Aria Automation Orchestrator

Using the Python module pyodbc with unixODBC to connect to an MS SQL Server database

Preface

pyodbc is an open source Python module that makes accessing ODBC databases simple. Aria Automation Orchestrator provides Python runtime environment that can be extended with additional modules. However, pyodbc requires some packages on the host OS to function properly, and we found these components are missing from the underlying container image so pyodbc does not work out of box.

Our goal is to fill the gaps and make this module working.

Photon OS 4

During my initial attempts of to run pyodbc code I received some weird error messages from the Python runtime. I followed the official documentation on how to Create a ZIP package for Python runtime extensibility actions, but still received this error:

ModuleNotFoundError: No module named 'pyodbc'

After searching on Stackoverflow I realized my problem might be having a different Python runtime version locally. I also found out that I'll have to install some additional OS packages, so decided to install the same OS and continue the preparations on this virtual machine.

The container images providing Python runtime based on Photon OS. To get the exact version and packages installed, let's run the following code:

1import os
2def handler(context, inputs):
3    os.system("cat /etc/photon-release")
4    os.system("tdnf --disablerepo=* info|grep 'Name          :'")
5    print(os.environ)
6    return "done"

Let's install this build Photon OS 4.0 Rev2 and login to the VM.

Install dependencies on the local VM

The first goal is to figure out what needs to be installed and configured to run pyodbc with Microsoft ODBC driver 18. Following the official documentation we need to install unixODBC and msodbcsql18 packages. Unfortunately the latter is not released for Photon OS but we try which binary works for us.

Let's start with unixODBC and add zip package too.

1tdnf -y install unixODBC zip

Then install MS ODBC for SLES15 (this is what I found working).

1mkdir unixODBC
2curl -sSL https://packages.microsoft.com/sles/15/prod/Packages/m/msodbcsql18-18.3.2.1-1.x86_64.rpm -O --output-dir unixODBC
3ACCEPT_EULA=Y tdnf install --nogpgcheck -y unixODBC/msodbcsql18-18.3.2.1-1.x86_64.rpm 

To install pip we need to download the installer first, as it is not available in Photon repos.

1curl -sS https://files.pythonhosted.org/packages/e0/63/b428aaca15fcd98c39b07ca7149e24bc14205ad0f1c80ba2b01835aedde1/pip-23.3-py3-none-any.whl -O
2python pip-23.3-py3-none-any.whl/pip install --no-index pip-23.3-py3-none-any.whl

Now we can install pyodbc.

1pip install pyodbc

Test pyodbc functionality on the local VM

Let's try to connect to our MS SQL server and run a query.

 1import pyodbc
 2
 3SERVER = 'mssql.cloud.lab'
 4DATABASE = 'test'
 5USERNAME = 'sa'
 6PASSWORD = 'P@ssw0rd'
 7
 8connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};TrustServerCertificate=yes;'
 9
10conn = pyodbc.connect(connectionString)
11
12SQL_QUERY = 'SELECT * FROM myObjects;'
13
14cursor = conn.cursor()
15cursor.execute(SQL_QUERY)
16records = cursor.fetchall()
17for r in records:
18    print(r)

The driver works, displaying the myObjects database table of our Custom Resource Type:

Packaging into an Orchestrator action

We need to make sure all the dependencies are installed on the Python container as well. For that we'll copy the dependencies onto the runtime environment and use some OS commands introduced in my previous post on Helm.

Based on the RPM list the following packages are missing: unixODBC, libstdc++, glibc-iconv
Let's download them, with pyodbc, on our Photon OS VM:

1tdnf reinstall -y --downloadonly --downloaddir unixODBC --disableexcludes unixODBC libstdc++ glibc-iconv
2pip install --no-cache-dir pyodbc --target=unixODBC/lib

Let's extend our test Python script to install the packages prior to pyodbc usage. Only after the dependencies installation we can import pyodbc.

 1import os
 2
 3def handler(context, inputs):
 4    os.system("ACCEPT_EULA=Y tdnf --disablerepo=* --nogpgcheck -y install ./unixODBC-2.3.9-1.ph4.x86_64.rpm ./msodbcsql18-18.3.2.1-1.x86_64.rpm ./glibc-iconv-2.32-11.ph4.x86_64.rpm ./libstdc++-10.2.0-2.ph4.x86_64.rpm")
 5
 6    import pyodbc
 7    SERVER = 'mssql.cloud.lab'
 8    DATABASE = 'test'
 9    USERNAME = 'sa'
10    PASSWORD = 'P@ssw0rd'
11
12    connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};TrustServerCertificate=yes;'
13
14    conn = pyodbc.connect(connectionString)
15
16    SQL_QUERY = 'SELECT * FROM dbo.myObjects;'
17
18    cursor = conn.cursor()
19    cursor.execute(SQL_QUERY)
20    records = cursor.fetchall()
21    for r in records:
22        print(r)
23
24    return "done"

After we saved this in unixODBC/handler.py, let's create the ZIP package to import the code into our Orchestrator action.

1cd unixODBC
2zip -r ../unixODBC.zip .

Trying the action

After creating the action let's try to run it:

As we can see in the logs, the packages are installed and then pyodbc can read from the MS SQL Server database.

Final thoughts

Altough the SQL plugin of Orchestartor is capable of connecting SQL Servers, we cannot use it in Python actions. Python has a huge library of modules suitable for a lot of usecases we may need: the above method allows to use databases via the ODBC connector within Python code.

You can download the com.test.unixodbc.mssql package containing the code from GitHub: https://github.com/kuklis/vro8-packages