This repository has been archived by the owner on Aug 2, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
MQttMySQL.py
105 lines (96 loc) · 3.39 KB
/
MQttMySQL.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
import paho.mqtt.client as mqtt
import json
import MySQLdb
def on_connect(client, userdata, flags, rc):
print("Connected with result code "+str(rc))
client.subscribe("GIOT-GW/UL/1C497B43217A")
def on_message(client, userdata, msg):
print(msg.topic+" "+str(msg.payload), "\n")
message_ = msg.payload.decode("utf-8")
print(type(message_))
print(message_, "\n")
if message_ != "close" :
try:
json_data = json.loads(message_)[0]
print(type(json_data))
print(json_data)
# print(type(json_data['channel']))
# print(json_data['channel'])
# print(type(json_data['sf']))
# print(json_data['sf'])
# print(type(json_data['time']))
# print(json_data['time'])
# print(type(json_data['gwip']))
# print(json_data['gwip'])
# print(type(json_data['gwid']))
# print(json_data['gwid'])
# print(type(json_data['repeater']))
# print(json_data['repeater'])
# print(type(json_data['systype']))
# print(json_data['systype'])
# print(type(json_data['rssi']))
# print(json_data['rssi'])
# print(type(json_data['snr']))
# print(json_data['snr'])
# print(type(json_data['snr_max']))
# print(json_data['snr_max'])
# print(type(json_data['snr_min']))
# print(json_data['snr_min'])
# print(type(json_data['macAddr']))
# print(json_data['macAddr'])
# print(type(json_data['data']))
# print(json_data['data'])
# print(type(json_data['frameCnt']))
# print(json_data['frameCnt'])
# print(type(json_data['fport']))
# print(json_data['fport'])
insert_stmt = (
"INSERT INTO iot_test (channel, sf, time, gwip, gwid, repeater,systype,rssi,snr,snr_max,snr_min,macAddr,data,frameCnt,fport)"
"VALUES (%(channel)s, %(sf)s, %(time)s, %(gwip)s, %(gwid)s, %(repeater)s, %(systype)s, %(rssi)s, %(snr)s, %(snr_max)s, %(snr_min)s, %(macAddr)s, %(data)s, %(frameCnt)s, %(fport)s)"
)
data = {
"channel" : json_data['channel'],
"sf" : json_data['sf'],
"time" : json_data['time'],
"gwip" : json_data['gwip'],
"gwid" : json_data['gwid'],
"repeater" : json_data['repeater'],
"systype" : json_data['systype'],
"rssi" : json_data['rssi'],
"snr" : json_data['snr'],
"snr_max" : json_data['snr_max'],
"snr_min" : json_data['snr_min'],
"macAddr" : json_data['macAddr'],
"data" : json_data['data'],
"frameCnt" : json_data['frameCnt'],
"fport" : json_data['fport']
}
# data = (\
# int(json_data['channel']), \
# int(json_data['sf']), \
# str(json_data['time']), \
# str(json_data['gwip']), \
# str(json_data['gwid']), \
# str(json_data['repeater']), \
# int(json_data['systype']), \
# float(json_data['rssi']), \
# float(json_data['snr']), \
# float(json_data['snr_max']), \
# float(json_data['snr_min']), \
# str(json_data['macAddr']), \
# str(json_data['data']), \
# int(json_data['frameCnt']), \
# int(json_data['fport']) \
# )
db = MySQLdb.connect(host="localhost", user="phpmyadmin", passwd="1122", db="mqtt_db", charset="utf8")
cursor = db.cursor()
cursor.execute(insert_stmt, data)
db.commit()
db.close()
except BaseException :
print("json decoding error!!!")
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.connect("167.99.224.125", 1883, 60)
client.loop_forever()