0

I've been trying to create a connection to my postgres DB. By doing so, I get following error by running this code

import pandas as pd
import numpy as np
import os
from sqlalchemy import create_engine, text
from datetime import datetime, timedelta
from pytz import timezone

postgres = create_engine("postgresql+psycopg2://user:password@xx.xx.xx.xx", connect_args={'sslmode':'require'})

Following error message appears:

Output exceeds the size limit. Open the full output data in a text editor
---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
/User/Documents/github/new_project copy.py Cell 4 in <cell line: 8>()
      5 from datetime import datetime, timedelta
      6 from pytz import timezone
----> 8 postgres = create_engine("postgresql+psycopg2://user:password@xx.xx.xx.xx", connect_args={'sslmode':'require'})

File <string>:2, in create_engine(url, **kwargs)

File ~/Documents/github/new_project/.venv/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py:309, in deprecated_params.<locals>.decorate.<locals>.warned(fn, *args, **kwargs)
    302     if m in kwargs:
    303         _warn_with_version(
    304             messages[m],
    305             versions[m],
    306             version_warnings[m],
    307             stacklevel=3,
    308         )
--> 309 return fn(*args, **kwargs)

File ~/Documents/github/new_project/.venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py:560, in create_engine(url, **kwargs)
    558         if k in kwargs:
    559             dbapi_args[k] = pop_kwarg(k)
--> 560     dbapi = dialect_cls.dbapi(**dbapi_args)
    562 dialect_args["dbapi"] = dbapi
    564 dialect_args.setdefault("compiler_linting", compiler.NO_LINTING)
...
     67 from psycopg2 import extensions as _ext

ImportError: dlopen(/Users/myname/Documents/github/new_project/.venv/lib/python3.9/site-packages/psycopg2/_psycopg.cpython-39-darwin.so, 0x0002): Library not loaded: '/opt/homebrew/opt/postgresql/lib/libpq.5.dylib'
  Referenced from: '/Users/myname/Documents/github/new_project/.venv/lib/python3.9/site-packages/psycopg2/_psycopg.cpython-39-darwin.so'
  Reason: tried: '/opt/homebrew/opt/postgresql/lib/libpq.5.dylib' (no such file), '/usr/local/lib/libpq.5.dylib' (no such file), '/usr/lib/libpq.5.dylib' (no such file)

(due to privacy reasons I changed the path, however, the logic was not changed)

Therefore, the error seems with the installation of the psycopg2 library. But by running the pip3 list I get psycopg2 2.9.3 showed as installed.

I'm running the code in a virtual environment (venv) but I also checked locally, which did not eliminate the problem.

Also to note is that this problem did not occur before. It could be that it is somehow related to the upgrade from postgresql@13 to postgresql@14, which I reversed again in the meantime. Additionally, I am running the code on a macOS with a M1 chip.

I looked up numerous articles in StackOverflow and other sites (Problems using psycopg2 on Mac OS (Yosemite), Import psycopg2 Library not loaded), but none of them could solve the problem.

As all my attempts failed for example reinstalling psycopg2 and also installing psycopg2-binary did not solve my problem, a possible solution or an indication of the source of the error is much appreciated.

Albin
  • 822
  • 1
  • 7
  • 25
  • Are you running your script inside vscode or a similar environment, if so look at this https://stackoverflow.com/questions/72823258/vscode-interactive-python-output-exceeds-size-limit. – Pepe N O Sep 01 '22 at 14:34
  • @PepeNO yes, I am running my code in vscode. But that only refers to the output limit of the error message if I'm correct – Albin Sep 01 '22 at 14:37
  • For second part of the error check https://stackoverflow.com/questions/9023482/library-not-loaded-usr-local-lib-libpq-5-4-dylib. – Pepe N O Sep 01 '22 at 14:40
  • @PepeNO I tried that but got following: `Gem 'pg' is not installed` also checked with postgresql and postgresql@13. So this is also not fixing the error – Albin Sep 01 '22 at 14:42
  • This user - https://stackoverflow.com/questions/13643452/libssl-and-libcrypto-causing-dyld-library-not-loaded-usr-lib-libpq-5-dylib - found that: Turns out /usr/lib/libpq.5.dylib was absent but /usr/lib/libpq.5.4.dylib was not 'sudo ln -s /usr/lib/libpq.5.4.dylib /usr/lib/libpq.5.dylib' fixed the issue. Might be worth looking what you have in your /usr/lib? – David Harris Sep 01 '22 at 14:47
  • @DavidHarris okay, that seems to point to the possible solution. `libpq.5.4.dylib` is absent in the folder `/usr/lib/`. I am not really sure what the sudo command above will do. Or should I look where `libpq.5.4.dylib` is located? – Albin Sep 01 '22 at 14:54
  • @Albin that solution will only work if there is a file in /usr/lib that is the one you really want to use, but has a different name from the one psycopg is looking for. The 'sudo ln ...' command creates a link so that the file that is there gets used when the looked-for file is referenced. – David Harris Sep 01 '22 at 18:17

1 Answers1

1

I found that in my case the file was in another folder. I created symlink with the command:

sudo ln -s /Applications/Postgres.app/Contents/Versions/14/lib/libpq.5.14.dylib /usr/local/lib/libpq.5.dylib

now it works fine on macos Monterey 12.4

Łukasz Bala
  • 13
  • 2