def save_pressure_avg_over_categories(db):
sql = "select ca.id, ca.name, hw.pressure " \
"from CHECKIN c " \
"inner join VENUE v on (v.id = c.venue_id) " \
"inner join CATEGORY ca on (v.category_id = ca.id) " \
"inner join DAILY_WEATHER dw on (dw.city_id = v.city_id) " \
"inner join HOURLY_WEATHER hw on (dw.id = hw.daily_weather_id and c.unix_utc_timestamp = hw.unix_utc_timestamp) " \
"where hw.pressure is not null and v.controlled is not null "\
"order by ca.id"
result = execute_select(sql, db)
list = []
for row in result:
list.append((row[0], row[1], row[2]))
with open(query_path + "pressure_avg_nodict.json", mode='w') as file:
file.write(json.dumps(list))
file.close()
def save_moonphase_over_categories(db):
sql = "select ca.id, ca.name, dw.moonphase " \
"from CHECKIN c " \
"inner join VENUE v on (v.id = c.venue_id) " \
"inner join CATEGORY ca on (v.category_id = ca.id) " \
"inner join DAILY_WEATHER dw on (dw.city_id = v.city_id) " \
"inner join HOURLY_WEATHER hw on (dw.id = hw.daily_weather_id and c.unix_utc_timestamp = hw.unix_utc_timestamp) " \
"where hw.wind_speed is not null and v.controlled is not null " \
"order by ca.id "
result = execute_select(sql, db)
list =[]
for row in result:
dict = {}
dict["id"] = row[0]
dict["category"] = row[1]
dict["moonphase"] = row[2]
list.append(dict)
with open(query_path + "moonphase_over_categories.json", mode = 'w') as file:
file.write(json.dumps(list))
file.close()
db="foursquare") # name of the data base
db.set_character_set('utf8')
sql = "select distinct FROM_UNIXTIME(CHECKIN.unix_utc_timestamp+(CHECKIN.timezone_offset*60)," \
" '%Y-%m-%d') as day, CITY.lat, CITY.lon, CITY.id " \
"from CHECKIN " \
"inner join VENUE on (CHECKIN.venue_id = VENUE.id) " \
"inner join CITY on (VENUE.city_id = CITY.id) " \
"where" \
" not exists (select 1 from DAILY_WEATHER " \
" inner join HOURLY_WEATHER on(HOURLY_WEATHER.daily_weather_id = DAILY_WEATHER.id) " \
" inner join CITY on (CITY.id = DAILY_WEATHER.city_id) " \
" inner join CHECKIN on (CHECKIN.unix_utc_timestamp = HOURLY_WEATHER.unix_utc_timestamp" \
" ) "
#try:
results = execute_select(sql, db, None)
i = 1;
response_json = {}
leng = len(results)
percent = -0.01
print (leng)
print(datetime.now().strftime("%H:%M:%S.%f"))
for row in results:
try:
if i % 10 == 0:
print (str(i) + " of " + str(leng))
print(datetime.now().strftime("%H:%M:%S.%f"))
request_url = "https://api.forecast.io/forecast/" + API_KEY + "/" + str(row[1]) + "," + str(row[2]) + "," + str(row[0]) +"T12:00:00?units=si"
r = requests.get(request_url)
]
if __name__ == "__main__":
db = MySQLdb.connect(host="localhost", # your host, usually localhost
user="******", # your username
passwd="foursquare", # your password
db="foursquare") # name of the data base
db.set_character_set('utf8')
venue_us_file = open(venues_us_file_name, mode = "a")
sql = "select CATEGORY.name, v.id from CATEGORY inner join VENUE v on(v.category_id = CATEGORY.id) where v.controlled is null order by CATEGORY.id desc"
curr_token_index = 0
curr_token = all_token[curr_token_index]
result = execute_select(sql,db)
i = 0
amount_errors = 0
datetime_begin = datetime.now()
print(datetime_begin.strftime("%H:%M:%S.%f"))
second = False
for row in result:
try:
#request_url = "https://api.foursquare.com/v2/venues/"+ str(row[1]) + "&client_id=ARQ3RY0FEUJ3CAHTWANXUX3X5EAFSFYDI5MFSQ2TINYZOWXJ&client_secret=T1HMEYIVX3UOOCOYNOJ0LJMWXKSBI2MS0J3IVVT0GWDNSZIA&v=20151027"
request_url = "https://api.foursquare.com/v2/venues/"+ str(row[1]) + "?oauth_token="+curr_token+"&v=20151008&locale=en"
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]: