---
# System prepended metadata

title: MQTT一條龍到GUI
tags: [mqtt]

---

# MQTT一條龍到GUI

###### tags: `mqtt`

Copyright 2021, [月下麒麟 YMont](https://hackmd.io/@YMont/note-catalog)

---

## 緣起
何謂MQTT，請參照[wikipedia](https://zh.wikipedia.org/wiki/MQTT)
串接MQTT的publish與subscribe，
中間利用backend將資料寫入database，
最後以GUI的方式呈現，這樣就能方便的觀看收集到的資訊。

如下為應用流程圖:
![](https://i.imgur.com/IlstK71.png)

補充:
因有將實作場景比例縮小，故Broker就以Localhost單機取代，
否則，實際場景皆會有獨立的分工的Server，
以下實際程式碼架構上有微調，故參照時需稍注意。


## Code
### Create SQLite Database & MQTT Subscribe
```mqtt_combo.py```
```python=
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os
import paho.mqtt.client as mqtt

# create an instance in the flask frame
app = Flask(__name__)

# SQLite datebase link
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = r'sqlite:///D:mqtt.db'
app.config['SECRET_KEY'] = os.urandom(10)

# connect app with SQLAchemy(SQLite)
db = SQLAlchemy(app)

# create data list and column
class DBFrame(db.Model):
    __tablename__ = "testname"
    id          = db.Column(db.Integer, primary_key=True)
    name        = db.Column(db.String(60))
    context     = db.Column(db.String(200))

    def __init__(self, name, context):
        self.name = name
        self.context = context
        
    def __repr__(self):
        return '< {} , {} , {}  >'.format(self.id,
                                        self.name,
                                        self.context)

# MQTT Subscribe
def subscribe():
    def on_connect(client, userdata, flags, rc):
        print("Connected with result code "+str(rc))
        client.subscribe("Try/MQTT")

    def on_message(client, userdata, msg):
        print(msg.topic+" "+ msg.payload.decode('utf-8'))
        msgg1 = msg.topic+" "
        msgg2 = msg.payload.decode('utf-8')
        item = DBFrame(msgg1,msgg2)
        db.session.add(item)
        db.session.commit()
    
    client = mqtt.Client()
    client.on_connect = on_connect
    client.on_message = on_message
    client.username_pw_set("test","123456")
    client.connect("localhost", 1883)
    client.loop_forever()

if __name__ == "__main__":
    db.create_all()
    subscribe()
```

### Flask App
```app.py```
```python=
from flask import Flask
from flask import render_template
import sqlite3

app = Flask(__name__)

@app.route('/index/mqtt')
def index():
    conn = sqlite3.connect(r'D:\mqtt.db')
    cur = conn.cursor()
    
    cur.execute('SELECT name, context FROM testname ORDER BY id ASC')
    datas = cur.fetchall()
    return render_template("index.html",data = datas)

if __name__ == "__main__":
    app.run(debug=True)
```

### MQTT Publish 
```publish.py```
```python=
import paho.mqtt.client as mqtt
import random
import json  
import datetime 
import time

# setting date and time format
ISOTIMEFORMAT = '%m/%d %H:%M:%S'

# connection setting
# initialize local program
client = mqtt.Client()

# setting login username and password
client.username_pw_set("test","123456")

# setting connection information(IP, Port)
client.connect("localhost", 1883)

while True:
    t0 = random.randint(0,30)
    t = datetime.datetime.now().strftime(ISOTIMEFORMAT)
    payload = {'Temperature' : t0 , 'Time' : t}
    print (json.dumps(payload))
    #要發布的主題和內容
    client.publish("Try/MQTT", json.dumps(payload))
    time.sleep(5)
```

### HTML
```index.html```
```javascript=
<!DOCTYPE html>
<html lang="zh_TW">
    <head>
        <meta charset="utf-8">
        <title>MQTT</title>
        <style type="text/css"></style>
        <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    </head>
    <body>
        <h1>MQTT's Data</h1>
        <div>
            Data:<br/>
            {% for var_list in data %}
                <li>{{ var_list }}</li>
            {% endfor %}
        </div>
    </body>
    <script type="text/javascript">
        setTimeout(function(){
            window.location.reload(6);
        },10000);
    </script>
</html>
```
### GUI presentation

* 建立DB & MQTT Subscribe
保持訂閱狀態(wait Publish)
```python mqtt_combo.py```
![](https://i.imgur.com/zULuXIH.png)

* 啟用Flask App
```python app.py```
![](https://i.imgur.com/QSEhNjN.png)

* 打開網頁，此時尚未開啟Publish，故Data裡面是沒有值的
```http://127.0.0.1:5000/index/mqtt```
![](https://i.imgur.com/BiAHcL9.png)

* 開啟Publish
```python publish.py```
![](https://i.imgur.com/8WYPzso.png)

* 網頁自動每10秒刷新一次，完成GUI顯示MQTT捕獲的值
![](https://i.imgur.com/jX0PEZS.png)



