Skip to main content

Using the Python DB-API

The InterSystems Python DB-API driver is a fully compliant implementation of the PEP 249 version 2.0Opens in a new tab 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 InterSystems DB-API driver (for example, if you are connecting from a host on which InterSystems IRIS is not installed), you can download it from the InterSystems IRIS Drivers pageOpens in a new tab and install it with:

pip install intersystems_irispython-<version>.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 DB-API 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.

Connecting Your Application to InterSystems IRISOpens in a new tab also provides instructions, including sample code, for connecting to an InterSystems IRIS server from a Python application using DB-API.

PEP 249 Implementation Reference

This section lists all required implementation features described in the PEP 249 version 2.0Opens in a new tab 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

This is a global in the _DBAPI.py file that indicates the format of the input parameters when parameterized calls to execute(), executemany(), and callproc() are invoked with SQL statements. The following values are supported:

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

  • "named" — query parameters use named style (for example: WHERE name=:name)).

The default value is qmark. The following example sets it to named:

  import iris
  iris.dbapi._DBAPI.paramstyle = "named"
Examples using "qmark"

An input parameter is indicated by a question mark (?). The input parameters are provided as a Python list.

  sql = "Select * from Sample.Person where id = ? and name = ?"
  params = [1, 'Jane Doe']
  cursor.execute(sql, params) // same for direct_execute
  sql = "Insert into Sample.Person (name, phone) values (?, ?)"
  params = [('ABC', '123-456-7890'), ('DEF', '234-567-8901'), ('GHI', '345-678-9012')]
  cursor.executemany(sql, params) // batch update
  proc = "{ ? = Proc1(?) }" // parameter modes: RETURN_VALUE, INPUT 
  params = [1]
  return_args = cursor.callproc(proc, params) // stored procedure
  proc = "{ CALL Proc2 (?, ?) }" // parameter modes: INPUT, OUTPUT 
  params = ['abc']
  return_args = cursor.callproc(proc, params) // stored procedure

Examples using "named"

An input parameter is indicated by a variable name preceded by a colon. The input parameters are provided as a Python dictionary where the keys indicate the variable names used in the SQL and the values for the keys hold the actual data for the corresponding variables/parameters. Named parameters will work even if paramstyle is not set.

  sql = "SELECT * FROM Sample.Person WHERE firstname = :fname AND lastname = :lname"
  params = {'fname' : 'John', 'lname' : 'Doe'}
  cursor.execute(sql, params)
  sql = "INSERT INTO Sample.Person(name) VALUES (:name)"
  params = [{'name' : 'John'}, {'name' : 'Jane'}]
  cursor.executemany(sql, params)

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

Once the cursor is closed, accessing the column data of a DataRow object or any other attributes/functions of the Cursor class will result in an error.

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

  • parameterslist 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)
Note:

Outputs of fetch*() and callproc() APIs which returned Python lists in previous releases now return Python tuples. If the output from older version was [1, "hello"], then the new version will return (1, "hello"). Python tuples containing one item will have a comma appended. For example, a list such as [100] is represented as tuple (100,).

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

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

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

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

Note:

The stored_results() method is deprecated and will be removed in a future release.

stored_results() [DEPRECATED 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

FeedbackOpens in a new tab