def test_nullCheck(self):
DBUtil.runDBScript(self.SCRIPT_FILE, False)
conn = DBUtil.connection()
try:
DBUtil.execute(
"insert into TestTypes (MyText,MyInteger) values ('Test With Null', 255)",
conn=conn)
DBUtil.execute(
"insert into TestTypes (MyText,MyInteger,MyReal,MyDateTime) values ('Test With Not Null', 255, 1.23, '2005-03-06')",
conn=conn)
result = DBUtil.execute(
"select MyText from TestTypes where MyInteger = 255 and MyReal is null",
conn=conn)
self.assertEqual('Test With Null', result[0][0])
result = DBUtil.execute(
"select MyText from TestTypes where MyInteger = 255 and MyReal is not null",
conn=conn)
self.assertEqual('Test With Not Null', result[0][0])
# Would not work with MySQL if used TIMESTAMP data type. Should be DATETIME. (TIMESTAMP tries to auto-fill values, so no nulls allowed?)
result = DBUtil.execute(
"select MyText from TestTypes where MyInteger = 255 and MyDateTime is null",
conn=conn)
self.assertEqual('Test With Null', result[0][0])
result = DBUtil.execute(
"select MyText from TestTypes where MyInteger = 255 and MyDateTime is not null",
conn=conn)
self.assertEqual('Test With Not Null', result[0][0])
finally:
conn.close()
def setUp(self):
DBTestCase.setUp(self)
self.connection = DBUtil.connection()
self._insertUMichTestRecords()
self.matrix = LabNormalityMatrix('WBC',
10,
random_state=1234,
isLabPanel=False)
def setUp(self):
"""Prepare state for test cases."""
DBTestCase.setUp(self)
StrideLoader.build_stride_psql_schemata()
ClinicalItemDataLoader.build_clinical_item_psql_schemata()
self._deleteTestRecords()
self._insertTestRecords()
self.factory = FeatureMatrixFactory()
self.connection = DBUtil.connection()
def __init__(self, variable, num_data_points, params=None):
# Process arguments.
self._var = variable
self._num_rows = num_data_points
if params is None:
self._params = {}
else:
self._params = params
# Initialize FeatureMatrixFactory.
self._factory = FeatureMatrixFactory()
# Initialize DB connection.
self._connection = DBUtil.connection()
def setUp(self):
"""Prepare state for test cases."""
DBTestCase.setUp(self)
# StrideLoader.build_stride_psql_schemata()
# ClinicalItemDataLoader.build_clinical_item_psql_schemata();
# self._deleteTestRecords()
# self._insertTestRecords()
self.factory = FeatureMatrixFactory() # TODO: self.RACE_FEATURES = self.queryAllRaces(), OperationalError: no such table: demographics
self.connection = DBUtil.connection(); # Setup a common connection for test cases to work with, can catch in finally tearDown method to close/cleanup
self.cursor = self.connection.cursor()
self._insertUMichTestRecords()
def setUp(self):
LocalEnv.DATABASE_CONNECTOR_NAME = medinfo.db.Env.DATABASE_CONNECTOR_NAME = "sqlite3"
LocalEnv.LOCAL_TEST_DB_PARAM["DSN"] = 'UMich_test.db'
LocalEnv.LOCAL_TEST_DB_PARAM["DATAPATH"] = os.path.join(
LocalEnv.PATH_TO_CDSS, 'scripts/LabTestAnalysis/test/')
medinfo.db.Env.SQL_PLACEHOLDER = "?"
DBTestCase.setUp(self)
# ClinicalItemDataLoader.build_clinical_item_psql_schemata()
self.connection = DBUtil.connection()
self._insertUMichTestRecords()
self.matrix = LabNormalityMatrix('WBC',
10,
random_state=1234,
isLabPanel=False)
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 main(argv):
conversionProcessor = STRIDEOrderResultsConversion();
conn = DBUtil.connection();
try:
# Pull out list of result names to look for that are not already in the calculated
nameTable = DBUtil.execute("select name from sim_result except select base_name from order_result_stat", conn=conn);
prog = ProgressDots(big=1,small=1,total=len(nameTable));
for row in nameTable:
baseName = row[0];
print("Calculating Stats for %s" % baseName, file=sys.stderr);
statModel = conversionProcessor.calculateResultStats( baseName, conn=conn );
DBUtil.insertRow("order_result_stat", statModel, conn=conn );
prog.update();
prog.printStatus();
conn.commit();
finally:
conn.close();
def __init__(self):
# Initialize DB connection.
connection = DBUtil.connection()
self.dbCursor = connection.cursor()
# Aggressively cache queries as much as possible.
self.dbCache = {}
# Collect stats of interest, always collecting annual statistics.
# print self._getNumUniquePatients()
# print self._getNumPatientEncounters()
# print self._getAdmitDateRange()
# print self._getNumPatientEncountersByMonth()
print self._getPatientDemographics()
#self.plotNumPatientEncountersByMonth()
#self.plotPatientDemographicsByMonth()
# Write statistics to textfile report.
pass
def queryPatientEpisodes(outputFile, extractor):
log.info(
"Select patient admissions with provider category of Tt Pamf Med (Primary) or Tt Med Univ (Primary)"
)
conn = DBUtil.connection()
cursor = conn.cursor()
try:
# # Clinical item category for admission diagnoses
# # ADMIT_DX_CATEGORY_ID = 2;
# admitDxCategoryId = DBUtil.execute("select clinical_item_category_id from clinical_item_category where description like '%%ADMIT_DX%%'", conn=conn)[0][0];
# # Look for items indicating suspected infection / sepsis
# ivAntibioticItemIds = loadIVAntibioticItemIds(extractor);
# bloodCultureItemIds = loadBloodCultureItemIds(extractor);
# respiratoryViralPanelItemIds = loadRespiratoryViralPanelItemIds(extractor);
# # Merge IV antibiotics and blood cultures, respiratory panels as items that suggest sepsis is suspected
# suspectSepsisItemIds = ivAntibioticItemIds.union(bloodCultureItemIds.union(respiratoryViralPanelItemIds));
# suspectSepsisItemIdsStr = str.join(',', [str(itemId) for itemId in suspectSepsisItemIds]); # Convert to comma-separated string via a str.join function on list contracture
# # Look for primary surgery teams to exclude
# excludeTeamCategory = "SurgerySpecialty";
# excludeTreatmentTeams = list();
# for row in extractor.loadMapData("TreatmentTeamGroups"):
# if row["team_category"] == excludeTeamCategory:
# excludeTreatmentTeams.append(row["treatment_team"]);
# query = SQLQuery();
# query.addSelect("clinical_item_id");
# query.addFrom("clinical_item");
# query.addWhereIn("description", excludeTreatmentTeams );
# excludeTeamItemIds = set();
# for row in DBUtil.execute(query, conn=conn):
# excludeTeamItemIds.add(row[0]);
# excludeTeamItemIdsStr = str.join(',', [str(itemId) for itemId in excludeTeamItemIds]); # Convert to comma-separated string via a str.join function on list contracture
# First pass query to get the list of patients and emergency department presentation times
cohortQuery = \
"""
select adt1.pat_anon_id, adt1.pat_enc_csn_anon_id, adt1.shifted_transf_in_dt_tm as edAdmitTime, adt2.shifted_transf_out_dt_tm as dischargeTime
from stride_adt as adt1, stride_adt as adt2
where
adt1.pat_anon_id in
(select patient_id from patient_item inner join clinical_item on patient_item.clinical_item_id = clinical_item.clinical_item_id where clinical_item.clinical_item_category_id = 161 AND clinical_item.description = '%s')
and adt1.pat_enc_csn_anon_id = adt2.pat_enc_csn_anon_id
""" % ("Tt Pamf Med (Primary)")
print(cohortQuery, file=sys.stderr)
cursor.execute(cohortQuery)
patientEpisodes = list()
patientEpisodeById = dict()
# Collect Build basic patient ID and
# ED presentation dates and Discharge date/time
prog = ProgressDots()
row = cursor.fetchone()
while row is not None:
(patientId, encounterId, edAdmitTime, dischargeTime) = row
#patientId = int(patientId);
patientEpisode = \
RowItemModel \
( { "patient_id":patientId,
"edAdmitTime":edAdmitTime,
"dischargeTime":dischargeTime,
"encounter_id":encounterId,
"payorTitle": None, # Default encounter data to null in case can't find it later
"bpSystolic": None,
"bpDiastolic": None,
"temperature": None,
"pulse": None,
"respirations": None,
}
)
patientEpisodes.append(patientEpisode)
if patientEpisode["encounter_id"] not in patientEpisodeById:
patientEpisodeById[
patientEpisode["encounter_id"]] = patientEpisode
prog.update()
row = cursor.fetchone()
prog.printStatus()
# Second query phase to link to encounter information (e.g., insurance, admitting vital signs)
encounterIds = columnFromModelList(patientEpisodes, "encounter_id")
query = SQLQuery()
query.addSelect("pat_id")
query.addSelect("pat_enc_csn_id")
query.addSelect("title")
query.addSelect("bp_systolic")
query.addSelect("bp_diastolic")
query.addSelect("temperature")
query.addSelect("pulse")
query.addSelect("respirations")
query.addFrom("stride_patient_encounter")
query.addWhereIn("pat_enc_csn_id", encounterIds)
cursor.execute(str(query), query.params)
row = cursor.fetchone()
while row is not None:
(patientId, encounterId, payorTitle, bpSystolic, bpDiastolic,
temperature, pulse, respirations) = row
if encounterId in patientEpisodeById:
patientEpisode = patientEpisodeById[encounterId]
if patientEpisode["payorTitle"] is None:
patientEpisode["payorTitle"] = set()
# Single encounters may have multiple payors to track
patientEpisode["payorTitle"].add(payorTitle)
patientEpisode["bpSystolic"] = bpSystolic
patientEpisode["bpDiastolic"] = bpDiastolic
patientEpisode["temperature"] = temperature
patientEpisode["pulse"] = pulse
patientEpisode["respirations"] = respirations
row = cursor.fetchone()
# Drop results as tab-delimited text output
formatter = TextResultsFormatter(outputFile)
formatter.formatResultDicts(patientEpisodes, addHeaderRow=True)
return patientEpisodes
finally:
cursor.close()
conn.close()
import sys, os
from medinfo.db import DBUtil
# Clear out any previous reference data (but keep the fixed order set data)
# (May want to backup database before running these commands)
print >> sys.stderr, "About to delete/replace existing item_collection_item reference data."
print >> sys.stderr, "Are you sure? (Ctrl-C to cancel)"
raw_input()
# Pause for command-line input/confirmation
conn = DBUtil.connection()
try:
DBUtil.execute \
( """
-- Delete reference or diagnosis linking items
delete
from item_collection_item as ici
where collection_type_id in (1,2,3,5);
-- Delete the resultant orphaned parent collection records
delete
from item_collection
where item_collection_id not in
(
select item_collection_id
from item_collection_item
);
""",
conn=conn
)
def __call__(self, analysisQuery, conn=None):
"""Go through the validation file to assess order set usage amongst test cases
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
conn = DBUtil.connection()
preparer = PreparePatientItems()
progress = ProgressDots(50, 1, "Patients")
for patientItemData in preparer.loadPatientItemData(analysisQuery):
patientId = patientItemData["patient_id"]
analysisResults = \
self.analyzePatientItems \
( patientItemData,
analysisQuery,
analysisQuery.baseRecQuery,
patientId,
analysisQuery.recommender,
conn=conn
)
if analysisResults is not None:
(queryItemCountById, verifyItemCountById,
recommendedItemIds, recommendedData,
orderSetItemData) = analysisResults
# Unpack results
# Start aggregating and calculating result stats
resultsStatData = self.calculateResultStats(
patientItemData, queryItemCountById,
verifyItemCountById, recommendedItemIds,
self.supportRecommender.patientCountByItemId,
analysisQuery.baseRecQuery, recommendedData)
resultsStatData["usedOrderSetIds"] = orderSetItemData[
"allUsedOrderSetIds"]
resultsStatData["numUsedOrderSets"] = len(
orderSetItemData["allUsedOrderSetIds"])
resultsStatData["numUsedOrderSetItems"] = len(
orderSetItemData["allUsedOrderSetItemIds"])
resultsStatData["numAvailableOrderSetItems"] = len(
orderSetItemData["allAvailableOrderSetItemIds"])
resultsStatData["numRecommendableUsedOrderSetItems"] = len(
orderSetItemData["recommendableUsedOrderSetItemIds"])
resultsStatData[
"numRecommendableAvailableOrderSetItems"] = len(
orderSetItemData[
"recommendableAvailableOrderSetItemIds"])
resultsStatData["numRecommendableQueryItems"] = len(
orderSetItemData["recommendableQueryItemIds"])
resultsStatData["numRecommendableVerifyItems"] = len(
orderSetItemData["recommendableVerifyItemIds"])
resultsStatData["numRecommendableQueryVerifyItems"] = len(
orderSetItemData["recommendableQueryItemIds"]
| orderSetItemData["recommendableVerifyItemIds"])
# Union of two sets
resultsStatData["orderSetItemUsageRate"] = 0.0
if resultsStatData["numAvailableOrderSetItems"] > 0:
resultsStatData["orderSetItemUsageRate"] = float(
resultsStatData["numUsedOrderSetItems"]
) / resultsStatData["numAvailableOrderSetItems"]
resultsStatData[
"recommendableQueryVerifyItemFromOrderSetRate"] = 0.0
if resultsStatData["numRecommendableQueryVerifyItems"] > 0:
resultsStatData[
"recommendableQueryVerifyItemFromOrderSetRate"] = float(
resultsStatData[
"numRecommendableUsedOrderSetItems"]
) / resultsStatData[
"numRecommendableQueryVerifyItems"]
yield resultsStatData
progress.Update()
# progress.PrintStatus();
finally:
if not extConn:
conn.close()
def queryPatientEpisodes(outputFile, extractor):
log.info("Select patient admissions with possible/probable sepsis within 24 hours of admission (long query >60 min?)...");
conn = DBUtil.connection();
cursor = conn.cursor();
try:
# Clinical item category for admission diagnoses
# ADMIT_DX_CATEGORY_ID = 2;
admitDxCategoryId = DBUtil.execute("select clinical_item_category_id from clinical_item_category where description like '%%ADMIT_DX%%'", conn=conn)[0][0];
# Look for items indicating suspected infection / sepsis
ivAntibioticItemIds = loadIVAntibioticItemIds(extractor);
bloodCultureItemIds = loadBloodCultureItemIds(extractor);
respiratoryViralPanelItemIds = loadRespiratoryViralPanelItemIds(extractor);
# Merge IV antibiotics and blood cultures, respiratory panels as items that suggest sepsis is suspected
suspectSepsisItemIds = ivAntibioticItemIds.union(bloodCultureItemIds.union(respiratoryViralPanelItemIds));
suspectSepsisItemIdsStr = str.join(',', [str(itemId) for itemId in suspectSepsisItemIds]); # Convert to comma-separated string via a str.join function on list contracture
# Look for primary surgery teams to exclude
excludeTeamCategory = "SurgerySpecialty";
excludeTreatmentTeams = list();
for row in extractor.loadMapData("TreatmentTeamGroups"):
if row["team_category"] == excludeTeamCategory:
excludeTreatmentTeams.append(row["treatment_team"]);
query = SQLQuery();
query.addSelect("clinical_item_id");
query.addFrom("clinical_item");
query.addWhereIn("description", excludeTreatmentTeams );
excludeTeamItemIds = set();
for row in DBUtil.execute(query, conn=conn):
excludeTeamItemIds.add(row[0]);
excludeTeamItemIdsStr = str.join(',', [str(itemId) for itemId in excludeTeamItemIds]); # Convert to comma-separated string via a str.join function on list contracture
# First pass query to get the list of patients and emergency department presentation times
cohortQuery = \
"""
-- Pick out date(s) when admitted through emergency department and matching discharge time
select adt1.pat_anon_id, adt1.pat_enc_csn_anon_id, adt1.shifted_transf_in_dt_tm as edAdmitTime, adt2.shifted_transf_out_dt_tm as dischargeTime
from stride_adt as adt1, stride_adt as adt2
where
-- Admission event
adt1.department_in = 'EMERGENCY DEPARTMENT' and
adt1.event_in = 'Admission' and
adt1.pat_anon_id in
( -- Select any patient with any suspected sepsis related order (i.e., IV antibiotics or blood cultures)
select patient_id
from patient_item as pi
where pi.clinical_item_id in (%s)
except
-- Exclude any patient who has been on a primary surgery team
select patient_id
from patient_item
where clinical_item_id in (%s)
-- -12434586418575,-12432455207729,-12428492282572,-12428492282572,-12424048595257,-12414081679705
) and
adt1.pat_enc_csn_anon_id = adt2.pat_enc_csn_anon_id and
-- Discharge event
adt2.event_out = 'Discharge'
order by adt1.shifted_transf_in_dt_tm
""" % (suspectSepsisItemIdsStr, excludeTeamItemIdsStr);
print >> sys.stderr, cohortQuery;
cursor.execute(cohortQuery);
patientEpisodes = list();
patientEpisodeById = dict();
# Collect Build basic patient ID and
# ED presentation dates and Discharge date/time
prog = ProgressDots();
row = cursor.fetchone();
while row is not None:
(patientId, encounterId, edAdmitTime, dischargeTime) = row;
#patientId = int(patientId);
patientEpisode = \
RowItemModel \
( { "patient_id":patientId,
"edAdmitTime":edAdmitTime,
"dischargeTime":dischargeTime,
"encounter_id":encounterId,
"payorTitle": None, # Default encounter data to null in case can't find it later
"bpSystolic": None,
"bpDiastolic": None,
"temperature": None,
"pulse": None,
"respirations": None,
}
);
patientEpisodes.append(patientEpisode);
if patientEpisode["encounter_id"] not in patientEpisodeById:
patientEpisodeById[patientEpisode["encounter_id"]] = patientEpisode;
prog.update();
row = cursor.fetchone();
prog.printStatus();
# Second query phase to link to encounter information (e.g., insurance, admitting vital signs)
encounterIds = columnFromModelList(patientEpisodes, "encounter_id");
query = SQLQuery();
query.addSelect("pat_id");
query.addSelect("pat_enc_csn_id");
query.addSelect("title");
query.addSelect("bp_systolic");
query.addSelect("bp_diastolic");
query.addSelect("temperature");
query.addSelect("pulse");
query.addSelect("respirations");
query.addFrom("stride_patient_encounter");
query.addWhereIn("pat_enc_csn_id", encounterIds);
cursor.execute(str(query), query.params);
row = cursor.fetchone();
while row is not None:
(patientId, encounterId, payorTitle, bpSystolic, bpDiastolic, temperature, pulse, respirations) = row;
if encounterId in patientEpisodeById:
patientEpisode = patientEpisodeById[encounterId];
if patientEpisode["payorTitle"] is None:
patientEpisode["payorTitle"] = set(); # Single encounters may have multiple payors to track
patientEpisode["payorTitle"].add(payorTitle);
patientEpisode["bpSystolic"] = bpSystolic;
patientEpisode["bpDiastolic"] = bpDiastolic;
patientEpisode["temperature"] = temperature;
patientEpisode["pulse"] = pulse;
patientEpisode["respirations"] = respirations;
row = cursor.fetchone();
# Drop results as tab-delimited text output
formatter = TextResultsFormatter(outputFile);
formatter.formatResultDicts(patientEpisodes, addHeaderRow=True);
return patientEpisodes;
finally:
cursor.close();
conn.close();
def main(argv):
usageStr = "usage: %prog [options] <syncTableName>\n"+\
" <syncTableName> Name of the database table to sync from the source DB to the target.\n"+\
" Looks for records by default table ID column. If not found in target DB,\n"+\
" inserts a new record, otherwise updates the existing record.\n"+\
" A space separated list can be provided to synchronize multiple tables (in order).\n"
parser = OptionParser(usage=usageStr)
parser.add_option("-c",
"--sourceComputer",
dest="sourceComputer",
metavar="<sourceComputer>",
help="Source database host computer.")
parser.add_option("-d",
"--sourceDatabase",
dest="sourceDatabase",
metavar="<sourceDatabase>",
help="Source database DSN / name.")
parser.add_option("-u",
"--sourceUsername",
dest="sourceUsername",
metavar="<sourceUsername>",
help="Source database user name.")
parser.add_option("-p",
"--sourcePassword",
dest="sourcePassword",
metavar="<sourcePassword>",
help="Source database user password.")
parser.add_option("-o",
"--sourcePort",
dest="sourcePort",
metavar="<sourcePort>",
help="Source database port.",
default=None)
parser.add_option("-C",
"--targetComputer",
dest="targetComputer",
metavar="<targetComputer>",
help="Target database host computer.")
parser.add_option("-D",
"--targetDatabase",
dest="targetDatabase",
metavar="<targetDatabase>",
help="Target database DSN / name.")
parser.add_option("-U",
"--targetUsername",
dest="targetUsername",
metavar="<targetUsername>",
help="Target database user name.")
parser.add_option("-P",
"--targetPassword",
dest="targetPassword",
metavar="<targetPassword>",
help="Target database user password.")
parser.add_option("-O",
"--targetPort",
dest="targetPort",
metavar="<targetPort>",
help="Target database port.",
default=None)
parser.add_option(
"-r",
"--rowIDs",
dest="rowIDs",
metavar="<rowIDs>",
help=
"Comma-separated list of IDs to specify which rows to copy / sync. Leave blank to copy / sync all rows from the source database."
)
parser.add_option(
'-S',
'--syncSequence',
dest='syncSequence',
action="store_true",
help=
'Add this option to update sequence number from the source table to the target table'
)
"""Assign options (available are above) and args (table names), that were passed via command line when script was executed
"""
(options, args) = parser.parse_args(argv[1:])
"""Prep conn vars
"""
sourceConn = None
targetConn = None
"""Assign Table names
"""
syncTableList = args
try:
sourceDB = dict()
sourceDB["HOST"] = options.sourceComputer
sourceDB["DSN"] = options.sourceDatabase
sourceDB["UID"] = options.sourceUsername
sourceDB["PWD"] = options.sourcePassword
if options.sourcePort is not None:
sourceDB["PORT"] = options.sourcePort
targetDB = dict()
targetDB["HOST"] = options.targetComputer
targetDB["DSN"] = options.targetDatabase
targetDB["UID"] = options.targetUsername
targetDB["PWD"] = options.targetPassword
if options.targetPort is not None:
targetDB["PORT"] = options.targetPort
sourceConn = DBUtil.connection(sourceDB)
targetConn = DBUtil.connection(targetDB)
except Exception, exc:
print str(exc)
parser.print_help()
exit()
def queryPatients(outputFile):
log.info("Select patients with any ICU life support orders and follow contiguous date trail for apparent hospitalization (long query >20 min)...");
conn = DBUtil.connection();
cursor = conn.cursor();
try:
anyLifeSupportItemId = DBUtil.execute("select clinical_item_id from clinical_item where name = 'AnyICULifeSupport'", conn=conn)[0][0];
patientById = dict();
query = \
"""select pi.patient_id, date_trunc('day',pi.item_date), min(pi.encounter_id), count(pi.patient_item_id)
from patient_item as pi,
(
select pi2.patient_id, min(pi2.item_date) as firstLifeSupportDate
from patient_item as pi2
where pi2.clinical_item_id = %s
group by pi2.patient_id
) as piX
where pi.patient_id = piX.patient_id
and pi.item_date >= piX.firstLifeSupportDate
group by pi.patient_id, date_trunc('day',pi.item_date)
order by pi.patient_id, date_trunc('day',pi.item_date)
""" % anyLifeSupportItemId;
cursor.execute(query);
row = cursor.fetchone();
while row is not None:
(patientId, itemDate, encounterId, itemCount) = row;
patientId = int(patientId);
if patientId not in patientById:
patientById[patientId] = \
RowItemModel \
( { "patient_id":patientId,
"firstLifeSupportDate":itemDate,
"lastContiguousDate":itemDate,
"encounter_id":encounterId, # Assumes single value that won't be overwritten
"payorTitle": None, # Default encounter data to null in case can't find it later
"bpSystolic": None,
"bpDiastolic": None,
"temperature": None,
"pulse": None,
"respirations": None,
}
);
if (itemDate - patientById[patientId]["lastContiguousDate"]) <= CONTIGUOUS_THRESHOLD:
patientById[patientId]["lastContiguousDate"] = itemDate;
if patientById[patientId]["encounter_id"] is None:
patientById[patientId]["encounter_id"] = encounterId;
row = cursor.fetchone();
# Second query phase to link to encounter information (e.g., insurance, admitting vital signs)
encounterIds = columnFromModelList(iter(patientById.values()), "encounter_id");
query = SQLQuery();
query.addSelect("pat_id");
query.addSelect("pat_enc_csn_id");
query.addSelect("title");
query.addSelect("bp_systolic");
query.addSelect("bp_diastolic");
query.addSelect("temperature");
query.addSelect("pulse");
query.addSelect("respirations");
query.addFrom("stride_patient_encounter");
query.addWhereIn("pat_enc_csn_id", encounterIds);
cursor.execute(str(query), query.params);
row = cursor.fetchone();
while row is not None:
(patientId, encounterId, payorTitle, bpSystolic, bpDiastolic, temperature, pulse, respirations) = row;
if patientById[patientId]["payorTitle"] is None:
patientById[patientId]["payorTitle"] = set(); # Single encounters may have multiple payors to track
patientById[patientId]["payorTitle"].add(payorTitle);
patientById[patientId]["bpSystolic"] = bpSystolic;
patientById[patientId]["bpDiastolic"] = bpDiastolic;
patientById[patientId]["temperature"] = temperature;
patientById[patientId]["pulse"] = pulse;
patientById[patientId]["respirations"] = respirations;
row = cursor.fetchone();
if patientById[patientId]["payorTitle"] is not None: # Condense to single string
payorList = list(patientById[patientId]["payorTitle"]);
payorList.sort();
patientById[patientId]["payorTitle"] = str.join(",", payorList);
# Drop results as tab-delimited text output
formatter = TextResultsFormatter(outputFile);
formatter.formatResultDicts(iter(patientById.values()), addHeaderRow=True);
return patientById;
finally:
cursor.close();
conn.close();
def main(argv):
usageStr = "usage: %prog [options] <syncTableName>\n"+\
" <syncTableName> Name of the database table to sync from the source DB to the target.\n"+\
" Looks for records by default table ID column. If not found in target DB,\n"+\
" inserts a new record, otherwise updates the existing record.\n"+\
" A space separated list can be provided to synchronize multiple tables (in order).\n"
parser = OptionParser(usage=usageStr)
parser.add_option("-c",
"--sourceComputer",
dest="sourceComputer",
metavar="<sourceComputer>",
help="Source database host computer.")
parser.add_option("-d",
"--sourceDatabase",
dest="sourceDatabase",
metavar="<sourceDatabase>",
help="Source database DSN / name.")
parser.add_option("-u",
"--sourceUsername",
dest="sourceUsername",
metavar="<sourceUsername>",
help="Source database user name.")
parser.add_option("-p",
"--sourcePassword",
dest="sourcePassword",
metavar="<sourcePassword>",
help="Source database user password.")
parser.add_option("-o",
"--sourcePort",
dest="sourcePort",
metavar="<sourcePort>",
help="Source database port.",
default=None)
parser.add_option("-C",
"--targetComputer",
dest="targetComputer",
metavar="<targetComputer>",
help="Target database host computer.")
parser.add_option("-D",
"--targetDatabase",
dest="targetDatabase",
metavar="<targetDatabase>",
help="Target database DSN / name.")
parser.add_option("-U",
"--targetUsername",
dest="targetUsername",
metavar="<targetUsername>",
help="Target database user name.")
parser.add_option("-P",
"--targetPassword",
dest="targetPassword",
metavar="<targetPassword>",
help="Target database user password.")
parser.add_option("-O",
"--targetPort",
dest="targetPort",
metavar="<targetPort>",
help="Target database port.",
default=None)
parser.add_option(
"-r",
"--rowIDs",
dest="rowIDs",
metavar="<rowIDs>",
help=
"Comma-separated list of IDs to specify which rows to copy / sync. Leave blank to copy / sync all rows from the source database."
)
parser.add_option(
'-S',
'--syncSequence',
dest='syncSequence',
action="store_true",
help=
'Add this option to update sequence number from the source table to the target table'
)
"""Assign options (available are above) and args (table names), that were passed via command line when script was executed
"""
(options, args) = parser.parse_args(argv[1:])
"""Prep conn vars
"""
sourceConn = None
targetConn = None
"""Assign Table names
"""
syncTableList = args
try:
sourceDB = dict()
sourceDB["HOST"] = options.sourceComputer
sourceDB["DSN"] = options.sourceDatabase
sourceDB["UID"] = options.sourceUsername
sourceDB["PWD"] = options.sourcePassword
if options.sourcePort is not None:
sourceDB["PORT"] = options.sourcePort
targetDB = dict()
targetDB["HOST"] = options.targetComputer
targetDB["DSN"] = options.targetDatabase
targetDB["UID"] = options.targetUsername
targetDB["PWD"] = options.targetPassword
if options.targetPort is not None:
targetDB["PORT"] = options.targetPort
sourceConn = DBUtil.connection(sourceDB)
targetConn = DBUtil.connection(targetDB)
except Exception as exc:
print(str(exc))
parser.print_help()
exit()
"""Check to see if target and source connections are good, and that there were args provided
"""
if sourceConn is not None and targetConn is not None and len(
syncTableList) > 0:
rowIDStrSet = None
"""Collect all the rowIDs, if any were provided, and place them individual into an array
just prep-work for the syncTable function
"""
if options.rowIDs:
rowIDStrSet = set()
for rowIDStr in options.rowIDs.split(","):
rowIDStrSet.add(rowIDStr)
"""For each table specified, format and sync
"""
for syncTableName in syncTableList:
print("Syncing %s" % syncTableName, file=sys.stderr)
##Resolve formatter to actual class
myFormatter = None
"""Sync table data
"""
syncTable(sourceConn, targetConn, syncTableName, rowIDStrSet,
myFormatter)
"""Sync sequence number
"""
if options.syncSequence:
syncSequence(sourceConn, targetConn, syncTableName)
sourceConn.close()
targetConn.close()
else:
parser.print_help()
