사용 환경 : mac

Untitled

‼️ DB는 앞서 만들어뒀던 테이블 이용

리눅스 MariaDB

DB에 컬럼 추가

Untitled

simul_sensors_mqtt_db.py

import datetime
import random
import time
import paho.mqtt.client as mqtt
import pymysql

# 메시가 들어올 때 처리 
def on_message(client, userdata, message):
    try:
        global mmmmmhh
        payload = message.payload.decode()
        data = eval(payload)

        db_connection = pymysql.connect(host=db_host,user=db_user,password=db_password,database=db_name)
        cursor = db_connection.cursor()

        timestamp = datetime.datetime.strptime(data['time'], '%Y-%m-%d %H:%M:%S')
        sensor_id = data['sensor_id']
        reading = data['reading']
        temperature = data['temperature']
        humidity = data['humidity']
        illuminance = data['illuminance']

        query=f"INSERT INTO {table_name} (sensor_id, reading, timestamp, temperature, humidity, illuminance) VALUES (%s,%s,%s,%s,%s,%s)"
        cursor.execute(query,(sensor_id, reading, timestamp, temperature, humidity, illuminance))
        db_connection.commit()

        db_connection.close()
        print(f"Received and stored: Time:{timestamp}, SID:{sensor_id}, Reading:{reading}, Temp:{temperature}'C, Hum    i:{humidity}%, Illuminance: {illuminance} lux")
    except Exception as e:
        print(f"Error:{e}")

def simulate_and_send(client):
    while True:
        current_time = datetime.datetime.now()
        sensor_id = random.randint(1,3)
        reading = random.uniform(20,30)
        temperature = random.uniform(20,30)
        humidity = random.uniform(40,60)
        illuminance = random.uniform(500,1000)

        data = {'time': current_time.strftime('%Y-%m-%d %H:%M:%S'), 
                'sensor_id': sensor_id,
                'reading': reading,
                'temperature': temperature,
                'humidity': humidity,
                'illuminance': illuminance}                                                                                              }
        client.publish(topic,str(data))

        print(f"Time:{current_time} - SID:{sensor_id}, Reading:{reading:.2f}, Temp:{temperature:.2f}'C, Humi:{humidity:.2f}%, Illuminanace:{illuminance:.2f} lux")
        time.sleep(1)

# 공용 브로커 주소, 포트, 브로커에 생성할 토픽
broker_address = 'broker.hivemq.com'
broker_port = 1883
topic = 'sensors'

# DB 연결정보
db_host = 'localhost'
db_user = 'scott'
db_password = 'tiger'
db_name = 'mydb'
table_name = 'SensorData'

# mqtt 객체 생성 및 연결
mqtt_client = mqtt.Client()
mqtt_client.connect(broker_address, broker_port)

# 토픽 구독 및 on_message 함수 적용
mqtt_client.subscribe(topic)
mqtt_client.on_message = on_message
mqtt_client.loop_start()
# 데이터 생성
simulate_and_send(mqtt_client)

Untitled

helloflask_mqtt_db.py

from flask import Flask, render_template, json, request
import pymysql.cursors

app = Flask(__name__)

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='scott',
                             password='tiger',
                             database='mydb',
                             cursorclass=pymysql.cursors.DictCursor,
                             charset='utf8'
                             )

# Route for displaying the sensor data
@app.route('/')
def display_sensor_data():
    with connection.cursor() as cursor:

        # Fetch data from SensorData table
        cursor.execute('SELECT * FROM SensorData ORDER BY id DESC LIMIT 100')
        sensor_data_data = cursor.fetchall()
        print(sensor_data_data)

        return render_template('index_mqtt.html', sensor_data=sensor_data_data)

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

index_mqtt.html

<!DOCTYPE html>
<html>
<head>
    <title>Sensor Data</title>
</head>
<body>
    <h1>Sensor Data</h1>
    <table border=1>
        <thead>
            <tr>
                <th>ID</th>
                <th>Sensor ID</th>
                <th>Reading</th>
                <th>Timestamp</th>
		<th>temperature</th>
		<th>humidity</th>
		<th>illuminance</th>
            </tr>
        </thead>
        <tbody>
            {% for data in sensor_data %}
                <tr>
	        {% for item, value in data.items(): %}
                    <td align=center>{{ value }}</td>
                {% endfor %}
                </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

실행

Untitled

Untitled