Saving data from 'mqtt in' for chart in a database?

[nowomanman]

Hello,
I have read a lot, but have not found a solution to my problem, as the data I want to save is not all in the payload.

I therefore need help on how to get this data into the payload as a string so that it can be saved in the database.

My sensor sends a message as follows:

    if temp is not None:
        publish.single(pub_topic, timestamp + str(temp),
                hostname=Broker, port=1883,
                auth=auth
                )

The whole thing then looks like this in Node Red:

I need the following data for the chart:
topic, timestamp, temperature (payload)

But I have not yet found out how to get this data out of the message from the sensor and how I can then send the whole thing as a string to the database.

Can anyone help me?

[jbudd]

It looks like your code snippet is Python?

Ideally you would construct your data as a JSON string, something like this

{
timestamp: "YYYY-MM-DDTHH:mm:ss.sssZ",
temperature: 14.4
}

You might find this tutorial useful http://www.steves-internet-guide.com/send-json-data-mqtt-python/

[nowomanman]

ok I have installed it like this:

while True:
    temp = read_temp()
    payload = {
        topic: tempNO
        timestamp: "YYYY-MM-DDTHH:mm:ss.sssZ",
        temperature: temp
    }
    if temp is not None:
        publish.single(pub_topic, payload,
                hostname=Broker, port=1883,
                auth=auth
                )

but then the pi does not send out a message.

I'm sorry but I'm not that familiar with Python either, I'm still a complete beginner in everything (python, nodered, mqtt)...

[jbudd]

Is that the way the tutorial I suggested sends a json formatted payload?

[zenofmud]

@nowomanman

The whole thing then looks like this in Node Red:

What do you see if you change the debug node to display the complete msg object?

[nowomanman]

this

but the time stamp is missing.

and I currently have no idea how to get this as a string so that it can be entered into the database.

and the database wants it that way:
INSERT INTO `dates`(`topic`, `timestamp`, `temperature`) VALUES ('[value-1]','[value-2]','[value-3]')

[nowomanman]

no unfortunately that confuses me even more.

I don't work with python directly on the pi. I have only created a python script that is executed via crontab every 10 minutes. That's why I don't see what he see in the tutorial and therefore can't understand it...

[jbudd]

@nowomanman

I don't work with python directly on the pi. I have only created a python script that is executed via crontab every 10 minutes.

I don't understand that, sorry.

Can you post your whole python script?
Post it as text not an image (use the </> button at the top of the editor)
If it contains any sensitive information: usernames, passwords etc of course falsify them before posting.

[nowomanman]

#!/usr/bin/python3
 
import os
import glob
import time
import paho.mqtt.publish as publish
 
Broker = 'IP'
auth = {
    'username': 'USERNAME',
    'password': 'PASSWORD',
}
 
pub_topic = 'TOPICNAME'
 
os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')
 
base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob(base_dir + '28*')[0]
device_file = device_folder + '/w1_slave'
 
def read_temp_raw():
    f = open(device_file, 'r')
    lines = f.readlines()
    f.close()
    return lines
 
def read_temp():
    lines = read_temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        time.sleep(0.2)
        lines = read_temp_raw()
    equals_pos = lines[1].find('t=')
    if equals_pos != -1:
        temp_string = lines[1][equals_pos+2:]
        temp_c = float(temp_string) / 1000.0
        temp_c = round(temp_c, 1)
        return temp_c
 
while True:
    temp = read_temp()
    if temp is not None:
        publish.single(pub_topic, str(temp),
                hostname=Broker, port=1883,
                auth=auth
                )
    break

[jbudd]

Your script does not work for me so I can't update it for you.
But here is a script which acquires the current time and prints it with temperature as a json string.
The essential bits are import json and using json.dumps to turn the python dictionary into a json string.

import json
from datetime import datetime

dt = datetime.now().isoformat()
temperature = 27.9

# Python dictionary
data = {
    'timestamp': dt,
    'temperature': temperature
}

# Convert dictionary to JSON string
json_data = json.dumps(data)

print(json_data)

Hopefully you could publish json_data and then Node-red could understand it

[nowomanman]

cool now I have the data I need.

Now the only thing missing is how to get it into the database.

Unfortunately, just turning it into a string doesn't work.

[jbudd]

What is the structure of your table?
Show us your INSERT statement.

[nowomanman]

INSERT INTO `dates`(`topic`, `timestamp`, `temperature`) VALUES ('[value-1]','[value-2]','[value-3]')

[jbudd]

I can't tell from that what data types the fields "timestamp" and "temperature" are.

You need to make sure the data you are providing matches the data type in the dB.

I'm not familiar with the syntax for an insert for your database, I assume you are using some sort of prepared statement?

[nowomanman]

I'm not familiar with the syntax for an insert for your database, I assume you are using some sort of prepared statement?

yes, because i have no idea how to do it. I'm not really familiar with databases.

by the way, there's a mariadb, if that helps.

[jbudd]

@nowomanman

by the way, there's a mariadb, if that helps.

If you mean that your database is Mariadb, this example flow might help:

The inject node gives msg.topic, msg.payload.timestamp and msg.payload.temperature. You could use your MQTT-in message instead.

"Construct SQL" is a function node which converts to the format required to pass to mariadb

"Espresso" is my Mariadb database. You would obviously need to replace this with your own.

To try it: copy this code and in Node-red Import and paste

[{"id":"17a9d087f8fa087b","type":"inject","z":"2a11e7cb421ab50e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"the/topic","payload":"{\"timestamp\":\"2024-05-20T15:22:59.12345\",\"temperature\":19.56}","payloadType":"json","x":170,"y":140,"wires":[["fe575281c5746cc3"]]},{"id":"fe575281c5746cc3","type":"function","z":"2a11e7cb421ab50e","name":"construct SQL","func":"// This is the data we have to work with\nconst topic = msg.topic\nconst ts = msg.payload.timestamp\nconst temp = msg.payload.temperature\n\n// Build the insert  statement as msg.topic\nmsg.topic = \"INSERT INTO mytable (topic, timestamp, temperature)  VALUES( :topic, :timestamp, :temperature)\"\n\n// And the values as key: value pairs in msg.payload\nmsg.payload = {\n    topic: topic,\n    timestamp: ts,\n    temperature : temp\n}\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":140,"wires":[["f7c847804ae5ca67"]]},{"id":"2ac2e186c0d2fa41","type":"debug","z":"2a11e7cb421ab50e","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":140,"wires":[]},{"id":"f7c847804ae5ca67","type":"mysql","z":"2a11e7cb421ab50e","mydb":"2381cb15.b563bc","name":"","x":520,"y":140,"wires":[["2ac2e186c0d2fa41"]]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"espresso","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"}]