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)
