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