Python and nzpy

Domino recommends the nzpy package for interacting with Netezza from Python.

Environment setup

Use the following Dockerfile instruction to add nzpy to your environment.

This instruction assumes you already have pip installed.

RUN pip install nzpy

Credential setup

You must set the following Domino environment variables to store secure information about your Netezza connection

db_username
db_password

See Secure Credential Storage to learn more about Domino environment variables.

Usage

See the nzpy docs for detailed information about how to use the package. The following is an example for connecting to Netezza with nzpy where:

You have set up environment variables noted above with the db_username and db_password
You’ve replaced 'my.host.name' with the host name for your machine
You’ve replaced 'my.database.name' with the database name for your machine

import nzpy
import os
import pandas as pd

hostname = 'my.host.name'
database_name = 'my.database.name'
port = 5480
username = os.environ['db_username']
password = os.environ['db_password']

def query_db(sql):
   conn = nzpy.connect(user=username, password=password, host=hostname, port=port, database=database_name)
   with conn.cursor() as cursor:
      cursor.execute(sql)
      df = cursor.fetchall()
      return df

sql_cmd = """

SELECT
 *
FROM
 table

"""

df_cmd = query_db(sql_cmd)

df_cmd

R and RJDBC

Domino recommends the RJDBC package and nzjdbc jar for interacting with Netezza from R.

Environment setup

Use the following Dockerfile instruction to add RJDBC to your environment.

RUN R -e 'install.packages("RJDBC")'

Credential setup

You must set the following Domino environment variables to store secure information about your Netezza connection

db_username
db_password

See Secure Credential Storage to learn more about Domino environment variables.

Usage

Test if the nzjdbc jar is working as expected by running the below in a workspace terminal

java -jar <path.to.the.nzjdbc.jar> -t -h my.host.name -p 5480 -u <username> -db <database_name>

The following is a simple example for connecting to Netezza with RJDBC and nzjdbc jar where:

nzjdbc jar is present in the project files (/mnt/nzjdbc3)
You have set up environment variables noted above with the db_username and db_password
You’ve replaced my.host.name with the host name for your machine
You’ve replaced my.database.name with the database name for your machine

library(RJDBC)
drv <- RJDBC::JDBC(driverClass = "org.netezza.Driver", classPath = "/mnt/nzjdbc3.jar")
conn <- dbConnect(drv, 'jdbc:netezza://my.host.name:5480/my.database.name;logLevel=2',Sys.getenv('db_username'), Sys.getenv('db_password'))
dbListTables(conn)