M1 Macbook: unixODBC + FreeTDS working, but pyodbc hangs

First, let me say thank you to the maintainers of this package. I appreciate all the support you've given to users like me.

Issue

On my M1 Macbook Pro, I've followed the steps carefully outlined in the helpful guide: Connecting to SQL Server from Mac OSX. I have unixODBC and FreeTDS successfully installed. I can use isql and sqlcmd to successfully connect to & query a SQL Server database on my network. Now, I'd like to use pyodbc to accomplish the same thing.

sqlcmd Working As Expected:

$ sqlcmd -S sqlserver.mylocal.network,1433 -E -Q 'SELECT @@VERSION'
Microsoft SQL Server 2014 (SP3-GDR) (KB4583463) - 12.0.6164.21 (X64)
    Nov  1 2020 04:25:14
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

isql Working As Expected:

$ isql MYMSSQL
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

pyodbc Not Working As Expected:

# test.py
import pyodbc
server = "sqlserver.mylocal.network"
port = "1433"
db = "mydb"
driver_str = "ODBC Driver 17 for SQL Server"
conn_str = (
    f"Driver={{{driver_str}}};"
    f"Server={server},{port};"
    f"Database={db};"
    "Trusted_Connection=yes;"
)
print(f"{conn_str=}")
conn = pyodbc.connect(conn_str)
print("Connected")
conn.close()

$ python3 test.py
conn_str='Driver={ODBC Driver 17 for SQL Server};Server=sqlserver.mylocal.network,1433;Database=mydb;Trusted_Connection=yes;'
# Hangs here. Never connects.

Using freetds.conf to create a DSN at least gives an immediate error message:

# test_with_freetds_dsn.py
import pyodbc
# the DSN value should be the name of the entry in odbc.ini, not freetds.conf
cnxn = pyodbc.connect("DSN=MYMSSQL;")
crsr = cnxn.cursor()
rows = crsr.execute("select @@VERSION").fetchall()
print(rows)
crsr.close()
cnxn.close()

$ python3 test_with_freetds_dsn.py
Traceback (most recent call last):
  File "/Users/edrogers/repos/pyodbc-test/test_with_freetds_dsn.py", line 6, in <module>
    cnxn = pyodbc.connect("DSN=MYMSSQL;")
pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unknown host machine name. (20013) (SQLDriverConnect)')

Things checked so far:

I've ensured that my python version, installed by pyenv, is an arm64 build, matching my device architecture
I've installed OpenSSL version 1.1.1o and ensured that it's pointed to by each of sqlcmd, isql, and my python version
I've built installed the pyodbc package from source in my virtual environment
I also tried editing my odbcinst.ini file to include:
```
[ODBC]
Trace=Yes
TraceFile=/dev/stdout
```

and I get verbose chatter during the connection process for sqlcmd and isql. None of that same chatter comes when using pyodbc though, which I find surprising.

Similarly, I can set the environment variable DYLD_PRINT_LIBRARIES=1 before calling either sqlcmd or isql to get an extensive list of libraries used by each. This environment variable doesn't generate any similar listing when using pyodbc.

Let me know if there are any debug flags or environment variables I can set to allow further investigation. The silent hang from pyodbc is quite puzzling.

Environment Details & Diagnostic Commands w/ Output:

Python

Version: 3.9.11 (installed with pyenv)

Architecture: arm64

$ python3 -c "import platform; print(platform.uname())"
uname_result(system='Darwin', node='Ed-Rogers.local', release='21.3.0', version='Darwin Kernel Version 21.3.0: Wed Jan  5 21:37:58 PST 2022; root:xnu-8019.80.24~20/RELEASE_ARM64_T6000', machine='arm64')
$ python3 -c "import platform; print(platform.architecture()[0])"
64bit

OpenSSL Version: 1.1.1o

$ python3 -c "import ssl; print(ssl.OPENSSL_VERSION)"
OpenSSL 1.1.1o  3 May 2022

pyodbc

Version: 4.0.32 (installed from source onto my M1 Macbook following these instructions)

OS:

MacOS Monterey Version 12.2.1

$ uname -a
Darwin Ed-Rogers.local 21.3.0 Darwin Kernel Version 21.3.0: Wed Jan  5 21:37:58 PST 2022; root:xnu-8019.80.24~20/RELEASE_ARM64_T6000 arm64

OpenSSL:

$ openssl version
OpenSSL 1.1.1o  3 May 2022

UnixODBC & FreeTDS Drivers:

unixODBC Version: 2.3.11
FreeTDS Version: 1.3.10

sqlcmd Version: 17.9.0001.1 Linux

$ 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/edrogers/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ DYLD_PRINT_LIBRARIES=1 isql MYMSSQL 2>&1 | grep odbc
dyld[79381]: <3C74CDB4-A94B-3BDD-864E-ED4AD16EDD52> /opt/homebrew/Cellar/unixodbc/2.3.11/bin/isql
dyld[79381]: <567F0218-30B5-32A0-8400-BCCAEB9E27C0> /opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbc.2.dylib
dyld[79381]: <37095933-050A-35DB-A64C-90C28D9281D0> /opt/homebrew/Cellar/freetds/1.3.10/lib/libtdsodbc.0.so
dyld[79381]: <DA536636-6C8E-3000-817D-CC0D45EB9CCB> /opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbcinst.2.dylib

$ sqlcmd "-?" | grep Version
Version 17.9.0001.1 Linux

Target DB:

Microsoft SQL Server 2014

Sorry for the gigantic write-up. I've been investigating this on my own for a while. Hopefully I've missed something obvious.

Do you see any ODBC tracing output at all when you attempt to connect with pyODBC? I.e. is it hanging at the SQLDriverConnect, or do you not even see it attempt to do the pre-connection setup such as allocating environment/connection handles, etc.?

Enabling tracing in my odbcinst.ini has no effect on pyODBC.

When I attempt to connect with pyODBC using a connection string that specifies Driver={ODBC Driver 17 for SQL Server}; etc, I get no tracing output at all. When I attempt to connect using a connection string that specifies DSN=MYMSSQL; (and freetds.conf & odbc.ini specify the server and driver), I get a Traceback that mentions SQLDriverConnect:

pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unknown host machine name. (20013) (SQLDriverConnect)')

Run otool -L on the pyODBC dylib to see if it is perhaps linked to the wrong DM (unixODBC) - you may have multiple DMs installed.

I'm a little puzzled by the output I got. But maybe it will make more sense to you:

Running otool -L on pyODBC's dylib:

$ otool -L $(python3 -c "import importlib.machinery; print(importlib.machinery.PathFinder().find_spec('pyodbc').origin)")
/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so:
    /usr/lib/libiodbc.2.dylib (compatibility version 4.0.0, current version 4.18.0)
    /usr/lib/libiodbcinst.2.dylib (compatibility version 4.0.0, current version 4.18.0)
    /opt/homebrew/opt/unixodbc/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
    /usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.23.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1311.100.3)

The first 2 dylibs in the listing appear to point to iODBC, strangely. This is odd because (1) to my knowledge, I haven't installed iODBC, and (2) /usr/lib/libiodbc.2.dylib and /usr/lib/libiodbcinst.2.dylib are both broken symlinks on my system. (Meanwhile, /usr/lib/libc++.1.dylib and /usr/lib/libSystem.B.dylib are not present on my system.)

I thought I might try to redirect the iODBC symlinks to /opt/homebrew/opt/unixodbc/lib/libodbc.2.dylib and /opt/homebrew/opt/unixodbc/lib/libodbcinst.2.dylib, but that was a non-starter -- Mac's don't even allow sudo to create links there as part of their System Integrity Protection 🤔

By contrast, here are the ODBC related dylib's in use by sqlcmd: Grepping sqlcmd's dylib Listing for ODBC Related dylibs:

$ DYLD_PRINT_LIBRARIES=1 sqlcmd -S sqlserver.mylocal.network,1433 -E -Q 'SELECT @@VERSION' 2>&1 | grep 'odbc'
dyld[97589]: <567F0218-30B5-32A0-8400-BCCAEB9E27C0> /opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbc.2.dylib
dyld[97589]: <D07E2297-81BC-39DB-96A3-B51F4941EBBA> /opt/homebrew/Cellar/msodbcsql17/17.9.1.1/lib/libmsodbcsql.17.dylib
dyld[97589]: <DA536636-6C8E-3000-817D-CC0D45EB9CCB> /opt/homebrew/Cellar/unixodbc/2.3.11/lib/libodbcinst.2.dylib

That certainly explains the problem, if not the solution: iODBC is somehow present in the build of pyODBC. Rebuild again and check the output carefully for any occurrences of -liodbc or similar.

The files which don't appear to exist may actually do. Starting in macOS 11, the majority of the system dylibs were moved into the "dyld shared cache", which isn't really a cache, but instead is one file containing all of the libs. Thus it is likely that iODBC is actually present and pyODBC is attempting to use it.

SIP definitely gets in the way. It's one of the first things to turn off as part of setting up a development machine...

Thanks for this guidance. Carefully inspecting the output when I build from source, I do see -liodbc and -liodbcinst sneaking in. Not sure how to point pyODBC to the correct dylibs, though.

Building from source with the recommended environment variables exported for Mac [1] [2]:

$ tar xzf pyodbc-4.0.32.tar.gz
$ cd pyodbc-4.0.32
$ export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib"
$ export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.11/include"
$ python3 setup.py build
running build
running build_ext
building 'pyodbc' extension
creating build
creating build/temp.macosx-12.2-arm64-3.9
creating build/temp.macosx-12.2-arm64-3.9/src
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/buffer.cpp -o build/temp.macosx-12.2-arm64-3.9/src/buffer.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/cnxninfo.cpp -o build/temp.macosx-12.2-arm64-3.9/src/cnxninfo.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/connection.cpp -o build/temp.macosx-12.2-arm64-3.9/src/connection.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/cursor.cpp -o build/temp.macosx-12.2-arm64-3.9/src/cursor.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/errors.cpp -o build/temp.macosx-12.2-arm64-3.9/src/errors.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/getdata.cpp -o build/temp.macosx-12.2-arm64-3.9/src/getdata.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/params.cpp -o build/temp.macosx-12.2-arm64-3.9/src/params.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/pyodbccompat.cpp -o build/temp.macosx-12.2-arm64-3.9/src/pyodbccompat.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/pyodbcdbg.cpp -o build/temp.macosx-12.2-arm64-3.9/src/pyodbcdbg.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/pyodbcmodule.cpp -o build/temp.macosx-12.2-arm64-3.9/src/pyodbcmodule.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/row.cpp -o build/temp.macosx-12.2-arm64-3.9/src/row.o -Wno-write-strings -Wno-deprecated-declarations
clang -Wno-unused-result -Wsign-compare -Wunreachable-code -DNDEBUG -g -fwrapv -O3 -Wall -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl -I/opt/homebrew/Cellar/unixodbc/2.3.11/include -DPYODBC_VERSION=4.0.32 -UMAC_OS_X_VERSION_10_7 -I/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/include -I/Users/edrogers/.pyenv/versions/3.9.11/include/python3.9 -c src/textenc.cpp -o build/temp.macosx-12.2-arm64-3.9/src/textenc.o -Wno-write-strings -Wno-deprecated-declarations
creating build/lib.macosx-12.2-arm64-3.9
clang++ -bundle -undefined dynamic_lookup -L/opt/homebrew/opt/readline/lib -L/opt/homebrew/opt/readline/lib -L/Users/edrogers/.pyenv/versions/3.9.11/lib -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -liodbc -liodbcinst -L/opt/homebrew/Cellar/openssl@1.1/1.1.1o/lib -L/opt/homebrew/lib -L/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/lib -L/opt/homebrew/opt/readline/lib -L/opt/homebrew/opt/readline/lib -L/Users/edrogers/.pyenv/versions/3.9.11/lib -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -liodbc -liodbcinst -L/opt/homebrew/Cellar/openssl@1.1/1.1.1o/lib -L/opt/homebrew/lib -L/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/lib -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -I/opt/homebrew/Cellar/unixodbc/2.3.11/include build/temp.macosx-12.2-arm64-3.9/src/buffer.o build/temp.macosx-12.2-arm64-3.9/src/cnxninfo.o build/temp.macosx-12.2-arm64-3.9/src/connection.o build/temp.macosx-12.2-arm64-3.9/src/cursor.o build/temp.macosx-12.2-arm64-3.9/src/errors.o build/temp.macosx-12.2-arm64-3.9/src/getdata.o build/temp.macosx-12.2-arm64-3.9/src/params.o build/temp.macosx-12.2-arm64-3.9/src/pyodbccompat.o build/temp.macosx-12.2-arm64-3.9/src/pyodbcdbg.o build/temp.macosx-12.2-arm64-3.9/src/pyodbcmodule.o build/temp.macosx-12.2-arm64-3.9/src/row.o build/temp.macosx-12.2-arm64-3.9/src/textenc.o -L/usr/local/lib -lodbc -o build/lib.macosx-12.2-arm64-3.9/pyodbc.cpython-39-darwin.so

It's the last line that includes the offending references to iODBC. To help in parsing it, I'll rebuild, grabbing only the last line, and inserting line-breaks and enumeration, for clarity:

$ rm -r build
$ python3 setup.py build | tail -1 | sed -E 's/(-L|-I)/\n\1/g' | sed -E 's/((-o )?build)/\n\1/g' | cat -n
     1  clang++ -bundle -undefined dynamic_lookup
     2  -L/opt/homebrew/opt/readline/lib
     3  -L/opt/homebrew/opt/readline/lib
     4  -L/Users/edrogers/.pyenv/versions/3.9.11/lib
     5  -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -liodbc -liodbcinst
     6  -L/opt/homebrew/Cellar/openssl@1.1/1.1.1o/lib
     7  -L/opt/homebrew/lib
     8  -L/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/lib
     9  -L/opt/homebrew/opt/readline/lib
    10  -L/opt/homebrew/opt/readline/lib
    11  -L/Users/edrogers/.pyenv/versions/3.9.11/lib
    12  -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -liodbc -liodbcinst
    13  -L/opt/homebrew/Cellar/openssl@1.1/1.1.1o/lib
    14  -L/opt/homebrew/lib
    15  -L/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/lib
    16  -L/opt/homebrew/Cellar/unixodbc/2.3.11/lib
    17  -I/opt/homebrew/Cellar/unixodbc/2.3.11/include
    18  build/temp.macosx-12.2-arm64-3.9/src/buffer.o
    19  build/temp.macosx-12.2-arm64-3.9/src/cnxninfo.o
    20  build/temp.macosx-12.2-arm64-3.9/src/connection.o
    21  build/temp.macosx-12.2-arm64-3.9/src/cursor.o
    22  build/temp.macosx-12.2-arm64-3.9/src/errors.o
    23  build/temp.macosx-12.2-arm64-3.9/src/getdata.o
    24  build/temp.macosx-12.2-arm64-3.9/src/params.o
    25  build/temp.macosx-12.2-arm64-3.9/src/pyodbccompat.o
    26  build/temp.macosx-12.2-arm64-3.9/src/pyodbcdbg.o
    27  build/temp.macosx-12.2-arm64-3.9/src/pyodbcmodule.o
    28  build/temp.macosx-12.2-arm64-3.9/src/row.o
    29  build/temp.macosx-12.2-arm64-3.9/src/textenc.o
    30  -L/usr/local/lib -lodbc
    31  -o build/lib.macosx-12.2-arm64-3.9/pyodbc.cpython-39-darwin.so

In the line-break delimited version above, I can see on lines 5 & 12 the problematic references to iODBC. Looking at how the setup.py constructs the ext_modules settings kwarg on the darwin platform, it's unclear to me how the iODBC references are sneaking in there.

FWIW, if I use odbc_config, (a CLI I didn't know about until reading the linux portion of the setup.py source code), I see output that looks more in-line with my expectations, as well as to what's in lines 16 & 17 above:

$ odbc_config --cflags
-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/opt/homebrew/Cellar/unixodbc/2.3.11/include
$ odbc_config --libs
-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -lodbc

Any suggestions on things to try next?

I've resolved this issue. @v-chojas, thank you so much for your guidance and help troubleshooting.

The problem was likely that I had problematic environment variables set before installing my python version with pyenv. (In particular, a Medium post I mentioned above, titled "Installing pyodbc and unixodbc for Apple Silicon" led me astray by recommending iODBC options be set for my environment variables.) I arrived at this diagnosis by searching for ODBC in my python build library directory.

Identifying unwanted iODBC flags in the python build:

$ find /Users/edrogers/.pyenv/versions/3.9.11/lib -type f -exec grep -FHni --binary-files=without-match 'odbc' {} \;

The output ended up showing the problematic -liodbc and -liodbcinst flags in my build.

So, the solution was to simply uninstall my python version with pyenv, set my preferred environment variables, and reinstall my python with pyenv.

Rebuild python without iODBC flags in the environment:

$ pyenv uninstall 3.9.11
$ export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib -L/opt/homebrew/Cellar/openssl@1.1/1.1.1o/lib"
$ export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.11/include -I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include"
$ export CFLAGS="-I/opt/homebrew/Cellar/openssl@1.1/1.1.1o/include/openssl"
$ pyenv install 3.9.11

Re-running the find & grep combo above showed that I no longer had references to -liodbc or -liodbcinst in my python build. Next, creating a virtualenv and building pyODBC from source (using python3 setup.py build and python3 setup.py install) gave me a package with no references to iODBC.

Verify that pyODBC is now linked solely to unixODBC as its DM:

otool -L $(python3 -c "import importlib.machinery; print(importlib.machinery.PathFinder().find_spec('pyodbc').origin)")
/Users/edrogers/.pyenv/versions/3.9.11/envs/pyodbc-test/lib/python3.9/site-packages/pyodbc-4.0.32-py3.9-macosx-12.2-arm64.egg/pyodbc.cpython-39-darwin.so:
    /opt/homebrew/opt/unixodbc/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
    /usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.23.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1311.100.3)

Now that IODBC is not referenced by pyODBC, everything works as expected.

Thank you again!

mkleehammer / pyodbc