# Mqtt message to python script into db database

**URL:** <https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694>\
**Category:** General\
**Created:** [11 March 2022 09:15 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694 "2022-03-11T09:15:42Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 09:15 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/1 "2022-03-11T09:15:42Z")

</div>

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](https://lindevs.com/save-mqtt-data-to-sqlite-database-using-python/)

 ![Skärmavbild 2022-03-11 kl. 10.09.40](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/8/38cf520a4aa9fd7a4025fea8d5280e4381ca69ed.png)

 ![Skärmavbild 2022-03-11 kl. 10.13.29](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/4/24e0773bd02a6e9e530840f3ceb3e9765d73195d.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 March 2022 09:23 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/2 "2022-03-11T09:23:29Z")

</div>

Why are you bothering with Python to do something so simple that can be achieved in node-red without the added complication?

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

---

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 09:43 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/3 "2022-03-11T09:43:06Z")

</div>

oh, i'm new to these kind of things 😃

what did you use in the function node? Thanks for answering!

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 March 2022 09:45 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/4 "2022-03-11T09:45:29Z")

</div>

> [@sayod](#):
>
> what did you use in the function node

Read the built in help and the nodes [readme](https://flows.nodered.org/node/node-red-node-sqlite)

from the readme...

> When using Via msg.topic, parameters can be passed in the query using a msg.payload array. Ex:
> 
> ```auto
> msg.topic = `INSERT INTO user_table (name, surname) VALUES ($name, $surname)`
> msg.payload = ["John", "Smith"]
> return msg;
> 
> ```

> [@sayod](#):
>
> oh, i'm new to these kind of things

Do yourself a favour ...

I recommend watching this playlist: [Node-RED Essentials](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). 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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [11 March 2022 09:45 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/5 "2022-03-11T09:45:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 10:04 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/6 "2022-03-11T10:04:02Z")

</div>

![Skärmavbild 2022-03-11 kl. 11.01.48](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ec0e6d85362bff870d4e49e72397d27849551fbd.png)

 ![Skärmavbild 2022-03-11 kl. 11.02.22](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/0/10f541ac214482ea372ead45f41bc140e59d8a2e.png)

i tried that before and this is what i got

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 March 2022 10:10 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/7 "2022-03-11T10:10:54Z")

</div>

Use debug nodes to see what is happening  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/3996de101d3b534a54ac873aaf1e2058c24194bb.png)

---

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 10:17 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/8 "2022-03-11T10:17:53Z")

</div>

![Skärmavbild 2022-03-11 kl. 11.16.39](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/1/d1bb9edff553aa587ca1e15354b658b15e0ce335.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 March 2022 10:21 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/9 "2022-03-11T10:21:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 11:34 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/10 "2022-03-11T11:34:07Z")

</div>

msg.topic = 'INSERT INTO sensors\_data (payload) VALUES ($payload)'  
msg.payload = msg.payload  
return msg;

 ![Skärmavbild 2022-03-11 kl. 12.33.43](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/e/4e5637bd8e1ed0fd50e9cae9791414c6961c40c6.jpeg)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 March 2022 11:47 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/11 "2022-03-11T11:47:48Z")

</div>

According you your database table, topic and created\_at MUST NOT be NULL - so add them to the INSERT query.

Try this...

```javascript
msg.topic = 'INSERT INTO sensors_data (topic, payload, created_at) VALUES ($topic, $payload, $timestamp)'
msg.payload = [msg.topic, msg.payload, Date.now()];
return msg;

```

---

<div class="post-metadata">

**Author:** ![sayod](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sayod/32/52427_2.png) [@sayod](https://discourse.nodered.org/u/sayod)\
**Post date:** [11 March 2022 11:54 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/12 "2022-03-11T11:54:53Z")

</div>

it works! THANK YOU

---

<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:** [25 March 2022 11:55 UTC](https://discourse.nodered.org/t/mqtt-message-to-python-script-into-db-database/59694/13 "2022-03-25T11:55:42Z")

</div>

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