Introduction
Python is a widely used general-purpose, high-level programming language. Its design philosophy emphasizes code readability, and its syntax allows programmers to express concepts in fewer lines of code than would be possible in other languages.
Python is included with Mac OS. Python can also be installed with the Homebrew package manager. There are several modules available for connecting to a SQL database using ODBC.
The first option is to use the commercially-supported mxODBC module from eGenix. mxODBC provides an easy to use, high-performance, reliable and robust Python interface to most popular ODBC compatible databases.
A second option is to use the open source pyodbc module. pyodbc is a standalone library makes it easy to connect to databases using ODBC. Unfortunately the version of pyodbc installed by Homebrew is not built using the iODBC standard used by most applications and drivers on Mac OS.
As a convenience, Actual Technologies provides a package repository for pyodbc that is built using the iODBC libraries. To install, execute the following on your command line:
pip3 uninstall pyodbc # to remove the incompatible version
pip3 install pyodbc --extra-index-url https://actualtech.com/python
The installed pyodbc module:
- is version 5.3.0
- requires Python 3.9 through 3.14
- supports Mac OS 15 "Sequoia" or later
- supports Apple Silicon and Intel processors
To use pyodbc with Actual Technologies ODBC drivers, you must set the character encoding options as follows:
import pyodbc
dbc = pyodbc.connect('DSN=my_dsn;UID=my_user_id;PWD=my_password;', encoding='UTF-32LE')
dbc.setencoding(encoding='UTF-8')
dbc.setdecoding(sqltype=pyodbc.SQL_WMETADATA, encoding='UTF-32LE', ctype=pyodbc.SQL_WCHAR)
For Actual Technologies ODBC drivers, specify the following Advanced Language options for your DSN:
[ ] Auto-detect language settings for application
[ X ] Application uses the "wide" ODBC API
[ X ] Treat text types as Unicode
Multi-byte text encoding: UTF-8
NOTE: if you build the pyodbc library from the source code, make sure it links with the iODBC libraries instead of the unixODBC libraries that are chosen by default. Actual Technologies drivers (and nearly all other drivers available for Mac OS) use the iODBC standard.