mxODBC - An ODBC Interface for Python

Interface : Datatypes : DB-API Extensions : Examples : Structure : Download & Installation : Subpackages ( Adabas : MySQL : Solid : Sybase : Oracle : Informix : Windows ODBC Manager : Unix iODBC Manager : Other DBs ) : Support : Copyright : License : History : Home

Version 1.0.1

Introduction

The mxODBC package provides a nearly 100% Python DB API 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.

As of version 0.8 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.

Interface

The module tries to adhere to the Python DB API Version 1.0 in most details (an updated, but yet unapproved, version 1.1 is included in the archive).

There are some minor deviations though:

cursor.description doesn't return display_size and internal_size; both values are always None
db.callproc() is not yet implemented; you can call stored procedure through standard SQL using cursor.execute() though.
cursor.nextset() (API 1.1) is not supported. It always returns None. Since this is only relevant when using stored procedures it will be implemented when .callproc() is.
db.setinputsizes() and db.setoutputsizes() don't do anything.
The dbi module is 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 "connect string" is not a single string, but a tuple of three strings. See the next section for details.
The standard (DB API compliant) constructor ODBC() has been renamed to Connect(). There is an alias to ODBC() to remain in sync with the API specifications.
the API 1.0 doesn't define how errors should be returned to the user; I used exceptions with either a single string as value or a value tuple having the format (sqlstate, sqltype, errortext, lineno) where lineno refers to the line number in the mxODBC.c file (to ease debugging the module).
the API 1.0 doesn't define what the cursor.fetchxxx() methods return in case no further data is available; mxODBC uses the following conventions:

Since the ODBC API is very rich in terms of accessing information about what is stored in the database, the module makes several additional cursor methods available. These and several other useful extensions are explained in the next section.

Extensions to the DB-API

Apart from the standard methods, mxODBC also offers some extra methods which provide more information to the user by giving access to many of the underlying ODBC API function.

Connection constructors, methods and attributes

 dbc =
	    Connect(datasource,userid,passwd[,clear_auto_commit=1])

This constructor returns a connection object for the given datasource.

Normally, auto-commit is turned off by the constructor. If given, clear_auto_commit overrides the default behaviour (false: don't clear the flag and use the database default, true: clear the flag). Note that a compile time switch (DONT_CLEAR_AUTOCOMMIT) allows altering the default behaviour to clear the flag.

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

 dbc =
	    ODBC(datasource,userid,passwd[,clear_auto_commit=1])

Is just an alias for Connect needed for DB API compliance.

 dbc =
	    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). 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;extrastuff=extravalues' (case is important !). See Connect() for comments on clear_auto_commit.

Note that this API is only available if the interface was compiled with HAVE_SQLDriverConnect defined. See install section for details.


	    dbc.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 defines are available through the SQL class (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 pass: 1 = ADABAS, 2 = DB2, 3 = ANSI, 4 = ORACLE, 5 = SAPR3. The option code is SQL.CONNECT_OPT_DRVR_START + 2 according to the documentation.

dbc.getconnectoption(option)

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

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

dbc.getinfo(info_id)

Same as above, except that the corresponding ODBC function now is SQLGetInfo(). The info_id is an integer. Suitable defines are available through the SQL class (see below).

The method returns a tuple (integer,string) giving a 32-bit integer decoding 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.

dbc.cursor([name])

Constructs a new cursor 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 useing cursor.getcursorname() (see below).

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

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

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

Cursor methods and attributes

Note that not all databases support all of these ODBC APIs. They can be selectively switched off to adapt the interface to the underlying ODBC driver.

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, that can be obtained with the fetch 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 information.

cursor.tables(qualifier,owner,table,type): Same as the corresponding ODBC API function. Catalog method.
cursor.tableprivileges(qualifier,owner,table): Same as the corresponding ODBC API function. Catalog method.
cursor.columns(qualifier,owner,table,column): Same as the corresponding ODBC API function. Catalog method.
cursor.columnprivileges(qualifier,owner,table,column): Same as the corresponding ODBC API function. Catalog method.
cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table, foreign_qualifier,foreign_owner,foreign_table): Same as the corresponding ODBC API function. Catalog method.
cursor.primarykeys(qualifier,owner,table): Same as the corresponding ODBC API function. Catalog method.
cursor.procedures(qualifier,owner,procedure): Same as the corresponding ODBC API function. Catalog method.
cursor.procedurecolumns(qualifier,owner,procedure,column): Same as the corresponding ODBC API function. Catalog method.
cursor.specialcolumns(qualifier,owner,table,coltype,scope,nullable): Same as the corresponding ODBC API function. Note: coltype is in a different position than it is in the corresponding C call. Catalog method.
cursor.statistics(qualifier,owner,table,unique,accuracy): Same as the corresponding ODBC API function. Catalog method.
cursor.setcursorname(name): Sets the name to be associated with the with the cursor object. There is a length limit for names in SQL at 18 characters. An InternalError will be raised if the name is too long or otherwise not useable.
cursor.getcursorname(): Returns the current cursor name associated with the cursor object. This may either be the name given to the cursor at creation time or a name generated by the ODBC driver for it to use.
cursor.datetimeformat: Instance variable to set the output format for date/time/timestamp columns on a per cursor basis. It takes the same values as the dbc.datetimeformat instance variable and defaults to the creating connection object's settings for datetimeformat.

I should also warn you: if you plan to write cross database applications, use these methods with care since at least some of the databases I know don't support certain APIs or return misleading results. Also, be sure to check the correct performance of the methods and executes. I don't want to see you loosing your data due to some error I made, or the fact that the ODBC driver of your DB is buggy (like mine :-().

Exceptions

The module uses a more detailed set of exceptions to do error reporting than defined in the DB API 1.0 (these are proposed extensions for the 1.1 versions of the specification).

Error, error

Baseclass for all other exceptions related to database or interface errors.

You can use this class to catch all errors related to database or interface failures. error is just an alias to Error needed for DB-API 1.0 compatibility.

Error is a subclass of exceptions.StandardError.

Warning

Exception raised for important warnings like data truncations while inserting, etc.

Warning is a subclass of exceptions.StandardError. This may change in a future release to some other baseclass indicating warnings.

InterfaceError

Exception raised for errors that are specific to the interface rather than the database itself.

DataError

Exception raised for errors that are due to problems with the processed data like division by zero, numeric out of range, etc.

OperationalError

Exception raised when the an unexpected disconnect occurs, the data source name is not found, etc.

IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.

InternalError

Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc.

ProgrammingError

Exception raised for programming erros, e.g. table not found or already exists, etc.

A hint for troubles with Warning exceptions:
If some function does not work because it always raises a Warning exception, you could either turn off warning generation completely by recompiling the module using the DONT_REPORT_WARNINGS flag (see Setup[.in] for explanations) or step through the source to find the exact location where the exception occurs and replace the Py_SQLCheck() with Py_SQLErrorCheck(). If you do the latter please inform me of the change so that I can include it in future versions.

Constants

The module defines a class called SQL which is initialized with flags and other integer values defined in the ODBC standard header files. E.g. to the flag value of SQL_AUTOCOMMIT is made available through SQL.AUTOCOMMIT.

If you pass None as a value where a string would be expected, that entry is passed as NULL to the underlying API. This is because some APIs use the empty string '' as special value.

SQL: Class that defines nearly all values available in the ODBC header files.
errorclass: Dictionary mapping SQL error code strings to exception objects used by the module.
sqltype: Dictionary mapping SQL type codes (these are returned in the type field of cursor.description) to type strings.
CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONGVARBINARY, TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, BIT, REAL, FLOAT, DOUBLE, DATE, TIME, TIMESTAMP: Type code integers for the various supported SQL types. These map to integers as returned in the type field of cursor.description. sqltype provides the inverse mapping.

Debugging

To simplify debugging the module I added debugging output in several important places. The feature is only enabled if the module is compiled with -DMAL_DEBUG and output is only generated if Python is run in debugging mode (use the interpreter flag '-d'). The debugging log file is named mxODBC.log. It will be created in the current working directory; messages are always appended to the file so no trace is lost until you explicitly erase the log file. If the log file can not be opened, the module will use stderr to do the reporting.

Note that the debug version of the module is almost as fast as the regular build, so you might as well leave it enabled.

Thread Safety & Thread Friendly

mxODBC itself is written in a thread safe way. There are no module globals that are written to and thus no locking is necessary. Many of the underlying ODBC SQL function calls are wrapped by macros unlocking the global Python interpreter lock before doing the call and regaining that lock directly afterwards. The most prominent of those are the connection APIs and the execute and fetch APIs.

So in general when using a separate database connection for each thread, you shouldn't run into threading problems. If you do, it is more likely that the ODBC driver is not 100% thread safe and thus not 100% ODBC compatible. Note that having threads share cursors is not a good idea: there are many very strange transaction related problems you can then run into.

Unlocking the interpreter lock during long SQL function calls gives your application more responsiveness. This is especially important for GUI based applications, since no other Python thread can run when the global lock is acquired by one thread.

Note: mxODBC will only support threading if you have built Python itself with thread support enabled. Python for Windows has this per default. Try: python -c "import thread" to find out.

Thanks to Andy Dustman for bringing this to my attention. If you do run into mxODBC related threading problems, feel free to contact me.

Supported Data Types

The following data types are supported:

SQL type Python type Comments
CHAR, VARCHAR, LONGVARCHAR (LONG in SQL) String The conversion truncates the string at the first 0-byte or the SQL field length -- whichever comes first. The handling of special characters depends on the codepage the database uses.
BINARY, VARBINARY, LONGVARBINARY (LONG BYTE in SQL) String Truncation at the SQL field length. These can contain embedded 0-bytes and other special characters.
TINYINT, SMALLINT, INTEGER, BIT Integer Conversion from the Python integer (a C long) to the SQL type is left to the ODBC API of the database, so expect the usual truncations.
BIGINT Long Integer Conversion to and from the Python long integer is done via string representation since there is no C type with enough precision to hold the value. Because of this, you might receive errors indicating truncation or errors because the database sent string data that cannot be converted to a Python long integer. Not all SQL databases implement this type, MySQL is one that does.
DECIMAL, NUMERIC, REAL, FLOAT, DOUBLE Float Conversion from the Python float (a C double) to the SQL type is left to the ODBC API of the database, so expect the usual truncations.
DATE DateTime instance / ticks / (year,month,day) / Python string While you should use DateTime instances, the module also excepts ticks (Python numbers indicating the number of seconds since the Unix Epoch; these are converted to local time and then stored in the database) and tuples (year,month,day) on input.
The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTime instances.

TIME DateTimeDelta instance / tocks / (hour,minute,second) / Python string While you should use DateTimeDelta instances, the module also excepts tocks (Python numbers indicating the number of seconds since 0:00:00.00) and tuples (hour,minute,second) on input.
The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTimeDelta instances.

TIMESTAMP DateTime instance / ticks / (year,month,day,hour,minute,second) / Python string While you should use DateTime instances, the module also excepts ticks (Python numbers indicating the number of seconds since the epoch; these are converted to local time and then stored in the database) and tuples (year,month,day,hour,minute,second) on input.
The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTime instances.

The above SQL types are provided by the module as SQL type code integers as attributes of the class SQL, so that you can decode the value in cursor.description by comparing it to one of those constants. A reverse mapping of integer codes to code names is provided by the dictionary sqltype.

Note: You may run into problems when using the tuple versions for date/time/timestamp arguments. This is because some databases (noteably MySQL) want these arguments to be passed as strings. mxODBC does the conversion internally but tuples turn out as: '(1998,4,6)' which it will refuse to accept. The solution: use DateTime[Delta] instances instead. These convert themselves to ISO dates/times which most databases (including MySQL) do understand.

Auto-Conversions

While you should always try to use the above Python types for passing input values to the resp. columns, the package will try to automatically convert the types you give into the proper ones, e.g. passing the integer literal '123' works just as well as passing the integer 123 directly. (You shouldn't rely on this feature in case you intend to move to another DB-API compliant database module.)

Examples

Sorry, I can't give you any more elaborate scripts at the moment:

projects/mxODBC> python
Python 1.5 (#159, Dec 15 1997, 13:25:26)  [GCC 2.7.2.1] on linux2
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import ODBC.MySQL
>>> db = ODBC.MySQL.Connect('host:MYDB','user','passwd')
>>> c = db.cursor()
>>> c.execute('select count(*) from test')
>>> c.fetchone()
(305,)
>>> c.close()
>>> db.close()
>>>

Package Structure

[ODBC]
       [Adabas]
              dbi.py
              showdb.py
       Doc/
       [Informix]
              dbi.py
       [MySQL]
              dbi.py
       [Oracle]
              dbi.py
       [Solid]
              dbi.py
       [Sybase]
              dbi.py
       [Windows]
              dbi.py
       [iODBC]
              dbi.py
       [mxODBC]
              dbi.py

Entries enclosed in brackets are packages (i.e. they are directories that include a __init__.py file). Ones with slashes are just simple subdirectories that are not accessible via import.

Installation

First, you will have to install another extension called mxDateTime. Be sure to always fetch the latest release of both packages, since I always synchronize the two whenever something changes. If you only update one of them, you may run into problems later.

After that is installed and running, download the archive, unzip it to a directory on your Python path. If you don't want to use the new date & time types or still use Python 1.4, then you'll have to stick to version 0.5.

You might also want to download the ODBC reference manual from SolidTech which I used to develop this package.

Note: The next section contains database specific installation notes. You may want to read those first before continuing the setup.

Next, follow the steps below for each of the subpackage that you intend to use.

If none of them fits your database configuration, create a new directory MyDatabase first and proceed as follows (please send me the modified Setup and mxODBC.h files for inclusion in future releases).

Unix:

Instructions for compiling the C extension(s) on Unix platforms:

cd to the database subpackage directory (e.g. Adabas/, MySQL/)
If you are setting up a new database subpackage, copy all files from the mxODBC directory to the subpackage directory and then edit the mxODBC.h header file and include the appropriate header files for your database
Boot the Makefile system by running
make -f Makefile.pre.in boot
Edit the file Setup:
Fix the include directories, libs and lib paths (this file uses the same syntax as the Modules/Setup file that you edit to configure Python); the file also contains some database specific hints
Build the database interface by running
make
Get your database running, and try to connect with the new module.
If you get an error like 'unresolved symbol: SQLxxx', try to add a '-DDONT_HAVE_SQLxxx' flag to the setup line in Setup and recompile (make clean; make).
Give me feedback :-)

Windows:

Please also see the section below on how to interface to the Windows ODBC Manager; especially the defines mentioned there are important.

Instructions for compiling the C extension for use with the Windows ODBC Manager using VC++ (adapted from generic instructions by Gordon McMillan):

Create a Project for a Win32 Dynamic-Link Library
Add the *.cpp files in the Windows/ subdirectory to the project
Use the same runtime library your Python is built with (should be Multithreaded DLL)
Point the compiler to Python/Include
Point the linker to Python/Lib, Python15.lib
Build
Rename the resulting *.dll files to *.pyd files (this step is optional)
get your database running, and try to connect with the new module;
give me feedback :-)

You now have a new package called ODBC with subpackages for each of your ODBC databases. Accessing a particular database is done by calling the connection constructor of the database subpackage, e.g. dbc = ODBC.Adabas.Connect('host:DB','user','passwd'). You can use multiple databases at the same time using this mechanism.

If you plan to use the dbi.py abstraction module for a particular database, you can access the specific version for that database by importing ODBC.<database name>.dbi, e.g. ODBC.Adabas.dbi.

Please post any bug reports, questions etc. to the db-sig@python.org (see the Python Website for details on how to subscribe) or mail them directly to me.

Special notes regarding the included database subpackages

This section includes some specific notes for preconfigured setups. If you are on WinXX you should always use the ODBC.Windows subpackage and access the databases through the ODBC Driver Manager. The other packages provide Unix based interfaces to the databases.

In general you should always check the path setting in the corresponding Setup file. I can't automate that process, sorry.

ODBC.Adabas -- SuSE Adabas D

The SuSE Linux distribution ships with a free personal edition of Adabas (available in form of RPMs from SuSE). A commercial version is also available, though I'd suggest first trying the personal edition.

If you want to trim down the interface module size, try linking against a shared version of the static ODBC driver libs. You can create a pseudo-shared lib by telling the linker to wrap the static ones into a single shared one:

ld -shared --whole-archive odbclib.a libsqlrte.a libsqlptc.a \
   -lncurses -o /usr/local/lib/libodbc.so

Note: The ADABAS ODBC driver returns microseconds in the timestamp fraction field. Because of this the Setup includes a define to do the conversion to seconds using a microseconds scale instead of the ODBC standard nanosecond scale (see the history section for more details on this problem).

The module has been tested under Linux 2 with Adabas D 6.1.1. Linux Edition.

ODBC.MySQL -- MySQL + MyODBC

MySQL is a SQL database for Unix and Windows platforms developed by TCX. It is free for most types of usage (see their FAQ for details) and offers good performance and stability.

There is one particularity with the ODBC driver for MySQL: all input parameters are being requested as string -- even integers and floats. mxODBC does the needed conversions for you, but they could obviously lead to loss of precision (the interface uses str() to do the conversion so you can test how the data that gets passed to the driver will look like).

Note that although MyODBC includes the free Unix ODBC manager iODBC, this package interfaces directly to the MySQL driver. Use the iODBC package if you intend to connect via the ODBC manager.

Since MySQL does not support transactions, clearing the auto-commit flag on connections (which is normally done per default by the connection constructors) will not work. The subpackage simply uses auto-commit mode as default. You can turn this "work-around" off by editing MySQL/Setup and removing the switch DONT_CLEAR_AUTOCOMMIT if the feature should become available.

Important: The setup MySQL version 3.21.33 + MyODBC-2.50.17 has some serious memory leaks. mxODBC contains a possible workaround for this, but it's probably be wiser to upgrade. TCX advises to use MySQL version 3.22 + MyODBC-2.50.19.

ODBC.Solid -- Solid Server

Solid Tech. offers a free personal edition of their database for Linux in addition to the standard server and webserver licenses. More information about prices, licenses and downloads is available on their website.

BTW: The Solid Server's low-level database API uses ODBC as interface standard (most other vendors have proprietary interfaces), so mxODBC should deliver the best performance possible.

Note: The Solid ODBC driver leaves out some of the ODBC 2.0 catalog functions. The missing ones are: SQLTablePrivileges, SQLColumnPrivileges, SQLForeignKeys, SQLProcedures, SQLProcedureColumns. You won't be able to use the corresponding cursor methods.

The setup for Solid was kindly donated by Andy Dustman from ComStar Communications Corp. He also found a long standing bug that needed fixing.

ODBC.Sybase -- Sybase Adaptive Server

Sybase for Unix doesn't ship with Unix ODBC drivers. You can get them from Intersolv or OpenLink though (see below for URLs).

The included setup is for the Intersolv evaluation drivers and Sybase Adaptive Server 11.5 and was kindly donated by Gary Pennington from Sun Microsystems. It was tested on Solaris 2.6.

To use the OpenLink driver setup instead copy Setup.in to Setup and enable the OpenLink section in Setup before compiling.

ODBC.Oracle -- Oracle

Oracle for Unix doesn't ship with Unix ODBC drivers. You can get them from Intersolv or OpenLink though (see below for URLs).

Once you have installed the ODBC drivers following the vendor's instructions, run make -f Makefile.pre.in boot in the Oracle/ subdirectory, enable the appropriate set of directives in Setup and then run make to finish the compilation.

Using Intersolv drivers is reported to work. Shawn Dyer (irin.com) has kindly provided the setup for this combination and some additional notes:

...we also set the following environment variables:
LD_LIBRARY_PATH= both the oracle lib path and the Intersolv library path
ODBCINI= the odbc.ini file in the Intersolv install
Once you talk to the Intersolv odbc driver, it seems to be a simple matter of setting up the ODBC data source name in their .ini file that has that stuff. At that point you can talk to any of their ODBC drivers you have installed.

To use the OpenLink driver setup instead copy Setup.in to Setup and enable the OpenLink section in Setup before compiling.

ODBC.Informix -- Informix SQL Server [untested]

Informix for Unix doesn't come with Unix ODBC drivers, but there a few source for these: Informix sells the driver under the term "Informix CLI"; Intersolv and OpenLink also support Informix through their driver suites (see below for URLs).

Note: There is also a free Informix SDK available for a few commercial Unix platforms like HP-UX and Solaris. It includes the needed ODBC libs and header files (named infxcli.h and libifsql.a).

Once you have installed the ODBC drivers following the vendor's instructions, run make -f Makefile.pre.in boot in the Informix/ subdirectory, enable the appropriate set of directives in Setup and then run make to finish the compilation.

To use the OpenLink driver setup instead copy Setup.in to Setup and enable the OpenLink section in Setup before compiling.

ODBC.Windows -- Windows ODBC Driver Manager

mxODBC compiles on Windows using VC++ and links against the Windows ODBC driver manager. The necessary import libs and header files are included in the VC++ package but are also available for free in the Microsoft ODBC SDK. Note that the latter is usually more up-to-date.

Compiling the module has to be done in the usual VC++ way (see the instructions above), producing a DLL named mxODBC.pyd. All necessary files are located in the Windows/ subdirectory of the package, the main target being mxODBC.cpp. You should pass the following defines to the compiler:

	    -DHAVE_SQLDriverConnect
	    -DMS_ODBC_MANAGER

The archive already contains a precompiled version of mxODBC provided courtesy of Stephen Ng (version 0.9.0; up-to-date versions are always welcome). He is using the module to interface to MS SQL Server.

Martin Sckopke reported that when connecting to the database through the ODBC manager, no 'host:' prefix to the DSN is necessary. He has the module running on Windows NT and is interfacing to ADABAS D and Oracle 8.0.x without problems.

Notes:

Use the DriverConnect() API to connect to the data source if you need to pass in extra configuration information such as names of log files, etc.

If you have installed the win32 extensions by Mark Hammond et al. you'll run into a naming collision: there already is an odbc module (all lowercase letters) in the distribution that could be loaded instead of the ODBC package (all uppercase letters) depending on your configuration.

AFAIK, there are at least three ways to change this:

rename odbc.pyd from the win32 extension to e.g. win32odbc.pyd
add the ODBC package to a directory that appears before the directory in which odbc.pyd lives
change the Python path (sys.path) to make sure the ODBC package is found first.

ODBC.iODBC -- Unix iODBC Driver Manager maintained by OpenLink

mxODBC compiles against the modified iODBC version shipped with the ODBC driver for MySQL (see notes above). You can get it from TCX for free.

It should also compile against the original version by Ke Jin which is now maintained and distributed under the LGPL by OpenLink (follow the link above).

Note: Use the DriverConnect() API to connect to the data source if you need to pass in extra configuration information such as names of log files, etc.

I tested the interface with iODBC-2.12 as included in the MyODBC-2.50.17 package on Linux using MySQL and ADABAS.

Running mxODBC from a CGI script

ODBC drivers and managers are usually compiled as a shared library. When running CGI scripts most HTTP daemons (aka web servers) don't pass through the path for the dynamic loader (e.g. LD_LIBRARY_PATH) to the script, thus importing the mxODBC C extension will fail with unresolved symbols because the loader doesn't find the ODBC driver/manager's libs.

To have the loader find the path to those shared libs you can either wrap the Python script with a shell script that sets the path according to your system configuration or tell the HTTP daemon to set or pass these through (see the daemon's documentation for information on how to do this; for Apache the directives are named SetEnv and PassEnv).

Not mentioned here...

If you want to run mxODBC in a Unix environment and your database doesn't provide an Unix ODBC driver, you can try the drivers sold by Intersolv. They have an evaluation package available. To see if their ODBC driver package supports your client/server setup check these two matrices: server based or client based solutions.

Another source for commercial ODBC drivers is OpenLink. To see if they support your client/server setup check this matrix. They were giving away 2-client/10-connect licenses for free last time I checked.

For a fairly large list of sources for ODBC drivers have a look on this page provided by the CorVu Cooperation. They also have some informative pages that describe common database functions and operators which are helpful.

Alternatively, you could write a remote client (in Python) that communicates with your database via a WinNT-Box. Most databases provide Win95/NT ODBC drivers so you can use mxODBC with the Windows ODBC manager. This method is not exactly high-performance, but cheaper (provided you can come up with a running version in less than a day's work, that is...). The Python standard lib module SocketServer.py should get you going pretty fast. Protocol and security are up to you, of course.

Support

I am providing commercial support for this package through Python Professional Services Inc.. If you are interested in receiving information about this service please visit their web-site.

What I'd like to hear from you...

Can you get the module to compile/work on other Unix platforms other than Linux ? [Solaris: yes; AIX: yes] On Windows 95/NT ? [Yes]
Does the package link to and work with other ODBC 2.0 compliant databases ? [preconfigured ones: yes]
Can it be made to link to the Windows ODBC Manager DLL ? [Yes]
To the Unix iOBDC Manager ? [Yes]
I'm always happy to receive up-to-date compiled version of mxODBC for Windows 95/NT -- I don't do much development work on that platform and don't have a VC++ compiler.

Copyright & Disclaimer

Permission to use, copy, modify, and distribute this software and its documentation for any purpose limited by the restrictions put forward in the following paragraph and without fee or royalty is hereby granted, provided that the above copyright notice appear in all copies and that both the copyright notice and this permission notice appear in supporting documentation or portions thereof, including modifications, that you make.

Redistribution of this software or modifications thereof for commercial use requires a separate license which must be negotiated with the author. As a guideline, packaging the module and documentation with other free software will be possible without fee or royalty as described above. This license does not allow you to ship this software as part of a product that you sell.

THE AUTHOR MARC-ANDRE LEMBURG DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE !

Please also see the additional licensing information. This is especially important if you intend to use the product in a commercial setting.

History & Future

Things that still need to be done:

Provide some examples.
Add a callback mechanism that replaces the current exception raising for SQL warnings.
Implement .callproc() and .nextset() APIs
Verify threading friendlyness and correctness.

Changes from 1.0.0 to 1.0.1:

Removed the SQL codes NULL_HENV, NULL_HDBC, NULL_HSTMT from the SQL class. These are handles (and pointers on some platforms) that aren't used by the exposed ODBC APIs anyway and can cause compiler warnings.
Andy Dustman reported that the module seems to work correctly with threads enabled. He also reported memory leakage when using mxODBC/Solid with threads which does not occur when used without threads. The exact source of this leakage is not yet determined.
Fixed a bug in the routine for fetching LONGVARCHAR column data. The previous version introduced a bug due to a change in the default setting of the ADABAS workaround.
The caching of SQL statements in cursor.execute() can now be deactivated using a compile time switch (see Setup.in for details). The cache was also extended to do string comparism in case the passed string object is not the same.
Changed the string representation of the MySQL connection and cursor objects to indicate that MySQL is being interfaced to.
Renamed compile time flag HAVE_ODBC_DRIVER_CONNECT to HAVE_SQLDriverConnect.

Changes from 0.9.0 to 1.0.0:

Fixed a problem with catalog functions reporting warnings about SQLCancel being treated as SQLFreeStmt/Close.
Corrected the TIMESTAMP tuples to actually only use 6 entries as documented. The internal routines used to use 7 entries (the last one being nanoseconds). They now add the nanoseconds to the second part which is returned as a float.
The strange thing about the timestamp fractions is that the ODBC standard manuals say they represent nanoseconds while an older SQL manual from IBM states they should in fact be microseconds. I've now adopted the ODBC POV, but am still not sure what is right and what wrong.
Since I couldn't make up my mind, I've introduced a compile time switch to set things up to use microseconds instead of nanoseconds: USE_MICROSECOND_FRACTIONS. See Setup.in for more information.
Some databases seem to use the old standard too: at least ADABAS does. The switch is defined in the ADABAS Setup per default.
Added some extra checks for conversion to Unix ticks. An over/underflow will now raise an OverflowError.
Added support for fetching fields which have an unknown size (other than LONGs). Some catalog functions return such fields for e.g. MySQL which is probably an error but one that can easily be worked around.
Enlarged the buffers for column names. ANSI SQL (and many databases out there) define a limit of 18 characters for column and table names (this gives a limit of 37 characters for 'table.column' descriptions). Some databases also allow longer names, so I did two things: turned the limit into a compile time option (see mxODBC.h for details) and raised the prior limit I had used (19) to 37 characters. Thanks to Tino Wildenhain (peacock.de) for pointing this out to me.
Added Sybase configuration donated by Gary Pennington from Sun Microsystems Ltd.
Upgrade Note: Moved the initialization of the SQL class to a separate source file. To upgrade, you'll have to adjust your Setup files to include the new source file mxSQLCodes.c. Have a look at Setup.in to see how it's done (or simply copy it to Setup and make the same changes to it that you did for your existing configuration). For Windows, you'll simply have to add mxSQLCodes.cpp from the Windows subdir to the project.
Added better support for SQL warnings: SQL state "00000" is now interpreted as success, even though the SQL API returned an error. The ODBC docs had a well hidden note about this.
Fixed some minor things to make the module compile with less warnings on WinXX with VC++. Thanks to Stephen Ng for the constructive feedback.
Fixed an allocation bug that resulted in corrupt memory areas. The bug was in the allocation routine for numeric columns. Thanks again to Stephen Ng for tracking this error down.
Added a possible workaround for a bug in MyODBC 2.50.17 which causes serious memory leakage (SQLCancel does not free up cursor resources properly). TCX recommends upgrading to the most recent versions of MySQL *and* MyODBC.
Added an untested Informix setup. The paths and settings are based on the files included in the free Informix SDK which is downloadable from their site.
Added dbc.closed instance variable.
Changed the exception raised for invalid ranges in date/time tuples from InterfaceError to the standard ValueError.
Added description of used exceptions to the docs.
Added explicit variable and parameter unbinding in a few places. This should make memory leakage due to the drivers not taking care of this themselves less likely.
Fixed a possible memory leakage in cursor.execute() that could occur when passing a list of parameter tuples using data types that need to be auto-converted by mxODBC to the types the driver wants to see.
Made sure that when passing SQL_C_CHAR data to the driver the data is 0-terminated. This is not really needed since the driver should use the provided length field, but you never know...
Added another SQLFreeStmt to the execute loop for lists of tuples: although executing multiple SELECTs using different parameters without fetching any data is really useful the docs state that this call must be made.
Added an optional name argument to the cursor constructor to allow creating named cursors.
Added new cursor methods .getcursorname() and .setcursorname().
Added several small fixes submitted by Wolf Logan from searchbutton.com to make the module compile with less warnings on VC6.
Fixed a memory leakage in the code for caching the most recently executed SQL statement found by Wolf Logan. Many thanks go to him for tracking this bug.
Added a compile time option to turn of reporting warnings by means of raising an exception: DONT_REPORT_WARNINGS.
Changed the exception type for the SQL error code 01000 from InternalError to Warning. Thanks Wolf Logan for noticing this bugglet.
Added Python threading macros around typically long running SQL function calls.
Added yet more fixes to make compilation on Windows platforms easier.
Changed exceptions Error and Warnings to be subclasses of exceptions.StandardError. This intergrates them into Python's standard exception hierarchy.

Changes from 0.8.1 to 0.9.0:

Added a detailed debugging facility. To use it, compile the module with flag -DMAL_DEBUG and run the interpreter in debugging mode (flag -d). Verbose output will then be written to mxODBC.log in the current working directory.
Fixed a bug that caused passing fractions of a second in TIMESTAMP fields to fail (thanks to Gert Degreef for pointing me to it).
Added a precompiled version of mxODBC for Windows which was kindly provided by Martin Sckopke (gis.ibfs.de).
Added more preconfigured subpackages and enhanced the documentation a bit.
Fixed a bug in the error handling routine. It caused very picky drivers/managers to not report any errors other than mxODBC.InterfaceError. This was the case for iODBC, BTW.
Important: Enabled error reporting for clearing AUTOCOMMIT. This may cause connections to fail that were working before. Especially MySQL is touched by this, since it currently does not implement transactions. There is a new compile switch DONT_CLEAR_AUTOCOMMIT to set the default value of the clear_auto_commit option on connection constructors to 0. It is used for the MySQL subpackage.
Moved the SQL_XXX defines to a class named SQL. This leaves the module dictionary in a much saner state. The class defines nearly all symbols that are included in ODBC 2.0. Thus, SQL_XXX values are now available as SQL.XXX.
Added dbc.getinfo().
The iODBC subpackage is now tested and working. Well, at least for me it is.

Changes from 0.8.0 to 0.8.1:

Added checks for closed cursors to all interface methods. They will now raise a ProgrammingError whenever you try to do actions on closed cursors. This makes the interface a lot safer against core dumps resulting from malfunctioning ODBC drivers that have problems handling operations on closed cursors (they should return an error, but instead simply dump core...).
Changed the exception that was being raised whenever you tried to use a cursor on a closed connection from InterfaceError to ProgrammingError (after all, it's not the interface's fault that something doesn't work ;).
Trying to create cursors on closed connections will now always raise a ProgrammingError.

Changes from 0.7 to 0.8.1:

Fixed a bug in the init-function that caused an endless loop in case something goes wrong while initialising the module (e.g. if the mxDateTime module can't be found).
Added a work-around for get/setconnectoption to get them to work with ADABAS: previous versions dumped core whenever you tried to set a connection option.
Moved DECIMAL and NUMERIC types from integer to floating point conversion.
Fixed a bug that could cause wrong data to be send to the database when using passing integers to fields that expect floating point numbers. Thanks to Andy Dustman for pointing me at these.
Added conversion code that automatically converts strings to floats or integers as required. You can now pass e.g. a string for an INTEGER column and a routine similar to the builtin int() will do the conversion for you. Same for floats. It is still better to pass the "right" types as no extra conversion is done in this case.
Added a datetimeformat attribute to connection objects. This is used as default for all cursors created using the connection object.
Added tests to all methods that ensure an open connection. The ADABAS ODBC client libs dump core if the cursor executes commands on closed connections. This also makes garbage collecting stray cursors a lot safer.
Added InterfaceError. All interface related errors are now reported as InterfaceErrors.
Changed the package structure: There now is one subpackage per preconfigured database. This allows for easy inter-database communcation and also makes switching from one database to another less cumbersome.
Changed version numbering to major.minor.patchlevel.

Changes from 0.6 to 0.7:

Documented cursor.datetimeformat.
Synchronized the module with my mxDateTime extension package.
Updated the dbi.py abstraction module. It is still not 100% compatible with what the API 1.0 specs say (the module does not return instances of dbiRaw and dbiDate), but I'm reluctant to fix it.
Added a cleanup function that get's called when the interpreter finalizes. It deallocates the ODBC environment.
Added __members__ attribute to cursors. This returns a list of known attributes (e.g. dir() looks for it and displays them).
Added optional fourth parameter clear_auto_commit to the connection constructor.
Date, time and timestamp can now also be processed via Python strings. The conversion is left to the ODBC driver/manager.
Fixed a bug in the allocation routines of both the connection and the cursor type that sometimes caused a segfault due to an ODBC error occurring while connecting.
Added a new API DriverConnect and renamed the old API Connect. The old one is still aliased to ODBC for DB API compliance and backward compatibility.
Fixed a refcount bug that prevented connection objects from begin garbage collected after a cursor was used on them (the cursor didn't dereference the connection object).

Changes from 0.5 to 0.6:

ODBC is now a package. The name mxODBC is only used for the C extension (with the mx-prefix to prevent naming hassles). You can access the module by importing ODBC and the dbi-submodule as ODBC.dbi.

SQL type	Python type	Comments
CHAR, VARCHAR, LONGVARCHAR (LONG in SQL)	String	The conversion truncates the string at the first 0-byte or the SQL field length -- whichever comes first. The handling of special characters depends on the codepage the database uses.
BINARY, VARBINARY, LONGVARBINARY (LONG BYTE in SQL)	String	Truncation at the SQL field length. These can contain embedded 0-bytes and other special characters.
TINYINT, SMALLINT, INTEGER, BIT	Integer	Conversion from the Python integer (a C long) to the SQL type is left to the ODBC API of the database, so expect the usual truncations.
BIGINT	Long Integer	Conversion to and from the Python long integer is done via string representation since there is no C type with enough precision to hold the value. Because of this, you might receive errors indicating truncation or errors because the database sent string data that cannot be converted to a Python long integer. Not all SQL databases implement this type, MySQL is one that does.
DECIMAL, NUMERIC, REAL, FLOAT, DOUBLE	Float	Conversion from the Python float (a C double) to the SQL type is left to the ODBC API of the database, so expect the usual truncations.
DATE	DateTime instance / ticks / (year,month,day) / Python string	While you should use DateTime instances, the module also excepts ticks (Python numbers indicating the number of seconds since the Unix Epoch; these are converted to local time and then stored in the database) and tuples (year,month,day) on input. The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTime instances.
TIME	DateTimeDelta instance / tocks / (hour,minute,second) / Python string	While you should use DateTimeDelta instances, the module also excepts tocks (Python numbers indicating the number of seconds since 0:00:00.00) and tuples (hour,minute,second) on input. The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTimeDelta instances.
TIMESTAMP	DateTime instance / ticks / (year,month,day,hour,minute,second) / Python string	While you should use DateTime instances, the module also excepts ticks (Python numbers indicating the number of seconds since the epoch; these are converted to local time and then stored in the database) and tuples (year,month,day,hour,minute,second) on input. The type of the return values depends on the setting of cursor.datetimeformat. Default is to return DateTime instances.