How to properly install pyodbc and drivers on M1 mac

Question

I have a new M1 pro macbook and I'm trying to install pyodbc and relevant drivers on my machine.

So far I have installed unixodbc via homebrew and ODBC drivers according to the instructions found here: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15

I then pip installed pyodbc but when I try to run pyodbc.connect() within a test script, python just aborts with the following message, regardless which connection string I use:

[1] 2086 abort python test.py

After investigating a bit more I find that pyodbc doesn't have any drivers. When I run pyodbc.drivers() I get an empty list. So it seems like pyodbc cannot find the drivers I installed earlier.

Here is the output when I run odbcinst -j:

unixODBC 2.3.9
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/kdot/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

and the odbcinst.ini file contains:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=3

I've also tried to explicitly give the driver file path to pyodbc in the connection string but then pyodbc.connect() just hangs instead of aborting right away.

I am running all of this within a virtual environment with the following versions:

python: 3.8.10
pyodbc: 4.0.32

Does anyone know how to make pyodbc see and use the relevant drivers?

Answer 1

First, install the ODBC driver as instructed by Microsoft here.

Further, Microsoft recommends creating symbolic links as follows:

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

However, the location of those files on M1 macs might be different, depending on the the default homebrew directory. In my case, the following paths worked:

sudo ln -s /opt/homebrew/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /opt/homebrew/etc/odbc.ini /etc/odbc.ini

Answer 2

The problem here is that "several critical CLI tools like nvm and brew do not have native versions built for the new M1 architecture." Source.

Luckily Apple Silicon comes with a translation layer named Rosetta 2, which is basically a CLI interface through which you have to install tools like brew in order for them to install correctly.

In this Medium post you can read how to use Rosetta 2. We got pyodbc working by installing Python 3.9, pip, wheel, openssl, unixodbc, freetds, the correct drivers and finally pyodbc (in this order) all with Rosetta 2.