Polyhedra® Polyhedra Python DB-API

Enea Polyhedra Ltd

Copyright notice Copyright © Enea Software AB 2017. Except to the extent expressly stipulated in any software license agreement covering this User Documentation and/or corresponding software, no part of this User Documentation may be reproduced, transmitted, stored in a retrieval system, or translated, in any form or by any means, without the prior written permission of Enea Software AB. However, permission to print copies for personal use is hereby granted.

Disclaimer The information in this User Documentation is subject to change without notice, and unless stipulated in any software license agreement covering this User Documentation and/or corresponding software, should not be construed as a commitment of Enea Software AB.

Trademarks Enea®, Enea OSE® and Polyhedra® are the registered trademarks of Enea AB and its subsidiaries. Enea OSE®ck, Enea OSE® Epsilon, Enea® Element, Enea® Optima, Enea® LINX, Enea® Accelerator, Polyhedra® Flash DBMS, Enea® dSPEED, Accelerating Network Convergence™, Device Software Optimized™ and Embedded for Leaders™ are unregistered trademarks of Enea AB or its subsidiaries. Any other company, product or service names mentioned in this document are the registered or unregistered trademarks of their respective owner.

Manual Details Manual title

Polyhedra Python DB-API

Document number

manuals/polydbapi

Revision number

9.3.0

Revision Date

28th November 2017

Software Version This documentation corresponds to the following version of the software: Version

9.3

2

PREFACE This document describes the Polyhedra Python DB-API. The manual is aimed at Polyhedra application writers. It is recommended that the reader also see the Polyhedra Overview, Polyhedra User's Guide and Real-time Relational Database prior to reading this document. It is assumed that the reader has knowledge of Python, relational databases and SQL.

3

CONTENTS 1.

INTRODUCTION................................................................................................................... 7 1.1 System Requirements .................................................................................................... 7 1.2 Polyhedra Requirements................................................................................................ 7 1.3 Limitations ................................................................................................................... 7 1.4 Examples ...................................................................................................................... 7

2.

INSTALLATION AND USAGE............................................................................................. 8 2.1 Installing the Polyhedra Python DB-API ....................................................................... 8 2.2 Using the Polyhedra Python DB-API............................................................................. 8

3.

POLYHEDRA SPECIFIC FEATURES ................................................................................. 9 3.1 Event handling ............................................................................................................. 9 3.2 Fault Tolerance ........................................................................................................... 10 Handler.disconnected(self, connection)....................................................................... 10 Handler.modechange(self, connection) ....................................................................... 10 3.3 Active Queries ............................................................................................................ 10 Handler.inserted(self, row).......................................................................................... 11 Handler.updated(self, row).......................................................................................... 11 Handler.deleted(self, row) ........................................................................................... 11 Handler.reordered(self, cursor) .................................................................................. 11 Handler.changed(self, cursor) ..................................................................................... 11

4.

MODULE INTERFACE ...................................................................................................... 12 4.1 Constructors................................................................................................................ 12 Polydbapi.connect(connect_string = '', timeout = 0, handler = None) ........................ 12 4.2 Globals ....................................................................................................................... 12 4.3 Exceptions .................................................................................................................. 12

5.

CONNECTION OBJECTS .................................................................................................. 13 5.1 Connection methods.................................................................................................... 13 Connection.close() ...................................................................................................... 13 Connection.commit() ................................................................................................... 13 Connection.rollback() ................................................................................................. 13 Connection.cursor(handler = None)............................................................................ 13 Connection.setautocommit(autocommit)...................................................................... 13 Connection.ftmode().................................................................................................... 13 Connection.connectionname() ..................................................................................... 13 Connection.events(timeout) ......................................................................................... 14

6.

CURSOR OBJECTS ............................................................................................................ 15 6.1 Cursor attributes ......................................................................................................... 15 Cursor.arraysize ......................................................................................................... 15 Cursor.description ...................................................................................................... 15 Cursor.rowcount ......................................................................................................... 16 6.2 Cursor methods ........................................................................................................... 16 Cursor.close() ............................................................................................................. 16 Cursor.execute(command, params = None)................................................................. 16 Cursor.executemany(command, params_list = [None]) .............................................. 16 Cursor.fetchone() ........................................................................................................ 16 Cursor.fetchall() ......................................................................................................... 16 Cursor.fetchmany(number = None) ............................................................................. 16

5

Cursor.setinputsizes(sizes) .......................................................................................... 16 Cursor.setoutputsize(size, column = None).................................................................. 17

6

Polyhedra Python DB-API

Introduction

1. Introduction This manual describes the Polyhedra Python DB-API. Polyhedra Python DB-API allows Python applications to access data stored in a Polyhedra database via the Polyhedra ODBC driver and can be used with any platform for which there is a Polyhedra ODBC driver. The Polyhedra Python DB-API is based on the Python Database API Specification 2.0 which is defined to encourage similarity between the Python modules that are used to access databases. In addition to the standard features of Python DB-API the Polyhedra Python DB-API also provides support for Polyhedra specific features such as fault tolerance and active queries.

1.1 System Requirements The Polyhedra Python DB-API requires the following: A system using Python version 2 or 3. The Polyhedra Python DB-API is currently tested using the minimum versions of Python 2.7.10 and Python 3.2.3. Windows supporting ODBC or Linux with unixODBC installed, for which a Polyhedra ODBC driver is provided. The version of the Polyhedra ODBC driver must be at least version 9.1 and it is recommend that the same versions of the Polyhedra ODBC driver and Python DB-API are used.

1.2 Polyhedra Requirements The Polyhedra Python DB-API requires the Polyhedra ODBC driver running on Windows, Linux x86 or Linux x64. It also requires a running Polyhedra database to connect to, though this need not be on the same platform as the ODBC driver.

1.3 Limitations Polyhedra Python DB-API does not support the following features: Multiple result sets: A transaction will only return a single result set and the interface does not support the Python Database API Specification 2.0 Cursor.nextset () method. It is not possible to perform queries in manual-commit mode, auto-commit mode must be used. However, manual-commit mode can be switched from and back to before and after performing a query providing care is taken to commit statements executed in manual-commit mode before switching to auto-commit mode. Calling stored procedures: Cursor.callproc() from the Python Database API Specification 2.0 is not supported.

1.4 Examples There are examples in the development release kit that illustrate how to use Polyhedra Python DBAPI. These are Python script versions of the active, animate, monitor, persist, query and transact clients that exist for other Polyhedra APIs, called pyactive.py, pyanimate.py, pymonitor.py, pypersist.py and pyquery.py. Details of how to use these example applications can be found in the Polyhedra Evaluation Guide.

7

Installation and usage

Polyhedra Python DB-API

2. Installation and usage This section describes how to install Polyhedra Python DB-API.

2.1 Installing the Polyhedra Python DB-API Polyhedra Python DB-API is supplied in the standard Polyhedra release kit in the form of a single Python source file, polydbapi.py, which can be found under the Python sub-directory of the installed kit, for example in poly9.1/python. This script must be able to access the Polyhedra ODBC driver on the same machine. On Linux this will mean setting the LD_LIBRARY_PATH environment variable to include the path to the Polyhedra ODBC driver so that the Polyhedra Python DB-API can find it. On Windows the OBDC driver must be findable in the normal search path.

2.2 Using the Polyhedra Python DB-API To connect to a running Polyhedra RTRDB using the Polyhedra Python DB-API you will need to write a Python script that imports polydbapi.py and creates a connection object with the appropriate parameters to make the connection. That object can then be used to create a cursor and perform database operations using that cursor. For example: import polydbapi connection = polydbapi.connect("SERVICE={8001}") cursor = connection.cursor() cursor.execute("select * from currency") rows = cursor.fetchall() for row in rows: print("Currency code: %s: 1 US Dollar buys %.2f %s %s." % (row["code"], float(row["usdollar"]), row["country"], row["name"])) cursor.execute("create table t(id integer primary key)") connection.setautocommit(False) cursor.execute("insert into t (id) values(?)", [1]) cursor.execute("insert into t (id) values(?)", [2]) cursor.execute("insert into t (id) values(?)", [3]) cursor.commit() cursor.close() connection.close()

8

Polyhedra Python DB-API

Polyhedra Specific Features

3. Polyhedra Specific Features This section describes features and behaviours that are specific to Polyhedra Python DB-API.

3.1 Event handling In order to access Polyhedra events via the Polyhedra Python DB-API a Polyhedra specific method, Connection.events(), is provided which calls the Polyhedra event handler for a given connection. For each relevant event an appropriate method will be called on the registered connection and query handlers if a method with the required name is defined in the handler. Note that although the Python script is polling the ODBC driver, it will not be polling the database as the event messages will have already been received asynchronously. Thus calling Connection.events() will not cause an expensive round trip to the database. Event handlers can be registered when a connection or cursor object is created as in the following example: import polydbapi class MyQueryHandler: def __init__(self, name): self.name = name self.cursor = None def setcursor(self,cursor): self.cursor = cursor def added(self, row): print(self.name, "- ROW ADDED:", row) def updated(self, row): print(self.name, "- ROW UPDATED:", row) def deleted(self, row ): print(self.name, "- ROW DELETED:", row) def reordered(self, cursor): print(self.name, "- ROWS MAY BE REORDERED:", cursor) def changed(self, cursor): print(self.name, "- CHANGED:", cursor) class MyConnectionHandler: def __init__(self,name): self.name = name def disconnected(self, connection): print(self.name, "- DISCONNECTED", connection) def modechange(self, connection): print("mode change: ", connection.ftmode()) conn_handler = MyConnectionHandler("The Connection") connection = polydbapi.connect('FT_ENABLE=yes;SERVICE={8001}', conn_handler) cursor_handler = MyQueryHandler("ACTIVE") cursor = connection.cursor(cursor_handler)

9

Polyhedra Specific Features

Polyhedra Python DB-API

cursor_handler.setcursor(cursor) while connection.connected: connection.events(100) cursor.close() connection.close()

3.2 Fault Tolerance Information about fault tolerant connections and the connection status of the database are provided by the Connection.ftmode() and Connection.connectionname() methods, as well as the connection handler modechange and disconnected events. A connection is specified as being fault tolerant by setting the value of the FT_ENABLE connection string keyword to true. Multiple alternative data services can be supplied for the connection by setting the value of the SERVICE connection string keyword to a comma separated list of data services. Other connection string keywords are provided for configuring fault tolerant (and non-fault tolerant) connections. The Polyhedra User’s Guide provides details of how to specify data services. The following optional methods may be defined for objects passed to the Polydbapi.connect() method.

Handler.disconnected(self, connection) Method called when the connection is disconnected by the server. The connection parameter identifies the newly disconnected connection. Note that the connection should be closed at the Python end as well.

Handler.modechange(self, connection) Method called when the mode of the connection changes, when the name of the connection changes or (in fault tolerant mode) when a connection is remade. The new mode can be obtained from the connection parameter using the Connection.ftmode() method and the connection name can be found using Connection.connectionname().

3.3 Active Queries Active queries are supported using the handler supplied when creating a cursor. Supplying a handler when creating a cursor enables an active query on that cursor by enabling asynchronous events for it. An active query can then be established by executing a query using that cursor. For example: cursor_handler = MyQueryHandler("ACTIVE") cursor = connection.cursor(cursor_handler) cursor_handler.setcursor(cursor) cursor.execute("select * from t order by 2") Active query updates can then be processed in Connection.events() using the query handler methods described below. Note that there is no way to update a column through an active query as is the case in other Polyehdra APIs. The following optional methods may be defined for objects passed to the Connection.cursor() method:

10

Polyhedra Python DB-API

Polyhedra Specific Features

Handler.inserted(self, row) Method called when the result set of the active query receives a new row. The row is supplied in the same format as a fetch method.

Handler.updated(self, row) Method called when a row in the result set of the active query has some changed values. The row is supplied in the same format as a fetch method.

Handler.deleted(self, row) Method called when a row is deleted from the result set of the active query. The row is supplied in the same format as a fetch method.

Handler.reordered(self, cursor) Method called when there is a change in the order of the rows in the result set of the active query. The cursor parameter identifies the active query.

Handler.changed(self, cursor) Method called when there has been one or more changes made to the result set of the active query. The cursor parameter identifies the active query. This method is called after any inserted, updated, deleted and reordered methods are called. The result set cursor will be reset before this method is called such that any Cursor.fetch() methods will access the full result set.

11

Module Interface

Polyhedra Python DB-API

4. Module Interface 4.1 Constructors Access to the database is made available through connection objects. The module provides the following constructor for these:

Polydbapi.connect(connect_string = '', timeout = 0, handler = None) The connect method returns a DB-API Connection object for a Polyhedra connection. If the connection fails then an exception is thrown. The following parameters are accepted: connect_string – A Polyhedra connection string. This string is in ODBC format and may contain parameters such as user name and password etc. timeout – The connection timeout in seconds. handler – An optional object that conforms to the Connection Handler interface. Methods of the object will be called during event handling to indicate changes to the connection. Each new connection will be created using its own newly created ODBC environment. The connection will be in auto-commit mode.

4.2 Globals The following globals are defined in accordance with the Python Database API Specification: apilevel – ―2.0‖ threadsafety – 1 - which means ―Threads may share the module but not connections‖ (and also therefore cursors). paramstyle – ―qmark‖ - which means that question mark parameters are used. The following Polyhedra specific globals are defined: version – ―XX.XX.XX.XX‖ – a string representing the Polyhedra version number.

4.3 Exceptions In addition to exceptions raised on the basis of ODBC errors, the following exceptions can be raised by the Polyhedra Python DB-API: Exception type OdbcNoLibrary OdbcLibraryError OdbcLibraryError ProgrammingError ProgrammingError ProgrammingError ProgrammingError ProgrammingError

Error text Polyhedra ODBC Driver is not found Polyhedra ODBC Driver does not support SQLWCHAR size greater than 2 Using narrow Python build with Polyhedra ODBC Driver not supported The SQL contains parameter markers, but parameters were supplied Attempt to execute active query in manual-commit mode Attempt to use fetch in manual-commit mode Attempt to use a closed connection Invalid getinfo value:

12

Polyhedra Python DB-API

Connection Objects

5. Connection Objects 5.1 Connection methods Connection.close() Close the connection and release all resources for the connection. Attempting to use a method of a related cursor object after the connection is closed will cause an exception to be raised.

Connection.commit() Commit executed transactions when in manual-commit mode.

Connection.rollback() Rollback executed transactions when in manual-commit mode.

Connection.cursor(handler = None) Return a new cursor object. An optional handler object marks the cursor as active such that the query executed using it will be an active query. The handler is an object that must conform to the query handler interface. Methods of the handler object will be called during event handling to indicate changes to the data returned by the active query.

Connection.setautocommit(autocommit) Set the connection to auto-commit or manual-commit mode. The parameter is a Boolean. When in manual-commit mode (autocommit = False): Cursor.execute() and executemany() methods append the operation to the current transaction. Cursor.fetchone(), Cursor.fetchmany() and Cursor.fetchall() methods raise an exception. Cursor.description attribute has the value None. Cursor.rowcount attribute has the value -1.

Connection.ftmode() Return an integer describing the FT mode as returned by Polyhedra specific ODBC connection attribute SQL_ATTR_POLY_FT_MODE.

Connection.connectionname() Return a string containing the name of the database currently connected to as returned by the Polyhedra specific ODBC connection attribute SQL_ATTR_POLY_CONNECTION_NAME. An

13

Connection Objects

Polyhedra Python DB-API

empty string indicates no connection (as for example when FT mode is being used and a failover is occurring).

Connection.events(timeout) This method calls the Polyhedra event handler with a timeout in milliseconds. The method will return after either some events have been received or the timeout has elapsed. A timeout of 0 indicates no timeout. For each relevant event an appropriate method will be called on the registered connection and query handlers.

14

Polyhedra Python DB-API

Cursor Objects

6. Cursor Objects 6.1 Cursor attributes Cursor.arraysize This read/write attribute specifies the number of rows to fetch at a time with fetchmany(). It defaults to 1 meaning to fetch a single row at a time.

Cursor.description This read-only attribute is a sequence of 7-item sequences. Each of these sequences contains information describing one result column: name type_code display_size internal_size precision scale null_ok This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the execute() method yet. The type_code can be interpreted by comparing it to the Python type of objects (integer, float, string etc.) The Polyhedra data types map to Python types as follows: Integer – integer Integer8 – integer Integer16 – integer Integer32 – integer Integer64 – integer Float – float. Float32 – float Varchar – string Bool – bool Binary – bytearray Datetime – datetime.datetime

15

Cursor Objects

Polyhedra Python DB-API

Cursor.rowcount This read-only attribute specifies the number of rows that the last execute(), for DML statements like UPDATE or INSERT. For the Polyhedra Python DB-API, rowcount returns 0 after a SELECT has been executed. The attribute is -1 in case no execute() has been performed on the cursor or the rowcount of the last operation cannot be determined by the interface.

6.2 Cursor methods Cursor.close() Close the cursor. The cursor will be unusable from this point forward; an Error exception will be raised if any operation is attempted with the cursor.

Cursor.execute(command, params = None) Prepare and execute a database command (an SQL statement) optionally using parameters (params). A reference to the operation will be retained by the cursor.

Cursor.executemany(command, params_list = [None]) Prepare a database command (an SQL statement) and then execute it against all parameter sequences or mappings found in the list of parameters (params_list). Use of this method for an operation which produces one or more result sets constitutes undefined behaviour.

Cursor.fetchone() Fetch the next row of a query result set, returning a single sequence, or None when no more data is available. An exception is raised if the previous call to execute() did not produce a result set or no call was issued yet or if the connection is in manual-commit mode.

Cursor.fetchall() Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). An exception is raised if the previous call to execute() did not produce a result set or no call was issued yet or if the connection is in manual-commit mode.

Cursor.fetchmany(number = None) Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available. The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor's arraysize determines the number of rows to be fetched. The method will try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned. An exception is raised if the previous call to execute() did not produce a result set or no call was issued yet or if the connection is in manual-commit mode.

Cursor.setinputsizes(sizes) This can be used before a call to execute() to predefine memory areas for the operation's parameters. sizes is specified as a sequence — one item for each input parameter. The item should be a Type

16

Polyhedra Python DB-API

Cursor Objects

Object that corresponds to the input that will be used, or it should be an integer specifying the maximum length of a string parameter. If the item is None, then no predefined memory area will be reserved for that column (this is useful to avoid predefined areas for large inputs). This method should be used before the execute() method is invoked.

Cursor.setoutputsize(size, column = None) Set a column buffer size for fetches of large columns. The column is specified as an index into the result sequence. Not specifying the column will set the default size for all large columns in the cursor. This method should be used before the execute() method is invoked.

17

Cursor Objects

Polyhedra Python DB-API

Index Active Queries, 7, 10, 11, 12, 13 Asynchronous Events, 9, 10, 14 Auto-commit, 7, 12, 13 Connection, 9, 10, 12, 13, 14 Cursor, 7, 11, 13, 15, 16, 17 Database Disconnection, 9, 10 Event Handler, 9, 10, 12, 13, 14

Fault Tolerance, 7, 9, 10, 13, 14 Manual-commit, 7, 12, 13, 16 Polyhedra ODBC driver, 7, 8, 9, 12, 13 Polyhedra Python DB-API file, 2, 8, 9 Timeout, 12, 14 Transaction, 7, 13 User, 2

18