Python DB-API Support

iris.connect() returns a new Connection object and attempts to create a new connection to an instance of InterSystems IRIS. The object will be open if the connection was successful, or closed otherwise (see Cursor.isClosed() ).

  iris.connect(hostname,port,namespace,username,password,timeout,sharedmemory,logfile)
  iris.connect(connectionstr,username,password,timeout,sharedmemory,logfile)

The hostname, port, namespace, timeout, and logfile from the last successful connection attempt are saved as properties of the connection object.

parameters:

Parameters may be passed by position or keyword.

• hostname — str specifying the server URL

• port — int specifying the superserver port number

• namespace — str specifying the namespace on the server

• The following parameter can be used in place of the hostname, port, and namespace arguments:

  • connectionstr — str of the form hostname:port/namespace.

• username — str specifying the user name

• password — str specifying the password

• timeout (optional) — int specifying maximum number of milliseconds to wait while attempting the connection. Defaults to 10000.

• sharedmemory (optional) — specify bool True to attempt a shared memory connection when the hostname is localhost or 127.0.0.1. Specify False to force a connection over TCP/IP. Defaults to True.

• logfile (optional) — str specifying the client-side log file path. The maximum path length is 255 ASCII characters.

Connection.close() closes the connection immediately. The connection and all cursors associated with it will be unusable. An implicit rollback will be performed on all uncommitted changes made by associated cursors.

   Connection.close()

A ProgrammingError exception will be raised if any operation is attempted with a closed connection or any associated cursor.

Connection.commit() commits all SQL statements executed on the connection since the last commit/rollback. The rollback affects all changes made by any cursor using this connection. Explicit calls to this method are not required.

   Connection.commit()

Connection.rollback() rolls back all SQL statements executed on the connection that created this cursor (since the last commit/rollback). It affects all changes made by any cursor using this connection.

   Connection.rollback()

Connection.cursor() returns a new Cursor object that uses this connection.

   Connection.cursor()

Any changes made to the database by one cursor are immediately visible to all other cursors created from the same connection. Rollbacks and commits affect all changes made by any cursor using this connection.

Cursor.arraysize is a read/write attribute that specifies the number of rows to fetch at a time with fetchmany(). Default is 1 (fetch one row at a time).

Cursor.description returns a list of tuples containing information for each result column returned by the last SQL select statement. Value will be None if an execute method has not been called, or if the last operation did not return any rows.

Each tuple (column description) in the list contains the following items:

• name — column name (defaults to None)

• type_code — integer SQLType identifier (defaults to 0). See “SQLType enumeration values” for valid values.

• display_size — not used - value set to None

• internal_size — not used - value set to None

• precision — integer (defaults to 0)

• scale — integer (defaults to None)

• nullable — integer (defaults to 0)

Cursor.rowcount specifies the number of rows modified by the last SQL statement. The value will be -1 if no SQL has been executed or if the number of rows is unknown. For example, DDLs like CREATE, DROP, DELETE, and SELECT statements (for performance reasons) return -1.

Batch updates also return the number of rows affected.

Cursor.callproc() calls a stored database procedure with the given procname.

   Cursor.callproc(procname)
   Cursor.callproc(procname, parameters) 

parameters:

• procname – name of the stored procedure to call

• parameters – list of parameter values to pass to the stored procedure

Cursor.close() closes the cursor.

   Cursor.close()

A ProgrammingError exception will be raised if any operation is attempted with a closed cursor. Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling this is not usually necessary.

Cursor.execute() executes the query specified in the operation parameter. Updates the Cursor object and sets the rowcount attribute to -1 for a query or 1 for an update.

   Cursor.execute(operation)
   Cursor.execute(operation, parameters) 

parameters:

• operation – string containing SQL statement to be executed

• parameters – optional list of values. This must be a Python list (tuples or sets are not acceptable).

examples:

Parameter values are used in positions where the SQL statement contains a ? (qmark) rather than a literal or constant. If the statement does not contain any qmarks, the parameters argument is not required will raise an exception if given.

• sql = "...(1,2)..."; execute(sql)

• sql = "...(?,?)..."; params = [1,2]; execute(sql, params)

• sql = "...(1,?)..."; params = [2]; execute(sql, params)

Cursor.executemany() is used for batch inserts/updates. It prepares a database operation (query or command) and then executes it against all parameter sequences or mappings found in the sequence seq_of_parameters.

   Cursor.executemany(operation)
   Cursor.executemany(operation, seq_of_parameters)

parameters:

• operation – string containing SQL statement to be executed

• seq_of_parameters – sequence of parameter sequences or mappings

Cursor.fetchone() returns the pointer to the next ResultSetRow.DataRow object (integer array of data offsets) in the query, or None if no more data is available.

   Cursor.fetchone()

Data is fetched only on request, via indexing. The object contains a list of integer offsets that can be used to retrieve the row values. Index values must be positive integers (a value of 1 refers to column 1, and so on).

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (for example, if it was not a SELECT statement).

Cursor.fetchmany() fetches the next set of rows of a query result, returning a sequence of sequences (a list of tuples). If the size argument is not specified, the number of rows to fetch at a time is set by the Cursor.arraysize attribute (default 1). An empty sequence is returned when no more rows are available.

   Cursor.fetchmany()
   Cursor.fetchmany(size)

parameters:

• size – optional. Defaults to the current value of attribute Cursor.arraysize.

Cursor.fetchall() fetches all remaining rows of a query result.

   Cursor.fetchall()

Cursor.isClosed() is an InterSystems extension method that returns True if the cursor object is already closed, False otherwise.

Cursor.isClosed()

Cursor.nextset() is an optional DB-API method for iterating over multiple result sets. Skips to the next result set if available. Returns True if available, otherwise False (hence, should not be used to access a result set/result set row).

      Cursor.nextset()

Example:

  for row in cursor.stored_results():
    row_values = row[0] // data in all columns 
    val1 = row[1]       // data in column 1
    cursor.nextset()    // skips to the next result set if multiple result sets 
    // does nothing (or breaks out of loop) in case of single result set; 

Cursor.scroll() is an optional DB-API method that scrolls the cursor in the result set to a new position and returns the row at that position.

  Cursor.scroll(value)
  Cursor.scroll(value, mode)

Raises an IndexError if scroll operation would leave the result set.

parameters:

• value – integer value specifying the new target position.

  • If mode is relative (the default) , value is a positive or negative offset to the current position in the result set.

  • If mode is absolute, value is an absolute target position (negative values are not valid).

• mode – optional. Valid values are relative or absolute.

Example:

For each example, assume the result set has a total of 10 rows, and the initial number of rows fetched is 5. Result set index values are 0–based, so the current position in the result set is rs[4] (the 5th row).

# Scroll forward 3 rows relative to rs[4] (mode defaults to 'relative')
   datarow = Cursor.scroll(3)             // returns rs[7] (8th row in resultset)
# Scroll to absolute position 3
   datarow = Cursor.scroll(3,'absolute')  // returns rs[2]  (3rd row in resultset)
# Scroll backward 4 rows relative to rs[4] (mode defaults to 'relative')
   datarow = Cursor.scroll(-4)            // returns rs[0]
# Scroll to absolute position -4 
   datarow = Cursor.scroll(-4,'absolute') // Throws IndexError (negative row number not valid)

Cursor.setinputsizes() is not applicable to InterSystems IRIS, which does not implement or require this functionality. Throws NotImplementedError if called.

Cursor.setoutputsize() is not applicable to InterSystems IRIS, which does not implement or require this functionality. Throws NotImplementedError if called.

Cursor.stored_results() is an InterSystems extension method that returns a list iterator (containing first row of each result set) if the procedure type is 'query', and empty list if the procedure type is 'function'

   Cursor.stored_results()

Example:

  for row in cursor.stored_results(): // row is DataRow object for 1st row of result set 
    row_values = row[0] // data in all columns 
    val1 = row[1] // data in column 1

Incorrect Syntax: 
  row = cursor.stored_results() // row values not accessible using row[0] since it is a list iterator