def latest():
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sensorid = query_parameters.get('sensorid')
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
query = "SELECT * FROM " + table + " order by datetime DESC LIMIT 1;"
cursor.execute(query)
rows = cursor.fetchone()
# print(rows)
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def insertSensorData(thingName, thingTypeId, returnedList, thingsDict,
datetime):
tableName = thingsDict[thingName]
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
query = "INSERT into " + tableName + " (sensorId, datetime, typeId, "
for item in returnedList:
item = str(item)[1:-1]
item = re.sub(r"[\"`']", "", item)
fieldName, fieldValue = item.split(", ", 1)
query += "`" + fieldName + "`, "
query = query[:-2]
query += ") Values ('" + thingName + "', '" + datetime + "', '" + thingTypeId + "', "
for item in returnedList:
item = str(item)[1:-1]
item = re.sub(r"[\"]", "", item)
fieldName, fieldValue = item.split(", ", 1)
query += fieldValue + ", "
query = query[:-2]
query += ");"
print(query)
cursor.execute(query)
conn.commit()
except Exception as E:
print("insertSensorData error: ", E)
finally:
conn.close()
def insertSensorTable(query, label, sensorid):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
checkQuery = "SELECT table_name FROM information_schema.tables WHERE table_name = %s LIMIT 1;"
# print(checkQuery)
cursor.execute(checkQuery, label)
row = cursor.fetchone()
if row is None:
# print(query)
cursor.execute(query)
conn.commit()
print(
"(", sensorid, label, ")",
"sensorTable did not previously exist in the database has been added."
)
else:
print("(", sensorid, label, ")",
"sensorTable already exists in the database.")
except Exception as E:
# Removes sensor from sensors table in case of nonexisting thingTable. This is to avoid the system attempting -
# to get data for a sensor in the sensors table, while the thingTable for the sensor does not exist.
removeQuery = "DELETE FROM sensors where id = %s;"
cursor.execute(removeQuery, sensorid)
conn.commit()
print("insertSensorTable error, sensorid:", sensorid, E)
finally:
conn.close()
def byTimeperiod(value, type):
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sensorid = query_parameters.get('sensorid')
startdate = query_parameters.get('startdate')
enddate = query_parameters.get('enddate')
groupby = query_parameters.get('groupby')
if not (sensorid and startdate and enddate):
return not_found(404)
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
to_filter = []
query = "SELECT ROUND(AVG(" + value + "), 1) as " + type + ", min(" + value + ") as min, max(" + value + ") as max, datetime FROM " + table + " WHERE (datetime BETWEEN %s AND %s) "
if groupby == "year":
query += "GROUP BY year(datetime)"
elif groupby == "month":
query += "GROUP BY year(datetime), month(datetime)"
elif groupby == "week":
query += "GROUP BY year(datetime), month(datetime), week(datetime)"
elif groupby == "day":
query += "GROUP BY year(datetime), month(datetime), week(datetime), day(datetime)"
elif groupby == "hour":
query += "GROUP BY year(datetime), month(datetime), week(datetime), day(datetime), hour(datetime)"
query += ";"
to_filter.append(startdate)
to_filter.append(enddate)
# print(query)
cursor.execute(query, to_filter)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def getSensors():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM sensors;")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def byDate(value, type):
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
year = query_parameters.get('year')
month = query_parameters.get('month')
day = query_parameters.get('day')
sensorid = query_parameters.get('sensorid')
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
to_filter = []
query = "SELECT ROUND(AVG(" + value + "), 1) as " + type + ", min(" + value + ") as min, max(" + value + ") as max, week(datetime) as week, datetime FROM " + table + " WHERE year(datetime) = %s"
to_filter.append(year)
if month:
query += " AND month(datetime) = %s"
to_filter.append(month)
if day:
query += " AND day(datetime) = %s GROUP BY hour(datetime);"
to_filter.append(day)
else:
query += " GROUP BY day(datetime);"
else:
query += " GROUP BY month(datetime);"
if not (year):
return not_found(404)
# print(query)
cursor.execute(query, to_filter)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def returnSensorIds():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
query = "SELECT id FROM sensors;"
cursor.execute(query)
rows = cursor.fetchall()
if len(rows) != 0:
return rows
else:
print(
"returnSensorIds: No sensors exist in the database table: sensors"
)
sys.exit()
except Exception as E:
print("returnSensorIds error: ", E)
finally:
conn.close()
def addSensors(id, label, description, thingType, latlng):
try:
label = label.lower()
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
query = "select id from sensors where id = %s;"
cursor.execute(query, id)
row = cursor.fetchone()
if row is None:
query = "INSERT into sensors (id, label, description, typeId, coordinates) Values (%s, %s, %s, %s, %s);"
cursor.execute(query, (id, label, description, thingType, latlng))
conn.commit()
# else:
# print("(", id, label, description, thingType, ")", "thing already exists in the database.")
except Exception as E:
print("addSensors error: ", E)
finally:
conn.close()
def rawDataDate():
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
year = query_parameters.get('year')
month = query_parameters.get('month')
day = query_parameters.get('day')
sensorid = query_parameters.get('sensorid')
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
to_filter = []
query = "SELECT * FROM " + table + " WHERE year(datetime) = %s"
to_filter.append(year)
if month:
query += " AND month(datetime) = %s"
to_filter.append(month)
if day:
query += " AND day(datetime) = %s;"
to_filter.append(day)
if not (year and sensorid):
return not_found(404)
# print(query)
cursor.execute(query, to_filter)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def insertSensorTypeTable(query, label, id, existingResourcesForType):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
checkQuery = "select thingTypeLabel from thingType where thingTypeId = %s;"
cursor.execute(checkQuery, id)
row = cursor.fetchone()
if row is None:
# print(query)
cursor.execute(query)
conn.commit()
print(
"(", id, label, ")",
"SensorTypeTable did not previously exist in the database has been added."
)
else:
label = row["thingTypeLabel"]
compare = "describe " + label + ";"
cursor.execute(compare)
comparison = cursor.fetchall()
resourcearray = ["long", "double", "float", "int"]
comparisonlist = []
for item in comparison:
comparisonlist.append(item["Field"])
for item in existingResourcesForType:
fieldname = re.sub(r"[-()\"#/@;:<>{}`'+=~|!?]", "", item[0])
if fieldname not in comparisonlist:
altertable = "alter table " + label + " add " + fieldname + " "
if item[1] in resourcearray:
altertable += item[1] + ";"
else:
altertable += "varchar(100);"
print(altertable)
cursor.execute(altertable)
conn.commit()
print("(", id, label, ")",
"SensorTypeTable already exists in the database.")
except Exception as E:
print("insertSensorTypeTable error: ", E)
finally:
conn.close()
def addSensorTypes(id, label, description):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
query = "select thingTypeId, thingTypeLabel from thingType where thingTypeId = %s;"
cursor.execute(query, id)
row = cursor.fetchone()
if row is None:
query = "INSERT into thingType (thingTypeId, thingTypeLabel, thingTypeDescription) Values (%s, %s, %s);"
cursor.execute(query, (id, label, description))
conn.commit()
print(
"(", id, label, ")",
"Sensor type did not previously exist in the database has been added."
)
else:
print("(", id, label, ")",
"Sensor type already exists in the database.")
except Exception as E:
print("addSensorTypes error: ", E)
finally:
conn.close()
def byRecentYear(value, type):
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sensorid = query_parameters.get('sensorid')
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
cursor.execute(
"SELECT ROUND(AVG(" + value + "), 1) as " + type + ", min(" + value + ") as min, max(" + value + ") as max, monthname(datetime) as month, date(datetime) as date FROM " + table + " WHERE DATE_SUB(datetime, INTERVAL 1 WEEK) And datetime > DATE_SUB(NOW(), INTERVAL 13 month) GROUP BY year(datetime), month(datetime);")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def rawDataTimeperiod():
try:
query_parameters = request.args
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
sensorid = query_parameters.get('sensorid')
startdate = query_parameters.get('startdate')
enddate = query_parameters.get('enddate')
if not (sensorid and startdate and enddate):
return not_found(404)
findtable = "SELECT label FROM sensors where id = %s;"
cursor.execute(findtable, sensorid)
table = cursor.fetchone()
table = table["label"]
to_filter = []
query = "SELECT * FROM " + table + " WHERE (datetime BETWEEN %s AND %s);"
to_filter.append(startdate)
to_filter.append(enddate)
# print(query)
cursor.execute(query, to_filter)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
