import serial
import serial.tools.list_ports
import re
import pymysql
import time
import datetime


def connectSql():
    conn = pymysql.connect(host='rm-bp11fi17g4n450g12io.mysql.rds.aliyuncs.com'  # 连接名称，默认127.0.0.1
                           , user='ro9906980am'  # 用户名
                           , passwd='ro9906980AM'  # 密码
                           , port=3306  # 端口，默认为3306
                           , db='smart_lamp'  # 数据库名称
                           , charset='utf8'  # 字符编码
                           )
    return conn


def runSql(cur, sql):
    cur.execute(sql)  # 执行SQL语句


def initSer():
    portx = "COM8"
    # 波特率，标准值之一：50,75,110,134,150,200,300,600,1200,1800,2400,4800,9600,19200,38400,57600,115200
    bps = 115200
    # 超时设置,None：永远等待操作，0为立即返回请求结果，其他值为等待超时时间(单位为秒）
    timex = None
    # 打开串口，并得到串口对象
    ser = serial.Serial(portx, bps, timeout=timex)
    return ser


ser = initSer()
# 连接数据库
conn = connectSql()
cur = conn.cursor()
flag = 0
listArr = []
while True:
    msg = ser.readline()
    # s = msg.encode('unicode_escape').decode('utf-8').replace('\\x','%')
    # un = parse.unquote(s)
    res = msg.decode('gbk', 'replace')
    print(res)
    if res[0] >= '0' and res[0] <= '3':
        listArr.append(re.findall(r"X(.+?)Y", res)[0])
        flag += 1
    if (flag == 4):
        if (int(listArr[0]) > 0 and int(listArr[0]) < 50):
            isOpen = '1'
        else:
            isOpen = '0'
        timeNow = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sql = """insert into STM32 (stm32_battery,stm32_light_intensity,stm32_pm25,stm32_pm10,stm32_time,stm32_light_status) values (%s,%s,%s,%s,%s,%s)"""
        values = (listArr[0], listArr[1], listArr[2],
                  listArr[3], timeNow, isOpen)
        print(values)
        cur.execute(sql, values)
        conn.commit()
        listArr = []
        flag = 0
        time.sleep(1)