pyodbc issue: ODBC driver not found on MacOS

Question

My environment

• Python: 3.9.7
• pyodbc: 4.0.32
• OS: MacOS 12.0.1, Apple M1 Max
• DB: Azure SQL
• driver: ODBC Driver 17 for SQL Server

Running

import pyodbc

server   = 'myserver.database.windows.net'
database = 'mydb'
username = 'myuser'
password = 'mypassword'
odbc_driver = '{ODBC Driver 18 for SQL Server}'

conn_str = (
    f"Driver={odbc_driver};"
    f"Server=tcp:{server},1433;"
    f"Database={database};"
    f"Uid={username};"
    f"Pwd={password};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;")

print(conn_str)

cnxn = pyodbc.connect(conn_str)

outputs

Driver={ODBC Driver 18 for SQL Server};Server=tcp:myserver.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
Traceback (most recent call last):
  File "/Users/....", line 21, in <module>
    cnxn = pyodbc.connect(conn_str)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (0) (SQLDriverConnect)")

Using the same connection string in isql:

isql -v -k "Driver={ODBC Driver 18 for SQL Server};Server=tcp:<server>,1433;Database=<db>;Uid=<user>;Pwd=<pw>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

returns Connected and queries are possible

Drivers are on my machine, I tried first with ODBC Driver 17 for SQL Server, then installed ODBC Driver 18 for SQL Server with same result.

How can I fix this? Created a GitHub issue as well here

Answer 1

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

According to the Above Error drivers are properly not installed .However, for ODBC Driver 17 for SQL server ,Please follow the official document Installing the Microsoft ODBC Driver for SQL Server on MacOS.

Another way to Solve an Error, check whether your driver is installed or not then, follow the driver path Example : "/user/local/lib/libmsodbcsql.17.dylib" , Replace Driver value as shown in below code.

cnxn = pyodbc.connect('DRIVER={/user/local/lib/libmsodbcsql.17.dylib};SERVER=ServerName,1433;DATABASE=DatabaseName;UID=Username;PWD=password')

Reference:

https://docs.snowflake.com/en/user-guide/odbc-mac.html#installing-and-configuring-the-odbc-driver-for-macos