def insert_hourly(hourly_json, daily_id, db): data = hourly_json["data"] for hour in data: if "precipType" not in hour.keys(): precipType = "" else: precipType = hour["precipType"] if "windBearing" not in hour.keys(): windBearing = 0 else: windBearing = hour["windBearing"] if "windSpeed" not in hour.keys(): windSpeed = 0 else: windSpeed = hour["windSpeed"] if "cloudCover" not in hour.keys(): cloudCover = None else: cloudCover = hour["cloudCover"] if "dewPoint" not in hour.keys(): dewPoint = None else: dewPoint = hour["dewPoint"] if "humidity" not in hour.keys(): humidity = None else: humidity = hour["humidity"] if "precipIntensity" not in hour.keys(): precipIntensity = None else: precipIntensity = hour["precipIntensity"] if "visibility" not in hour.keys(): visibility = None else: visibility = hour["visibility"] if "pressure" not in hour.keys(): pressure = None else: pressure = hour["pressure"] if "temperature" not in hour.keys(): temperature = None else: temperature = hour["temperature"] sql = "INSERT INTO HOURLY_WEATHER VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" params = (daily_id, hour["time"], hour["summary"], hour["icon"], precipIntensity, precipType, dewPoint, windSpeed, windBearing, cloudCover, humidity, pressure, visibility, temperature) execute_insert_statement(sql, db, params)
def insert_daily(daily_json, city_id, daily_id, db): data = daily_json["data"][0] sql = "INSERT INTO DAILY_WEATHER VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" if "precipType" not in data.keys(): precipType = "" else: precipType = data["precipType"] if "windBearing" not in data.keys(): windBearing = None else: windBearing = data["windBearing"] if "windSpeed" not in data.keys(): windSpeed = None else: windSpeed = data["windSpeed"] if "cloudCover" not in data.keys(): cloudCover = None else: cloudCover = data["windSpeed"] if "dewPoint" not in data.keys(): dewPoint = None else: dewPoint = data["dewPoint"] if "humidity" not in data.keys(): humidity = None else: humidity = data["humidity"] if "precipIntensity" not in data.keys(): precipIntensity = None else: precipIntensity = data["precipIntensity"] if "visibility" not in data.keys(): visibility = None else: visibility = data["visibility"] if "pressure" not in data.keys(): pressure = None else: pressure = data["pressure"] params = (daily_id, city_id, data["time"], data["summary"], data["icon"], data["sunriseTime"], data["sunsetTime"], data["moonPhase"], precipIntensity, precipType , dewPoint, windSpeed, windBearing, cloudCover, humidity, pressure, visibility) execute_insert_statement(sql, db, params)
r = requests.get(request_url) response_json = r.json() if not response_json["response"]["venue"]["categories"]: continue if response_json["response"]["venue"]["categories"][0]["name"] != row[0]: sql_select_category = " select name, id from CATEGORY where name like '%{0}%' ".format(response_json["response"]["venue"]["categories"][0]["name"].replace("'", "\\'").encode('utf8')) result = execute_select(sql_select_category, db) category_id = 0; if len(result) == 0: print("make new") sql_select_category = " select max(id) from CATEGORY " max_id = execute_select(sql_select_category, db) category_id = int(max_id[0][0]) + 1 insert_new_category = " INSERT INTO CATEGORY VALUES ( {0} , '{1}' ) ".format(category_id, response_json["response"]["venue"]["categories"][0]["name"].encode('utf8') ) execute_insert_statement(insert_new_category, db) else: category_id = result[0][1] update = " UPDATE VENUE SET category_id = {0} , controlled = 1 where id = '{1}'".format(category_id, str(row[1])) execute_insert_statement(update, db) amount_errors +=1 else: update = " UPDATE VENUE SET controlled = 1 where id = '{0}'".format(str(row[1])) execute_insert_statement(update, db) print(i) print("found errors: " + str(amount_errors)) to_write = {} entries = {} entries["name"] = response_json["response"]["venue"]["name"] entries["location"] = response_json["response"]["venue"]["location"]