| Authors: | Daniel Smertnig and Carsten Haese |
|---|---|
| Version: | informixdb 2.5 |
| Date: | 2007-10-16 |
| Homepage: | InformixDB on Sourceforge |
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)]
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
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.
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.
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
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
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 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.
(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.
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 objects expose a few attributes, most of which provide extra information about executed statements:
(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.
(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.
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.
The following describes how database values are mapped to Python objects by InformixDB:
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.
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:
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.
InformixDB defines a number of exception types derived from StandardError to report error conditions:
Base for all InformixDB errors.
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.
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'}]
Connection and Cursor objects both have errorhandler and messages attributes which are used for error handling.
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):
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 |