Interface : Datatypes : DB-API Extensions : Functions : Subpackages ( Adabas : MySQL : Solid : Sybase : Oracle : Informix : Windows ODBC Manager : Unix iODBC Manager : EasySoft ODBC Bridge : Other DBs ) : Hints & Links : Examples : Structure : Download & Installation : Testing : Support : Copyright : License : History : Home | Version 1.1.1 |
The mxODBC package provides a nearly 100% Python DB 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.
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 the deviations from the specification:
cursor.execute()
though.
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()
API which is
available for ODBC managers and some ODBC drivers.
(sqlstate, sqltype, errortext, lineno)
where lineno
refers to the line number in the
mxODBC.c file (to ease debugging the module).
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.
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.
Normally, auto-commit is turned off by the constructor. 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.
Note that a compile time switch (DONT_CLEAR_AUTOCOMMIT)
allows altering the default value for
Use the connection method
With auto-commit turned on, transactions are effectively
disabled. The
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
Note that this API is only available if the interface was
compiled with HAVE_SQLDriverConnect switch defined. See the
subpackages section and the
subpackage's Setup file for details.
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. The method returns the data as 32-bit integer. It is up to
the callee to decode the integer using the SQL
defines.
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.
Note: This is an extension to the DB-API spec. The
specification does not allow any arguments to the
constructor.
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.
When connecting to a database with transaction support, you
should explicitly do a
Note that 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.
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.
If you pass
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.
The module uses the DB API 2.0 exceptions layout.
You can use this class to catch all errors related to
database or interface failures.
Error is a subclass of exceptions.StandardError.
Warning is a subclass of exceptions.StandardError. This may
change in a future release to some other baseclass
indicating warnings.
This is the exception inheritance layout:
A hint for troubles with Warning exceptions:
If you are interested in the exact mapping of SQL error codes to
exception classes, have a look at the
Connection constructors, methods and attributes
Connect(dsn,user='',password=''[,clear_auto_commit=1])
dsn
indicates the data source to be used, user
and
password
are optional and used for database
login.
clear_auto_commit
.
db.setconnectoption(SQL.AUTOCOMMIT,
SQL.AUTOCOMMIT_ON|OFF|DEFAULT)
(see below) to adjust
the connection's behaviour to your needs.
rollback()
method will raise a
NotSupportedError
when used on such a
connection.
ODBC(dsn,user='',password=''[,clear_auto_commit=1])
Connect()
needed for DB API
1.0 compliance.
connect(dsn,user='',password=''[,clear_auto_commit=1])
Connect()
needed for DB API
2.0 compliance.
DriverConnect(DSN_string[,clear_auto_commit=1])
Connect()
constructor.
Connect()
for comments on
clear_auto_commit.
connection.setconnectoption(option,value)
option
itself must be an
integer. Suitable option values are available through the
SQL
class (see below), e.g. SQL.AUTOCOMMIT
corresponds to the SQL option (SQL_AUTOCOMMIT in C).
connection.getconnectoption(option)
option
must be an
integer. Suitable option values are available through the
SQL
class (see below).
connection.getinfo(info_id)
info_id
must be an
integer. Suitable values are available through the
SQL
class (see below).
connection.cursor([name])
cursor.getcursorname()
(see the Cursors section).
connection.bindmethod
connection.datetimeformat
DATETIME_DATETIMEFORMAT
[default]
TIMEVALUE_DATETIMEFORMAT
TUPLE_DATETIMEFORMAT
STRING_DATETIMEFORMAT
connection.closed
ProgrammingError
to be raised. This variable can
be used to conveniently test for this state.
.rollback()
or
.commit()
prior to closing the connection. mxODBC
does an automatic rollback of the transaction when the
connection is closed.
Cursor methods and attributes
None
as a value where a string would be
expected, that entry is converted to NULL before passing it to
the underlying API.
cursor.tables(qualifier,owner,table,type)
cursor.tableprivileges(qualifier,owner,table)
cursor.columns(qualifier,owner,table,column)
cursor.columnprivileges(qualifier,owner,table,column)
cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table,
foreign_qualifier,foreign_owner,foreign_table)
cursor.primarykeys(qualifier,owner,table)
cursor.procedures(qualifier,owner,procedure)
cursor.procedurecolumns(qualifier,owner,procedure,column)
cursor.specialcolumns(qualifier,owner,table,coltype,scope,nullable)
cursor.statistics(qualifier,owner,table,unique,accuracy)
connection.gettypeinfo(sqltypecode)
cursor.setcursorname(name)
cursor.getcursorname()
cursor.datetimeformat
connection.datetimeformat
instance variable and defaults
to the creating connection object's settings for
datetimeformat
.
Exceptions
Error, error
error
is just
an alias to Error
needed for DB-API 1.0
compatibility.
Warning
InterfaceError
DatabaseError
DataError
OperationalError
IntegrityError
InternalError
ProgrammingError
NotSupportedError
StandardError
|__Warning
|__Error
|__InterfaceError
|__DatabaseError
|__DataError
|__OperationalError
|__IntegrityError
|__InternalError
|__ProgrammingError
|__NotSupportedError
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.
mxODBC_ErrorCodeTranslations
array defined in
mxODBC.c.
mxODBC allows to use two different input variable binding modes (also see the Constants section):
Binding Mode | Value of connection.bindmethod | Comments |
SQL type binding | BIND_USING_SQLTYPE | The database is asked for the appropriate data type and mxODBC tries to convert the input variable into that type. |
Python type binding | BIND_USING_PYTHONTYPE | mxODBC looks at the type of the input variable and passes its value to the database directly; conversion is done by the ODBC driver/manager as necessary. |
The default depends on the settings with which the ODBC subpackage was compiled. If not indicated in the subpackage section, it is set to SQL type binding, since this offers more flexibility.
Note that for SQL type binding to be possible, mxODBC needs a working SQLDescribeParam() API implementation. This is checked at connect time and the binding style adjusted to Python type binding, if mxODBC cannot rely on SQLDescribeParam().
The following data types are supported for output variable mapping and for BIND_USING_SQLTYPE input variable binding:
SQL Type | Python Type | Comments |
CHAR, VARCHAR, LONGVARCHAR (TEXT, BLOB or LONG in SQL) | String |
The conversion truncates the string at the SQL field
length. The handling of special characters depends on
the codepage the database uses.
Some database drivers/managers can't handle binary data in these column types, so you better check the database's capabilities with the incluced test script first before using them. |
BINARY, VARBINARY, LONGVARBINARY (BLOB or LONG BYTE in SQL) | String |
Truncation at the SQL field length. These can contain
embedded 0-bytes and other special characters.
Handling of these column types is database dependent. Please refer to the database's documentation for details. Many databases store the passed in data as-is and thus make these columns types useable as storage facility for arbitrary binary data. |
TINYINT, SMALLINT, INTEGER, BIT | Integer | Conversion from the Python integer (a C long) to the SQL type is left to the ODBC driver/manager, 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 driver/manager, so expect the usual truncations. |
DATE | DateTime instance / ticks / (year,month,day) / 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 and whether the ODBC driver/manager does return the value with proper type information. Default is to return DateTime instances in case it returns the data with SQL DATE type code. |
TIME | DateTimeDelta instance / tocks / (hour,minute,second) / 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 and whether the ODBC driver/manager does return the value with proper type information. Default is to return DateTimeDelta instances in case it returns the data with SQL TIME type code. |
TIMESTAMP | DateTime instance / ticks / (year,month,day, hour,minute,second) / 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 and whether the ODBC driver/manager does return the value with proper type information. Default is to return DateTime instances in case it returns the data with SQL TIMESTAMP type code. |
Note that output bindings can only be done using the above mapping by mxODBC if the database correctly identifies the type of the output variables.
Some ODBC drivers return data using different type codes than the ones accepted for input, e.g. a database might accept a time value, convert it internally to a timestamp and then return it in a subsequent SELECT as timestamp value. mxODBC cannot know that the value only contains valid time information and no date information and thus converts the output data into a DateTime instance instead of a DateTimeDelta instance (which would normally be returned for time values).
Use the incluced test script to check for this behaviour. It tests many common column types and reports the outcome.
These mappings are used for input variables in BIND_USING_PYTHONTYPE mode (see the ODBC documentation for more information on how the C datatypes are mapped to SQL column types). Output variables are treated by applying the conversions defined in the previous section.
Python Type | SQL C Data Type | Comments |
String | CHAR (char *) | The conversion truncates the string at the SQL field length. The string may also contain binary data, if the ODBC driver/manager supports this. |
Integer | SLONG (signed long) | Conversion from the signed long to the SQL column type is left to the ODBC driver/manager, so expect the usual truncations. |
Long Integer | CHAR (char *) | Conversion from the Python long integer is done via the string representation since there usually is no C type with enough precision to hold the value. |
Float | DOUBLE (double) | Conversion from the Python float (a C double) to the SQL column type is left to the ODBC driver/manager, so expect the usual truncations. |
DateTime | TIMESTAMP | Converts the DateTime instance into a TIMESTAMP struct defined by the ODBC standard. The ODBC driver may use the time part of the instance or not depending on the SQL column type (DATE or TIMESTAMP). |
DateTimeDelta | TIME | Converts the DateTimeDelta instance into a TIME struct defined by the ODBC standard. Fractions of a second will be lost in this conversion. |
Buffer (new in Python 1.5.2) | CHAR (char *) | No conversion is done, the character buffer is passed directly to the ODBC driver/manager. The buffer has to be a single segment buffer. |
Any other type | CHAR (char *) | Conversion is done by calling str(variable) and then passing the resulting string value to the ODBC driver/manager. |
While you should always try to use the above Python types for passing input values to the respective columns, the package will try to automatically convert the types you give into the ones the database expects when using the BIND_USING_SQLTYPE bind method (see the Constants section), e.g. an integer literal '123' will be converted into an integer 123 by the interface if the database requests integers.
The situation is different in BIND_USING_PYTHONTYPE mode: the Python type used in the parameter is passed directly to the database, thus passing '123' or 123 does make a difference and could result in an error from the database.
Note: You shouldn't rely on this feature in case you intend to move to another DB API compliant database module, since this is an extension to the DB API standard.
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.
To check the ODBC driver/manager capabilities and support for the above column types, use the incluced test script.
mxODBC provides a few additional helper functions available through the top-level ODBC package. These are:
format_resultset(cursor,headers=(),
colsep=' | ',headersep='-',stringify=repr)
-header- -headersep- -row1- -row2- ...
headers may be given as list of strings. If not given, or too short, the function will add numbered columns as appropriate.
Columns are separated by colsep; the header is separated from the result set by a line of headersep characters.
The function calls stringify to format the value data returned by the driver into a string. It defauls to repr().
print_resultset(cursor,headers=())
headers can be given as list of column header strings.
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 MS ODBC Driver Manager. The other packages provide Unix based interfaces to the databases.
Even though the setups include many database specific settings, you should always check the paths and filenames used in the corresponding Setup file because these depend on your specific installation.
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/libadabasodbc.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. Since the ODBC driver for Adabas on Linux also provides the DriverConnect() API it is also exposed by the package (even though the driver itself is not an ODBC manager).
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 processed as string -- even integers and floats. The ODBC driver implements the necessary conversions. mxODBC uses the Python Type binding method to bind the input parameters; see the Constants section.
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.
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.
Sybase for Unix doesn't ship with Unix ODBC drivers. You can get them from Intersolv or OpenLink though (see the Hints section 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.
Oracle for Unix doesn't ship with Unix ODBC drivers. You can get them from Intersolv or OpenLink though (see the Hints section 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 installOnce 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.
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 the Hints section 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.
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 (MDAC). Note that the latter is usually more up-to-date.
Compiling the module has to be done in the usual VC++ way (see the Windows install instructions), producing a DLL named mxODBC.pyd. All necessary files are located in the Windows/ subdirectory of the package, the main target being mxODBC.cpp.
Martin Sckopke (gis.ibfs.de) 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.
Stephen Ng (grossprofit.com) who contributed the previous compiled versions and the VC6 project files is using mxODBC to interface to MS Access.
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:
The subpackage defaults to SQL type binding mode, but reverts to Python type binding in case the connection does not support the SQLDescribeParam() API. MS Access is one candidate for which this API is not useable.
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.
EasySoft currently has an ODBC-ODBC bridge in beta which allows you to connect to e.g. a MS SQL Server running on an NT box from your Linux web-server.
The included setup is for the beta 0.2.2 of that bridge. You can download it via their FTP Server. Version 0.2.2 is runtime limited until July 1999.
Remember to download setups for client (Linux in the example) and server (NT in the example).
Check out the list of links to other resources in the next section. Creation of new sub packages is explained in the installation guide.
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).
There are several resources available online that should help you getting started with ODBC. Here is a small list of links useful for further reading:
Note: If you are not happy about the size of the SDK download (over 31MB), you can also grab the older 3.0 SDK which might still be available from a FTP server. Look for "odbc3sdk.exe" using e.g. FTP Search.
Another source for commercial ODBC drivers is OpenLink. To see if they support your client/server setup check this matrix. They are giving away 2-client/10-connect licenses for free.
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.
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('database','user','passwd')
>>> c = db.cursor()
>>> c.execute('select count(*) from test')
>>> c.fetchone()
(305,)
>>> c.tables(None,None,None,None)
8
>>> ODBC.print_resultset(c)
Column 1 | Column 2 | Column 3 | Column 4 | Column 5
---------------------------------------------------------------
'' | '' | 'test' | 'TABLE' | 'MySQL table'
'' | '' | 'test1' | 'TABLE' | 'MySQL table'
'' | '' | 'test4' | 'TABLE' | 'MySQL table'
'' | '' | 'testblobs' | 'TABLE' | 'MySQL table'
'' | '' | 'testblobs2' | 'TABLE' | 'MySQL table'
'' | '' | 'testdate' | 'TABLE' | 'MySQL table'
'' | '' | 'testdates' | 'TABLE' | 'MySQL table'
'' | '' | 'testdatetime' | 'TABLE' | 'MySQL table'
>>> c.close()
>>> db.close()
>>>
When connecting to a database with transaction support, you
should explicitly do a .rollback()
or
.commit()
prior to closing the connection. In
the example this was omitted since MySQL does not support
transactions.
[ODBC] [Adabas] dbi.py showdb.py Doc/ [EasySoft] dbi.py [Informix] dbi.py [Misc] proc.py test.py [MySQL] dbi.py [Oracle] dbi.py [Solid] dbi.py [Sybase] dbi.py [Windows] vc6/ dbi.py [iODBC] dbi.py [mxODBC] dbi.py LazyModule.py ODBC.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
.
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 mxODBC archive, unzip it to a
directory on your Python path
e.g. /usr/local/lib/python1.5/site-packages/ on
Unix or C:\Python\Lib\ under Windows (note: do not
add the ODBC directory itself to the 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 mxODBC version 0.5.
You may also want to download the ODBC reference manual from
SolidTech
which I used to develop this package.
Note: The subpackages 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:
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
make -f Makefile.pre.in boot
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
make
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).
Windows:
The archive includes an up-to-date precompiled binary for
Windows. See the Windows ODBC Manager
section for details.
Instructions for compiling the C extension for use with the
Windows ODBC Manager using VC++ (adapted from generic
instructions by Gordon McMillan):
Stephen Ng has sent me a set of project files for VC6 which
are included in the ODBC/Windows/vc6 subdirectory.
These might help you getting starting a little faster.
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.
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.
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.
The package includes a rudimentary test script that checks
some of the database's features. As side effect this also
provides a good regression test for the mxODBC interface.
To start the test, simply run the script in
ODBC/Misc/test.py. It will generate a few temporary
tables (named mxODBC0001, mxODBC0002, etc; no existing tables
will be overwritten) and then test the interface - database
communication. The tables are removed after the tests have run
through. Here is some typical output:
MySQL 3.22.20a with MyODBC 2.50.22a en direct:
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.
© 1997, 1998, Copyright by Marc-André Lemburg
(mailto:mal@lemburg.com);
All Rights Reserved.
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 !
Things that still need to be done:
Changes from 1.1.0 to 1.1.1:
Changes from 1.0.1 to 1.1.0:
Some of these databases simply ignored the rollback
without any notice to the programmer (e.g.
MySQL).
mxODBC now does a check to make sure that the connection
supports transactions and raises a NotSupportedError in
case .rollback() is called on such a connection (using
AUTOCOMMIT also means disabling transactions !).
Changed the default setup for MySQL: since MySQL
does not support transactions, the new switch
DONT_HAVE_TRANSACTIONS is defined per default in the
Setup file.
Note that usage of dbi.py is depreciated starting
with DB API 2.0. It will no longer be supported in
future versions of mxODBC.
Changes from 1.0.0 to 1.0.1:
Changes from 0.9.0 to 1.0.0:
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.
Changes from 0.8.1 to 0.9.0:
Changes from 0.8.0 to 0.8.1:
Changes from 0.7 to 0.8.1:
Changes from 0.6 to 0.7:
Changes from 0.5 to 0.6:
Installation
connection =
ODBC.Adabas.Connect('host:DB','user','passwd')
. You
can use multiple databases at the same time using this
mechanism.
ODBC.Adabas.dbi
. Note
that the dbi module is depreciated by DB API 2.0
and no longer maintained.
Testing the Database Connection
Subpackage Name [MySQL]:
DriverConnect arguments [DSN=test;UID=root]:
Clear AUTOCOMMIT ? (1/0) [0]
Run tests continuously to check for leaks ? (y/n) [n]
Show driver type information ? (y/n) [n]
Output file [stdout]:
Test suite:
Connecting to the database.
Connected to DBMS MySQL 2.50.22 using driver myodbc.dll 2.50.22; ODBC 02.50
BIGINT column type with 64bits : supported
BINARY column type : type not supported
BIT column type : supported
BLOB column type with binary data : supported
BLOBs with >32kB ASCII data : supported
CHAR column type : supported
CHAR column type with binary data : supported
CHAR padding (with spaces) : not supported
DATE column type : supported
DATETIME column type : supported
DATETIME with string values : not supported
DECIMAL column type : supported
DOUBLE column type : supported
FLOAT column type : supported
IMAGE column type : type not supported
INT column type : supported
INT column type using array processing : supported
LONG BYTE column type : type not supported
LONG BYTE with >32kB ASCII data : type not supported
LONG column type : type not supported
LONGs with >32kB ASCII data : type not supported
LONGs with binary data : type not supported
MEMO column type : type not supported
MEMOs with >32kB ASCII data : type not supported
MEMOs with binary data : type not supported
TEXT column type : supported
TIME column type : supported
TIMESTAMP column type : supported
TIMESTAMP data having fractions : not supported
Transactions : not supported
VARBINARY column type : type not supported
VARCHAR column type : type not supported
VARCHAR column type with binary data : type not supported
Variable Bind Method : Python Type
Disconnecting.
Support
What I'd like to hear from you...
To the Unix iOBDC Manager ? [Yes]
Copyright & Disclaimer
Please also see the additional licensing
information. This is especially important if you
intend to use the product in a commercial setting.
History & Future
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.