Mqtt message to python script into db database

i want a mqtt message to upload into a database without manually typing, python 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 :smiley:

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.

i tried that before and this is what i got

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,];
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.