def validate_sql_connection(config=None, verbose=False): """ :param config: login credentials defining 'host', 'dbname', 'port' and optionally 'user' and 'password' :type config: dict :param verbose: indicates if cmd line printing should be performed :type verbose: bool :return: True if configuration is valid :rtype: bool """ valid = True if config: try: with DVH_SQL(config) as cnx: pass except: valid = False else: try: with DVH_SQL() as cnx: pass except: valid = False if verbose: if valid: print("SQL DB is alive!") else: print("Connection to SQL DB could not be established.") return valid
def update_study_dates(self): with DVH_SQL() as cnx: choices = cnx.get_unique_values('Plans', 'sim_study_date', "mrn = '%s'" % self.mrn) self.combo_box_study_date.SetItems(choices) if choices: self.combo_box_study_date.SetValue(choices[0]) self.update_uids()
def ignore_dvh(self): unignore = self.button_uncategorized_ignored_ignore.GetLabelText( ) == 'Unignore DVH' with DVH_SQL() as cnx: for uid in self.dvh_uids: cnx.ignore_dvh(self.dvh, uid, unignore=unignore) self.update_uncategorized_ignored_choices()
def uid_has_ptvs(study_instance_uid): with DVH_SQL() as cnx: ans = cnx.query( 'DVHs', 'roi_type', "study_instance_uid = '%s' and roi_type LIKE 'PTV%%'" % study_instance_uid) return bool(ans)
def update_db(self): with DVH_SQL() as cnx: try: cnx.update(self.table, self.column, self.value, self.condition) except SQLError as sql_exception: SQLErrorDialog(self, sql_exception) self.error = True
def update_columns(self): table = self.combo_box_table.GetValue() with DVH_SQL() as cnx: columns = cnx.get_column_names(table) self.combo_box_column.SetItems(columns) if self.combo_box_column.GetValue() not in columns: self.combo_box_column.SetValue(columns[0])
def update_range(self, evt): key = self.combo_box_1.GetValue() table = self.numerical_categories[key]['table'] col = self.numerical_categories[key]['var_name'] units = self.numerical_categories[key]['units'] with DVH_SQL() as cnx: min_value = cnx.get_min_value(table, col) max_value = cnx.get_max_value(table, col) self.button_date_picker.Enable('date' in key.lower()) self.text_ctrl_min.Enable('date' not in key.lower()) self.text_ctrl_max.Enable('date' not in key.lower()) self.set_min_value(min_value) self.set_max_value(max_value) if 'date' in key.lower(): self.update_min_max_text('Start:', 'End:') self.on_date_picker() elif units: self.update_min_max_text('Min (%s):' % units, 'Max (%s):' % units) else: self.update_min_max_text('Min:', 'Max:') self.clean_numeric_input()
def get_plan_values(self, plan_column): """ Get values from the Plans table and store in order matching mrn / study_instance_uid :param plan_column: name of the SQL column to be queried :type plan_column: str :return: values from the Plans table for the DVHs stored in this class :rtype: list """ with DVH_SQL() as cnx: condition = "study_instance_uid in ('%s')" % "','".join( self.study_instance_uid) data = cnx.query('Plans', 'study_instance_uid, %s' % plan_column, condition) force_date = cnx.is_sqlite_column_datetime( 'Plans', plan_column) # returns False for pgsql uids = [row[0] for row in data] values = [row[1] for row in data] if force_date: # sqlite does not have date or time like variables for i, value in enumerate(values): try: if type(value) is int: values[i] = str(date_parser(str(value))) else: values[i] = str(date_parser(value)) except Exception: values[i] = 'None' return [values[uids.index(uid)] for uid in self.study_instance_uid]
def action(self): old_id = self.text_ctrl_1.GetValue() new_id = self.text_ctrl_2.GetValue() with DVH_SQL() as cnx: validation_func = [cnx.is_uid_imported, cnx.is_mrn_imported][self.sql_column == 'mrn'] change_func = [cnx.change_uid, cnx.change_mrn][self.sql_column == 'mrn'] if validation_func(old_id): if self.sql_column == 'study_instance_uid' and validation_func( new_id): wx.MessageBox( 'This Study Instance UID is already in use.', '%s Error' % self.combo_box_patient_identifier.GetValue(), wx.OK | wx.ICON_WARNING) else: change_func(old_id, new_id) else: wx.MessageBox( 'No studies found with this %s.' % self.combo_box_patient_identifier.GetValue(), '%s Error' % self.combo_box_patient_identifier.GetValue(), wx.OK | wx.ICON_WARNING)
def update_ptv_data(tv, study_instance_uid): """ :param tv: treatment volume formatted as a "sets of points" object specified in tools.roi_geometry :type tv: dict :param study_instance_uid: study_instance_uid in SQL database :type study_instance_uid: str """ ptv_cross_section = roi_geom.cross_section(tv) ptv_spread = roi_geom.spread(tv) condition = "study_instance_uid = '%s' and roi_type like 'PTV%%'" % study_instance_uid with DVH_SQL() as cnx: max_dose = cnx.get_max_value('dvhs', 'max_dose', condition=condition) min_dose = cnx.get_min_value('dvhs', 'min_dose', condition=condition) ptv_data = {'ptv_cross_section_max': ptv_cross_section['max'], 'ptv_cross_section_median': ptv_cross_section['median'], 'ptv_max_dose': max_dose, 'ptv_min_dose': min_dose, 'ptv_spread_x': ptv_spread[0], 'ptv_spread_y': ptv_spread[1], 'ptv_spread_z': ptv_spread[2], 'ptv_surface_area': roi_geom.surface_area(tv, coord_type='sets_of_points'), 'ptv_volume': roi_geom.volume(tv)} for key, value in ptv_data.items(): cnx.update('Plans', key, value, "study_instance_uid = '%s'" % study_instance_uid)
def get_uncategorized_variations(physician, ignored_variations=False): if echo_sql_db(): with DVH_SQL() as cnx: physician = clean_name(physician).upper() condition = "physician_roi = '%s'" % [ 'uncategorized', 'ignored' ][ignored_variations] cursor_rtn = cnx.query('dvhs', 'roi_name, study_instance_uid', condition) new_variations = {} for row in cursor_rtn: variation = str(row[0]) study_instance_uid = str(row[1]) physician_db = cnx.get_unique_values( 'Plans', 'physician', "study_instance_uid = '%s'" % study_instance_uid) if physician_db and physician_db[0] == physician: if variation not in list(new_variations): new_variations[variation] = { 'roi_name': variation, 'study_instance_uid': [study_instance_uid] } else: new_variations[variation][ 'study_instance_uid'].append( study_instance_uid) return new_variations
def query(table, column, condition): """ Automatically creates connection and for query """ with DVH_SQL() as cnx: ans = cnx.query(table, column, condition) return ans
def recalculate_plan_complexities_from_beams(): with DVH_SQL() as cnx: uids = cnx.get_unique_values('Plans', 'study_instance_uid') for uid in uids: try: condition = "study_instance_uid = '%s'" % uid beam_complexities = cnx.query( 'Beams', 'fx_count, complexity, fx_grp_number', condition) complexity = {} fx_counts = {} for row in beam_complexities: fx_count, beam_complexity, fx_group_number = tuple(row) if fx_group_number not in complexity: complexity[fx_group_number] = 0.0 fx_counts[fx_group_number] = fx_count complexity[fx_group_number] += beam_complexity total_fx = float(sum([fx for fx in fx_counts.values()])) plan_complexity = sum([ c * fx_counts[fx_grp] for fx_grp, c in complexity.items() ]) / total_fx except Exception as e: msg = "tools.utilities.recalculate_plan_complexities_from_beams: failed on uid = %s" % uid push_to_log(e, msg=msg) plan_complexity = None if plan_complexity is not None: cnx.update('Plans', 'complexity', plan_complexity, condition)
def on_query(self, evt): self.update_selected_tree_items() table = self.combo_box_query_table.GetValue() columns = [ c for c, sel in self.selected_columns[table].items() if sel and c not in {'mrn', 'study_instance_uid'} ] columns.sort() if not columns: columns = [ c for c in self.db_tree[table] if c not in {'mrn', 'study_instance_uid'} ] columns.insert(0, 'study_instance_uid') columns.insert(0, 'mrn') condition = self.text_ctrl_condition.GetValue() with DVH_SQL() as cnx: try: data = cnx.query(table, ','.join(columns), condition, bokeh_cds=True) self.data_query_results.set_data(data, columns) except SQLError as e: SQLErrorDialog(self, e) self.data_query_results.clear()
def remap_rois(self): with DVH_SQL() as cnx: for physician, variations in self.variations_to_update.items(): for variation in variations: new_physician_roi = self.get_physician_roi( physician, variation) new_institutional_roi = self.get_institutional_roi( physician, new_physician_roi) condition = "REPLACE(REPLACE(LOWER(roi_name), '\'', '`'), '_', ' ') == '%s'" % variation sql_query = "SELECT DISTINCT study_instance_uid, roi_name FROM DVHs WHERE %s;" % condition uids_roi_names = cnx.query_generic(sql_query) uids = [row[0] for row in uids_roi_names] roi_names = [row[1] for row in uids_roi_names] if uids: for i, uid in enumerate(uids): roi_name = roi_names[i] condition = "roi_name = '%s' and study_instance_uid = '%s'" % ( roi_name, uid) cnx.update('dvhs', 'physician_roi', new_physician_roi, condition) cnx.update('dvhs', 'institutional_roi', new_institutional_roi, condition) self.write_to_file()
def action(self): with DVH_SQL() as cnx: dicom_files = cnx.get_dicom_file_paths(uid=self.uid) if self.delete_from_db: cnx.delete_rows("study_instance_uid = '%s'" % self.uid) move_imported_dicom_files(dicom_files, INBOX_DIR) ImportDicomFrame(inbox=INBOX_DIR)
def update_uncategorized_rois_in_database(): roi_map = DatabaseROIs() dvh_data = QuerySQL('DVHs', "physician_roi = 'uncategorized'") with DVH_SQL() as cnx: for i in range(len(dvh_data.roi_name)): uid = dvh_data.study_instance_uid[i] mrn = dvh_data.mrn[i] physician = get_physician_from_uid(uid) roi_name = dvh_data.roi_name[i] new_physician_roi = roi_map.get_physician_roi(physician, roi_name) new_institutional_roi = roi_map.get_institutional_roi( physician, roi_name) if new_physician_roi != 'uncategorized': print(mrn, physician, new_institutional_roi, new_physician_roi, roi_name, sep=' ') condition = "study_instance_uid = '" + uid + "'" + "and roi_name = '" + roi_name + "'" cnx.update('DVHs', 'physician_roi', new_physician_roi, condition) cnx.update('DVHs', 'institutional_roi', new_institutional_roi, condition)
def __init__(self, table_name, condition_str, unique=False, columns=None): """ :param table_name: 'Beams', 'DVHs', 'Plans', or 'Rxs' :type table_name: str :param condition_str: condition in SQL syntax :type condition_str: str :param unique: If set to True, only unique values stored :type unique: bool """ table_name = table_name.lower() if table_name in {'beams', 'dvhs', 'plans', 'rxs'}: self.table_name = table_name self.condition_str = condition_str with DVH_SQL() as cnx: all_columns = cnx.get_column_names(table_name) if columns is not None: columns = set(all_columns).intersection(columns) # ensure provided columns exist in SQL table else: columns = all_columns for column in columns: if column not in {'roi_coord_string', 'distances_to_ptv'}: # ignored for memory since not used here self.cursor = cnx.query(self.table_name, column, self.condition_str) force_date = cnx.is_sqlite_column_datetime(self.table_name, column) # returns False for pgsql rtn_list = self.cursor_to_list(force_date=force_date) if unique: rtn_list = get_unique_list(rtn_list) setattr(self, column, rtn_list) # create property of QuerySQL based on SQL column name else: print('Table name in valid. Please select from Beams, DVHs, Plans, or Rxs.')
def action(self): if self.text_ctrl_2.GetValue() == 'delete': value = self.text_ctrl_1.GetValue() with DVH_SQL() as cnx: dicom_files = cnx.get_dicom_file_paths(**{self.sql_column: value}) cnx.delete_rows("%s = '%s'" % (self.sql_column, value)) DeleteFilesFromQuery(self, dicom_files)
def action(self): with DVH_SQL() as cnx: dicom_files = cnx.get_dicom_file_paths(uid=self.uid) if self.delete_from_db: cnx.delete_rows("study_instance_uid = '%s'" % self.uid) inbox = self.options.INBOX_DIR move_imported_dicom_files(dicom_files, inbox) ImportDicomFrame(self.roi_map, self.options, inbox=inbox, auto_parse=True)
def action_yes(self): with DVH_SQL() as cnx: cnx.reinitialize_database() ImportDicomFrame(self.roi_map, self.options, inbox=self.options.IMPORTED_DIR, auto_parse=True)
def get_physician_from_uid(uid): with DVH_SQL() as cnx: results = cnx.query('Plans', 'physician', "study_instance_uid = '" + uid + "'") if len(results) > 1: print('Warning: multiple plans with this study_instance_uid exist') return str(results[0][0])
def query(table, column, condition, unique=False): """ Automatically creates connection for query """ with DVH_SQL() as cnx: func = cnx.get_unique_values if unique else cnx.query ans = func(table, column, condition) return ans
def get_physician_from_uid(uid): with DVH_SQL() as cnx: results = cnx.query('Plans', 'physician', "study_instance_uid = '" + uid + "'") if len(results) > 1: msg = 'roi_name_manager.get_physician_from_uid: multiple plans with this study_instance_uid exist: %s' % uid push_to_log(msg=msg) return str(results[0][0])
def get_database_tree(): """ Query SQL to get all columns of each table :return: column data sorted by table :rtype: dict """ with DVH_SQL() as cnx: tree = {table: cnx.get_column_names(table) for table in cnx.tables} return tree
def mrn_ticker(self, evt): with DVH_SQL() as cnx: is_mrn_valid = cnx.is_mrn_imported(self.mrn) if is_mrn_valid: self.update_study_dates() else: self.combo_box_study_date.SetItems([]) self.combo_box_uid.SetItems([]) self.button_reimport.Disable()
def update_category_2(self, evt): key = self.combo_box_1.GetValue() table = self.selector_categories[key]['table'] col = self.selector_categories[key]['var_name'] with DVH_SQL() as cnx: options = cnx.get_unique_values(table, col) self.combo_box_2.Clear() self.combo_box_2.Append(options) if options: self.combo_box_2.SetValue(options[0])
def run(self): res = self.ShowModal() if res == wx.ID_OK: new_config = {key: self.input[key].GetValue() for key in self.keys if self.input[key].GetValue()} if echo_sql_db(new_config, db_type=self.selected_db_type): self.write_successful_cnf() with DVH_SQL(group=self.group) as cnx: cnx.initialize_database() else: dlg = wx.MessageDialog(self, 'Connection to database could not be established.', 'ERROR!', wx.OK | wx.ICON_ERROR) dlg.ShowModal() self.Destroy()
def update_uids(self): date = ['is NULL', "= '%s'::date" % self.sim_study_date ][self.sim_study_date != 'None'] condition = "mrn = '%s' and sim_study_date %s" % (self.mrn, date) with DVH_SQL() as cnx: choices = cnx.get_unique_values('Plans', 'study_instance_uid', condition) self.combo_box_uid.SetItems(choices) if choices: self.combo_box_uid.SetValue(choices[0]) else: self.combo_box_uid.SetValue(None) self.uid_ticker(None)
def update_dvhs_table(study_instance_uid, roi_name, column, value): """ Generic function to update a value in the DVHs table :param study_instance_uid: study instance uid in the SQL table :type study_instance_uid: str :param roi_name: the roi name associated with the value to be updated :type roi_name: str :param column: the SQL column of the value to be updated :type column: str :param value: the value to be set, it's type should match the type as specified in the SQL table """ with DVH_SQL() as cnx: cnx.update('dvhs', column, value, "study_instance_uid = '%s' and roi_name = '%s'" % (study_instance_uid, roi_name))