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

           ^~~~~~~~~~
  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): https://github.com/microsoft/homebrew-mssql-release/issues/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/GoogleDrive-anibal.consultant@gmail.com/.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)

[keitherskine]

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

[keitherskine]

@anibal2j I copied the wrong Microsoft instructions, since corrected above. Install the "18" toolset, not "17".

[anibal2j]

First of all, thanks for helping me out with this.

I got the ODBC drivers installed and the message at the end says:

If you installed this formula with the registration option (default), you'll
need to manually remove [ODBC Driver 17 for SQL Server] section from
odbcinst.ini after the formula is uninstalled. This can be done by executing
the following command:
    odbcinst -u -d -n "ODBC Driver 17 for SQL Server"

Not sure what that means. However, in /opt/homebrew/etc/odbcinst.ini I see:

(my_pyodbc_venv) Anibals-New-MacBook-Air:my_pyodbc_venv anibal$ \cat /opt/homebrew/etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=1

So, I modified my code to use 17 rather than 18, like this:

(my_pyodbc_venv) Anibals-New-MacBook-Air:my_pyodbc_venv anibal$ cat ~/PycharmProjects/VMG/FFI/testmssql.py
import pyodbc

server = 'tcp:xxx.xxx.xxx.xxx'
database = 'mydb'
username = 'vmgzc\\vmg-ffi-admin'
password = 'pw_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 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD=' + password)

And I'm still getting 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 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD=' + password)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate] (-1) (SQLDriverConnect)')

Oh, I see... the issue is now with SSL and not the driver. I changed the ENCRYPT=yes to ENCRYPT=no and now I'm getting another issue with login credentials:

(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 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=no;UID='+username+';PWD=' + password)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'vmgzc\\vmg-ffi-admin'. (18456) (SQLDriverConnect)")

I'm trying to uncover this. If you know what might be the issue let me know.

I just saw your update and used version 18 and evertything works except for the login, so I need to find out how to connect remotely. I have access to the MSSQL server and Studio, but I'm not sure if the credentials I'm using are the correct ones.

[anibal2j]

@keitherskine I'm now trying to get pyodbc installed on my other Mac. This is an iMac on Intel silicone and the instructions are not working when I'm trying to install pyodbc:

The rest of the dependencies installed correctly - I think. What could be the problem here?

[keitherskine]

@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

[anibal2j]

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!

[chiefmetto23]

Hi i kind of have the same issue that @anibal2j had

Im running on macOS M1 Pro (macOS Monterey 12.6.2 ) Im getting the following error message:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate] (-1) (SQLDriverConnect)')

I did all steps of @keitherskine description

Thats the connection string im calling: cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';ENCRYPT=yes;UID='+username+';PWD='+password)

I have access to that MS SQL DB through DBeaver and im using the same credentials i was using there maybe someone can help...stuck here and not able to come forward

[anibal2j]

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.

[Shelby86]

i have tried all of the above and am still getting the error. I have the M2 on Ventura

[chiefmetto23]

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.

does not make any difference - same error for encrypt=no or not running with encrypt at all - i think on ODBC 18 its default yes

[Shelby86]

Thanks that worked!

[chiefmetto23]

@Shelby86 can u share what u did to fix it... @keitherskine do u have more ideas

[chiefmetto23]

@Shelby86 what python version are you running could that be an issue on my side?

[Shelby86]

I exited the terminal and it worked after closing it when I ran the above code for the ODBC.

[basnijholt]

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"

[arfaoui47]

Regarding this issue: Can't open lib 'FreeTDS' : file not found (0) (SQLDriverConnect)") You need to run: odbcinst -j to view the location of obdcinst.ini file :

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

Then append the following config to the file (/opt/homebrew/etc/odbcinst.ini):

[FreeTDS]
Description=FreeTDS Driver
Driver=/opt/homebrew/Cellar/freetds/1.3.18/lib/libtdsodbc.so
Setup=/opt/homebrew/Cellar/freetds/1.3.18/lib/libtdsodbc.so

[adamofig]

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

[mrluthra]

After following all the steps above, I can import pyodbc module in my base environment. But for any conda virtual environments, I am still getting the same error. ImportError: dlopen(/Users/<>/opt/anaconda3/envs/myenv/lib/python3.9/site-packages/pyodbc.cpython-39-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle' Thoughts?

[v-chojas]

Make sure you have unixODBC, arm64 version, installed.

[chuxz777]

On M1 chip this is the symlinks

sudo ln -s /opt/homebrew/etc/odbc.ini /etc/odbc.ini sudo ln -s /opt/homebrew/etc/odbc.ini /etc/odbc.ini

[borisevskiy]

@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

Thank you very much, everything worked for me

[mrluthra]

Thanks @v-chojas @chuxz777. It worked for me after switching to rosetta terminal