From: Philipp Spitzer Date: Wed, 25 Jul 2018 21:09:52 +0000 (+0200) Subject: Implement writing openweathermap data to database. X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/commitdiff_plain/c76885ae93a85e0abe3ed4362e6cf2f628a239aa Implement writing openweathermap data to database. --- diff --git a/owm.py b/owm.py index 6f7ba24..c41c319 100755 --- a/owm.py +++ b/owm.py @@ -13,11 +13,13 @@ import os import csv import datetime import math +import json +from sqlalchemy import create_engine from seeparklib.openweathermap import openweathermap_json -def fromtimestamp(timestamp, format): - return datetime.datetime.fromtimestamp(timestamp).strftime(format) +def fromtimestamp(timestamp): + return datetime.datetime.fromtimestamp(timestamp) # https://stackoverflow.com/questions/7490660/converting-wind-direction-in-angles-to-text-words @@ -31,9 +33,9 @@ def degToCompass(num): def extractweatherdata(w): data = dict( - datetime = w['dt'], - sunrise = w['sys']['sunrise'], - sunset = w['sys']['sunset'], + datetime = fromtimestamp(w['dt']), + sunrise = fromtimestamp(w['sys']['sunrise']), + sunset = fromtimestamp(w['sys']['sunset']), temp = w['main']['temp'], pressure = w['main']['pressure'], humidity = w['main']['humidity'], @@ -43,10 +45,6 @@ def extractweatherdata(w): cloudiness = w['clouds']['all'], ) - data['sunrise_t'] = fromtimestamp(data['sunrise'], '%H:%M:%S') - data['sunset_t'] = fromtimestamp(data['sunset'], '%H:%M:%S') - data['date'] = fromtimestamp(data['datetime'], '%Y-%m-%d') - data['time'] = fromtimestamp(data['datetime'], '%H:%M:%S') data['winddegrees'] = w['wind']['deg'] if 'deg' in w['wind'] else math.nan data['winddirection'] = degToCompass(data['winddegrees']) data['precipitation'] = w['rain']['3h'] if 'rain' in w else math.nan @@ -60,10 +58,10 @@ def write_csv(csv_file, weather_data): with open(csv_file, "a", newline="") as file: writer = csv.writer(file, dialect="excel", delimiter=';') writer.writerow([ - weather_data['date'], - weather_data['time'], - weather_data['sunrise_t'], - weather_data['sunset_t'], + weather_data['datetime'].date(), + weather_data['datetime'].time(), + weather_data['sunrise'].time(), + weather_data['sunset'].time(), "{:.2f}".format(weather_data['temp']), "{:.2f} mm/h".format(weather_data['precipitation']), "{:.1f} km/h {}".format(weather_data['windspeed'], weather_data['winddirection']), @@ -72,6 +70,27 @@ def write_csv(csv_file, weather_data): ]) +def write_db(config, url, weather_json, weather_data): + user = config.get('database', 'user') + pwd = config.get('database','password') + host = config.get('database','hostname') + db = config.get('database','database') + + engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False) + conn = engine.connect() + row = dict(cityid=config.get('openweathermap', 'cityid'), url=url, result=json.dumps(weather_json)) + row.update(weather_data) + for key, value in row.items(): + if isinstance(value, float) and math.isnan(value): + row[key] = None + sql_columns = list(row.keys()) + sql_values = list(row.values()) + sql = 'insert into openweathermap ({}) values ({})'.format(', '.join(sql_columns), ','.join(['%s'] * len(sql_columns))) + print(sql) + conn.execute(sql, *sql_values) + conn.close() + + def main(configfile, debug): config = configparser.ConfigParser() config.read(configfile) @@ -79,18 +98,18 @@ def main(configfile, debug): cityid = config.get('openweathermap', 'cityid') csvfile = config.get("openweathermap", 'csvfilename') - weather_raw = openweathermap_json(apikey, cityid) + url, weather_json = openweathermap_json(apikey, cityid) if debug: - pprint(weather_raw) - weather = extractweatherdata(weather_raw) + pprint(weather_json) + weather_data = extractweatherdata(weather_json) if debug: - pprint(weather) + pprint(weather_data) - # TODO: # write to db + write_db(config, url, weather_json, weather_data) # write to csv - write_csv(os.path.expanduser(csvfile), weather) + write_csv(os.path.expanduser(csvfile), weather_data) if __name__ == '__main__': diff --git a/seeparklib/openweathermap.py b/seeparklib/openweathermap.py index d350eb6..626f77e 100644 --- a/seeparklib/openweathermap.py +++ b/seeparklib/openweathermap.py @@ -9,12 +9,12 @@ def openweathermap_json(apikey, cityid): """Returns parsed JSON as returned by openweathermap for the given cityid. In case of errors, an OpenWeatherMapError is raised.""" baseurl = 'http://api.openweathermap.org/data/2.5/weather' - query = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid) + url = baseurl + '?units=metric&APPID={}&id={}&lang=de'.format(apikey, cityid) try: - response = requests.get(query) + response = requests.get(url) if response.status_code != 200: raise OpenWeatherMapError('Got status code {} ({}).'.format(response.status_code, response.reason)) else: - return response.json() + return url, response.json() except requests.exceptions.RequestException as error: raise OpenWeatherMapError('Request not successful: {}'.format(error)) diff --git a/web/seepark_web.py b/web/seepark_web.py index c19e9b9..e8b9da1 100644 --- a/web/seepark_web.py +++ b/web/seepark_web.py @@ -132,7 +132,7 @@ def sensordata(sensor_id=None, sensor_type=None): def currentairtemperature(apikey, cityid): """Retruns the tuple temperature, datetime (as float, datetime) in case of success, otherwise None, None.""" try: - weatherdata = openweathermap_json(apikey, cityid) + url, weatherdata = openweathermap_json(apikey, cityid) return weatherdata['main']['temp'], datetime.datetime.fromtimestamp(weatherdata['dt']) except OpenWeatherMapError: return None, None