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, 2014, 2016, 2017 or 2019 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
- Tested Versions
- Building mxODBC with unixODBC Support
- Testing Easysoft ODBC Drivers with Python and mxODBC
- Appendix A: Resources
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:
- mxODBC-1.0.1 and mxODBC-pre1.0.2 with Python-1.5.1/1.5.2.
- mxODBC 2+ and Python 2.3.3
- mxODBC 3.0.0 and Python 2.5.1
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.
- Download egenix-mx-base-2.0.6.tar.gz, unpack and cd into the created directory.
- Read the README file, which says to run:
python setup.py install
- Download egenix-mx-commercial-2.0.7.tar.gz, unpack and cd into the created directory.
- 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.
- 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
andlibrary_dirs
entries shown above are correct for the directory where you installed unixODBC.
- If you are using the unixODBC Driver Manager included in the Easysoft ODBC driver distribution:
- 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.
- 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.
- Read the installation instructions on the mx Base Distribution web page, which say to run:
python setup.py build --skip install
- Download the mxODBC distribution for your platform, unpack and cd into the created directory.
- 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.
- 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
andneeded_libraries
entries shown above are correct for the directory where you installed unixODBC.
- If you are using the unixODBC Driver Manager included in the Easysoft ODBC driver distribution:
- Run
python setup.py build --skip install
(see the installation instructions on the mxODBC web page). - 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
- ODBC examples written in Python.
- Easysoft ODBC drivers:
- Oracle® ODBC driver
- SQL Server ODBC driver
- Salesforce.com ODBC driver
- MongoDB ODBC driver
- DB2 ODBC driver
- Access ODBC driver
- Sybase ODBC driver
- InterBase ODBC driver
- DB2 ODBC driver
- Derby ODBC driver
- Firebird ODBC driver
- C/D-ISAM ODBC driver
- LINC Developer ODBC driver
- System Z ODBC driver
- ODBC-JDBC Gateway -- providing ODBC access to JDBC data sources
- ODBC-ODBC Bridge -- providing ODBC access to any remote ODBC data source e.g. MS Access from Linux