Connecting to ODBC Databases from Python with mxODBC

This mxODBC tutorial shows how to connect Python with ODBC databases such as MS SQL Server, Oracle, Salesforce.com, DB2, Sybase ASE, InterBase, MS Access and Derby.

For example, connect Python on Linux to SQL Server 7.0, 2000, 2005, 2008, 2012 or 2014, 2016 databases. Do this by using Python and mxODBC with the SQL Server ODBC driver.

Use mxODBC with our Oracle, Salesforce.com, MongoDB ODBC driver, DB2, Sybase, InterBase and Derby, drivers to access those databases.

To access other databases for which an ODBC driver is available, for example MS Access, use the ODBC-ODBC Bridge.

Contents

Introduction

mxODBC is available at http://www.egenix.com/products/python/mxODBC/. From the mxODBC web page:

"The eGenix.com mxODBC Distribution contains the mxODBC Python extension package, a Python DB-API 2.0 compatible interface to ODBC compliant databases."

Tested Versions

Easysoft ODBC drivers have been tested with:

Building mxODBC with unixODBC Support

These instructions are for 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 (OOB). This is a significant help when building mxODBC against OOB. 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 OOB 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 look 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'.

    Look 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 are using the unixODBC Driver Manager included in the 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".

    • If you are 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 (see mxODBC’s README).

Building mxODBC 3+ with unixODBC Support

Here is 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 look 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 are 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 MS 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".

    • If you are 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 (see 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 ODBC-SQL Server 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, see 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 will 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.

Appendix A: Resources