psycopg2 - ImportError: DLL load failed while importing _psycopg: The operating system cannot run %1

Question

I installed psycopg2 using conda on Windows 10.

https://anaconda.org/anaconda/psycopg2

I did it in a clean new conda environment (named wr).

I then tried to run this sample app but I am getting this error (see below). I have no idea what I might be doing wrong because it was all straightforward and I did it in a clean way.

Any ideas how to solve this?

import psycopg2
try:
    connection = psycopg2.connect(user = "***",
                                  password = "***",
                                  host = "***",
                                  port = "5432",
                                  database = "***")


    cursor = connection.cursor()
    # Print PostgreSQL Connection properties
    print ( connection.get_dsn_parameters(),"\n")

    # Print PostgreSQL version
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

Error in VS code:

PS C:\Work\WRR\git\tools\JTunnelTestApp>  cd 'c:\Work\WRR\git\tools\JTunnelTestApp'; & 'C:\Programs\Anaconda3\envs\wr\python.exe' 'c:\Users\petrop01\.vscode\extensions\ms-python.python-2020.9.114305\pythonFiles\lib\python\debugpy\launcher' '56143' '--' 'c:\Work\WRR\git\tools\JTunnelTestApp\main.py'
Traceback (most recent call last):
  File "c:\Work\WRR\git\tools\JTunnelTestApp\main.py", line 1, in <module>
    import psycopg2
  File "C:\Programs\Anaconda3\envs\wr\lib\site-packages\psycopg2\__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
ImportError: DLL load failed while importing _psycopg: The operating system cannot run %1.
PS C:\Work\WRR\git\tools\JTunnelTestApp>

EDIT: Seems they had a bug open for this 2 years ago and they just closed it, ignoring it completely.

https://github.com/psycopg/psycopg2/issues/734

Notarize

Answer 1

you can use psycopg2-binary library instead of psycopg2. after installation the usage is the same.

Knit

Thanks... I can but... should I? Will it solve my problem? Where is this documented? I mean... what I did was really by the book... So why is it failing so badly? – Notarize

Also, psycopg2-binary - what is it? It does not seem to have a special package for conda. – Notarize

psycopg2 requires some dll file to be installed on your operating system. you can either install it manually or you can install psycopg2-binary instead which packs both the library and dll files together. the documentation resides in psycopg2 library docs: psycopg.org/docs/install.html – Knit

i haven't used conda until now, but i think all the pip packages are also available there – Knit

OK... Installing (via pip) and using psycopg2-binary works fine. I tried it. No idea why the anaconda package is broken though. – Notarize

psycopg2-binary can also be installed from conda forge: conda install -c conda-forge psycopg2-binary – Aircondition

Answer 2

For Windows when using Anaconda I have found that installing from the VS Code/Windows terminal just doesn't work for all cases. Instead install from the Anaconda terminal. I have no idea why this is the case, but it has been the fix on multiple computers.

1. Open Anaconda navigator

2. Environments

3. Select the environment you want to install psycopg2/psycopg2-binary to and Open Terminal

4. Uninstall any pervious installs

   pip uninstall psycopg2

   pip uninstall psycopg2-binary

5. Install again

   pip install psycopg2

   pip install psycopg2-binary

Now it should work.

Particularly found this useful to get standalone scripts that make use of Django ORM to work with Postgresql. Django was working fine, but without this fix the standalone scripts don't. Very strange.

Garth

This is due to that anaconda adds its own folders to Path variable – Vue

What do you mean with "Anaconda Terminal"? "Anaconda Prompt" or "Anaconda Navigator"? – Disgusting

@Disgusting Anaconda is a Python bundle which comes with a lot of tools focused on data science. anaconda.com Anaconda Navigator is part of the bundle. If you went to the Anaconda website and installed your Python from there then you are using Anaconda. If you installed Python via the windows store, then you are not using Anaconda. – Garth

This does not answer the question. – Disgusting

Hi Mouwsy, I'm not sure I understand at what part of the process you are stuck? To directly answer your question, by 'with Anaconda Terminal' I mean 'using Anaconda Terminal'. Anaconda Terminal is a terminal, you can use it by typing commands in it. For a detailed definition of the Anaconda products you may want to check the Anaconda website. – Garth

Answer 3

I found the solution in this reddit post, all credit to u/brianckeegan

If you're using conda to manage psycopg2 for Python 3.9+, the wheels point to an old version (v2.8.6) which causes this error. If you install via pip, you'll get a more up-to-date version (v2.9.1) that supports Python 3.9. Until the conda wheels are updated:

conda remove psycopg2
pip install psycopg2

Bartie

Answer 4

For me updating psycopg2 to 2.9.9 worked for Python 3.11

Houk

Answer 5

for me updating to psycopg2 and psycopg2-binary to 2.8.6 worked in python 3.8

Ninon

Answer 6

For me, updating psycopg2 to 2.9.1 works in Python 3.10

Sporulate

Answer 7

This worked for me

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2

Restriction