InformixDB Documentation

Authors: Daniel Smertnig and Carsten Haese
Version: informixdb 2.5
Date: 2007-10-16
Homepage:InformixDB on Sourceforge

InformixDB

Introduction

InformixDB is a Python extension module that provides a way to connect to an Informix database via Python's Database API 2.0 [1].

This document describes versions 2.0 and up of InformixDB. Features that are not present in version 2.0 are identified by indicating the version in which they were implemented.

Here's a quick example to get started:

>>> import informixdb
>>> conn = informixdb.connect('mydatabase')
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM names")
>>> cursor.fetchall()
[('donald', 'duck', 34), ('mickey', 'mouse', 23)]

Connecting

To do anything useful with InformixDB one must connect to a database. This is accomplished by calling informixdb.connect:

>>> import informixdb
>>> conn = informixdb.connect('db@daniel', user='me', password='something')
>>> conn
<_informixdb.Connection object at 0xb7d08e90>

informixdb.connect takes three arguments: A dsn which identifies the database and server to connect to, as recognized by ESQL's CONNECT statement (e.g. 'database@server', 'database', '@server') plus an optional user and a corresponding password.

If the dsn doesn't include a servername the value of the environment variable INFORMIXSERVER is used. When connecting without specifying the name of the database no database will be selected. This is useful for setting up a new database from within InformixDB.

New in version 2.2: An optional autocommit parameter may be passed to turn on autocommit mode. In autocommit mode, the connection operates without transaction control and any changes are committed automatically. By default, autocommit is off.

A Connection can be closed by calling its close method. From that point forward all operations on the Connection object or any of its associated Cursor objects will raise an InterfaceError. A connection will also be closed upon deallocation (this requires all Cursor objects which use the connection to be closed or deallocated first).

New in version 2.3: Connections and cursors support the context management protocol for Python 2.5 with blocks. This allows code like this:

conn = informixdb.connect(...)
try:
   cur = conn.cursor()
   try:
      # do something with cur
   finally:
      cur.close()
finally:
   conn.close()

to be written in Python 2.5 more concisely as:

with informixdb.connect(...) as conn:
   with conn.cursor() as cur:
      # do something with cur

Executing SQL statements

To actually execute a SQL statement a Cursor object must be created by calling Connection.cursor:

>>> cursor = conn.cursor()
>>> cursor
<_informixdb.Cursor object at 0xb7d84440>

This Cursor object can then be used to execute SQL statements or to call stored procedures:

>>> cursor.execute('SELECT * FROM NAMES')
>>> cursor.callproc('lower')

Note

It is also possible to call a stored procedure by issuing a EXECUTE PROCEDURE ... statement with execute, but callproc should be used where possible for standards compatibility.

Binding parameters

To pass in arguments for an SQL statement a sequence can be passed as second parameter to execute. The arguments can then be referenced either using numeric or qmark style:

>>> cursor.execute('INSERT INTO names VALUES(:1,:2,:3)', ('some', 'body', 56))
1
>>> cursor.execute('INSERT INTO names VALUES(?,?,?)', ('dagobert', 'duck', 77))
1

Care must be taken when passing in a single string as argument, since it would be interpreted as a sequence, which is usually not what is intended:

>>> cursor.execute('SELECT * FROM names WHERE first=:1', 'dagobert')
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
_informixdb.InterfaceError: too many actual parameters
>>> cursor.execute('SELECT * FROM names WHERE first=:1', ('dagobert',)

InformixDB will catch most of these errors since it requires the number of actual parameters to match the number of parameters which are referenced by the SQL statement exactly.

Calling stored procedures with arguments is supported in a similar way, except that no placeholders have to be specified:

>>> cursor.callproc('lower', ('FOO',))

New in version 2.4: Parameters may also be specified using named style, in which case the arguments must be a dictionary whose keys correspond to the parameter names:

>>> cursor.execute('INSERT INTO names VALUES(:first,:last,:age)',
...                dict(first='some', last='body', age=56) )

If a parameter name is not in the dictionary, a KeyError will be raised. However, the dictionary may contain more keys than the statement needs. This allows, for example, to simply pass locals() to bind all parameters from correspondingly named variables.

Note: While it is perfectly acceptable to execute different statements with different parameter styles, mixing parameter styles in the same statement is not allowed.

Getting results

To retrieve the results of a previously executed SQL statement or stored procedure Cursor objects provide the fetchone, fetchmany and fetchall methods:

  • fetchone(): Returns the next row of the result set or None if no more rows are available.

  • fetchmany([size=Cursor.arraysize]): Fetches up to size rows (less if there are no more rows) and returns them as a list. An empty list is returned when no (more) rows are available.

    If size is not specified Cursor.arraysize is used, which defaults to 1 for InformixDB.

  • fetchall(): Fetches all (remaining) rows and returns them as a list.

>>> cursor.execute("SELECT * FROM names")
>>> cursor.fetchall()
[('donald', 'duck', 34), ('mickey', 'mouse', 23),
 ('dagobert', 'duck', 77), ('some', 'body', 56)]

As an extension to the DB-API 2.0 specification InformixDB Cursor objects can also be used as iterators:

>>> cursor.execute("SELECT * FROM names")
>>> for row in cursor:
...     print "%s %s (%d)" % row
...
donald duck (34)
mickey mouse (23)
dagobert duck (77)
some body (56)
>>>

Normally rows are returned as tuples, but optionally dictionaries can be returned. This must be specified when the Cursor is created:

>>> dictcur = conn.cursor(rowformat = informixdb.ROW_AS_DICT)
>>> dictcur.execute("SELECT * FROM names")
>>> dictcur.fetchone()
{'age': 34, 'last': 'duck', 'first': 'donald'}

New in version 2.1: Rows can also be returned as objects whose attributes correspond to column names:

>>> objcur = conn.cursor(rowformat = informixdb.ROW_AS_OBJECT)
>>> objcur.execute("SELECT * FROM names")
>>> row = objcur.fetchone()
>>> print row.age
34

Describing result sets

Whenever a statement which returns a result set is executed a description of the columns returned is made available in the Cursor objects description attribute.

It is a sequence of 7-tuples with one tuple for each returned column. Each tuple consists of name, type_code, display_size, internal_size, precision, scale and null_ok values (precision and scale are currently always set to None by InformixDB).

The type_code is a textual description of the column type and can be evaluated by comparing it to the global type objects defined by the informixdb-module (see Database values):

>>> cursor.execute('SELECT * FROM names')
>>> cursor.description
(('first', 'char', 25, 25, None, None, 1),('last', 'char', 50, 50,
None, None, 1), ('age', 'integer', 4, 4, None, None, 1))
>>> cursor.description[0][1] == informixdb.STRING
1
>>> cursor.description[0][1] == informixdb.NUMBER
0

Fast execution of batch statements

Statement caching

When execute is called multiple times with the same SQL statement object on the same Cursor object but different arguments the statement is prepared only once. This mechanism is called statement caching. Each Cursor object only caches its most recent query. If you need to execute different queries multiple times while still making use of statement caching, you will need to create one cursor for each query.

Note: Statement caching operates based on the identity of the statement string, not on the contents, because an identity check is much faster than an equality check. In practice this means that:

>>> cursor.execute("UPDATE names SET age = :2 WHERE last = :1", params1)
>>> cursor.execute("UPDATE names SET age = :2 WHERE last = :1", params2)

will not make use of statement caching, since each command will instantiate its own copy of the query string, which will result in two query strings that are equal to each other but not identical. To gurantee that statement caching is used, you need to do the following:

>>> query_string = "UPDATE names SET age = :2 WHERE last = :1"
>>> cursor.execute(query_string, params1)
>>> cursor.execute(query_string, params2)

executemany()

executemany can be used to execute the same statement with different parameters multiple times. It takes a sequence of parameter-sequences as taken by execute as its second argument and executes the statement for each of the parameters. Internally, insert cursors are used for batch inserts, which can result in a huge performance boost, especially over a network:

>>> cursor.executemany("INSERT INTO names VALUES(:1,:2,:3)",
...     [ ('no', 'more', 12), ('names', 'left', 34) ]
... )

(New in version 2.3): In earlier versions, the parameter list for executemany had to be a true sequence (i.e. list or tuple) of sequences. Beginning with version 2.3, it is allowed to be any iterable object that yields sequences.

Explicitly prepared statements

(new in version 2.3)

The method Cursor.prepare explicitly prepares a query for subsequent execution by the same cursor, and the attribute command contains the query string that the cursor has most recently prepared or executed. Note that prepare simply caches the statement like execute does, except that the query is not immediately executed. Consequently, to prepare more than one statement, you'll need to create more than one cursor.

Since prepare is just statement caching in disguise, it does not offer any additional performance benefits, but it makes your code more readable. The following code snippet is equivalent to the above example of guaranteed statement caching, but it conveys in a clearer fashion that a statement is prepared once and executed twice:

>>> cursor.prepare("UPDATE names SET age = :2 WHERE first = :1")
>>> cursor.execute(cursor.command, params1)
>>> cursor.execute(cursor.command, params2)

New in version 2.4: You may pass None instead of cursor.command to execute the previously prepared query. This reduces code clutter if your cursor instances have long names.

Using named cursors

Named Cursors can be created by passing the requested cursor name in as first argument to Connection.cursor:

>>> namcursor = conn.cursor('foo')

The returned Cursor object will then use this name whenever it declares a database cursor. It is important to note that a Cursor object's lifetime is decoupled from that actual database cursors. Database cursor are created whenever a SQL statement that can return a result set is executed (i.e. SELECT and EXECUTE PROCEDURE) and destroyed when the Cursor is closed or another statement is executed with the same Cursor object.

The main implication of this is that you cannot use the named Cursor object to execute statements on the named cursor itself, as that will destroy the database cursor before executing the statement. Instead, a second (possibly unnamed) Cursor is used to execute ... WHERE CURRENT OF ... statements:

>>> namcursor = conn.cursor('foo')
>>> cursor = conn.cursor()
>>> namcursor.execute('SELECT * FROM names FOR UPDATE')
>>> namcursor.fetchone()
('donald', 'duck', 34)
>>> cursor.execute("UPDATE names SET first='notdonald' WHERE CURRENT OF foo")
1
>>> cursor.execute("SELECT * FROM names")
>>> cursor.fetchone()
('notdonald', 'duck', 34)

Cursor attributes

Cursor objects expose a few attributes, most of which provide extra information about executed statements:

arraysize
Default size to be used for fetchmany, defaults to 1.
connection
The Connection object associated with this Cursor.
description
Description of the result set returned by the currently prepared statement. See Describing result sets for information on how to use this attribute.
rowcount
Number of rows affected by the last executed statement, or -1 if no statement was executed or the number can not be determined. InformixDB only provides values for DELETE, UPDATE, and INSERT statements.
sqlerrd
Informix SQL error descriptor (sqlerrd) as a tuple.
messages
See Advanced error handling on how to use errorhandler and messages.
errorhandler
See Advanced error handling on how to use errorhandler and messages.

Scroll Cursors and Cursors with Hold

(new in version 2.1)

InformixDB allows you to use scroll cursors and cursors with hold. To create a scroll cursor, pass the argument scroll=True to the Connection.cursor() call. To create a cursor with hold, pass the argument hold=True. Both arguments may be given together to create a scroll cursor with hold.

If a cursor was created as a scroll cursor, you may use its scroll() method to navigate through the result set. scroll() takes up to two arguments, a mandatory value argument and an optional mode argument. If given, mode must be either 'absolute' or 'relative', and it defaults to 'relative' if not given. In absolute mode, value is the result row number to which you want to scroll. In relative mode, value is the number of rows by which you want to scroll from the current row.

Interrupting Queries

(new in version 2.5)

Cursor objects provide two mechanisms for interrupting long-running queries, configurable with the attributes sqltimeout and sqlinterrupt. sqltimeout is an integer that specifies how many milliseconds a query is allowed to take. If a query takes longer than that, it is automatically aborted. If sqltimeout is zero, no timeout is in effect and queries are allowed to run indefinitely.

sqlinterrupt is a boolean attribute that indicates whether interrupt signals during query execution interrupt the query. Note that turning this feature on disables Python's handling of interrupt signals while queries are executed.

Connection objects have sqltimeout and sqlinterrupt attributes that set the defaults for the corresponding attributes of any cursor objects that the connection object creates. By default, sqltimeout is zero, and sqlinterrupt is False.

Transactions

When enabled in the underlying database, transactions can be used by calling commit and rollback on a Connection object. A new transaction will automatically be started after the previous one was committed or rolled back.

Before the connection to the database is closed an implicit rollback is performed.

When transactions are disabled in the database, commit becomes a no-op and rollback raises a NotSupportedError when called.

New in version 2.2: A Connection object has an autocommit attribute that can be used to turn automatic commits on or off.

Database values

The following describes how database values are mapped to Python objects by InformixDB:

CHAR, VARCHAR, NCHAR, NVARCHAR, LVARCHAR
string
FLOAT, SMALLFLOAT
float
DECIMAL, MONEY
float or decimal.Decimal; In InformixDB-2.2 and Python 2.4, DECIMAL and MONEY values will be returned as decimal.Decimal instances unless the cursor was created with the use_decimal flag set to False.
SMALLINT, INT, SERIAL
int
BYTE, TEXT
buffer; The informixdb.Binary(string) should be used to create database independent large object values.
DATE
datetime.date; informixdb.Date(year,month,day) or informixdb.DateFromTicks(ticks) should be used to create database independent date values.
DATETIME
datetime.datetime; informixdb.Time(hour,minute,second[,microsecond=0]), informixdb.TimeFromTicks(ticks), informixdb.Timestamp(year,month,day,hour=0,minute=0,second=0[,microsecond=0] or informixdb.TimestampFromTicks(ticks) should be used to create database independent time or timestamp (i.e. DATETIME) values.
INTERVAL (new in version 2.1)
informixdb.IntervalYearToMonth or informixdb.IntervalDayToFraction depending on the interval's precision. Interval instances support certain arithmetic operations, such as adding to or subtracting from a datetime, adding to or subtracting from another compatible interval, and scaling by a factor.
INT8, SERIAL8 (IDS) (new in version 2.1)
long
BLOB, CLOB (IDS) (new in version 2.1)
informixdb.Sblob; To insert (or update) a Smart Large Object into a database column, you need to create an Sblob instance by calling the Sblob() method of the connection to which the Sblob should belong. Once an Sblob instance is either created or fetched, it provides a file-like interface for reading and writing its data.
BOOLEAN (IDS) (new in version 2.2)
bool
extended types, user-defined types (IDS) (new in version 2.1)

Complex and opaque types are mapped to their string representation. Distinct types are mapped like the type from which they are derived.

New in version 2.4: If desired, opaque types can be fetched in their internal binary representation. The cursor attribute binary_types is a dictionary that controls which opaque types are fetched in binary format.

When a query returns values of an opaque type, the type name is looked up in the cursor's binary_types dictionary. If the type name is found and has a true value, the contents will be read in binary format. Otherwise, the contents will be read in text format.

Connection objects have a binary_types dictionary that serves as the default for the cursors that the connection creates. This dictionary is empty by default.

NULL
SQL NULL is represented by Python's None when binding parameters or returning results.

Furthermore there are a number of objects defined by the module which can be used to compare database column types as they appear in Cursor.description against well-known types in a database independent manner:

informixdb.STRING
Compares equal to any string based types.
informixdb.BINARY
Compares equal to long/binary types (BYTE and TEXT).
informixdb.NUMBER
Compares equal to any numeric types.
informixdb.DATETIME
Compares equal to any date, time or datetime types.
informixdb.ROWID
Compares equal to rowid types (i.e. SERIAL).

They are used like this, except of course that the actual column types would come from Cursor.description:

>>> informixdb.STRING == 'char'
1
>>> informixdb.STRING == 'integer'
0
>>> informixdb.NUMBER == 'integer'
1
>>> informixdb.NUMBER == 'decimal'
1

Note

InformixDB uses the datetime module to represent date, time and timestamp values. This module is part of the standard library since Python 2.3 and is automatically installed by InformixDB for Python 2.2.

Note

The FRAC part of Informix DATETIME fields is mapped to datetime.microseconds but the precision is limited to a maximum of 10 microseconds (or less, depending on how the field is defined in the database), due to the limited precision of fractional datetimes in Informix.

Error handling

InformixDB defines a number of exception types derived from StandardError to report error conditions:

Error

Base for all InformixDB errors.

InterfaceError
Error in the database interface itself. This is currently raised when trying to used closed Cursor or Connection objects or when an internal date/time conversion error occurs.
DatabaseError

Base and catch-all for errors reported by the database. DatabaseError or subclasses of it are raised whenever a database operation fails. When it's possible to determine the class of an error via SQLSTATE, the more specific error types are raised.

DataError
Error due to the processed data. E.g. a division by zero or when a numeric value is out of range.
OperationalError
Operational database errors that aren't necessarily under the programmer's control, e.g. connection problems, permission denied or out of memory conditions.
IntegrityError
An integrity constraint would be violated.
InternalError
An error internal to the database, e.g. invalid cursor or transaction states.
ProgrammingError
An error caused by the program. Typically raised when an invalid table is referenced, a syntax error occurs or a SQL statement is invalid.
NotSupportedError
A operation or method is not supported by the database. Raised when trying to invoke rollback on a database that doesn't support transactions or when the database doesn't support a particular feature (e.g. BYTE/TEXT or VARCHAR on Informix SE).
Warning
Represents a SQL warning (e.g. data truncation). This exception is never actually raised by the default error handler. By default, warnings are only reported via the Connection or Cursor object's messages attribute (see Advanced error handling).

These exception types are accessible as attributes of the informixdb-module or Connection objects.

DatabaseError and Warning provide a number of useful attributes with information about the underlying SQL error:

>>> try:
...     informixdb.connect('nonexisting')
... except informixdb.DatabaseError, e:
...     print e
...     print e.action, e.sqlcode, e.diagnostics
...
SQLCODE -329 in CONNECT:
IX000: Database not found or no system permission.
IX000: No such file or directory

CONNECT -329 [ {'message': 'Database not found or no system permission.',
'sqlstate': 'IX000'}, {'message': 'No such file or directory',
'sqlstate': 'IX000'}]
action
The ESQL/C statement which was executed by InformixDB when the error was encountered.
sqlcode
Value of Informix' SQLCODE.
diagnostics
Sequence of all diagnostic messages reported by Informix where each item is a dictionary with keys 'message' for a text representation of the message and 'sqlstate' for the corresponding SQLSTATE value.

Advanced error handling

Connection and Cursor objects both have errorhandler and messages attributes which are used for error handling.

messages
A list of (exception_class, exception_value) tuples for all errors and warnings generated by the database relating to this Cursor or Connection. This list is automatically cleared prior to execution of any Cursor or Connection methods except for the fetch* methods.
errorhandler

A Python callable of the form errorhandler(connection, cursor, errorclass, errorvalue) which is called for all database warnings and errors relating to this Cursor or Connection. The default (if this is None) is to append the error/warning to the messages list and raise an exception for errors.

This attribute is inherited from the associated Connection by Cursor objects when they are created.

When a database error occurs in InformixDB it is basically handled like this (which path is followed depends on whether the error occurs during execution of a Connection or Cursor method):

  • The exception class and exception value is determined.
  • The Cursor or Connection objects errorhandler attribute is checked to see if it is set.
  • If an errorhandler is available, it is invoked with connection, cursor, errorclass and errorvalue as its arguments. The error handler might raise an exception or return, in which case execution is continued.
  • If no errorhandler is available, the built in error handler is invoked. A (errorclass, errorvalue) tuple is appended to the Cursor or Connection object's messages attribute, and if the error is an actual error (i.e. not of type Warning), an exception is raised.

Inspecting Version Numbers

Occasionally it may be useful to inspect the version numbers of the database engine, the ESQL/C driver, or of InformixDB itself. The following version information is available at runtime:

Module-level attribute:

  • version: The version number of InformixDB itself. (new in version 2.3)

Versions 2.0 through 2.2 didn't provide any version information. If you have an installation of InformixDB that doesn't have a version attribute, you should probably upgrade, since you're missing out on a lot of features and bug fixes.

Connection attributes (new in version 2.5):

  • dbms_name, dbms_version: The name and version number of the database engine. For older engines such as Standard Engine, the name can not be determined and the string "Unknown" is returned instead.
  • driver_name, driver_version: The name and version number of the ESQL/C installation with which InformixDB was compiled. The name is either "INFORMIX-ESQL" or "IBM Informix-ESQL"
[1]http://www.python.org/peps/pep-0249.html