Use with statement for connection resource handling.

[chrisu/seepark.git] / owm.py

diff --git a/owm.py b/owm.py
index c36064f56e7556fa9e4b9c1f89df7c903dec6241..1a55892a1cd0ba7bda3beb25b3dc7e186c107ca9 100755 (executable)
--- a/owm.py
+++ b/owm.py
@@ -6,15 +6,20 @@
 # cityid=..
 # 3319578 for Obsteig, AT
 
 # cityid=..
 # 3319578 for Obsteig, AT
 

-from pprint import pprint
+# needed packaes: python3-mysqldb python3-sqlalchemy
+

 import argparse
 import configparser
 import argparse
 import configparser

-import os

 import csv
 import datetime
 import csv
 import datetime

-import math

 import json
 import json

-from sqlalchemy import create_engine
+import math
+import os
+from pprint import pprint
+
+import sqlalchemy
+from sqlalchemy import create_engine, Table
+

 from seeparklib.openweathermap import openweathermap_json
 
 
 from seeparklib.openweathermap import openweathermap_json
 
 


@@ -47,7 +52,7 @@ def extractweatherdata(w):
 
     data['winddegrees'] = w['wind']['deg'] if 'deg' in w['wind'] else math.nan
     data['winddirection'] = degToCompass(data['winddegrees'])
 
     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
+    data['precipitation'] = w['rain']['3h'] if 'rain' in w and w['rain'].get('3h') else math.nan

     data['visibility'] = w.get('visibility', math.nan)
 
     return data
     data['visibility'] = w.get('visibility', math.nan)
 
     return data


@@ -77,17 +82,17 @@ def write_db(config, url, weather_json, weather_data):
     db = config.get('database','database')
 
     engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db), echo=False)
     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)))
-    conn.execute(sql, *sql_values)
-    conn.close()
+    with engine.connect() as conn:
+        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
+        metadata = sqlalchemy.MetaData()
+        openweathermap_table = Table('openweathermap', metadata, autoload_with=engine)
+        ins = openweathermap_table.insert().prefix_with('IGNORE').values(**row)
+        conn.execute(ins)
+        conn.commit()

 
 
 def main(configfile, debug):
 
 
 def main(configfile, debug):