Connect Netezza to Python using JDBC Driver

Connect to Netezza by using various methods and programming languages. Netezza supports ODBC, OLEDB and JDBC drivers for connections.

Before you begin, gather this connection information from the database administrator:

• Name of the server that hosts the database you want to connect to
• Database name
• User name and password
• Are you connecting to an SSL server?

Netezza JDBC Driver

Connection to Netezza using JDBC driver is easy and one of the widely used method.

Netezza provides JDBC driver, you can use that driver from any programming language that supports JDBC connections such as Java, Python etc. You can download JDBC driver from IBM fix central site. You should have jdbc jar by the name nzjdbc.jar.

Alternatively, you will get software packages if you purchase Netezza or IBM PureData Systems for analytics.

First, you need to install jaydebeapi python module using pip (or any other method).

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to databases using Java JDBC. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

pip install Jaydebeapi

Set CLASSPATH to Driver Location

As we have no other dependent jar for this Netezza JDBC driver, you can directly refer this driver in your jaydebeapi module. Alternatively, you can export jar location to CLASSPATH shell variable and run your python program without needing to set jar location in your module.

You can read on how to set CLASSPATH variable in my another post Set and Use Environment Variable inside Python Script

How to Connect Netezza using JDBC Driver?

In this section we will discuss how can we connect Netezza using JDBC driver. I will be using python and jaydebeapi to execute Netezza JDBC driver.

Once you have Netezza jar in a place and installed required modules, you are ready to access Netezza from withing your Python program using JDBC driver.

Note that, Netezza jdbc driver class name is “org.netezza.Driver“

Python Sample code to Connect to Netezza Server

Step 1: Importing Python jaydebeapi library:

import jaydebeapi, os

Step 2: Setting Database connection settings:


dsn_database = "DBNAME"
dsn_hostname = "191.168.100.10"
dsn_port = "5480"
dsn_uid = "admin"
dsn_pwd = "pwd"
jdbc_driver_name = "org.netezza.Driver"
jdbc_driver_loc = os.path.join('D:\\Work\\Connections_Softwares\\Jar\\nzjdbc.jar')

sql_str = "select now()"


connection_string=’jdbc:netezza://’+dsn_hostname+’:’+dsn_port+’/’+dsn_database

url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

Step 3: Creating Database Connection:

conn = jaydebeapi.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

Step 4: Processing SQL Query:

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()
print(result[0])

Step 6: Closing all connections:

curs.close()
conn.close()

Here is the sample output:

URL: jdbc:netezza://191.168.100.10:5480/DBNAME:user=admin;password=pwd
Connection String: jdbc:netezza://191.168.100.10:5480/DBNAME
('2020-01-02 06:02:12',)