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?

Poland

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

Liken

The first set of commands worked on my mac - (MacOS Monterey 12.6.1) – Accra

For thos who prefer the link in English: learn.microsoft.com/en-au/sql/connect/odbc/linux-mac/… – Lacewing

Answer 2

For MAC M1 chip

pip install --no-binary :all: pyodbc

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

Pile

The only that worked for me was this. Uninstalled pyodbc from local environment and reinstalled with this command. – Pilatus

Answer 3

This is what worked for me on a m2 macbook (from the official ms documentation).
Make sure you have homebrew installed.

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

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

Drumstick

Answer 4

Found the following link helpful to install pyodbc on MacBook M1 Chip.

We happen to get sql.h file missing error and if we try to install the wheel directly from the site it throws platform not supported error.

Follow the below document and then add the export commands into your zrc or bash file and you should be good to go.

https://whodeenie.medium.com/installing-pyodbc-and-unixodbc-for-apple-silicon-8e238ed7f216

Need more reading about the bug and how people tried to learn it.

https://github.com/mkleehammer/pyodbc/issues/846

Reinforce

Answer 5

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.

Lully

I have tried symlinking to both /etc/odbcinst.ini and /usr/local/etc/odbcinst.ini but neither seems to work – Poland

In my original post I suggested creating these symlinks. Eventually we got to the root of the problem: brew installs packages in the wrong place due to the fact that there is no native M1 version of brew. – Lully

@Lully please can you elaborate on the solution, I am running Mac M1 Pro with the exact same issue. – Triquetrous

@Lully can you post your solution? – Tiu

The solution is to install brew with the Rosetta 2 terminal. See also the sources that I quote. Not sure how to elaborate further. What would help you? – Lully

So the truck is to install using Rosetta terminal? I can try that. I’m also trying to connect to Azure Sql Edge in a dicker container. Fun x! – Tiu

Answer 6

I still had this issue with the default Homebrew & ODBC installations on macOS Ventura with a 2022 MacBook Pro.

The only thing that worked was to completely uninstall Homebrew and the ODBC drivers and then reinstall both with the prefix arch -x86_64.

$ arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

$ arch -x86_64 brew install msodbcsql18 mssql-tools18

Birgit