pyodbc: Cannot import `pyodbc` with latest version on Apple Silicon

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

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

Ok this works for me, Mac M2, Ventura.

1. brew install unixodbc
2. pip uninstall pyodbc
3. pip install --no-binary :all: pyodbc
4. install drivers for 18 (copy and paste bash code), https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15
5. run python code and get connected

  import pyodbc 
  server = 'xxxxx.database.windows.net' 
  database = 'xxx' 
  username = 'xxx@xx.x.com' 
  password = 'xxxxx' 
  cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password )
  cursor = cnxn.cursor()

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:

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.

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.

@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.

Still not working for me:

@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

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'

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.

Ok this works for me, Mac M2, Ventura.

1. brew install unixodbc
2. pip uninstall pyodbc
3. pip install --no-binary :all: pyodbc
4. install drivers for 18 (copy and paste bash code), https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15
5. run python code and get connected

  import pyodbc 
  server = 'xxxxx.database.windows.net' 
  database = 'xxx' 
  username = 'xxx@xx.x.com' 
  password = 'xxxxx' 
  cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password )
  cursor = cnxn.cursor()

THX !!!

@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'

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

@anibal2j On an Intel x64 Mac, you should be able to “pip install” pyodbc in the usual manner (from the available wheel on PyPi), so instead of: python -m pip install --force-reinstall --no-binary :all: pyodbc You should be able to just use: python -m pip install pyodbc

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?

@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.

This comment here https://github.com/microsoft/homebrew-mssql-release/issues/53#issuecomment-922208476 helped me a lot.

I have written up some instructions to get pyodbc to work on the M1 with Microsoft ODBC drivers as well:

One can only use pyodbc when using Rosetta (x86 emulation). Follow the steps below.

Uninstall M1 versions of brew packages (if installed at all):

brew uninstall unixodbc msodbcsql17 mssql-tools freetds

Install x86 Homebrew alongside the ARM M1 homebrew:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Then use x86 homebrew like arch -x86_64 /usr/local/bin/brew install or use the following alias (add to ~/.bash_profile)

# Relies on having installed x86 brew like:
# arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
alias x86brew="arch -x86_64 /usr/local/bin/brew"
alias brew="/opt/homebrew/bin/brew"  # M1 version, to avoid from using x86 version accidentally

Install the ODBC packages.

x86brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
x86brew update
HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

Create an x86 conda env with:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 conda create -n $ENV_NAME python
conda activate $ENV_NAME
conda config --env --set subdir osx-64

or if using micromamba:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 micromamba create -n $ENV_NAME python
micromamba activate $ENV_NAME

Test with

python -c "import pyodbc"

on Mac M3 ok too. Thanks a lot.

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

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?

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

>>> 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

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

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'>

I have added Apple Silicon wheels to 5.0.0b2. Can someone please try installing them with just pip install pyodbc==5.0.0b2? Thanks.

In the past Github actions, which we use to build the wheels, did not support macOS ARM very well. Github is not finished, but it looks like there might be enough support now.

I just tried this on my Apple M2 Pro, Ventura, python 3.9.16 (installed using asdf-vm):

$ python -m venv pyodbc_beta
$ source pyodbc_beta/bin/activate
$ pip install pyodbc==5.0.0b2
$ python
>>> import pyodbc
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: dlopen(pyodbc_beta/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'
$ echo $LDFLAGS
-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib
$ echo $CPPFLAGS
-L/opt/homebrew/Cellar/unixodbc/2.3.11/include
$ ls /opt/homebrew/Cellar/unixodbc/2.3.11
AUTHORS			ChangeLog		NEWS			bin			lib
COPYING			INSTALL_RECEIPT.json	README			include			share

edit: installing using pip install --no-binary :all: pyodbc==5.0.0b2 seems to work just fine.

I have added Apple Silicon wheels to 5.0.0b2. Can someone please try installing them with just pip install pyodbc==5.0.0b2? Thanks.

In the past Github actions, which we use to build the wheels, did not support macOS ARM very well. Github is not finished, but it looks like there might be enough support now.

Having issues with --no-binary :all: pyodbc I get errors to do with setup.py and so I tried using --use-pep517 but still get symbol not found in flat namespace '_SQLAllocHandle'. Please help, this issue is destroying me

Change ENCRYPT=yes to ENCRYPT=no and see what happens. Not sure if in your environment this is OK or not, but depending on your situation it might be OK.

Excellent! that did it. All I have to do now is fix credentials - I’m reaching out to the vendor who installed the MS SQL server for credentials and I should be good to go. Thanks a lot for your help!

@anibal2j To talk to SQL Server with Python, you will need:

• pyodbc (now installed)
• a driver manager (unixODBC, already installed)
• a SQL Server ODBC driver (apparently not yet installed)
• access to a SQL Server database instance (to be determined)

Your code is using the SQL Server driver called “ODBC Driver 18 for SQL Server”, but it looks like that driver is not yet installed. Check this by looking in your odbcinst.ini file (probably at location /usr/local/etc/odbcinst.ini but check with odbcinst -j). There must be a section that starts with “[ODBC Driver 18 for SQL Server]”. If that doesn’t exist, install the driver using the instructions here, i.e.:

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

(the first line in the Microsoft instructions simply installs homebrew)

After installation, check your odbcinst.ini file again. If that is OK now, try running your python code once more.

Do you have unixODBC (arm64 binary) installed?

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.