i want a mqtt message to upload into a database without manually typing, python main.py in the Terminal.
This is the python code i use: Save MQTT Data to SQLite Database using Python | Lindevs
Why are you bothering with Python to do something so simple that can be achieved in node-red without the added complication?
oh, i'm new to these kind of things 
what did you use in the function node? Thanks for answering!
Read the built in help and the nodes readme
from the readme...
When using Via msg.topic, parameters can be passed in the query using a msg.payload array. Ex:
msg.topic = `INSERT INTO user_table (name, surname) VALUES ($name, $surname)` msg.payload = ["John", "Smith"] return msg;
Do yourself a favour ...
I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.
The bit in the Python code that is the on_message function. It has a prepared SQL statement and inserts some values from the msg. That's the bit you need to translate.
Use debug nodes to see what is happening
ok, so we do have a payload but your database is complaining about a field called topic being null.
You need to add something for the topic in the INSERT query.
Please share the code of the function node (as text - since i cannot edit a picture)
And please show me the structure of your DB table
msg.topic = 'INSERT INTO sensors_data (payload) VALUES ($payload)'
msg.payload = msg.payload
return msg;
According you your database table, topic and created_at MUST NOT be NULL - so add them to the INSERT query.
Try this...
msg.topic = 'INSERT INTO sensors_data (topic, payload, created_at) VALUES ($topic, $payload, $timestamp)'
msg.payload = [msg.topic, msg.payload, Date.now()];
return msg;
1 Like
it works! THANK YOU
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
