Skip to main content

Python DB-API Support

The InterSystems Python DB-API driver is a fully compliant implementation of the PEP 249 version 2.0 Python Database API specification. The following sections list all required implementation features, indicate the level of support for each one, and describe all InterSystems-specific features in detail:

Note:
DB-API Driver Installation

The DB-API is available when you install InterSystems IRIS. If you do not have the driver (for example, if you are connecting to IRIS Cloud from a host with no InterSystems IRIS instance) you can download the InterSystems DB-API driver from https://github.com/intersystems-community/iris-driver-distribution and install it with:

pip install intersystems_irispython-3.2.0-py3-none-any.whl

Usage

The following example makes a connection to the InterSystems IRIS database, creates a cursor associated with the connection, sets up to make some DBAPI calls, and then shuts down.

See “Connection Object” and “Cursor Object” in the following section for detailed documentation on all available DBAPI methods.

Connecting to the DB-API driver and getting a cursor
import iris

def main():
  connection_string = "localhost:1972/USER"
  username = "_system"
  password = "SYS"

  connection = iris.connect(connection_string, username, password)
  cursor = connection.cursor()

try:
  pass  # do something with DB-API calls
except Exception as ex:
  print(ex)
finally:
  if cursor:
    cursor.close()
  if connection:
    connection.close()

if __name__ == "__main__":
  main()

See iris.connect(), Connection.close(), Connection.cursor(), and Cursor.close() for more information on the methods called in this example.

PEP 249 Implementation Reference

This section lists all required implementation features described in the PEP 249 version 2.0 Python Database API specification, indicates the level of support for each one, and describes all InterSystems-specific features in detail.

Globals

These are required implementation-specific constants. In the InterSystems implementation, these globals are set to the following values:

apilevel

"2.0" — specifies compliance with PEP 249 version 2.0.

threadsafety

0 — threads may not share the module.

paramstyle

"qmark" — query parameters use question mark style (for example: WHERE name=?).

Connection Object

This section describes how to use iris.connect() to create a Connection object, and provides implementation details for required Connection methods close(), commit(), rollback(), and cursor().

Creating a Connection Object

DB-API Connection objects are created by calls to the InterSystems iris.connect() method:

connect()

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.

  • hostnamestr specifying the server URL

  • portint specifying the superserver port number

  • namespacestr specifying the namespace on the server

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

    • connectionstrstr of the form hostname:port/namespace.

  • usernamestr specifying the user name

  • passwordstr 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 Object Methods

A Connection object can be used to create one or more Cursor objects. Database changes made by one cursor are immediately visible to all other cursors created from the same connection. Rollbacks and commits affect all changes made by cursors using this connection.

close()

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.

commit()

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()
rollback()

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()
cursor()

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 Object

This section describes how to create a Cursor object, and provides implementation details for the following required Cursor methods and attributes:

Creating a Cursor object

A Cursor object is created by establishing a connection and then calling Connection.cursor(). For example:

  connection = iris.connect(connection_string, username, password)
  cursor = connection.cursor()

Any changes made to the database by one cursor are immediately visible to all other cursors created from the same connection.

See “Connecting to the DB-API driver and getting a cursor” for a more complete example. See “Creating a Connection Object” for detailed information on creating a connection.

Cursor attributes

arraysize

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

description

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)

rowcount

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 methods

callproc()

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

  • parameterslist of parameter values to pass to the stored procedure

close()

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.

execute()

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:

  • operationstring 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)

executemany()

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

fetchone()

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

fetchmany()

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.

fetchall()

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

   Cursor.fetchall()
isClosed() [InterSystems extension method]

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

Cursor.isClosed()
nextset() [optional DB-API method]

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; 
scroll() [optional DB-API method]

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)
setinputsizes()

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

setoutputsize()

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

stored_results() [InterSystems extension method]

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

SQLType enumeration values

Valid values for the Cursor.description attribute.

  • BIGINT = -5

  • BINARY = -2

  • BIT = -7

  • CHAR = 1

  • DECIMAL = 3

  • DOUBLE = 8

  • FLOAT = 6

  • GUID = -11

  • INTEGER = 4

  • LONGVARBINARY = -4

  • LONGVARCHAR = -1

  • NUMERIC = 2

  • REAL = 7

  • SMALLINT = 5

  • DATE = 9

  • TIME = 10

  • TIMESTAMP = 11

  • TINYINT = -6

  • TYPE_DATE = 91

  • TYPE_TIME = 92

  • TYPE_TIMESTAMP = 93

  • VARBINARY = -3

  • VARCHAR = 12

  • WCHAR = -8

  • WLONGVARCHAR = -10

  • WVARCHAR = -9

  • DATE_HOROLOG = 1091

  • TIME_HOROLOG = 1092

  • TIMESTAMP_POSIX = 1093

Feedback