说明:本代码经本人测试,stm89c51等开发板传感器采集数据发送到串口,实现读取并写入数据库,不懂之处大家留言,看见会及时回复大家。
1:读取串口数据写入csv文件:
#!/usr/bin/python3
from PyQt5.QtCore import QTimer, QByteArray, QIODevice
from PyQt5.QtSerialPort import QSerialPort, QSerialPortInfo
from PyQt5.QtWidgets import *
import sys
from SaveData import *
class SerialWidget(QWidget):
def __init__(self, parent=None):
super().__init__(parent)
self.__data = QByteArray()
self.__serial = QSerialPort()
self.__timer = QTimer(self)
for info in QSerialPortInfo.availablePorts():
if info.description() == "USB-SERIAL CH340":
self.__serial = QSerialPort(info)
print(self.__serial.portName())
break
self.__serial.readyRead.connect(self.__read_data)
self.__timer.timeout.connect(self.__timer_update_com)
self.__temperature = 0
self.__humidity = 0
self.__co2 = 0
self.__tvoc = 0
self.__pm25 = 0
self.__pm10 = 0
self.__o2 = 0
if self.__serial.open(QIODevice.ReadWrite):
print("open success")
else:
print("open fail")
self.__auto_save_thread = AutoSave(self)
self.__auto_save_thread.start()
def closeEvent(self, QCloseEvent):
self.__auto_save_thread.kill()
super().closeEvent(QCloseEvent)
def __read_data(self):
self.__timer.start(40)
self.__data.append(self.__serial.readAll())
def __timer_update_com(self):
self.__timer.stop()
length = self.__data.length()
i = 0
while i < length:
num = ord(self.__data[i])
if num == 116:
num = ord(self.__data[i + 1])
self.__temperature = num
i += 1
print("temperature:" + str(self.__temperature))
elif num == 116 - ord('t') + ord('h'):
num = ord(self.__data[i + 1])
self.__humidity = num
i += 1
print("humidity:" + str(self.__humidity))
elif num == 116 - ord('t') + ord('c'):
num = ord(self.__data[i + 1]) * 258 + ord(self.__data[i + 2])
self.__co2 = num
i += 2
print("CO2:" + str(self.__co2))
num = ord(self.__data[i + 1]) * 258 + ord(self.__data[i + 2])
self.__tvoc = num
i += 2
print("TVOC:" + str(self.__tvoc))
elif num == 116 - ord('t') + ord('o'):
num = ord(self.__data[i + 1]) * 258 + ord(self.__data[i + 2])
num = num * (3300 / 4096) / 100
num = round(num, 3)
self.__o2 = num
i += 2
print("O2:" + str(self.__o2) + "%")
elif num == 116 - ord('t') + ord('p'):
num = ord(self.__data[i + 1]) * 258 + ord(self.__data[i + 2])
num /= 10
self.__pm25 = num
i += 2
print("PM2.5:" + str(self.__pm25))
num = ord(self.__data[i + 1]) * 258 + ord(self.__data[i + 2])
num /= 10
self.__pm10 = num
i += 2
print("PM10:" + str(self.__pm10))
i += 1
self.__data.clear()
def get_temperature(self):
return self.__temperature
def get_humidity(self):
return self.__humidity
def get_co2(self):
return self.__co2
def get_tvoc(self):
return self.__tvoc
def get_pm25(self):
return self.__pm25
def get_pm10(self):
return self.__pm10
def get_o2(self):
return self.__o2
if __name__ == '__main__':
app = QApplication(sys.argv)
my_serial = SerialWidget()
my_serial.show()
sys.exit(app.exec_())
数据保存:
#!/usr/bin/python3
import threading
import time
import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font
class SaveData:
def __init__(self, my_serial):
self.serial = my_serial
self.__dirpath = "../Data/"
if not os.path.exists(self.__dirpath):
os.makedirs(self.__dirpath)
@staticmethod
def check_path(filepath):
if not os.path.exists(filepath):
new_workbook = Workbook()
new_workbook.save(filepath)
@staticmethod
def insert_headings(current_sheet):
columns = ["Time", "Temperature", "Humidity", "CO2", "TVOC", "O2", "PM2.5", "PM10"]
for i in range(8):
cell = current_sheet.cell(column=i + 1, row=1, value=columns[i])
cell.font = Font(size=12, bold=True)
def save_to_local(self):
localtime = time.localtime(time.time())
filepath = self.__dirpath + time.strftime("%Y_%m", localtime) + ".xlsx"
self.check_path(filepath)
current_workbook = load_workbook(filepath)
sheetname = time.strftime("%m-%d", localtime)
current_sheet = current_workbook.active
for sheet in current_workbook:
if sheet.title == "Sheet":
sheet.title = sheetname
self.insert_headings(sheet)
current_workbook.save(filepath)
current_sheet = sheet
break
elif sheet.title == sheetname:
current_sheet = sheet
break
if current_sheet.title != sheetname:
current_sheet = current_workbook.create_sheet(sheetname)
self.insert_headings(current_sheet)
current_workbook.save(filepath)
current_time = time.strftime("%H:%M", localtime)
temperature = self.serial.get_temperature()
humidity = self.serial.get_humidity()
co2 = self.serial.get_co2()
tvoc = self.serial.get_tvoc()
o2 = self.serial.get_o2()
pm25 = self.serial.get_pm25()
pm10 = self.serial.get_pm10()
new_data = [current_time, temperature, humidity, co2, tvoc, o2, pm25, pm10]
current_sheet.append(new_data)
current_workbook.save(filepath)
class AutoSave(threading.Thread):
def __init__(self, my_serial):
threading.Thread.__init__(self)
self.name = "auto_save_data"
self.serial = my_serial
self.__save_data = SaveData(self.serial)
self.__saved = False
self.__closed = False
def run(self):
while True:
if self.__closed:
break
localtime = time.localtime(time.time())
if localtime.tm_min == 0 and not self.__saved:
self.__save_data.save_to_local()
self.__saved = True
elif localtime.tm_min != 0:
self.__saved = False
def kill(self):
self.__closed = True
if __name__ == '__main__':
my_thread = AutoSave()
my_thread.start()
my_thread.kill()
2:读取串口数据写入数据库:
#备注 代码根据自己数据库及存储格式修改
import serial
import pymysql # 导入pymysql包
# import easygui
log = 0
ser = serial.Serial()
ser.baudrate = 9600 # 设置波特率(这里使用的是stc89c52)
ser.port = 'COM12' # 端口是COM3
print(ser)
ser.open() # 打开串口
print(ser.is_open) # 检验串口是否打开
db = pymysql.connect("localhost", "root", "", "test") # 打开数据库,配置数据库
cursor = db.cursor() # 数据库操作
cursor.execute("DROP TABLE IF EXISTS Monitor_Data") # 如果存在表则重新创建
creatTab = """CREATE TABLE Monitor_Data( # 创建表
LOG_ID INT NOT NULL,
temp CHAR(50),
temp_value CHAR(50),
guang CHAR(50),
guang_value CHAR(50)
)"""
cursor.execute(creatTab) # 执行数据库语句
while (1):
# Yes_or_No = easygui.buttonbox("是否良品?", choices=[ 'Yes', 'No', '退出' ]) # 提供简易UI
# if Yes_or_No == '退出': break
# if Yes_or_No == 'Yes':
# demo = b"2" # 传入2的ASCII码 这里用b+str强制转换
# else:
# demo = b"1" # 传入1的ASCII码 这里用b+str强制转换
# ser.write()
s = ser.readline()
log += 1 # 传输次数记录+1
data_pre = str(s) # 强制用字符串格式
data = data_pre[ 2:-2 ] # 取部分数据
did = data_pre[ 2:6 ] # 分类取有效数据
did1 = data_pre[ 8:12 ] # 分类取有效数据
did2 = data_pre[ 13: 18 ] # 分类取有效数据
did3 = data_pre[ 20:25 ] # 分类取有效数据
print(log, did, did1, did2, did3)
sql = "INSERT INTO Monitor_Data(LOG_ID,temp,temp_value,guang,guang_value)VALUES('%d','%s','%s','%s','%s')" % (
log, did, did1, did2, did3) # 存入数据库
cursor.execute(sql) # 执行数据库语句