一开始的想法是网关将数据传送到云平台,比如说阿里云或者OneNet再者就是其他的一些物联网云平台。这些云平台会让我省去不少时间,也能够实现及时的报警。但是考虑到后面自己要对数据分析,我便舍去了节省时间的方法。自己先搭建一个Mysql数据库,网上有很详细的教程,我按部就班的照做,最终数据库搭建成功了。
为了能让网关的数据成功传送到数据库,本来是打算直接用Arduino的Mysql库的,想到自己的Python挂过,就用Python来做的。因为之前做过Python连接数据库,所以在原来的基础上再加一个端口监听就行了,然后转发就行了。
直接贴代码了:
# -*- coding: utf-8 -*-
"""
Created on Tue Sep 24 17:23:50 2019
@author: Administrator
"""
import threading
import socket
import pymysql
encoding = 'utf-8'
BUFSIZE = 1024
conn = pymysql.Connect(
# 数据库的IP地址
host="1.1.1.1",
# 数据库用户名称
user="root",
# 数据库用户密码
password="000000",
# 数据库名称
db="example",
# 数据库端口名称
port=3306,
# 数据库的编码方式 注意是utf8
charset="utf8"
)
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
# a read thread, read data from remote
class Reader(threading.Thread):
def __init__(self, client):
threading.Thread.__init__(self)
self.client = client
def run(self):
while True:
data = self.client.recv(BUFSIZE)
if(data):
string = bytes.decode(data, encoding)
#print(string, end='')
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
try:
cursor.execute("insert into CareID001 (Door,Fire,Temp,Humi) values("+string+")")
except:
print("存入数据库失败")
print(string)
#print("insert into CareID001 (Door,Fire,Temp,Humi) values("+string+")")
conn.commit()
# 关闭游标
cursor.close()
#关闭连接
#conn.close()
else:
break
print("close:", self.client.getpeername())
def readline(self):
rec = self.inputs.readline()
if rec:
string = bytes.decode(rec, encoding)
if len(string)>2:
string = string[0:-2]
else:
string = ' '
else:
string = False
return string
# a listen thread, listen remote connect
# when a remote machine request to connect, it will create a read thread to handle
class Listener(threading.Thread):
def __init__(self, port):
threading.Thread.__init__(self)
self.port = port
self.sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
self.sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
self.sock.bind(("172.17.38.94", port))
self.sock.listen(0)
def run(self):
print("listener started")
while True:
client, cltadd = self.sock.accept()
Reader(client).start()
cltadd = cltadd
print("accept a connect")
lst = Listener(9090) # create a listen thread
lst.start() # then start