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://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15https://learn.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

For MAC M1 chip

pip install --no-binary :all: pyodbc

Reference: https://github.com/mkleehammer/pyodbc/issues/1124