Python - Our key to Efficiency

eGenix.com · Website 2002 · Python · mxODBC - Python ODBC Interface

mxODBC - Python ODBC Interface

Interface
Connection Objects - Cursor Objects - Type Objects and Constructors - Datatypes - Functions

Subpackages
Adabas - IBM DB2 - DBMaker - Informix - MySQL - PostgreSQL - Oracle - Solid - SybaseASE - SybaseASA - Windows ODBC Manager - Unix iODBC Driver Manager - Unix unixODBC Driver Manager - EasySoft ODBC Bridge - Other DBs

Hints & Links : Examples : Structure : Testing : Threads : Transactions : Stored Procedures : Debugging : Support : Download : Copyright & License : History : Home

Version 2.0.7

Introduction

The eGenix™ mxODBC™ package provides a Python Database API 2.0 compliant interface to databases that are accessible via the ODBC API. This can either be done through an ODBC manager, e.g. the one that comes with Windows, or iODBC which is a free ODBC manager for Unix now maintained by OpenLink, or directly by linking to the database's ODBC driver.

Since ODBC is a widely supported standard for accessing databases, it should in general be possible to adapt the package to any ODBC 2.0 compliant database driver / manager. All you have to do is change the include directives to point to the specific files for your database and maybe set some macro switches to disable ODBC APIs that your driver does not provide. See the installation section for details.

The package supports multiple database interfacing meaning that you can access e.g. two different databases from within one process. Included are several preconfigured subpackages for a wide range of common databases.

Note: Unlike most of the other database modules available for Python, this package uses the new date & time types provided by another package I wrote, mxDateTime, eliminating the problems you normally face when handling dates before 1.1.1970 and after 2038. This also makes the module Year 2000 safe.

Commercial Users:
Users in commercial environments may use this package for an evaluation period of 30 days starting with the day of initial installation. After this period, commercial users must either buy mxODBC CPU licenses from eGenix.com for continued use (more details) or stop using and uninstall the package.

Interface

The package tries to adhere to the Python DB API Version 1.0 in most details. Many features of the new DB API 2.0 are already supported too. The package will eventually move to the 2.0 DB API version.

Here is a list of deviations from the specifications:

cursor.description doesn't return display_size and internal_size; both values are always None
cursor.executemany() (API 2.0) is currently only supported for lists of tuples. Handling of arbitrary sequences of sequences will be available in a future release of mxODBC.
db.callproc() is not yet implemented; you can call stored procedure through standard SQL using cursor.execute() though.
The ODBC standard defines the following syntax for calling stored procedures:
{call procedure-name [([parameter][,[parameter]]...)]}
cursor.nextset() (API 2.0) is currently not supported. Since this is only relevant when using stored procedures it will be implemented when .callproc() is.
db.setinputsizes() and db.setoutputsizes() are dummy functions.
The type objects / constructors (formerly found in the dbi module defined by DB API 1.0) are only needed if you want to write database independant code (well, at least it helps working in that direction, since you can still use database specific SQL code). If you always stick to ODBC, you might as well use the ODBC datatypes directly.
The connection constructor is available under three different names: ODBC() (DB API 1.0), connect() (DB API 2.0) and Connect() (mxODBC specific). See the next section for details on the used parameters. mxODBC also defines a DriverConnect() constructor which is available for ODBC managers and some ODBC drivers.
mxODBC uses the DB API 2.0 exception layout. The exception values are either a single string or a tuple having the format (sqlstate, sqltype, errortext, lineno) where lineno refers to the line number in the mxODBC.c file (to ease debugging the package).
mxODBC uses the DB API 2.0 conventions for .fetchXXX() return values.

The ODBC API is very rich in terms of accessing information about what is stored in the database and mxODBC makes many of these APIs available as additional connection and cursor methods. These are all extensions to the DB API, of course, so porting your program to other database interfaces will be more difficult than otherwise -- on the other hand there are ODBC drivers for almost all relational databases available.

Since most of the parameters and names of the ODBC function names were mapped directly to Python method names (by dropping the SQL prefix and converting them to lower-case) and because of copyright issues, I have not copied the ODBC documentation to this page.

You can download the MS ODBC reference from e.g. Solid Tech -- Solid uses ODBC as native API for their database, BTW.

Connection Objects

You connect to a database via a connection object. All communication from and to the database is done using these objects. They are also the scope of transactions you perform. Each connection can be setup to your specific needs, multiple connections may be opened at the same time.

mxODBC uses a dedicated object type for connections. Each subpackage defines its own object type, but all share the same name: ConnectionType.

Connection Object Constructors

Connect(dsn, user='', password=''[, clear_auto_commit=1])

This constructor returns a connection object for the given datasource. It accepts keyword arguments. dsn indicates the data source to be used, user and password are optional and used for database login.

Normally, auto-commit is turned off by the constructor if the database supports transactions. If given, the value clear_auto_commit overrides the default behaviour. Passing a false value disables the clearing of the flag and lets the connection use the database's default COMMIT behaviour.

Note that a compile time switch (DONT_CLEAR_AUTOCOMMIT) allows altering the default value for clear_auto_commit.

Use the connection method db.setconnectoption(SQL.AUTOCOMMIT, SQL.AUTOCOMMIT_ON|OFF|DEFAULT) (see below) to adjust the connection's behaviour to your needs.

With auto-commit turned on, transactions are effectively disabled. The rollback() method will raise a NotSupportedError when used on such a connection.

If you get an exception during connect telling you that the driver is not capable or does not support transactions, e.g. mxODBC.NotSupportedError: ('S1C00', 84, '[Microsoft][ODBC Excel Driver]Driver not capable ', 4226), please try to connect with clear_auto_commit set to 0.

ODBC(dsn, user='', password=''[, clear_auto_commit=1])

Is just an alias for Connect() needed for DB API 1.0 compliance.

connect(dsn, user='', password=''[, clear_auto_commit=1])

Is just an alias for Connect() needed for DB API 2.0 compliance.

DriverConnect(DSN_string[, clear_auto_commit=1])

This constructor returns a connection object for the given datasource which is managed by an ODBC Driver Manager (e.g. the Windows ODBC Manager or iODBC). It allows passing more information to the database than the standard Connect() constructor.

Please refer to the ODBC manuals of your ODBC manager and database for the exact syntax of the DSN_string. It typically has these entries: 'DSN=datasource_name;UID=userid;PWD=password;' (case is important !). See Connect() for comments on clear_auto_commit.

Note that this API is only available if the interface was compiled with the compile time switch HAVE_SQLDriverConnect defined. See the subpackages section and the subpackage's Setup file for details.

When connecting to a database with transaction support, you should explicitly do a .rollback() or .commit() prior to closing the connection. mxODBC does an automatic rollback of the transaction when the connection is closed if the driver supports transactions.

Helpers

For some subpackages, mxODBC also defines a few helpers which you can use to query additional information from the ODBC driver or manager.

DataSources()

Returns a dictionary mapping data source names to descriptions.

This function is only available for ODBC manager and some ODBC drivers which have internal ODBC manager support, e.g. IBM's DB2 ODBC driver.

Connection Object Methods

cursor([name])

Constructs a new Cursor Object with the given name using the connection. If no name is given, the ODBC driver will determine a unique name on its own. You can query this name with cursor.getcursorname() (see the Cursors section).

Note: This is an extension to the DB-API spec. The specification does not allow any arguments to the constructor.

close()

Close the connection now (rather than whenever __del__ is called). The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.

commit()

This method does nothing in case transactions are not supported on the connection or by the database.

Commit any pending transaction to the database. Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.

Database modules that do not support transactions should implement this method with void functionality.

rollback()

Note: This method is only available if the database subpackage was compiled with transaction support. For ODBC manager subpackages it may raise a NotSupportedError in case the connection does not support transactions.

In case a database does provide transactions this method causes the the database to roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

Additional Connection Methods only available in mxODBC

The following methods are extensions to the DB-API specification and only available in mxODBC and its subpackages.

setconnectoption(option, value)

This method lets you set some ODBC integer options to new values, e.g. to set the transaction isolation level or to turn on auto-commit. option itself must be an integer. Suitable option values are available through the SQL singleton (see below), e.g. SQL.AUTOCOMMIT corresponds to the SQL option (SQL_AUTOCOMMIT in C).

The method is a direct interface to the ODBC SQLSetConnectOption() function. Please refer to the ODBC documentation for more information.

Note that while the API function also supports setting character fields, the method currently does not know how to handle these.

Note for ADABAS users: Adabas can emulate several different SQL dialects. They have introduced an option for this to be set. These are the values you can use: 1 = ADABAS, 2 = DB2, 3 = ANSI, 4 = ORACLE, 5 = SAPR3. The option code is SQL.CONNECT_OPT_DRVR_START + 2 according to the Adabas documentation.

getconnectoption(option)

Same as above, except that the corresponding ODBC function now is SQLGetConnectOption(). option must be an integer. Suitable option values are available through the SQL singelton (see below).

The method returns the data as 32-bit integer. It is up to the callee to decode the integer using the SQL defines.

getinfo(info_id)

Interface to the corresponding ODBC function SQLGetInfo(). The info_id must be an integer. Suitable values are available through the SQL singleton (see below).

The method returns a tuple (integer, string) giving an integer decoding (in native integer byte order) of the first bytes of the API's result as well as the raw buffer data as string. It is up to the callee to decode the data (e.g. using the struct module).

This API gives you a very wide range of information about the underlying database and its capabilities. See the ODBC documentation for more information.

nativesql(command)

This method returns the command as it would have been modified by the driver to pass to the database engine. It is a direct interface to the ODBC API SQLNativeSql().

In many cases it simply returns the same string. Some drivers unescape ODBC escape sequences in the command string. Syntax checking is usually not applied by this method and errors are only raised in case of command string truncation.

Note: Not all mxODBC subpackages support this API.

Connection Object Attributes

Note: All these attributes are extensions to the DB-API specification. Attributes are not defined for connection objects in the DB-API specification.

bindmethod

Attribute to query and set the input variable binding method used by the connection. This can either be BIND_USING_PYTHONTYPE of BIND_USING_SQLTYPE (see the Constants section).

datetimeformat

Use this instance variable to set the default output format for date/time/timestamp columns of all cursors created using this connection object. Possible values are:

DATETIME_DATETIMEFORMAT [default]: DateTime and DateTimeDelta instances
TIMEVALUE_DATETIMEFORMAT: Ticks (numberof seconds since the epoch) and tocks (number of seconds since midnight)
TUPLE_DATETIMEFORMAT: Python tuples (see the above table for definitions)
STRING_DATETIMEFORMAT: Python strings. The format used depends on the settings in the database. See your database's manuals for the exact format and ways to change it.

I strongly suggest always using the DateTime/DateTimeDelta instances. Note that changing the values of this attribute will not change the date/time format for existing cursors using this connection.

stringformat

Use this instance variable to set or query the default input and output handling for string columns of all cursors created using this connection object. Data conversion on input is dependent on the input binding type.

Note: This variable only has an effect if mxODBC was compiled with Unicode support. If not, mxODBC will always work in EIGHTBIT_STRINGFORMAT mode.

Possible values are:

EIGHTBIT_STRINGFORMAT [default]

String columns are converted to Python 8-bit strings assuming the connection's encoding setting (see the .encoding attribute of connection objects) and then passed as Unicode to the ODBC driver.

On output, all string columns are fetched as strings and passed back as Python 8-bit string objects.

This setting emulates the behaviour of previous mxODBC versions and is the default.

MIXED_STRINGFORMAT

Input and output conversion is dependent on the data format the ODBC driver expects or returns for a given column. If the driver returns a string, a Python string is created; if it returns Unicode data, a Python Unicode object is used.

UNICODE_STRINGFORMAT

String columns are converted to Python Unicode objects assuming the connection's encoding setting (see the .encoding attribute of connection objects) and then passed as Unicode to the ODBC driver.

On output, string data is converted to Python Unicode objects in the same way.

Use this setting if you plan to use Unicode objects with non-Unicode aware databases (e.g. by setting the encoding to UTF-8 -- be careful though: multibyte character encodings usually take up more space and are not necessarily compatible with the database's string functions).

NATIVE_UNICODE_STRINGFORMAT

String columns are converted to Python Unicode objects assuming the connection's encoding setting (see the .encoding attribute of connection objects) and then passed as Unicode to the ODBC driver.

On output, string data is always fetched as Unicode data from the ODBC driver and returned using Python Unicode objects.

Note that even though mxODBC may report that Unicode support is enabled (the global HAVE_UNICODE_SUPPORT is 1), the ODBC driver may still reject Unicode data. In this case an InternalError of type S1003 is raised whenever trying to read data from the database in this stringformat mode.

You can use the included test.py script to find out whether the database backend support Unicode or not.

Note that binary and other plain data columns will still use 8-bit strings for interfacing, since storing this data in Unicode objects would cause trouble. mxODBC will eventually use buffer objects to store binary data in some future version.

encoding

Read/write attribute which defines the encoding to use for converting Unicode to 8-bit strings and vice-versa. If set to None (default), Python's default encoding will be used, otherwise it has to be a string providing a valid encoding name, e.g. 'latin-1' or 'utf-8'.

closed

Instance variable that is true in case the connection is closed. Any action on a closed connection will result in a ProgrammingError to be raised. This variable can be used to conveniently test for this state.

converter

Read/write attribute that sets the converter callback default for all newly created cursors using the connection. It is None per default (meaning to use the standard conversion mechanism). See the data type section for details.

Cursor Objects

These objects represent a database cursor, which is used to manage the context of a fetch operation. They are created via the connection method cursor(). Cursors are only active as long as the connection is. Using cursors on a closed connection will result in a ProgrammingError to be raised.

mxODBC uses a dedicated object type for cursors. Each subpackage defines its own object type, but all share the same name: CursorType.

Cursor Object Methods

The following cursor methods are defined in the DB API:

callproc(procname[, parameters])

This method is not yet implemented in mxODBC

Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

The procedure may also provide a result set as output. This must then be made available through the standard fetchXXX() methods.

close()

Close the cursor now (rather than whenever __del__ is called). The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.

execute(operation[,parameters])

Prepare and execute a database operation (query or command).

Parameters must be provided as sequence and will be bound to variables in the operation. Variables are specified using the ODBC variable placeholder '?', e.g. 'SELECT name,id FROM table WHERE amount > ? AND amount < ?' (also see the module attribute paramstyle) and get bound in the order they appear in the SQL statement from left to right.

A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor will optimize its behavior by reusing the previously prepared statement. This is most effective for algorithms where the same operation is used, but different parameters are bound to it (many times).

The parameters may also be specified as list of tuples to e.g. insert multiple rows in a single operation, but this kind of usage is deprecated: executemany() should be used instead.

Return values are not defined.

executedirect(operation[,parameters])

Just like .execute(), except that no perpare step is issued and the operation is not cached. This can result in better performance with some ODBC driver setups.

Return values are not defined.

executemany(operation,seq_of_parameters[,direct])

Prepare a database operation (query or command) and then execute it against all parameter sequences found in the sequence seq_of_parameters.

mxODBC currently only supports lists of tuples in seq_of_parameters. This will be changed in future versions.

The same comments as for execute() also apply accordingly to this method.

If the optional integer direct is given and true, mxODBC will not cache the operation, but submit it for one-time execution to the database. This can result in better performance with some ODBC driver setups.

Return values are not defined.

fetchone()

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

An Error (or subclass) exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.

mxODBC will move the associated database cursor by one row only. This behaviour is not guaranteed by the DB API, though.

fetchmany([size=cursor.arraysize])

Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.

The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor's arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.

An Error (or subclass) exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one fetchmany() call to the next.

fetchall()

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). Note that the cursor's arraysize attribute can affect the performance of this operation.

An Error (or subclass) exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.

nextset()

This method is not yet implemented in mxODBC.

This method will make 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 a true value and subsequent calls to the fetch methods will return rows from the next result set.

An Error (or subclass) exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.

setinputsizes(sizes)

This methods does nothing in mxODBC, it is just needed for DB API compliance.

setoutputsize(size[, column])

This methods does nothing in mxODBC, it is just needed for DB API compliance.

Additional Cursor Methods only available in mxODBC

The following methods are extensions to the DB-API specification and only available in mxODBC and its subpackages.

Since not all databases support all of these ODBC APIs, they can be selectively switched off at compile time to adapt the interface to the underlying ODBC driver/manager. Please take note of this when using them.

All of the following catalog methods use the same interface: they do an implicit call to cursor.execute() and return their output in form of a list of rows which that can be fetched with the fetchXXX() methods in the usual way. The methods always return the number of rows in the result set.

Please refer to the ODBC documentation for more detailed information about parameters and the layout of the result sets. Note: If you pass None as a value where a string would be expected, that entry is converted to NULL before passing it to the underlying API.

All catalog methods support keywords and use the indicated default values for parameters which are omitted in the call.

prepare(operation)

Prepare a database operation (query or command) statement for later execution and set cursor.command. To execute a prepared statement, pass cursor.statement to one of the .executeXXX() methods.

Return values are not defined.

Note: this method is unavailable if mxODBC was compiled with compile time switch DISABLE_EXECUTE_CACHE.

tables(qualifier=None, owner=None, table=None, type=None)