Changing location of libodbc.2.dylib on macOS

[paw-lu]

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.7.6
• pyodbc: 4.0.28
• OS: macOS 10.14.4
• driver: freetds

Issue

When importing pyodbc

❯ python
>>> import pyodbc
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: dlopen(/Users/pcosta/Documents/test/myenv/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
  Referenced from: /Users/pcosta/Documents/test/myenv/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so
  Reason: image not found

I know why this is happening, as I don't have libodbc.2.dylib in the expected location. The reason is I do not have permission to write to /usr/local/, so I have Homebrew installing into ~/.brew. This mostly works fine. I am even able to get both tsql and isql working as expected by following the steps outlined here: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX.

So I do have libodbc.2.dylib, it's just that it lives in /Users/pcosta/.brew/opt/unixodbc/lib, not /usr/local/opt/unixodbc/lib.

The main questions is can I get pyodbc to look for libodbc.2.dylib (and other associated files) in another directory?

I have all the files needed and have configured them correctly, I just need to repoint pyodbc somehow.

Thanks!

[v-makouz]

I would recommend using the latest unixODBC rather then iodbc, there is more discussion here: #444

[paw-lu]

To the best of my knowledge, I am using unixODBC.

[gordthompson]

FWIW, the path appears to be hard-coded into the .so file:

[paw-lu]

Ah good find! Not sure if I can work around it then.

[gordthompson]

If the pre-compiled wheel file won't work for you then you could try installing from source ...

pip install --no-binary pyodbc pyodbc

... but you'd need to have a C++ compiler and the unixODBC development (header) files on your machine for that to work.

[v-makouz]

I looked into it a bit more and it looks like if you ldd that .so file you can see /usr/local/lib/libodbc.2.dylib, I tried using install_name_tool to change the dependency, but after that I started getting "ImportError: No module named pyodbc".

When I have time I want to investigate it further, but the .so file seems to be created if you run python setup.py install if you want to build it yourself from source

[paw-lu]

Want to thank both @gordthompson and @v-makouz for their response, it's been super appreciated.

I didn't bring it up initially, but I have tried to build from source and that also seems to run into issues.

~
❯ pip install --no-binary pyodbc pyodbc
...
    In file included from src/buffer.cpp:12:
    src/pyodbc.h:56:10: fatal error: 'sql.h' file not found
    #include <sql.h>
             ^~~~~~~
    1 error generated.
    error: command 'clang' failed with exit status 1
    ----------------------------------------
...

Which mirrors some of the issues shown in #101

Like before, I know sql.h exists...

~
❯ cd ~/homebrew

~/homebrew stable
❯ find . -name 'sql.h'
./include/sql.h
./Cellar/unixodbc/2.3.7/include/sql.h

So again when PyODBC looks for sql.h on this line it misses the homebrew file.

P.S. I changed the directory from ~/brew to ~/homebrew because I saw these lines in setup.py. So I thought if I renamed ~/.brew/include to ~/homebrew/include it would be able to locate sql.h. It did not work.

[paw-lu]

Want to update the issue with my solution.

The clang issues can be solved by adding unixODBC's path to these environmental variables:

export LDFLAGS="-L/Users/pcosta/homebrew/opt/unixodbc/lib $LDFLAGS"
export CPPFLAGS="-I/Users/pcosta/homebrew/opt/unixodbc/include $CPPFLAGS"
export PKG_CONFIG_PATH="/Users/pcosta/homebrew/opt/unixodbc/lib/pkgconfig $PKG_CONFIG_PATH"

This makes it possible to run pip install --no-binary pyodbc pyodbc with an atypical installation directory.

It would be nice if users would be able to specify locations of drivers to avoid the build process, but I realize this is a specific edge case.

[Thomasillo]

I don't think this is a specific edge case. It holds for all mac users who use homebrew or macports (like me).
So I would vote for reopening this issue.

[paw-lu]

@Thomasillo Note that this seems to only apply to the case where you have an atypical homebrew installation.

In my case, I had installed homebrew into ~/homebrew. I believe (have not tested) that the installation should work without issue if you install homebrew as recommended (/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh).

If you think this is not the case, I'd be happy to reopen if you're having issues.

[Thomasillo]

It applies also to the typical mac ports install directory. /opt/local
I was having this issue but fixed it with your directions. :) I just thought after stumbling over quite some other posts with this, that it should be treated in a more general way.

[paw-lu]

Gotcha @Thomasillo.

Glad my instructions helped you out!

I have never used MacPorts—so thanks for the clarification. That sounds fair. I'll reopen the issue.

The official ask then is to allow for more flexibility for the location of the necessary unixODBC files when installing pyodbc as a wheel.

[fonnesbeck]

Yes, I spent an afternoon spinning my wheels on this one. If you install your unixODBC and FreeTDS via MacPorts, you are just. out of luck--pyodbc will not see them. It would be nice to have an package installer-agnostic way of making all this work.

[v-chojas]

Due to how the Mac dynamic linking system works, there will be hardcoded absolute paths in the binaries. It's not really possible to work around that without adding own dynamic searching and loading logic, which can be quite complex.

[AntonM030481]

@v-chojas
Previous versions of pylib for Darwin were looking for libodbc.2.dylib at Python lib folder
Now it is looking for it in /usr/local/lib
Is it possible to search in both locations? Or it is also too complex?
If so, maybe searching in Python lib folder (old behaviour) is better,
since symlinks can be used for flexibility.

[Himanshup21234]

Facing same issue, the file libodbc.2.dylib is not available after unixodbc installation, Why M3 Silicon chip is behaving weird?
Anybody solved this issue?

[caldempsey]

I solved this by adding: ODBCInstLib=libiodbcinst.2.dylib to the ini config of my driver. In the case of Simba /Library/simba/spark/lib/simba.sparkodbc.ini should read:

[Driver]
ErrorMessagesPath=/Library/simba/spark/ErrorMessages/
LogLevel=0
LogPath=
SwapFilePath=/tmp
ODBCInstLib=libiodbcinst.2.dylib

To be up to date with the latest version of unixodbc https://packages.debian.org/sid/libodbcinst2

[caldempsey]

cc: @Himanshup21234