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 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 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 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 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 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 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)