def dbstart(): conn = psql.dbstart(schemaname="freesurfer") cur = conn.cursor() # Creates table try: cur.execute("""CREATE TABLE IF NOT EXISTS freesurfer.log_files ( Subject TEXT CONSTRAINT subkey PRIMARY KEY, Date TEXT, Version TEXT, Dicom TEXT, OrigLogFile TEXT );""") except: print 'Table (log_files) creation error' conn.rollback() try: cur.execute("""CREATE TABLE IF NOT EXISTS freesurfer.hip_vol ( Subject TEXT CONSTRAINT subkey2 PRIMARY KEY, MRN TEXT, L_volume DECIMAL, R_volume DECIMAL, eTIV DECIMAL, normL_volume DECIMAL, normR_volume DECIMAL, normTot_volume DECIMAL );""") except: print 'Table (hip_vol) creation error' conn.rollback() conn.commit() cur.close() return conn
def hipVols(numtries = 2): conn = None if psql: while conn is None and numtries > 0: conn = psql.dbstart() numtries = numtries - 1 if conn: # freesurfer hip_vol is now in scidb target, try that first, otherwise can load from postgres cols = ['Subject', 'MRN', 'normTot_volume'] fhiprows = scidb.selectDB(toselect=','.join([c.lower() for c in cols])) # cols = ['Subject', 'MRN', 'normTot_volume'] # fhiprows = psql.selectDB(conn, ['Subject', 'MRN', 'normTot_volume'], "hip_vol", "freesurfer") # adding date and dicom file name? cols.extend(['Date','Dicom']) oldrows = fhiprows newrows = [] for row in oldrows: subject = row[0] row = list(row) qrows = psql.selectDB(conn,['Date','Dicom'],"log_files","freesurfer",whereclause="Subject = %s"%subject) if qrows is not None and len(qrows) > 0: row.extend(list(qrows[0])) else: row.extend(['','']) newrows.append(row) # adding birthdate from rpdr cols.extend(['Date_of_Birth']) oldrows = newrows newrows = [] for row in oldrows: row[1] = str.join('',[a for a in row[1] if a.isdigit()]) mrn = row[1] qrows = psql.selectDB(conn,['Date_of_Birth'],"Dem","rpdr",whereclause="MRN = '%s'"%mrn) if qrows is not None and len(qrows) > 0: val = str(qrows[0][0]) row.append(val) else: row.append('') newrows.append(row) # adding DICOM information? how about seriesdescription, protocolname, and patientage? cols.extend(['SeriesDescription','ProtocolName','PatientAge']) oldrows = newrows newrows = [] for row in oldrows: # many extra things in stored dicom paths: just keep the last part, raw filename? dicom = row[4].split('/')[-1] qrows = psql.selectDB(conn,['SeriesDescription','ProtocolName','PatientAge'],"imagemeta","dicom",whereclause="FilePath LIKE '%"+str(dicom)+"'") if qrows is not None and len(qrows) > 0: row.extend(list(qrows[0])) else: row.extend(['','','']) newrows.append(row) psql.dbend(conn) newrows = [cols] + newrows return newrows # otherwise, fake test data can be returned? return [['Subject', 'MRN', 'normTot_volume','Date','Dicom','Date_of_Birth','SeriesDescription','ProtocolName','PatientAge'],['subj###', '###', 0.12,'DateTime1','FilePath','DateTime2','Series','T1Weighted',18]];
def __init__(self, dataDir, dbName="RPDR_DATASET"): # save directory self.dir = dataDir # compute path to SQLite database # self.dbPath = os.path.join( self.dir, '%s.sqlite' % dbName ) # search for RPDR text files self.tables = self.findTables(self.dir) if len(self.tables) < 1: print "Warning! No RPDR files found in given directory: %s" % self.dir # search for SQLite database # if not os.path.isfile(self.dbPath): # print "Warning! SQLite database found: %s" % self.dbPath # return # # create/connect to SQLite database and enable dictionary access of rows # if not os.path.isfile(self.dbPath): # print "Creating SQLite database: %s" % self.dbPath # sometimes multiple connection attempts are required for psql, not sure why self.dbCon = None nTries = 3 n = nTries while n > 0 and self.dbCon is None: self.dbCon = psql.dbstart(schemaname="rpdr") n -= 1 if self.dbCon is None: print "Failed to connect in %s tries" % nTries return print "Connected, will read from %s" % self.dir # self.dbCon.row_factory = sqlite3.Row # loop through RPDR text files for tableName in self.tables: if not self.dbExistsRpdrTable(tableName): # create RPDR table in database self.dbCreateRpdrTable(tableName) if self.dbExistsRpdrTable(tableName): # fill table self.dbFillRpdrTable(tableName) # count check count = self.dbCountRpdrTable(tableName) print "%s rows in table %s" % (count, tableName) if count > 0: self.dbCon.commit()
def dbStats(numtries = 2): conn = None if psql: while conn is None and numtries > 0: conn = psql.dbstart() numtries = numtries - 1 if conn: schemas = psql.allSchemas(conn) nestedresponse = {} for s in schemas: nestedresponse[s] = {} tables = psql.tablesInSchema(conn, s) for t in tables: v = psql.countall(conn, t, schemaname=s) nestedresponse[s][t] = v psql.dbend(conn) return nestedresponse return {'data':{'schema1':{'table1':200,'table2':300},'schema2':{'table3': 400}}}
def dbstart(): conn = psql.dbstart(schemaname="rpdr") return conn
def dbstart(coltags=tag_types): return psql.dbstart("dicom", "imagemeta", coltags)