def GetAvgEnviroInfoByDay(RoomId): db_results = db.query( "SELECT Avg(temp) 'AvgTemp', time from enviro_info where roomId = {} group by DAY(time)" .format(RoomId)) humid = [] temp = [] for x in db_results: temp.append([x['time'], x['AvgTemp']]) return data_to_json(temp)
def GetTop10EnviroInfo(RoomId): db_results = db.query( "SELECT ei.temp, ei.humidity, ei.time FROM enviro_info ei where ei.roomId = {} order by ei.time desc limit 10" .format(RoomId)) results_flipped = db_results[::-1] humid = [] temp = [] for x in results_flipped: humid.append([x['time'], x['humidity']]) temp.append([x['time'], x['temp']]) return data_to_json({"temp": temp, "humidity": humid})
def GetLatestEnviroInfo(RoomId): db_results = db.query( "SELECT ei.temp, ei.humidity, ei.light_value, ei.time FROM enviro_info ei where ei.roomId = {} order by ei.time desc limit 1" .format(RoomId)) if db_results == None or len(db_results) <= 0: return None result = db_results[0] return data_to_json({ "time": result['time'], "temp": result['temp'], "humidity": result['humidity'], "light": result['light_value'] })
def GetAllAccessRights(): return data_to_json( db.query( "SELECT ac.Id, u.Username,u.Id UserId, r.RoomName,r.Id RoomId from access_rights ac " + "JOIN users u ON u.Id = ac.UserId " + "JOIN rooms r ON r.Id = ac.RoomId"))
def getUserAccessRights(UserId): return data_to_json( db.query("SELECT r.RoomName from access_rights ac " + "JOIN rooms r ON r.Id = ac.RoomId where ac.UserId = {}". format(UserId)))
def GetAllRoomsJSON(): return data_to_json(db.query("select * from rooms"))
logger = logging.getLogger("AWSIoTPythonSDK.core") logger.setLevel(logging.DEBUG) streamHandler = logging.StreamHandler() formatter = logging.Formatter( '%(asctime)s - %(name)s - %(levelname)s - %(message)s') streamHandler.setFormatter(formatter) logger.addHandler(streamHandler) aws = AwsIot('room_updater' + str(room.Id), -1) adc = MCP3008(channel=0) while True: humidity, temperature = Adafruit_DHT.read_retry(11, 4) print('Temp: {:.1f} C'.format(temperature)) print('Humidity: {:.1f}'.format(humidity)) light = adc.value * 1024 print('light_value: {}'.format(light)) en_info = EnviroInfo(roomId=room.Id, temp=temperature, humidity=humidity, light_value=light) en_info.WriteToDb() message = {} message["time"] = datetime.datetime.now() message["temp"] = temperature message["humid"] = humidity aws.my_rpi.publish("rooms/" + str(room.Id) + "/enviroData", json.dumps(data_to_json(message)), 0) sleep(5)