Home Labs Stories Consuming Payload Data with Python

CONSUMING PAYLOAD DATA WITH PYTHON

image story

ABOUT THIS STORY


Posted on Oct. 11, 2021



Intermediate
1 Hour

Introduction

The Configuration File

Configuring The Things Stack API

Configuring PostgreSQL

Configuring Devices

Run with systemd

Run with Docker

Troubleshooting

Thoughts and Improvements

Introduction


Introduction

In this lab, we'll use Python to subscribe to The Things Stack v3 (via MQTT) and write the payload values to a PostgreSQL database.

It is assume that you have a working application and devices in your console.

The lab will revolve around a configurable script that I've written, available on GitLab here. It uses Paho MQTT client for Python to subscribe to MQTT and SQLAlchemy to write to the DB.
You should clone this to your machine and follow the configuration instructions in the next sections to get started.

The script handles multiple devices and is configured via a YAML file, passed into it via a CLI flag.

It subscribes to TTSv3 via MQTT and creates a table per device, then writes the payload data as it comes in.

It provides support for the TimescaleDB, an extension built to optimise PostgreSQL for time-series data.

The data flow will look like the diagram below.

Data Flow

And the tables for your devices will look a little something like:

ERD Representation