Netezza Performance Server

Nzalchemy - SQLAlchemy dialect for Netezza Performance Server

By Sandeep Pawar posted Tue February 02, 2021 10:07 AM

SQLAlchemy is famous for its object-relational mapper (ORM), using which, classes can be mapped to the database objects, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.
SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI compliant database driver is installed.
For SQLAlchemy in details you can check here

Nzalchemy is a dialect built for Netezza Performance Server to work with nzodbc driver. This Netezza specific driver(nzodbc) is used with pyodbc to make nzalchemy work. 
Engine class of sqlalchemy connects a Pool and Dialect together to provide a source of database connectivity and behavior. An object of Engine class is instantiated using the create_engine() function.
params= urllib.parse.quote_plus("DRIVER=<path to>;SERVER=<server>; PORT=5480; DATABASE=<db>; UID=<uid>;PWD=<pwd>")
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)

How to Use It?

Install SQLAlchemy

Install pyodbc Python package
You might need to install unixODBC-devel and python3-devel packages in order to successfully install pyodbc

Install Netezza OBDC(nzodbc) driver
You will not be able to use pyodbc driver without installing Netezza OBDC drivers. This step is one of the pre-requisites to use pyodbc.

Install Netezza SQLAlchemy(nzalchemy):  The Netezza SQLAlchemy package can be installed from the public PyPI repository using pip
Connection Parameters To connect to Netezza with nzalchemy use the following connection string          
netezza+pyodbc:///?<ODBC connection parameters>

import urllib 
params= urllib.parse.quote_plus("DRIVER=/nzscratch/lib64/;;PORT=5480;DATABASE=testdb;UID=testuser1;
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)

In above example, is expected to have been unpacked in /nzscratch directory under lib64. It calls the create_engine method with the user name testuser1, password 0123456, database testdb on Netezza Performance Server
For detailed installation, configuration steps and example please refer

Supported Features

SQLAlchemy Core - schema-centric SQL Expression Language
It has a schema-centric view, which like traditional SQL is focused around tables, keys, and index structures. Its main role is to provide programmatic way to generate sql queries and DDL. Result set would be only tuples and not objects like in ORM.
We can use SQLAlchemy core to execute SQL queries after creating engine with SQLAlchemy provided basic operations like connect(), execute() etc.

employee = Table( 
   'employee', meta, 
   Column('id', Integer), 
   Column('name', VARCHAR(20) ), 
   Column('gender', CHAR), 
conn = engine.connect() 
ins = employee.insert().values(id='21',name='jack', gender='M') 
result = conn.execute(ins)
upd = employee.update().where(id=='21').values(name='updated_name')
result = conn.execute(upd)
result = conn.execute(select([func.max(]))
SQLAlchemy ORM - Python object based automatically constructed SQL
It represents database relationships as python objects. The SQLAlchemy ORM is developed on top of SQLAlchemy Core. 
# Class declaration
class Customers(Base): 
   __tablename__ = 'CUSTOMERS' 
   id = Column(nz.SMALLINT, Sequence('USR_ID_SEQ3'), primary_key = True) 
   name = Column(VARCHAR(30)) 
   address = Column(nz.NVARCHAR(30)) 
   email = Column(nz.NCHAR(30)) 
Customers.__table__.drop(engine, checkfirst=True) 
Customers.__table__.create(engine, checkfirst=True) 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind = engine) 
session = Session() 
#Object Creation
c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '') 
# select all 
result = session.query(Customers).all()
SQLAlchemy ORM and Core is supported in nzalchemy dialect. 
Real power of SQLAlchemy is Object Relational Mapper.  With SQLAlchemy classes can be mapped to the database, allowing the object model and database schema to develop in a cleanly decoupled way from the beginning. So, with nzalchemy, an application developer can fully access Netezza without having much knowledge about sql.

Simplified Syntax
Some examples without using sqlalchemy and with using sqlalchemy
Without SQLAlchemy With SQLAlchemy
create_test_table = """CREATE TABLE test(id INT, name VARCHAR(20),                                                                               gender CHAR)"""
with connection.cursor() as cursor:
    cursor.execute(create_test_table)    connection.commit()
test = Table('TEST', meta, Column('id', nz.INTEGER),
                   Column('name', nz.VARCHAR(20) ),
                   Column('gender', nz.CHAR),
select_test_table = """select * from test""" s = select([test])
update_test_table = """update test set name=""update_name" where id='2'"
updt = test.update().where( == '2').
delete_test_table = """ delete from test where name ='abc' """ delt = test.delete().where( == 'abc')

SQLAlchemy Examples 
1. Table creation and basic operations
Create Engine
#!/usr/bin/env python3
from sqlalchemy import create_engine, MetaData, Table, Column, select
import nzalchemy as nz
import urllib 
params= urllib.parse.quote_plus("DRIVER=<>;SERVER=<nz-running-server>; 
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)

Declare Table, datatypes INTEGER, VARCHAR and CHAR used from nzalchemy

meta = MetaData()
test = Table(
'TEST', meta,
Column('id', nz.INTEGER),
Column('name', nz.VARCHAR(20) ),
Column('gender', nz.CHAR),

Create table and perform insert operation

#conn for insert and select
conn = engine.connect()

Select from table

print ("After Insert")
s = select([test])
result = conn.execute(s)
for row in result:
        print (row)

Update with where clause

updt = test.update().where( == '2').values(name='updated_name')
s = select([test])
result = conn.execute(s)
for row in result:
        print (row)

Delete row with matching value

#Delete Row/s
delt = test.delete().where( == 'abc')
s = select([test])
result = conn.execute(s)
for row in result:
        print (row) 

2. Different joins on table

Create tables

import sys 
from sqlalchemy import create_engine, MetaData, Table, Integer, String, Column, DateTime, ForeignKey 
from datetime import datetime 
from sqlalchemy.types import CHAR 
from sqlalchemy.types import VARCHAR 
from sqlalchemy import select 
import urllib 
params= urllib.parse.quote_plus("DRIVER=<>;SERVER=<nz-running-server>;PORT=5480;DATABASE=<dbname>;UID=<usr>;PWD=<password>")
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)
meta = MetaData() 
conn = engine.connect() 
students = Table( 
   'STUDENTS', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', VARCHAR(25)), 
   Column('lastname', VARCHAR(25)), 
addresses = Table( 
   'ADDRESSES', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer, ForeignKey('')), 
   Column('postal_add', VARCHAR(25)), 
   Column('email_add', VARCHAR(25)) 
ins = students.insert() 
ins = test.insert()

Insert some data into student and addresses table

conn.execute(students.insert(), [ 
   {'id':13,'name':'Ravi', 'lastname':'Kapoor'}, 
   {'id':14,'name':'Rajiv', 'lastname' : 'Khanna'}, 
conn.execute(addresses.insert(), [ 
   {'id':1,'st_id':13, 'postal_add':'Shivajinagar Pune', 'email_add':''}, 
   {'id':2,'st_id':14, 'postal_add':'ChurchGate Mumbai', 'email_add':''}, 

Join on student id

from sqlalchemy import join 
from sqlalchemy.sql import select 
j = students.join(addresses, == addresses.c.st_id) 
stmt = select([students]).select_from(j)
stmt = select([addresses]).select_from(j) 


from sqlalchemy import union, union_all, except_, intersect 
u = union('')),''))) 
result = conn.execute(u) 
u = union_all('')),''))) 
result = conn.execute(u) 
u = except_('')),''))) 
result = conn.execute(u) 
u = intersect('')),''))) 
result = conn.execute(u)

3Accessing system datatypes
Accessing some internal system data types which are specific to NPS. 
Some of the data types accessed here: OID, NAME, ABSTIME, TEXT
import nzalchemy as nz 
TEST = Table( 
   '_v_object_data', meta, 
data = conn.execute( 
for row in data: 
     print (row)

4Get database and tables metadata(Reflection)
Access some metadata about tables, schemas such as Schema Names, Table Names, View Names & Definitions, Constraints, etc.
from sqlalchemy import inspect 
inspector = inspect(engine)
result = inspector.get_table_oid(123) 
result = inspector.get_schema_names() 
result = inspector.get_table_names() 
result = inspector.get_foreign_table_names() 
result = inspector.get_view_names() 
result = inspector.get_view_definition() 
result = inspector.get_columns() 
result = inspector.get_pk_constraint() 
result = inspector.get_foreign_keys() 
result = inspector.get_indexes() 
result = inspector.get_unique_constraints() 
result = inspector.get_table_comment() 
result = inspector.get_check_constraints() 

5. Example showing class relationship in sqlachemy
Create engine
import sys 
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, DateTime, select, desc, ForeignKey 
import urllib 
import datetime 
import nzalchemy as nz 
##Engine Creation 
params= urllib.parse.quote_plus("DRIVER=<>;SERVER=<nz-running-server>;PORT=5480;DATABASE=<dbname>;UID=<usr>;PWD=<password>")
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)

Two classes Customer & Invoice created. Invoice should be mapped to Customer in such a way that  it should be many to one mapping. Customer can have multiple invoices.

from sqlalchemy.ext.declarative import declarative_base 
Base = declarative_base() 
from sqlalchemy.orm import relationship 
class Customer(Base): 
   __tablename__ = 'CUSTOMER' 
   id = Column(Integer, primary_key = True) 
   name = Column(VARCHAR(30)) 
   address = Column(VARCHAR(30)) 
   email = Column(VARCHAR(30)) 
class Invoice(Base): 
   __tablename__ = 'INVOICE' 
   id = Column(Integer, primary_key = True) 
   custid = Column(Integer, ForeignKey('')) 
   invno = Column(Integer) 
   amount = Column(Integer) 
   customer = relationship("Customer", back_populates = "INVOICE") 
Customer.INVOICE = relationship("Invoice", order_by =, back_populates = "customer") 
Customer.__table__.drop(engine, checkfirst=True) 
Invoice.__table__.drop(engine, checkfirst=True) 

relationship directive in Invoice tells ORM that Invoice class itself should be linked to customer class. ForeignKey('') used to determine nature of linkage. Additional directive placed on customer under Customer.INVOICE. With this mapping customer.invoice refers to customer list of invoices and invoice.customer reference to customer instance. 
Use of OuterJoin, subqueryload 

c1 = Customer(id=2, name = "Gopal Krishna", address = "Bank Street Hyarebad", email = "") 

c1.INVOICE = [Invoice(id=3, invno = 10, amount = 15000), Invoice(id=4, invno = 14, amount = 3850)] 
from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind = engine) 
session = Session() 
c2 = [ 
      id = 3, 
      name = "Govind Pant", 
      address = "Gulmandi Aurangabad", 
      email = "", 
      INVOICE = [Invoice(id=5,invno = 3, amount = 10000), 
      Invoice(id=6,invno = 4, amount = 5000)] 
rows = [ 
      id = 4, 
      name = "Govind Kala", 
      address = "Gulmandi Aurangabad", 
      email = "", 
      INVOICE = [Invoice(id=7,invno = 7, amount = 12000), Invoice(id=8,invno = 8, amount = 18500)]), 
      id = 5, 
      name = "Abdul Rahman", 
      address = "Rohtak", 
      email = "", 
      INVOICE = [Invoice(id=9,invno = 9, amount = 15000), 
      Invoice(id=10,invno = 11, amount = 6000) 
for c, i in session.query(Customer, Invoice).filter( == Invoice.custid).all(): 
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(,, i.invno, i.amount)) 
result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500) 
for row in result: 
   for inv in row.INVOICE: 
      print (,, inv.invno, inv.amount) 
from sqlalchemy.sql import func 
stmt = session.query( 
   Invoice.custid, func.count('*').label('invoice_count') 
for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, == stmt.c.custid).order_by( 
   print(, count) 
s = session.query(Customer).filter(Invoice.invno.__eq__(12)) 
s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit')) 
s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5])) 
s = session.query(Customer).filter(Customer.INVOICE.any(Invoice.invno==11)) 
from sqlalchemy.orm import subqueryload 
c1 = session.query(Customer).options(subqueryload(Customer.INVOICE)).filter_by(name = 'Govind Pant').one() 
x = session.query(Customer).get(2) 
print (Invoice.custid.foreign_keys)