def create_table():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute('''CREATE TABLE weather_data
(id SERIAL PRIMARY KEY NOT NULL,
date_of_measure TIMESTAMP,
temperature NUMERIC,
realfeel NUMERIC,
dew_point NUMERIC,
humidity NUMERIC,
wind NUMERIC,
wind_gust NUMERIC,
wind_direction TEXT,
rain_chance NUMERIC,
rain_prediction NUMERIC,
snow_chance NUMERIC,
snow_prediction NUMERIC,
ice_chance NUMERIC,
ice_prediction NUMERIC
);'''
)
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
cur.close()
conn.close()
def select_data_from_city(city, start_date, end_date):
try:
conn = connect_to_db()
cur = conn.cursor()
if city == 'krk':
cur.execute(
'''SELECT * FROM krakow_data WHERE (date_of_count >= '{}' AND date_of_count <= '{}');'''.format(
start_date, end_date
))
conn.commit()
desired_data = cur.fetchall()
elif city == "br":
cur.execute(
'''SELECT * FROM brussels_data WHERE (date_of_count >= '{}' AND date_of_count <= '{}');'''.format(
start_date, end_date
))
conn.commit()
desired_data = cur.fetchall()
return desired_data
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return error
finally:
cur.close()
conn.close()
def insert_all_data_temp():
print('lets go!')
try:
conn = connect_to_db()
cur = conn.cursor()
start_date = datetime.date(2018, 12, 5)
end_date = datetime.date(2020, 11, 30)
delta = datetime.timedelta(days=1)
devices = list_of_all_counters()
while start_date <= end_date:
print('still working... ', start_date)
for device in devices:
json = get_json_from_location(devices[device], start_date,
start_date)
no_of_cyclists = count_all_the_cyclists(json)
cur.execute('''INSERT INTO brussels_data
(date_of_count, street_name, day_cnt) VALUES
('{}','{}',{});'''.format(start_date, device,
no_of_cyclists))
conn.commit()
start_date += delta
print('success?')
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
cur.close()
conn.close()
def insert_all_data(start_date, end_date):
conn = connect_to_db()
delta = datetime.timedelta(days=1)
devices = list_of_all_counters()
while start_date <= end_date:
for device in devices:
json = get_json_from_location(devices[device], start_date,
start_date)
no_of_cyclists = count_all_the_cyclists(json)
insert_to_db(conn, start_date, device, no_of_cyclists)
start_date += delta
conn.close()
def drop_table():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute('''DROP TABLE brussels_data;''')
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
cur.close()
conn.close()
def insert_data_to_db(dataframe):
print('started')
conn = connect_to_db()
for col in dataframe:
print('working...')
if col == 'Średnia temp. (°C)': # if loop finds "average temp." column it stops
break
for index, row in dataframe.iterrows():
print(index)
insert_to_db(conn,index, col, row[col])
conn.commit()
conn.close()
print('ended!')
def get_values_from_counters(dict_of_counters):
todays_the_day = datetime.date.today() - datetime.timedelta(days=1) #yesterday | day -1 !
connection_to_db = connect_to_db()
for count in dict_of_counters:
if 'DAILY' in count: #get only daily values
driver.get(dict_of_counters[count])
elem = driver.find_element_by_id('corps') #selenium finds value of cyclist in each counter
insert_to_db(connection_to_db,todays_the_day, count[:-6].lower(), elem.text.replace(" ",""))
# print("date ",todays_the_day,'street', count[:-6].lower(),'cyclists', elem.text.replace(" ",""))
connection_to_db.close()
def create_table():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute('''CREATE TABLE brussels_data
(id SERIAL PRIMARY KEY NOT NULL,
date_of_count DATE,
street_name TEXT,
day_cnt VarChar(10));''')
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
cur.close()
conn.close()
def select_last_health_status():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute(
'''SELECT * FROM ping_data ORDER BY id DESC LIMIT 1;''')
conn.commit()
desired_data = cur.fetchall()
return desired_data
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return error
finally:
cur.close()
conn.close()
def select_last_3_hours_from_weather():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute(
'''SELECT * FROM weather_data ORDER BY id DESC LIMIT 3;''')
conn.commit()
desired_data = cur.fetchall()
return desired_data
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return error
finally:
cur.close()
conn.close()
def get_health_status():
dict_of_requests = {}
conn = connect_to_db()
for service in list_of_services:
try:
request = requests.get(list_of_services[service])
dict_of_requests[service] = request.text
except Exception as exc:
print("failure ", exc)
dict_of_requests[service] = 'dead'
insert_status_to_db(conn,
date_of_ping=datetime.datetime.today(),
BSS=dict_of_requests['BSS'],
KSS=dict_of_requests['KSS'],
WCS=dict_of_requests['WCS'],
DAS=dict_of_requests['DAS'],
UIS=dict_of_requests['UIS'])
return dict_of_requests
def create_table():
try:
conn = connect_to_db()
cur = conn.cursor()
cur.execute('''CREATE TABLE ping_data
(id SERIAL PRIMARY KEY NOT NULL,
date_of_ping TIMESTAMP,
BSS TEXT,
KSS TEXT,
WCS TEXT,
DAS TEXT,
UIS TEXT
);''')
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
cur.close()
conn.close()
def select_all_data_from_city(city):
try:
conn = connect_to_db()
cur = conn.cursor()
if city == 'krk':
cur.execute(
'''SELECT * FROM krakow_data;''')
conn.commit()
desired_data = cur.fetchall()
elif city == "br":
cur.execute(
'''SELECT * FROM brussels_data;''')
conn.commit()
desired_data = cur.fetchall()
return desired_data
except (Exception, psycopg2.DatabaseError) as error:
print(error)
return error
finally:
cur.close()
conn.close()
def insert_values_to_db(json):
try:
conn = connect_to_db()
for j in json[0:3]:
insert_to_db(conn,
date_of_measure=j['DateTime'],
temperature=j['Temperature']['Value'],
realfeel=j['RealFeelTemperature']['Value'],
dew_point=j['DewPoint']['Value'],
humidity=j['RelativeHumidity'],
wind=j['Wind']['Speed']['Value'],
wind_gust=j['WindGust']['Speed']['Value'],
wind_direction=j['Wind']['Direction']['Localized'],
rain_chance=j['RainProbability'],
rain_prediction=j['Rain']['Value'],
snow_chance=j['SnowProbability'],
snow_prediction=j['Snow']['Value'],
ice_chance=j['IceProbability'],
ice_prediction=j['Ice']['Value']
)
conn.close()
except(Exception) as error:
print(error)
def setUp(self):
connection = connect_to_db()
yield connection
connection.close()