使用Python将MQTT传感器数据记录到SQL数据库_msg.payload.decode()

在这个项目中，我们将创建一个简单的数据记录器，将数据记录到sqlite数据库。

 

该项目包括两个模块。

• sql logger类模块sql_logger.py
• 日志记录脚本。

该脚本使用一个主线程来获取数据（on_message回调），并使用一个工作线程来记录数据。

队列用于在线程之间移动消息。


 

先决条件者

• 蟒蛇
• sqlite3 -pip安装sqlite

SQLITE基础

教程要点很好地介绍了将sqlite与python一起使用。

如果您不熟悉sql数据库，那么我在这里推荐有关SQLite的这些教程。

我们需要做三件事。

• 创建数据库
• 创建表以存储数据。
• 创建SQL查询以写入数据

创建数据库

这非常简单，因为我们要做的就是确定数据库名称和位置。您可以从脚本内部或使用SQL命令在外部创建数据库。

创建表

为此，您需要知道要存储的数据和类型。SQLite没有很多数据类型，脚本中使用的主要数据类型是text和Integer。

数据排列在需要名称的列中，每个数据条目都是一行。

同样，您可以从SQLite命令行或在脚本中创建表。

数据库查询

这是最困难的部分，我建议如果您需要构建自己的查询，请先使用SQLite命令行对其进行测试，然后再使用Python对其进行编码。

基本结构是：

插入TABLE_NAME（列名称）值（COLUMN VALUES）

传感器数据特征

由于传感器数据通常是重复的脚本，因此默认情况下，仅记录更改的数据。

这意味着，如果传感器每秒将其状态发送为“ ON”，则每小时可能会记录3600条“ ON”消息。但是，该脚本将仅记录1条消息。

您可以使用-s选项覆盖它。

SQL Logger类

该类在名为sql_logger.py（sql logger）的模块中实现。

它由5种主要方法组成

• __init__ –不具体化类使用数据库名称
• Log_sensor-记录传感器数据
• Log_message –由log_sensor取代
• drop_table –删除表
• create_table –创建一个表

要创建实例，您需要提供一个参数–数据库文件名：

logger = SQL_data_logger（db_file）

然后，您创建一个表来存储数据，并有选择地删除旧表以删除旧数据。

 
logger.drop_table("logs")
logger.create_table("logs",table_fields) 

数据库字段是您将要存储的字段。就我而言，它们是：

• 时间
• 话题
• 传感器名称
• 信息

要将数据存储在数据库中，请使用Log_sensor方法和两个参数，如下所示：

logger.Log_sensor（data_query，data_out）

该data_query包含SQL语句要执行和DATA_OUT参数包含字段值的列表：

 data_query =“ INSERT INTO” + Table_name \ +“（时间，主题，传感器，消息）VALUES（？，？，？，？）” 

data_out = [时间，主题，传感器，消息]

MQTT数据记录器

该脚本将记录有关主题的数据。它记录

• 留言时间
• 留言主题
• 信息

该ON_MESSAGE回调调用MESSAGE_HANDLER函数来处理消息。

消息处理程序函数调用has_changed函数以检查消息状态是否与最后一条消息不同。

如果相同，则不存储该消息，因为没有一点可以多次存储相同的消息值。

如果不同，则将其放在队列中。

工作程序从队列中获取数据并将其记录到磁盘。

相关代码如下所示。

def on_message（客户端，用户数据，信息）：
    topic = msg.topic
    m_decode = str（msg.payload.decode（“ utf-8”，“忽略”））
    message_handler（客户，m_decode，主题）
    #print（“收到邮件”）
def message_handler（client，msg，topic）：
    数据= dict（）
    tnow = time.localtime（time.time（））
    m = time.asctime（tnow）+“” + topic +“” + msg
    data [“ time”] = now
    data [“ topic”] =主题
    data [“ message”] = msg
    如果has_changed（topic，msg）：
        打印（“存储更改的数据”，主题，“”，msg）
        q.put（data）#put消息在队列中
def has_changed（topic，msg）：
    topic2 = topic.lower（）
    如果topic2.find（“ control”）！=-1：
        返回False
    如果主题在last_message中：
        如果last_message [topic] == msg：
            返回False
    last_message [topic] = msg
    返回True
def log_worker（）：
    logger = SQL_data_logger（db_file）
    logger.drop_table（“ logs”）
    logger.create_table（“ logs”，table_fields）
    “”“在自己的线程中运行以记录数据”“”
    而Log_worker_flag：
        而不是q.empty（）：
            结果= q.get（）
            如果结果为None：
                继续
            log.log_json（结果）
            #print（“已保存邮件”，结果[“邮件”]）
    log.close_file（）

工作程序在脚本的开头启动。

t = threading.Thread（target = log_worker）＃启动记录器
Log_worker_flag =真实
t.start（）＃启动日志记录线程

该Log_worker_flag是用来阻止工人当脚本终止

使用数据记录仪

您需要为脚本提供：

• 要监视的主题列表
• 经纪人名称和港口
• 用户名和密码（如果需要）。
• 基本日志目录和日志数具有默认值

该脚本也可以从命令行运行。类型

python mqtt-data-logger-sql.py -h

有关选项列表。

用法示例：

您将始终需要指定代理名称或IP地址以及要记录的主题

注意：您可能不需要使用python前缀或可能
需要使用python3 mqtt-data-logger-sql.py（Linux）

指定经纪人和主题

python mqtt-data-logger-sql.py -b 192.168.1.157 -t sensor /＃

指定经纪人和多个主题

python mqtt-data-logger-sql.py -b 192.168.1.157 -t sensor /＃-t home /＃

记录所有数据：

python mqtt-data-logger-sql.py b 192.168.1.157 -t sensor /＃-s

指定记录器使用的客户端名称

python mqtt-data-logger-sql.py b 192.168.1.157 -t sensor /＃-n data-logger

评论和反馈

本教程中是否有足够的细节供您遵循？

请留下您的评论，给它们评分，提出问题，以帮助我改善这些教程。

资源资源

我不是sqlite专家，该脚本的模板来自此Github脚本。

该文章还帮助处理并发。

真的很好的sqlite教程集合在这里。

下载

相关教程

• MQTT传感器数据记录指南
• 使用Python记录MQTT传感器数据
• 如何使用Python按主题记录IOT传感器数据
• Python中的视频编码/解码JSON数据
• Python中的简单可控MQTT传感器模拟器

PYTHON MQTT项目

8条评论

1. 亚历克斯 说：
   2020年5月3日，下午3:35

   有趣的Sqlite并发性文章，但是大多数时候，您需要将所有代理MSG写入db中，这些消息来自许多设备（通常同时），通常，数据库锁定来了……
   我不知道如何处理这个问题使用简单的SQlite数据库

   回复
   1. 史蒂夫 说：
      2020年5月3日，下午4:12

      我了解您关于并发的观点，我不是SQL专家，但是对于记录器，只有一个用户，也就是记录器，因此这不是问题。
      我使用SQLLite是因为它很容易，并且在任何系统上都可用，如果您想采用这种方式，代码也应与MYSQL一起使用。 史蒂夫（
      Rgds
      Steve）

      回复
2. 周杰伦 说：
   2020年4月7日，下午3:08

   史蒂夫

   我已经看到了msg.payload.decode（）和msg.payload.translate（）之类的代码，但我无法找到有关其用法的良好解释。你能指出我合适的资源吗？

   周杰伦

   回复
   1. 史蒂夫 说：
      2020年4月8日，下午5:13

      您必须记住，msg.payload是二进制文件。解码将二进制字符串转换为字符。在早期，这很容易，因为ascii是标准的，所以1个字节= 1个字符。
      看看这个
      http://www.steves-internet-guide.com/guide-data-character-encoding/
      显然，只有在知道msg.payload是一个字符串的情况下，才需要使用解码。如果它是mp3文件，则不需要解码，因为它以二进制格式保存。
      这有更多关于解码的详细信息
      https://www.tutorialspoint.com/python/string_decode.htm
      我已经使用了翻译，但是这里还是一个描述
      https://www.tutorialspoint.com/python/string_translate.htm
      希望对您有所帮助 史蒂夫（
      Rgds
      Steve）

      回复
3. 杰尔莫·戈尔茨 （GermoGörtz）说：
   九月27，2019在1:07下午

   还有一些问题：

   在代码中，我可以看到：
   logger = SQL_data_logger（db_file）logger.drop_table
   （“ logs”）
   logger.create_table（“ logs”，table_fields）

   而且我还看到：
   db_file =“ logs.db”
   Table_name =“ logs”

   这是否意味着将在python脚本所在的目录中创建数据库？

   回复
   1. 史蒂夫 说：
      九月27，2019在5:15下午

      是的，但是您只需要编辑脚本即可更改
      rgds
      steve的位置

      回复
4. 杰尔莫·戈尔茨 （GermoGörtz）说：
   九月27，2019在12:52下午

   如何开始？
   我以前从未使用过Python，但是我不想使用这部分将MQTT数据读入数据库。在下一步中，我将从那里导入SQL Server。
   我在Windows 10上安装了Python，但是当我调用脚本时得到：

   追溯（最近一次通话）：
   文件“ D：\ Git_Repositories \ Stromzaehler \ MQTT \ sql-logger \ sql-logger \ mqtt-data-logger-sql.py”，第9行，在
   导入paho.mqtt.client中作为mqtt
   ModuleNotFoundError：没有名为“ paho”的模块

   回复
   1. 杰尔莫·戈尔茨 （GermoGörtz）说：
      九月27，2019在3:40下午

      我在这里找到了一些东西：
      https
      :
      //pypi.org/project/paho-mqtt/但这也不能解决问题：pip install paho-mqtt
      它将被安装。当我尝试再次安装它时，我收到消息：
      已满足要求：c：\ users \ ggoer \ appdata \ local \ packages \ pythonsoftwarefoundation.python.3.7_qbz5n2kfra8p0 \ localcache \ local-packages \ python37 \ site中的paho-mqtt -程序包（1.4.0）

      但是错误仍然相同。据我了解，这不是脚本的特定问题。“ import”的语法类似于https://pypi.org/project/paho-mqtt/上的示例