def keys_from_icg(conn=get_conn()): curs = conn.cursor() select = 'SELECT * FROM insulin_carb_grouped ' curs.execute(select) global IC_KEYS IC_KEYS = [d[0] for d in curs.description] print 'IC_KEYS ', IC_KEYS
def bad_row(label): conn = get_conn() curs = conn.cursor() rows = curs.execute( "select * from insulin_carb_smoothed where rtime = '2014-03-14 17:15:00'" ) print label, 'bad row is there'
def get_first_corrective_insulin(year=2018): conn = get_conn() curs = conn.cursor(MySQLdb.cursors.DictCursor) curs.execute('''SELECT min(rtime) as rtime from insulin_carb_smoothed_2 where corrective_insulin = 1 and year(rtime) = %s''', [year]) start = curs.fetchone()['rtime'] return start
def browse_isf2(date=None,time=None): # not sufficiently general, but okay for now first = isf.get_first_corrective_insulin(year=2018) first_date, first_time = (first.strftime('%Y-%m-%d'), first.strftime('%H:%M:%S')) if date == None: date,time = first_date,first_time elif time == None: date = first_date try: rtime_str,rtime_dt = date_ui.to_datestr(date,time) except: flash('invalid date: {} {}'.format(date,time)) return redirect(url_for('browse_isf2')) print('browsing w/ start time = {}'.format(rtime_str)) if request.method == "POST": # POST almost certainly means the user clicked the "next" button # we'll assume that conn = get_conn() print('getting next ISF after {}'.format(rtime_str)) start_dt = isf.get_isf_next(conn,rtime_str) return redirect(url_for('browse_isf2', date=start_dt.strftime('%Y-%m-%d'), time=start_dt.strftime('%H:%M:%S'))) else: conn = get_conn() rows = isf.get_isf_at(conn,rtime_str) trouble = rows[0]['ISF_trouble'] # convert 'ok' to empty trouble = '' if trouble == 'ok' else trouble details = isf.get_isf_details(conn,rtime_dt) return render_template('isf2.html', isf_trouble = trouble, script = url_for('browse_isf2', date = rtime_dt.strftime('%Y-%m-%d'), time = rtime_dt.strftime('%H:%M:%S')), rows = rows, details = details, # decoration page_title = ('''ISF for {dt:%A}, {dt:%B} {dt.day}, {dt.year}, at {dt.hour}:{dt.minute:02d}''' .format(dt=rtime_dt)))
def get_all_isf_plus_buckets(): ''' Returns all good isf values and all isf values in 2-hour time buckets''' conn = get_conn() curs = conn.cursor() curs.execute('''SELECT isf from isf_details;''') allData = curs.fetchall() curs.execute('''select time_bucket(rtime),isf from isf_details;''') bucketed = curs.fetchall() bucket_list = [ [ row[1] for row in bucketed if row[0] == b ] for b in range(0,24,2) ] return(allData,bucket_list)
def join_bg_values(): conn = get_conn() curs = conn.cursor() # this takes 18.71 seconds, so be patient curs.execute( '''update insulin_carb_smoothed as ics inner join cgm_noduplicates as cgm using (rtime) set ics.cgm = cgm.mgdl''') # this only takes 0.77 seconds curs.execute( '''update insulin_carb_smoothed as ics inner join mgm_noduplicates as mgm using (rtime) set ics.bg = mgm.mgdl''')
def get_isf(rtime): conn = get_conn() curs = conn.cursor(MySQLdb.cursors.DictCursor) curs.execute('''SELECT rtime from insulin_carb_smoothed_2 where corrective_insulin = 1 and rtime > %s''',[rtime]) start = curs.fetchone()['rtime'] #get table from rtime curs.execute('''SELECT rtime, corrective_insulin, bg, cgm, total_bolus_volume,ISF,ISF_rounded,ISF_trouble from insulin_carb_smoothed_2 where rtime>= %s and rtime<= addtime(%s,'2:00')''', [start,start]) return curs.fetchall()
def icg(start='2017-01-01', end='2017-01-02'): conn = get_conn() curs = conn.cursor() get_cursor_columns(tablename='insulin_carb_grouped') curs.execute(( "select * from insulin_carb_grouped where rtime >= '{start}' and rtime <= '{end}' " .format(start=start, end=end))) while True: row = curs.fetchone() if row is None: return yield row
def get_isf_for_years(start_year,end_year): '''returns all isf values and all isf values in 2-hour time buckets for a specific time period (in years) ''' conn = get_conn() curs = conn.cursor() curs.execute('''SELECT isf from isf_details where year(rtime) >= %s and year(rtime)<= %s''',[start_year, end_year]) allData = curs.fetchall() curs.execute('''SELECT time_bucket(rtime), isf from isf_details where year(rtime)>= %s and year(rtime) <= %s''', [start_year, end_year]) bucketed = curs.fetchall() bucket_list = [[ row[1] for row in bucketed if row[0] == b ]for b in range(0,24,2)] return (allData, bucket_list)
def get_isf_for_bg (bg_value): ''' returns isf values and isf values in 2-hour time buckets for a specific starting bg value''' conn = get_conn() curs = conn.cursor() curs.execute('''SELECT time_bucket(rtime), isf from isf_details where bg0 < %s ''', [bg_value]) less_than = curs.fetchall() less_than_list = [ [ row[1] for row in less_than if row[0] == b] for b in range(0,24,2)] curs.execute('''SELECT time_bucket(rtime), isf from isf_details where bg0 > %s ''', [bg_value]) greater_than = curs.fetchall() greater_than_list = [[row[1] for row in greater_than if row[0] == b] for b in range(0,24,2)] return (less_than_list, greater_than_list)
def csv_dump(table, CSVfilename): conn = get_conn() curs = conn.cursor(MySQLdb.cursors.DictCursor) # results as Dictionaries curs.execute('select * from {table} limit 1'.format(table=table)) row = curs.fetchone() keys = row.keys() curs = conn.cursor() # results as lists curs.execute('select * from {table}'.format(table=table)) with open(CSVfilename, 'wb') as csvfile: writer = csv.writer(csvfile) # default format is Excel writer.writerow(keys) while True: row = curs.fetchone() if row is None: break writer.writerow(row)
def ic_output_list(rows, tablename, keys): '''Write rows to the given table. Rows can be an iterable. Each row is a list. ''' conn = get_conn() curs = conn.cursor() # clear out old data curs.execute('delete from {table}'.format(table=tablename)) sql = 'insert into {table}({cols}) values({vals})'.format( table=tablename, cols=','.join(keys), vals=','.join(['%s' for k in cursor_columns])) print('insert using ', sql) insert_count = 0 for row in rows: insert_count += 1 if insert_count % 10000 == 0: print 'ic_output_list to {} '.format(tablename), str(insert_count) curs.execute(sql, row)
def ic_output_dict(rows, tablename, keys): '''Write rows (can be an iterable) but each row is a dict not list to the given table''' conn = get_conn() curs = conn.cursor() # clear out old data curs.execute('delete from {table}'.format(table=tablename)) sql = 'insert into {table}({cols}) values({vals})'.format( table=tablename, cols=','.join(keys), vals=','.join(['%s' for k in keys])) print('insert using ', sql) insert_count = 0 for row in rows: insert_count += 1 if insert_count % 10000 == 0: print 'ic_output_dict to {} '.format(tablename), str(insert_count) # this would also be more efficient if we kept things as lists throughout curs.execute(sql, [row[k] for k in keys])
def gen_rows_ic_dictionaries(conn=get_conn(), start=None, end=None, tablename='insulin_carb_smoothed'): curs = conn.cursor(MySQLdb.cursors.DictCursor) # results as Dictionaries select = 'SELECT * FROM {} '.format(tablename) if start is not None: select += " WHERE rtime >= '{start}' and rtime <= '{end}' ".format( start=start, end=end) print 'gen_rows_ic in table {} with query {} '.format(tablename, select) curs.execute(select) global cursor_columns cursor_columns = [desc[0] for desc in curs.description] print 'cursor_columns: ', cursor_columns while True: row = curs.fetchone() if row is None: return yield row
def test_compute_minutes_since(): rows = gen_rows_ic( end='2014-05-01' ) # this gets more columns than we need, but maybe we can optimize that later global cursor_columns get_cursor_columns() col1 = cursor_columns.index('minutes_since_last_bolus') col2 = cursor_columns.index('minutes_since_last_meal') col3 = cursor_columns.index('rtime') conn = get_conn() num_rows = 0 update = conn.cursor() for row in compute_minutes_since(rows): num_rows += 1 if num_rows % 1000 == 0: print('updated ', num_rows) update.execute( 'update insulin_carb_smoothed set minutes_since_last_bolus = %s, minutes_since_last_meal = %s where rtime = %s', [row[col1], row[col2], row[col3]])
def getRecentISF (time_bucket, num_weeks, min_data, debug=False): '''returns at least min_data isf values for a specific 2-hour time bucket looking back num_weeks (or more depending on available data points) ''' conn = get_conn () curs = conn.cursor() def try_weeks(num_weeks): time_end = datetime.strptime("18/09/10", '%y/%m/%d') - timedelta(weeks = num_weeks) curs.execute ('''SELECT isf FROM isf_details where time_bucket(rtime) = %s and rtime > %s ''', [time_bucket, time_end]) def doubling_up(min_weeks): if debug: print(('doubling up ',min_weeks)) try_weeks(min_weeks) if curs.rowcount >= min_data: return recur(int(min_weeks/2), min_weeks) else: return doubling_up(min_weeks*2) def recur(min_weeks, max_weeks): mid = int((min_weeks+max_weeks)/2) if debug: print(('recur ',min_weeks, mid, max_weeks)) try_weeks(mid) if max_weeks == min_weeks + 1: # done, so either use min or max if (curs.rowcount < min_data): min_weeks = max_weeks try_weeks(min_weeks) # base case: results = curs.fetchall() isf = [result[0] for result in results] isf = sorted(isf) return min_weeks, isf elif curs.rowcount >= min_data: # try lower half return recur(min_weeks,mid) else: # try upper half return recur(mid,max_weeks) return doubling_up(num_weeks)
def gen_rows_ic( conn=get_conn(), start=None, end=None, tablename='insulin_carb_smoothed'): curs = conn.cursor() select = 'SELECT * FROM {} '.format(tablename) if start is not None: select += " WHERE rtime >= '{start}' and rtime <= '{end}' ".format( start=start, end=end) print 'gen_rows_ic in table {} with query {} '.format(tablename, select) curs.execute(select) get_cursor_columns( ) # caller may have to do this anyhow, because of the deferred execution of a generator global cursor_columns print 'cursor_columns: ', cursor_columns while True: row = curs.fetchone() if row is None: return yield list( row ) # conses a lot, but fetchone() returns tuples, and we can't modify those.
def ztest(where, split): conn = get_conn() curs = conn.cursor() curs.execute(''' select {cond} as grp, avg(isf) as mean, stddev(isf) as sd,count(isf) as n from isf_details where {where} group by {cond} '''.format(where=where, cond=split)) print 'where: ', where print 'split condition: ', split (grp1, mean1, sd1, n1) = curs.fetchone() (grp2, mean2, sd2, n2) = curs.fetchone() print(grp1, mean1, sd1, n1) print(grp2, mean2, sd2, n2) sp = math.sqrt( ((n1 - 1) * sd1 * sd1 + (n2 - 1) * sd2 * sd2) / (n1 + n2 - 2)) print 'pooled sp', sp diff = mean2 - mean1 print 'diff ', diff t = diff / (sp * math.sqrt((1 / float(n1)) + (1 / float(n2)))) return (mean1, mean2, diff, sp, t)
def db_update_rtime(rows, tablename, keys=cursor_columns): '''Update rows (can be an iterable) to the given table''' conn = get_conn() curs = conn.cursor() curs.execute('select * from {table}'.format(table=tablename)) global cursor_columns cursor_columns = [desc[0] for desc in curs.description] print '\n in db_update_rtime, cursor_columns is ', cursor_columns settings = ','.join( ['{col} = %s'.format(col=key) for key in cursor_columns]) curs = conn.cursor() sql = 'update {} set {} where rtime = %s'.format(tablename, settings) print('update using ', sql) insert_count = 0 for row in rows: if len(row) != len(cursor_columns): raise Exception('row has wrong length') insert_count += 1 if insert_count % 10000 == 0: print 'updated ', str(insert_count) data = list(row) data.append(row_get(row, 'rtime')) # extra occurrence for the key curs.execute(sql, data) print 'done updating'
def compute_isf(): conn = get_conn() curs = conn.cursor(MySQLdb.cursors.DictCursor) curs.execute('update insulin_carb_smoothed_2 SET ISF_trouble = null, isf = null, ISF_rounded = null') curs.execute('delete from isfvals') # make sure table is initially empty curs.execute('delete from isf_details') # make sure table is initially empty curs.execute('''select rtime, total_bolus_volume from insulin_carb_smoothed_2 where corrective_insulin = 1 ''') # and year(rtime) = 2018''') rows = curs.fetchall() # some stats total = len(rows) skipped_before = 0 # events skipped because of insulin in BEFORE period skipped_small = 0 # events skipped because bolus too small skipped_middle = 0 # events skipped because bolus during MIDDLE period skipped_nobg_beg = 0 # events skipped because start or end BG value missing (or both) skipped_nobg_end = 0 good_isf = 0 # events with good ISF value insulin_before = 0 #events with insulin 4 hours prior to the event for row in rows: start = row t2 = start['rtime'] t1 = t2 - timedelta(minutes=100) prior_insulin = any_bolus_in_time_span(conn, t2 - timedelta(hours=4),t1) if any_bolus_in_time_span(conn, t1,t2 - timedelta(minutes=5)): # print 'skipping {} because of insulin in the BEFORE period: {} to {}'.format(t2,t1,t2) skipped_before += 1 curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''', ['insulin before', t2]) continue # get values for the next 2:30 (worst case scenario) rows = get_window(curs, start['rtime']) # Sum the boluses in the first 30 minutes bolus_sum, t3 = bolus_sum_during_start(rows,t2) if t3 != t2: # this is just informational. Can be deleted # print 'boluses in beginning from {} to {} sum to {}'.format(t2,t3,bolus_sum) pass if bolus_sum < min_bolus: # print 'bolus sum {} is too small; skipping {}'.format(bolus_sum,t3) curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''', ['bolus too small', t3]) skipped_small += 1 continue # from now on, use t3 rather than t2, particularly for looking up BG t4 = t3 + timedelta(minutes=100) t5 = t4 + timedelta(minutes=20) # Check for no boluses in middle time if boluses_in_time_range(rows, t3+timedelta(minutes=5), t4-timedelta(minutes=5)): # print 'skipping {} because of insulin in the middle period'.format(t3) skipped_middle += 1 curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''', ['insulin in middle', t3]) if t2 != t3: curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s where rtime = %s''', ['insulin in middle', t2]) continue # Check whether there were boluses in end time boluses_in_end = boluses_in_time_range(rows, t4, t5) #boluses_in_time_range(rows, t4+timedelta(minutes=5), t5) if boluses_in_end: # print 'insulin in the end period: {} to {}'.format(t4,t5) pass # Okay, ready for calculation. bg_at_t3 = bg_at_time(rows, t3) bg_rows = get_bg_time_window(curs,t5) bg_at_t5 = bg_at_time_extended(bg_rows,t5) if bg_at_t3 and bg_at_t5: if prior_insulin: insulin_before += 1 isf = (bg_at_t3 - bg_at_t5) / bolus_sum #print 'isf {} to {} => ( {} - {} ) / {} => {:.2f} '.format(t3,t5,bg_at_t3, bg_at_t5, bolus_sum, isf) good_isf += 1 if boluses_in_end: curs.execute('''UPDATE insulin_carb_smoothed_2 SET ISF_trouble = %s, ISF_rounded = %s where rtime = %s''', ['insulin at end', isf, t3]) else: curs.execute('''UPDATE insulin_carb_smoothed_2 SET isf = %s where rtime = %s''', [isf, t3]) # New: record details of the calc in the isfvals table. [Scott 12/13/2019] #curs.execute('''insert into isf_details(rtime,isf,bg0,bg1,bolus) values (%s,%s,%s,%s,%s)''', # [t3, isf, bg_at_t3, bg_at_t5, bolus_sum]) curs.execute('''insert into isf_details(rtime,isf,bg0,bg1,bolus,prior_insulin) values (%s,%s,%s,%s,%s,%s)''',[t3,isf,bg_at_t3,bg_at_t5, bolus_sum,prior_insulin]) else: #skipped_bg += 1 skipped = False if not bg_at_t3: skipped_nobg_beg += 1 skipped = True curs.execute('''UPDATE insulin_carb_smoothed_2 set ISF_trouble = %s where rtime = %s''', [ 'nobg', t3]) # 'missing start BG value' if not bg_at_t5 and not skipped: skipped_nobg_end += 1 #print 'nobg at end', t3 curs.execute('''UPDATE insulin_carb_smoothed_2 set ISF_trouble = %s where rtime = %s''', [ 'nobg', t3]) # 'missing end BG value' #print "start: ", startbg, "end: ", endbg # check if any additional insulin given within 30 minutes of start -- done # check if any additional insulin given later in that time range -- done # check if we have a CGM or BG value near the beginning of the range AND -- done # check if we have a CGM or BG value near the end of the range -- done # if we have *both* CGM and BG, take the BG # compute ISF based on starting and ending CGM or BG # update database using start (the primary key for ICS2) #raw_input('another?') # end of loop print('''There were {} corrective insulin events from 2014 - 2018. {} events were skipped because of insulin before the START period {} events were skipped because the bolus was too small {} events were skipped because there was a bolus in the MIDDLE period {} events were skipped because start BG was not available, {} events were skipped because end BG was not available, leaving {} events with a good ISF and {} good events with insulin within 4 hours before event {} skipped + {} good is {} total.'''.format(total, skipped_before, skipped_small, skipped_middle, skipped_nobg_beg,skipped_nobg_end,good_isf,insulin_before, (skipped_before + skipped_small + skipped_middle + skipped_nobg_beg + skipped_nobg_end), good_isf,total))
def get_cursor_columns(conn=get_conn(), tablename='insulin_carb_smoothed'): curs = conn.cursor() curs.execute('select * from {} limit 0'.format(tablename)) global cursor_columns cursor_columns = [desc[0] for desc in curs.description] return cursor_columns
def num_rows(conn=get_conn(), tablename='insulin_carb_smoothed'): curs = conn.cursor() curs.execute('select count(*) from {}'.format(tablename)) row = curs.fetchone() return row[0]
def gen_insulin_carb_vrows(conn=get_conn(), rows=None, pipeIn=None): '''This version generates virtual rows from rows (real ones), with timestamps at 5 minute intervals. It's the basis of the insulin_carb_smoothed table.''' # Loop over real rows, creating virtual rows. The invariant is that # curr <= vrow < next. When vrow catches up to next, discard curr, # make curr be next, and get another next. If timestamp of new next is # equal to curr, merge them and get another next. # Now that we are reading from ICG, merging should never happen. curr_row = rows.next() next_row = rows.next() curr_time = curr_row['rtime'] next_time = next_row['rtime'] if curr_time == next_time: raise Exception('before loop: next row time equals curr row time') vrow_time = curr_time delta5 = timedelta(minutes=5) global merged_rows # should stay empty debug = False merged_rows = [] # print('vrow_time',vrow_time,'next_time',next_time) try: # loop over vrows. Exit condition is when we get StopIteration from real rows while True: # This outer loop produces vrows # print('vrow_time',vrow_time,'next_time',next_time) if vrow_time >= next_time: # this inner loop pulls in real rows # advance everything. if there's no next row, this will raise # StopIteration, which is perfect while True: curr_row = next_row curr_time = next_time next_row = rows.next() next_time = next_row['rtime'] if next_time > curr_time: break # merge from curr into next, since the top of the # loop discards curr_row. Should not happen merge_rows(curr_row, next_row) if vrow_time == curr_time: # yield a real row yield curr_row else: # yield a virtual row. vrow = { 'rtime': vrow_time, 'real_row': 0, 'user': curr_row['user'] } # fill in other fields with None for key in curr_row.iterkeys(): if key not in vrow: vrow[key] = None yield vrow vrow_time = vrow_time + delta5 except StopIteration: yield curr_row print '''Done generating virtual rows. End time is {end} and we merged {nmerge} rows'''.format(end=curr_row['rtime'], nmerge=len(merged_rows))