Connecting to databases that support ODBC from Python with mxODBC

This mxODBC tutorial shows how to connect Python with data stores such as Microsoft SQL Server, Oracle, Salesforce, DB2, Sybase ASE, InterBase, Microsoft Access, and Derby.

Contents

Introduction

mxODBC is a Python extension that provides a DB-API 2.0 interface to databases for which an ODBC driver is available.

Easysoft ODBC drivers have been tested with:

Building mxODBC with unixODBC support

These instructions are for Linux and UNIX users. On Windows, mxODBC links against the Windows ODBC Driver Manager. Windows users can therefore use an Easysoft ODBC driver with mxODBC in the same way as any other ODBC driver.

Marc-Andre Lemburg (author of mxODBC) has added a configuration section to mxODBC specifically for the Easysoft ODBC-ODBC Bridge. This is a significant help when building mxODBC against the ODBC-ODBC Bridge. However, for recent versions of mxODBC (3.0.0, 2+), we strongly recommend building mxODBC with unixODBC support. You must build mxODBC with unixODBC support to use mxODBC with Easysoft ODBC drivers other than the ODBC-ODBC Bridge client.

Follow the mxODBC installation instructions at the mxODBC web site.

Building mxODBC 2+ with unixODBC support

Here is an example that shows how to install mxODBC 2.0.6. It also describes the changes you need to make to build mxODBC with unixODBC support.

  1. Download egenix-mx-base-2.0.6.tar.gz, unpack and cd into the created directory.
  2. Read the README file, which says to run: 
    python setup.py install
  3. Download egenix-mx-commercial-2.0.7.tar.gz, unpack and cd into the created directory.
  4. Open mxCOMMERCIAL.py and search for: 
    packages[len(packages):] = [

#
# These are the subpackages which the installer will install.
# If you want to disable installing one of the default
# subpackages or would like to add a new subpackage, edit this
# list accordingly and also add an Extension() entry in the list
# of C extensions below. You should use the information from the
# Setup file in the subpackage to modify the Extension() entry as
# needed.
#
'mx.ODBC.iODBC',
'mx.ODBC.unixODBC',

    Delete the line containing 'mx.ODBC.iODBC'.

    Search for the following lines:

    Extension('mx.ODBC.iODBC.mxODBC',
  ['mx/ODBC/iODBC/mxODBC.c',
   'mx/ODBC/iODBC/mxSQLCodes.c'
  ],
  include_dirs=['mx/ODBC/iODBC',
   '/usr/local/iODBC/include'],
  define_macros=[('iODBC', None)],
  library_dirs=['/usr/local/iODBC/lib'],
  libraries=['iodbc']
  ),

    Delete these lines.

  5. Do one of the following:
    • If you're using the unixODBC Driver Manager included in an Easysoft ODBC driver distribution:

      In:

      Extension('mx.ODBC.unixODBC.mxODBC',
  ['mx/ODBC/unixODBC/mxODBC.c',
   'mx/ODBC/unixODBC/mxSQLCodes.c'
  ],
  include_dirs=['mx/ODBC/unixODBC',
   '/usr/local/unixODBC/include'],
  define_macros=[('unixODBC', None)],
  library_dirs=['/usr/local/unixODBC/lib'],
  libraries=['odbc']
  ),

      Change all occurrences of /usr/local/unixODBC to /usr/local/easysoft/unixODBC.

      –Or–

    • If you're using another copy of unixODBC, make sure the include_dirs and library_dirs entries shown above are correct for the directory where you installed unixODBC.
  6. Run python setup.py install (refer to mxODBC's README).

Building mxODBC 3+ with unixODBC support

Here's an example that shows how to install mxODBC 3.0.0. It also describes the changes you need to make to build mxODBC with unixODBC support.

  1. Download the mx Base Distribution for your platform, unpack and cd into the created directory.

    Refer to the note on the mx Base Distribution web page, which explains how to find out whether you need the UCS2 or UCS4 version of mx Base.

  2. Read the installation instructions on the mx Base Distribution web page, which say to run: 
    python setup.py build --skip install
  3. Download the mxODBC distribution for your platform, unpack and cd into the created directory.
  4. Open mxODBC.py and search for: 
    # iODBC 3.0.x manager
mx_Extension('mx.ODBC.iODBC.mxODBC',
             ['mx/ODBC/iODBC/mxODBC.c',
              'mx/ODBC/iODBC/mxSQLCodes.c'],
             include_dirs=['mx/ODBC/iODBC'],
             define_macros=[('iODBC', None),
                            ('WANT_UNICODE_SUPPORT', None)],
             needed_includes=[('sql.h',
                               ['/usr/local/iODBC/include',
                                '/usr/local/iodbc/include'],
                               'iODBC driver manager')],
             needed_libraries=[('iodbc',
                                ['/usr/local/iODBC/lib',
                                 '/usr/local/iodbc/lib'],
                                '\[iODBC\]')],
             data_files=['mx/ODBC/iODBC/COPYRIGHT',
                         'mx/ODBC/iODBC/LICENSE',
                         'mx/ODBC/iODBC/README'],
             packages=['mx.ODBC.iODBC'],
             required=0
             ),

    Delete these lines.

  5. Do one of the following:
    • If you're using the unixODBC Driver Manager included in the Easysoft ODBC driver distribution:

      In:

      # unixODBC 2.2.x manager
mx_Extension('mx.ODBC.unixODBC.mxODBC',
             ['mx/ODBC/unixODBC/mxODBC.c',
              'mx/ODBC/unixODBC/mxSQLCodes.c'],
             include_dirs=['mx/ODBC/unixODBC'],
             define_macros=[('unixODBC', None),
                            ('WANT_UNICODE_SUPPORT', None)],
             needed_includes=[('sql.h',
                               ['/usr/local/unixODBC/include',
                                '/usr/local/unixodbc/include'],
                               'consistent with the Microsoft version')],
             needed_libraries=[('odbc',
                                ['/usr/local/unixODBC/lib',
                                '/usr/local/unixodbc/lib'],
                                '\[unixODBC\]')],

      Change all occurrences of /usr/local/unixODBC to /usr/local/easysoft/unixODBC.

      –Or–

    • If you're using another copy of unixODBC, make sure the needed_includes and needed_libraries entries shown above are correct for the directory where you installed unixODBC.
  6. Run python setup.py build --skip install (refer to the installation instructions on the mxODBC web page).
  7. Contact eGenix for an mxODBC evaluation license. To license mxODBC, follow the instructions provided with your license files.

Testing Easysoft ODBC drivers with Python and mxODBC

Create a data source in the unixODBC /etc/odbc.ini file and test it with unixODBC's isql command.

For example, the Easysoft SQL Server ODBC driver data source below connects to a remote SQL Server instance:

[mssql-python-mxodbc]
Driver                  = Easysoft ODBC-SQL Server
Server                  = my_machine\my_instance
User                    = my_domain\my_user
Password                = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database                = AdventureWorks

For information about adding a data source for a different Easysoft ODBC driver, refer to the documentation for your Easysoft ODBC driver.

Use isql to test the new data source. For example:

cd /usr/local/easysoft/unixODBC/bin
./isql -v mssql-python-mxodbc

This should connect successfully and you'll then get a prompt where you can type help to get a list of tables. Just press Return in an empty prompt line to exit.

Run python from the command line to get a python prompt. The example session below uses the sample Easysoft data source shown earlier. In the DriverConnect call, replace mssql-python-mxodbc with the name of your data source.

# python
Python 2.3.3 (#1, Jan 22 2004, 11:19:32)
[GCC 2.95.2 19991024 (release)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

>>> import mx.ODBC.unixODBC as mx
>>> db = mx.DriverConnect('DSN=mssql-python-mxodbc')
>>> c = db.cursor()
>>> c.tables()
-1
>>> res = c.fetchall()
>>> print res

This should print a list of tables in your database.