[Python] How to use ODBC to connect HPE NonStop SQL/MX

Hello World! In this tutorial, we will be programming in python to execute queries on the HPE NonStop SQL/MX Database, using pyodbc and the NonStop SQL/MX ODBC driver.

This tutorial assumes that NonStop ODBC 3.x Unicode driver has already been installed. Check out the NonStop ODBC/MX Client Drivers User Guide for more information on the driver.

This tutorial also assumes that on your host, NonStop SQL/MX has been installed, MXCS is running, and a MXCS data source has been added and started. Check with your administrator for the IP address, port number etc. (If you’re the administrator uhh check this manual)

Link to source code: https://github.com/shaniceabigail/python-odbc-nonstop-sqlmx

Let’s get started!

Getting the prerequisites

Python and pip

First, download Python, if you have not done so. You can check if your machine already has python by running the command below in the command prompt on Windows. You should get Python with its version number if it has been installed properly.

C:\> python --version
Python 3.9.0
C:\>

Next, install pip (PIP is the python package manager that we will be using). Download get-pip.py on your laptop. Navigate to the folder of the file and run the following command.

C:\Downloads> python get-pip.py

Once it’s complete, check if pip has been installed by running the following command in Windows.

C:\> py -m pip --version
pip 20.3.1 from C:\Miniconda3\lib\site-packages\pip (python 3.9)
C:\> 

Installing pyodbc

pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience. tldr; it helps you to access the databases that use ODBC drivers.

You can read more about pyodbc at its Github wiki page.

You can install pyodbc using pip.

C:\> pip install pyodbc

Configuring your ODBC

In order for pyodbc to recognize ODBC driver and data source to use, it will check with the ODBC Data Source Administrator on your Windows machine. Here’s how you can configure a new data source that will use the NonStop SQL/MX ODBC driver.

1. Search and open the ODBC Data Source Administrator on your machine.
2. Select “Add” button to create a new data source.

ODBC Data Source Administrator

3. Select the NonStop(TM) ODBCMX 3.x Unicode driver, and click “Finish”.

ODBC Data Source Administrator — Create New Data Source

4. A new window should pop up. Write a Data Source Name for this data source that you want to connect to — The data source names must match between those defined to MXCS on the database server, and the client PCs, otherwise the connection will FAIL.

Data Source Name and Description

5. Insert the IP address of the NonStop SQL/MX, as well as the port number that has been opened up for connections.

IP address and Port number

6. Insert the catalog and schema that you’re intending to connect to.

Catalog and Schema

7. Leave the “Translate DLL” portion (DLL Name and Option), and the Localization (Replacement Character) blank for now.

Leave blank

8. We will not be doing any tracing in this data source so leave the settings as the default, and click finish.

You can test the connection, and click “OK”. You should be able to see that the data source has been added to the list.

Alright, now it’s time to code!

The code:

The setup

Create a new .py file in any of your favourite text editor — mine is VSCode.

Import the python package into the script.

Add your Data Source Name, UID (User ID) and Password (PWD) in the fields.

Finally, set the decoding and encoding parameters for the connection which are database specific. NonStop SQL/MX supports “iso-8859–1”, but this varies with the database you’re using. (We set this for good measure — so copy paste the parameters from the code below for NonStop SQL/MX)

This is what you should have so far.

import pyodbc 
conn = pyodbc.connect('DSN=[DATA SOURCE NAME];UID=[USER];PWD=[PASSWORD]') 
conn.setdecoding(pyodbc.SQL_CHAR, encoding='iso-8859-1')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='iso-8859-1')
conn.setencoding(encoding='iso-8859-1') 

Now onto executing an SQL query in the database.

Create a cursor variable and execute the SQL statement that you would like to have in your database.

Do note that YOU HAVE TO COMMIT THE TRANSACTION if you make an insert or update table statement in the python script. You can insert the commit at the end of the set of updates or inserts.

The act of committing the transactions is how we make sure that the set of transactions / executions are properly executed, and data integrity maintained.

cursor = conn.cursor()
cursor.execute('INSERT INTO CATALOG.SCHEMA.TABLE VALUES (VALUE1, VALUE2)')
# makes sure that insert statement is a committed transaction in NonStop SQL/MX database
conn.commit() 

Seeing the result / select statement

You can execute the select statement using the cursor and print the values in the cursor.

# prints table to make sure that data was updated
cursor.execute('SELECT * FROM CATALOG.SCHEMA.TABLE')
for row in cursor:    
    print(row)

And there you have it!

The python script should be able to insert, update, create and select. Alright, that’s it for now, and hope you’ll have fun coding with the NonStop SQL/MX database!