pip install psycopg2 on MacOS M1 and python 3.10.5 not working

psycopg2 is not working on M1. Did anyone successfully install?

  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/psycopg2/__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/psycopg2/_psycopg.cpython-310-darwin.so, 0x0002): symbol not found in flat namespace '_PQbackendPID'

I've tried installing:

psycopg2
psycopg2 and psycopg2-binary
psycopg2-binary alone

Nothing seems to work. I'm using python3.10.5

Many Thanks!

Solution

Install libpq and openssl from brew:

brew install libpq
brew install openssl

Check path and compiler flags:

>>> brew info libpq
libpq: stable 14.4 (bottled) [keg-only]
Postgres C API library
https://www.postgresql.org/docs/14/libpq.html
/opt/homebrew/Cellar/libpq/14.4 (2,338 files, 28.3MB)
  Poured from bottle on 2022-07-20 at 16:31:04
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/libpq.rb
License: PostgreSQL
==> Dependencies
Required: krb5 ✔, openssl@1.1 ✔
==> Caveats
libpq is keg-only, which means it was not symlinked into /opt/homebrew,
because conflicts with postgres formula.

If you need to have libpq first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc

For compilers to find libpq you may need to set:
  export LDFLAGS="-L/opt/homebrew/opt/libpq/lib"
  export CPPFLAGS="-I/opt/homebrew/opt/libpq/include"

>>> brew info openssl
openssl@3: stable 3.0.5 (bottled) [keg-only]
Cryptography and SSL/TLS Toolkit
https://openssl.org/
/opt/homebrew/Cellar/openssl@3/3.0.5 (6,444 files, 27.9MB)
  Poured from bottle on 2022-07-20 at 16:31:21
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/openssl@3.rb
License: Apache-2.0
==> Dependencies
Required: ca-certificates ✔
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /opt/homebrew/etc/openssl@3/certs

and run
  /opt/homebrew/opt/openssl@3/bin/c_rehash

openssl@3 is keg-only, which means it was not symlinked into /opt/homebrew,
because macOS provides LibreSSL.

If you need to have openssl@3 first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/openssl@3/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@3 you may need to set:
  export LDFLAGS="-L/opt/homebrew/opt/openssl@3/lib"
  export CPPFLAGS="-I/opt/homebrew/opt/openssl@3/include"

Export path for libpq and export LD/CPPFLAGS for openssl (taken from output above) and install psycopg2:

>>> export PATH=/opt/homebrew/opt/libpq/bin:$PATH
>>> export LDFLAGS="-L/opt/homebrew/opt/openssl@3/lib"
>>> export CPPFLAGS="-I/opt/homebrew/opt/openssl@3/include"
>>> python -m pip install psycopg2
Collecting psycopg2
  Using cached psycopg2-2.9.3.tar.gz (380 kB)
  Preparing metadata (setup.py) ... done
Building wheels for collected packages: psycopg2
  Building wheel for psycopg2 (setup.py) ... done
  Created wheel for psycopg2: filename=psycopg2-2.9.3-cp310-cp310-macosx_12_0_arm64.whl size=142252 sha256=78ca9fc7ca6752e234904bf38d052937e20b063cb68eb67caa874511207e076e
  Stored in directory: /Users/.../Library/Caches/pip/wheels/81/b6/3d/091aad3e8919ea76c84c2674b02ce3ab52de882e091c39249e
Successfully built psycopg2
Installing collected packages: psycopg2
Successfully installed psycopg2-2.9.3

Test:

python
Python 3.10.5 (main, Jul 20 2022, 17:05:05) [Clang 13.0.0 (clang-1300.0.27.3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> c = psycopg2.connect("port=5556 host=localhost ...")
>>> c
<connection object at 0x102d8a500; dsn: 'user=xxx password=xxx dbname=xxx host=localhost port=5556', closed: 0>
>>> exit()