def check_user_db_exists(ath_un, db_host, superuser_un, superuser_pw): conn = None db_name = str(str2md5(ath_un)) + '_Athlete_Data_DB' with ProgressStdoutRedirection(ath_un): print(('User DB Name: ' + str(db_name))) sql_check_db_exists = """ select exists(SELECT datname FROM pg_catalog.pg_database WHERE datname = %s); """ try: # read connection parameters # connect to the PostgreSQL server conn = psycopg2.connect(dbname='postgres', host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Executing SQL to check whether the database aleady exists') cur.execute(sql_check_db_exists, (db_name, )) result = cur.fetchone() if result[0] is True: db_exists = True else: db_exists = False conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() with ProgressStdoutRedirection(ath_un): print('Database connection closed.') return db_exists
def insert_df_values(ath_un, conn, datafrm, table, unique): # Creating a list of tupples from the dataframe values tpls = [tuple(x) for x in datafrm.to_numpy()] # dataframe columns with Comma-separated cols = ','.join(list(datafrm.columns)) unique = ','.join(unique) # SQL query to execute sql = "INSERT INTO %s(%s) VALUES %%s ON CONFLICT (%s) DO NOTHING;" % ( table, cols, unique) cur = conn.cursor() try: psycopg2.extras.execute_values(cur, sql, tpls) conn.commit() with StdoutRedirection(ath_un): print('Custom data succesfully inserted to: {}'.format(table)) with ProgressStdoutRedirection(ath_un): print('Custom data succesfully inserted to: {}'.format(table)) except (Exception, psycopg2.DatabaseError) as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) cur.close()
def check_user_token_exists(ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): conn = None ath_un = ath_un db_name = db_name decrypted_dbx_token = None sql_check_dbx_token_exists = """ SELECT dropbox_access_token FROM athlete WHERE ath_un = %s; """ try: # connect to the PostgreSQL server with ProgressStdoutRedirection(ath_un): print( 'Connecting to the PostgreSQL server to check whether the dbx token exist...' ) # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement try: cur.execute(sql_check_dbx_token_exists, (ath_un, )) result = cur.fetchone() if result: if result[0] is not None: token_exists = True dbx_token = result[0] #Decrypt dbx token decrypted_dbx_token = decrypt(base64.b64decode(dbx_token), encr_pass) else: token_exists = False else: token_exists = False conn.commit() except: token_exists = False # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() return token_exists, decrypted_dbx_token
def mm_user_insert(mm_export_link, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): mm_export_link = (mm_export_link, ) gc_user = (ath_un, ) db_name = (db_name) conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) sql = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET mm_export_link = %s where ath_un= %s; END IF; END $do$ """ try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Inserting Mind Monitor User Data into postgreSQL:') cur.execute(sql, (gc_user, mm_export_link, gc_user)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def check_host_record_exists(ath_un, db_name, db_host, encr_pass): conn_localhost = None sql_check_db_host_exists = """ SELECT db_host FROM db_info WHERE db_name = %s; """ try: # connect to the PostgreSQL server params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") postgres_un = params.get("user") postgres_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=postgres_un, password=postgres_pw) conn_localhost.autocommit = True # create a cursor cur = conn_localhost.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Checking whether the db_host record aleady exists') cur.execute(sql_check_db_host_exists, (db_name, )) result = cur.fetchone() if result: if result[0] is not None: #Previous record exists stored_db_host = result[0] if stored_db_host != db_host: #Previous record does not match db_host_diff = True else: db_host_diff = False else: #No previous record exists db_host_diff = False else: db_host_diff = False # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn_localhost is not None: conn_localhost.close() return db_host_diff
def check_data_file_exists(data_file_path,ath_un,db_host,db_name,superuser_un,superuser_pw,encr_pass): conn = None ath_un = (ath_un) data_file_path = (data_file_path) db_name = db_name sql_check_file_exists = """ SELECT data_file_path FROM files WHERE athlete_id = (select id from athlete where ath_un=%s) and data_file_path =%s; """ try: # connect to the PostgreSQL server with ProgressStdoutRedirection(ath_un): print('Connecting to the PostgreSQL server to check if the data_file already exists...') conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement cur.execute(sql_check_file_exists,(ath_un,data_file_path)) result = cur.fetchone() if not result: data_file_exists = False else: data_file_exists = True conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ProgressStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() return data_file_exists
def delete_all_db_data(ath_un, mfp_username, db_host, db_name, superuser_un, superuser_pw, encr_pass): db_name = (db_name) conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) sql = """ SELECT truncate_schema('public'); """ try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Deleting DB data for user: '******'....')) cur.execute(sql) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() with StdoutRedirection(ath_un): print(('All DB data for user: '******' deleted successfully.')) with ProgressStdoutRedirection(ath_un): print(('All DB data for user: '******' deleted successfully.'))
def insert_last_synch_timestamp(ath_un, encr_pass, db_name): last_synch = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") last_synch = (last_synch, ) user_db = (db_name, ) sql_postgres_db = """ UPDATE db_info SET last_synch = %s where db_name= %s; """ #Insert last_synch into postgres/db_info table try: params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") postgres_un = params.get("user") postgres_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=postgres_un, password=postgres_pw) conn_localhost.autocommit = True # create a cursor cur_localhost = conn_localhost.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print( 'Inserting last_synch timestamp into postgres/db_info table:') cur_localhost.execute(sql_postgres_db, (last_synch, user_db)) # close the communication with the PostgreSQL cur_localhost.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn_localhost is not None: conn_localhost.close
def csv_match_columns2data(ath_un, csv_data): sniffer = csv.Sniffer() dialect = sniffer.sniff(csv_data.read()) delimiter = dialect.delimiter csv_data.seek(0) #Move pointer to the beginning for i, line in enumerate(csv_data): if i == 0: headerCount = line.count(delimiter) + 1 elif i == 1: dataCount = line.count(delimiter) + 1 if (headerCount != dataCount): with ProgressStdoutRedirection(ath_un): print( "Warning: Header and data size mismatch in {}. Columns beyond header size will be removed." .format(csv)) elif i > 1: break return range(dataCount)
def cstm_user_insert(ath_un, usr_params_str, usr_params_int, table_name, db_host, db_name, superuser_un, superuser_pw): conn = None cstm_dbx_link = usr_params_str[0] download_folder = usr_params_str[1] ui_table_name = usr_params_str[2] date_format = usr_params_str[3] time_standard = usr_params_str[4] datetime_clmn = usr_params_str[5] unique = usr_params_int ins_query_str = "ARRAY['{}','{}','{}','{}','{}','{}']".format( cstm_dbx_link, download_folder, ui_table_name, date_format, time_standard, datetime_clmn) sql_create_clmn = "ALTER TABLE athlete ADD COLUMN IF NOT EXISTS str_up_{0} VARCHAR[], ADD COLUMN IF NOT EXISTS int_up_{0} INT[] ;".format( table_name) sql_insert_usr_str_params = "DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = '{}') THEN UPDATE athlete SET str_up_{} = {} where ath_un= '{}';END IF;END $do$".format( ath_un, table_name, ins_query_str, ath_un) sql_insert_usr_int_params = "DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = '{}') THEN UPDATE athlete SET int_up_{} = ARRAY{} where ath_un= '{}';END IF;END $do$".format( ath_un, table_name, unique, ath_un) try: conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) cur = conn.cursor() with ProgressStdoutRedirection(ath_un): print('Inserting cstm table user params in to postgresDB :') cur.execute(sql_create_clmn) cur.execute(sql_insert_usr_str_params) cur.execute(sql_insert_usr_int_params) conn.commit() cur.close() except (Exception, psycopg2.DatabaseError) as error: print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def delete_all_files(output, ath_un): folder = os.path.join(output, ath_un) #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) for the_file in os.listdir(folder): file_path = os.path.join(folder, the_file) if os.path.isfile(file_path): os.unlink(file_path) elif os.path.isdir(file_path): shutil.rmtree(file_path) with StdoutRedirection(ath_un): print(('All XML,JSON and FIT files for user: '******' deleted successfully.')) with ProgressStdoutRedirection(ath_un): print(('All XML,JSON and FIT files for user: '******' deleted successfully.'))
def data_file_path_insert(file_path,ath_un,db_host,db_name,superuser_un,superuser_pw,encr_pass): file2import = (file_path) athlete_id = (ath_un) db_name = (db_name) conn = None sql = """ INSERT INTO files (data_file_path,athlete_id) VALUES (%s,(select id from athlete where ath_un=%s)) ON CONFLICT (data_file_path) DO NOTHING; """ try: # connect to the PostgreSQL server with ProgressStdoutRedirection(ath_un): print('Connecting to the PostgreSQL server to insert data_file_path...') conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement cur.execute(sql,(file2import,athlete_id)) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) finally: if conn is not None: conn.close()
def gc_original_record_insert(file_path,activity_id,ath_un,db_host,db_name,superuser_un,superuser_pw, encr_pass): start=time.time() file2import = (file_path) gc_activity_id = (activity_id) ath_un = (ath_un) db_name = (db_name) activity_type,altitude,cadence,distance,enhanced_altitude,enhanced_speed,fractional_cadence,heart_rate,position_lat,position_long,speed,stance_time,stance_time_balance,step_length,timestamp,vertical_oscillation,vertical_ratio,accumulated_power,left_pedal_smoothness,left_torque_effectiveness,power,right_pedal_smoothness,right_torque_effectiveness,temperature,avg_speed,avg_swimming_cadence,event,event_group,event_type,length_type,message_index,start_time,swim_stroke,total_calories,total_elapsed_time,total_strokes,total_timer_time,est_core_temp = [None]*38 hrv_record_list = (None, None, None, None) hrv_record_list_combined = [] hrv_rmssd = None hrv_sdrr = None hrv_pnn50 = None hrv_pnn20 = None fitfile = FitFile(file2import) #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un,password=superuser_pw) # two-phase insert prepapre conn.tpc_begin(conn.xid(42, 'transaction ID', ath_un)) # Get all data messages that are of type record for record in fitfile.get_messages('record'): # Go through all the data entries in this record for record_data in record: if record_data.name == 'activity_type': activity_type = record_data.value if record_data.name == 'altitude': altitude = record_data.value if record_data.name == 'cadence': cadence = record_data.value if record_data.name == 'distance': distance = record_data.value if record_data.name == 'enhanced_altitude': enhanced_altitude = record_data.value if record_data.name == 'enhanced_speed': enhanced_speed = record_data.value if record_data.name == 'fractional_cadence': fractional_cadence = record_data.value if record_data.name == 'heart_rate': heart_rate = record_data.value if record_data.name == 'position_lat': position_lat = record_data.value if record_data.name == 'position_long': position_long = record_data.value if record_data.name == 'speed': speed = record_data.value if record_data.name == 'stance_time': stance_time = record_data.value if record_data.name == 'stance_time_balance': stance_time_balance = record_data.value if record_data.name == 'step_length': step_length = record_data.value if record_data.name == 'timestamp': timestamp = record_data.value if record_data.name == 'vertical_oscillation': vertical_oscillation = record_data.value if record_data.name == 'vertical_ratio': vertical_ratio = record_data.value if record_data.name == 'accumulated_power': accumulated_power = record_data.value if record_data.name == 'left_pedal_smoothness': left_pedal_smoothness = record_data.value if record_data.name == 'left_torque_effectiveness': left_torque_effectiveness = record_data.value if record_data.name == 'power': power = record_data.value if record_data.name == 'right_pedal_smoothness': right_pedal_smoothness = record_data.value if record_data.name == 'right_torque_effectiveness': right_torque_effectiveness = record_data.value if record_data.name == 'temperature': temperature = record_data.value if record_data.name == 'Est Core Temp': est_core_temp = record_data.value sql = """ INSERT INTO garmin_connect_original_record (activity_type,altitude,cadence,distance,enhanced_altitude,enhanced_speed,fractional_cadence,heart_rate,position_lat ,position_long,speed,stance_time,stance_time_balance,step_length,timestamp,vertical_oscillation,vertical_ratio,accumulated_power ,left_pedal_smoothness,left_torque_effectiveness,power,right_pedal_smoothness,right_torque_effectiveness,temperature,est_core_temp,lap_id) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,(select id from garmin_connect_original_lap where timestamp >= %s and start_time < %s LIMIT 1)) ON CONFLICT (timestamp,lap_id) DO NOTHING; """ try: # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting track record from session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) cur.execute(sql,(activity_type,altitude,cadence,distance,enhanced_altitude,enhanced_speed,fractional_cadence,heart_rate,position_lat,position_long,speed,stance_time, stance_time_balance,step_length,timestamp,vertical_oscillation,vertical_ratio,accumulated_power,left_pedal_smoothness,left_torque_effectiveness, power,right_pedal_smoothness,right_torque_effectiveness,temperature,est_core_temp,str(timestamp),str(timestamp))) cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) # Get all data messages that are of type hrv for record in fitfile.get_messages('hrv'): # Go through all the data entries in this record for record_data in record: if record_data.name == 'time': hrv_record_list = record_data.value for i in hrv_record_list: if i != None: hrv_record = i hrv_record_list_combined.append(hrv_record*1000) sql_hrv = """ INSERT INTO garmin_connect_hrv_tracking(gc_activity_id,hrv) VALUES (%s,%s); """ try: # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting hrv record from session: ' + str(gc_activity_id))) cur.execute(sql_hrv,(gc_activity_id,hrv_record)) # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) else: continue # two-phase insert commit or rollback try: conn.tpc_prepare() except (Exception, psycopg2.DatabaseError) as error: conn.tpc_rollback() with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) else: try: conn.tpc_commit() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #PG: Add Pool Swim specific data for record in fitfile.get_messages('sport'): for record_data in record: sport = record_data.value if sport == 'Pool Swim': with StdoutRedirection(ath_un): print(('Activity ' + str(gc_activity_id) + ' is a Pool Swim. Inserting additional data')) for record in fitfile.get_messages('length'): for record_data in record: if record_data.name == 'avg_speed': avg_speed = record_data.value if record_data.name == 'avg_swimming_cadence': avg_swimming_cadence = record_data.value if record_data.name == 'event_group': event_group = record_data.value if record_data.name == 'event_type': event_type = record_data.value if record_data.name == 'length_type': length_type = record_data.value if record_data.name == 'message_index': message_index = record_data.value if record_data.name == 'start_time': start_time = record_data.value if record_data.name == 'swim_stroke': swim_stroke = record_data.value if record_data.name == 'timestamp': timestamp = record_data.value if record_data.name == 'total_calories': total_calories = record_data.value if record_data.name == 'total_elapsed_time': total_elapsed_time = record_data.value if record_data.name == 'total_strokes': total_strokes = record_data.value if record_data.name == 'total_timer_time': total_timer_time = record_data.value sql_swim = """ CREATE TEMPORARY TABLE temp_garmin_connect_original_record(avg_speed real,avg_swimming_cadence int,event varchar,event_group varchar,event_type varchar,length_type varchar,message_index int ,start_time varchar,swim_stroke varchar,total_calories int,total_elapsed_time real,total_strokes int,total_timer_time real, timestamp varchar) ON COMMIT DROP; INSERT INTO temp_garmin_connect_original_record (avg_speed,avg_swimming_cadence,event,event_group,event_type,length_type,message_index ,start_time,swim_stroke,total_calories,total_elapsed_time,total_strokes,total_timer_time,timestamp) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s); UPDATE garmin_connect_original_record SET avg_speed = temp_garmin_connect_original_record.avg_speed, avg_swimming_cadence = temp_garmin_connect_original_record.avg_swimming_cadence, event = temp_garmin_connect_original_record.event, event_group = temp_garmin_connect_original_record.event_group, event_type = temp_garmin_connect_original_record.event_type, length_type = temp_garmin_connect_original_record.length_type, message_index = temp_garmin_connect_original_record.message_index, start_time = temp_garmin_connect_original_record.start_time, swim_stroke = temp_garmin_connect_original_record.swim_stroke, total_calories = temp_garmin_connect_original_record.total_calories, total_elapsed_time = temp_garmin_connect_original_record.total_elapsed_time, total_strokes = temp_garmin_connect_original_record.total_strokes, total_timer_time = temp_garmin_connect_original_record.total_timer_time FROM temp_garmin_connect_original_record WHERE temp_garmin_connect_original_record.timestamp = garmin_connect_original_record.timestamp and garmin_connect_original_record.gc_activity_id = %s; """ try: # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting Pool swim data record from session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) cur.execute(sql_swim,(avg_speed,avg_swimming_cadence,event,event_group,event_type,length_type,message_index,start_time,swim_stroke,total_calories,total_elapsed_time,total_strokes,total_timer_time,timestamp,gc_activity_id)) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) with StdoutRedirection(ath_un): print(('--- All record data for session: ' + str(gc_activity_id) + ' inserted successfully. ---')) with ProgressStdoutRedirection(ath_un): print(('--- All record data for session: ' + str(gc_activity_id) + ' inserted successfully. ---')) #Correct Errors. Change all values with SD > 2 to mean hrv_record_list_combined = np.array(hrv_record_list_combined) hrv_record_list_combined[np.abs(zscore(hrv_record_list_combined)) > 2] = np.median(hrv_record_list_combined) #Calculate the square root of the mean square of the differences of RR-intervals hrv_rmssd = np.sqrt(np.mean(np.square(np.diff(hrv_record_list_combined)))) #Calculate the standard deviation of the RR-intervals hrv_sdrr = np.std(hrv_record_list_combined) #Calculate the number of pairs of successive RR-intervals that differ by more than 50/20 ms nn50 = np.sum(np.abs(np.diff(hrv_record_list_combined)) > 50)*1 nn20 = np.sum(np.abs(np.diff(hrv_record_list_combined)) > 20)*1 #Calculate he proportion of NN50/NN20 divided by the total number of RR-intervals. hrv_pnn50 = 100 * nn50 / len(hrv_record_list_combined) hrv_pnn20 = 100 * nn20 / len(hrv_record_list_combined) sql_hrv_stats = ''' UPDATE garmin_connect_original_session SET hrv_rmssd = %s, hrv_sdrr = %s, hrv_pnn50 = %s, hrv_pnn20 = %s WHERE gc_activity_id = %s; ''' try: # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting hrv stats for session: ' + str(gc_activity_id))) cur.execute(sql_hrv_stats,(hrv_rmssd,hrv_sdrr,hrv_pnn50,hrv_pnn20,gc_activity_id)) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) # close the communication with the PostgreSQL if conn is not None: conn.close() end = time.time() with ProgressStdoutRedirection(ath_un): print('\nExecution_time:') with ProgressStdoutRedirection(ath_un): print((end-start))
def gc_user_insert(ath_un, gc_username, gc_password, db_host, db_name, superuser_un, superuser_pw, encr_pass, save_pwd): if save_pwd == True: #Encrypt gc password. if gc_password is not None: encrypted_pwd = base64.b64encode(encrypt(gc_password, encr_pass)) encrypted_pwd = encrypted_pwd.decode('utf-8') else: encrypted_pwd = None else: encrypted_pwd = None #Query params lists gc_user = (gc_username, ) gc_pwd = (encrypted_pwd, ) ath_user = (ath_un, ) conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) sql = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE gc_email = %s) THEN UPDATE athlete SET gc_password = %s where gc_email= %s; ELSEIF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET gc_password = %s,gc_email = %s where ath_un= %s; END IF; END $do$ """ try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Inserting GC User Data into postgreSQL:') cur.execute( sql, (gc_user, gc_pwd, gc_user, ath_user, gc_pwd, gc_user, ath_user)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def dwnld_insert_oura_data(ath_un,db_host,db_name,superuser_un,superuser_pw,oura_refresh_token,start_date_dt,end_date_dt,save_pwd,encr_pass): #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) oura_params = config(filename="encrypted_settings.ini", section="oura",encr_pass=encr_pass) OURA_CLIENT_ID = str(oura_params.get("oura_client_id")) OURA_CLIENT_SECRET = str(oura_params.get("oura_client_secret")) OURA_TOKEN_URL = str(oura_params.get("oura_token_url")) #Refresh the access token payload = dict(grant_type='refresh_token', refresh_token=oura_refresh_token,client_id=OURA_CLIENT_ID,client_secret=OURA_CLIENT_SECRET) refresh = requests.post(OURA_TOKEN_URL, data=payload) response = refresh.json() access_token = response['access_token'] refresh_token = response['refresh_token'] if save_pwd == True: encrypted_refresh_token = base64.b64encode(encrypt(refresh_token, encr_pass)) encrypted_refresh_token = encrypted_refresh_token.decode('utf-8') else: encrypted_refresh_token = None start_date = datetime.datetime.strftime(start_date_dt,"%Y-%m-%d") end_date = datetime.datetime.strftime(end_date_dt,"%Y-%m-%d") conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un,password=superuser_pw) sql_insert_oura_refresh_token = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET oura_refresh_token = %s where ath_un= %s; END IF; END $do$ """ sql_insert_sleep_summary = """ INSERT INTO oura_sleep_daily_summary(athlete_id,summary_date,period_id,is_longest,timezone,bedtime_start,bedtime_end,score,score_total,score_disturbances, score_efficiency,score_latency,score_rem,score_deep,score_alignment,total,duration,awake,light,rem,deep,onset_latency,restless,efficiency,midpoint_time,hr_lowest, hr_average ,rmssd,breath_average ,temperature_delta ,bedtime_end_delta,midpoint_at_delta,bedtime_start_delta,temperature_deviation,temperature_trend_deviation) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (summary_date,period_id) DO NOTHING; """ sql_insert_activity_summary = """ INSERT INTO oura_activity_daily_summary(athlete_id,summary_date,day_start,day_end,timezone,score,score_stay_active,score_move_every_hour,score_meet_daily_targets, score_training_frequency,score_training_volume,score_recovery_time,daily_movement,non_wear,rest,inactive,inactivity_alerts,low,medium,high,steps,cal_total, cal_active,met_min_inactive,met_min_low,met_min_medium,met_min_high,average_met,rest_mode_state,to_target_km,target_miles,total,to_target_miles,target_calories,target_km) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (summary_date) DO NOTHING; """ sql_insert_readiness_summary = """ INSERT INTO oura_readiness_daily_summary(athlete_id,summary_date,period_id,score,score_previous_night,score_sleep_balance,score_previous_day, score_activity_balance,score_resting_hr,score_hrv_balance,score_recovery_index,score_temperature,rest_mode_state) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (summary_date,period_id) DO NOTHING; """ sql_insert_sleep_detail = """ INSERT INTO oura_sleep_detail(oura_sleep_id,timestamp_gmt,hypnogram_5min,hr_5min,rmssd_5min) VALUES ((select id from oura_sleep_daily_summary where summary_date=%s and period_id=%s),%s,%s,%s,%s) ON CONFLICT (timestamp_gmt) DO NOTHING; """ sql_insert_activity_detail = """ INSERT INTO oura_activity_detail(oura_activity_id,timestamp_gmt,class_5min,met_1min) VALUES ((select id from oura_activity_daily_summary where summary_date=%s),%s,%s,%s) ON CONFLICT (timestamp_gmt) DO NOTHING; """ sql_insert_utc_offset = """ INSERT INTO gmt_local_time_difference (athlete_id,local_date,local_midnight_timestamp,gmt_midnight_timestamp,gmt_local_difference) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s) ON CONFLICT (local_date) DO NOTHING; """ try: cur = conn.cursor() cur.execute(sql_insert_oura_refresh_token,(ath_un,encrypted_refresh_token,ath_un)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #------------------- Retrieve Sleep data --------------------------- sleep_data = requests.get('https://api.ouraring.com/v1/sleep?' 'start={}&end={}&access_token={}' .format(start_date, end_date, access_token)) json_sleep = sleep_data.json() sleep_df = pd.DataFrame(json_sleep['sleep']) ####sleep_df = sleep_df.fillna(0) sleep_df = sleep_df.where(pd.notnull(sleep_df), None) for row in sleep_df.itertuples(): awake = row.awake bedtime_end = row.bedtime_end #convert bedtime_end to utc bedtime_end = datetime.datetime.strptime((bedtime_end), "%Y-%m-%dT%H:%M:%S%z") bedtime_end = bedtime_end.astimezone(pytz.utc) bedtime_end_delta = row.bedtime_end_delta bedtime_start = row.bedtime_start #convert bedtime_start to utc bedtime_start = datetime.datetime.strptime((bedtime_start), "%Y-%m-%dT%H:%M:%S%z") bedtime_start = bedtime_start.astimezone(pytz.utc) bedtime_start_delta = row.bedtime_start_delta breath_average = row.breath_average deep = row.deep duration = row.duration efficiency = row.efficiency hr_5min = row.hr_5min hr_average = row.hr_average hr_lowest = row.hr_lowest hypnogram_5min = row.hypnogram_5min is_longest = row.is_longest light = row.light midpoint_at_delta = row.midpoint_at_delta midpoint_time = row.midpoint_time onset_latency = row.onset_latency period_id = row.period_id rem = row.rem restless = row.restless rmssd = row.rmssd rmssd_5min = row.rmssd_5min score = row.score score_alignment = row.score_alignment score_deep = row.score_deep score_disturbances = row.score_disturbances score_efficiency = row.score_efficiency score_latency = row.score_latency score_rem = row.score_rem score_total = row.score_total summary_date = row.summary_date temperature_delta = row.temperature_delta temperature_deviation = row.temperature_deviation temperature_trend_deviation = row.temperature_trend_deviation timezone = row.timezone total = row.total with StdoutRedirection(ath_un): print('Downloading Oura sleep data from: {}'.format(summary_date)) with ProgressStdoutRedirection(ath_un): print('Downloading Oura sleep data from: {}'.format(summary_date)) try: cur = conn.cursor() cur.execute(sql_insert_sleep_summary,(ath_un,summary_date,period_id,is_longest,timezone,datetime.datetime.strftime(bedtime_start,"%Y-%m-%d %H:%M:%S"), datetime.datetime.strftime(bedtime_end,"%Y-%m-%d %H:%M:%S"),score,score_total,score_disturbances,score_efficiency,score_latency,score_rem,score_deep, score_alignment,total,duration,awake,light,rem,deep,onset_latency,restless,efficiency,midpoint_time,hr_lowest,hr_average ,rmssd,breath_average , temperature_delta,bedtime_end_delta,midpoint_at_delta,bedtime_start_delta,temperature_deviation,temperature_trend_deviation) ) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #Create and populate a list of 5min intervals starting from bedtime_start rmssd_ints = [] #add first value (bedtime_start) to 5 min intervals list rmssd_ints.append(datetime.datetime.strftime(bedtime_start,"%Y-%m-%d %H:%M:%S")) rmssd_int = bedtime_start for i in range (len(rmssd_5min)-1): rmssd_int = rmssd_int + datetime.timedelta(seconds=300) rmssd_int_str = datetime.datetime.strftime(rmssd_int,"%Y-%m-%d %H:%M:%S") rmssd_ints.append(rmssd_int_str) #Create sleep detail dataframe sleep_detail_df = pd.DataFrame({'timestamp': pd.Series(rmssd_ints), 'hr_5min': pd.Series(hr_5min),'hypnogram_5min': pd.Series(list(hypnogram_5min)),'rmssd_5min': pd.Series(rmssd_5min)}) ####sleep_detail_df = sleep_detail_df.fillna(0) sleep_detail_df = sleep_detail_df.where(pd.notnull(sleep_detail_df), None) for row in sleep_detail_df.itertuples(): timestamp_row = row.timestamp hr_5min_row = row.hr_5min hypnogram_5min_row = row.hypnogram_5min rmssd_5min_row = row.rmssd_5min try: cur = conn.cursor() cur.execute(sql_insert_sleep_detail,(summary_date,period_id,timestamp_row,hypnogram_5min_row,hr_5min_row,rmssd_5min_row)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #------------------- Retrieve Activity data --------------------------- activity_data = requests.get('https://api.ouraring.com/v1/activity?' 'start={}&end={}&access_token={}' .format(start_date, end_date, access_token)) json_activity = activity_data.json() activity_df = pd.DataFrame(json_activity['activity']) ####activity_df = activity_df.fillna(0) activity_df = activity_df.where(pd.notnull(activity_df), None) for row in activity_df.itertuples(): average_met = row.average_met cal_active = row.cal_active cal_total = row.cal_total class_5min = row.class_5min daily_movement = row.daily_movement day_end = row.day_end #convert day_end to utc day_end = datetime.datetime.strptime((day_end), "%Y-%m-%dT%H:%M:%S%z") day_end = day_end.astimezone(pytz.utc) day_start = row.day_start #convert day_start to utc day_start = datetime.datetime.strptime((day_start), "%Y-%m-%dT%H:%M:%S%z") day_start = day_start.astimezone(pytz.utc) high = row.high inactive = row.inactive inactivity_alerts = row.inactivity_alerts low = row.low medium = row.medium met_1min = row.met_1min met_min_high = row.met_min_high met_min_inactive = row.met_min_inactive met_min_low = row.met_min_low met_min_medium = row.met_min_medium non_wear = row.non_wear rest = row.rest rest_mode_state = row.rest_mode_state try: score = row.score score_meet_daily_targets = row.score_meet_daily_targets score_move_every_hour = row.score_move_every_hour score_recovery_time = row.score_recovery_time score_stay_active = row.score_stay_active score_training_frequency = row.score_training_frequency score_training_volume = row.score_training_volume #If Ouraring rest mode enabled except: score=score_meet_daily_targets=score_move_every_hour=score_recovery_time=score_stay_active=score_training_frequency=score_training_volume=None steps = row.steps summary_date = row.summary_date target_calories = row.target_calories target_km = row.target_km target_miles = row.target_miles timezone = row.timezone to_target_km = row.to_target_km to_target_miles = row.to_target_miles total = row.total with StdoutRedirection(ath_un): print('Downloading Oura activity data from: {}'.format(summary_date)) with ProgressStdoutRedirection(ath_un): print('Downloading Oura activity data from: {}'.format(summary_date)) try: cur = conn.cursor() cur.execute(sql_insert_activity_summary,(ath_un,summary_date,datetime.datetime.strftime(day_start,"%Y-%m-%d %H:%M:%S"),datetime.datetime.strftime(day_end,"%Y-%m-%d %H:%M:%S"), timezone,score,score_stay_active,score_move_every_hour,score_meet_daily_targets,score_training_frequency,score_training_volume,score_recovery_time,daily_movement, non_wear,rest,inactive,inactivity_alerts,low,medium,high,steps,cal_total,cal_active,met_min_inactive,met_min_low,met_min_medium,met_min_high,average_met, rest_mode_state,to_target_km,target_miles,total,to_target_miles,target_calories,target_km) ) conn.commit() cur.close() #Insert utc offset into gmt_local_time_difference table if the record not already present from gc gmt_local_difference = datetime.timedelta(minutes=timezone) local_midnight = datetime.datetime.strptime(summary_date, '%Y-%m-%d') gmt_midnight = local_midnight-gmt_local_difference local_date = local_midnight.date() cur = conn.cursor() cur.execute(sql_insert_utc_offset,(ath_un,local_date,local_midnight,gmt_midnight,gmt_local_difference)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #Create and populate a list of 1min intervals starting from day_start met_ints = [] #add first value (day_start) to 1 min intervals list met_ints.append(datetime.datetime.strftime(day_start,"%Y-%m-%d %H:%M:%S")) met_int = day_start for i in range (len(met_1min)-1): met_int = met_int + datetime.timedelta(seconds=60) met_int_str = datetime.datetime.strftime(met_int,"%Y-%m-%d %H:%M:%S") met_ints.append(met_int_str) #Pad class_5min_list to match the size of 1min list's class_5min_list = list(class_5min) def insert_element_list(lst, x, n): i = n while i < len(lst): lst.insert(i, x) i+= n+1 return lst #Add 4 new "0" elements after every original element n=1 for i in range(4): insert_element_list(class_5min_list,'0',n) n=n+1 activity_detail_df = pd.DataFrame({'timestamp': pd.Series(met_ints), 'met_1min': pd.Series(met_1min),'class_5min': pd.Series(class_5min_list)}) ####activity_detail_df = activity_detail_df.fillna(0) activity_detail_df = activity_detail_df.where(pd.notnull(activity_detail_df), None) for row in activity_detail_df.itertuples(): timestamp_row = row.timestamp met_1min_row = row.met_1min class_5min_row = row.class_5min try: cur = conn.cursor() cur.execute(sql_insert_activity_detail,(summary_date,timestamp_row,class_5min_row,met_1min_row)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #------------------- Retrieve Readiness data --------------------------- readiness_data = requests.get('https://api.ouraring.com/v1/readiness?' 'start={}&end={}&access_token={}' .format(start_date, end_date, access_token)) json_readiness = readiness_data.json() readiness_df = pd.DataFrame(json_readiness['readiness']) ####readiness_df = readiness_df.fillna(0) readiness_df = readiness_df.where(pd.notnull(readiness_df), None) for row in readiness_df.itertuples(): period_id = row.period_id rest_mode_state = row.rest_mode_state score = row.score score_activity_balance = row.score_activity_balance score_hrv_balance = row.score_hrv_balance score_previous_day = row.score_previous_day score_previous_night = row.score_previous_night score_recovery_index = row.score_recovery_index score_resting_hr = row.score_resting_hr score_sleep_balance = row.score_sleep_balance score_temperature = row.score_temperature summary_date = row.summary_date with StdoutRedirection(ath_un): print('Downloading Oura readiness data from: {}'.format(summary_date)) with ProgressStdoutRedirection(ath_un): print('Downloading Oura readiness data from: {}'.format(summary_date)) try: cur = conn.cursor() cur.execute(sql_insert_readiness_summary,(ath_un,summary_date,period_id,score,score_previous_night,score_sleep_balance,score_previous_day, score_activity_balance,score_resting_hr,score_hrv_balance,score_recovery_index,score_temperature,rest_mode_state) ) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e)))
def gc_original_lap_insert(file_path,activity_id,ath_un, db_host,db_name,superuser_un,superuser_pw,encr_pass): file2import = (file_path) gc_activity_id = (activity_id) ath_un = (ath_un) db_name = (db_name) avg_cadence,avg_combined_pedal_smoothness,avg_heart_rate,avg_left_pco,avg_left_pedal_smoothness,avg_left_torque_effectiveness,avg_power,avg_right_pco,avg_right_pedal_smoothness,avg_right_torque_effectiveness,avg_speed,end_position_lat,end_position_long,enhanced_avg_speed,enhanced_max_speed,event,event_group,event_type,intensity,lap_trigger,left_right_balance,max_cadence,max_heart_rate,max_power,max_speed,message_index,normalized_power,sport,stand_count,start_position_lat,start_position_long,start_time,time_standing,timestamp,total_ascent,total_calories,total_cycles,total_descent,total_distance,total_elapsed_time,total_fat_calories,total_timer_time,total_work = [None]*43 avg_cadence_position = (None, None) avg_left_power_phase = (None, None, None, None) avg_left_power_phase_peak = (None, None, None, None) avg_power_position = (None, None) avg_right_power_phase = (None, None, None, None) avg_right_power_phase_peak = (None, None, None, None) max_cadence_position = (None, None) max_power_position = (None, None) fitfile = FitFile(file2import) #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un,password=superuser_pw) # Get all data messages that are of type record for record in fitfile.get_messages('lap'): # Go through all the data entries in this record for record_data in record: if record_data.name == 'avg_cadence': avg_cadence = record_data.value if record_data.name == 'avg_cadence_position': avg_cadence_position = record_data.value if record_data.name == 'avg_combined_pedal_smoothness': avg_combined_pedal_smoothness = record_data.value if record_data.name == 'avg_heart_rate': avg_heart_rate = record_data.value if record_data.name == 'avg_left_pco': avg_left_pco = record_data.value if record_data.name == 'avg_left_pedal_smoothness': avg_left_pedal_smoothness = record_data.value if record_data.name == 'avg_left_power_phase': avg_left_power_phase = record_data.value if record_data.name == 'avg_left_power_phase_peak': avg_left_power_phase_peak = record_data.value if record_data.name == 'avg_left_torque_effectiveness': avg_left_torque_effectiveness = record_data.value if record_data.name == 'avg_power': avg_power = record_data.value if record_data.name == 'avg_power_position': avg_power_position = record_data.value if record_data.name == 'avg_right_pco': avg_right_pco = record_data.value if record_data.name == 'avg_right_pedal_smoothness': avg_right_pedal_smoothness = record_data.value if record_data.name == 'avg_right_power_phase': avg_right_power_phase = record_data.value if record_data.name == 'avg_right_power_phase_peak': avg_right_power_phase_peak = record_data.value if record_data.name == 'avg_right_torque_effectiveness': avg_right_torque_effectiveness = record_data.value if record_data.name == 'avg_speed': avg_speed = record_data.value if record_data.name == 'end_position_lat': end_position_lat = record_data.value if record_data.name == 'end_position_long': end_position_long = record_data.value if record_data.name == 'enhanced_avg_speed': enhanced_avg_speed = record_data.value if record_data.name == 'enhanced_max_speed': enhanced_max_speed = record_data.value if record_data.name == 'event': event = record_data.value if record_data.name == 'event_group': event_group = record_data.value if record_data.name == 'event_type': event_type = record_data.value if record_data.name == 'intensity': intensity = record_data.value if record_data.name == 'lap_trigger': lap_trigger = record_data.value if record_data.name == 'left_right_balance': left_right_balance = record_data.value if record_data.name == 'max_cadence': max_cadence = record_data.value if record_data.name == 'max_cadence_position': max_cadence_position = record_data.value if record_data.name == 'max_heart_rate': max_heart_rate = record_data.value if record_data.name == 'max_power': max_power = record_data.value if record_data.name == 'max_power_position': max_power_position = record_data.value if record_data.name == 'max_speed': max_speed = record_data.value if record_data.name == 'message_index': message_index = record_data.value if record_data.name == 'normalized_power': normalized_power = record_data.value if record_data.name == 'sport': sport = record_data.value if record_data.name == 'stand_count': stand_count = record_data.value if record_data.name == 'start_position_lat': start_position_lat = record_data.value if record_data.name == 'start_position_long': start_position_long = record_data.value if record_data.name == 'start_time': start_time = record_data.value if record_data.name == 'time_standing': time_standing = record_data.value if record_data.name == 'timestamp': timestamp = record_data.value if record_data.name == 'total_ascent': total_ascent = record_data.value if record_data.name == 'total_calories': total_calories = record_data.value if record_data.name == 'total_cycles': total_cycles = record_data.value if record_data.name == 'total_descent': total_descent = record_data.value if record_data.name == 'total_distance': total_distance = record_data.value if record_data.name == 'total_elapsed_time': total_elapsed_time = record_data.value if record_data.name == 'total_fat_calories': total_fat_calories = record_data.value if record_data.name == 'total_timer_time': total_timer_time = record_data.value if record_data.name == 'total_work': total_work = record_data.value sql = """ INSERT INTO garmin_connect_original_lap (avg_cadence,avg_cadence_position,avg_combined_pedal_smoothness,avg_heart_rate,avg_left_pco,avg_left_pedal_smoothness,avg_left_power_phase, avg_left_power_phase_peak,avg_left_torque_effectiveness,avg_power,avg_power_position,avg_right_pco,avg_right_pedal_smoothness,avg_right_power_phase,avg_right_power_phase_peak, avg_right_torque_effectiveness,avg_speed,end_position_lat,end_position_long,enhanced_avg_speed,enhanced_max_speed,event,event_group,event_type,intensity,lap_trigger,left_right_balance, max_cadence,max_cadence_position,max_heart_rate,max_power,max_power_position,max_speed,message_index,normalized_power,sport,stand_count,start_position_lat,start_position_long, start_time,time_standing,timestamp,total_ascent,total_calories,total_cycles,total_descent,total_distance,total_elapsed_time,total_fat_calories,total_timer_time,total_work,gc_activity_id) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (timestamp,gc_activity_id) DO NOTHING; """ try: # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting lap from session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) with ProgressStdoutRedirection(ath_un): print(('Inserting lap from session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) cur.execute(sql,(avg_cadence,list(avg_cadence_position),avg_combined_pedal_smoothness,avg_heart_rate,avg_left_pco,avg_left_pedal_smoothness,list(avg_left_power_phase), list(avg_left_power_phase_peak),avg_left_torque_effectiveness,avg_power,list(avg_power_position),avg_right_pco,avg_right_pedal_smoothness,list(avg_right_power_phase),list(avg_right_power_phase_peak), avg_right_torque_effectiveness,avg_speed,end_position_lat,end_position_long,enhanced_avg_speed,enhanced_max_speed,event,event_group,event_type,intensity,lap_trigger,left_right_balance, max_cadence,list(max_cadence_position),max_heart_rate,max_power,list(max_power_position),max_speed,message_index,normalized_power,sport,stand_count,start_position_lat,start_position_long, start_time,time_standing,timestamp,total_ascent,total_calories,total_cycles,total_descent,total_distance,total_elapsed_time,total_fat_calories,total_timer_time,total_work,gc_activity_id)) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) # close the communication with the PostgreSQL if conn is not None: conn.close() with StdoutRedirection(ath_un): print(('--- All lap data for session: ' + str(gc_activity_id) + ' inserted successfully. ---')) with ProgressStdoutRedirection(ath_un): print(('--- All lap data for session: ' + str(gc_activity_id) + ' inserted successfully. ---'))
def gc_dailysummary_insert(file_path, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): """ Connect to the PostgreSQL database server """ db_name = (db_name) conn = None #Lists to store the data from xml startGMT = [] endGMT = [] activityLevelConstant = [] steps = [] primaryActivityLevel = [] #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) # PG Amend path for postgres "pg_read_file()" function. text = file_path head, sep, tail = text.partition('temp/') pg_read_file = (tail, ) xpath_sql = """ INSERT INTO garmin_connect_daily_summary (athlete_id,start_gmt,end_gmt,activity_level_constant,steps,primary_activity_level) SELECT (select id from athlete where ath_un=%s), to_char(to_timestamp(unnest (xpath('//*[local-name()="item"]/*[local-name()="startGMT"]/text()', x))::text::text,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AS start_gmt, to_char(to_timestamp(unnest (xpath('//*[local-name()="item"]/*[local-name()="endGMT"]/text()', x))::text::text,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AS end_gmt, unnest (xpath('//*[local-name()="item"]/*[local-name()="activityLevelConstant"]/text()', x))::text::bool AS activity_level_constant, unnest (xpath('//*[local-name()="item"]/*[local-name()="steps"]/text()', x))::text::int AS steps, unnest (xpath('//*[local-name()="item"]/*[local-name()="primaryActivityLevel"]/text()', x))::text::text AS primary_activity_level FROM UNNEST (xpath('//*[local-name()="root"]', pg_read_file(%s)::xml)) x ON CONFLICT (athlete_id,start_gmt) DO NOTHING; """ pd_df_sql = """ INSERT INTO garmin_connect_daily_summary (athlete_id,start_gmt,end_gmt,activity_level_constant,steps,primary_activity_level) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s) ON CONFLICT (athlete_id,start_gmt) DO NOTHING; """ sql_time_diff = """ INSERT INTO gmt_local_time_difference (athlete_id,local_date,local_midnight_timestamp,gmt_midnight_timestamp,gmt_local_difference) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s) ON CONFLICT (local_date) DO NOTHING; """ sql_sleep_tracking = """ INSERT INTO gc_original_wellness_sleep_tracking (timestamp_gmt,timestamp_local,activity_tracking_id,hr_tracking_id,stress_tracking_id) VALUES (%s,%s,(select id from gc_original_wellness_activity_tracking where timestamp=%s),(select id from gc_original_wellness_hr_tracking where timestamp=%s),(select id from gc_original_wellness_stress_tracking where timestamp=%s)) ON CONFLICT (timestamp_gmt) DO NOTHING; """ #Parse the XML document, and append the data to lists xml_document = parse(file_path) for item in xml_document.iterfind('item'): startGMT.append(item.findtext('startGMT')) endGMT.append(item.findtext('endGMT')) activityLevelConstant.append(item.findtext('activityLevelConstant')) steps.append(item.findtext('steps')) primaryActivityLevel.append(item.findtext('primaryActivityLevel')) #Create pandas dataframe and populate with data from lists df = pd.DataFrame({ 'startGMT': startGMT, 'endGMT': endGMT, 'activityLevelConstant': activityLevelConstant, 'steps': steps, 'primaryActivityLevel': primaryActivityLevel }) #Convert startGMT and endGMT to datetime df['startGMT'] = pd.to_datetime(df['startGMT']) df['endGMT'] = pd.to_datetime(df['endGMT']) #Locate gmt midnight midnight_gmt_dt = df['startGMT'].iloc[0] #Retrieve date form the xml file name date_str = file_path[-22:-12] #Convert local date midnight to datetime midnight_local_dt = datetime.datetime.strptime(date_str, "%Y-%m-%d") #Get time difference (timedelta) by subtracting gmt from local local_gmt_time_diff = midnight_local_dt - midnight_gmt_dt try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) conn.tpc_begin(conn.xid(42, 'transaction ID', 'connection 1')) ### Insert sleep tracking data to gc_original_wellness_sleep_tracking ### #Iterate through rows in pandas df for row in df.itertuples(): row_startGMT = row.startGMT row_endGMT = row.endGMT row_activityLevelConstant = row.activityLevelConstant row_steps = row.steps row_primaryActivityLevel = row.primaryActivityLevel ### Insert daily summary data to garmin_connect_daily_summary ### # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print('Inserting Daily Summary Data into postgreSQL:') cur.execute( pd_df_sql, (ath_un, row_startGMT, row_endGMT, row_activityLevelConstant, row_steps, row_primaryActivityLevel)) #conn.commit() # close the communication with the PostgreSQL cur.close() #If activity level eq sleeping, extract start and end times (15min intervals) if row_primaryActivityLevel == 'sleeping': #Break down 15min intervals in to 1min and convert each to local time while (row_startGMT < row_endGMT): row_startGMT = row_startGMT + datetime.timedelta(minutes=1) row_startLocal = row_startGMT + local_gmt_time_diff #Convert datetimes to strings row_startGMT_str = datetime.datetime.strftime( (row_startGMT), "%Y-%m-%d %H:%M:%S") row_startLocal_str = datetime.datetime.strftime( (row_startLocal), "%Y-%m-%d %H:%M:%S") # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print('Inserting Sleep Tracking Data into postgreSQL:') cur.execute( sql_sleep_tracking, (row_startGMT_str, row_startLocal_str, row_startGMT_str, row_startGMT_str, row_startGMT_str)) # close the communication with the PostgreSQL cur.close() ### Insert Local to GMT time differnce to gmt_local_time_difference table ### # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print('Inserting Local to GMT time difference into postgreSQL:') cur.execute(sql_time_diff, (ath_un, date_str, midnight_local_dt, midnight_gmt_dt, local_gmt_time_diff)) # close the communication with the PostgreSQL cur.close() with StdoutRedirection(ath_un): print('Local to GMT time difference Data Inserted Successfully') with ProgressStdoutRedirection(ath_un): print('Local to GMT time difference Data Inserted Successfully') try: conn.tpc_prepare() except (Exception, psycopg2.DatabaseError) as error: conn.tpc_rollback() with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) else: conn.tpc_commit() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def gc_original_wellness_insert(file_path, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): start = time.time() file2import = (file_path) athlete_id = (ath_un) db_name = (db_name) active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, heart_rate, timestamp_16, intensity, stress_level_time, stress_level_value = [ None ] * 12 current_activity_type_intensity = (None) fitfile = FitFile(file2import) #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) messages = fitfile.get_messages() conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) #two-phase insert conn.tpc_begin(conn.xid(42, 'transaction ID', ath_un)) # Get all data messages for message in fix_times(messages): # Go through all the data entries in this record for message_data in message: if message_data.name == 'active_calories': active_calories = message_data.value if message_data.name == 'active_time': active_time = message_data.value if message_data.name == 'activity_type': activity_type = message_data.value if message_data.name == 'distance': distance = message_data.value if message_data.name == 'duration_min': duration_min = message_data.value if message_data.name == 'steps': steps = message_data.value if message_data.name == 'timestamp': timestamp = message_data.value if message_data.name == 'heart_rate': heart_rate = message_data.value if message_data.name == 'timestamp_16': timestamp_16 = message_data.value if message_data.name == 'intensity': intensity = message_data.value if message_data.name == 'stress_level_time': stress_level_time = message_data.value if message_data.name == 'stress_level_value': stress_level_value = message_data.value if message_data.name == 'current_activity_type_intensity': current_activity_type_intensity = message_data.value sql_act_type_summary = """ INSERT INTO gc_original_wellness_act_type_summary(active_calories, active_time, activity_type, distance, duration_min, steps, timestamp,athlete_id) VALUES (%s,%s,%s,%s,%s,%s,%s,(select id from athlete where ath_un=%s)) ON CONFLICT (athlete_id,activity_type,timestamp) DO NOTHING; """ sql_hr_tracking = """ INSERT INTO gc_original_wellness_hr_tracking(heart_rate, timestamp_16, timestamp,athlete_id) VALUES (%s,%s,%s,(select id from athlete where ath_un=%s)) ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ sql_activity_tracking = """ INSERT INTO gc_original_wellness_activity_tracking(activity_type, current_activity_type_intensity,intensity,timestamp,athlete_id) VALUES (%s,%s,%s,%s,(select id from athlete where ath_un=%s)) ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ sql_stress_tracking = """ INSERT INTO gc_original_wellness_stress_tracking(stress_level_time, stress_level_value,timestamp,athlete_id) VALUES (%s,%s,%s,(select id from athlete where ath_un=%s)) ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ try: # create a cursor cur = conn.cursor() if duration_min is not None: # execute a statement with StdoutRedirection(ath_un): print(('Inserting Activity Type Summary record : ' + ' with timestamp:' + str(timestamp))) cur.execute( sql_act_type_summary, (active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, athlete_id)) # close the communication with the PostgreSQL cur.close() active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, heart_rate, timestamp_16, intensity, stress_level_time, stress_level_value = [ None ] * 12 current_activity_type_intensity = (None) if heart_rate is not None: # execute a statement with StdoutRedirection(ath_un): print(('Inserting Heart Rate Tracking record : ' + ' with timestamp:' + str(timestamp))) cur.execute(sql_hr_tracking, (heart_rate, timestamp_16, timestamp, athlete_id)) # close the communication with the PostgreSQL cur.close() active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, heart_rate, timestamp_16, intensity, stress_level_time, stress_level_value = [ None ] * 12 current_activity_type_intensity = (None) if intensity is not None: # execute a statement with StdoutRedirection(ath_un): print(('Inserting Activity Tracking record : ' + ' with timestamp:' + str(timestamp))) cur.execute( sql_activity_tracking, (activity_type, list(current_activity_type_intensity), intensity, timestamp, athlete_id)) # close the communication with the PostgreSQL cur.close() active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, heart_rate, timestamp_16, intensity, stress_level_time, stress_level_value = [ None ] * 12 current_activity_type_intensity = (None) if stress_level_value is not None: # execute a statement with StdoutRedirection(ath_un): print(('Inserting Stress Tracking record : ' + ' with timestamp:' + str(timestamp))) cur.execute(sql_stress_tracking, (stress_level_time, stress_level_value, timestamp, athlete_id)) # close the communication with the PostgreSQL cur.close() active_calories, active_time, activity_type, distance, duration_min, steps, timestamp, heart_rate, timestamp_16, intensity, stress_level_time, stress_level_value = [ None ] * 12 current_activity_type_intensity = (None) except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) #two-phase insert commit or rollback try: conn.tpc_prepare() except (Exception, psycopg2.DatabaseError) as error: conn.tpc_rollback() with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) else: try: conn.tpc_commit() except Exception as e: with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) # close the communication with the PostgreSQL if conn is not None: conn.close() with StdoutRedirection(ath_un): print('--- All wellness record data inserted successfully. ---') with ProgressStdoutRedirection(ath_un): print('--- All wellness record data inserted successfully. ---') end = time.time() with ProgressStdoutRedirection(ath_un): print('\nExecution_time:') with ProgressStdoutRedirection(ath_un): print((end - start))
def parse_dataset(dataset, conn, start_date_dt, end_date_dt, data_source): for row in dataset.itertuples(): glucose_nmol = None glucose_nmol_avrg = None ketone_nmol = None utc_time_str = None if data_source == 'glimp_android': record_type = row._9 #Current(5) or 15min average(3) if record_type == 5: glucose_nmol = round((float(row._6) / 18), 2) else: glucose_nmol_avrg = round((float(row._6) / 18), 2) local_time = row._2 epoch_time = row._3 local_time_dt = datetime.datetime.strptime((local_time), "%d/%m/%Y %H.%M.%S") local_time_str = datetime.datetime.strftime( (local_time_dt), "%Y-%m-%d %H:%M:%S") utc_time_str = datetime.datetime.utcfromtimestamp( epoch_time).strftime('%Y-%m-%d %H:%M:%S') else: ketone_nmol = row._7 glucose_nmol = row._6 glucose_nmol_avrg = row._5 local_time = row._3 local_time_dt = local_time.to_pydatetime( ) #Convert from pandas to python datetime local_time_str = datetime.datetime.strftime( (local_time_dt), "%Y-%m-%d %H:%M:%S") local_date_str = datetime.datetime.strftime((local_time_dt), "%Y-%m-%d") try: # create a cursor cur = conn.cursor() # execute a statement cur.execute(sql_local2gmt, (local_date_str, )) result = cur.fetchone() # close the communication with the PostgreSQL conn.commit() cur.close() if result is not None: utc_time_dt = local_time_dt - result[0] utc_time_str = datetime.datetime.strftime( (utc_time_dt), "%Y-%m-%d %H:%M:%S") except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' ' + str(error))) # Compare if the Record Date is within the download Date range. This only works if parsed from top of the csv file. # The most recent record at the top if local_time_dt > end_date_dt + datetime.timedelta(days=1): continue if local_time_dt < start_date_dt: break with ProgressStdoutRedirection(ath_un): print( ('Inserting BG record from Glimp/LibreView. Time: ' + str(local_time_str) + ' Glucose: ' + str(glucose_nmol))) with StdoutRedirection(ath_un): print( ('Inserting BG record from Glimp/LibreView. Time: ' + str(local_time_str) + ' Glucose: ' + str(glucose_nmol))) try: # create a cursor cur = conn.cursor() # execute a statement cur.execute( sql, (ath_un, local_time_str, glucose_nmol, glucose_nmol_avrg, ketone_nmol, data_source, utc_time_str)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' ' + str(error)))
def gc_original_session_insert(file_path, activity_id, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): file2import = (file_path) gc_activity_id = (activity_id) athlete_id = (ath_un) db_name = (db_name) avg_cadence, avg_combined_pedal_smoothness, avg_heart_rate, avg_left_pco, avg_left_pedal_smoothness, avg_left_torque_effectiveness, avg_power, avg_right_pco, avg_right_pedal_smoothness, avg_right_torque_effectiveness, avg_speed, enhanced_avg_speed, enhanced_max_speed, event, event_group, event_type, first_lap_index, intensity_factor, left_right_balance, max_cadence, max_heart_rate, max_power, max_speed, message_index, nec_lat, nec_long, normalized_power, num_laps, sport, stand_count, start_position_lat, start_position_long, start_time, sub_sport, swc_lat, swc_long, threshold_power, time_standing, timestamp, total_ascent, total_calories, total_cycles, total_descent, total_distance, total_elapsed_time, total_fat_calories, total_timer_time, total_work, training_stress_score, trigger = [ None ] * 50 avg_cadence_position = (None, None) avg_left_power_phase = (None, None, None, None) avg_left_power_phase_peak = (None, None, None, None) avg_power_position = (None, None) avg_right_power_phase = (None, None, None, None) avg_right_power_phase_peak = (None, None, None, None) max_cadence_position = (None, None) max_power_position = (None, None) fitfile = FitFile(file2import) enhanced_altitude = [] start_position_lat_degr = None start_position_long_degr = None end_time_gmt_str = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) tz = tzwhere.tzwhere() # Get all data messages that are of type record try: for record in fitfile.get_messages('record'): # Go through all the data entries in this record for record_data in record: # Append altitude values to list if record_data.name == 'enhanced_altitude': enhanced_altitude.append(record_data.value) #Calculate avereage altitude for a better weather data accuracy avg_altitude = int(mean(enhanced_altitude)) fitfile.close except: avg_altitude = None fitfile = FitFile(file2import) # Get all data messages that are of type session for record in fitfile.get_messages('session'): # Go through all the data entries in this record for record_data in record: if record_data.name == 'avg_cadence': avg_cadence = record_data.value if record_data.name == 'avg_cadence_position': avg_cadence_position = record_data.value if record_data.name == 'avg_combined_pedal_smoothness': avg_combined_pedal_smoothness = record_data.value if record_data.name == 'avg_heart_rate': avg_heart_rate = record_data.value if record_data.name == 'avg_left_pco': avg_left_pco = record_data.value if record_data.name == 'avg_left_pedal_smoothness': avg_left_pedal_smoothness = record_data.value if record_data.name == 'avg_left_power_phase': avg_left_power_phase = record_data.value if record_data.name == 'avg_left_power_phase_peak': avg_left_power_phase_peak = record_data.value if record_data.name == 'avg_left_torque_effectiveness': avg_left_torque_effectiveness = record_data.value if record_data.name == 'avg_power': avg_power = record_data.value if record_data.name == 'avg_power_position': avg_power_position = record_data.value if record_data.name == 'avg_right_pco': avg_right_pco = record_data.value if record_data.name == 'avg_right_pedal_smoothness': avg_right_pedal_smoothness = record_data.value if record_data.name == 'avg_right_power_phase': avg_right_power_phase = record_data.value if record_data.name == 'avg_right_power_phase_peak': avg_right_power_phase_peak = record_data.value if record_data.name == 'avg_right_torque_effectiveness': avg_right_torque_effectiveness = record_data.value if record_data.name == 'avg_speed': avg_speed = record_data.value if record_data.name == 'enhanced_avg_speed': enhanced_avg_speed = record_data.value if record_data.name == 'enhanced_max_speed': enhanced_max_speed = record_data.value if record_data.name == 'event': event = record_data.value if record_data.name == 'event_group': event_group = record_data.value if record_data.name == 'event_type': event_type = record_data.value if record_data.name == 'first_lap_index': first_lap_index = record_data.value if record_data.name == 'intensity_factor': intensity_factor = record_data.value if record_data.name == 'left_right_balance': left_right_balance = record_data.value if record_data.name == 'max_cadence': max_cadence = record_data.value if record_data.name == 'max_cadence_position': max_cadence_position = record_data.value if record_data.name == 'max_heart_rate': max_heart_rate = record_data.value if record_data.name == 'max_power': max_power = record_data.value if record_data.name == 'max_power_position': max_power_position = record_data.value if record_data.name == 'max_speed': max_speed = record_data.value if record_data.name == 'message_index': message_index = record_data.value if record_data.name == 'nec_lat': nec_lat = record_data.value if record_data.name == 'nec_long': nec_long = record_data.value if record_data.name == 'normalized_power': normalized_power = record_data.value if record_data.name == 'num_laps': num_laps = record_data.value if record_data.name == 'sport': sport = record_data.value if record_data.name == 'stand_count': stand_count = record_data.value if record_data.name == 'start_position_lat': start_position_lat = record_data.value if start_position_lat is not None: #Convert semicircles to degrees start_position_lat_degr = start_position_lat * (180. / (2**31)) else: start_position_lat_degr = None if record_data.name == 'start_position_long': start_position_long = record_data.value if start_position_long is not None: #Convert semicircles to degrees start_position_long_degr = start_position_long * (180. / (2**31)) else: start_position_long_degr = None if record_data.name == 'sub_sport': sub_sport = record_data.value if record_data.name == 'swc_lat': swc_lat = record_data.value if record_data.name == 'swc_long': swc_long = record_data.value if record_data.name == 'threshold_power': threshold_power = record_data.value if record_data.name == 'time_standing': time_standing = record_data.value if record_data.name == 'timestamp': #Activity end time timestamp = record_data.value if record_data.name == 'total_ascent': total_ascent = record_data.value if record_data.name == 'total_calories': total_calories = record_data.value if record_data.name == 'total_cycles': total_cycles = record_data.value if record_data.name == 'total_descent': total_descent = record_data.value if record_data.name == 'total_distance': total_distance = record_data.value if record_data.name == 'total_elapsed_time': total_elapsed_time = record_data.value if record_data.name == 'total_fat_calories': total_fat_calories = record_data.value if record_data.name == 'total_timer_time': total_timer_time = record_data.value if record_data.name == 'total_work': total_work = record_data.value if record_data.name == 'training_stress_score': training_stress_score = record_data.value if record_data.name == 'trigger': trigger = record_data.value try: #Get timezone from lat/long timezone = None local_time_str = None if start_position_lat_degr is not None and start_position_long_degr is not None: timezone = tz.tzNameAt(start_position_lat_degr, start_position_long_degr) local_tz = pytz.timezone(timezone) gmt_time_dt = datetime.datetime.strptime((str(timestamp)), "%Y-%m-%d %H:%M:%S") #Calculate activity_end time activity_duration_dt = datetime.timedelta( 0, int(total_elapsed_time)) start_time_gmt_dt = gmt_time_dt - activity_duration_dt start_time_gmt_str = datetime.datetime.strftime( (start_time_gmt_dt), "%Y-%m-%d %H:%M:%S") #Get local time from activity start time(gmt) local_dt = start_time_gmt_dt.replace( tzinfo=pytz.utc).astimezone(local_tz) local_dt_norm = local_tz.normalize(local_dt) local_time_str = datetime.datetime.strftime( (local_dt_norm), "%Y-%m-%d %H:%M:%S") except Exception as e: with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) pass sql = """ INSERT INTO garmin_connect_original_session (avg_cadence,avg_cadence_position,avg_combined_pedal_smoothness,avg_heart_rate,avg_left_pco, avg_left_pedal_smoothness,avg_left_power_phase,avg_left_power_phase_peak,avg_left_torque_effectiveness,avg_power,avg_power_position,avg_right_pco, avg_right_pedal_smoothness,avg_right_power_phase,avg_right_power_phase_peak,avg_right_torque_effectiveness,avg_speed,enhanced_avg_speed, enhanced_max_speed,event,event_group,event_type, first_lap_index,intensity_factor,left_right_balance,max_cadence,max_cadence_position, max_heart_rate, max_power,max_power_position,max_speed,message_index,nec_lat,nec_long,normalized_power,num_laps,sport,stand_count,start_position_lat, start_position_long,start_time,sub_sport,swc_lat,swc_long,threshold_power,time_standing,timestamp,total_ascent,total_calories,total_cycles, total_descent,total_distance,total_elapsed_time,total_fat_calories,total_timer_time,total_work,training_stress_score,trigger,gc_activity_id,athlete_id) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,(select id from athlete where ath_un=%s)) ON CONFLICT (gc_activity_id) DO NOTHING; """ sql_timezone = """ INSERT INTO timezones(gc_activity_id,strava_activity_id,timestamp_local,timestamp_gmt,timezone,long_degr,lat_degr,alt_avrg,end_time_gmt) VALUES (%s,null,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (timestamp_gmt,long_degr,lat_degr) DO NOTHING; """ sql_insert_utc_offset = """ INSERT INTO gmt_local_time_difference (athlete_id,local_date,local_midnight_timestamp,gmt_midnight_timestamp,gmt_local_difference) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s) ON CONFLICT (local_date) DO NOTHING; """ try: #Insert session data into garmin_connect_original_session table cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) with ProgressStdoutRedirection(ath_un): print(('Inserting session: ' + str(gc_activity_id) + ' with timestamp:' + str(timestamp))) cur.execute( sql, (avg_cadence, list(avg_cadence_position), avg_combined_pedal_smoothness, avg_heart_rate, avg_left_pco, avg_left_pedal_smoothness, list(avg_left_power_phase), list(avg_left_power_phase_peak), avg_left_torque_effectiveness, avg_power, list(avg_power_position), avg_right_pco, avg_right_pedal_smoothness, list(avg_right_power_phase), list(avg_right_power_phase_peak), avg_right_torque_effectiveness, avg_speed, enhanced_avg_speed, enhanced_max_speed, event, event_group, event_type, first_lap_index, intensity_factor, left_right_balance, max_cadence, list(max_cadence_position), max_heart_rate, max_power, list(max_power_position), max_speed, message_index, nec_lat, nec_long, normalized_power, num_laps, sport, stand_count, start_position_lat, start_position_long, start_time, sub_sport, swc_lat, swc_long, threshold_power, time_standing, timestamp, total_ascent, total_calories, total_cycles, total_descent, total_distance, total_elapsed_time, total_fat_calories, total_timer_time, total_work, training_stress_score, trigger, gc_activity_id, athlete_id)) conn.commit() # close the communication with the PostgreSQL cur.close() if timezone is not None: #Insert timezone and local time into timezones table cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print(('Inserting timezone info: ' + str(timezone) + ' and local tz timestamp:' + str(local_time_str))) with ProgressStdoutRedirection(ath_un): print(('Inserting timezone info: ' + str(timezone) + ' and local tz timestamp:' + str(local_time_str))) cur.execute( sql_timezone, (gc_activity_id, local_time_str, start_time_gmt_str, timezone, start_position_long_degr, start_position_lat_degr, avg_altitude, timestamp)) conn.commit() # close the communication with the PostgreSQL cur.close() #Insert utc offset into gmt_local_time_difference table if the record not already present #get utc offset local_dt_norm = local_dt_norm.replace(tzinfo=None) utc_offset = local_dt_norm - start_time_gmt_dt #get local date local_date = local_dt_norm.date() #get local midnight local_midnight = local_dt_norm.replace(hour=00, minute=00, second=00) gmt_midnight = local_midnight - utc_offset cur = conn.cursor() cur.execute( sql_insert_utc_offset, (ath_un, local_date, local_midnight, gmt_midnight, utc_offset)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) # close the communication with the PostgreSQL if conn is not None: conn.close() with StdoutRedirection(ath_un): print(('--- All summary data for session: ' + str(gc_activity_id) + ' inserted successfully. ---')) with ProgressStdoutRedirection(ath_un): print(('--- All summary data for session: ' + str(gc_activity_id) + ' inserted successfully. ---'))
def gc_wellness_insert(file_path, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): db_name = (db_name) conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) #Create empty dataframe using sorted db fields as headers. This will be converted to empty dataframe and merged with xml dataframe db_fields_list = { 'athlete_id', 'calendar_date', 'wellness_min_heart_rate', 'wellness_total_steps_goal', 'sleep_sleep_duration', 'wellness_floors_descended', 'wellness_floors_ascended', 'wellness_bodybattery_drained', 'wellness_max_heart_rate', 'wellness_total_distance', 'wellness_vigorous_intensity_minutes', 'food_calories_consumed', 'wellness_user_intensity_minutes_goal', 'wellness_common_active_calories', 'food_calories_remaining', 'wellness_moderate_intensity_minutes', 'wellness_bmr_calories', 'common_total_distance', 'wellness_active_calories', 'wellness_total_calories', 'wellness_resting_heart_rate', 'wellness_max_stress', 'wellness_average_steps', 'wellness_max_avg_heart_rate', 'wellness_abnormalhr_alerts_count', 'wellness_total_steps', 'wellness_average_stress', 'common_total_calories', 'wellness_user_floors_ascended_goal', 'wellness_min_avg_heart_rate', 'wellness_bodybattery_charged' } #Sort alphabeticaly db_fields_list = sorted(db_fields_list) db_df = pd.DataFrame(columns=db_fields_list) #List to store XML tags for xml_df column names column_list = [] #List to store XML values in xml_df dataframe and used as params for SQL insert/update query xml_list = [] xml_list.append(ath_un) column_list.append('athlete_id') #Parse the XML document, and append the data to column_list and xml_list lists. root = parse(file_path).getroot() for startDate in root.iter('statisticsStartDate'): date = startDate.text xml_list.append(date) column_list.append('calendar_date') for category in root.findall('.//metricsMap/*'): if category.tag != 'SLEEP_SLEEP_DURATION': column_list.append(category.tag.lower()) for tag in root.iter(category.tag): for item in tag.iterfind('item'): xml_list.append(item.findtext('value')) else: column_list.append(category.tag.lower()) for tag in root.iter(category.tag): for item in tag.iterfind('item'): if item.findtext('calendarDate') == date: xml_list.append(item.findtext('value')) #Combine xml_list and column_list in a xml_df dataframe xml_df = pd.DataFrame(xml_list).T # Write in DF and transpose it xml_df.columns = column_list # Update column names xml_df = xml_df.reindex(sorted(xml_df.columns), axis=1) # Sort alphabeticaly #Combine the dataframes combined_df = db_df.append(xml_df) #Drop all columns that do not exist as DB fields combined_df = combined_df[db_fields_list] #Export all values to list df2list = combined_df.values.tolist() #Flatten the list df2list = [item for sublist in df2list for item in sublist] #Build a list of parameters to pass to sql query query_params = df2list + df2list query_params.append(date) # PG Amend path for postgres "pg_read_file()" function. text = file_path head, sep, tail = text.partition('temp/') pg_read_file = (tail, ) list_sql = """ INSERT INTO garmin_connect_wellness (athlete_id, calendar_date, common_total_calories, common_total_distance, food_calories_consumed, food_calories_remaining, sleep_duration, wellness_abnormalhr_alerts_count, wellness_active_calories, wellness_average_steps, wellness_average_stress, wellness_bmr_calories, wellness_body_battery_drained, wellness_bodybattery_charged, wellness_common_active_calories, wellness_floors_ascended, wellness_floors_descended, wellness_max_avg_heart_rate, wellness_max_heart_rate, wellness_max_stress, wellness_min_avg_heart_rate, wellness_min_heart_rate, wellness_moderate_intensity_minutes, wellness_resting_heart_rate, wellness_total_calories, wellness_total_distance, wellness_total_steps, wellness_total_steps_goal, wellness_user_floors_ascended_goal, wellness_user_intensity_goal, wellness_vigorous_intensity_minutes) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (calendar_date) DO UPDATE SET (athlete_id, calendar_date, common_total_calories, common_total_distance, food_calories_consumed, food_calories_remaining, sleep_duration, wellness_abnormalhr_alerts_count, wellness_active_calories, wellness_average_steps, wellness_average_stress, wellness_bmr_calories, wellness_body_battery_drained, wellness_bodybattery_charged, wellness_common_active_calories, wellness_floors_ascended, wellness_floors_descended, wellness_max_avg_heart_rate, wellness_max_heart_rate, wellness_max_stress, wellness_min_avg_heart_rate, wellness_min_heart_rate, wellness_moderate_intensity_minutes, wellness_resting_heart_rate, wellness_total_calories, wellness_total_distance, wellness_total_steps, wellness_total_steps_goal, wellness_user_floors_ascended_goal, wellness_user_intensity_goal, wellness_vigorous_intensity_minutes) = ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) WHERE garmin_connect_wellness.calendar_date = %s; """ try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with StdoutRedirection(ath_un): print('Inserting Wellness Data into postgreSQL:') with ProgressStdoutRedirection(ath_un): print('Inserting Wellness Data into postgreSQL:') #cur.execute(xpath_sql,(athlete_id,pg_read_file,athlete_id,file2import,file2import,file2import)) cur.execute(list_sql, (query_params)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() with StdoutRedirection(ath_un): print('Wellness Data Inserted Successfully') with ProgressStdoutRedirection(ath_un): print('Wellness Data Inserted Successfully')
def restore_db_schema(ath_un, db_host, db_name, superuser_un, superuser_pw): conn = None username = (ath_un) db_name = (db_name) head, sep, tail = username.partition('@') db_username = head #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) #PG: Read SQL query to restore db schema from file sql_file = open('db_schema.sql', 'r') sql_restore_db_schema = s = " ".join(sql_file.readlines()) #PG: Read SQL query to create 1min data view from file sql_file_view_streams = open('db_create_view_streams.sql', 'r') sql_create_view_streams = s = " ".join(sql_file_view_streams.readlines()) #PG: Read SQL query to create 1day data view from file sql_file_view_summary = open('db_create_view_summary.sql', 'r') sql_create_view_summary = s = " ".join(sql_file_view_summary.readlines()) sql_grant_userpriv = "GRANT ALL PRIVILEGES ON DATABASE \"" + db_name + "\" to \"" + db_username + "\";" sql_grant_table_permissions = "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"" + db_username + "\";" sql_revoke_public = "REVOKE ALL ON DATABASE \"" + db_name + "\" FROM PUBLIC;" sql_revoke_public_1 = "REVOKE ALL ON DATABASE postgres FROM PUBLIC;" try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) conn.autocommit = True # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Restoring DB schema...') cur.execute(sql_restore_db_schema) cur.execute(sql_create_view_streams) cur.execute(sql_create_view_summary) cur.execute(sql_grant_userpriv) cur.execute(sql_grant_table_permissions) cur.execute(sql_revoke_public) cur.execute(sql_revoke_public_1) # close the communication with the PostgreSQL cur.close() sql_file.close() sql_file_view_streams.close() sql_create_view_summary.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def cstm_data_insert(ath_un, usr_params_str, usr_params_int, db_host, superuser_un, superuser_pw): cstm_dbx_link = usr_params_str[0] download_folder = usr_params_str[1] download_folder = os.path.join(download_folder, 'CSTM_Temp_CSV') archive_folder = os.path.join(download_folder, 'Archive') table_name = usr_params_str[2] date_format = usr_params_str[3] timezone = usr_params_str[4] datetime_clmn = usr_params_str[5] unique = usr_params_int dataframes = [] # Download the csv file from dropbox using share link with StdoutRedirection(ath_un): print('Downloading custom csv files from: {}'.format(cstm_dbx_link)) with ProgressStdoutRedirection(ath_un): print('Downloading custom csv files from: {}'.format(cstm_dbx_link)) cstm_dbx_link = cstm_dbx_link[:-1] + '1' # Replace the 0 at the end of the link with 1.Will download as .zip file. table_name = 'cstm_{}'.format(table_name) response = urllib.request.urlopen(cstm_dbx_link) sniffer = csv.Sniffer() db_name = str(str2md5(ath_un)) + '_Athlete_Data_DB' archive_params = config(filename="encrypted_settings.ini", section="archive") preserve_files = str(archive_params.get("preserve_files")) #Insert usr_params to athlete table.. cstm_user_insert(ath_un, usr_params_str, usr_params_int, table_name, db_host, db_name, superuser_un, superuser_pw) conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) sql_select_athlete_id = """ SELECT id FROM athlete WHERE ath_un = %s; """ # Extract the downloaded file in to variable downloadFile = io.BytesIO() downloadFile.write(response.read()) downloadFile.seek(0) #Move pointer to the beginning # Create output directory (if it does not already exist). if not os.path.exists(download_folder): os.makedirs(download_folder) # Create Archive directory (if it does not already exist). if preserve_files == "true": if not os.path.exists(archive_folder): os.makedirs(archive_folder) #Insert the data from csv to dataframe if zipfile.is_zipfile(downloadFile): with zipfile.ZipFile(downloadFile, 'r') as z: #List the content of the zip file zipfile_content = z.namelist() #Iterate through list and only extract files for item in zipfile_content: z.extract(item, download_folder) downloadFile.close() for filename in os.listdir(download_folder): if os.path.isfile(os.path.join(download_folder, filename)): #Detect whether the csv file has a header. with open(os.path.join(download_folder, filename), 'r') as f: has_header = sniffer.has_header(f.read()) if has_header: f.seek(0) #Move pointer to the beginning data_count = csv_match_columns2data(ath_un, f) df = pd.read_csv(os.path.join(download_folder, filename), usecols=data_count) else: df = pd.read_csv(os.path.join(download_folder, filename), header=None) dataframes.append(df) f.close() #Archive csv files if preserve_files == "false": #Remove the csv file from download folder os.remove(os.path.join(download_folder, filename)) with StdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB.' .format(filename))) with ProgressStdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB.' .format(filename))) else: #Move the csv to archive folder and remove from download if os.path.exists(os.path.join(archive_folder, filename)): #Replace the existing file with the updated one os.remove(os.path.join(archive_folder, filename)) os.rename(os.path.join(download_folder, filename), os.path.join(archive_folder, filename)) with StdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB and the original csv file archived' .format(filename))) with ProgressStdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB and the original csv file archived' .format(filename))) else: #Remove the csv file from download folder os.remove(os.path.join(download_folder, filename)) else: filename = re.search(r'([^\/]+)\?.*$', cstm_dbx_link).group( 1) #Exctract the filename from the link downloadFile.seek(0) #Move pointer to the beginning #Detect whether the csv file has a header. wrapper = io.TextIOWrapper(downloadFile, encoding='utf-8') has_header = sniffer.has_header(wrapper.read()) downloadFile.seek(0) #Move pointer to the beginning if has_header: data_count = csv_match_columns2data(ath_un, wrapper) downloadFile.seek(0) #Move pointer to the beginning df = pd.read_csv(downloadFile, usecols=data_count) else: df = pd.read_csv(downloadFile, header=None) #Archive downloaded File if preserve_files == "true": downloadFile.seek(0) #Move pointer to the beginning with open(os.path.join(archive_folder, filename), 'wb') as f: f.write(downloadFile.read()) with StdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB and the original csv file archived' .format(filename))) with ProgressStdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB and the original csv file archived' .format(filename))) else: with StdoutRedirection(ath_un): print(('The content of \"{}\" parsed and inserted into DB.'. format(filename))) with ProgressStdoutRedirection(ath_un): print(('The content of \"{}\" parsed and inserted into DB.'. format(filename))) wrapper.close() dataframes.append(df) downloadFile.close() #Remove duplicates dataframes = pd.concat(dataframes).drop_duplicates().reset_index(drop=True) #---- Parse and format datetime values ---- cur = conn.cursor() #Iterate through values in df datetime column if datetime_clmn is not None: datetime_clmn = int(datetime_clmn) #set how to to interpret the the first value in an ambiguous 3-integer date #eu date format dd/mm/yyyy if date_format == "eu": first_val = "day" #us date format mm/dd/yyyy else: first_val = 'month' #add timestamp_gmt and timestamp_local columns datetime_clmn_gmt = "timestamp_gmt" datetime_clmn_local = "timestamp_local" dataframes[datetime_clmn_gmt] = np.nan dataframes[datetime_clmn_local] = np.nan item_row_nr = 0 for item in dataframes[dataframes.columns[datetime_clmn]]: timestamps = datefinder.find_dates(str(item), first=first_val) for timestamp in timestamps: #If the timestamp is timezone aware if timestamp.tzinfo is not None: timestamp_utc = timestamp.astimezone(pytz.UTC) timestamp_utc = datetime.datetime.strftime( timestamp_utc, "%Y-%m-%d %H:%M:%S.%f") timestamp_local = datetime.datetime.strftime( timestamp, "%Y-%m-%d %H:%M:%S.%f %Z") #If the timestamp is timezone naive else: local_timezone = pytz.timezone(timezone) timestamp_local_tz = local_timezone.localize(timestamp, is_dst=True) timestamp_utc = timestamp_local_tz.astimezone(pytz.UTC) timestamp_utc = datetime.datetime.strftime( timestamp_utc, "%Y-%m-%d %H:%M:%S.%f") timestamp_local = datetime.datetime.strftime( timestamp_local_tz, "%Y-%m-%d %H:%M:%S.%f %Z") #insert timestamp_utc and timezone_local values to the newly created df columns row_index = dataframes.index[item_row_nr] dataframes.loc[row_index, datetime_clmn_gmt] = timestamp_utc dataframes.loc[row_index, datetime_clmn_local] = timestamp_local item_row_nr += 1 cur.execute(sql_select_athlete_id, (ath_un, )) result = cur.fetchone() conn.commit() ath_un_id = result[0] dataframes['athlete_id'] = ath_un_id cur.close() #format column names to comply with postgres rules dataframes.rename(columns={i: format_column_name(i) for i in df.columns}, inplace=True) #Get list of unique column names from column numbers (suplied by user) and add athlete_id column to the list for i in unique: x = unique.index(i) unique[x] = dataframes.columns[i] unique.append("athlete_id") #Check if the table already exists cur = conn.cursor() cur.execute( "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = '{}');". format(table_name)) result = cur.fetchone() conn.commit() if result[0] is True: with StdoutRedirection(ath_un): print( 'The custom table {} already exists. Will now insert the new data' .format(table_name)) with ProgressStdoutRedirection(ath_un): print( 'The custom table {} already exists. Will now insert the new data' .format(table_name)) pass else: with StdoutRedirection(ath_un): print('Creating custom table: {}'.format(table_name)) with ProgressStdoutRedirection(ath_un): print('Creating custom table: {}'.format(table_name)) cur.execute(gen_tbl_sql(ath_un, dataframes, table_name, unique)) conn.commit() cur.close() insert_df_values(ath_un, conn, dataframes, table_name, unique) # close the communication with the PostgreSQL if conn is not None: conn.close()
def update_autosynch_prefrnc(ath_un, db_host, db_name, superuser_un, superuser_pw, encrypted_superuser_pw, enable_auto_synch, encr_pass): ath_user = (ath_un, ) #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) #Do not save pw to db_info if no autosynch or db_host=localhost if enable_auto_synch == True: if db_host != 'localhost': encrypted_superuser_pw = (encrypted_superuser_pw, ) else: encrypted_superuser_pw = None else: encrypted_superuser_pw = None enable_auto_synch = (enable_auto_synch, ) conn = None sql = """ UPDATE athlete SET auto_sync = %s where ath_un= %s; """ sql_postgres_db = """ UPDATE db_info SET db_auto_synch = %s,db_un = %s,db_pw = %s where db_name= %s; """ #Insert user autosynch preference into user db athlete table try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Inserting Auto_Sych preferences into postgreSQL:') cur.execute(sql, (enable_auto_synch, ath_user)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() #Insert user autosynch preference into postgres/db_info table try: params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") postgres_un = params.get("user") postgres_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=postgres_un, password=postgres_pw) conn_localhost.autocommit = True # create a cursor cur_localhost = conn_localhost.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print( 'Inserting Auto_Sych preferences into postgres/db_info table:') cur_localhost.execute( sql_postgres_db, (enable_auto_synch, superuser_un, encrypted_superuser_pw, db_name)) # close the communication with the PostgreSQL cur_localhost.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn_localhost is not None: conn_localhost.close
def gc_bodycomposition_insert(file_path, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass): """ Connect to the PostgreSQL database server """ athlete_id = (ath_un, ) db_name = (db_name) body_water = [] muscle_mass_gm = [] visceral_fat = [] weight_gm = [] bmi = [] body_fat = [] physique_rating = [] timestamp = [] calendar_date = [] metabolic_age = [] bone_mass_gm = [] caloric_intake = [] source_type = [] conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) # PG Amend path for postgres "pg_read_file()" function. text = file_path head, sep, tail = text.partition('temp/') pg_read_file = (tail, ) sql = """ INSERT INTO garmin_connect_body_composition(athlete_id,body_water,muscle_mass_gm,visceral_fat,weight_gm,bmi, body_fat,physique_rating,timestamp,calendar_date,metabolic_age, bone_mass_gm,caloric_intake,source_type) SELECT (select id from athlete where ath_un=%s), unnest (xpath('//*[local-name()="item"]/*[local-name()="bodyWater"]/text()', x))::text::numeric AS body_water, unnest (xpath('//*[local-name()="item"]/*[local-name()="muscleMass"]/text()', x))::text::int AS muscle_mass_gm, unnest (xpath('//*[local-name()="item"]/*[local-name()="visceralFat"]/text()', x))::text::int AS visceral_fat, unnest (xpath('//*[local-name()="item"]/*[local-name()="weight"]/text()', x))::text::numeric AS weight_gm, unnest (xpath('//*[local-name()="item"]/*[local-name()="bmi"]/text()', x))::text::numeric AS bmi, unnest (xpath('//*[local-name()="item"]/*[local-name()="bodyFat"]/text()', x))::text::numeric AS body_fat, unnest (xpath('//*[local-name()="item"]/*[local-name()="physiqueRating"]/text()', x))::text::text AS physique_rating, to_char(to_timestamp(unnest (xpath('//*[local-name()="item"]/*[local-name()="samplePk"]/text()', x))::text::numeric/1000) AT TIME ZONE 'UTC','YYYY-MM-DD HH24:MI:SS') AS timestamp, unnest (xpath('//*[local-name()="item"]/*[local-name()="calendarDate"]/text()', x))::text::text AS calendar_date, unnest (xpath('//*[local-name()="item"]/*[local-name()="metabolicAge"]/text()', x))::text::text AS metabolic_age, unnest (xpath('//*[local-name()="item"]/*[local-name()="boneMass"]/text()', x))::text::int AS bone_mass_gm, unnest (xpath('//*[local-name()="item"]/*[local-name()="caloricIntake"]/text()', x))::text::text AS caloric_intake, unnest (xpath('//*[local-name()="item"]/*[local-name()="sourceType"]/text()', x))::text::text AS source_type FROM UNNEST (xpath('//*[local-name()="root"]', pg_read_file(%s)::xml)) x ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ pd_df_sql = """ INSERT INTO garmin_connect_body_composition(athlete_id,body_water,muscle_mass_gm,visceral_fat,weight_gm,bmi, body_fat,physique_rating,timestamp,calendar_date,metabolic_age, bone_mass_gm,caloric_intake,source_type) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ #Parse the XML document, and append the data to lists xml_document = parse(file_path) for item in xml_document.iterfind('item'): body_water.append(item.findtext('bodyWater')) muscle_mass_gm.append(item.findtext('muscleMass')) visceral_fat.append(item.findtext('visceralFat')) weight_gm.append(item.findtext('weight')) bmi.append(item.findtext('bmi')) body_fat.append(item.findtext('bodyFat')) physique_rating.append(item.findtext('physiqueRating')) timestamp.append( datetime.datetime.utcfromtimestamp( float(item.findtext('samplePk')) / 1000).strftime('%Y-%m-%d %H:%M:%S')) calendar_date.append(item.findtext('calendarDate')) metabolic_age.append(item.findtext('metabolicAge')) bone_mass_gm.append(item.findtext('boneMass')) caloric_intake.append(item.findtext('caloricIntake')) source_type.append(item.findtext('sourceType')) #Get the data from lists to dataframe df = pd.DataFrame({ 'body_water': body_water, 'muscle_mass_gm': muscle_mass_gm, 'visceral_fat': visceral_fat, 'weight_gm': weight_gm, 'bmi': bmi, 'body_fat': body_fat, 'physique_rating': physique_rating, 'timestamp': timestamp, 'calendar_date': calendar_date, 'metabolic_age': metabolic_age, 'bone_mass_gm': bone_mass_gm, 'caloric_intake': caloric_intake, 'source_type': source_type }) #Replace empty string with None.Required for SQL insert df.replace(r'', np.nan, inplace=True) df = df.where(pd.notnull(df), None) try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() with StdoutRedirection(ath_un): print('Inserting Body composition Data into postgreSQL:') with ProgressStdoutRedirection(ath_un): print('Inserting Body composition Data into postgreSQL:') #Iterate through rows in pandas dataframe for row in df.itertuples(): df_body_water = row.body_water df_muscle_mass_gm = row.muscle_mass_gm df_visceral_fat = row.visceral_fat df_weight_gm = row.weight_gm df_bmi = row.bmi df_body_fat = row.body_fat df_physique_rating = row.physique_rating df_timestamp = row.timestamp df_calendar_date = row.calendar_date df_metabolic_age = row.metabolic_age df_bone_mass_gm = row.bone_mass_gm df_caloric_intake = row.caloric_intake df_source_type = row.source_type cur.execute( pd_df_sql, (athlete_id, df_body_water, df_muscle_mass_gm, df_visceral_fat, df_weight_gm, df_bmi, df_body_fat, df_physique_rating, df_timestamp, df_calendar_date, df_metabolic_age, df_bone_mass_gm, df_caloric_intake, df_source_type)) ### Insert using Xpath method ### #cur.execute(xpath_sql,(athlete_id,pg_read_file)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() with StdoutRedirection(ath_un): print('Body composition Data Inserted Successfully')
def get_weather(ath_un,db_host, db_name, superuser_un,superuser_pw,start_date,end_date,encr_pass): sql_timezones_select = ''' SELECT timestamp_gmt,end_time_gmt,long_degr,lat_degr,alt_avrg from timezones WHERE timestamp_gmt::date >= %s and timestamp_gmt::date <= %s ORDER BY timestamp_gmt asc; ''' pd_df_sql = """ INSERT INTO weather(athlete_id,timestamp_gmt,temperature,dew_point,relative_humidity,precipitation,snow,wind_direction,wind_speed,wind_gust,sea_air_pressure,total_sunshine,condition_code) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (athlete_id,timestamp_gmt) DO NOTHING; """ #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) try: conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un,password=superuser_pw) conn.autocommit = True cur = conn.cursor() cur.execute(sql_timezones_select,(start_date,end_date)) long_degr = None lat_degr = None alt_avrg = None row_start_time = None row_end_time = None row_start_time_dt = None row_end_time_dt = None start_time = None end_time = None #Earth radius R = 6373.0 row_nr = 1 combined_df = pd.DataFrame() def meteostat(lat_degr,long_degr,alt_avrg,start_time,end_time): #Retrieve nearest weather station stations = Stations() stations = stations.nearby(lat_degr, long_degr) station = stations.fetch(1) #Use Point to agregate nearby weather stations data for better accuracy weather_point = Point(lat_degr, long_degr,alt_avrg) weather_data = Hourly(weather_point, start_time - timedelta(0,7200), end_time + timedelta(0,7200)) weather_data = weather_data.fetch() #Use weather data from nearest station if Point returns an empty dataset if weather_data.empty: weather_data = Hourly(station, start_time - timedelta(0,7200), end_time + timedelta(0,7200)) weather_data = weather_data.fetch() return weather_data for row in cur: #Retrieve activity start and end times and convert to datetime row_start_time = row[0] row_start_time_dt = datetime.strptime((str(row_start_time)), "%Y-%m-%d %H:%M:%S") row_end_time = row[1] row_end_time_dt = datetime.strptime((str(row_end_time)), "%Y-%m-%d %H:%M:%S") #Retrieve activity average altitude row_alt_avrg = row[4] #This is the first row,set initial values. if row_nr == 1: long_degr = row[2] lat_degr = row[3] start_time = row_start_time_dt end_time = row_end_time_dt #Calculate distance between subsequent activity locations lon1 = math.radians(long_degr) lat1 = math.radians(lat_degr) lon2 = math.radians(row[2]) lat2 = math.radians(row[3]) dlon = lon2 - lon1 dlat = lat2 - lat1 a = math.sin(dlat / 2)**2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon / 2)**2 c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a)) distance = R * c #If subsequent activities within 30km update endtime and move onto next row if distance < 30: end_time = row_end_time_dt #start_time stays the same #move onto next row #If subsequent activities not within 30km, download weather data and reset end and start times else: combined_df = combined_df.append(meteostat(lat_degr,long_degr,alt_avrg,start_time,end_time)) with StdoutRedirection(ath_un): print(('Inserting weather data for period from: ' + str(start_time) + ' until: ' + str(end_time))) with ProgressStdoutRedirection(ath_un): print(('Inserting weather data for period from: ' + str(start_time) + ' until: ' + str(end_time))) start_time = row_start_time_dt end_time = row_end_time_dt #Update variables with values from the current row and move onto the next one long_degr = row[2] lat_degr = row[3] alt_avrg = row[4] #Incrememt the row number variable row_nr = row_nr+1 #There is one more block to be retrieved combined_df = combined_df.append(meteostat(lat_degr,long_degr,alt_avrg,start_time,end_time)) with StdoutRedirection(ath_un): print(('Inserting weather data for period from: ' + str(start_time) + ' until: ' + str(end_time))) with ProgressStdoutRedirection(ath_un): print(('Inserting weather data for period from: ' + str(start_time) + ' until: ' + str(end_time))) cur.close() #Iterate through rows in the consolidated pandas df combined_df = combined_df.reset_index() for row in combined_df.itertuples(): row_timeGMT = row.time row_temp = row.temp row_dwpt = row.dwpt row_rhum = row.rhum row_prcp = row.prcp row_snow = row.snow row_wdir = row.wdir row_wspd = row.wspd row_wpgt = row.wpgt row_pres = row.pres row_tsun = row.tsun row_coco = row.coco ### Insert weather data to weather ### # create a cursor cur = conn.cursor() # execute a statement cur.execute(pd_df_sql,(ath_un,row_timeGMT,row_temp,row_dwpt,row_rhum,row_prcp,row_snow,row_wdir,row_wspd,row_wpgt,row_pres,row_tsun,row_coco)) conn.commit() # close the communication with the PostgreSQL cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) finally: if conn is not None: conn.close
def create_user_db(ath_un, ath_pw, db_host, db_name, superuser_un, superuser_pw, encrypted_superuser_pw, save_pwd, encr_pass): conn = None head, sep, tail = ath_un.partition('@') db_username = head #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) sql_get_lc_collate = "SHOW LC_COLLATE" sql_create_db = "CREATE DATABASE \"" + db_name + "\" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = %s LC_CTYPE = %s;" sql_create_dbuser = "******"" + db_username + "\" WITH PASSWORD \'" + ath_pw + "\';" sql_update_db_info_record = "UPDATE db_info SET db_name = %s, db_host = %s, db_un = %s, db_pw = %s, db_auto_synch = %s WHERE ath_un= %s; " try: conn = psycopg2.connect(dbname='postgres', host=db_host, user=superuser_un, password=superuser_pw) conn.autocommit = True # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Creating user database...') cur.execute(sql_get_lc_collate) lc_collate = cur.fetchone() cur.execute(sql_create_db, (lc_collate[0], lc_collate[0])) cur.execute(sql_create_dbuser) # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close() #Insert user db connection info into postgres/db_info table try: params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") postgres_un = params.get("user") postgres_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=postgres_un, password=postgres_pw) conn_localhost.autocommit = True # create a cursor cur_localhost = conn_localhost.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Inserting DB connection info...') cur_localhost.execute(sql_update_db_info_record, (db_name, db_host, superuser_un, encrypted_superuser_pw, save_pwd, ath_un)) # close the communication with the PostgreSQL cur_localhost.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn_localhost is not None: conn_localhost.close
def dwnld_insert_strava_data(ath_un, db_host, db_name, superuser_un, superuser_pw, strava_refresh_token, start_date_dt, end_date_dt, save_pwd, encr_pass): #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) strava_params = config(filename="encrypted_settings.ini", section="strava", encr_pass=encr_pass) STRAVA_CLIENT_ID = str(strava_params.get("strava_client_id")) STRAVA_CLIENT_SECRET = str(strava_params.get("strava_client_secret")) STRAVA_TOKEN_URL = str(strava_params.get("strava_token_url")) sql_insert_strava_refresh_token = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET strava_refresh_token = %s where ath_un= %s; END IF; END $do$ """ sql_insert_activity_summary = """ INSERT INTO strava_activity_summary(athlete_id,strava_activity_id,strava_athlete_id,name,distance,moving_time,elapsed_time,total_elevation_gain,type, workout_type,external_id,upload_id,start_date,start_date_local,timezone,utc_offset,start_latitude,start_longitude,end_latitude,end_longitude,location_city, location_state,location_country,map,summary_polyline,trainer,commute,manual,gear_id,average_speed,max_speed,average_cadence,average_temp,average_watts, weighted_average_watts,kilojoules,device_watts,average_heartrate,max_heartrate,max_watts,elev_high,elev_low,suffer_score) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (start_date) DO NOTHING; """ sql_insert_activity_streams = """ INSERT INTO strava_activity_streams(activity_id,altitude,cadence,distance,grade_smooth,heartrate,latitude,longitude,moving,temp,time_gmt,velocity_smooth,watts) VALUES ((select id from strava_activity_summary where strava_activity_id=%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (time_gmt) DO NOTHING; """ sql_insert_utc_offset = """ INSERT INTO gmt_local_time_difference (athlete_id,local_date,local_midnight_timestamp,gmt_midnight_timestamp,gmt_local_difference) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s) ON CONFLICT (local_date) DO NOTHING; """ sql_timezone = """ INSERT INTO timezones(gc_activity_id,strava_activity_id,timestamp_local,timestamp_gmt,timezone,long_degr,lat_degr,alt_avrg,end_time_gmt) VALUES (null,(select id from strava_activity_summary where strava_activity_id=%s),%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (timestamp_gmt,long_degr,lat_degr) DO NOTHING; """ conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) def refresh_oauth_tokens(): #Refresh the access token payload = dict(grant_type='refresh_token', refresh_token=strava_refresh_token, client_id=STRAVA_CLIENT_ID, client_secret=STRAVA_CLIENT_SECRET) refresh = requests.post(STRAVA_TOKEN_URL, data=payload) response = refresh.json() oauth_token = response['access_token'] refresh_token = response['refresh_token'] expires_at = response['expires_at'] if save_pwd == True: encrypted_refresh_token = base64.b64encode( encrypt(refresh_token, encr_pass)) encrypted_refresh_token = encrypted_refresh_token.decode('utf-8') else: encrypted_refresh_token = None try: cur = conn.cursor() cur.execute(sql_insert_strava_refresh_token, (ath_un, encrypted_refresh_token, ath_un)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print( (str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) return oauth_token, expires_at oauth_token, expires_at = refresh_oauth_tokens() header = {'Authorization': 'Bearer {}'.format(oauth_token)} epoch_start_date = start_date_dt.timestamp() epoch_end_date = end_date_dt.timestamp() #Loop through all activities page = 1 activities_url = "https://www.strava.com/api/v3/activities" while True: # Retry 3 times if request fails tries = 3 for i in range(tries): try: #Check if the oauth token is stil valid, and refresh if not. if int(datetime.now().timestamp()) > expires_at: oauth_token, expires_at = refresh_oauth_tokens() header = {'Authorization': 'Bearer {}'.format(oauth_token)} with ProgressStdoutRedirection(ath_un): print( str(datetime.now()) + 'The Strava oauth token expired and has been refreshed' ) # get page of 200 activities from Strava r = requests.get( '{}?before={}&after={}&page={}&per_page=200'.format( activities_url, epoch_end_date, epoch_start_date, str(page)), headers=header) sleep_sec = api_rate_limits(r) except Exception as e: if i < tries - 1: time.sleep(10) continue else: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) raise break if sleep_sec == 0: pass else: time.sleep(sleep_sec) pass # if no results then exit loop if (not r.json()): if conn is not None: conn.close() with StdoutRedirection(ath_un): print('Strava Activities Inserted Successfully') with ProgressStdoutRedirection(ath_un): print('Strava Activities Inserted Successfully') break #Do something with the response/data activity_data = json.loads(r.text) #assign activity values to variables for activity in activity_data: if "id" in activity: strava_activity_id = activity['id'] #PG: Check whether the data for this activity have been inserted into to DB during one of the previous runs data_exist_for_activity = 'Strava activity {} data'.format( strava_activity_id) data_exists = check_data_file_exists(data_exist_for_activity, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) if data_exists == True: with StdoutRedirection(ath_un): print(( 'Strava activity {} data already downloaded and inserted to DB. Skipping.' .format(strava_activity_id))) with ProgressStdoutRedirection(ath_un): print(( 'Strava activity {} data already downloaded and inserted to DB. Skipping' .format(strava_activity_id))) continue else: strava_activity_id = None if "id" in activity['athlete']: strava_athlete_id = activity['athlete']['id'] else: strava_athlete_id = None if "name" in activity: name = activity['name'] else: name = None if "distance" in activity: distance = activity['distance'] else: distance = None if "moving_time" in activity: moving_time = activity['moving_time'] else: moving_time = None if "elapsed_time" in activity: elapsed_time = activity['elapsed_time'] else: elapsed_time = None if "total_elevation_gain" in activity: total_elevation_gain = activity['total_elevation_gain'] else: total_elevation_gain = None if "type" in activity: type = activity['type'] else: type = None if "workout_type" in activity: workout_type = activity['workout_type'] else: workout_type = None if "external_id" in activity: external_id = activity['external_id'] else: external_id = None if "upload_id" in activity: upload_id = activity['upload_id'] else: upload_id = None if "start_date" in activity: start_date = activity['start_date'] start_date_dt = datetime.strptime(start_date, '%Y-%m-%dT%H:%M:%SZ') start_date = datetime.strftime(start_date_dt, '%Y-%m-%d %H:%M:%S') else: start_date = None if "start_date_local" in activity: start_date_local = activity['start_date_local'] start_date_local_dt = datetime.strptime( start_date_local, '%Y-%m-%dT%H:%M:%SZ') start_date_local = datetime.strftime(start_date_local_dt, '%Y-%m-%d %H:%M:%S') else: start_date_local = None if "timezone" in activity: timezone = activity['timezone'] else: timezone = None if "utc_offset" in activity: utc_offset = activity['utc_offset'] else: utc_offset = None if "start_latlng" in activity: start_latlng = activity['start_latlng'] #if start_latlng is not None: #Changed 2021/10/29 if start_latlng: start_latitude = start_latlng[0] start_longitude = start_latlng[1] else: start_latitude = None start_longitude = None else: start_latitude = None start_longitude = None if "end_latlng" in activity: end_latlng = activity['end_latlng'] #if end_latlng is not None: #Changed 2021/10/29 if end_latlng: end_latitude = end_latlng[0] end_longitude = end_latlng[1] else: end_latitude = None end_longitude = None else: end_latitude = None end_longitude = None if "location_city" in activity: location_city = activity['location_city'] else: location_city = None if "location_state" in activity: location_state = activity['location_state'] else: location_state = None if "location_country" in activity: location_country = activity['location_country'] else: location_country = None if "id" in activity['map']: map = activity['map']['id'] else: map = None if "summary_polyline" in activity['map']: summary_polyline = activity['map']['summary_polyline'] else: summary_polyline = None if "trainer" in activity: trainer = activity['trainer'] else: trainer = None if "commute" in activity: commute = activity['commute'] else: commute = None if "manual" in activity: manual = activity['manual'] else: manual = None if "gear_id" in activity: gear_id = activity['gear_id'] else: gear_id = None if "average_speed" in activity: average_speed = activity['average_speed'] else: average_speed = None if "max_speed" in activity: max_speed = activity['max_speed'] else: max_speed = None if "average_cadence" in activity: average_cadence = activity['average_cadence'] else: average_cadence = None if "average_temp" in activity: average_temp = activity['average_temp'] else: average_temp = None if "average_watts" in activity: average_watts = activity['average_watts'] else: average_watts = None if "weighted_average_watts" in activity: weighted_average_watts = activity['weighted_average_watts'] else: weighted_average_watts = None if "kilojoules" in activity: kilojoules = activity['kilojoules'] else: kilojoules = None if "device_watts" in activity: device_watts = activity['device_watts'] else: device_watts = None if "average_heartrate" in activity: average_heartrate = activity['average_heartrate'] else: average_heartrate = None if "max_heartrate" in activity: max_heartrate = activity['max_heartrate'] else: max_heartrate = None if "max_watts" in activity: max_watts = activity['max_watts'] else: max_watts = None if "elev_high" in activity: elev_high = activity['elev_high'] else: elev_high = None if "elev_low" in activity: elev_low = activity['elev_low'] else: elev_low = None if "suffer_score" in activity: suffer_score = activity['suffer_score'] else: suffer_score = None with StdoutRedirection(ath_un): print('Downloading Strava activity {} from: {}'.format( strava_activity_id, start_date_local)) with ProgressStdoutRedirection(ath_un): print('Downloading Strava activity {} from: {}'.format( strava_activity_id, start_date_local)) #Insert activity summary data to DB try: cur = conn.cursor() cur.execute( sql_insert_activity_summary, (ath_un, strava_activity_id, strava_athlete_id, name, distance, moving_time, elapsed_time, total_elevation_gain, type, workout_type, external_id, upload_id, start_date, start_date_local, timezone, utc_offset, start_latitude, start_longitude, end_latitude, end_longitude, location_city, location_state, location_country, map, summary_polyline, trainer, commute, manual, gear_id, average_speed, max_speed, average_cadence, average_temp, average_watts, weighted_average_watts, kilojoules, device_watts, average_heartrate, max_heartrate, max_watts, elev_high, elev_low, suffer_score)) conn.commit() cur.close() # Update the files table data_file_path_insert(data_exist_for_activity, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) #Insert utc offset into gmt_local_time_difference table if the record not already present from gc gmt_local_difference = timedelta(seconds=utc_offset) local_date = start_date_local_dt.date() local_midnight = start_date_local_dt.replace(hour=00, minute=00, second=00) gmt_midnight = local_midnight - gmt_local_difference cur = conn.cursor() cur.execute(sql_insert_utc_offset, (ath_un, local_date, local_midnight, gmt_midnight, gmt_local_difference)) conn.commit() cur.close() #Insert timezone info into timezones table if the record not already present from gc if start_latitude is not None: #Calculate avereage altitude for a better weather data accuracy avg_altitude = int((elev_high + elev_low) / 2) #Drop (GMT+hh:mm) from timezone string timezone = re.sub("[(].*[) ]", "", timezone) #Calculate end_time_gmt_str end_time_gmt_dt = start_date_dt + timedelta( seconds=elapsed_time) end_time_gmt_str = datetime.strftime((end_time_gmt_dt), "%Y-%m-%d %H:%M:%S") cur = conn.cursor() with StdoutRedirection(ath_un): print(('Inserting timezone info: ' + str(timezone) + ' and local tz timestamp:' + str(local_date))) with ProgressStdoutRedirection(ath_un): print(('Inserting timezone info: ' + str(timezone) + ' and local tz timestamp:' + str(local_date))) cur.execute( sql_timezone, (strava_activity_id, start_date_local, start_date, timezone, start_longitude, start_latitude, avg_altitude, end_time_gmt_str)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) #Get all available streams for the activity types = 'time,distance,latlng,altitude,velocity_smooth,heartrate,cadence,watts,temp,moving,grade_smooth' df_columns = { 'activity_id', 'time_gmt', 'distance', 'latitude', 'longitude', 'altitude', 'velocity_smooth', 'heartrate', 'cadence', 'watts', 'temp', 'moving', 'grade_smooth' } df_columns = sorted(df_columns) # Retry 3 times if request fails tries = 3 for i in range(tries): try: #Check if the oauth token is stil valid, and refresh if not. if int(datetime.now().timestamp()) > expires_at: oauth_token, expires_at = refresh_oauth_tokens() header = { 'Authorization': 'Bearer {}'.format(oauth_token) } with ProgressStdoutRedirection(ath_un): print( str(datetime.now()) + 'The Strava oauth token expired and has been refreshed' ) streams = requests.get( "https://www.strava.com/api/v3/activities/{}/streams?keys={}" .format(strava_activity_id, types), headers=header) sleep_sec = api_rate_limits(streams) except Exception as e: if i < tries - 1: time.sleep(10) continue else: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) raise break if sleep_sec == 0: pass else: time.sleep(sleep_sec) pass #TODO: if sleep_sec > 6hrs request a new access_token #Do something with the response/data activity_streams = json.loads(streams.text) #Check if there is data in activity_streams if 'message' in activity_streams: if "Resource Not Found" in activity_streams['message']: with ErrorStdoutRedirection(ath_un): print( str(datetime.now()) + ' No streams retrieved for {}. Moving onto next activity.' .format(strava_activity_id)) continue if "Rate Limit Exceeded" in activity_streams['message']: with ErrorStdoutRedirection(ath_un): print( str(datetime.now()) + ' Download Rate limit exceeded. This attempt did not get caught by api_rate_limits() function!' ) #Usualy due to failed request on a very last attempt before limit, and subsequent succesfull retry.Too messy, move onto next activity and see what happens. continue else: pass activity_streams_df = pd.DataFrame(columns=df_columns) for stream in activity_streams: if "time" in stream['type']: activity_id_list = [] time_stream = stream['data'] for i in range(len(time_stream)): time_stream[i] = start_date_dt + timedelta( 0, int(time_stream[i])) time_stream[i] = datetime.strftime( time_stream[i], '%Y-%m-%d %H:%M:%S') activity_id_list.append(strava_activity_id) activity_streams_df['activity_id'] = activity_id_list activity_streams_df['time_gmt'] = time_stream if "distance" in stream['type']: distance_stream = stream['data'] activity_streams_df['distance'] = distance_stream if "latlng" in stream['type']: latlng_stream = stream['data'] latitude = [] longitude = [] if latlng_stream is not None: for i in range(len(latlng_stream)): latitude.append(latlng_stream[i][0]) longitude.append(latlng_stream[i][1]) activity_streams_df['latitude'] = latitude activity_streams_df['longitude'] = longitude if "altitude" in stream['type']: altitude_stream = stream['data'] activity_streams_df['altitude'] = altitude_stream if "velocity_smooth" in stream['type']: velocity_smooth_stream = stream['data'] activity_streams_df[ 'velocity_smooth'] = velocity_smooth_stream if "heartrate" in stream['type']: heartrate_stream = stream['data'] activity_streams_df['heartrate'] = heartrate_stream if "cadence" in stream['type']: cadence_stream = stream['data'] activity_streams_df['cadence'] = cadence_stream if "watts" in stream['type']: watts_stream = stream['data'] activity_streams_df['watts'] = watts_stream if "temp" in stream['type']: temp_stream = stream['data'] activity_streams_df['temp'] = temp_stream if "moving" in stream['type']: moving_stream = stream['data'] activity_streams_df['moving'] = moving_stream if "grade_smooth" in stream['type']: grade_smooth_stream = stream['data'] activity_streams_df['grade_smooth'] = grade_smooth_stream activity_streams_df = activity_streams_df.reindex( sorted(activity_streams_df.columns), axis=1) # Sort df alphabeticaly activity_streams_df = activity_streams_df.where( pd.notnull(activity_streams_df), None) # Replace NaN with None df2list = activity_streams_df.values.tolist() #Insert activity streams data to DB try: cur = conn.cursor() for sublist in df2list: cur.execute(sql_insert_activity_streams, (sublist)) conn.commit() cur.close() except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) # increment page page += 1
def user_tokens_insert(ath_un, db_host, db_name, superuser_un, superuser_pw, dbx_auth_token, oura_refresh_token, strava_refresh_token, encr_pass, save_pwd): if save_pwd == True: #Encrypt dbx token if dbx_auth_token is not None: encrypted_dbx_auth_token = base64.b64encode( encrypt(dbx_auth_token, encr_pass)) encrypted_dbx_auth_token = encrypted_dbx_auth_token.decode('utf-8') else: encrypted_dbx_auth_token = None #Encrypt oura token if oura_refresh_token is not None: encrypted_oura_refresh_token = base64.b64encode( encrypt(oura_refresh_token, encr_pass)) encrypted_oura_refresh_token = encrypted_oura_refresh_token.decode( 'utf-8') else: encrypted_oura_refresh_token = None #Encrypt strava token if strava_refresh_token is not None: encrypted_strava_refresh_token = base64.b64encode( encrypt(strava_refresh_token, encr_pass)) encrypted_strava_refresh_token = encrypted_strava_refresh_token.decode( 'utf-8') else: encrypted_strava_refresh_token = None else: encrypted_dbx_auth_token = None encrypted_oura_refresh_token = None encrypted_strava_refresh_token = None #Query params lists ath_user = (ath_un, ) auth_token_tuple = (encrypted_dbx_auth_token, ) oura_token_tuple = (encrypted_oura_refresh_token, ) strava_token_tuple = (encrypted_strava_refresh_token, ) conn = None #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) sql = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET ath_un = %s where ath_un = %s; ELSE INSERT INTO athlete (ath_un) VALUES (%s); END IF; END $do$ """ sql_insert_dbx_auth_token = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET dropbox_access_token = %s where ath_un= %s; END IF; END $do$ """ sql_insert_oura_refresh_token = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET oura_refresh_token = %s where ath_un= %s; END IF; END $do$ """ sql_insert_strava_refresh_token = """ DO $do$ BEGIN IF EXISTS (SELECT id FROM athlete WHERE ath_un = %s) THEN UPDATE athlete SET strava_refresh_token = %s where ath_un= %s; END IF; END $do$ """ try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) # create a cursor cur = conn.cursor() # execute a statement with ProgressStdoutRedirection(ath_un): print('Inserting GC User Data into postgreSQL:') cur.execute(sql, (ath_user, ath_user, ath_user, ath_user)) conn.commit() if dbx_auth_token is not None: with ProgressStdoutRedirection(ath_un): print( 'Inserting Dropbox user authentication token into postgreSQL:' ) cur.execute(sql_insert_dbx_auth_token, (ath_user, auth_token_tuple, ath_user)) conn.commit() if oura_refresh_token is not None: with ProgressStdoutRedirection(ath_un): print('Inserting Oura refresh token into postgreSQL:') cur.execute(sql_insert_oura_refresh_token, (ath_user, oura_token_tuple, ath_user)) conn.commit() if strava_refresh_token is not None: with ProgressStdoutRedirection(ath_un): print('Inserting Strava refresh token into postgreSQL:') cur.execute(sql_insert_strava_refresh_token, (ath_user, strava_token_tuple, ath_user)) conn.commit() # close the communication with the PostgreSQL cur.close() except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) finally: if conn is not None: conn.close()
def backup_user_db(db_name, ath_un, output, dbx_auth_token, encr_pass): if os.name == 'nt': backup_file_path = output + '\\' + ath_un + '\DB_Backup.gz' else: backup_file_path = output + '/' + ath_un + '/DB_Backup.gz' #Get PID of the current process and write it in the file pid = str(os.getpid()) pidfile = PID_FILE_DIR + ath_un + '_PID.txt' open(pidfile, 'w').write(pid) open(pidfile).close # read connection parameters params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) superuser_un = params.get("user") superuser_pw = params.get("password") db_host = params.get("host") try: with gzip.open(backup_file_path, 'wb') as f: with StdoutRedirection(ath_un): print('Backing up DB, please wait....') with ProgressStdoutRedirection(ath_un): print('Backing up DB, please wait....') # create .pgpass in user's AppData or home directory if os.name == 'nt': appdata_dir = os.getenv('APPDATA') pgpass_dir = os.path.join(appdata_dir, "postgresql") pgpassfile = os.path.join(pgpass_dir, "pgpass.conf") if not os.path.exists(pgpass_dir): os.makedirs(pgpass_dir) if not os.path.exists(pgpassfile): open(pgpassfile, 'w+').write(db_host + ':5432:*:postgres:' + superuser_pw) open(pgpassfile).close else: pgpassfile = os.path.expanduser('~') + '/.pgpass' if not os.path.exists(pgpassfile): open(pgpassfile, 'w+').write(db_host + ':5432:*:postgres:' + superuser_pw) open(pgpassfile).close os.chmod(pgpassfile, 0o600) # execute pg_dump popen = subprocess.Popen( ['pg_dump', '-h', db_host, '-U', superuser_un, db_name], stdout=subprocess.PIPE, universal_newlines=True) for stdout_line in iter(popen.stdout.readline, ""): f.write(stdout_line.encode()) popen.stdout.close() popen.wait() try: os.remove(pgpassfile) except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) with StdoutRedirection(ath_un): print( 'DB backup completed. The backup file will be uploaded to Dropbox now.' ) with ProgressStdoutRedirection(ath_un): print( 'DB backup completed. The backup file will be uploaded to Dropbox now.' ) except Exception as e: with ErrorStdoutRedirection(ath_un): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e))) today = datetime.datetime.today().strftime('%Y%m%d') download_folder_dbx = 'DB_Backup' file_name_to = today + '_DB_Backup.gz' dbx_file_exists = check_if_file_exists_in_dbx(file_name_to, dbx_auth_token, download_folder_dbx, encr_pass) if dbx_file_exists == True: with StdoutRedirection(ath_un): print((file_name_to + ' already exists in Dropbox, skipping.')) with ProgressStdoutRedirection(ath_un): print((file_name_to + ' already exists in Dropbox, skipping.')) else: try: with StdoutRedirection(ath_un): print(('Uploading ' + file_name_to + ' Dropbox, please wait.')) download_files_to_dbx(backup_file_path, file_name_to, dbx_auth_token, download_folder_dbx, encr_pass) with StdoutRedirection(ath_un): print('DB Backup file uploaded to Dropbox successfuly') with ProgressStdoutRedirection(ath_un): print('DB Backup file uploaded to Dropbox successfuly') except Exception as e: with ErrorStdoutRedirection(ath_un): print( (str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format(sys.exc_info()[-1].tb_lineno) + ' ' + str(e)))