Cannot import pyodbc with latest version on Apple Silicon

[nleroy917]

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.10.6
• pyodbc: 4.0.35
• OS: macOS Ventura (13.0)
• DB: N/A
• driver: N/A

Issue

overview:
I upgraded my pyodbc version to the latest on PyPi (v4.0.35) and noticed I was getting a new error that I had never dealt with before. Specifically, when importing pyodbc I receive the following:

(0x0002): symbol not found in flat namespace '_SQLAllocHandle'

An issue for this was raised and closed a while ago; it references this exact problem: #885

expected behavior:
pyodbc should import without issue

steps to reproduce:

1. Create new virtual environment and activate:

python -m venv .venv
source .venv/bin/activate

2. Install latest pyodbc:

pip install pyodbc

3. Import pyodbc and observe error:

python
>>> import pyodbc
>>> (0x0002): symbol not found in flat namespace '_SQLAllocHandle'

[keitherskine]

Many thanks for the feedback, @nleroy917 . Just as an experiment, could I ask you to try setting the LDFLAGS and CPPFLAGS environment variables before installing pyodbc, per the wiki? Many thanks.

I appreciate pyodbc should "just install" but all feedback is useful.

[nleroy917]

It doesn't appear that worked 😕

Steps:

New venv:

python -m venv .venv
source .venv/bin/activate

Find version of unixodbc:

ls /opt/homebrew/Cellar/unixodbc/
2.3.11

Export flags:

export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib"        
export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.11/include"

Install, verify version:

pip install pyodbc
pip freeze
pyodbc==4.0.35

Test:

python
>>> import pyodbc
>>> (0x0002): symbol not found in flat namespace '_SQLAllocHandle'

[svintuss]

Hi @nleroy917! I've just had the same issue the other day. I managed to fix it with a --no-binary option which forces pip to compile the module from source instead of installing from precompiled wheel.

pip install --no-binary :all: pyodbc

[nleroy917]

Ahh. Is this specifiable in a requirements.txt file?

[svintuss]

I would also like to upvote the issue because ver. 4.0.34 works fine without compiling from source.
E. g. you may force pip to install specific version of pyodbc with:

pip install pyodbc==4.0.34

[keitherskine]

FYI, pyodbc 4.0.34 didn't include wheel files for ARM64 MacOSX, only regular macs.

https://pypi.org/project/pyodbc/4.0.34/#files
https://pypi.org/project/pyodbc/4.0.35/#files

Yes, on M1 macs, please use the --no-binary workaround for now.

[nleroy917]

Thanks for the help! I'll close this.

[keitherskine]

@nleroy917 This is a genuine issue so let's keep this open for the time being. Other people will then be able to see it. Thank you pointing out this issue and also for your comprehensive notes (these are always greatly appreciated!). We will investigate and figure out what's going on. Meanwhile, I will update the Wiki with the workaround.

[kupuguy]

I came here with the same problem but we're using poetry rather than pip. So in case anyone else needs it, the poetry workaround is to run:

poetry config installer.no-binary pyodbc

(or add the --local flag to do it for a specific project).

This configures poetry itself so won't stop other machines on the same project doing binary installs.

[wt-asw]

I came here with the same problem but we're using poetry rather than pip. So in case anyone else needs it, the poetry workaround is to run:

poetry config installer.no-binary pyodbc

(or add the --local flag to do it for a specific project).

This configures poetry itself so won't stop other machines on the same project doing binary installs.

Thank you for this. I came with the same issue and this fixed it.

[nick-gibb]

Note that in requirements.txt you can freeze the --no-binary option with something like, pyodbc --no-binary=pyodbc==4.0.35.

[lperumalla]

unable to install pip3 install --no-binary :all: pyodbc==4.0.34 getting following error

src/pyodbc.h:45:10: fatal error: 'Python.h' file not found
#include <Python.h>
^~~~~~~~~~
1 error generated.
error: command '/usr/bin/clang' failed with exit code 1

[falkben]

If the problem with the wheel is known, or wheel files can be compiled manually, would it be possible to re-issue a fixed wheel file for the m1 architecture with a higher build number/build-tag? This page discusses how one might do this: https://snarky.ca/what-to-do-when-you-botch-a-release-on-pypi/

[v-chojas]

Did you install python-dev or similar package that contains the headers (including Python.h)?

[JenaAshish]

Hi @nleroy917! I've just had the same issue the other day. I managed to fix it with a --no-binary option which forces pip to compile the module from source instead of installing from precompiled wheel.

pip install --no-binary :all: pyodbc

Hi I tried this method but it's not working.

Python: 3.10.5
pyodbc: 4.0.35
OS: macOS Monterey 12.2
chip apple- M1

>>> import pyodbc Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: dlopen({venv_path}env/lib/python3.10/site-packages/pyodbc.cpython-310-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

does any one have any other idea?

[v-chojas]

Do you have unixODBC (arm64 binary) installed?

[JenaAshish]

Yes I have.

[anibal2j]

I'm running on Apple Silicon as well and I'm unable to install pyodbc. I don't have a full development environment - not sure if that's needed to install from sources and compile. I have tried pip3 install pyodbc==4.0.34 and I get:

[martinwinter]

Yes I have.

The fix worked for me just now. One difference I can see is that I have Python 3.11 installed. Also, since you have msodbcsql17 installed, perhaps this old issue might be useful (could be completely irrelevant though): microsoft/homebrew-mssql-release#53

[Emmanuel-Tsavaris]

Hi I tried this method but it's not working.

Python: 3.10.5
pyodbc: 4.0.35
OS: macOS Monterey 12.2
chip apple- M1

import pyodbc Traceback (most recent call last): File "", line 1, in ImportError: dlopen({venv_path}env/lib/python3.10/site-packages/pyodbc.cpython-310-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

does any one have any other idea?

Would you be able to send a screenshot of what you get when running pip install --no-binary :all: pyodbc ?

I found that I had to uninstall my initial installation of pyodbc before running the above command.
I'll also mention that I'm using pip3 instead of pip.

[danitonio]

@Emmanuel-Tsavaris thanks a lot, pip install --no-binary :all: pyodbc worked fine for my MacOS M1 Max, python3.9.6.
previourly I got this error:
ImportError: dlopen(/opt/homebrew/lib/python3.10/site-packages/pyodbc.cpython-310-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

[anibal2j]

Still not working for me:

[sauce71]

Managed to use the binary build globally - Python 3.10.9 Homebrew (pip3 install --no-binary :all: pyodbc). Then when creating my virtual environment I used:
python -m venv venv --system-site-packages
This solved it for me. I could not use --no-binary in venv because it could not find Python.h

[lawe94]

@anibal2j do you have unixodbc installed (brew install unixodbc)?
If yes, try ```
export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/your-version/lib"
export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/your-version/include"
pip install --no-binary :all: pyodbc

[anibal2j]

It seems I didn't have unixodbc installed, so I installed it with your homebrew instructions.

Then I did pip install and that worked, but I'm using python3 so I ran pip3 install --no-binary :all: pyodbc and that fails:

So, my script is still throwing:

Anibals-New-MacBook-Air:FFI anibal$ python3 testmssql.py
Traceback (most recent call last):
File "/Users/anibal/Library/CloudStorage/[email protected]/.shortcut-targets-by-id/0B896VF0V6HQNd0Jkc2c5TXNaTlE/Consulting Services/VMG - Scripts/FFI/testmssql.py", line 1, in
import pyodbc
ModuleNotFoundError: No module named 'pyodbc'
Anibals-New-MacBook-Air:FFI anibal$

[anibal2j]

And some more info about what I have installed:

You'll see there are two versions of Python. This was a mess due to different ways of installing different packages that I need. All other packages (even psycopg2) are working fine with the current setup.

[keitherskine]

@anibal2j , a couple of things. Firstly, I'd definitely recommend using virtual environments when "pip installing", but I would also suggest not using Python either from homebrew or the built-in version on your Mac. You may want to consider using a utility like pyenv (see here) to install Python from scratch. That way, you will get a clean install of Python, effectively straight from python.org.

PYENV SETUP (optional)

If you want to install Python with pyenv, you can follow the instructions on the website, but to be honest they are a bit confusing so here is my summary:

brew update
brew install pyenv

In your ~/.bash_profile file, add the following:

export PYENV_ROOT="$HOME/.pyenv"
export PATH="$PYENV_ROOT/bin:$PATH"
eval "$(pyenv init --path)"

if command -v pyenv 1>/dev/null 2>&1; then
  eval "$(pyenv init -)"
fi

Then, in a NEW shell, you can install multiple versions of Python like so:

pyenv install --list  # to get the versions of Python available
pyenv install 3.8.12
pyenv install 3.10.7

(they get installed to directory ~/.pyenv/versions)

To set the Python versions you would like available at the command line (in order of priority), use "pyenv global":

pyenv global 3.10.7 3.8.12

After running the above command, the commands "python3.10", "python3", and "python" all point to the Python 3.10.7 installation, and "python3.8" points to Python 3.8.12 (you can't choose the incremental version).

VIRTUAL ENVIRONMENTS

Even if you don't want to use pyenv I would definitely recommend installing pyodbc into a virtual environment, for example:

# first check for unixODBC, which should already be installed, if not "brew install unixodbc"
odbcinst -j  # should return successfully with the unixODBC version, e.g. 2.3.11

# "cd" to the directory where you want to create this virtual environment
python3.10 -m venv my_pyodbc_venv  # or just "python" (the homebrew version) if you're not using pyenv
cd my_pyodbc_venv
source ./bin/activate
python -m pip install --upgrade pip
# on an M1 mac, you will probably have to uncomment these two lines (checking the unixodbc version in the paths):
# export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib"
# export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.11/include"
python -m pip install --force-reinstall --no-binary :all: pyodbc
python -c "import pyodbc; print(pyodbc.version)"

Try that and see if it works for you.

[anibal2j]

Excellent!! Thanks a lot! I hadn't put the time to learn about this venv and now that I see how it works, it's great. I'll start using it going forward. I'm sticking with 3.9.16 for now.

I followed all your commands and now I get this:

(my_pyodbc_venv) Anibals-New-MacBook-Air:my_pyodbc_venv anibal$ python -c "import pyodbc; print(pyodbc.version)" 4.0.35

So, it looks good. But my test program, which is to open a connection to an MS SQL server still fails with this:

(my_pyodbc_venv) Anibals-New-MacBook-Air:my_pyodbc_venv anibal$ python ~/PycharmProjects/VMG/FFI/testmssql.py
Traceback (most recent call last):
  File "/Users/anibal/PycharmProjects/VMG/FFI/testmssql.py", line 9, in <module>
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD=' + password)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (0) (SQLDriverConnect)")

The script has this:

import pyodbc

server = 'tcp:xx.xx.xx.xx'
database = 'mydb'
username = 'vmgzc\\vmg-ffi-admin'
password = 'password_here'

# ENCRYPT defaults to yes starting in ODBC Driver 18. It's good to always specify ENCRYPT=yes on the client side to avoid MITM attacks.
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD=' + password)

[mkleehammer]

Thanks. Can you run the following to help me troubleshoot this:

import platform
print(platform.processor())

[mkleehammer]

UPDATE: Never mind - I see the previous post has the same error and does have unixodbc installed.

@falkben Could your error indicate it is not finding the ODBC driver manager?

ImportError: 
dlopen(/Users/bfalk/miniconda3/envs/test/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so, 0x0002): 
symbol not found in flat namespace '_SQLAllocHandle'

This function is not a pyodbc function but rather an ODBC function that should be implemented by the driver manager. Are you able to run pyodbc when builing from source? Do you have unixodbc installed?

[mkleehammer]

It is somehow related to either (1) not finding unixodbc or (2) an architecture mismatch. Running the Python snippet I supplied above will tell us if Python is running under Rosetta.

Can you also run file $(which isql) and paste the output here?

Does anyone have any other recommended ways to tell? I'm going by this: https://apple.stackexchange.com/a/416044

[falkben]

>>> import platform
>>> print(platform.processor())
arm

(.venv) $ file $(which isql)
/opt/homebrew/bin/isql: Mach-O 64-bit executable arm64

i've tried in a conda environment (with unixodbc installed into the conda environment) and a virtualenv virtual environment with this new wheel and neither work.

When installing with:

pip install --no-binary :all: --no-cache-dir --force-reinstall pyodbc

it is able to import pyodbc without error

[mkleehammer]

@falkben OK, thanks. I'm going to have to borrow my wife's M1 mac and work on this.

[ebauch]

@anibal2j do you have unixodbc installed (brew install unixodbc)? If yes, try ``` export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/your-version/lib" export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/your-version/include" pip install --no-binary :all: pyodbc

Worked on Apple M1 Max

[Nonary]

By the way if anyone else is also having problems even with the workaround, make sure you uninstall the x86 unixodbc and Microsoft odbc installations if you happen to have a Rosetta installation of brew on the side. That was what caused my problem, once I removed those it worked.

[wilsonatmaddisonclarke]

Hi @nleroy917! I've just had the same issue the other day. I managed to fix it with a --no-binary option which forces pip to compile the module from source instead of installing from precompiled wheel.

pip install --no-binary :all: pyodbc

that's very advanced problem solving, I salut you sir.

[mkleehammer]

I thought we were just waiting for some Apple Silicon GitHub action runners for building, but now I seriously don't know if we can even have a good solution given that the environment on Mac varies so much due to Rosetta.

Does anyone feel like they are sure what the solution really is and could spell it out in detail? Do we pick the "best", like all AS, and somehow detect and raise an error if the env doesn't match?

[jordantshaw]

Macbook Pro M1.

I am able to successfully install and import pyodbc using conda by running. conda install pyodbc

I am able able to install using pip, but fails upon import. pip install pyodbc

If I run pip install --no-binary :all: pyodbc then I am able to successfully install and import.

Any ideas why it works fine with conda but not pip?

[iloveitaly]

This is what worked for me:

poetry install

# make sure this runs successfully
odbcinst -j

export LDFLAGS="-L$(brew --prefix unixodbc)/lib"
export CPPFLAGS="-I$(brew --prefix unixodbc)/include"

pip uninstall pyodbc
pip cache remove pyodbc

pip install --no-binary :all: pyodbc

[stealthco-tony]

Any chance we can resolve this internally so M1 users don't need a special command/flag? We need our code to work across OSX, unix, Windows servers.

Would prefer not to write conditional logic based on OS. Thanks!

[softgandhi]

Uninstall pyodbc and reinstall with:
pip3 install --no-cache-dir --no-binary :all: pyodbc
worked for me on Mac m3. Thanks!!

[cagrimunyas]

Uninstall pyodbc and reinstall with: pip3 install --no-cache-dir --no-binary :all: pyodbc worked for me on Mac m3. Thanks!!

Worked for me as well on macOS Sonoma with M1 Max

[igorvgalas]

hey. i work on mac m3 try all recomendation. i had an Error loading pyodbc module: dlopen(/Users/cheef/.local/share/virtualenvs/FAM-LuwvcngT/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'.

by the way for python 3.9 the same error.
Error loading pyodbc module: dlopen(/Users/cheef/.local/share/virtualenvs/FAM-e8HsoArV/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/xxxx/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

oh, and one more i use a pipenv.

Can some one help me with that please?)))

[mkleehammer]

This was a very long road. I hope we're at the end.

Version 5.1.0 has been released with both Intel and ARM binaries, so hopefully pip will do the right thing.

We'd appreciate any feedback from Mac users. I'll keep this open for a while until we find out how well they will work.

[jordantshaw]

This was a very long road. I hope we're at the end.

Version 5.1.0 has been released with both Intel and ARM binaries, so hopefully pip will do the right thing.

We'd appreciate any feedback from Mac users. I'll keep this open for a while until we find out how well they will work.

Just tested on Macbook M1, works as intended! Thank you for all the hard work that went into this. So glad its fixed!

❯ pip install pyodbc==5.1.0
Looking in indexes: https://pypi.org/simple
Collecting pyodbc==5.1.0
  Obtaining dependency information for pyodbc==5.1.0 from https://files.pythonhosted.org/packages/a5/2f/32e8845205e7fc31f67d8b01c8906b964bfbf640aa6306aba8e696eeef79/pyodbc-5.1.0-cp311-cp311-macosx_11_0_arm64.whl.metadata
  Using cached pyodbc-5.1.0-cp311-cp311-macosx_11_0_arm64.whl.metadata (2.7 kB)
Using cached pyodbc-5.1.0-cp311-cp311-macosx_11_0_arm64.whl (71 kB)
Installing collected packages: pyodbc
Successfully installed pyodbc-5.1.0

[notice] A new release of pip is available: 23.2.1 -> 24.0
[notice] To update, run: python3.11 -m pip install --upgrade pip

❯ python -c 'import pyodbc; print(pyodbc)'
<module 'pyodbc' from '/Users/jshaw/.pyenv/versions/datapipeline/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so'>

[airdrik]

Tested on a Macbook M2 (pip install --upgrade pyodbc) with similar results as jordantshaw.
Nice work, thank you!

[igorvgalas]

on Mac M3 ok too. Thanks a lot.

[13brindarao]

on Mac M3 ok too. Thanks a lot.
@igorvgalas
I have Mac M3 too and facing the same issue. Could you please write down the steps you followed. Many thanks.

[igorvgalas]

on Mac M3 ok too. Thanks a lot.
@igorvgalas
I have Mac M3 too and facing the same issue. Could you please write down the steps you followed. Many thanks.

Hey. Are you made a copy of the system to your Mac m3 from your pervious Mac? Why i asking it, because I do and it copied also a homebrew. My previous Mac was on intel processor. That’s a problem. You need to uninstall howebrew, then install all from scratch.

[13brindarao]

@igorvgalas No, I did not copy any settings from my old Mac. This is a new laptop setup. I freshly installed homebrew, yet i am facing the same issue.

[igorvgalas]

@igorvgalas No, I did not copy any settings from my old Mac. This is a new laptop setup. I freshly installed homebrew, yet i am facing the same issue.

What error message do you get?

[13brindarao]

@igorvgalas

The steps followed:

Step1:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

Step2:
brew install unixodbc

Step3:
export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/your-version/lib"
export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/your-version/include"

Step4:
python -m .venv venv
pip install --no-binary :all: pyodbc
pip install aioodbc

output of odbcinst -j
unixODBC 2.3.12
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/brindarao/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

ImportError: dlopen(/Users/brindarao/.venv/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

[igorvgalas]

@igorvgalas

The steps followed:

Step1: brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

Step2: brew install unixodbc

Step3: export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/your-version/lib" export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/your-version/include"

Step4: python -m .venv venv pip install --no-binary :all: pyodbc pip install aioodbc

output of odbcinst -j unixODBC 2.3.12 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/brindarao/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8

ImportError: dlopen(/Users/brindarao/.venv/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

Try to install windows driver 17.

[anguspmitchell]

I followed some variation of recommended steps 9 different ways. Couldn't figure out what was going on. I consistently got the 0x0002): symbol not found in flat namespace '_SQLAllocHandle' error

Steps I kept following

brew install unixodbc

# make sure this runs successfully
odbcinst -j

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

# check out installed drivers
cat /opt/homebrew/etc/odbcinst.ini

export LDFLAGS="-L$(brew --prefix unixodbc)/lib"
export CPPFLAGS="-I$(brew --prefix unixodbc)/include"

pip uninstall pyodbc
pip cache remove pyodbc

pip install --no-binary :all: pyodbc

x86 Python vs ARM Python

Finally I noticed that wheel in my pip install output was pyodbc-5.1.0-cp38-cp38-macosx_10_9_x86_64.whl, emphasis on the x86_64 part. I thought this was odd, since I was working in an ARM terminal.

What I did not realize is that there are ARM versions of Python and x86 versions of python, and x86 versions can work from ARM terminals.

I confirmed this by running

python -c "import platform; print(platform.processor())"
> i386

So the problem was that my Homebrew was in ARM world, and my Python was in x86 world. I assume that I could have fixed the problem by installing an ARM version of Python. However, I simply installed an x86 version of unixodbc and the MSFT drivers through an x86 version of Homebrew.

[v-chojas]

Yes, you need to have either consistently arm64 or amd64 software. Python needs to match pyODBC which then needs to match unixODBC and the ODBC driver(s) you need to use.