Using the Python DB-API

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 seconds to wait while attempting the connection. Defaults to 10.

• 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 – string containing a stored procedure call with parameterized arguments.

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

Any of the fetch*() methods can be used to access the rows of a result set for a stored procedure that is expected to return result sets. They are expected to behave in the same way as for SELECT queries (non-procedures). For example, after using callproc() to call a procedure that is expected to return at least one result set, fetchone() will return the first row of the first result set and subsequent calls to fetchone() will return the remaining rows one by one. A fetchall() call will return all the remaining rows of the current result set.

For example, this code calls stored procedure Sample.SP_Sample_By_Name, specifying parameter value "A" in a list:

  cursor.callproc("CALL Sample.SP_Sample_By_Name (?)", ["A"])
  row = cursor.fetchone() 
  while row: 
    print(row.ID, row.Name, row.DOB, row.SSN) 
    row = cursor.fetchone()

Output will be similar to the following:

  167 Adams,Patricia J. 1964-10-12 216-28-1384
  28 Ahmed,Dave H. 1954-01-12 711-67-4091
  20 Alton,Samantha E. 2015-03-28 877-53-4204
  118 Anderson,Elvis V. 1994-05-29 916-13-245

If a stored procedure of 'function' type is expected to return a result set, then it will be available in the return value of callproc() as a tuple at the corresponding placeholder. An internal call to fetchall() is made in this specific case, hence, the tuple holds all the rows of the result set. The rows hold all the column data as well.

For example, the stored procedure below has two parameters whose modes are RETURN_VALUE and INPUT, respectively:

  proc = "{ ? = MyProc3(?) }"
  params = [1]
  return_args = cursor.callproc(proc, params)
  print(return_args[0]) # tuple of all the result set rows
  print(return_args[1] == None)

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 INSERT or UPDATE statement to be executed

• seq_of_parameters – sequence of parameter sequences or mappings

Returns a tuple of integers and/or strings, depending on success or failure of the INSERT or UPDATE operation. Each item in the tuple corresponds to the row in the batch which is a list/tuple of user-provided parameters. (Integers for success, strings for error messages in case of failure). Returns 1 to indicate every successful INSERT or an error message with details in case of a failure. Returns a cardinal number to indicate the number of rows that were successfully modified for an UPDATE or an error message with details in case of failure. The rowcount attribute indicates the number of rows successfully inserted/updated.

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).

Column values can be fetched using cardinal values, column name (as a string), and the slice operator, but not via dynamic attributes. For example, row[:] fetches all the column data, row[0] fetches the data in first column, row[1] fetches the data in second column, and row['<columnName>'] fetches the data from the <columnName> column, but row.columnName will not work.

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. It makes the cursor skip to the next available set, discarding any remaining rows from the current set. If there are no more sets, the method returns None. Otherwise, it returns True and subsequent calls to the fetch*() methods will return rows from the next result set.

      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. This method does not work with stored procedures. It raises an IndexError if scroll operation would leave the result set.

  Cursor.scroll(value, mode)

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. The use of an empty string for the mode argument sets its value to relative (for example, cursor.scroll(3,'')).

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).

  cursor.execute("select id, * from simple.human where id <= 10")
  cursor.fetchmany(5)

  # Scroll forward 3 rows, relative to Row 5
  datarow = cursor.scroll(3,'relative')      # Row 8
  print(datarow[0] == 8)

  # Scroll to absolute position 3
  datarow = cursor.scroll(3,'absolute')      # Row 3
  print(datarow[0] == 3)

  # Scroll backward 4 rows, relative to Row 3 (mode defaults to 'relative')
  datarow = cursor.scroll(-4,'')             # Row 9
  print(datarow[0] == 9)

  # Attempt to scroll to absolute position -4 
  # Error: Negative values with absolute scrolling are not allowed.
  datarow = cursor.scroll(-4,'absolute')     # ERROR
  print(datarow[0])

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