def generatePatientItemsForCompositeId(self,
clinicalItemIds,
compositeId,
conn=None):
"""Create patient_item records for the composite to match the given clinical item ID patient items.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Record linking information
for componentId in clinicalItemIds:
linkModel = RowItemModel()
linkModel["clinical_item_id"] = compositeId
linkModel["linked_item_id"] = componentId
insertQuery = DBUtil.buildInsertQuery("clinical_item_link",
linkModel.keys())
insertParams = linkModel.values()
DBUtil.execute(insertQuery, insertParams, conn=conn)
# Extract back link information, which will also flatten out any potential inherited links
linkedItemIdsByBaseId = self.loadLinkedItemIdsByBaseId(conn=conn)
linkedItemIds = linkedItemIdsByBaseId[compositeId]
# Create patienItem records for the composite clinical item to overlap existing component ones
# First query for the existing component records
query = SQLQuery()
query.addSelect("*")
query.addFrom("patient_item")
query.addWhereIn("clinical_item_id", linkedItemIds)
results = DBUtil.execute(query, includeColumnNames=True, conn=conn)
patientItems = modelListFromTable(results)
# Patch component records to instead become composite item records then insert back into database
progress = ProgressDots(total=len(patientItems))
for patientItem in patientItems:
del patientItem["patient_item_id"]
patientItem["clinical_item_id"] = compositeId
patientItem["analyze_date"] = None
insertQuery = DBUtil.buildInsertQuery("patient_item",
patientItem.keys())
insertParams = patientItem.values()
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.info(err)
progress.Update()
# progress.PrintStatus();
finally:
if not extConn:
conn.close()
def patientItemFromSourceItem(self, sourceItem, clinicalItem, conn):
# Produce a patient_item record model for the given sourceItem
patientItem = \
RowItemModel \
( { "external_id": sourceItem["order_med_id"],
"patient_id": sourceItem["pat_id"],
"encounter_id": sourceItem["pat_enc_csn_id"],
"clinical_item_id": clinicalItem["clinical_item_id"],
"item_date": sourceItem["ordering_date"],
}
)
insertQuery = DBUtil.buildInsertQuery("patient_item",
patientItem.keys())
insertParams = patientItem.values()
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
patientItem["patient_item_id"] = DBUtil.execute(
DBUtil.identityQuery("patient_item"), conn=conn)[0][0]
except IntegrityError, err:
# If turns out to be a duplicate, okay, pull out existint ID and continue to insert whatever else is possible
log.info(err)
# Lookup just by the composite key components to avoid attempting duplicate insertion again
searchPatientItem = \
{ "patient_id": patientItem["patient_id"],
"clinical_item_id": patientItem["clinical_item_id"],
"item_date": patientItem["item_date"],
}
(patientItem["patient_item_id"],
isNew) = DBUtil.findOrInsertItem("patient_item",
searchPatientItem,
conn=conn)
def patientItemCollectionLinkFromSourceItem(self, sourceItem,
collectionItem, patientItem,
conn):
hash_key = hash('{}{}'.format(
patientItem["patient_item_id"],
collectionItem["item_collection_item_id"]))
if hash_key in self.patient_item_collection_links:
return
# Produce a patient_item_collection_link record model for the given sourceItem
patientItemCollectionLink = RowItemModel({
"patient_item_id":
patientItem["patient_item_id"],
"item_collection_item_id":
collectionItem["item_collection_item_id"],
})
insertQuery = DBUtil.buildInsertQuery(
"patient_item_collection_link",
list(patientItemCollectionLink.keys()))
insertParams = list(patientItemCollectionLink.values())
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
self.patient_item_collection_links.add(hash_key)
except conn.IntegrityError as err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.warn(err)
self.patient_item_collection_links.add(hash_key)
def patientItemModelFromSourceItem(self, sourceItem, clinicalItem, conn):
# Produce a patient_item record model for the given sourceItem
patient_item = RowItemModel({
"external_id":
None,
"patient_id":
int(sourceItem["rit_uid"][2:], 16),
"encounter_id":
None,
"clinical_item_id":
clinicalItem["clinical_item_id"],
"item_date":
str(sourceItem["itemDate"]
), # without str(), the time is being converted in postgres
"item_date_utc":
None, # it's a date - so, no need to have a duplicate here
})
insert_query = DBUtil.buildInsertQuery("patient_item",
patient_item.keys())
insert_params = patient_item.values()
try:
# Optimistic insert of a new unique item
DBUtil.execute(insert_query, insert_params, conn=conn)
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.warn(err)
def verifyVirtualItemLinked(self,
itemIdSequence,
virtualItemId,
linkedItemIdsByBaseId,
conn=None):
"""Verify links exist from the virtualItemId to those in the itemIdSequence.
If not, then create them in the database and in memory
"""
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
try:
if virtualItemId not in linkedItemIdsByBaseId:
linkedItemIdsByBaseId[virtualItemId] = set()
for componentId in itemIdSequence:
if componentId not in linkedItemIdsByBaseId[virtualItemId]:
linkModel = RowItemModel()
linkModel["clinical_item_id"] = virtualItemId
linkModel["linked_item_id"] = componentId
insertQuery = DBUtil.buildInsertQuery(
"clinical_item_link", list(linkModel.keys()))
insertParams = list(linkModel.values())
DBUtil.execute(insertQuery, insertParams, conn=conn)
linkedItemIdsByBaseId[virtualItemId].add(componentId)
finally:
if not extConn:
conn.close()
def compositeRelated(self,
clinicalItemIds,
itemName,
itemDescription,
categoryId,
compositeId=None,
conn=None):
"""A new clinical item will be created, with patient item records created to match every one currently
matching one of the specified clinical items.
Parameters specify new composite item name/code, description, and clinical item category to be created under.
Option to explicitly specify the composite clinical item Id value rather than taking a sequence number value (convenient for test cases)
Returns ID of newly created item
Depending on context, may wish to deactivateAnalysis of component items once this composite one is created
if they are no longer of interest.
Newly created composite item's default_recommend attribute will be reset to 0 since it presumably does not represent a
discrete order item.
Linking records will be created in clinical_item_link between the composite and and component clinical items
so that these relationships can be reconstructed
Examples this could be relevant for:
ICUVasopressors to include all vasopressor infusions (dopamine, norepinephrine, epinephrine, vasopressin, etc.)
All blood transfusion indexes, G vs J vs Feeding tube equivalent, Ear, Eyes med routes irrelevant which ear/eye.
Eventually lump together medication classes (e.g., any "PPI" same difference as choosing pantoprazole or omeprazole.
Eventually lump together diagnosis codes by major prefix to reduce granularity and improve general signal.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Build new composite item
compositeItem = RowItemModel()
compositeItem["name"] = itemName
compositeItem["description"] = itemDescription
compositeItem["clinical_item_category_id"] = categoryId
compositeItem["default_recommend"] = 0
if compositeId is not None:
compositeItem["clinical_item_id"] = compositeId
insertQuery = DBUtil.buildInsertQuery("clinical_item",
compositeItem.keys())
insertParams = compositeItem.values()
DBUtil.execute(insertQuery, insertParams, conn=conn)
if compositeId is None:
compositeId = DBUtil.execute(
DBUtil.identityQuery("clinical_item"), conn=conn)[0][0]
# Retrieve the just inserted item's ID
self.generatePatientItemsForCompositeId(clinicalItemIds,
compositeId,
conn=conn)
return compositeId
finally:
if not extConn:
conn.close()
def patientItemFromSourceItem(self, sourceItem, clinicalItem, conn):
# some prov_map_id values are NULL in starr_datalake2018
if sourceItem["prov_map_id"] is not None:
# prov_map_id starts with letters, we're interested only in number parts
external_id = int(
re.sub("[A-Z]+(\\d+)", "\\1", sourceItem["prov_map_id"]), 16)
else:
external_id = None
# Produce a patient_item record model for the given sourceItem
patientItem = RowItemModel({
"external_id":
external_id,
"patient_id":
int(sourceItem["rit_uid"][2:], 16),
"encounter_id":
sourceItem["pat_enc_csn_id_coded"],
"clinical_item_id":
clinicalItem["clinical_item_id"],
"item_date":
str(sourceItem["trtmnt_tm_begin_dt_jittered"]
), # without str(), the time is being converted in postgres
"item_date_utc":
str(sourceItem["trtmnt_tm_begin_dt_jittered_utc"]
) # without str(), the time is being converted in postgres
})
insertQuery = DBUtil.buildInsertQuery("patient_item",
list(patientItem.keys()))
insertParams = list(patientItem.values())
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
# Retrieve id of just inserted row
patientItem["patient_item_id"] = DBUtil.execute(
DBUtil.identityQuery("patient_item"), conn=conn)[0][0]
except conn.IntegrityError as err:
# If turns out to be a duplicate, okay, pull out existing ID and continue to insert whatever else is possible
log.info(
err
) # Lookup just by the composite key components to avoid attempting duplicate insertion again
searchPatientItem = {
"patient_id": patientItem["patient_id"],
"clinical_item_id": patientItem["clinical_item_id"],
"item_date": patientItem["item_date"],
}
(patientItem["patient_item_id"],
isNew) = DBUtil.findOrInsertItem("patient_item",
searchPatientItem,
conn=conn)
return patientItem
def patientItemFromSourceItem(self, sourceItem, clinicalItem, conn):
# Produce a patient_item record model for the given sourceItem
patientItem = RowItemModel({
"external_id":
sourceItem["order_proc_id_coded"],
"patient_id":
int(sourceItem["jc_uid"][2:], 16),
"encounter_id":
sourceItem["pat_enc_csn_id_coded"],
"clinical_item_id":
clinicalItem["clinical_item_id"],
"item_date":
sourceItem["order_time_jittered"],
"item_date_utc":
str(sourceItem["order_time_jittered_utc"]
), # without str(), the time is being converted in postgres
})
key_hash = hash('{}{}{}'.format(patientItem["patient_id"],
patientItem["clinical_item_id"],
patientItem["item_date"]))
if key_hash in self.patient_items:
return self.patient_items[key_hash]
insertQuery = DBUtil.buildInsertQuery("patient_item",
list(patientItem.keys()))
insertParams = list(patientItem.values())
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
patientItem["patient_item_id"] = DBUtil.execute(
DBUtil.identityQuery("patient_item"), conn=conn)[0][0]
self.patient_items[key_hash] = patientItem
except conn.IntegrityError as err:
# If turns out to be a duplicate, okay, pull out existint ID and continue to insert whatever else is possible
log.warn(
err
) # Lookup just by the composite key components to avoid attempting duplicate insertion again
searchPatientItem = {
"patient_id": patientItem["patient_id"],
"clinical_item_id": patientItem["clinical_item_id"],
"item_date": patientItem["item_date"],
}
(patientItem["patient_item_id"],
isNew) = DBUtil.findOrInsertItem("patient_item",
searchPatientItem,
conn=conn)
self.patient_items[key_hash] = patientItem
return patientItem
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 patientItemCollectionLinkFromSourceItem(self, sourceItem, collectionItem, patientItem, conn):
# Produce a patient_item_collection_link record model for the given sourceItem
patientItemCollectionLink = \
RowItemModel \
( { "patient_item_id": patientItem["patient_item_id"],
"item_collection_item_id": collectionItem["item_collection_item_id"],
}
);
insertQuery = DBUtil.buildInsertQuery("patient_item_collection_link", patientItemCollectionLink.keys() );
insertParams= patientItemCollectionLink.values();
try:
# Optimistic insert of a new unique item
DBUtil.execute( insertQuery, insertParams, conn=conn );
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.info(err);
def accessLogFromSourceItem(self, sourceItem, user, metric, metricLine, conn):
# Produce an access log for the given sourceItem with links to the lookup user and metric
# Only record once for multi-line descriptions, so check the line number
accessLog = None;
if metricLine == 1:
accessLog = \
RowItemModel \
( { "user_id": user["user_id"],
"de_pat_id": sourceItem["de_pat_id"],
"metric_id": metric["metric_id"],
"access_datetime": sourceItem["access_datetime"],
}
);
insertQuery = DBUtil.buildInsertQuery("access_log", accessLog.keys() );
insertParams= accessLog.values();
DBUtil.execute( insertQuery, insertParams, conn=conn );
return accessLog;
def patientItemModelFromSourceItem(self, sourceItem, clinicalItem, conn):
# Produce a patient_item record model for the given sourceItem
patientItem = \
RowItemModel \
({"external_id": None,
"patient_id": int(sourceItem["rit_uid"][2:], 16),
"encounter_id": None,
"clinical_item_id": clinicalItem["clinical_item_id"],
"item_date": sourceItem["itemDate"],
}
)
insertQuery = DBUtil.buildInsertQuery("patient_item",
patientItem.keys())
insertParams = patientItem.values()
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.info(err)
def mergeRelated(self,
baseClinicalItemId,
clinicalItemIds,
reassignMergedItems=True,
conn=None):
"""The specified clinical items will be merged / composited into the base clinical item provided.
The remaining now redundant items will be deactivated
Patient_item instances will be reassigned to the merged clinical_item
(while backup links will be saved to backup_link_patient_item),
clinical_item_association counts for the redundant items will removed and analyze_dates reset,
requiring a re-run of AssociationAnalysis to redo those counts from scratch
(but will now count as the merged / composite item rather than separate ones).
Could theoretically figure out how to combine the association stats without re-running analysis, but
patient_counts are supposed to ignore duplicates, so hard to know how to aggregate stats
(not enough info in them to tell if unique cooccurrences?)
Examples this could be relevant for:
All blood transfusion indexes, G vs J vs Feeding tube equivalent, Ear, Eyes med routes irrelevant which ear/eye.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Deactivate other items
deactivateIds = set(clinicalItemIds)
deactivateIds.discard(baseClinicalItemId)
self.deactivateAnalysis(deactivateIds, conn=conn)
# Build composite item name and description
allIds = set(deactivateIds)
allIds.add(baseClinicalItemId)
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addSelect("name")
query.addSelect("description")
query.addFrom("clinical_item")
query.addWhereIn("clinical_item_id", allIds)
query.addOrderBy("name")
# Ensure consistency across multiple runs
results = DBUtil.execute(query, conn=conn)
nameList = list()
descrList = list()
# First pass to get Base Item Description
for (clinicalItemId, name, description) in results:
if clinicalItemId == baseClinicalItemId:
if name is None:
name = ""
if description is None:
description = ""
nameList.append(name)
descrList.append(description)
break
# Second pass to get the rest
for (clinicalItemId, name, description) in results:
if clinicalItemId != baseClinicalItemId:
if name is None:
name = ""
if description is None:
description = ""
nameList.append(name)
descrList.append(description)
compositeName = str.join("+", nameList)
compositeDescription = str.join("+", descrList)
DBUtil.updateRow("clinical_item", {
"name": compositeName,
"description": compositeDescription
},
baseClinicalItemId,
conn=conn)
if reassignMergedItems:
# Reassign other items to the base item, but save backup data first
query = SQLQuery()
query.addSelect("patient_item_id")
query.addSelect("clinical_item_id")
query.addFrom("patient_item")
query.addWhereIn("clinical_item_id", deactivateIds)
results = DBUtil.execute(query, conn=conn)
insertQuery = DBUtil.buildInsertQuery(
"backup_link_patient_item",
["patient_item_id", "clinical_item_id"])
for (patientItemId, clinicalItemId) in results:
insertParams = (patientItemId, clinicalItemId)
try:
# Optimistic insert of a new unique item
DBUtil.execute(insertQuery, insertParams, conn=conn)
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.info(err)
pass
# Now to actual reassignment of patient items to the unifying base clinical item
placeholders = generatePlaceholders(len(deactivateIds))
query = "update patient_item set clinical_item_id = %s where clinical_item_id in (%s)" % (
DBUtil.SQL_PLACEHOLDER, placeholders)
params = [baseClinicalItemId]
params.extend(deactivateIds)
DBUtil.execute(query, params, conn=conn)
finally:
if not extConn:
conn.close()
def syncTable(sourceConn,
targetConn,
syncTableName,
rowIDStrSet=None,
formatter=None):
if formatter is None:
idCol = DBUtil.defaultIDColumn(syncTableName)
idQuery = "select %s from %s" % (idCol, syncTableName)
# Collect all of the IDs known in the target database and store in memory for rapid lookup
print >> sys.stderr, "Querying for IDs from Target Database"
targetIdTable = DBUtil.execute(idQuery, conn=targetConn)
targetIdSet = Set()
for row in targetIdTable:
targetId = row[0]
targetIdSet.add(targetId)
# Query data out of the source table, but do it by a cursor so we can stream through large data tables
print >> sys.stderr, "Querying for Source Data"
dataQuery = "select * from %s" % (syncTableName)
sourceCursor = sourceConn.cursor()
sourceCursor.execute(dataQuery)
colNames = DBUtil.columnNamesFromCursor(sourceCursor)
targetCursor = targetConn.cursor()
insertQuery = None
updateQuery = None
progress = ProgressDots()
row = sourceCursor.fetchone()
while row is not None:
dataModel = RowItemModel(row, colNames)
if rowIDStrSet is None or str(dataModel[idCol]) in rowIDStrSet:
if rowIDStrSet is not None:
print >> sys.stderr, "Syncing record: %s" % dataModel[idCol]
if dataModel[idCol] not in targetIdSet:
# Row does not yet exist in target database, need to insert it
if insertQuery is None:
insertQuery = DBUtil.buildInsertQuery(
syncTableName, dataModel.keys())
insertParams = dataModel.values()
targetCursor.execute(insertQuery, insertParams)
else:
# Row already exists in target database, just update values
if updateQuery is None:
updateQuery = DBUtil.buildUpdateQuery(
syncTableName, dataModel.keys())
updateParams = []
updateParams.extend(dataModel.values())
updateParams.append(dataModel[idCol])
targetCursor.execute(updateQuery, updateParams)
if progress.GetCounts() % progress.big == 0:
targetConn.commit()
row = sourceCursor.fetchone()
progress.Update()
progress.PrintStatus()
targetConn.commit()
else:
##Do something with thr formatter
##Set up the formatter
theFormatter = formatter(syncTableName,
targetConn,
includeColumnNames=True,
autoCommit=True)
#Call DB execute
res = DBUtil.execute("select * from %s" % syncTableName,
includeColumnNames=True,
conn=sourceConn,
formatter=theFormatter)
