def createPatient(self, patientData, initialStateId, conn=None): """Create a new patient record given the data dictionary. Create a respective initial patient state record as well. """ extConn = True if conn is None: conn = self.connFactory.connection() extConn = False try: DBUtil.insertRow("sim_patient", patientData, conn=conn) patientId = DBUtil.execute(DBUtil.identityQuery("sim_patient"), conn=conn)[0][0] patientStateData = { "sim_patient_id": patientId, "sim_state_id": initialStateId, "relative_time_start": 0 } DBUtil.insertRow("sim_patient_state", patientStateData, conn=conn) conn.commit() # Transactional commit for two step process return patientId 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 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 test_identityQuery(self): DBUtil.runDBScript( self.SCRIPT_FILE, False ) # Run some other commands to see if scripts produced expected results results = DBUtil.execute("select max(TestTypes_id) from TestTypes"); lastSeq = results[0][0] conn = DBUtil.connection() try: cur = conn.cursor() cur.execute("insert into TestTypes (MyText,MyInteger,MyYesNo) values ('Another',255,True)") cur.execute( DBUtil.identityQuery("TestTypes") ) self.assertEqual( lastSeq+1, cur.fetchone()[0] ) cur.execute("select TestTypes_id from TestTypes where MyText = 'Another' and MyInteger = 255") self.assertEqual( lastSeq+1, cur.fetchone()[0] ) finally: conn.close()
def copyPatientTemplate(self, patientData, templatePatientId, conn=None): """Create a new patient record based on the given template patient ID to copy from. Will copy shallow attributes, overridden by any provided in the given patientData, as well as any patient states, notes, or physician orders UP TO (and including) relative time zero, but not subsequent states, notes, or physician orders (the latter is expected to reflect real user interaction records). """ extConn = True if conn is None: conn = self.connFactory.connection() extConn = False try: templatePatientData = DBUtil.loadRecordModelById("sim_patient", templatePatientId, conn=conn) del templatePatientData["sim_patient_id"] # Remove prior ID to allow for new one templatePatientData.update(patientData) # Override with new content (if exists) DBUtil.insertRow("sim_patient", templatePatientData, conn=conn) # Create new patient record patientId = DBUtil.execute(DBUtil.identityQuery("sim_patient"), conn=conn)[0][0] # Copy initial template patient states query = SQLQuery() query.addSelect("*") # Copy all columns query.addFrom("sim_patient_state as sps") query.addWhereEqual("sps.sim_patient_id", templatePatientId) query.addWhereOp("relative_time_start", "<=", 0) query.addOrderBy("relative_time_start") dataTable = DBUtil.execute(query, includeColumnNames=True, conn=conn) dataModels = modelListFromTable(dataTable) nStates = len(dataModels) for i, dataModel in enumerate(dataModels): del dataModel["sim_patient_state_id"] # Discard copied ID to allow new one if i == nStates - 1: del dataModel["relative_time_end"] # Last state. Blank out end time to reflect open ended for simulation dataModel["sim_patient_id"] = patientId DBUtil.insertRow("sim_patient_state", dataModel, conn=conn) # Copy initial template orders query = SQLQuery() query.addSelect("*") query.addFrom("sim_patient_order as spo") query.addWhereEqual("sim_patient_id", templatePatientId) query.addWhereOp("relative_time_start", "<=", 0) query.addOrderBy("relative_time_start") dataTable = DBUtil.execute(query, includeColumnNames=True, conn=conn) dataModels = modelListFromTable(dataTable) for dataModel in dataModels: del dataModel["sim_patient_order_id"] dataModel["sim_patient_id"] = patientId DBUtil.insertRow("sim_patient_order", dataModel, conn=conn) conn.commit() # Transactional commit for multi-step process return patientId finally: if not extConn: conn.close()