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