pyodbc: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/lib/libmsodbcsql.18.dylib' : file not found (0) (SQLDriverConnect)")

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.9.7
• pyodbc: 4.0.34
• OS: Mac (M1)
• DB:
• driver: ODBC

Issue

I have Mac M1, trying to connect to Azure SQL server using pyodbc. my settings works well on my previous windows base laptop, but not here. This is the error I get when trying to connect to sql server using pyodbc

Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘/opt/homebrew/lib/libmsodbcsql.18.dylib’ : file not found (0) (SQLDriverConnect)”) but file exists.

I checked lipo -info /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib

returns: Non-fat file: /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib is architecture: arm64

libmsodbcsql.18.dylib exists in /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib, but there is a shortcut in /opt/homebrew/lib/libmsodbcsql.18.dylib. I also changed Driver to the above address, still same result.

This was my process: I have installed ODBC V18 and brew using Microsoft instruction. when I run this on terminal works fine:

odbcinst -j
unixODBC 2.3.11 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/user_name/.odbc.ini SQLULEN Size…: 8 SQLLEN Size…: 8 SQLSETPOSIROW Size.: 8 (base) User_name@TMDLXNPPVJ4J ~ % cat /opt/homebrew/etc/odbcinst.ini [ODBC Driver 18 for SQL Server] Description=Microsoft ODBC Driver 18 for SQL Server Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib UsageCount=1

pyodbc.drivers() returns [ODBC Driver 18 for SQL Server]

I have also did these:

brew install unixodbc export LDFLAGS=“-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib” export CPPFLAGS=“-I/opt/homebrew/Cellar/unixodbc/2.3.11/include” pip install pyodbc

isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx" works by the way

Please help. thanks