AWS云lamda实时判断IoTCore上传的数据并插入RDS中

1. 背景

1.1 简介

需要对实时上传的数据进行监测，如果发现有异常数据需要将该设备的机器信息写入到RDS中

1.2 流程

IoTCore编写路由到Lambda，Lambda对该条数据进行判断，如果有异常数据则将该条数据的机器信息写入到RDS中（频率：70s上传一次）

1.3 遇到的问题

如果某个机器一直的数据一直有问题，那这台机器的信息就一直被写入到数据库中，2天写入数据库70w条，这是一个很大的问题，于是进行了优化

2. 思考

怎么解决上述问题？

可以对这条告警数据的its（时间戳）进行解析，得出日期截止到天，再去数据库中找该设备的最新一条数据的its（时间戳），将这两个进行对比，如果差值大于或等于1那么就写入数据库

3. 实际解决

def lambda_handler(event, context):
    GatewayId = event['gSN']
    Ccn = event['ccn']
    UplodingTime = event['iTs']
    OilFilterInletPressure = event["OilFilterInletPressure"]
    OilFilterOutletPressure = event["OilFilterOutletPressure"]
    CompressorStatusWord=event["CompressorStatusWord"]
    status_02 = int(bin(CompressorStatusWord)[2:].zfill(10)[-3])
    InletVacuum = "InletVacuum"
    timeArray = time.localtime(UplodingTime)
    dayTime = time.strftime("%Y-%m-%d", timeArray)
    conn = pymysql.connect(
        host = '*********',
        user = '*********',
        password = "*********",
        database = '**********',
        port = int(3306))
    cursor = conn.cursor()
    unittype=""
    warning = 0
    In_Out = OilFilterInletPressure -OilFilterOutletPressure
    #--------R series系列 范围 160----12   -------------
    if (160 > In_Out > 12) and Ccn in ["23748957","23748965","24859480"] and status_02==1:
        warning=12
        insight4 = 4
        unittype = "Rseries"
        selectRowNums = cursor.execute("SELECT gsn,its,daytime FROM insight where gsn=%s order by daytime desc ", GatewayId)
        selectResultList = cursor.fetchall()
        if len(selectResultList) > 0 :
             timeArray = time.localtime(UplodingTime)
             otherStyleTime = time.strftime("%Y-%m-%d", timeArray)
             lasttime = datetime.datetime.strptime(selectResultList[0][2], "%Y-%m-%d")
             nowtime = datetime.datetime.strptime(otherStyleTime, "%Y-%m-%d")
             if (nowtime-lasttime).days>1 or(nowtime-lasttime).days==1:
                sql = 'insert into insight(ccn,gsn ,OilFilterOutletPressure,OilFilterInletPressure,its,insight,daytime,unittype,warning,CompressorStatusWord) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                result=cursor.execute(sql, (Ccn,GatewayId,OilFilterOutletPressure,OilFilterInletPressure,UplodingTime,insight4,dayTime,unittype,warning,CompressorStatusWord))
                conn.commit()
                if result == 1:
                    pass
                    return 'Rseries添加成功!'
                else:
                    return 'Rseries添加数据失败！'
        else:
            sql = 'insert into insight(ccn,gsn ,OilFilterOutletPressure,OilFilterInletPressure,its,insight,daytime,unittype,warning,CompressorStatusWord) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
            result=cursor.execute(sql, (Ccn,GatewayId,OilFilterOutletPressure,OilFilterInletPressure,UplodingTime,insight4,dayTime,unittype,warning,CompressorStatusWord))
            conn.commit()
            if result == 1:
                pass
                return 'Rseries添加成功!'
            else:
                return 'Rseries添加数据失败！'
        
    #--------end ----------------------------------------