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 ath_auth_reset(ath_un, ath_pw, encr_pass): sql_update_usr_pwd = "UPDATE db_info SET ath_pw = %s where ath_un= %s;" ath_pw_hash = generate_password_hash(ath_pw) query_params = (ath_pw_hash, ath_un) try: # connect to the PostgreSQL server params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") superuser_un = params.get("user") superuser_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=superuser_un, password=superuser_pw) conn_localhost.autocommit = True # create a cursor cur = conn_localhost.cursor() cur.execute(sql_update_usr_pwd, query_params) # Reset superset user password superset_params = config(filename="encrypted_settings.ini", section="superset", encr_pass=encr_pass) superset_used = str(superset_params.get("superset")) if superset_used == 'true': os.system( "superset fab reset-password --username {} --password {}". format(ath_un, ath_pw)) except Exception 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))) return None finally: if conn_localhost is not None: conn_localhost.close()
def send_email(encr_pass, subject, body, att_path=None, recipients=None): app_params = config(filename="encrypted_settings.ini", section="app", encr_pass=encr_pass) smtp_user = app_params.get("smtp_user") smtp_password = app_params.get("smtp_password") smtp_default_sender = app_params.get("smtp_default_sender") admin_email = app_params.get("admin_email") smtp_server = app_params.get("smtp_server") smtp_server_port = int(app_params.get("smtp_server_port")) # Create a multipart message and set headers message = MIMEMultipart() message["From"] = smtp_default_sender if recipients is not None: if isinstance(recipients, list): message["To"] = ", ".join(recipients) else: message["To"] = recipients else: recipients = admin_email message["To"] = recipients message["Subject"] = subject #message["Bcc"] = receiver_email # Recommended for mass emails # Add body to email message.attach(MIMEText(body, "plain")) if att_path is not None: # Open attachement file in binary mode with open(att_path, "rb") as attachment: # Add file as application/octet-stream part = MIMEBase("application", "octet-stream") part.set_payload(attachment.read()) # Encode file in ASCII characters to send by email encoders.encode_base64(part) filename = os.path.basename(os.path.normpath(att_path)) # Add header as key/value pair to attachment part part.add_header("Content-Disposition", "attachment; filename= %s" % filename) # Add attachment to message message.attach(part) # Convert message to string text = message.as_string() # Log in to server and send email server = smtplib.SMTP_SSL(smtp_server, smtp_server_port) server.login(smtp_user, smtp_password) server.sendmail(smtp_default_sender, recipients, text)
def check_if_file_exists_in_dbx(file_name, dbx_auth_token, folder, encr_pass): dbx_params = config(filename="encrypted_settings.ini", section="dropbox", encr_pass=encr_pass) APP_KEY = str(dbx_params.get("app_key")) APP_SECRET = str(dbx_params.get("app_secret")) access_token = dbx_auth_token checkIfFileExists = CheckIfFileExists(access_token, APP_KEY, APP_SECRET) file_name_to = file_name file_path_to = '/' + folder + '/' + file_name_to file_exists = checkIfFileExists.check_if_file_exists(file_path_to) return file_exists
def download_files_to_dbx(file_path_from, file_name, dbx_auth_token, folder, encr_pass): dbx_params = config(filename="encrypted_settings.ini", section="dropbox", encr_pass=encr_pass) APP_KEY = str(dbx_params.get("app_key")) APP_SECRET = str(dbx_params.get("app_secret")) access_token = dbx_auth_token transferData = TransferData(access_token, APP_KEY, APP_SECRET) file_name_to = file_name file_path_to = '/' + folder + '/' + file_name_to file_from = file_path_from file_to = file_path_to transferData.upload_file(file_from, file_to)
def start_autosynch_loop(encr_pass=None): #Retrieve encr_pass from .temp file if running on unix, else the encr_pass will be passed as argument if os.name != 'nt': if os.fork( ): # The script will run in the background.Returns 0 in the child, pid of the child in the parent sys.exit() time.sleep(1) print( 'The autosynch process has been started and will continue running in the background...' ) path_params = config(filename="encrypted_settings.ini", section="path") TEMP_FILE_PATH = str(path_params.get("temp_file_path")) #Retrieve passphrase from the .temp file f = open(TEMP_FILE_PATH, "r") passphrase_input = f.read() f.close encr_pass = passphrase_input while True: interval = 3600 #number of seconds to wait between runs task_start = time.time() # Retrieve list of credentials for user DB's with last synch older than n (specified in .ini) retrieve_decrypt_creds(get_databases_list(encr_pass), encr_pass) task_end = time.time() previous_task_duration = task_end - task_start #Previous run time exceeds interval, start next run immediately. if previous_task_duration > interval: with ConsolidatedProgressStdoutRedirection(): print((str(datetime.now()) + '\n---- Last run took ' + str(previous_task_duration) + ' seconds, which exceeds the ' + str(interval) + ' seconds interval.Starting the next run now.----\n')) retrieve_decrypt_creds(get_databases_list(encr_pass), encr_pass) #Previous run time is less then interval. Sleep for the time difference. else: with ConsolidatedProgressStdoutRedirection(): print((str('\n---- Previous run finished at:' + str(datetime.now())) + ', and took ' + str(previous_task_duration) + ' seconds to complete. Next run will start in ' + str(interval - previous_task_duration) + ' seconds.----\n')) time.sleep((task_start + interval) - task_end)
def check_user_exists(ath_un, encr_pass): conn_localhost = None sql_check_user_exists = """ SELECT ath_un FROM db_info WHERE ath_un = %s; """ 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 cur_localhost.execute(sql_check_user_exists, (ath_un, )) result = cur_localhost.fetchone() if result[0] is not None: #User record exists user_exists = True return user_exists else: user_exists = False # 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 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 ath_auth_login(ath_un, ath_pw, encr_pass): sql_select_usr_pwd = "SELECT ath_pw FROM db_info WHERE ath_un = %s;" query_params = (ath_un, ) try: # connect to the PostgreSQL server params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) postgres_db = params.get("database") superuser_un = params.get("user") superuser_pw = params.get("password") conn_localhost = psycopg2.connect(dbname=postgres_db, user=superuser_un, password=superuser_pw) conn_localhost.autocommit = True # create a cursor cur = conn_localhost.cursor() cur.execute(sql_select_usr_pwd, query_params) result = cur.fetchone() cur.close() if check_password_hash(result[0], ath_pw): return ath_un else: return None except Exception 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))) return None finally: if conn_localhost is not None: conn_localhost.close()
import os import datetime import Crypto.Random from Crypto.Cipher import AES from db_encrypt import generate_key, pad_text, unpad_text, str2md5 import base64 #----Crypto Variables---- # salt size in bytes SALT_SIZE = 16 # number of iterations in the key generation NUMBER_OF_ITERATIONS = 2000 # PG:Consider increasing number of iterations # the size multiple required for AES AES_MULTIPLE = 16 path_params = config(filename="encrypted_settings.ini", section="path") PID_FILE_DIR = path_params.get("pid_file_dir") def encrypt(plaintext, password): salt = Crypto.Random.get_random_bytes(SALT_SIZE) #iv = Crypto.Random.get_random_bytes(AES.block_size) key = generate_key(password, salt, NUMBER_OF_ITERATIONS) cipher = AES.new(key, AES.MODE_ECB) #PG: Consider changing to MODE_CBC padded_plaintext = pad_text(plaintext, AES_MULTIPLE) ciphertext = cipher.encrypt(padded_plaintext) ciphertext_with_salt = salt + ciphertext return ciphertext_with_salt @processify
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 mm_data_insert(output, start_date, end_date, ath_un, encr_pass, mm_dbx_link, save_pwd, db_host, db_name, superuser_un, superuser_pw): # Before executing the script go to your droppox Apps/Mind Monitor folder. # Right click on the folder, select 'Share' option and copy the generated link. # Assign the link string to the 'mm_dbx_link' parameter. user_output = os.path.join(output, ath_un) download_folder = os.path.join(user_output, 'MM_Historical_EEG') archive_folder = os.path.join(download_folder, 'Archive') #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) path_params = config(filename="encrypted_settings.ini", section="archive") preserve_files = str(path_params.get("preserve_files")) if save_pwd == True: encrypted_link = base64.b64encode(encrypt(mm_dbx_link, encr_pass)) encrypted_link = encrypted_link.decode('utf-8') else: encrypted_link = None #PG: insert MM export link into database mm_user_insert(encrypted_link, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) # Download the MM export csv file from dropbox using share link mm_dbx_link = mm_dbx_link[:-1] + '1' # Replace the 0 at the end of the link with 1.Will download as .zip file. response = urllib.request.urlopen(mm_dbx_link) # Extract the downloaded folder in to variable compressedFile = io.BytesIO() compressedFile.write(response.read()) compressedFile.seek(0) #Check if the date variable are of string or datetime type if type(start_date) == str: #Script run as standalone start_date_dt = datetime.datetime.strptime(start_date, "%Y-%m-%d") else: #Script run from flask server (variable passes as datetime type) start_date_dt = start_date if type(end_date) == str: #Script run as standalone end_date_dt = datetime.datetime.strptime(end_date, "%Y-%m-%d") else: #Script run from flask server (variable passes as datetime type) end_date_dt = end_date # 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) with zipfile.ZipFile(compressedFile, 'r') as z: #List the content of the zip file zipfile_content = z.namelist() #Iterate through list and only extract files within date range for item in zipfile_content: #Extract date from filename item_date = item[12:22] if item_date != '': item_date_dt = datetime.datetime.strptime( item_date, "%Y-%m-%d") else: continue # Check if the file date is within the download Date range. if item_date_dt > end_date_dt + datetime.timedelta( days=1) or item_date_dt < start_date_dt: continue else: #PG: Check whether the data from this file "file_path" have been inserted into to DB during one of the previous runs data_file_exists = check_data_file_exists( os.path.join(download_folder, item), ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) if data_file_exists == True: with StdoutRedirection(ath_un): print(('{} already exists in {}. Skipping.'.format( item, download_folder))) with ProgressStdoutRedirection(ath_un): print(('{} already exists in {}. Skipping.'.format( item, download_folder))) continue z.extract(item, download_folder) #Insert filepath into the db try: data_file_path_insert(os.path.join(download_folder, item), ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) 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))) for filename in os.listdir(download_folder): if filename.endswith(".zip"): with zipfile.ZipFile(os.path.join(download_folder, filename), 'r') as z: z.extractall(download_folder) os.remove(os.path.join(download_folder, filename)) with StdoutRedirection(ath_un): print(( 'The content of \"{}\" extracted into csv and the original removed' .format(filename))) with ProgressStdoutRedirection(ath_un): print(( 'The content of \"{}\" extracted into csv and the original removed' .format(filename))) db_name = str(str2md5(ath_un)) + '_Athlete_Data_DB' conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) sql = """ INSERT INTO mind_monitor_eeg(timestamp,delta_tp9, delta_af7,delta_af8,delta_tp10,athlete_id,theta_tp9,theta_af7,theta_af8,theta_tp10,alpha_tp9,alpha_af7,alpha_af8,alpha_tp10,beta_tp9,beta_af7,beta_af8, beta_tp10,gamma_tp9,gamma_af7,gamma_af8,gamma_tp10,raw_tp9,raw_af7,raw_af8,raw_tp10,aux_right,accelerometer_x,accelerometer_y,accelerometer_z, gyro_x,gyro_y,gyro_z,head_band_on,hsi_tp9,hsi_af7,hsi_af8,hsi_tp10,battery,elements,timestamp_gmt) VALUES (%s,%s,%s,%s,%s,(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) ON CONFLICT (athlete_id,timestamp) DO NOTHING; """ sql_local2gmt = """ SELECT gmt_local_difference FROM gmt_local_time_difference WHERE local_date = %s; """ for filename in os.listdir(download_folder): if filename.endswith(".csv"): # Read/Parse the .csv file and load the data into pandas dataset with StdoutRedirection(ath_un): print( ('Parsing and inserting the content of \"{}\" into the DB'. format(filename))) with ProgressStdoutRedirection(ath_un): print( ('Parsing and inserting the content of \"{}\" into the DB'. format(filename))) dataset = pd.read_csv(os.path.join(download_folder, filename), sep=",", header=None) dataset.drop(dataset.index[:1], inplace=True) #Drop the first row for row in dataset.itertuples(): local_time = row._1 local_time_dt = datetime.datetime.strptime( (local_time), "%Y-%m-%d %H:%M:%S.%f") local_time_str = datetime.datetime.strftime( local_time_dt, "%Y-%m-%d %H:%M:%S.%f") local_date_str = datetime.datetime.strftime((local_time_dt), "%Y-%m-%d") utc_time_str = None delta_tp9 = row._2 delta_af7 = row._3 delta_af8 = row._4 delta_tp10 = row._5 theta_tp9 = row._6 theta_af7 = row._7 theta_af8 = row._8 theta_tp10 = row._9 alpha_tp9 = row._10 alpha_af7 = row._11 alpha_af8 = row._12 alpha_tp10 = row._13 beta_tp9 = row._14 beta_af7 = row._15 beta_af8 = row._16 beta_tp10 = row._17 gamma_tp9 = row._18 gamma_af7 = row._19 gamma_af8 = row._20 gamma_tp10 = row._21 raw_tp9 = row._22 raw_af7 = row._23 raw_af8 = row._24 raw_tp10 = row._25 aux_right = row._26 accelerometer_x = row._27 accelerometer_y = row._28 accelerometer_z = row._29 gyro_x = row._30 gyro_y = row._31 gyro_z = row._32 head_band_on = row._33 hsi_tp9 = row._34 hsi_af7 = row._35 hsi_af8 = row._36 hsi_tp10 = row._37 battery = row._38 elements = row._39 # Check if the Record Date is within the download Date range. if local_time_dt > end_date_dt + datetime.timedelta( days=1) or local_time_dt < start_date_dt: break else: 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.%f") except (Exception, psycopg2.DatabaseError) as error: with ErrorStdoutRedirection(ath_un): print((str(datetime.now()) + ' ' + str(error))) try: # create a cursor cur = conn.cursor() # execute a statement cur.execute( sql, (local_time_str, delta_tp9, delta_af7, delta_af8, delta_tp10, ath_un, theta_tp9, theta_af7, theta_af8, theta_tp10, alpha_tp9, alpha_af7, alpha_af8, alpha_tp10, beta_tp9, beta_af7, beta_af8, beta_tp10, gamma_tp9, gamma_af7, gamma_af8, gamma_tp10, raw_tp9, raw_af7, raw_af8, raw_tp10, aux_right, accelerometer_x, accelerometer_y, accelerometer_z, gyro_x, gyro_y, gyro_z, head_band_on, hsi_tp9, hsi_af7, hsi_af8, hsi_tp10, battery, elements, 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))) 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 and the original csv file removed' .format(filename))) with ProgressStdoutRedirection(ath_un): print(( 'The content of \"{}\" parsed and inserted into DB and the original csv file removed' .format(filename))) else: #Move the csv to archive folder if not os.path.exists(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)) # close the communication with the PostgreSQL if conn is not None: conn.close()
def ath_auth_register(ath_un, ath_pw, encr_pass): sql_create_info_db_table = """ CREATE TABLE IF NOT EXISTS public.db_info (id serial NOT NULL, ath_un character varying(300) COLLATE pg_catalog."default", ath_pw character varying(300) COLLATE pg_catalog."default", db_name character varying(300) COLLATE pg_catalog."default", db_host character varying(300) COLLATE pg_catalog."default", db_un character varying(300) COLLATE pg_catalog."default", db_pw character varying(300) COLLATE pg_catalog."default", last_synch character varying(300) COLLATE pg_catalog."default", db_auto_synch boolean, CONSTRAINT db_info_pkey PRIMARY KEY (id), CONSTRAINT db_info_unique UNIQUE (db_name)) WITH (OIDS = FALSE) TABLESPACE pg_default; ALTER TABLE public.db_info OWNER to postgres; """ sql_insert_db_info_record = "INSERT INTO db_info (ath_un,ath_pw,db_name) VALUES (%s,%s,%s);" ath_pw_hash = generate_password_hash(ath_pw) db_name = str(str2md5(ath_un)) + '_Athlete_Data_DB' query_params = (ath_un, ath_pw_hash, db_name) 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() cur.execute(sql_create_info_db_table) cur.execute(sql_insert_db_info_record, query_params) # close the communication with the PostgreSQL cur.close() # Add the user to superset fab superset_params = config(filename="encrypted_settings.ini", section="superset", encr_pass=encr_pass) superset_used = str(superset_params.get("superset")) if superset_used == 'true': head, sep, tail = ath_un.partition('@') usr_name = head os.system( "superset fab create-user --role ath_role1 --username {} --firstname {} --lastname {} --email {} --password {}" .format(ath_un, usr_name, usr_name, ath_un, ath_pw)) except (Exception, psycopg2.IntegrityError) 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))) return None finally: if conn_localhost is not None: conn_localhost.close() return ath_un
def create_sample_db(encr_pass): conn = None try: # read connection parameters params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) sample_db_host = params.get("sample_db_host") sample_db_port = params.get("sample_db_port") if sample_db_port == "": sample_db_port == "5432" db_name = params.get("sample_db") superuser_un = params.get("user") superuser_pw = params.get("password") ro_user = params.get("ro_user") ro_password = params.get("ro_password") sql_get_lc_collate = "SHOW LC_COLLATE" sql_check_db_exists = "select exists(SELECT datname FROM pg_catalog.pg_database WHERE datname = %s);" sql_create_db = "CREATE DATABASE \"" + db_name + "\" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = %s LC_CTYPE = %s;" sql_create_ro_role = "CREATE ROLE " + ro_user + " WITH LOGIN PASSWORD \'" + ro_password + "\' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL \'infinity\';" sql_grant_permissions_1 = "GRANT CONNECT ON DATABASE " + db_name + " TO " + ro_user + ";" sql_grant_permissions_2 = "GRANT USAGE ON SCHEMA public TO " + ro_user + ";" sql_grant_permissions_3 = "GRANT SELECT ON ALL TABLES IN SCHEMA public TO " + ro_user + ";" sql_grant_permissions_4 = "GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO " + ro_user + ";" sql_revoke_public = "REVOKE ALL ON DATABASE \"" + db_name + "\" FROM PUBLIC;" sql_revoke_public_1 = "REVOKE ALL ON DATABASE postgres FROM PUBLIC;" # connect to the PostgreSQL server postgres db to check whether sample db exists and create it if doesnt. conn = psycopg2.connect(dbname="postgres", user=superuser_un, password=superuser_pw, host=sample_db_host, port=sample_db_port) conn.autocommit = True # create a cursor cur = conn.cursor() # execute a statement with ConsolidatedProgressStdoutRedirection(): print('Checking whether the sample DB aleady exists') cur.execute(sql_check_db_exists, (db_name, )) result = cur.fetchone() if result[0] is True: db_exists = True with ConsolidatedProgressStdoutRedirection(): print('Sample DB already exists, exiting...') else: db_exists = False if db_exists == False: with ConsolidatedProgressStdoutRedirection(): print("Creating sample DB...") cur.execute(sql_get_lc_collate) lc_collate = cur.fetchone() cur.execute(sql_create_db, (lc_collate[0], lc_collate[0])) cur.close() conn.close #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()) try: # connect to the PostgreSQL server (sample_db) conn = psycopg2.connect(dbname=db_name, user=superuser_un, password=superuser_pw, host=sample_db_host, port=sample_db_port) conn.autocommit = True # create a cursor cur = conn.cursor() with ConsolidatedProgressStdoutRedirection(): print("Restoring sample DB schema...") cur.execute(sql_restore_db_schema) cur.close() cur = conn.cursor() with ConsolidatedProgressStdoutRedirection(): print( "Creating sample db RO user and granting RO permissions..." ) cur.execute(sql_create_ro_role) cur.close() cur = conn.cursor() cur.execute(sql_grant_permissions_1) cur.execute(sql_grant_permissions_2) cur.execute(sql_grant_permissions_3) cur.execute(sql_grant_permissions_4) cur.execute(sql_revoke_public) cur.execute(sql_revoke_public_1) cur.close() conn.close() except (Exception, psycopg2.DatabaseError) as error: with ConsolidatedProgressStdoutRedirection(): print((str(datetime.datetime.now()) + ' [' + sys._getframe().f_code.co_name + ']' + ' Error on line {}'.format( sys.exc_info()[-1].tb_lineno) + ' ' + str(error))) else: cur.close() conn.close() except (Exception, psycopg2.DatabaseError) as error: with ConsolidatedProgressStdoutRedirection(): 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 get_databases_list(encr_pass): conn = None sql_get_databases = """ SELECT datname FROM pg_database WHERE datname like '%_Athlete_Data_DB'; """ # Retrieve list of databases with last synch older than n sql_get_databases_db_info = """ SELECT db_name FROM db_info WHERE db_auto_synch = 'true' AND last_synch::timestamp < %s::timestamp; """ try: # read connection parameters params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) autosynch_params = config(filename="encrypted_settings.ini", section="autosynch", encr_pass=encr_pass) postgres_db = params.get("database") postgres_un = params.get("user") postgres_pw = params.get("password") interval = int( autosynch_params.get("interval") ) #The amount of time in seconds to wait before attempting next synch time_now = datetime.datetime.now() now_less_interval_dt = time_now - datetime.timedelta(seconds=interval) now_less_interval_str = now_less_interval_dt.strftime( "%Y-%m-%d %H:%M:%S") conn = psycopg2.connect(dbname=postgres_db, user=postgres_un, password=postgres_pw) # connect to the PostgreSQL server with ConsolidatedProgressStdoutRedirection(): print( 'Connecting to the PostgreSQL server to get list of databases...' ) # create a cursor cur = conn.cursor() # Retrieve list of databases with last synch older than n cur.execute(sql_get_databases_db_info, (now_less_interval_str, )) conn.commit() databases = cur.fetchall() except (Exception, psycopg2.DatabaseError) as error: with ConsolidatedProgressStdoutRedirection(): 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 databases
def glimp_data_insert(output, start_date, end_date, ath_un, encr_pass, glimp_dbx_link, save_pwd, db_host, db_name, superuser_un, superuser_pw): # Before executing the script go to your droppox Apps/Glimp folder and locate ClicemiaMisurazioni.csv.gz file. # Right click on the file, select 'Share' option and copy the generated link. # Assign the link string to the 'glimp_dbx_link' parameter. glimp_export_csv = 'GlicemiaMisurazioni.csv' glimp_export_csv_gz = 'GlicemiaMisurazioni.csv.gz' user_output = os.path.join(output, ath_un) download_folder = os.path.join(user_output, 'CGM_Historical_BG') archive_folder = os.path.join(download_folder, 'Archive') glimp_file_path = os.path.join(download_folder, glimp_export_csv) glimp_file_path_gz = os.path.join(download_folder, glimp_export_csv_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) path_params = config(filename="encrypted_settings.ini", section="archive") preserve_files = str(path_params.get("preserve_files")) if save_pwd == True: encrypted_link = base64.b64encode(encrypt(glimp_dbx_link, encr_pass)) encrypted_link = encrypted_link.decode('utf-8') else: encrypted_link = None #Check if the date variable are of string or datetime type if type(start_date) == str: #Script runs as standalone start_date_dt = datetime.datetime.strptime(start_date, "%Y-%m-%d") else: #Script runs from flask server (variable passes as datetime type) start_date_dt = start_date if type(end_date) == str: #Script run as standalone end_date_dt = datetime.datetime.strptime(end_date, "%Y-%m-%d") else: #Script runs from flask server (variable passes as datetime type) end_date_dt = end_date # Download the glimp export csv file from dropbox using share link glimp_dbx_link = glimp_dbx_link[:-1] + '1' # Replace the 0 at the end of the link with 1. response = urllib.request.urlopen(glimp_dbx_link) # Extract the downloaded file compressedFile = io.BytesIO() compressedFile.write(response.read()) compressedFile.seek(0) # 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) with zipfile.ZipFile(compressedFile, 'r') as z: #List the content of the zip file zipfile_content = z.namelist() #Iterate through list and only extract files matching the name for item in zipfile_content: if item == glimp_export_csv_gz: #The File is generated by Glimp app z.extract(item, download_folder) # Write the extracted content to a new csv file glimp_export = gzip.GzipFile(glimp_file_path_gz, mode='rb') with open(glimp_file_path, 'wb') as f: f.write(glimp_export.read()) glimp_export.close #os.remove(os.path.join(download_folder,item)) #Delete the GZ file after content extracted else: #The file is manualy download from LibreView z.extract(item, download_folder) db_name = str(str2md5(ath_un)) + '_Athlete_Data_DB' conn = None # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db_name, host=db_host, user=superuser_un, password=superuser_pw) sql = """ INSERT INTO diasend_cgm(athlete_id,timestamp,glucose_nmol_l,glucose_nmol_l_15min_avrg,ketone_nmol_l,data_source,timestamp_gmt) VALUES ((select id from athlete where ath_un=%s),%s,%s,%s,%s,%s,%s) ON CONFLICT (athlete_id,timestamp,data_source) DO NOTHING; """ sql_local2gmt = """ SELECT gmt_local_difference FROM gmt_local_time_difference WHERE local_date = %s; """ 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))) # Iterate throught files in the download folder for item in os.listdir(download_folder): #The file is an export from Glimp app if item == glimp_export_csv: #Insert Glimp export link into database glimp_user_insert(encrypted_link, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) data_source = 'glimp_android' # Read/Parse the .csv file dataset = pd.read_csv(glimp_file_path, sep=";", encoding='UTF-16 LE', header=None) parse_dataset(dataset, conn, start_date_dt, end_date_dt, data_source) if preserve_files == "false": #Remove the csv file from download folder os.remove(os.path.join(download_folder, item)) else: #Move the csv to archive folder if not os.path.exists(os.path.join(archive_folder, item)): os.rename(os.path.join(download_folder, item), os.path.join(archive_folder, item)) else: os.remove(os.path.join(download_folder, item)) #The file is an export from LibreView elif re.match( r"^[a-zA-Z0-9]+_glucose_[0-9|-]+.csv", item): #Search for pattern FirstLast_glucose_dd-mm-yyyy.csv #Insert LIbreView export link into database libreview_user_insert(encrypted_link, ath_un, db_host, db_name, superuser_un, superuser_pw, encr_pass) data_source = 'libre_view' dataset = pd.read_csv(os.path.join(download_folder, item), sep=",", skiprows=1) dataset = dataset.iloc[:, [ 0, 1, 2, 3, 4, 5, 15 ]] # Keep first 6 columns and column 16 and drop everything else dataset['Device Timestamp'] = pd.to_datetime( dataset['Device Timestamp'], format="%d-%m-%Y %H:%M") dataset = dataset.sort_values(by='Device Timestamp', ascending=False) dataset = dataset.dropna( subset=[ 'Historic Glucose mmol/L', 'Scan Glucose mmol/L', 'Ketone mmol/L' ], thresh=1) #Drop row if NaN in all three columns dataset = dataset.replace({np.nan: None}) #Replace NaN values with None parse_dataset(dataset, conn, start_date_dt, end_date_dt, data_source) if preserve_files == "false": #Remove the csv file from download folder os.remove(os.path.join(download_folder, item)) else: #Move the csv to archive folder if not os.path.exists(os.path.join(archive_folder, item)): os.rename(os.path.join(download_folder, item), os.path.join(archive_folder, item)) else: os.remove(os.path.join(download_folder, item)) else: continue # close the communication with the PostgreSQL if conn is not None: conn.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 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 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 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)))
def retrieve_decrypt_creds(synch_req_db_list, encr_pass, full_synch=False): sql_get_creds = """ SELECT gc_email,gc_password,mfp_username,mfp_password,diasend_username,diasend_password,dropbox_access_token,glimp_export_link,libreview_export_link,mm_export_link,oura_refresh_token,strava_refresh_token,ath_un FROM athlete; """ sql_select_dbsu_creds = """ SELECT db_host,db_un,db_pw FROM db_info WHERE db_name = %s; """ for row in synch_req_db_list: for db in row: try: #Reset all user variables conn = None ath_un = None gc_un = None gc_encr_pw = None gc_decr_pw = None mfp_un = None mfp_encr_pw = None mfp_decr_pw = None cgm_un = None cgm_encr_pw = None cgm_decr_pw = None dbx_encr_token = None dbx_decr_token = None oura_encr_token = None oura_decr_token = None strava_encr_token = None strava_decr_token = None glimp_encr_export_link = None glimp_decr_export_link = None libreview_encr_export_link = None libreview_decr_export_link = None mm_encr_export_link = None mm_decr_export_link = None # read connection parameters dbsu_params = config(filename="encrypted_settings.ini", section="postgresql", encr_pass=encr_pass) superuser_un = dbsu_params.get("user") superuser_pw = dbsu_params.get("password") dbsu_conn = psycopg2.connect(dbname='postgres', user=superuser_un, password=superuser_pw) dbsu_cur = dbsu_conn.cursor() dbsu_cur.execute(sql_select_dbsu_creds, (db, )) dbsu_conn.commit() dbsu_result = dbsu_cur.fetchone() db_host = dbsu_result[0] db_un = dbsu_result[1] db_pw = dbsu_result[2] if db_host != 'localhost': #User database is hosted remotely superuser_un = db_un superuser_pw = decrypt(base64.b64decode(db_pw), encr_pass) except (Exception, psycopg2.DatabaseError) as error: with ConsolidatedProgressStdoutRedirection(): print('Autosynch DB Error: ' + str(error)) finally: if dbsu_conn is not None: dbsu_conn.close() try: # connect to the PostgreSQL server conn = psycopg2.connect(dbname=db, host=db_host, user=superuser_un, password=superuser_pw) cur = conn.cursor() cur.execute(sql_get_creds) conn.commit() result = cur.fetchone() gc_un = result[0] gc_encr_pw = result[1] mfp_un = result[2] mfp_encr_pw = result[3] cgm_un = result[4] cgm_encr_pw = result[5] dbx_encr_token = result[6] glimp_encr_export_link = result[7] libreview_encr_export_link = result[8] mm_encr_export_link = result[9] oura_encr_token = result[10] strava_encr_token = result[11] ath_un = result[12] #Now decrypt (gc_encr_pw,mfp_encr_pw,dbx_encr_token and others) if gc_encr_pw is not None: gc_decr_pw = decrypt(base64.b64decode(gc_encr_pw), encr_pass) if mfp_encr_pw is not None: mfp_decr_pw = decrypt(base64.b64decode(mfp_encr_pw), encr_pass) if cgm_encr_pw is not None: cgm_decr_pw = decrypt(base64.b64decode(cgm_encr_pw), encr_pass) if dbx_encr_token is not None: dbx_decr_token = decrypt(base64.b64decode(dbx_encr_token), encr_pass) if glimp_encr_export_link is not None: glimp_decr_export_link = decrypt( base64.b64decode(glimp_encr_export_link), encr_pass) if libreview_encr_export_link is not None: libreview_decr_export_link = decrypt( base64.b64decode(libreview_encr_export_link), encr_pass) if mm_encr_export_link is not None: mm_decr_export_link = decrypt( base64.b64decode(mm_encr_export_link), encr_pass) if oura_encr_token is not None: oura_decr_token = decrypt( base64.b64decode(oura_encr_token), encr_pass) if strava_encr_token is not None: strava_decr_token = decrypt( base64.b64decode(strava_encr_token), encr_pass) ###Execute auto synch from "main_data_autosynch.py"### auto_synch(ath_un, db, db_host, superuser_un, superuser_pw, gc_un, gc_decr_pw, mfp_un, mfp_decr_pw, cgm_un, cgm_decr_pw, glimp_decr_export_link, libreview_decr_export_link, mm_decr_export_link, dbx_decr_token, oura_decr_token, strava_decr_token, encr_pass, full_synch) except (Exception, psycopg2.DatabaseError) as error: with ConsolidatedProgressStdoutRedirection(): print(('Autosynch DB Error: ' + str(error))) finally: if conn is not None: conn.close()