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. As a convenience, Actual Technologies provides an installer that will install a pre-built pyodbc library for iODBC drivers in the site-packages folder within the /Library/Python folder:
Download pyodbc installer for Mac iODBC v5.1.0
The installed pyodbc module:
- is installed for all found installations of python
- requires Python 3.8 through 3.12
- supports Mac OS 13 "Ventura" or later (Python 3.8 support Mac OS 10.14 "Mojave" 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.