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

**URL:** <https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112>\
**Category:** General\
**Created:** [20 May 2024 07:19 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112 "2024-05-20T07:19:21Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 07:19 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/1 "2024-05-20T07:19:21Z")

</div>

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:

```auto
    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:  
 ![Bildschirmfoto_2024-05-20_09-13-33](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b146ce856e553cd2d700d69d9af091a17a68ddc4.png)

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?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 07:44 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/2 "2024-05-20T07:44:09Z")

</div>

It looks like your code snippet is Python?

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

```auto
{
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/](http://www.steves-internet-guide.com/send-json-data-mqtt-python/)

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 08:19 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/3 "2024-05-20T08:19:40Z")

</div>

ok I have installed it like this:

```auto
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)...

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 08:52 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/4 "2024-05-20T08:52:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [20 May 2024 09:37 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/5 "2024-05-20T09:37:39Z")

</div>

> [@nowomanman](#):
>
> The whole thing then looks like this in Node Red:  
> ![Bildschirmfoto_2024-05-20_09-13-33](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b146ce856e553cd2d700d69d9af091a17a68ddc4.png)

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

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 10:04 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/6 "2024-05-20T10:04:50Z")

</div>

this  
 ![Bildschirmfoto_2024-05-20_11-59-22](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/5/d57949b9d5c3731195a965ef61cb8c80908eb2b1.png)

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]')`

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 10:06 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/7 "2024-05-20T10:06:50Z")

</div>

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...

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 10:27 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/8 "2024-05-20T10:27:56Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 10:34 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/9 "2024-05-20T10:34:59Z")

</div>

```auto
#!/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

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 11:11 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/10 "2024-05-20T11:11:00Z")

</div>

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.

```auto
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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/9/f9aedb6f35ae5596448757b50075a57aac2634dd.png)

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 11:46 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/11 "2024-05-20T11:46:03Z")

</div>

![Bildschirmfoto_2024-05-20_13-41-49](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/d/dd43c2f26d7ba3fdef5f3f20d70d4e626eae736e.png)

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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 11:56 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/12 "2024-05-20T11:56:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 12:32 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/13 "2024-05-20T12:32:44Z")

</div>

![Bildschirmfoto_2024-05-20_14-30-28](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d3949d0306f1fa75e1cf7d1c59d4eb7fd26914fb.png)

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

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 13:22 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/14 "2024-05-20T13:22:31Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![nowomanman](https://avatars.discourse-cdn.com/v4/letter/n/977dab/32.png) [@nowomanman](https://discourse.nodered.org/u/nowomanman)\
**Post date:** [20 May 2024 13:40 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/15 "2024-05-20T13:40:53Z")

</div>

![Bildschirmfoto_2024-05-20_15-37-06](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/5/9537eec0b163d10c36a64d017082d3cc2f88b911.png)

> 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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 May 2024 14:34 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/16 "2024-05-20T14:34:32Z")

</div>

> [@nowomanman](#):
>
> by the way, there's a mariadb, if that helps.

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/5/9572dda20ced284de653efe46a721fca39978a13.png)

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

```auto
[{"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"}]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [18 August 2024 14:35 UTC](https://discourse.nodered.org/t/saving-data-from-mqtt-in-for-chart-in-a-database/88112/17 "2024-08-18T14:35:03Z")

</div>

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.