class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.influx = InfluxDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.influx.init_cfg(cfg)
self._con = self.mysql.get_connection()
self.query.init_db(self._con, self.influx.get_client())
def getQuery(self):
return self.query
def close(self):
return self.mysql.close()
def getallMAC(self, table):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT distinct("MAC") as MAC FROM "{}";'.format(
escape(table)))
return result.raw
def getallSensor(self, table, mac):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery('SELECT s,topic FROM "{}" WHERE MAC=$mac;'.format(
escape(table)),
bind_params=param)
return result.raw
def getallTopic(self, table, mac):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT topic,MAC,status FROM "{}"'.format(
escape(table)))
for sensor in result.raw["series"][0]["values"]:
smac = sensor[2]
topic = sensor[1]
if (smac == mac):
return ['topic', topic, 'MAC', smac]
def getLastCT(self, sid, _type="ct"):
infQuery = self.query.getInfQuery()
param = {"s": sid}
result = infQuery('SELECT a FROM "{}" WHERE s=$s LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
def getLastPIR(self, mac, _type="pir"):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery(
'SELECT status FROM "{}" WHERE MAC=$mac LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.mongo = MongoDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.mongo.init_cfg(cfg)
self._con = self.mysql.get_connection()
self._client = self.mongo.get_client()
self.query.init_db(self._con, self._client)
self._db = self._client[cfg.MONGODB_COLLECTION]
def getQuery(self):
return self.query
def getallMAC(self, table): #/
allmac = self._db[table].find().distinct("message.MAC")
return allmac
def getallSensor(self, table, mac): #/
result = self._db[table].find(
{"$and": [{
"message.MAC": mac
}, {
"message.s": {
"$exists": True
}
}]})
alltopic = result.distinct("topic")
allnum = result.distinct("message.s")
res = []
for s in allnum:
for topic in alltopic:
res.append({"s": s, "topic": topic})
return res
def getallTopic(self, table, mac): #/
result = self._db[table].find_one(
{"$or": [{
"message.MAC": mac
}, {
"message.s": mac
}]})
return ['topic', result["topic"], 'MAC', result["message"]["MAC"]]
def getLastVAL(self, sid, mac, _type="ct"):
createdAt = 0
res = None
query = {"message.MAC": mac}
if (_type.split("_") == "dm"):
query["message.s"] = sid
rawCT = self._db[_type].find(query).sort("created_at", -1).limit(1)
for CT in rawCT:
if (CT["created_at"] > createdAt):
res = CT
createdAt = CT["created_at"]
if (_type.split("_")[0] == "dm"):
return (res["message"]["AL1"] + res["message"]["AL2"] +
res["message"]["AL3"]) / 3
return res["message"]["a"]
def getLastPIR(self, mac, _type="pir"):
createdAt = 0
res = {}
for t in self.getTable({"sensor_type": _type}):
rawPIR = self._db[t].find({
"message.MAC": mac
}).sort("created_at", -1).limit(1)
for PIR in rawPIR:
if (PIR["created_at"] > createdAt):
res = PIR
createdAt = PIR["created_at"]
return res
def getTable(self, devFind={}): #/
res = []
for _type in self._db["iot_type"].find(devFind):
name = _type["sensor_type"]
if (_type["device_type"] != ''):
name = name + "_" + _type["device_type"]
res.append(name)
return res
