In this project we will create a simple data logger to store MQTT messages in a sqlite database.
MQTT message Data can be JSON or simple text and is stored as is.
The project Consists of two modules.
- A sql logger class module sql_logger.py
- The logging script.
The script uses a main thread to get the messages(on_message callback) and a worker thread to store the data.
A queue is used to move the messages between threads.
- sqlite3 -pip install sqlite
Tutorials point have a good introduction to using sqlite with python.
If you are new to sql databases then I recommend these tutorials on SQLite here.
There are three basic things we need to do.
- Create the database
- Create the table to store the data.
- Create the SQL query to write the data
Creating the Database
This is pretty straightforward as all we need to do is decide on a database name and location. You can create the database from within the script or externally using SQL commands.
Creating the Table
To do this you need to know the data you will store and the type. SQLite doesn’t have many data types and the main ones used in the script are text and Integer.
Data is arranged in columns which require a name and each data entry is a row.
Again you can create the table from the SQLite command line or from within the script.
This is the most difficult part and I suggest that if you need to build your own query that you first test it using the SQLite command line before you code it in Python.
The basic structure is:
INSERT INTO TABLE_NAME (COLUMN NAMES) VALUES(COLUMN VALUES)
Sensor Data Characteristics
Because sensor data is often repetitive the script,by default, only logs (stores) changed data.
That means that if a sensor sends its status as “ON” once a second then it could result in 3600 “ON” messages logged every hour. The script will however only log 1 message.
You can override this using the -s option.
SQL Logger Class
The class is implemented in a module called sql_logger.py (sql logger).
It consists of 5 main methods
- __init__ – initailise the class takes the database name
- Log_sensor -logs the sensor data
- Log_message – replaced by log_sensor
- drop_table – drops a table
- create_table – creates a table
To create an instance you need to supply a single parameter – the database file name:
You then create a table to store the data, and optionally delete the old data by dropping the old table
The database fields are the fields that you will be storing. In my case they are:
- Sensor name
To store data in the database you use the Log_sensor method with two parameters as shown:
The data_query contains the SQL statements that you want to execute and the data_out parameter contains a list of field values:
data_query="INSERT INTO "+ Table_name \ +"(time,topic,sensor,message)VALUES(?,?,?,?)"
MQTT Data Logger
This script will store messages on a collection of topics. It stores:
- Message time
- Message topic
The on_message callback calls the message_handler function to process the message.
The message handler function calls the has_changed function to check if the message status is different from the last message.
If it is the same then the message isn’t stored as there is not point storing the same message value multiple times.
If it is different it is placed on the queue.
The worker takes the data from the queue and logs it to disk.
The relevant code is shown below.
def on_message(client,userdata, msg): topic=msg.topic m_decode=str(msg.payload.decode("utf-8","ignore")) message_handler(client,m_decode,topic) #print("message received") def message_handler(client,msg,topic): data=dict() tnow=time.localtime(time.time()) m=time.asctime(tnow)+" "+topic+" "+msg data["time"]=tnow data["topic"]=topic data["message"]=msg if has_changed(topic,msg): print("storing changed data",topic, " ",msg) q.put(data) #put messages on queue def has_changed(topic,msg): topic2=topic.lower() if topic2.find("control")!=-1: return False if topic in last_message: if last_message[topic]==msg: return False last_message[topic]=msg return True def log_worker(): logger=SQL_data_logger(db_file) logger.drop_table("logs") logger.create_table("logs",table_fields) """runs in own thread to log data""" while Log_worker_flag: while not q.empty(): results = q.get() if results is None: continue log.log_json(results) #print("message saved ",results["message"]) log.close_file()
The worker is started at the beginning of the script.
t = threading.Thread(target=log_worker) #start logger Log_worker_flag=True t.start() #start logging thread
The Log_worker_flag is used to stop the worker when the script terminates
Using the Data Logger
You need to provide the script with:
- List of topics to monitor
- broker name and port
- user name and password if needed.
- base log directory and number of logs have defaults
The script can also be run from the command line. Type
python mqtt-data-logger-sql.py -h
for a list of options.
You will always need to specify the broker name or IP address and the topics to log
Note: You may not need to use the python prefix or may
need to use python3 mqtt-data-logger-sql.py (Linux)
Specify broker and topics
python mqtt-data-logger-sql.py -b 192.168.1.157 -t sensors/#
Specify broker and multiple topics
python mqtt-data-logger-sql.py -b 192.168.1.157 -t sensors/# -t home/#
Log All Data:
python mqtt-data-logger-sql.py b 192.168.1.157 -t sensors/# -s
Specify the client name used by the logger
python mqtt-data-logger-sql.py b 192.168.1.157 -t sensors/# -n data-logger.
JSON data is stores as is and there is no data extraction. The screen shot below show sample stored data.
You can extract values from the data in the message handler function and store those but you will need to modify the table and insert commands accordingly.
In a similar fashion the topic in the example above also contains the sensor name and so this could be extracted from the topic and used.
Comments and Feedback
Was there enough detail in this tutorial for you to follow?
Please help me improve these tutorials by leaving your comments,rating them,asking questions.
I’m not a sqlite expert and my template for this script came from this Github script.
This article also helped with dealing with concurrency.
Really good collections of tutorials on sqlite here.
- A Guide to Logging MQTT Sensor Data
- Logging MQTT Sensor Data Using Python
- How to Log IOT Sensor Data by Topic Using Python
- Video Encoding/decoding JSON data in Python
- Simple Controllable MQTT Sensor Simulator in Python