[Node-RED] Execute Python on Anaconda virtual environment from Node-RED [Anaconda] [Python]

Thing you want to do

--Run Python from Node-RED.

Feature

-Use Anaconda (to facilitate Python execution environment) -Leverage available Node-RED nodes as much as possible (to lower the hurdles for beginners)

Image of what you want to do

Execution environment

• OS:windows 10
• postgreSQL：9.6
• ANACONDA NAVIGATOR：1.9.12 --Python (on Anaconda virtual environment): 3.7.7
• Node-RED：v1.0.4

goal

1. Connect to postgreSQL from Node-RED and create a csv file

You need 5 nodes. Join the following nodes in the following order.

1. [Function] function node --Purpose: Assemble SQL statements --Usage: Write SQL statement directly in msg.payload

Description image to function node

msg.select = "SELECT  val"
msg.from = "FROM public.sensordata "
msg.where = "where sensor ='Humidity sensor'"
msg.orderBy = "order by datetime desc"
msg.limit = "limit 1000"

msg.payload = msg.select
msg.payload += " " + msg.from
msg.payload += " " + msg.where
msg.payload += " " + msg.orderBy
msg.payload += " " + msg.limit

return msg;

2. [Storage] postgres node --Purpose: Connect to postgreSQL and execute SQL --Usage: Standard usage
3. [Function] json to csv converter node --Purpose: The postgre node returns in json format, so convert it to csv format --Usage: Standard usage
4. [Function] function node --Purpose: Specify the file name --Usage: Specify the full path of the csv file in msg.filename

Description image to function node

//Example) msg.rootpath = "C:\\temp\\conda"
//Example) msg.Get timestamp from inject node
msg.filename = msg.rootpath + "\\indata\\" + msg.timestamp + ".csv";

return msg;

5. [Storage] file node --Purpose: Create a csv file --Usage: Specify *** blank *** for the file name (if it is blank, create a file with the setting value in msg.filename)

2. Execute the bat file from Node-RED

Two nodes are needed. Join the following nodes in the following order.

point

--Specify the command + argument in *** msg.payload so that the execution command can be specified arbitrarily *** (Normally, the command is specified directly in the exec node, but in that case it cannot be changed)
* It seems that the command is executed with the setting value to the command of the exec node + blank + msg.payload

1. [Function] function node --Purpose: Assemble the execution command --Usage: Set the command to be executed in msg.payload

Description image to function node

//Full path of bat file to be executed Example)"C:\\temp\\conda\\ExecPython.bat"* Of the bat file that executes the specified Python program
msg.command = msg.execPythonCmd
//1st argument example)"test.py"* File name of the Python program to be executed
msg.arg_1 = msg.execPythonNm
//2nd argument Example) I to the Python program to be executed/F (input file)
msg.arg_2 = "indata\\" + msg.timestamp + ".csv"
//3rd argument Example) I to the Python program to be executed/F (output file)
msg.arg_3 = "outdata\\" + msg.timestamp + ".csv"

msg.payload = msg.command + " " + msg.arg_1 + " " + msg.arg_2 + " " + msg.arg_3

return msg;

2. [Function] exec node --Purpose: Execute command --Usage: Specify blank *** for the *** command. *** The checkbox for msg.payload is selected ***.

3. Enable the conda environment in the bat file and execute the Python program

ExecPython.bat

Move to rem execution folder
rem Example) C:\temp\When the execution environment is already prepared under conda
cd /d C:\temp\conda

Enable rem conda
call activateConda.bat

rem run python
rem %1:Python file name to run
rem %1～7:Arguments to Python to execute
python %1 %2 %3 %4 %5 %6 %7

activateConda.bat

conda activate [Environment name you want to enable Example) base]

4. Specify the I / F file for the Python program

test.py

import sys
#Get arguments
args = sys.argv

import pandas as pd
sensordata = pd.read_csv(args[1])

print(sensordata.describe())
sensordata.describe().to_csv(args[2], index=True)

Run

Preparation 1) Preparation of executable file

The files described in 3 and 4 are stored as follows.

Preparation 2) Flow creation

Prepare the flow described in 1 and 2. It may be easier to manage if you describe the information required for execution in the function node as shown below.

• Create a flow to execute in the order of "inject node-> execution condition setting-> 1-> 2"

Execution condition setting

msg.timestamp = msg.payload
msg.rootpath = "C:\\temp\\conda"
msg.execPythonCmd = "C:\\temp\\conda\\ExecPython.bat"
msg.execPythonNm = "test.py"
return msg;

Execution result

Read the csv file stored in the indata folder and output simple statistics to the outdata folder

in conclusion

"Node-RED" as an IoT platform, "Anaconda + Python + Jupyter Notebook" as a data science platform I think that can be mentioned, but I expect that DX will advance if these are connected seamlessly.