Netezza Performance Server

 View Only

Nzalchemy - SQLAlchemy dialect for Netezza Performance Server

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

  
Introduction
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 https://www.sqlalchemy.org/


Nzalchemy
Nzalchemy is a dialect built for Netezza Performance Server to work with nzodbc and nzpy driver.  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.
With nzodbc
params= urllib.parse.quote_plus("DRIVER=<path to libnzodbc.so>;SERVER=<server>; PORT=5480; DATABASE=<db>; UID=<uid>;PWD=<pwd>")
engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)
With nzpy
engine = create_engine("netezza+nzpy://username:password@hostname:port/databasename")
How to Use It?

Install SQLAlchemy
https://pypi.org/project/SQLAlchemy/
Using nzodbc driver(with pyodbc)

Using nzpy driver

Install Netezza SQLAlchemy(nzalchemy):  The Netezza SQLAlchemy package can be installed from the public PyPI repository using pip
https://pypi.org/project/nzalchemy/

Example: Using nzodbc driver

Connection Parameters To connect to Netezza with nzalchemy using nzodbc use the following connection string          
netezza+pyodbc:///?<ODBC connection parameters>

import urllib 



params= urllib.parse.quote_plus("DRIVER=/nzscratch/lib64/libnzodbc.so;SERVER=testserver1.rtp.raleigh.ibm.com;PORT=5480;DATABASE=testdb;UID=testuser1;



PWD=0123456")



engine = create_engine("netezza+pyodbc:///?odbc_connect=%s" % params,  echo=True)

In above example, libnzodbc.so 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 testserver1.rtp.raleigh.ibm.com.
Example: Using nzpy driver

Connection Parameters To connect to Netezza with nzalchemy using nzpy use the following connection string          
netezza+nzpy://username:password@hostname:port/databasename

engine = create_engine("netezza+nzpy://admin:password@localhost:5480/db1")

In order to pass any nzpy connection arguments to nzalchemy use below:

import nzpy

def creator():
    return nzpy.connect(user="admin", password="password",host='localhost', port=5480, database="db1", securityLevel=0,logOptions=nzpy.LogOptions.Logfile, char_varchar_encoding='utf8')

engine = create_engine("netezza+nzpy://", creator=creator)
For detailed installation, configuration steps and example please refer https://pypi.org/project/nzalchemy/


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.

Example:
employee = Table( 
   'employee', meta, 
   Column('id', Integer), 
   Column('name', VARCHAR(20) ), 
   Column('gender', CHAR), 
) 
meta.create_all(engine)
conn = engine.connect() 
# Insert
ins = employee.insert().values(id='21',name='jack', gender='M') 
result = conn.execute(ins)
# Update
upd = employee.update().where(id=='21').values(name='updated_name')
result = conn.execute(upd)
#select
result = conn.execute(select([func.max(employee.c.id)]))
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. 
Example:
# 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 = 'ravi@gmail.com') 
session.add(c1) 
#INSERT 
session.commit()
# 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),
                  )
meta.create_all(engine)
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(test.c.id == '2').
                                        values(name='updated_name')
delete_test_table = """ delete from test where name ='abc' """ delt = test.delete().where(test.c.name == '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=<path-to-libnzodbc.so>;SERVER=<nz-running-server>; 
PORT=5480;DATABASE=<dbname>;UID=<usr>;PWD=<password>")
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

meta.create_all(engine)
#conn for insert and select
conn = engine.connect()
#Insert 
conn.execute(test.insert(),[
                        {'id':2,'name':'xyz','gender':'F'},
                        {'id':3,'name':'abc','gender':'M'},
                        ]
                )

Select from table

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

Update with where clause

#Update
updt = test.update().where(test.c.id == '2').values(name='updated_name')
conn.execute(updt)
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(test.c.name == 'abc')
conn.execute(delt)
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=<path-to-libnzodbc.so>;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('STUDENTS.id')), 
   Column('postal_add', VARCHAR(25)), 
   Column('email_add', VARCHAR(25)) 
) 
meta.drop_all(engine) 
meta.create_all(engine) 
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':'ravi@gmail.com'}, 
   {'id':2,'st_id':14, 'postal_add':'ChurchGate Mumbai', 'email_add':'kapoor@gmail.com'}, 
])

Join on student id

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

UNION, EXCEPT and INTERSECTION

#UNION 
from sqlalchemy import union, union_all, except_, intersect 
u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), 
addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))) 
result = conn.execute(u) 
result.fetchall() 
u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))) 
result = conn.execute(u) 
#EXCEPT 
u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), 
addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))) 
result = conn.execute(u) 
#INTERSECT 
u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))) 
result = conn.execute(u)

3. Accessing 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, 
Column('objid',nz.OID), 
Column('owner',nz.NAME), 
Column('createdate',nz.ABSTIME), 
Column('description',nz.TEXT), 
)
...
data = conn.execute(TEST.select().limit(10)).fetchall() 
for row in data: 
     print (row)

4. Get 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=<path-to-libnzodbc.so>;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('CUSTOMER.id')) 
   invno = Column(Integer) 
   amount = Column(Integer) 
   customer = relationship("Customer", back_populates = "INVOICE") 
Customer.INVOICE = relationship("Invoice", order_by = Invoice.id, back_populates = "customer") 
Customer.__table__.drop(engine, checkfirst=True) 
Invoice.__table__.drop(engine, checkfirst=True) 
Base.metadata.create_all(engine) 

relationship directive in Invoice tells ORM that Invoice class itself should be linked to customer class. ForeignKey('CUSTOMER.id') 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 = "gk@gmail.com") 

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() 
session.add(c1) 
session.commit() 
c2 = [ 
   Customer( 
      id = 3, 
      name = "Govind Pant", 
      address = "Gulmandi Aurangabad", 
      email = "gpant@gmail.com", 
      INVOICE = [Invoice(id=5,invno = 3, amount = 10000), 
      Invoice(id=6,invno = 4, amount = 5000)] 
   ) 
] 
rows = [ 
   Customer( 
      id = 4, 
      name = "Govind Kala", 
      address = "Gulmandi Aurangabad", 
      email = "kala@gmail.com", 
      INVOICE = [Invoice(id=7,invno = 7, amount = 12000), Invoice(id=8,invno = 8, amount = 18500)]), 
   Customer( 
      id = 5, 
      name = "Abdul Rahman", 
      address = "Rohtak", 
      email = "abdulr@gmail.com", 
      INVOICE = [Invoice(id=9,invno = 9, amount = 15000), 
      Invoice(id=10,invno = 11, amount = 6000) 
   ]) 
] 
session.add_all(c2) 
session.commit() 
session.add_all(rows) 
session.commit() 
for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all(): 
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount)) 
result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500) 
for row in result: 
   for inv in row.INVOICE: 
      print (row.id, row.name, inv.invno, inv.amount) 
from sqlalchemy.sql import func 
stmt = session.query( 
   Invoice.custid, func.count('*').label('invoice_count') 
).group_by(Invoice.custid).subquery() 
for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id): 
   print(u.name, 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) 
session.delete(x) 
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count() 
print (Invoice.custid.foreign_keys)

#NetezzaPerformanceServer
0 comments
38 views

Permalink