Save MQTT Data to SQLite Database using Python

MQTT clients can publish messages to a MQTT broker, and other clients can subscribe to message topics which they want to receive. However, there might be a case where we may need historical data for analysis and reporting. Many MQTT brokers don't have any functionality that allows to save MQTT data to a database.

This tutorial provides example how to save MQTT data to the SQLite database using Python.

Components

Library

The paho-mqtt is an MQTT client library for Python. This library can be installed using pip package manager from the command line.

pip install paho-mqtt

Code

We define variables to store the IP address or hostname of MQTT broker, the network port, the username and the corresponding password. Each MQTT client should have a unique ID, which is used by an MQTT broker for identification.

We use the multi-level wildcard # in the topic. It means that we will receive all messages of a topic that begins with the home pattern. For example, home/temperature, home/humidity, etc.

SQLite database will be stored in the mqtt.db file.

We define two callback functions. The on_connect function is called when a connection is established between a client and MQTT broker. Once the client has successfully connected, we subscribe to the message topic. The on_message function is called when the client receives a message from the MQTT broker. A message topic, data and Unix timestamp is saved to the database table.

In the main function, a connection is established to the SQLite database and a new table is created if it does not already exist. We create an instance of Client class. The user_data_set method is used to set the custom data that will be passed to callback functions. We connect to the MQTT broker and starting a loop that invokes callback functions, handles reconnecting, etc.

main.py

import paho.mqtt.client as mqtt
import sqlite3
from time import time

MQTT_HOST = '192.168.0.184'
MQTT_PORT = 1883
MQTT_CLIENT_ID = 'Python MQTT client'
MQTT_USER = 'YOUR MQTT USER'
MQTT_PASSWORD = 'YOUR MQTT USER PASSWORD'
TOPIC = 'home/#'

DATABASE_FILE = 'mqtt.db'


def on_connect(mqtt_client, user_data, flags, conn_result):
    mqtt_client.subscribe(TOPIC)


def on_message(mqtt_client, user_data, message):
    payload = message.payload.decode('utf-8')

    db_conn = user_data['db_conn']
    sql = 'INSERT INTO sensors_data (topic, payload, created_at) VALUES (?, ?, ?)'
    cursor = db_conn.cursor()
    cursor.execute(sql, (message.topic, payload, int(time())))
    db_conn.commit()
    cursor.close()


def main():
    db_conn = sqlite3.connect(DATABASE_FILE)
    sql = """
    CREATE TABLE IF NOT EXISTS sensors_data (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        topic TEXT NOT NULL,
        payload TEXT NOT NULL,
        created_at INTEGER NOT NULL
    )
    """
    cursor = db_conn.cursor()
    cursor.execute(sql)
    cursor.close()

    mqtt_client = mqtt.Client(MQTT_CLIENT_ID)
    mqtt_client.username_pw_set(MQTT_USER, MQTT_PASSWORD)
    mqtt_client.user_data_set({'db_conn': db_conn})

    mqtt_client.on_connect = on_connect
    mqtt_client.on_message = on_message

    mqtt_client.connect(MQTT_HOST, MQTT_PORT)
    mqtt_client.loop_forever()


main()

Testing

Run Python script from the command line:

python main.py

We can use MQTT Explorer for testing. We publish messages on the home/temperature and home/humidity topics. A Python script receives messages and saves data to the SQLite Database.