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"]