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()