Connecting to NonStop SQL/MX using PyODBC with ODBC Driver

PyODBC is a Python module that provides a simple and consistent interface to connect to databases using the Open Database Connectivity (ODBC) API. In this article, we will explore different ways to connect to NonStop SQL/MX using PyODBC with ODBC driver.

This tutorial assumes that NonStop ODBC 3.x 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 NonStop SQL/MX has been installed on your host, 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 are the administrator, check out this NonStop SQL/MX Connectivity Service Manual for SQL/MX.)

In this comprehensive tutorial, we will explore two methods of connecting to an SQL/MX Database using PyODBC: one without relying on an ODBCDSN file, and the other utilizing an ODBCDSN file. Additionally, we will delve into the efficient retrieval of data from database tables, demonstrating both with and without the aid of the Pandas library. Moreover, we will uncover the power of parameterized queries and shed light on the significance of SQLFractionValue=9 in PyODBC for SQL/MX. Join us as we embark on this journey to master database connectivity and data manipulation in the world of Python.

List of the drivers supported

• Windows
• Linux
• NonStop(NSK)

Installation steps

Installing Python 3:

Python 3 is a versatile programming language used in various fields, including data analysis, web development, and more. To install Python 3 on your system, follow these steps:

1. Visit the Official Python Website: Go to the official Python website at https://www.python.org/downloads/ to download the latest version of Python 3.

2. Choose the Installer: Depending on your operating system (Windows, macOS, or Linux), choose the appropriate installer for Python 3. Download the installer for your system.

3. Run the Installer: Execute the installer you downloaded. During installation, make sure to check the box that says “Add Python X.Y to PATH” (where X.Y represents the version number). This ensures that you can run Python from the command line easily.

4. Complete Installation: Follow the installation prompts to complete the installation process. Python 3 should now be installed on your system.

5. Verify Installation: Open a command prompt (Windows) or terminal (macOS or Linux) and type python --version or python3 --version

to confirm that Python 3 is installed and check the version number.

Installing PyODBC:

PyODBC is a Python module that provides access to ODBC (Open Database Connectivity) databases, making it easy to connect to various database systems. Here’s how to install PyODBC:

1. Open a Terminal or Command Prompt: Open a terminal or command prompt on your system.

2. Install PyODBC via pip: Use the following command to install PyODBC using pip, the Python package manager:

pip install pyodbc

If you’re using Python 3, you might need to use pip3 instead of pip.

3. Verify PyODBC Installation: After the installation is complete, you can verify that PyODBC is installed by running a Python script that imports the module without any errors. Create a Python script (e.g., test_pyodbc.py) with the following content:

import pyodbc

print("PyODBC is installed and working!")

Run the script using the command python test_pyodbc.py. If you don’t encounter any errors and see the “PyODBC is installed and working!” message, PyODBC is successfully installed.

Installing Pandas:

Pandas is a powerful data manipulation library for Python, widely used in data analysis and data science. To install Pandas, follow these steps:

1. Open a Terminal or Command Prompt: Open a terminal or command prompt on your system.

2. Install Pandas via pip: Use the following command to install Pandas using pip, the Python package manager:

pip install pandas

If you’re using Python 3, you might need to use pip3 instead of pip.

3. Verify Pandas Installation: After the installation is complete, you can verify that Pandas is installed by running a Python script that imports the module without any errors. Create a Python script (e.g., test_pandas.py) with the following content:

import pandas as pd

print("Pandas is installed and working!")

Run the script using the command python test_pandas.py. If you don’t encounter any errors and see the “Pandas is installed and working!” message, Pandas is successfully installed.

With Python 3, PyODBC, and Pandas installed on your system, you’re well-equipped for data analysis and working with databases in Python.

Direct Connection

To connect to SQL/MX using PyODBC with ODBC driver , we need to provide the following basic details: The basic example looks like below, and please refer to HPE NonStop ODBC/MX Client Drivers UserGuide for details. In order to utilize this Direct connection, it is essential that MXCS is initially started and configured with ‘Data Source: TDM_Default_DataSource.’

- DRIVER={NonStop(TM) ODBCMX 3.8}
- SERVER=TCP:machine_address/PORT_NUMBER
- SQLFractionValue=9

The connection string is structured as follows:

con_sqlmx = pyodbc.connect('''DRIVER={driver_name};
                            SQL_ATTR_CONNECTION_TIMEOUT=NO_TIMEOUT;
                            SERVER=TCP:machine_name/PORT_NUMBER;
                            UID=USERID;PWD=PASSWORD''',
                            ansi=True,autocommit=True,
                            SQLFractionValue=9)

and for example: driver_name: NonStop(TM) ODBCMX 3.8

Create a Python script (e.g., delete_temporary_data.py) with the following content which connects to SQL/MX NonStop database directly without using ODBCDSN file. To execute this program, use ‘python delete_temporary_data.py’ command:

import pyodbc
# Direct connection to SQLMX driver without ODBCDSN file in NSK or without adding DSN details in windows driver
con_sqlmx = pyodbc.connect('''DRIVER={driver_name};
                            SQL_ATTR_CONNECTION_TIMEOUT=NO_TIMEOUT;
                            SERVER=TCP:machine_name/PORT_NUMBER;
                            UID=USERID;PWD=PASSWORD''',
                            ansi=True,autocommit=True,
                            SQLFractionValue=9)
# Deleting records from table and printing number of records deleted
with con_sqlmx.cursor() as cur:
     cur.execute('delete from TABLE1 where amount>100')
     print("Number of rows deleted : cur.rowcount)
con_sqlmx.close()

This code connects to a NonStop SQL/MX database using the specified parameters, deletes rows from TABLE1 where the amount is greater than 100, and then prints the number of rows that were deleted. Finally, it closes the database connection.

Using Data Source

Another way to connect to SQL/MX database using PyODBC with ODBC driver is by using data source. To do this, Add a Data source with attributes in the ODBCDSN file and connect to the data source using PyODBC. For more information on ODBCDSN file, please refer to HPE NonStop ODBC/MX Client Drivers UserGuide. Here is an example of an ODBCDSN file:

[ODBC Data Sources]
ODSN = DSN

DataSourceName         = Driver

[DSN]
Description                 = Default Data Source
Catalog                     = USR
Schema                      = NEO
DataLang                    = 0
FetchBufferSize             = SYSTEM_DEFAULT
Server                      = TCP:machine_address/port_number
SQL_ATTR_CONNECTION_TIMEOUT = NO_TIMEOUT
SQL_LOGIN_TIMEOUT           = SYSTEM_DEFAULT
SQL_QUERY_TIMEOUT           = NO_TIMEOUT
SQL_Interval_Insertion      = MSDN_DEFAULT

If you are working on Windows driver with datasource please refer Article.

To connect to the data source using Python, we need to provide the following details:

• DSN: Data source name which is created to connect to SQL/MX database.
• UID/PWD: Credentials to connect to SQL/MX/database.

Here is an example of how to connect to the data source using Python:

con_sqlmx=pyodbc.connect(DSN='DSN',  UID=username,   PWD=password,    ansi=True,  autocommit=True,     SQLFractionValue=9)

Retrieving Data from SQL/MX Database

Once we have established a connection to the SQL/MX database using PyODBC with ODBC driver, we can retrieve data from the database using Python using execute method. Here is an example of how to retrieve data from the SQL/MX database using the execute method:

import pyodbc

# Define database connection parameters
dsn = 'DSN'  # Your DSN (Data Source Name)
username = 'username'  # Your database username
password = 'password'  # Your database password

# Establish a connection to the SQL/MX database
with pyodbc.connect(DSN=dsn, UID=username, PWD=password, ansi=True, autocommit=True, SQLFractionValue=9) as con_sqlmx:
    # Create a cursor for executing SQL commands
    with con_sqlmx.cursor() as cur:
        # Define the SQL query
        query = 'SELECT * FROM TABLE1'

        # Execute the query
        cur.execute(query)

        # Fetch and print the results
        for record in cur.fetchall():
            print(record)

# The connection is automatically closed when exiting the 'with' block

Using Pandas

Here is an example of how to retrieve data from the SQL/MX database using Pandas:

# Import the necessary libraries
import pyodbc
import pandas as pd

# Define database connection parameters
dsn = 'DSN'  # Your DSN (Data Source Name)
username = 'username'  # Your database username
password = 'password'  # Your database password

# Establish a connection to the SQL/MX database
con_sqlmx = pyodbc.connect(DSN=dsn, UID=username, PWD=password, ansi=True, autocommit=True, SQLFractionValue=9)

# Use Pandas to read data from the database and create a DataFrame
query = 'SELECT * FROM TABLE1'
df = pd.read_sql(query, con_sqlmx)

# Print the DataFrame containing the retrieved data
print(df)

# Close the database connection
con_sqlmx.close()

Using Pandas simplifies the code and allows you to work with the data in a more structured and convenient way.

Parameterized Queries

Here is an example of how to retrieve data from the SQL/MX database with parameterized queries:

# Import the pyodbc library for database connection
import pyodbc

# Define database connection parameters
dsn = 'DSN'  # Your DSN (Data Source Name)
username = 'username'  # Your database username
password = 'password'  # Your database password

# Establish a connection to the SQL/MX database
con_sqlmx = pyodbc.connect(DSN=dsn, UID=username, PWD=password, ansi=True, autocommit=True, SQLFractionValue=9)

# Create a cursor for executing SQL commands
with con_sqlmx.cursor() as cur:
    # Define a parameterized SQL query
    query = 'SELECT * FROM TABLE1 WHERE ID=? AND NAME=?'

    # Specify the values for the query parameters
    parameters = (1, 'ATOM')

    # Execute the parameterized query with the specified parameters
    cur.execute(query, parameters)

    # Iterate through the result set and print each record
    for record in cur:
        print(record)

# Close the database connection
con_sqlmx.close()

Do you know SQLFractionValue=9?

SQL/MX Database and PyODBC handle timestamp fractions with inherent differences. SQL/MX Database natively represents timestamp fractions in microseconds, whereas PyODBC expects them in nanoseconds. To ensure seamless compatibility when a fractional timestamp datatype is used, it is crucial to incorporate the SQLFractionValue=9 attribute within your connection string or datasource configuration, when interfacing with SQL/MX Database. This attribute assumes significant importance in PyODBC’s connection string for SQL/MX Database, as its omission may lead to incorrect output values. Here’s how to do it:

con_sqlmx=pyodbc.connect(DSN='DSN',   UID=username,        PWD=password,  ansi=True,  autocommit=True, SQLFractionValue=9)

Let’s illustrate the difference in output by comparing two connection setups:

import pyodbc

# Connection without SQLFractionValue=9
con1 = pyodbc.connect('DRIVER={NonStop(TM) ODBCMX 3.8};SQL_ATTR_CONNECTION_TIMEOUT=NO_TIMEOUT;SERVER=TCP:machine_address/port_number;UID=username;PWD=password;',ansi=True,autocommit=True)

# Connection with SQLFractionValue=9
con2 = pyodbc.connect('DRIVER={NonStop(TM) ODBCMX 3.8};SQL_ATTR_CONNECTION_TIMEOUT=NO_TIMEOUT;SERVER=TCP:machine_address/port_number;UID=username;PWD=password;',ansi=True,autocommit=True,SQLFractionValue=9)

cur1=con1.cursor()
cur2=con2.cursor()

cur1.execute('select current from dual')
cur2.execute('select current from dual')

for c in cur1:
    # with out SQLFractionValue=9 then the current timestamp value appears as '01-Sep-2023 (15:12:57.000625)' which is incorrect
    print(c[0].strftime("%d-%b-%Y (%H:%M:%S.%f)"))    

for c in cur2:
    # with SQLFractionValue=9 then the output timestamp value appears as '01-Sep-2023 (15:12:57.625963)'
    print(c[0].strftime("%d-%b-%Y (%H:%M:%S.%f)"))  

#closing the cursors
cur1.close()
cur2.close()

#closing the connections
con1.close()
con2.close()

In summary, SQL/MX returns timestamp values with fractions in microseconds by default, while PyODBC expects them in nanoseconds. Therefore, including SQLFractionValue=9 in the connection string ensures accurate timestamp handling in your Python application.

Conclusion

In this article, we have explored different ways to connect to SQL/MX using PyODBC with ODBC driver. We have also seen how to retrieve data from the SQL/MX database using Python. We explored the parameter SQLFractionValue in the connection string. By using PyODBC with ODBC driver, we can easily connect to SQL/MX and perform various operations on the database using Python.