def _pivotexecfunc(database,title,ycoltype,xcoltype,tblname,distinct=False,master=False,whereclause=None,result="count(*)"): headers = {} with database: for hdrtype in [ycoltype,xcoltype]: if distinct==True: _,headers[hdrtype],_ = tbl_query(database,"select distinct({0}) from {1}".format(hdrtype,tblname)) else: _,headers[hdrtype],_ = tbl_query(database,"select name from {0}".format(hdrtype)) headers['dow'] = ['MO','TU','WE','TH','FR'] headers[hdrtype] = [_hdrtype[0] for _hdrtype in headers[hdrtype]] resulttable = [] resulttable.append([title] + headers[ycoltype]) with database: for xaxishdr in headers[xcoltype]: row=[] row.append(xaxishdr) for yaxishdr in headers[ycoltype]: exec_str = "select {1} from {0} ".format(tblname,result) #exec_str = "select subject,teacher from {0} ".format(tblname) exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype) exec_str += " and {1} = {0} ".format("\""+str(xaxishdr)+"\"",xcoltype) if master == True: exec_str += " and status = \"master\" " if whereclause <> None: for pred,op,predval in whereclause: exec_str += " and {0} {1} {2} ".format(pred,op,"\""+str(predval)+"\"") _,results,exec_str = tbl_query(database,exec_str) try: if len(results[0]) > 1: row.append(",".join(results[0])) else: row.append(results[0][0]) pass except: row.append(0) resulttable.append(row) row=[title] with database: for yaxishdr in headers[ycoltype]: exec_str = "select count(*) from {0} ".format(tblname) exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype) _,results,_ = tbl_query(database,exec_str) try: row.append(results[0][0]) except: row.append(0) resulttable.append(row) return resulttable
def mask(oldlist,newlist,map,namemap=None): if namemap==None: namemap={} for i in range(len(oldlist)): namemap[oldlist[i]] = newlist[int(random()*len(newlist))] with database: for tablename,colnames in map.iteritems(): for colname in colnames: for oldname,newname in namemap.iteritems(): exec_str = " select __id,{0} ".format(colname) exec_str += " from {0} ".format(tablename) exec_str += " where {0} like \"%{1}%\"".format(colname,oldname) _,results,_ = tbl_query(database,exec_str) for row in results: __id = row[0] _old = row[1] _new = _old.replace(oldname,newname) exec_str = " update {0} ".format(tablename) exec_str += " set {0} = \"{1}\"".format(colname,_new) exec_str += " where __id = \"{0}\"".format(__id) tbl_query(database,exec_str) print exec_str
def session_code_gen(dbname,dryrun=False): database = Database(dbname) exec_str = "select s.name,lt.code || '.' || s.code" exec_str += " from subject as s,lessontype as lt" exec_str += " where s.lessontype = lt.name" with database: colnames,rows = tbl_query(database,exec_str) subject_lookup = dict((row[0],row[1]) for row in rows) exec_str = "select teacher,subject,__id,code,type,day from session" with database: colnames,rows = tbl_query(database,exec_str) enums = setenums('All','-1',database) for row in rows: teacher = row[0] subject = row[1] __id = row[2] oldcode = row[3] lessontype = row[4] dow = row[5] if dow == None: dow = "??" if teacher == None: teacher_code = "??" lessontype_code = "??" if lessontype == "WP": lessontype_code = "WP" if subject == None: subject_code = lessontype_code + ".??" elif subject_lookup.has_key(subject) == False: subject_code = lessontype_code + ".??" else: subject_code =subject_lookup[subject] teacher_code = enums['adult']['name2code'][teacher] #session_code = ".".join([teacher_code,subject_code,]) dow_code = enums['dow']['name2code'][dow] session_code = ".".join([teacher_code,subject_code,dow_code]) if oldcode <> session_code: with database: exec_str,_ = tbl_rows_update(database,'session', ['code',"\""+session_code+"\"",'__id',"\""+__id+"\""], dryrun=dryrun) print exec_str,oldcode log.log(thisfuncname(),4,msg="session code updated",execstr=exec_str,oldcode=oldcode)
def _versions_subjects(database,period,dow,student): exec_str = "select distinct(subject) from lesson" exec_str += " where period = \"{0}\" ".format(period) exec_str += " and student = \"{0}\" ".format(student) exec_str += " and dow = \"{0}\" ".format(dow) return(tbl_query(database,exec_str))
def getdbenum(enums, database, fldname, tblname, **kwargs): ''' every table has a code column which is a 2 digit unique mnemonic given a name lookup a code given a code lookup a name given a code/name get an enumeration get names, codes ''' #database = Database(dbname) exec_str = "select {1},code,enum from {0}".format(tblname, fldname) where_str = "" for key, value in kwargs.iteritems(): if where_str == "": where_str = " where {0} = {1}".format(key, value) else: where_str = where_str + " and {0} = {1}".format(key, value) exec_str = exec_str + where_str import sqlite3 with database: try: coldefn, values, _ = tbl_query(database, exec_str) except DBException, e: log.log(thisfuncname(), 0, exception=e, msg=e.message, tblname=tblname, fldname=fldname)
def _versions(database,period,dow,student): exec_str = "select \"lesson\",dow,period,subject,teacher,source,recordtype from lesson" exec_str += " where period = \"{0}\" ".format(period) exec_str += " and student = \"{0}\" ".format(student) exec_str += " and dow = \"{0}\" ".format(dow) return(tbl_query(database,exec_str))
def getdbenum(enums,database,fldname,tblname,**kwargs): ''' every table has a code column which is a 2 digit unique mnemonic given a name lookup a code given a code lookup a name given a code/name get an enumeration get names, codes ''' #database = Database(dbname) exec_str = "select {1},code,enum from {0}".format(tblname,fldname) where_str = "" for key,value in kwargs.iteritems(): if where_str == "": where_str = " where {0} = {1}".format(key,value) else: where_str = where_str + " and {0} = {1}".format(key,value) exec_str = exec_str + where_str import sqlite3 with database: try: coldefn,values,_ = tbl_query(database,exec_str) except DBException, e: log.log(thisfuncname(),0,exception=e,msg=e.message,tblname=tblname,fldname=fldname)
def test_(self): class MyTcxDerived(TcxAnalyzerAddDerivedData): def create_values(self, **kwargs): ''' to be overwritten; this is just a stub that creates copy of original data''' bucket_ts = [] for i in range(0, len(self.sourcedata)): _data = self.sourcedata[i] * kwargs['multiplier'] bucket_ts.append((self.sourceid[i], _data)) return bucket_ts tcx_add_col = MyTcxDerived(self.databasename) tcx_add_col.add('bar', 'id', 'watts', 'filename', "\"fileA\"", 'bar', 'foobar', 'integer', rows=[[3, '\"foo\"'], [4, '\"bar\"']]) with self.database: _, rows, _ = tbl_query(self.database, "select id,foobar from bar") expected_result = [[1, None], [2, None], [3, 'foo'], [4, 'bar']] self.assertListEqual(rows, expected_result)
def _dbid2userdefid(database,asdict=False): exec_str = "select __id,userobjid from lesson" _,values,_ = tbl_query(database,exec_str) if asdict==True: return(_asdict(values)) return(values)
def _findsessions(database,period,dow,teacher): exec_str = "select __id,teacher,code,subject,enum " exec_str += " from session" exec_str += " where period = {0}".format("\"" + str(period) + "\"") exec_str += " and dow = {0}".format("\"" + dow + "\"") exec_str += " and teacher = {0}".format("\"" + teacher + "\"") return(tbl_query(database,exec_str))
def _lastsaveversion_get(self): try: with self.database: colndefn,rows = tbl_query(self.database,"select max(saveversion) from lesson") return(rows[0][0]) except Exception: return(-1)
def test(self): self.tcxparser.persist(self.test_file_name2) database = Database('foo',True) with database: _,rows,_ = tbl_query(database,'select count(*) from foo where filename=\"'+os.path.splitext(self.test_file_name2)[0]+'\"') self.assertEqual(200,rows[0][0])
def _lastsaveversion_get(self): try: with self.database: colndefn, rows = tbl_query( self.database, "select max(saveversion) from lesson") return (rows[0][0]) except Exception: return (-1)
def _sessionversions(database,period,dow,subject): exec_str = "select \"session\",dow,period,subject,teacher,source,recordtype from session" exec_str += " where period = \"{0}\" ".format(period) exec_str += " and dow = \"{0}\" ".format(dow) exec_str += " and subject = \"{0}\" ".format(subject) exec_str += " and substatus = \"nochildrenatinit\" " exec_str += " and status = \"master\" " print exec_str return(tbl_query(database,exec_str))
def _execfunc(database,value,prep,dow): exec_str = "select s.code " exec_str += "from session as s,adult as a " #exec_str += "where a.prep = {0} and ".format(prep) exec_str += "where s.prep = {0} and ".format(prep) exec_str += "a.name = s.teacher and " exec_str += "s.period = {0} and ".format(value) #exec_str += "s.day = \"{0}\"".format(dow) exec_str += "s.dow = \"{0}\"".format(dow) return(tbl_query(database,exec_str))
def test_all(self): types = ['watts', 'hr'] files = ['fileA', 'fileB'] tcx_analyzer = TcxAnalyzer(self.databasename) for _type in types: for _file in files: tcx_analyzer.get_source_data(self.tablename, 'id', _type, 'filename', "\"" + _file + "\"") row = tcx_analyzer.get_ts_values() tcx_analyzer.persist_ts_values(_file, _type, row) with self.database: _, watts_rows, _ = tbl_query(self.database, "select * from watts_values") _, hr_rows, _ = tbl_query(self.database, "select * from hr_values") self.assertListEqual( watts_rows, [[u'fileA', 240, 262, 247], [u'fileB', 306, 311, 308]]) self.assertListEqual( hr_rows, [[u'fileA', 130, 133, 131], [u'fileB', 150, 157, 152]])
def test_persist_new_column(self): filename = "fileA" tcx_analyzer = TcxAnalyzer(self.databasename) tcx_analyzer.get_source_data(self.tablename, 'id', 'watts', 'filename', "\"" + filename + "\"") rows = tcx_analyzer.get_buckets('watts') tcx_analyzer.add_column_to_table(self.tablename, 'watt_bucket', 'string', rows) with self.database: _, row, _ = tbl_query( self.database, "select watt_bucket from " + self.tablename + " where filename=\"" + filename + "\"") self.assertListEqual(row, [[u'221>260'], [u'221>260'], [u'221>260'], [u'221>260'], [u'261>300'], [u'261>300']])
def test_from_db(self): tcxparser = TcxParser(TEST_FILES,databasename="foo",tablename="foo2") limits = tcxparser.get_limits_from_db('bar') expected_results = {u'test_9trackpoints': {'start': 2, 'end': 6}, u'test_all_trackpoints': {'start': 900, 'end': 1500}} self.assertEqual(limits,expected_results) tcxparser.process_files(summation_type="avg",limits=limits,bucket_size=2) tcxparser.persist() database = Database('foo',True) with database: _,rows,_ = tbl_query(database,'select watts from foo2 where filename=\"test_9trackpoints\"') self.assertListEqual(rows,self.wattsavg_points)
def get_source_data(self, tablename, pkcolname, valuecolname, predcolname, predvalue): ''' query's the database and sets the following object attributes sourceid : list of row indexex and sourcedata : list of values also stores the predicates used to build the query ''' querystr = "select " + pkcolname + "," + valuecolname + " from " + tablename + " where " + predcolname + "=" + predvalue try: with self.database: _, _rows, _ = tbl_query(self.database, querystr) if len(_rows[0]) == 0: raise Exception("zero rows retreived for query [" + querystr + "]") except Exception, e: log.log(PRIORITY.FAILURE, msg="failed to retrieve source data for [" + predcolname + "]=[" + predvalue + "][" + e.message + "]") return False
def _distinct(database,value,table): exec_str = "select distinct({1}) from {0}".format(table,value) #if orderby <> False: # exec_str += " order by prep" return tbl_query(database,exec_str)
def _maxsessionenum(database): exec_str = "select max(enum) from session" try: return tbl_query(database,exec_str) except OperationalError: return [None,[[0]],None]
def _formatsexecfunc(database,colortype): exec_str = "select f.name,c.hex " exec_str += "from formats as f, colors as c " exec_str += "where c.name = f.{0}".format(colortype) return(tbl_query(database,exec_str))
def _columnheaderexecfunc(database,pred=None,predvalue=None): exec_str = "select name from student" if pred <> None: exec_str = exec_str + " where {0} = {1}".format(pred,predvalue) return(tbl_query(database,exec_str))
def _rowheaderexecfunc(database,pred=None,predvalue=None): exec_str = "select name from period" return(tbl_query(database,exec_str))
def _rowcount(database,table): exec_str = "select count(*) from {0} ".format(table) return(tbl_query(database,exec_str))
def session_code_gen(dbname, dryrun=False): database = Database(dbname) exec_str = "select s.name,lt.code || '.' || s.code" exec_str += " from subject as s,lessontype as lt" exec_str += " where s.lessontype = lt.name" with database: colnames, rows = tbl_query(database, exec_str) subject_lookup = dict((row[0], row[1]) for row in rows) exec_str = "select teacher,subject,__id,code,type,day from session" with database: colnames, rows = tbl_query(database, exec_str) enums = setenums('All', '-1', database) for row in rows: teacher = row[0] subject = row[1] __id = row[2] oldcode = row[3] lessontype = row[4] dow = row[5] if dow == None: dow = "??" if teacher == None: teacher_code = "??" lessontype_code = "??" if lessontype == "WP": lessontype_code = "WP" if subject == None: subject_code = lessontype_code + ".??" elif subject_lookup.has_key(subject) == False: subject_code = lessontype_code + ".??" else: subject_code = subject_lookup[subject] teacher_code = enums['adult']['name2code'][teacher] #session_code = ".".join([teacher_code,subject_code,]) dow_code = enums['dow']['name2code'][dow] session_code = ".".join([teacher_code, subject_code, dow_code]) if oldcode <> session_code: with database: exec_str, _ = tbl_rows_update( database, 'session', [ 'code', "\"" + session_code + "\"", '__id', "\"" + __id + "\"" ], dryrun=dryrun) print exec_str, oldcode log.log(thisfuncname(), 4, msg="session code updated", execstr=exec_str, oldcode=oldcode)
def _dowexecfunc(database,value,prep,*args): exec_str = "select code from dow " return(tbl_query(database,exec_str))
def _refexecfunc(database,objtype): exec_str = "select name from " + objtype return(tbl_query(database,exec_str))
def _colorexecfunc(database): exec_str = "select name,hex from colors " return(tbl_query(database,exec_str))
def _rowheaderexecfunc(database): exec_str = "select name from period" return(tbl_query(database,exec_str))
def _sessionenum(database,code,period,prep): exec_str = "select enum from session where code = {0} ".format(code) exec_str += " and period = {0} ".format(period) exec_str += " and prep = {0} ".format(prep) return(tbl_query(database,exec_str))
if __name__ == "__main__": types = ['watts', 'hr'] dbname = "trainerroad" database = Database(dbname) tcx_analyzer = TcxAnalyzer(dbname) #data_filenames = ['20180126_10638564'] #metadata_filenames = ['20180126_10638564'] #UNCOMMENT THIS IF WANT TO REFRESH ALL _VALUES DATA # get all distinct filenames with database: _, data_filenames, _ = tbl_query(database, "select distinct filename from data") data_filenames = [_filename[0] for _filename in data_filenames] # get all filenames that have a meta record with database: _, metadata_filenames, _ = tbl_query( database, "select distinct filename from metadata") metadata_filenames = [_filename[0] for _filename in metadata_filenames] # create hr_values and watts_values tables and data tcx_analyzer.run(metadata_filenames, types) # adds duration in seconds columns to the metadata tables tcx_add_col = MyTcxDerived_Seconds(dbname) for filename in metadata_filenames: tcx_add_col.add('metadata', 'filename', 'start', 'filename',