nzpy is a pure-Python IBM Netezza driver that complies with DB-API 2.0. It is tested on Python versions 3.5+. It is supported with NPS 11.1.2.x and later. Although nzpy works with older NPS versions but it do not support few features such as external table, parameter style query etc.
To install nzpy using pip type:
pip install nzpy
To install nzpy using setup.py:
python setup.py install
This examples make use of the nzpy extensions to the DB-API 2.0 standard,
Import nzpy, connect to the database, create a table, add some rows and then query the table:
import nzpy
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480, database="db1", securityLevel=1,logLevel=0)
with conn.cursor() as cursor:
try:
cursor.execute("create table customerAddress(Id int, Name varchar(10), Address varchar(50), Email varchar(20) )")
print("Table customerAddress created successfully")
except Exception as e:
print(str(e))
#insert data using parameters marker
cursor.execute("insert into customerAddress values (?,?,?,?)", (1,'Jack','High street, London', 'jack4321@ibm.com'))
print(cursor.rowcount, 'rows inserted')
cursor.execute("insert into customerAddress values (?,?,?,?)", (2,'Tom', 'Park street, NY','tom1234@ibm.com'))
print(cursor.rowcount, 'rows inserted')
cursor.execute("insert into customerAddress values (?,?,?,?)", (3,'James', 'MG street, SG','james678@ibm.com'))
print(cursor.rowcount, 'rows inserted')
# Using parameters (IMPORTANT: YOU SHOULD USE TUPLE TO PASS PARAMETERS)
# Python note: a tuple with just one element must have a trailing comma, otherwise is just a enclosed variable
cursor.execute("select * from customerAddress where Id = ? and Name = ?", (1,'Jack'))
results = cursor.fetchall()
for c1,c2,c3,c4 in results:
print("Id = %s" % (c1))
print("Name = %s" % (c2))
print("Address = %s" % (c3))
print("Email = %s" % (c4))
try:
cursor.execute("create table customerData(Id int, FirstName varchar(20), LastName varchar(20), Age int)")
print("Table customerData created successfully")
except Exception as e:
print(str(e))
#insert data using parameters marker in customerData table
cursor.execute("insert into customerData values (?,?,?,?)", (1,'Jack','Bentley', 42))
print(cursor.rowcount, 'rows inserted')
cursor.execute("insert into customerData values (?,?,?,?)", (2,'Tom', 'Banks',28))
print(cursor.rowcount, 'rows inserted')
cursor.execute("insert into customerData values (?,?,?,?)", (3,'James', 'Grant',30))
print(cursor.rowcount, 'rows inserted')
cursor.execute("select ca.Id,cd.FirstName, cd.LastName, cd.Age, ca.Address, ca.Email from customerAddress ca, customerData cd where ca.Id = ? and ca.Id = cd.Id", (2,))
results = cursor.fetchall()
for c1,c2,c3,c4,c5,c6 in results:
print("Id = %s" % (c1))
print("FirstName = %s" % (c2))
print("LastName = %s" % (c3))
print("Age = %s" % (c4))
print("Address = %s" % (c5))
print("Email = %s" % (c6))
# rowcount before
cursor.execute("select count(*) from customerAddress")
results = cursor.fetchall()
for c1 in results:
print("Table row count is %s" % (c1))
# using remotesource 'python', create named external table and unload table data
try:
cursor.execute("create external table et1 '/tmp/et10' using ( remotesource 'python' delimiter '|') as select * from customerAddress")
print("Create external table created successfully")
except Exception as e:
print(str(e))
# load data from external table onto user table
try:
cursor.execute("insert into customerAddress select * from external '/tmp/et10' using ( remotesource 'python' delimiter '|' socketbufsize 8388608 ctrlchars 'yes' encoding 'internal' timeroundnanos 'yes' crinstring 'off' maxerrors 3 LogDir '/tmp')")
print("External Table loaded successfully")
except Exception as e:
print(str(e))
# rowcount after load from external table
cursor.execute("select count(*) from customerAddress")
results = cursor.fetchall()
for c1 in results:
print("After load from External Table, table row count is %s" % (c1))
As autocommit is on by default in IBM Netezza the default value of autocommit is on. It can be turned off by using the autocommit property of the connection.
conn.autocommit = False #autocommit is on by default. It can be turned off by using the autocommit property of the connection.
with conn.cursor() as cursor:
cursor.execute("create table t2(c1 numeric (10,5), c2 varchar(10),c3 nchar(5))")
cursor.execute("insert into t2 values (123.54,'xcfd','xyz')")
conn.rollback()
IBM Netezza notices are stored in a deque called cursor.notices and added using the append() method. Here’s an example:
with conn.cursor() as cursor:
cursor.execute("call CUSTOMER();")
print(cursor.notices)
The customer name is alpha
If backend returns multiple notices:
with conn.cursor() as cursor:
cursor.execute("call CUSTOMER();")
for notice in cursor.notices:
print(notice)
The customer name is alpha
The customer location is beta
You can set logLevel to control logging verbosity. In order to enable logging, you need to pass logLevel in your application using connection string.
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480,
database="db1", securityLevel=0, logLevel=logging.DEBUG)
In addition there are 3 more options to control logging. One or more of these can be specified using logOptions argument to nzpy.connect
This is nzpy.LogOptions.Inherit option. The logging from nzpy is propgated to the logging configured by the parent
logging.basicConfig(filename="myapplication.log")
logging.info("...")
# ..
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480,
database="db1", securityLevel=0,
logOptions=nzpy.LogOptions.Inherit)
# .. all of nzpy logs will go to the inherited log settings
This is nzpy.LogOptions.Logfile option. The logging from nzpy is sent to 'nzpy.log' in the current directory. The file is rotated after 10 G. If nzpy.LogOptions.Inherit is set as well then both are honored
logging.basicConfig(filename="myapplication.log")
logging.info("...")
# ..
conn1 = nzpy.connect(user="admin", password="password",host='localhost', port=5480,
database="db1", securityLevel=0,
logOptions=nzpy.LogOptions.Logfile)
# .. all of conn1's nzpy logs will go to the nzpy.log only
conn2 = nzpy.connect(user="admin", password="password",host='localhost', port=5480,
database="db1", securityLevel=0,
logOptions=nzpy.LogOptions.Logfile | nzpy.LogOptions.Inherit)
# .. all of conn2's nzpy logs will go to the nzpy.log _and_ to myapplication.log
conn3 = nzpy.connect(user="admin", password="password",host='localhost', port=5480,
database="db1", securityLevel=0,
logOptions=nzpy.LogOptions.Disabled)
# .. conn3's logging is completely disabled
This is nzpy.LogOptions.Disabled option
You can configure logLevel as per your requirement. Any levels in standard logging module can be used. The default is logging.INFO
The level of security (SSL/TLS) that the driver uses for the connection to the data store.
onlyUnSecured: The driver does not use SSL.
preferredUnSecured: If the server provides a choice, the driver does not use SSL.
preferredSecured: If the server provides a choice, the driver uses SSL.
onlySecured: The driver does not connect unless an SSL connection is available.
Similarly, IBM Netezza server has above securityLevel.
Cases which would fail :
Below is an example how you could pass securityLevel and ca certificate in connection string:
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480, database="db1", securityLevel=3, logLevel=0, ssl = {'ca_certs' : '/nz/cacert.pem'})
Below are the securityLevel you can pass in connection string :
0: Preferred Unsecured session
1: Only Unsecured session
2: Preferred Secured session
3: Only Secured session
Use connect to create a database connection with connection parameters:
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480, database="db1", securityLevel=3, logLevel=0, ssl = {'ca_certs' : '/nz/cacert.pem'})
The above example opens a database handle on localhost. nzpy driver should connect on port 5480(postgres port). The user is admin, password is password, database is db1 and the location of the ca certificate file is /nz/cacert.pem with securityLevel as 'Only Secured session'
Connection Parameters When establishing a connection using nzgo you are expected to supply a connection string containing zero or more parameters. Below are subset of the connection parameters supported by nzgo. The following special connection parameters are supported:
As autocommit is on by default in IBM Netezza the default value of autocommit is on. It can be turned off by using the autocommit property of the connection.
conn.autocommit = False #This would internally called 'begin'
with conn.cursor() as cursor:
cursor.execute("create table t2(c1 numeric (10,5), c2 varchar(10),c3 nchar(5))")
conn.commit() # This will commit create table transaction
cursor.execute("insert into t2 values (123.54,'xcfd','xyz')")
conn.rollback() # This will rollback insert into table transaction
This package returns the following types for values from the IBM Netezza backend:
nzpy do not support all the DB-API parameter styles. It only supports qmark style. Here’s an example of using the 'qmark' parameter style:
with conn.cursor() as cursor:
cursor.execute("select * from t1 where c3 = ? and c2 = ?", ('abc','abcd'))
results = cursor.fetchall()
for c1, c2, c3 in results:
print("c1 = %s, c2 = %s, c3 = %s" % (c1, c2, c3))
User can provide encoding for char/varchar datatype from application. Default value is 'latin'. For example, user can set nzpy to support 'UTF8' encoding for char and varchar by passing char_varchar_encoding='utf8' in connection parameter like below:
conn = nzpy.connect(user="admin", password="password",host='localhost', port=5480, database="db1", securityLevel=1,logLevel=0, char_varchar_encoding='utf8')
You can unload data from an IBM Netezza database table on a Netezza host system to a remote client. This unload does not remove rows from the database but instead stores the unloaded data in a flat file (external table) that is suitable for loading back into a Netezza database. Below query would create a file 'et1.txt' on remote system from Netezza table t1 with data delimeted by '|'.
cursor.execute("create external table et1 'C:\\et1.txt' using ( remotesource 'python' delimiter '|') as select * from t1")
To run tests, go to tests folder and run :
pytest -xv *
To run individual tests, you can run :
pytest -xv test_connection.py
All bug reports, feature requests and contributions are welcome at http://github.com/IBM/nzpy
If you have any questions or issues you can create a new issue here.
Pull requests are very welcome! Make sure your patches are well tested. Ideally create a topic branch for every separate change you make. For example:
git checkout -b my-new-feature)git commit -am 'Added some feature')git push origin my-new-feature)If you would like to see the detailed LICENSE click here.
Copyright:: 2019-2020 IBM, Inc
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
FAQs
IBM Netezza python driver
We found that nzpy demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 3 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.
Security News
The Ultralytics' PyPI Package was compromised four times in one weekend through GitHub Actions cache poisoning and failure to rotate previously compromised API tokens.
Security News
Research
Socket researchers found a malicious Maven package impersonating the legitimate ‘XZ for Java’ library, introducing a backdoor for remote code execution.