def __init__(self, id, name): Sensor.__init__(self, id, name)
def readMeasurements( connection, fromTimestamp=None, toTimestamp=datetime.datetime.now(), topCount=None, sensorIDs=None ): # Liest alle Messungen entsprechend dem Filter aus der Datenbank und gibt sie als Sensorliste zurueck # fromTimestamp datetime: Von einem Eintragszeitpunkt # toTimestamp datetime: Bis zu einem Eintragszeitpunkt # topCount int: Maximale Anzahl an zurueckggebenen Messwerten pro Sensor # sensorIDs [int]: Filterung nach den uebergebenen Sensord-ID if not sensorIDs is None: if len(sensorIDs) == 0: return [] # WHERE Query----------------------------------- filterValues = {} filterQuery = [] whereExpression = "" if not fromTimestamp is None: filterValues["fromTimestamp"] = fromTimestamp filterQuery.append("m.timestamp >= :fromTimestamp") if not toTimestamp is None: filterValues["toTimestamp"] = toTimestamp filterQuery.append("m.timestamp <= :toTimestamp") if not topCount is None: filterValues["topCount"] = topCount tmpFilterForSubquery = "" if len(filterQuery) > 0: tmpFilterForSubquery = "AND " + " AND ".join([f.replace("m.", "") for f in filterQuery]) filterQuery.append( "m.timestamp IN ( " + "SELECT timestamp FROM measurements " + "WHERE sensorid = m.sensorid " + tmpFilterForSubquery + " ORDER BY timestamp DESC LIMIT :topCount)" ) if not sensorIDs is None: filterQuery.append("s.id IN (" + ",".join([str(id) for id in sensorIDs]) + ")") if len(filterQuery) > 0: whereExpression = "WHERE " + " AND ".join(filterQuery) # --------------------------------------------- cursor = connection.cursor() sql = ( "SELECT s.id as sensorid, s.name, m.timestamp, m.value, m.errorcode " + "FROM sensors s " + "LEFT JOIN measurements m ON s.id = m.sensorid " + whereExpression + " ORDER BY s.name, s.id, m.timestamp DESC" ) # print(sql) # print(filterValues) cursor.execute(sql, filterValues) sensors = [] cSensor = None for row in cursor: # Abrufe aller Messungen und zuweisung zu den Sensoren # Betrifft die aktuelle Zeile einen anderen Sensor wir dieser angelegt. if not Sensor.hasId(cSensor, row[0]): cSensor = Sensor(row[0], row[1]) sensors.append(cSensor) if not row[2] is None: cSensor.addMeasurement(MeasurementDAO(cSensor.getName(), row[2], row[3], row[4])) return sensors