Import psycopg2 error when running python node

#1

I am trying to execute python script which invokes postgress database connection on a remote machine and I see following error, though the psycopg2 package is installed as part of python packages. I have installed "node-red-contrib-python3-function" , "node-red-contrib-python-function" and "node-red-contrib-pythonshell"
.
msg.payload : string[136]
string[136]
Traceback (most recent call last):
File "dbconnect.py", line 5, in
import psycopg2
ImportError: No module named psycopg2

I see this error if I use either of "python node" or pythonshell or "exec" node.
Any pointers in this regard to unblock me.

#2

And it works from the commandline ?

#3

Why call a python script? Why not query the database from node red?

There are about 6 nodes for postgres (search postgres here)

e.g. This one

#4

Yes, it works from command line

#5

is Node-RED running in the same environment as your command line user ? Ie are the python packages available globally - or just to your user. ?

#6

Yes, its running on same environment as command line user.
Infact I have my python script placed at this location.
/var/lib/docker/overlay2/18abcc8e24afc641fa13752e9d5477f79d5fe2d2d9a643496b1473995599fd9b/merged/usr/src/node-red
I execute the python script as "root" user, which is working fine from command line.
From node red, I get the import error(*"ImportError: No module named psycopg2")not sure whats the missing link here.

Should I have this file in different path?
The reason why I am using python script to connect to postgres DB as I need to pass certificate and key file to connect to my database.

#7

As I don't use postgres nor the available nodes so I cant answer whether they support that i'm afraid.

However, it would be good for the community if they did. Perhaps give them a try & feedback a request/issue via github?

#8

Sure, I will do that.
My current issue to run python script that connects to postgres is running into "psycopq2" import error, that I need to get it resolved to make me get going.

#9

I figured out the problem, the problem is the docker image of node-red do not contain "python-psycopg2" packages, you can verify it by getting into node-red shell.

  1. docker exec -it mynodered /bin/bash
    I ran it manually, it failed again.

node-red@77f884ce09a9:~$ python dbconnect.py
Traceback (most recent call last):
File "dbconnect.py", line 5, in
import psycopg2
ImportError: No module named psycopg2

However the global python present in my system had this when I executed it as root.

How can I add this "python-psycopg2" package to node-red docker image.
I tried even building it, it did not help.
I did modify the Dockerfile to install "psycopg2 and tried to build the docker image of "node-red" it did not help.

RUN apt-get install python-psycopg2

Step 10/14 : RUN apt-get install python-psycopg2
---> Running in 506c3d9919f6
E: Could not open lock file /var/lib/dpkg/lock - open (13: Permission denied)
E: Unable to lock the administration directory (/var/lib/dpkg/), are you root?
The command '/bin/sh -c apt-get install python-psycopg2' returned a non-zero code: 100

Any help in this regard is appreciated.

#10

You need to be the root user to run apt-get. I think the following will help.

(Please pick if you want to continue this discussion here or in slack... not going to split it over both places)

USER root
RUN apt-get install python-psycopg2
USER node-red
#11

I tried as you suggested, look like its unable to pick the package.

Step 3/4 : RUN apt-get install python-psycopg2
---> Running in 7a41d497a588
Reading package lists...
Building dependency tree...
Reading state information...
E: Unable to locate package python-psycopg2
The command '/bin/sh -c apt-get install python-psycopg2' returned a non-zero code: 100

Am I missing something, I tried to install "pip" even that had run into same error.

#12

I'm just making educated guesses as it isn't something I've ever done.

A quick google search finds lots of threads talking about installing psycopg2 into a Docker image. I suggest you go read those threads and follow their advice based on actual experience.