def insertPredictedData(self,id,current): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "UPDATE `future_data` SET current_output ="+str(current)+" WHERE id="+str(id)+"" ret = cursor.execute(query) cnx.commit() cursor.close() cnx.close() return ret
def getfurturedata(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT id,description, temperature FROM `future_data` WHERE current_output=1.00 OR current_output IS NULL LIMIT 700" lines = cursor.execute(query) #execute the query data = cursor.fetchall() cursor.close() cnx.close() return data
def getfurturedata(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT description,wind,DATE(weather_date) as wdate FROM `future_data` WHERE weather_date >= CURDATE() AND weather_date< CURDATE()+6" lines = cursor.execute(query) #execute the query data = cursor.fetchall() cursor.close() cnx.close() return data
def previousandnext5DaysfullCurrent(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT weather_date, SUM(current_output)*10800/1000000 FROM (SELECT CAST(weather_date AS DATE) AS weather_date,current_output FROM future_data WHERE city = 'Heidelberg'AND weather_date > CURDATE()+1 AND weather_date< CURDATE()+6 )t1 GROUP BY weather_date" lines = cursor.execute(query) data = cursor.fetchall() cursor.close() cnx.close() return data
def getTrainData(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT AS suntime,description, temperature,current_output FROM `current_data` WHERE current_output>=0 ORDER BY id DESC LIMIT 100" #actual SQL statement to be executed lines = cursor.execute(query) #execute the query data = cursor.fetchall() cursor.close() cnx.close() return data
def Nnext5DaysWeatherdesc(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "Select description, count(description) as num from (select * from future_data where city = 'Heidelberg' order by curr_timestamp desc, weather_date asc limit 40) t1;" lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = data[0] cursor.close() cnx.close() return temp
def Nnext5DaysWeather(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #receiving details about next 5 day's weather query = "SELECT avg(temperature - 273.15) as result from (select * from future_data where city = 'Heidelberg' order by curr_timestamp desc, weather_date asc limit 40) t1;" # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = float("{0:.2f}".format(float(data[0]))) cursor.close() cnx.close() return temp
def tillNowCurrent(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #computing total current received today till the present time query = "SELECT sum(current_output*900/1000000) as result from current_data where curr_timestamp < DATE_ADD(now(), INTERVAL 2 HOUR) and curr_timestamp >= curdate() and city = 'Heidelberg'; " # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = float("{0:.2f}".format(float(data[0]))) cursor.close() cnx.close() return temp
def todayWeatherDesc(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #receiveing description about today's weather query = "SELECT description as result from current_data where city = 'Heidelberg' and curr_timestamp < DATE_ADD(now(), INTERVAL 2 HOUR) and curr_timestamp >= curdate() group by description order by count(description) desc limit 1;" lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = data[0] cursor.close() cnx.close() return temp
def todayWeather(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #receiving details about today's weather query = "SELECT avg(temperature - 273.15) as result from current_data where curr_timestamp < DATE_ADD(now(), INTERVAL 2 HOUR) and curr_timestamp >= curdate() and city = 'Heidelberg';" # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = float("{0:.2f}".format(float(data[0]))) cursor.close() cnx.close() return temp
def pastYesterday(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #computing total current received yesterday query = "SELECT sum(current_output*900/1000000) as result from current_data where city = 'Heidelberg' and curr_timestamp >= curdate()-1 and curr_timestamp <curdate(); " # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = float("{0:.2f}".format(float(data[0]))) cursor.close() cnx.close() return temp
def tomorrowWeatherDesc(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #receiving description about tomorrow's weather query = "Select description, count(description) as num from (select * from future_data where city = 'Heidelberg' and weather_date >= (curdate()+ 1) order by curr_timestamp desc, weather_date asc limit 8) t1 group by description order by count(description) desc limit 1;" lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = data[0] cursor.close() cnx.close() return temp
def last5DaysfullCurrent(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT weather_date, SUM(current_output)FROM (SELECT CAST(curr_timestamp AS DATE) AS weather_date,current_output FROM current_data WHERE city = 'Heidelberg' AND curr_timestamp > CURDATE()-5 AND curr_timestamp< CURDATE()+1)) t1 GROUP BY weather_date;" #actual SQL statement to be executed- 18000 * config.powerFactor/ 1000000 lines = cursor.execute(query) # execute the query data = cursor.fetchall() #for row in data: # print(row) #temp = float("{0:.2f}".format(float(data1))) cursor.close() cnx.close() return data
def nowWeather(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #receiving details about present weather query = "select description, (temperature - 273.15) as tempo, city from current_data where city = 'Heidelberg' order by id desc limit 1; " # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() temp = (float("{0:.2f}".format(float(data[1]))), data[0], data[2]) print(temp) cursor.close() cnx.close() return temp
def next5DaysfullWeather(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = " SELECT weather_date,AVG(temperature - 273.15) AS result FROM (SELECT CAST(weather_date AS DATE) AS weather_date,temperature FROM future_data WHERE city = 'Heidelberg' AND weather_date > CURDATE()+1 AND weather_date< CURDATE()+6 ) t1 GROUP BY weather_date;" # actual SQL statement to be executed lines = cursor.execute(query) # execute the query datax = cursor.fetchall() #only the last value is returning not all the values for row in datax: data = row temp = datax #float("{0:.2f}".format(float(data[0]))) cursor.close() cnx.close() return temp
def next5DaysfullCurrent(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() query = "SELECT weather_date, SUM(current_output) FROM (SELECT CAST(weather_date AS DATE) AS weather_date,current_output FROM future_data WHERE city = 'Heidelberg'AND weather_date > CURDATE()+1 AND weather_date< CURDATE()+6 ) t1 GROUP BY weather_date;" #actual SQL statement to be executed- 18000 *3* config.powerFactor/ 1000000 lines = cursor.execute(query) # execute the query data = cursor.fetchall() #if data1 is None: # data1 = 0.0 #temp = float("{0:.2f}".format(float(data1))) cursor.close() cnx.close() return data
def tomorrowCurrent(self): cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() #computing expected tomorrow current query = "SELECT sum(t1.current_output*10800/1000000) as result from (select * from future_data where city = 'Heidelberg' and weather_date >= (curdate()+ 1) order by curr_timestamp desc, weather_date asc limit 8) t1;" # actual SQL statement to be executed lines = cursor.execute(query) # execute the query data = cursor.fetchone() data1 = data[0] if data1 is None: data1 = 0.0 temp = float("{0:.2f}".format(float(data1))) cursor.close() cnx.close() return temp
def currentNow(self): # receiving present current query = "SELECT ip from `heroku_c0277ef6294fdf7`.`raspi_ip` WHERE `status`=1 limit 1;" cnx = classCommonFunc.dataBaseConnection() cursor = cnx.cursor() lines = cursor.execute(query) datax = cursor.fetchall() for row in datax: data = row tempip = data[0] cursor.close() cnx.close() url = "http://" + str(tempip) + "/sensordata" #print(url) f = requests.get(url,timeout=5) if(str(f)!="<Response [502]>" and str(f)!="<Response [500]>" and str(f)!="<Response [404]>"): ret = f jdata = f.json() powq = jdata[0] * config.powerFactor # actual conversion ret = str(powq) else: ret=0 return float("{0:.2f}".format(float(ret)))
import requests, json from datetime import datetime, timedelta import annotate_current from classCommonFunc import classCommonFunc classCommonFunc = classCommonFunc() # fuction curcomplete #def curcomplete(): cnx = classCommonFunc.dataBaseConnection() cur = cnx.cursor() now = datetime.now() #+ timedelta(hours=2) date_string = now.strftime( '%Y-%m-%d %H:%M:%S' ) #Convert a tuple to a string as specified by the format argument url = "http://api.openweathermap.org/data/2.5/weather?zip=69115,de&appid=04ac6f7772b575cbd7bb17063a1430f2" f = requests.get(url) data = f.json() sqlinsert = "insert into heroku_c0277ef6294fdf7.current_data (longitude, latitude, city, description, temperature, temperature_min, temperature_max, humidity, wind, sunrise, sunset, current_output, curr_timestamp) values ('" + str( data['coord']['lon']) + "', '" + str(data['coord']['lat']) + "', '" + str( data['name'] ) + "', '" + str(data['weather'][0]['description']) + "', " + str( data['main']['temp'] ) + ", " + str(data['main']['temp_min']) + ", " + str( data['main']['temp_max']) + ", " + str( data['main']['humidity']) + ", " + str( data['wind']['speed']) + ", " + str( data['sys']['sunrise']) + ", " + str( data['sys']['sunset']