def clinicalItemFromSourceItem(self, sourceItem, category, conn):
# Load or produce a clinical_item record model for the given sourceItem
sourceItem_description = "%s:%s" % (sourceItem['antibiotic_name'], sourceItem['suseptibility'])
clinicalItemKey = (category["clinical_item_category_id"], sourceItem_description); ########## TODO probably needs to change
if clinicalItemKey not in self.clinicalItemByCompositeKey:
# Clinical Item does not yet exist in the local cache. Check if in database table (if not, persist a new record)
if sourceItem['antibiotic_name'] is None and sourceItem['suseptibility'] is None: # we only get here if no bacteria grew
name = "Negative Culture"
description ="Microculture Grew No Bacteria"
else:
name = "%s:%s" % (sourceItem['antibiotic_name'], sourceItem['suseptibility'])
description = "%s TO %s" % (sourceItem['suseptibility'], sourceItem['antibiotic_name'])
clinicalItem = \
RowItemModel \
( { "clinical_item_category_id": category["clinical_item_category_id"],
"external_id": None,
"name": name,
"description": description
}
);
(clinicalItemId, isNew) = DBUtil.findOrInsertItem("clinical_item", clinicalItem, conn=conn);
clinicalItem["clinical_item_id"] = clinicalItemId;
self.clinicalItemByCompositeKey[clinicalItemKey] = clinicalItem;
return self.clinicalItemByCompositeKey[clinicalItemKey];
def __init__(self):
"""Default constructor"""
self.connFactory = DBUtil.ConnectionFactory()
# Default connection source
self.categoryBySourceDescr = dict()
self.clinicalItemByCategoryIdExtId = dict()
def _executeCachedDBQuery(self, query):
# Only query if results not in cache.
cacheKey = str(query)
if cacheKey not in self.dbCache:
self.dbCache[cacheKey] = DBUtil.execute(query)
return self.dbCache[cacheKey]
def queryPatients(period, locations, rxCount):
log.info("Select patients fitting criteria in designated time period: (%s,%s)" % period);
query = SQLQuery();
query.addSelect("med.pat_id");
query.addSelect("count(order_med_id)");
query.addFrom("stride_mapped_meds as map");
query.addFrom("stride_order_med as med");
query.addFrom("stride_patient as pat");
query.addWhere("analysis_status = 1");
query.addWhere("map.medication_id = med.medication_id");
query.addWhere("med.pat_id = pat.pat_id");
query.addWhere("possible_oncology = 0");
query.addWhereIn("patient_location", locations );
query.addWhereOp("ordering_datetime",">", period[0] );
query.addWhereOp("ordering_datetime","<", period[-1] );
query.addGroupBy("med.pat_id");
query.addHaving("count(order_med_id) >2");
results = DBUtil.execute(query);
cols = ["patientId","nOpioidRx"];
patientDF = pd.DataFrame(results,columns=cols);
#patientDF.set_index("patientId",drop=False,inplace=True);
patientDF["periodStart"] = period[0]; # Identify this group of patient records
return patientDF;
def _get_average_orders_per_patient(self):
# Initialize DB cursor.
cursor = self._connection.cursor()
# Get average number of results for this lab test per patient.
query = SQLQuery()
if LocalEnv.DATASET_SOURCE_NAME == 'STRIDE': #TODO: add STRIDE component routine
query.addSelect('CAST(pat_id AS BIGINT) AS pat_id')
query.addSelect('COUNT(sop.order_proc_id) AS num_orders')
query.addFrom('stride_order_proc AS sop')
query.addFrom('stride_order_results AS sor')
query.addWhere('sop.order_proc_id = sor.order_proc_id')
query.addWhereIn("proc_code", [self._lab_panel])
components = self._get_components_in_lab_panel()
query.addWhereIn("base_name", components)
query.addGroupBy('pat_id')
elif LocalEnv.DATASET_SOURCE_NAME == 'UMich':
query.addSelect('CAST(pat_id AS BIGINT) AS pat_id')
query.addSelect('COUNT(order_proc_id) AS num_orders')
query.addFrom('labs')
query.addWhereIn(self._varTypeInTable, [self._lab_var])
components = self._get_components_in_lab_panel()
query.addWhereIn("base_name", components)
query.addGroupBy('pat_id')
log.debug('Querying median orders per patient...')
results = DBUtil.execute(query)
order_counts = [row[1] for row in results]
if len(order_counts) == 0:
error_msg = '0 orders for lab "%s."' % self._lab_var
log.critical(error_msg)
raise Exception(error_msg)
# sys.exit('[ERROR] %s' % error_msg) # sxu: sys.exit cannot be caught by Exception
else:
return numpy.median(order_counts)
def loadRespiratoryViralPanelItemIds(extractor):
# labCategoryId = 6;
labCategoryId = DBUtil.execute(
"select clinical_item_category_id from clinical_item_category where description like 'Lab'"
)[0][0]
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addFrom("clinical_item")
query.addWhereEqual("analysis_status", 1)
query.addWhereIn("clinical_item_category_id", (labCategoryId, ))
query.addWhere("description ~* '%s'" % 'Respiratory.*Panel')
respiratoryViralPanelItemIds = set()
for row in DBUtil.execute(query):
respiratoryViralPanelItemIds.add(row[0])
return respiratoryViralPanelItemIds
def _insertUMichTestRecords(self):
db_name = medinfo.db.Env.DB_PARAM['DSN']
db_path = medinfo.db.Env.DB_PARAM['DATAPATH']
conn = sqlite3.connect(db_path + '/' + db_name)
table_names = ['labs', 'pt_info', 'demographics', 'encounters', 'diagnoses']
for table_name in table_names:
columns = FMTU.FM_TEST_INPUT_TABLES["%s_columns"%table_name]
column_types = FMTU.FM_TEST_INPUT_TABLES["%s_column_types"%table_name]
df = pd.DataFrame()
for one_line in FMTU.FM_TEST_INPUT_TABLES['%s_data'%table_name]:
df = df.append(dict(zip(columns, one_line)), ignore_index=True)
df.to_sql(table_name, conn, if_exists="append", index=False)
# First, write basic (pat_id, order_time) episode information to TempFile
# Then, all [[pat_id, event_time]] operations are based on these episodes
# i.e., pat_id are all from these pat_ids
patientEpisodeQuery = SQLQuery()
patientEpisodeQuery.addSelect("CAST(pat_id AS INTEGER) AS pat_id")
patientEpisodeQuery.addSelect("order_time")
patientEpisodeQuery.addFrom("labs")
self.cursor.execute(str(patientEpisodeQuery), patientEpisodeQuery.params)
self.factory.setPatientEpisodeInput(self.cursor, "pat_id", "order_time")
self.factory.processPatientEpisodeInput()
resultEpisodeIterator = self.factory.getPatientEpisodeIterator()
resultPatientEpisodes = list()
for episode in resultEpisodeIterator:
episode["pat_id"] = int(episode["pat_id"])
episode["order_time"] = DBUtil.parseDateValue(episode["order_time"])
resultPatientEpisodes.append(episode)
def queryClinicalItems(outputFile, clinicalItemIds, patientById):
log.info("Query Clinical Items: %s" % str(clinicalItemIds))
formatter = TextResultsFormatter(outputFile)
colNames = ["patient_id", "item_date"]
query = SQLQuery()
for col in colNames:
query.addSelect(col)
query.addFrom("patient_item")
query.addWhereIn("clinical_item_id", clinicalItemIds)
query.addWhereIn("patient_id", patientById.keys())
query.addOrderBy("patient_id")
query.addOrderBy("item_date")
DBUtil.execute(query, includeColumnNames=True, formatter=formatter)
def itemCollectionFromSourceItem(self, sourceItem, conn):
# Load or produce an item_collection record model for the given sourceItem
if sourceItem["protocol_id"] is None:
# No order set link to this item, so nothing to return
return None
collectionKey = "%(protocol_id)s-%(protocol_name)s-%(section_name)s-%(smart_group)s" % sourceItem
self.itemCollectionByKeyStr = dict()
# Local cache to track item collections
if collectionKey not in self.itemCollectionByKeyStr:
# Collection does not yet exist in the local cache. Check if in database table (if not, persist a new record)
collection = \
RowItemModel \
( { "external_id": sourceItem["protocol_id"],
"name": sourceItem["protocol_name"],
"section": sourceItem["section_name"],
"subgroup": sourceItem["smart_group"],
}
)
(collectionId, isNew) = DBUtil.findOrInsertItem("item_collection",
collection,
conn=conn)
collection["item_collection_id"] = collectionId
self.itemCollectionByKeyStr[collectionKey] = collection
return self.itemCollectionByKeyStr[collectionKey]
def clinicalItemFromSourceItem(self, sourceItem, category, conn):
# Load or produce a clinical_item record model for the given sourceItem
clinicalItemKey = (category["clinical_item_category_id"],
sourceItem["proc_code"])
# This should be what determines a new unique clinical_item.
# Some debate about whether to distinguish by proc_id or proc_code, but there are many labs and other procs
# that use different proc_ids even though they are obviously the same. Go link in STRIDE_ORDER_PROC for examples like LABA1C.
# The self.clinicalItemByCategoryIdExtId is supposed to keep track of which clinical_items we're already aware of,
# but not that it starts blank when this module runs.
# So you in theory should only run this conversion process on a database once
# (otherwise it will not be aware that a bunch of duplicate clinical_items already exist in the database).
# Alternatively, this module should be updated, so that it initializes this key tracker with whatever is already in the database.
if clinicalItemKey not in self.clinicalItemByCategoryIdExtId:
# Clinical Item does not yet exist in the local cache. Check if in database table (if not, persist a new record)
clinicalItem = \
RowItemModel \
( { "clinical_item_category_id": category["clinical_item_category_id"],
"external_id": sourceItem["proc_id"],
"name": sourceItem["proc_code"],
"description": sourceItem["description"],
}
)
(clinicalItemId, isNew) = DBUtil.findOrInsertItem("clinical_item",
clinicalItem,
conn=conn)
clinicalItem["clinical_item_id"] = clinicalItemId
self.clinicalItemByCategoryIdExtId[clinicalItemKey] = clinicalItem
return self.clinicalItemByCategoryIdExtId[clinicalItemKey]
def _getNonNullBaseNames(self):
query = SQLQuery()
# SELECT
query.addSelect(BASE_NAME)
query.addSelect('max_result_flag')
# FROM
query.addFrom('order_result_stat')
# WHERE
query.addWhere('max_result_flag is not null')
print query
print query.getParams()
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results, columns=query.select).to_csv('base_names.csv', index=False)
def _test_dataConversion_aggregate(self):
# Run the data conversion on the same data and look for expected records
log.debug("Run the conversion process...")
convOptions = ConversionOptions()
convOptions.startDate = TEST_START_DATE
convOptions.aggregate = True
self.converter.convertSourceItems(convOptions)
# Just query back for the same data, de-normalizing the data back to a general table
testQuery = \
"""
select
pi.external_id as pi_external_id,
pi.patient_id,
pi.encounter_id,
cic.description as cic_description,
ci.external_id as ci_external_id,
ci.name,
ci.description as ci_description,
pi.item_date
from
%s.patient_item as pi,
%s.clinical_item as ci,
%s.clinical_item_category as cic
where
pi.clinical_item_id = ci.clinical_item_id and
ci.clinical_item_category_id = cic.clinical_item_category_id and
cic.source_table = '%s'
order by
pi.external_id desc, ci.external_id desc
""" % (TEST_DEST_DATASET, TEST_DEST_DATASET, TEST_DEST_DATASET, TEST_SOURCE_TABLE)
expectedData = \
[]
actualData = DBUtil.execute(testQuery)
self.assertEqualTable(expectedData, actualData)
def itemCollectionFromSourceItem(self, sourceItem, conn):
# Load or produce an item_collection record model for the given sourceItem
if sourceItem["protocol_id"] is None:
# No order set link to this item, so nothing to return
return None
key = {
"protocol_id":
sourceItem["protocol_id"],
"ss_section_id":
sourceItem["ss_section_id"],
"ss_sg_key":
sourceItem["ss_sg_key"].strip().upper()
if sourceItem["ss_sg_key"] is not None else None
}
collection_key = "%(protocol_id)d-%(ss_section_id)s-%(ss_sg_key)s" % key
if collection_key not in self.itemCollectionByKeyStr:
# Collection does not yet exist in the local cache. Check if in database table (if not, persist a new record)
collection = RowItemModel({
"external_id": sourceItem["protocol_id"],
"name": sourceItem["protocol_name"],
"section": sourceItem["ss_section_name"],
"subgroup": sourceItem["ss_sg_name"],
})
(collectionId, isNew) = DBUtil.findOrInsertItem("item_collection",
collection,
conn=conn)
collection["item_collection_id"] = collectionId
self.itemCollectionByKeyStr[collection_key] = collection
return self.itemCollectionByKeyStr[collection_key]
def action_default(self):
# Read checkboxes by presence or absence of field
self.requestData[
"incCols"] = "" # Checkboxes not passed if unchecked, so extra step to ensure uncheck is persisted
incCols = False
if "incCols" in self.mForm:
self.requestData["incCols"] = self.mForm["incCols"].value
incCols = True
# Point to the specified database
connFactory = self.connectionFactory()
timer = time.time()
# Just execute a normal query, possibly with a result set
results = DBUtil.execute(self.mForm["input"].value,
includeColumnNames=incCols,
connFactory=connFactory)
if type(results) == list: # Result set, format as table
formatter = TextResultsFormatter(StringIO())
formatter.formatResultSet(results)
self.requestData["resultsText"] = formatter.getOutFile().getvalue()
headerRowFormat = None
if incCols: headerRowFormat = "th"
formatter = HtmlResultsFormatter(StringIO(), headerRowFormat)
formatter.formatResultSet(results)
self.requestData["resultsHtml"] = formatter.getOutFile().getvalue()
self.requestData["resultsInfo"] = "(%d rows) " % len(results)
else:
self.requestData[
"resultsText"] = "%d rows affected (or other return code)" % results
timer = time.time() - timer
self.requestData["resultsInfo"] += "(%1.3f seconds)" % timer
def _getClinicalItems(self):
query = SQLQuery()
# SELECT
query.addSelect(CLINICAL_ITEM_ID)
query.addSelect('name')
# FROM
query.addFrom('clinical_item')
print(query)
print(query.getParams())
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results,
columns=query.select).to_csv('clinical_items.csv',
index=False)
def setCacheData(self, key, value, conn=None):
"""Utility function to set cached data item in data_cache table"""
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
# Clear any prior setting to make way for the new one
self.clearCacheData(key, conn=conn)
insertQuery = DBUtil.buildInsertQuery(
"data_cache", ("data_key", "data_value", "last_update"))
insertParams = (key, str(value), datetime.now())
DBUtil.execute(insertQuery, insertParams, conn=conn)
if not extConn:
conn.close()
def loadClinicalItemBaseCountByItemId(self, countPrefix=None, acceptCache=True, conn=None):
"""Helper query to get the baseline analyzed item counts for all of the clinical items
If countPrefix is provided, can use alternative total item counts instead of the default item_count,
such as patient_count or encounter_count to match the respective association query baselines used.
"""
extConn = True;
if conn is None:
conn = self.connFactory.connection();
extConn = False;
try:
# First ensure the denormalized count data is updated
self.updateClinicalItemCounts(acceptCache=acceptCache, conn=conn);
if countPrefix is None or countPrefix == "":
countPrefix = "item_"; # Default to general item counts, allowing for repeats per patient
baseCountQuery = SQLQuery();
baseCountQuery.addSelect("clinical_item_id");
baseCountQuery.addSelect("%scount" % countPrefix);
baseCountQuery.addFrom("clinical_item");
if acceptCache:
baseCountResultTable = self.executeCacheOption( baseCountQuery, conn=conn );
else:
baseCountResultTable = DBUtil.execute( baseCountQuery, conn=conn );
baseCountByItemId = dict();
for (itemId, baseCount) in baseCountResultTable:
baseCountByItemId[itemId] = baseCount;
return baseCountByItemId;
finally:
if not extConn:
conn.close();
def parseLabResultsFile(labFile):
log.info("Parse lab results file")
prog = ProgressDots()
labsByBaseNameByPatientId = dict()
# Dictionary of dictionaries of lists of result items
for labResult in TabDictReader(labFile):
if labResult["ord_num_value"] is not None and labResult[
"ord_num_value"] != NULL_STRING:
patientId = int(labResult["pat_id"])
labBaseName = labResult["base_name"]
resultValue = float(labResult["ord_num_value"])
resultTime = DBUtil.parseDateValue(labResult["result_time"])
if resultValue < LAB_SENTINEL_VALUE: # Skip apparent placeholder values
labResult["pat_id"] = labResult["patient_id"] = patientId
labResult["ord_num_value"] = resultValue
labResult["result_time"] = resultTime
if patientId not in labsByBaseNameByPatientId:
labsByBaseNameByPatientId[patientId] = dict()
if labBaseName not in labsByBaseNameByPatientId[patientId]:
labsByBaseNameByPatientId[patientId][labBaseName] = list()
labsByBaseNameByPatientId[patientId][labBaseName].append(
labResult)
prog.update()
prog.printStatus()
return labsByBaseNameByPatientId
def _get_random_patient_list(self):
# Initialize DB cursor.
cursor = self._connection.cursor()
# Get average number of results for this lab test per patient.
avg_orders_per_patient = self._get_average_orders_per_patient()
log.info('avg_orders_per_patient: %s' % avg_orders_per_patient)
# Based on average # of results, figure out how many patients we'd
# need to get for a feature matrix of requested size.
self._num_patients = int(numpy.max([self._num_requested_episodes / \
avg_orders_per_patient, 1]))
# Get numPatientsToQuery random patients who have gotten test.
# TODO(sbala): Have option to feed in a seed for the randomness.
query = SQLQuery()
query.addSelect('pat_id')
query.addFrom('stride_order_proc AS sop')
query.addWhereIn('proc_code', [self._lab_panel])
query.addOrderBy('RANDOM()')
query.setLimit(self._num_patients)
log.debug('Querying random patient list...')
results = DBUtil.execute(query)
# Get patient list.
random_patient_list = [row[0] for row in results]
return random_patient_list
def __init__(self):
"""Default constructor"""
self.connFactory = DBUtil.ConnectionFactory(); # Default connection source
self.categoryBySourceDescr = dict();
self.clinicalItemByCategoryIdExtId = dict();
self.resultStatsByBaseName = None;
def load_stride_to_psql():
# Build clean data files.
StrideLoader.build_clean_csv_files()
# Build psql schemata.
StrideLoader.build_stride_psql_schemata()
# Build paths to clean data files.
clean_data_dir = StrideLoader.fetch_clean_data_dir()
for raw_file in sorted(STRIDE_LOADER_PARAMS.keys()):
params = STRIDE_LOADER_PARAMS[raw_file]
# Build clean data file.
clean_file = params['clean_file'] % TABLE_PREFIX
log.info('loading %s...' % clean_file)
clean_path = os.path.join(clean_data_dir, clean_file)
# Uncompress data file.
unzipped_clean_path = clean_path[:-3]
with gzip.open(clean_path,
'rb') as f_in, open(unzipped_clean_path,
'wb') as f_out:
shutil.copyfileobj(f_in, f_out)
# psql COPY data from clean files into DB.
psql_table = params['psql_table'] % TABLE_PREFIX
log.debug('stride/data/clean/%s ==> %s' % (clean_file, psql_table))
# In some cases, two files going to the same table will have
# non-identical column names. Pass these explicitly so that
# psql knows which columns to try to fill from file.
# Strip the newline character.
with open(unzipped_clean_path, 'r') as f_in:
columns = f_in.readline()[:-1]
command = "COPY %s (%s) FROM '%s' WITH (FORMAT csv, HEADER);" % (
psql_table, columns, unzipped_clean_path)
DBUtil.execute(command)
# Delete unzipped_clean_path.
os.remove(unzipped_clean_path)
# Run any one-off postprocessing transformations which all users
# of the STRIDE database should receive. Defer any application-specific
# transformations to other modules.
StrideLoader.process_stride_psql_db()
# Build indices.
StrideLoader.build_stride_psql_indices()
def tearDown(self):
"""Restore state from any setUp or test steps"""
log.info("Purge test records from the database")
DBUtil.execute \
( """delete from patient_item_collection_link
where item_collection_item_id in
( select item_collection_item_id
from item_collection_item as ici, item_collection as ic
where ici.item_collection_id = ic.item_collection_id
and ic.external_id < 0
);
"""
)
DBUtil.execute \
( """delete from item_collection_item
where item_collection_id in
( select item_collection_id
from item_collection as ic
where ic.external_id < 0
);
"""
)
DBUtil.execute("delete from item_collection where external_id < 0;")
DBUtil.execute \
( """delete from patient_item
where clinical_item_id in
( select clinical_item_id
from clinical_item as ci, clinical_item_category as cic
where ci.clinical_item_category_id = cic.clinical_item_category_id
and cic.source_table = 'stride_order_proc'
);
"""
)
DBUtil.execute \
( """delete from clinical_item
where clinical_item_category_id in
( select clinical_item_category_id
from clinical_item_category
where source_table = 'stride_order_proc'
);
"""
)
DBUtil.execute(
"delete from clinical_item_category where source_table = 'stride_order_proc';"
)
DBUtil.execute(
"delete from stride_orderset_order_proc where order_proc_id in (%s)"
% str.join(",", self.orderProcIdStrList))
DBUtil.execute(
"delete from stride_order_proc where order_proc_id in (%s)" %
str.join(",", self.orderProcIdStrList))
DBTestCase.tearDown(self)
def prepareItemAssociations(self, itemIdPairs, linkedItemIdsByBaseId,
conn):
"""Make sure all pair-wise item association records are ready / initialized
so that subsequent queries don't have to pause to check for their existence.
Should help greatly to reduce number of queries and execution time.
"""
clinicalItemIdSet = set()
#Do the below to convert the list of strings into a list of pairs, which is needed for the rest of this function
for index, pair in enumerate(itemIdPairs):
itemIdPairs[index] = eval(pair)
for (itemId1, itemId2) in itemIdPairs:
clinicalItemIdSet.add(itemId1)
clinicalItemIdSet.add(itemId2)
nItems = len(clinicalItemIdSet)
# Now go through all needed item pairs and create default records as needed
log.debug("Ensure %d baseline records ready" % (nItems * nItems))
for itemId1 in clinicalItemIdSet:
# Query to see which ones already exist in the database
# Do this for each source clinical item instead of all combinations to avoid excessive in memory tracking
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addSelect("subsequent_item_id")
query.addFrom("clinical_item_association")
query.addWhereEqual("clinical_item_id", itemId1)
query.addWhereIn("subsequent_item_id", clinicalItemIdSet)
associationTable = DBUtil.execute(query, conn=conn)
# Keep track in memory temporarily for rapid lookup
existingItemIdPairs = set()
for row in associationTable:
existingItemIdPairs.add(tuple(row))
for itemId2 in clinicalItemIdSet:
itemIdPair = (itemId1, itemId2)
if itemIdPair not in existingItemIdPairs and self.acceptableClinicalItemIdPair(
itemId1, itemId2, linkedItemIdsByBaseId):
defaultAssociation = RowItemModel(
itemIdPair, ("clinical_item_id", "subsequent_item_id"))
try: # Optimistic insert of a new item pair, should be safe since just checked above, but parallel processes may collide
DBUtil.insertRow("clinical_item_association",
defaultAssociation,
conn=conn)
except conn.IntegrityError, err:
log.warning(err)
pass
def querySourceItems(self, convOptions, progress=None, conn=None):
"""Query the database for list of all source clinical items (culture results, etc.)
and yield the results one at a time. If startDate provided, only return items whose
occurence date is on or after that date.
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Column headers to query for that map to respective fields in analysis table
headers = ["order_proc_anon_id","pat_anon_id","pat_enc_csn_anon_id","proc_code","organism_name","antibiotic_name","suseptibility", "shifted_result_time"];
query = SQLQuery();
for header in headers:
query.addSelect( header );
query.addFrom("stride_culture_micro");
# TODO: FIGURE OUT WHY CAN"T DO >= OPERATION HERE
# if convOptions.startDate is not None:
# query.addWhereOp("shifted_result_time",">=", convOptions.startDate);
# if convOptions.endDate is not None:
# query.addWhereOp("shifted_result_time","<", convOptions.endDate); # Still use begin date as common filter value
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
rowModel = RowItemModel( row, headers );
if rowModel['shifted_result_time'] is None: # Don't add if no result time given
row = cursor.fetchone();
continue
if rowModel['organism_name'] is not None: # if positive culture but results uninterpretable, don't add feature
if rowModel['suseptibility'] is None or rowModel['antibiotic_name'] == 'Method' or rowModel['antibiotic_name'] is None:
row = cursor.fetchone();
continue
# So that we don't run into directory issues later when writing temp files
try:
rowModel['antibiotic_name'] = rowModel['antibiotic_name'].replace('/', '-')
except: # When antibiotic name is none
pass
yield rowModel; # Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def loadPatientOrders(self,
patientId,
currentTime,
loadActive=True,
conn=None):
"""Load orders for the given patient that exist by the specified current time point.
loadActive - Specify whether to load active vs. inactive/completed orders. Set to None to load both
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("po.sim_patient_order_id")
query.addSelect("po.sim_user_id")
query.addSelect("po.sim_patient_id")
query.addSelect("po.sim_state_id")
query.addSelect("po.clinical_item_id")
query.addSelect("po.relative_time_start")
query.addSelect("po.relative_time_end")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addSelect("cic.source_table")
query.addSelect("cic.description as category_description")
query.addFrom("sim_patient_order as po")
query.addFrom("clinical_item as ci")
query.addFrom("clinical_item_category as cic")
query.addWhere("po.clinical_item_id = ci.clinical_item_id")
query.addWhere(
"ci.clinical_item_category_id = cic.clinical_item_category_id")
query.addWhereEqual("sim_patient_id", patientId)
query.addWhereOp("relative_time_start", "<=", currentTime)
if loadActive: # Filter out inactive orders here.
query.openWhereOrClause()
query.addWhere("relative_time_end is null")
query.addWhereOp("relative_time_end", ">", currentTime)
query.closeWhereOrClause()
#elif loadActive is not None: # Filter out active orders here.
# query.addWhereOp("relative_time_end","<=", currentTime)
if loadActive: # Organize currently active orders by category
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
query.addOrderBy("relative_time_start")
else: # Otherwise chronologic order
query.addOrderBy("relative_time_start")
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def removeItemCollectionTablesAddedLines(self, source_table):
"""delete added records"""
log.info(
'Removing item_collection_item and item_collection added lines in PSQL DB'
)
DBUtil.execute("""delete from item_collection_item ici
using clinical_item ci, clinical_item_category cic
where ici.clinical_item_id = ci.clinical_item_id
and ci.clinical_item_category_id = cic.clinical_item_category_id
and cic.source_table = '{}';
""".format(source_table),
conn=self.pgConn)
# TODO should be using source_table also
DBUtil.execute("delete from item_collection where true;",
conn=self.pgConn)
def test_runDBScript_commandline(self):
# Equivalent to test_runDBScript, but try higher level interface
# through command-line "main" method
origStdin = sys.stdin
sys.stdin = self.SCRIPT_FILE
argv = ["DBUtil.py", "--script", "-"]
DBUtil.main(argv)
sys.stdin = origStdin
# Run some other commands to see if scripts produced expected results
results = DBUtil.execute(
"select * from TestTypes where MyInteger > %s", (200, ))
self.assertEqual(2, len(results))
results = DBUtil.execute(
"select * from TestTypes where MyInteger < %s", (100, ))
self.assertEqual(0, len(results))
def __init__(self):
"""Default constructor"""
self.connFactory = DBUtil.ConnectionFactory(); # Default connection source, but Allow specification of alternative DB connection source
self.categoryBySourceDescr = dict(); # Local cache to track the clinical item category table contents
self.clinicalItemByCategoryIdExtId = dict(); # Local cache to track clinical item table contents
self.itemCollectionByKeyStr = dict(); # Local cache to track item collections
self.itemCollectionItemByCollectionIdItemId = dict(); # Local cache to track item collection items
def loadBloodCultureItemIds(extractor):
# microCategoryId = 15;
microCategoryId = DBUtil.execute(
"select clinical_item_category_id from clinical_item_category where description like 'Microbiology'"
)[0][0]
# Look for diagnostic tests indicating suspected infection / sepsis
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addFrom("clinical_item")
query.addWhereEqual("analysis_status", 1)
query.addWhereIn("clinical_item_category_id", (microCategoryId, ))
query.addWhere("description ~* '%s'" % 'Blood Culture')
bloodCultureItemIds = set()
for row in DBUtil.execute(query):
bloodCultureItemIds.add(row[0])
return bloodCultureItemIds
def __init__(self):
"""Default constructor"""
self.connFactory = DBUtil.ConnectionFactory()
# Default connection source, but Allow specification of alternative DB connection source
self.categoryBySourceDescr = dict()
# Local cache to track the clinical item category table contents
self.clinicalItemByCompositeKey = dict()
