# nzpy: Pure python driver for IBM Netezza
## Scope
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.
## Installation
To install nzpy using pip type:
```shell
pip install nzpy
```
To install nzpy using setup.py:
```shell
python setup.py install
```
## Interactive Example
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:
```python
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))
```
## Autocommit
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.
```python
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()
```
## Notices
IBM Netezza notices are stored in a deque called cursor.notices and added using the append() method. Here’s an example:
```python
with conn.cursor() as cursor:
cursor.execute("call CUSTOMER();")
print(cursor.notices)
The customer name is alpha
```
If backend returns multiple notices:
```python
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
```
## Logging
You can set logLevel to control logging verbosity. In order to enable logging, you need to pass logLevel in your application using connection string.
```python
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`
1. Inherit the logging settings of the parent / caller
This is `nzpy.LogOptions.Inherit` option. The logging from nzpy is propgated to the logging configured by the parent
```python
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
```
2. Logging details to a logfile
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
```python
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
```
3. Disable nzpy logging
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`
## SecurityLevel
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 :
- Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Only Unsecured' mode
- Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Preferred Unsecured' mode
- Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Only Secured' mode
- Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Preferred Secured' mode
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
```
## Connection String
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:
- database - The name of the database to connect to
- user - The user to sign in as
- password - The user's password
- host - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)
- port - The port to bind to.
- securityLevel - Whether or not to use SSL (default is 0)
- ssl - Python dictionary containing location of the root certificate file. The file must contain PEM encoded data.
## Transactions
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
```
## Supported Data Types
This package returns the following types for values from the IBM Netezza backend:
- integer types byteint, smallint, integer, and bigint are returned as int
- floating-point types real and double precision are returned as float
- character types char, varchar, nchar and nvarchar are returned as string
- temporal types date, time, timetz, timestamp, interval and timestamptz are
returned as string
- numeric and geometry are returned as string
- the boolean type is returned as bool
- JSON, JSONB and JSONPATH datatypes is returned as string
## Parameter Style
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))
```
## Encoding
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')
```
## External table
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")
```
## Features
- SSL/TLSv1.2 crypto support
- Transaction support: begin, rollback, commit
- Full support for all IBM Netezza data types
- Support JSON, JSONB and JSONPATH datatypes
- Full DDL, DML query syntax support for IBM Netezza
- Full external table support (load and unload)
- Configurable logging feature
- Parameter style support
## Tests
To run tests, go to tests folder and run :
``` pytest -xv * ```
To run individual tests, you can run :
``` pytest -xv test_connection.py ```
## Contribution and help
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][issues].
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:
1. Fork the repo (git clone https://github.com/IBM/nzpy.git)
2. Create your feature branch (`git checkout -b my-new-feature`)
3. Commit your changes (`git commit -am 'Added some feature'`)
4. Push to the branch (`git push origin my-new-feature`)
5. Create new Pull Request
6. Assign any one of the reviewers:
- abhishekjog
- sandippawar1412
- shabbir10july
<!-- License and Authors is optional here, but gives you the ability to highlight who is involed in the project -->
## License
If you would like to see the detailed LICENSE click [here](LICENSE).
```text
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.
```
[issues]: https://github.com/IBM/nzpy/issues/new
Raw data
{
"_id": null,
"home_page": "https://github.com/ibm/nzpy",
"name": "nzpy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.5",
"maintainer_email": null,
"keywords": "Netezza dbapi",
"author": "IBM",
"author_email": "shabmoh3@in.ibm.com",
"download_url": "https://files.pythonhosted.org/packages/01/f3/fe4708a202c4a884b72df70364e930c3bcc2d4776f2f12d938dfec638666/nzpy-1.17.tar.gz",
"platform": null,
"description": "# nzpy: Pure python driver for IBM Netezza\n\n## Scope\n\nnzpy 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.\n\n## Installation\nTo install nzpy using pip type:\n```shell\npip install nzpy\n```\n\nTo install nzpy using setup.py:\n```shell\npython setup.py install\n```\n\n\n## Interactive Example\nThis examples make use of the nzpy extensions to the DB-API 2.0 standard,\n\nImport nzpy, connect to the database, create a table, add some rows and then query the table:\n\n```python\nimport nzpy\n\nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480, database=\"db1\", securityLevel=1,logLevel=0)\n\nwith conn.cursor() as cursor:\n try:\n cursor.execute(\"create table customerAddress(Id int, Name varchar(10), Address varchar(50), Email varchar(20) )\")\n print(\"Table customerAddress created successfully\")\n except Exception as e:\n print(str(e))\n \n #insert data using parameters marker \n cursor.execute(\"insert into customerAddress values (?,?,?,?)\", (1,'Jack','High street, London', 'jack4321@ibm.com'))\n print(cursor.rowcount, 'rows inserted')\n cursor.execute(\"insert into customerAddress values (?,?,?,?)\", (2,'Tom', 'Park street, NY','tom1234@ibm.com'))\n print(cursor.rowcount, 'rows inserted')\n cursor.execute(\"insert into customerAddress values (?,?,?,?)\", (3,'James', 'MG street, SG','james678@ibm.com'))\n print(cursor.rowcount, 'rows inserted')\n\n # Using parameters (IMPORTANT: YOU SHOULD USE TUPLE TO PASS PARAMETERS)\n # Python note: a tuple with just one element must have a trailing comma, otherwise is just a enclosed variable\n cursor.execute(\"select * from customerAddress where Id = ? and Name = ?\", (1,'Jack'))\n results = cursor.fetchall()\n for c1,c2,c3,c4 in results:\n print(\"Id = %s\" % (c1))\n print(\"Name = %s\" % (c2))\n print(\"Address = %s\" % (c3))\n print(\"Email = %s\" % (c4))\n\n try:\n cursor.execute(\"create table customerData(Id int, FirstName varchar(20), LastName varchar(20), Age int)\")\n print(\"Table customerData created successfully\")\n except Exception as e:\n print(str(e))\n \n #insert data using parameters marker in customerData table\n cursor.execute(\"insert into customerData values (?,?,?,?)\", (1,'Jack','Bentley', 42))\n print(cursor.rowcount, 'rows inserted')\n cursor.execute(\"insert into customerData values (?,?,?,?)\", (2,'Tom', 'Banks',28))\n print(cursor.rowcount, 'rows inserted')\n cursor.execute(\"insert into customerData values (?,?,?,?)\", (3,'James', 'Grant',30))\n print(cursor.rowcount, 'rows inserted')\n\n 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,))\n results = cursor.fetchall()\n for c1,c2,c3,c4,c5,c6 in results:\n print(\"Id = %s\" % (c1))\n print(\"FirstName = %s\" % (c2))\n print(\"LastName = %s\" % (c3))\n print(\"Age = %s\" % (c4))\n print(\"Address = %s\" % (c5))\n print(\"Email = %s\" % (c6))\n \n # rowcount before\n cursor.execute(\"select count(*) from customerAddress\")\n results = cursor.fetchall()\n for c1 in results:\n print(\"Table row count is %s\" % (c1))\n \n # using remotesource 'python', create named external table and unload table data \n try:\n cursor.execute(\"create external table et1 '/tmp/et10' using ( remotesource 'python' delimiter '|') as select * from customerAddress\")\n print(\"Create external table created successfully\") \n except Exception as e:\n print(str(e))\n\n # load data from external table onto user table\n try:\n 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')\")\n print(\"External Table loaded successfully\") \n except Exception as e:\n print(str(e))\n\n # rowcount after load from external table\n cursor.execute(\"select count(*) from customerAddress\")\n results = cursor.fetchall()\n for c1 in results:\n print(\"After load from External Table, table row count is %s\" % (c1))\n\n```\n\n## Autocommit\nAs 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.\n\n```python\nconn.autocommit = False #autocommit is on by default. It can be turned off by using the autocommit property of the connection.\nwith conn.cursor() as cursor:\n cursor.execute(\"create table t2(c1 numeric (10,5), c2 varchar(10),c3 nchar(5))\")\n cursor.execute(\"insert into t2 values (123.54,'xcfd','xyz')\")\n conn.rollback()\n```\n\n## Notices\nIBM Netezza notices are stored in a deque called cursor.notices and added using the append() method. Here\u2019s an example:\n\n```python\nwith conn.cursor() as cursor:\n cursor.execute(\"call CUSTOMER();\")\n print(cursor.notices)\n\nThe customer name is alpha\n\n```\nIf backend returns multiple notices:\n\n```python\nwith conn.cursor() as cursor:\n cursor.execute(\"call CUSTOMER();\")\n for notice in cursor.notices:\n print(notice)\n\nThe customer name is alpha\nThe customer location is beta\n\n```\n\n## Logging \nYou can set logLevel to control logging verbosity. In order to enable logging, you need to pass logLevel in your application using connection string. \n\n```python\nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480,\n database=\"db1\", securityLevel=0, logLevel=logging.DEBUG)\n```\n\nIn addition there are 3 more options to control logging. One or more of these can be specified using `logOptions` argument to `nzpy.connect`\n\n1. Inherit the logging settings of the parent / caller \n\nThis is `nzpy.LogOptions.Inherit` option. The logging from nzpy is propgated to the logging configured by the parent\n\n```python\nlogging.basicConfig(filename=\"myapplication.log\")\nlogging.info(\"...\")\n# .. \nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480,\n database=\"db1\", securityLevel=0,\n logOptions=nzpy.LogOptions.Inherit)\n\n# .. all of nzpy logs will go to the inherited log settings\n```\n\n2. Logging details to a logfile\n\nThis 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\n\n```python\nlogging.basicConfig(filename=\"myapplication.log\")\nlogging.info(\"...\")\n# .. \nconn1 = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480,\n database=\"db1\", securityLevel=0,\n logOptions=nzpy.LogOptions.Logfile)\n\n# .. all of conn1's nzpy logs will go to the nzpy.log only\n\nconn2 = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480,\n database=\"db1\", securityLevel=0,\n logOptions=nzpy.LogOptions.Logfile | nzpy.LogOptions.Inherit)\n\n# .. all of conn2's nzpy logs will go to the nzpy.log _and_ to myapplication.log\n\n\nconn3 = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480,\n database=\"db1\", securityLevel=0,\n logOptions=nzpy.LogOptions.Disabled)\n# .. conn3's logging is completely disabled\n\n```\n\n3. Disable nzpy logging\n\nThis is `nzpy.LogOptions.Disabled` option\n\nYou can configure logLevel as per your requirement. Any levels in standard `logging` module can be used. The default is `logging.INFO`\n\n\n## SecurityLevel \nThe level of security (SSL/TLS) that the driver uses for the connection to the data store. \n```\nonlyUnSecured: The driver does not use SSL. \npreferredUnSecured: If the server provides a choice, the driver does not use SSL. \npreferredSecured: If the server provides a choice, the driver uses SSL. \nonlySecured: The driver does not connect unless an SSL connection is available. \n```\nSimilarly, IBM Netezza server has above securityLevel. \n\nCases which would fail :\n- Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Only Unsecured' mode\n- Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Preferred Unsecured' mode\n- Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Only Secured' mode\n- Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Preferred Secured' mode \n\nBelow is an example how you could pass securityLevel and ca certificate in connection string:\n```\nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480, database=\"db1\", securityLevel=3, logLevel=0, ssl = {'ca_certs' : '/nz/cacert.pem'})\n```\nBelow are the securityLevel you can pass in connection string : \n```\n0: Preferred Unsecured session\n1: Only Unsecured session\n2: Preferred Secured session\n3: Only Secured session\n```\n\n## Connection String \nUse connect to create a database connection with connection parameters: \n```\nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480, database=\"db1\", securityLevel=3, logLevel=0, ssl = {'ca_certs' : '/nz/cacert.pem'})\n```\nThe 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' \n\n**Connection Parameters**\nWhen 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. \nThe following special connection parameters are supported: \n- database - The name of the database to connect to\n- user - The user to sign in as\n- password - The user's password\n- host - The host to connect to. Values that start with / are for unix domain sockets. (default is localhost)\n- port - The port to bind to. \n- securityLevel - Whether or not to use SSL (default is 0)\n- ssl - Python dictionary containing location of the root certificate file. The file must contain PEM encoded data.\n\n\n## Transactions \nAs 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.\n```\nconn.autocommit = False #This would internally called 'begin'\n\nwith conn.cursor() as cursor:\n cursor.execute(\"create table t2(c1 numeric (10,5), c2 varchar(10),c3 nchar(5))\")\n conn.commit() # This will commit create table transaction\n\n cursor.execute(\"insert into t2 values (123.54,'xcfd','xyz')\")\n conn.rollback() # This will rollback insert into table transaction\n```\n\n## Supported Data Types \nThis package returns the following types for values from the IBM Netezza backend: \n- integer types byteint, smallint, integer, and bigint are returned as int\n- floating-point types real and double precision are returned as float\n- character types char, varchar, nchar and nvarchar are returned as string\n- temporal types date, time, timetz, timestamp, interval and timestamptz are\n returned as string\n- numeric and geometry are returned as string\n- the boolean type is returned as bool\n- JSON, JSONB and JSONPATH datatypes is returned as string\n\n## Parameter Style\nnzpy do not support all the DB-API parameter styles. It only supports qmark style. Here\u2019s an example of using the 'qmark' parameter style:\n```\nwith conn.cursor() as cursor:\n cursor.execute(\"select * from t1 where c3 = ? and c2 = ?\", ('abc','abcd'))\n results = cursor.fetchall()\n for c1, c2, c3 in results:\n print(\"c1 = %s, c2 = %s, c3 = %s\" % (c1, c2, c3))\n```\n\n## Encoding\nUser can provide encoding for char/varchar datatype from application. Default value is 'latin'.\nFor example, user can set nzpy to support 'UTF8' encoding for char and varchar by passing char_varchar_encoding='utf8' in connection parameter like below:\n\n```\nconn = nzpy.connect(user=\"admin\", password=\"password\",host='localhost', port=5480, database=\"db1\", securityLevel=1,logLevel=0, char_varchar_encoding='utf8')\n```\n\n## External table \nYou 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. \nBelow query would create a file 'et1.txt' on remote system from Netezza table t1 with data delimeted by '|'. \n```\ncursor.execute(\"create external table et1 'C:\\\\et1.txt' using ( remotesource 'python' delimiter '|') as select * from t1\")\n```\n\n## Features\n- SSL/TLSv1.2 crypto support\n- Transaction support: begin, rollback, commit\n- Full support for all IBM Netezza data types\n- Support JSON, JSONB and JSONPATH datatypes\n- Full DDL, DML query syntax support for IBM Netezza\n- Full external table support (load and unload)\n- Configurable logging feature\n- Parameter style support\n\n\n## Tests\nTo run tests, go to tests folder and run :\n\n``` pytest -xv * ```\n\nTo run individual tests, you can run :\n\n``` pytest -xv test_connection.py ```\n\n## Contribution and help\nAll bug reports, feature requests and contributions are welcome at http://github.com/IBM/nzpy\n\nIf you have any questions or issues you can create a new [issue here][issues].\n\nPull requests are very welcome! Make sure your patches are well tested.\nIdeally create a topic branch for every separate change you make. For\nexample:\n\n1. Fork the repo (git clone https://github.com/IBM/nzpy.git)\n2. Create your feature branch (`git checkout -b my-new-feature`)\n3. Commit your changes (`git commit -am 'Added some feature'`)\n4. Push to the branch (`git push origin my-new-feature`)\n5. Create new Pull Request\n6. Assign any one of the reviewers:\n - abhishekjog\n - sandippawar1412\n - shabbir10july\n\n<!-- License and Authors is optional here, but gives you the ability to highlight who is involed in the project -->\n## License \n\nIf you would like to see the detailed LICENSE click [here](LICENSE).\n\n```text\nCopyright:: 2019-2020 IBM, Inc\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\nhttp://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n```\n\n[issues]: https://github.com/IBM/nzpy/issues/new\n\n",
"bugtrack_url": null,
"license": "IBM",
"summary": "IBM Netezza python driver",
"version": "1.17",
"project_urls": {
"Documentation": "https://github.com/IBM/nzpy/wiki",
"Homepage": "https://github.com/ibm/nzpy",
"Source": "https://github.com/IBM/nzpy",
"Tracker": "https://github.com/IBM/nzpy/issues"
},
"split_keywords": [
"netezza",
"dbapi"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "1e23d375a7dd936d2b033a473fc7c52802f763cef34824dd33f964c1c8db6987",
"md5": "aa26e1e4d369b89fecebf7dee57fe8b6",
"sha256": "45230e674656b0ce9edb659de8ff06c9e093e825add7c7e1ba5ccd36fe99356d"
},
"downloads": -1,
"filename": "nzpy-1.17-py3-none-any.whl",
"has_sig": false,
"md5_digest": "aa26e1e4d369b89fecebf7dee57fe8b6",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.5",
"size": 52363,
"upload_time": "2025-03-20T14:19:28",
"upload_time_iso_8601": "2025-03-20T14:19:28.229155Z",
"url": "https://files.pythonhosted.org/packages/1e/23/d375a7dd936d2b033a473fc7c52802f763cef34824dd33f964c1c8db6987/nzpy-1.17-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "01f3fe4708a202c4a884b72df70364e930c3bcc2d4776f2f12d938dfec638666",
"md5": "6f6a6575fe0af57401a423a4a7a2fc84",
"sha256": "98cc17b2c6cf24c816ef69205a3d85d2e740eedd1e7787641e87dc4a28aca9a3"
},
"downloads": -1,
"filename": "nzpy-1.17.tar.gz",
"has_sig": false,
"md5_digest": "6f6a6575fe0af57401a423a4a7a2fc84",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.5",
"size": 79586,
"upload_time": "2025-03-20T14:19:29",
"upload_time_iso_8601": "2025-03-20T14:19:29.603397Z",
"url": "https://files.pythonhosted.org/packages/01/f3/fe4708a202c4a884b72df70364e930c3bcc2d4776f2f12d938dfec638666/nzpy-1.17.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-03-20 14:19:29",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ibm",
"github_project": "nzpy",
"travis_ci": true,
"coveralls": false,
"github_actions": true,
"lcname": "nzpy"
}
JSON
