Can't open lib 'ODBC Driver 17 for SQL Server' in python

Question

I'm using a python script to connect to a remote MSSql server. I installed the required libraries in my OSX m/c and tried connecting using python shell but the operation fails. MSSql is not installed locally in my dev machine.

Python shell:
>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
>>>

Content of odbcinst.ini

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib

File reference of driver, it's present.

lrwxr-xr-x    1 biju  admin      56 Mar 23 07:57 libmsodbcsql.17.dylib -> ../Cellar/msodbcsql17/17.5.2.1/lib/libmsodbcsql.17.dylib

Using the file path directly in driver, the code works.

Found one more odbcinst file at /etc/odbcinst.ini

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=1

No file present in the path mentioned above /usr/local/lib/libmsodbcsql.13.dylib. I'm not sure if these two versions of library are installed properly or has to be removed.

You could try brew reinstall msodbcsql17 if you think your msodbcsql17 installation is broken. Also, when you installed msodbcsql13, did you notice the info notice that said you needed to run odbcinst -u -d -n "ODBC Driver 13 for SQL Server" before uninstalling it? — AlwaysLearning

Will Keeling Will Keeling · Accepted Answer · 2020-10-16T11:19:25

I was having the same issue and I fixed it by setting the ODBCSYSINI environment variable to point to the directory containing the odbcinst.ini.

export ODBCSYSINI=/opt/odbc

Note that the variable points to the directory containing the file not the file itself. The full path to my odbcinst.ini is /opt/odbc/odbcinst.ini

Ensure that the variable is set in the script or shell that calls the Python code.

Can't open lib 'ODBC Driver 17 for SQL Server' in python

1 Answers