def insert_weight_demo(time_pair):
if len(time_pair) == 0:
return
insert_set = []
for time in time_pair:
fitbit_data = time_pair[time]
# print(str(time) + " / " + str(fitbit_data))
insert_set.append(fitbit_data.uid, fitbit_data.weight, str(datetime.datetime.now()))
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
dbhandler = connection.cursor()
flush_user = insert_set[0][1]
flush_date = insert_set[0][0][:10]
# flush that day's existing data
#NEED TO EDIT DATE TO UPDATED_AT???#
flush_stmt = "DELETE FROM Demographics WHERE fitbit_uid = '%s' AND date(timestamp) = '%s'" % (
flush_user, flush_date)
dbhandler.execute(flush_stmt)
connection.commit()
insert_cursor = connection.cursor()
stmt = "INSERT INTO Demographics(fitbit_uid, weight, updated_at) VALUES (%s, %s, %s)"
insert_cursor.executemany(stmt, insert_set)
except Exception as e:
print ("EXCEPTION IN insert_weight_demo: " + str(e))
finally:
connection.commit()
connection.close()
def insert_weight_dict(time_pair):
if len(time_pair) == 0:
return
insert_set = []
for time in time_pair:
fitbit_data = time_pair[time]
# print(str(time) + " / " + str(fitbit_data))
insert_set.append((time, fitbit_data.uid, fitbit_data.weight, fitbit_data.bmi, fitbit_data.fat, fitbit_data.source, str(datetime.datetime.now())))
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
dbhandler = connection.cursor()
flush_user = insert_set[0][1]
flush_date = insert_set[0][0][:10]
# flush that day's existing data
flush_stmt = "DELETE FROM PC_Weight WHERE fitbit_uid = '%s' AND date(timestamp) = '%s'" % (
flush_user, flush_date)
dbhandler.execute(flush_stmt)
connection.commit()
insert_cursor = connection.cursor()
stmt = "INSERT INTO PC_Weight (timestamp, fitbit_uid, weight, bmi, fat, source, added_on) VALUES (%s, %s, %s, %s, %s, %s, %s)"
insert_cursor.executemany(stmt, insert_set)
except Exception as e:
print("EXCEPTION IN insert_weight_dict: " + str(e))
finally:
connection.commit()
connection.close()
def insert_daily_activity(ds):
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
try:
print("Inserting daily set: " + str(ds))
dbhandler = connection.cursor()
flush_user = ds.uid
flush_date = ds.date
# flush that day's existing data
flush_stmt = "DELETE FROM PC_Daily_Activities WHERE fitbit_uid = '%s' AND date(timestamp) = '%s'" % (
flush_user, flush_date)
dbhandler.execute(flush_stmt)
connection.commit()
# repopulate that day's data
insert_set = (ds.date, ds.uid, ds.daily_steps, ds.sedentary_mins, ds.lightly_mins, ds.fairly_mins, ds.very_mins,
ds.distance, str(datetime.datetime.now()))
insert_stmt = "INSERT INTO PC_Daily_Activities (timestamp, fitbit_uid, daily_step_total, " \
"minutes_sedentary, minutes_lightly_active, minutes_fairly_active, minutes_very_active, " \
"distance, added_on) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
dbhandler.execute(insert_stmt, insert_set)
except Exception as e:
print ("EXCEPTION IN insert_daily_activity: " + str(e))
print(dbhandler._last_executed)
traceback.print_exc()
finally:
connection.commit()
connection.close()
print("Done inserting daily set: " + str(ds))
def update_devices(device_id_pair):
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
cursor = connection.cursor(db.cursors.DictCursor)
result = cursor.execute("SELECT * FROM Devices")
now_time = str(datetime.datetime.now())
# print(now_time)
rows = cursor.fetchall()
for device_id in device_id_pair:
# print(row["submitted_at"] < datetime.datetime.now())
print("DEVICE : " + device_id)
device = device_id_pair[device_id]
result = cursor.execute("INSERT INTO PC_Devices \
(fitbit_uid, device_id, last_sync_time, device_version, device_type, battery, battery_level, updated_at)\
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')\
ON DUPLICATE KEY UPDATE last_sync_time='%s', battery='%s', battery_level='%s', updated_at='%s'"
% (device.uid, device_id, device.last_sync_time, device.device_version,
device.device_type, device.battery, device.battery_level, now_time,
device.last_sync_time, device.battery, device.battery_level, now_time))
except Exception as e:
print(e)
finally:
cursor.close()
connection.commit()
connection.close()
def insert_noncompliance_ping(user_id, ping_date, sync_ping_type=0):
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
dbhandler = connection.cursor()
stmt = """INSERT INTO PC_Noncompliance_Ping (date, fitbit_uid, not_equipped_flag, not_synced_flag, added_on) VALUES (%s, %s, %s, %s, %s)"""
dbhandler.execute(stmt, (ping_date, user_id, 1, sync_ping_type, str(datetime.datetime.now())))
except Exception as e:
print ("EXCEPTION IN insert_noncompliance_ping: " + str(e))
finally:
connection.commit()
connection.close()
def get_db_last_hr_record():
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB,
cursorclass=cursors.SSCursor)
try:
dbhandler = connection.cursor(cursors.DictCursor)
# EDITED to update general step_heartrate table
stmt = "SELECT * FROM step_heartrate WHERE fitbit_uid = '%s' ORDER BY timestamp DESC LIMIT 1" % uid
dbhandler.execute(stmt)
for row in dbhandler:
return row["timestamp"]
return None
except Exception as e:
traceback.print_exc()
print ("EXCEPTION get_db_last_hr_record: " + str(e))
finally:
connection.close()
def insert_user_info(user_json):
insert_set = []
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
user = user_json["user"]
dbhandler = connection.cursor()
stmt = "UPDATE PC_Users SET height=%s WHERE fitbit_uid='%s'" % (user["height"], user["encodedId"])
dbhandler.execute(stmt)
except Exception as e:
print ("EXCEPTION IN insert_user_info: " + str(e))
print(dbhandler._last_executed)
finally:
connection.commit()
connection.close()
def connect_db():
try:
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
dbhandler = connection.cursor()
# EDIT TO CONNECT TO CORRECT TABLE
dbhandler.execute("SELECT * from FitbitArchiveHeartRate")
dbhandler.execute("")
result = dbhandler.fetchall()
for item in result:
print (item)
except Exception as e:
print (e)
finally:
connection.commit()
connection.close()
def insert_intraday_dict(time_pair):
insert_set = []
for time in time_pair:
fitbit_data = time_pair[time]
# print(str(time) + " / " + str(fitbit_data))
# EDITED to remove distance
insert_set.append((fitbit_data.uid, time, fitbit_data.heart_rate, fitbit_data.step_count,
fitbit_data.activity_level, str(datetime.datetime.now())))
if len(insert_set) == 0:
return
connection = db.Connection(host=HOST, port=PORT,
user=USER, passwd=PASSWORD, db=DB)
try:
dbhandler = connection.cursor()
flush_user = insert_set[0][1]
flush_date = insert_set[0][0][:10]
# flush that day's existing data
flush_stmt = "DELETE FROM Step_HeartRate WHERE fitbit_uid = '%s' AND date(timestamp) = '%s'" % (
flush_user, flush_date)
dbhandler.execute(flush_stmt)
connection.commit()
# repopulate that day's data
# EDITED to insert into universal step_heartrate table
# EDITED not to report distance
insert_stmt = "INSERT INTO step_heartrate (fitbit_uid, timestamp, heart_rate, step_count, activity_level, added_on) VALUES (%s, %s, %s, %s, %s, %s)"
dbhandler.executemany(insert_stmt, insert_set)
except Exception as e:
traceback.print_exc()
print ("EXCEPTION IN insert_intraday_dict: " + str(e))
finally:
connection.commit()
connection.close()