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

user_599989 0
Viewed 6757

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.

pyodbc odbc sqlalchemy sql-server python
1 Answers

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.

user_20919250
Related