__author__ = 'Sulantha' from SQLDB.DBClient import DBClient import csv newPETCSV = 'CSVFiles/CSV_RESULT_MCI_CONV_DATA.csv' DBC = DBClient() getAllMCIAtanyPointRIDs = "SELECT DISTINCT RID FROM CSV_SUBJECT_DX_DATA WHERE DX_Current_Code_TNL = 'MCI'" allRIDs = DBC.executeAllRes(getAllMCIAtanyPointRIDs) finalList = [['RID', 'DX_BL', 'REC_ID_1', 'REC_ID_2', 'VISCODE_1', 'VISCODE_2', 'EXAM_DATE_1', 'EXAM_DATE_2', 'DX_1', 'DX_2', 'CONV']] for rid in allRIDs: allSubjectINFOSQL = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(rid[0]) allSubjectINFO = DBC.executeAllRes(allSubjectINFOSQL) subRecs = [] for subRec in allSubjectINFO: subRecs.append(subRec) if len(subRecs) == 1: print("Only one rec found. Skipping. - {0}".format(subRecs[0])) for rec in subRecs: vis = 0 if rec[2] == 'bl' else int(rec[2][1:]) vis24_str = 'm{0}'.format(vis+24) visit24_rec = None for newRec in subRecs: if newRec[2] == vis24_str: visit24_rec = newRec break if visit24_rec: if rec[21] == 'MCI':
__author__ = 'Sulantha' from SQLDB.DBClient import DBClient DBClient = DBClient() getAllRecsSQL = "SELECT RID FROM CSV_SUBJECT_DX_DATA" dxCodeDict = {"NL":1, "Dementia":3,"MCI":2} dxSTRDict = {1:'CN', 3:'AD', 2:'MCI'} allRes = DBClient.executeAllRes(getAllRecsSQL) for rec in allRes: subRID = rec[0] allRecsForSub = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(subRID) subResults = DBClient.executeAllRes(allRecsForSub) subDXList = [] for subRec in subResults: dx = subRec[19] if dx == '': subDXList.append(0) continue if 'to' in dx: dx = dx.split('to')[1].strip() dxCode = dxCodeDict[dx] subDXList.append(dxCode) allRecsForSub = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(subRID) subResults = DBClient.executeAllRes(allRecsForSub) idx = 0
import datetime from SQLDB.DBClient import DBClient dbc = DBClient() getRescSQL = "SELECT * FROM CSV_RESULT_MCI_CONV_DATA" allResults = dbc.executeAllRes(getRescSQL) for rec in allResults: rec_id = rec[0] rid = rec[1] d1 = datetime.datetime.strptime(rec[7], '%Y-%m-%d') d2 = datetime.datetime.strptime(rec[8], '%Y-%m-%d') PETRecsSQL = "SELECT * FROM CSV_SUBJECT_PET_DATA WHERE RID = '{0}' AND SEQ_CODE = '{1}'".format(rid, 'TNL_PP') petData = dbc.executeAllRes(PETRecsSQL) allPETRECs = [] for petRec in petData: allPETRECs.append([i for i in petRec]) d1_match_list = [] d2_match_list = [] for petLine in allPETRECs: da = datetime.datetime.strptime(petLine[4], '%Y-%m-%d') if abs((da-d1).days) < 60: d1_match_list.append('{0}_{1}'.format(petLine[6], petLine[7])) if abs((da-d2).days) < 60: d2_match_list.append('{0}_{1}'.format(petLine[6], petLine[7])) sql = "INSERT INTO MCI_CONV_PET_MATCH VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}')".format(rid, rec[7], rec[8], ','.join(d1_match_list), ','.join(d2_match_list)) dbc.executeNoResult(sql)