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. Hovewer there might be a cases 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

No.ComponentQuantity
1.MQTT broker. For example, Mosquitto running on Raspberry Pi1

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 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 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 client receives message from the MQTT broker. A message topic and data 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

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) VALUES (?, ?)'
    cursor = db_conn.cursor()
    cursor.execute(sql, (message.topic, payload))
    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
    )
    """
    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.

Publish Messages on home Topic using MQTT explorer

Leave a Comment

Your email address will not be published. Required fields are marked *