def plot_selection(): db_access.setup_db() session = db_access.get_session() try: obj = defaultdict(lambda: defaultdict(lambda: defaultdict(list))) rows = execute_with_retry( session, ''' SELECT selection_params.id, selection_params.subsystem, selection_params.pd, selection_params.processing_string, last_calculated_configs.name FROM selection_params JOIN last_calculated_configs ON config_id = last_calculated_configs.id ORDER BY selection_params.subsystem, selection_params.pd, selection_params.processing_string, last_calculated_configs.name ; ''') rows = list(rows) for row in rows: if (row['processing_string'] in ALLOWED_PROCESSING_STRINGS): obj[row['subsystem']][row['pd']][ row['processing_string']].append({ 'name': row['name'], 'id': row['id'] }) return jsonify(obj) finally: session.close()
def fetch(update, nproc): db_access.setup_db() all_runs = [] extracted_runs = [] session = db_access.get_session() try: print("Getting missing runs...") all_runs = list( session.execute("SELECT DISTINCT(run) FROM historic_data_points;")) all_runs = [x[0] for x in all_runs] extracted_runs = list( session.execute("SELECT DISTINCT(run) FROM oms_data_cache;")) extracted_runs = [x[0] for x in extracted_runs] finally: session.close() # Diff needs to be extracted! if update: diff = all_runs else: diff = [x for x in all_runs if x not in extracted_runs] print("Number of runs to be fetched: %s" % len(diff)) db_access.dispose_engine() pool = Pool(nproc) pool.map(fetch_run, diff) print("Done.")
def execute_with_retry(session, sql, params=None): try: result = session.execute(sql, params) except psycopg2.OperationalError as e: print('Retrying:') print(e) session = db_access.get_session() result = session.execute(sql) return result
def runs(): db_access.setup_db() session = db_access.get_session() runs = [ h.run for h in session.query(db_access.HistoricDataPoint.run).distinct( ).order_by(db_access.HistoricDataPoint.run.asc()) ] session.close() return jsonify(runs)
def prewarm(): # prewarm multiple times for _ in range(3): session = db_access.get_session() try: print('Prewarming...') response = session.execute( "SELECT pg_prewarm('historic_data_points');") print('Done.') except Exception as e: print(e) finally: session.close()
def move_to_second_queue(me_id, queue_id): session = db_access.get_session() try: session.execute( 'INSERT INTO queue_to_calculate_later (me_id) VALUES (:me_id);', {'me_id': me_id}) session.execute('DELETE FROM queue_to_calculate WHERE id = :queue_id;', {'queue_id': queue_id}) session.commit() except Exception as e: session.rollback() print(e) finally: session.close()
def get_me_blob_by_me_id(id): sql = 'SELECT monitor_elements.gui_url, monitor_elements.image_url, me_blob FROM monitor_elements JOIN me_blobs ON monitor_elements.me_blob_id= me_blobs.id WHERE monitor_elements.id=:id;' session = db_access.get_session() me = None try: me = session.execute(sql, {'id': id}) me = list(me) except Exception as e: print(e) finally: session.close() if not me: return None, None, None return me[0]['me_blob'], me[0]['gui_url'], me[0]['image_url']
def exec_transaction(sql, params=None): session = db_access.get_session() try: if type(sql) == list: for query in sql: session.execute(query, params) else: session.execute(sql, params) session.commit() return True except Exception as e: print(e) return False finally: session.close()
def expand_url(): valid_url_types = [ 'main_gui_url', 'main_image_url', 'optional1_gui_url', 'optional1_image_url', 'optional2_gui_url', 'optional2_image_url', 'reference_gui_url', 'reference_image_url' ] data_point_id = request.args.get('data_point_id', type=int) url_type = request.args.get('url_type') if data_point_id == None: return jsonify( {'message': 'Please provide a data_point_id parameter.'}), 400 if url_type not in valid_url_types: return jsonify({ 'message': 'Please provide a valid url_type parameter. Accepted values are: %s' % ','.join(valid_url_types) }), 400 db_access.setup_db() session = db_access.get_session() try: sql = ''' SELECT %s FROM historic_data_points WHERE id = :id ; ''' % url_type rows = list(execute_with_retry(session, sql, {'id': data_point_id})) url = rows[0][url_type] if url: url = url.replace('+', '%2B') return redirect(url, code=302) else: return jsonify({'message': 'Requested URL type is not found.'}), 404 except Exception as e: print(e) finally: session.close() return jsonify({'message': 'Error getting the url from the DB.'}), 500
def selection(): db_access.setup_db() session = db_access.get_session() try: obj = defaultdict(lambda: defaultdict(list)) rows = execute_with_retry( session, 'SELECT DISTINCT subsystem, pd, processing_string FROM selection_params ORDER BY subsystem, pd, processing_string;' ) rows = list(rows) for row in rows: if (row['processing_string'] in ALLOWED_PROCESSING_STRINGS): obj[row['subsystem']][row['pd']].append( row['processing_string']) return jsonify(obj) finally: session.close()
def fetch(update): db_access.setup_db() min_run = 0 max_run = 0 session = db_access.get_session() try: print('Getting min and max runs...') minmax = list( session.execute('SELECT MIN(run), MAX(run) FROM oms_data_cache;')) min_run = minmax[0][0] max_run = minmax[0][1] finally: session.close() print('Run range: %s-%s' % (min_run, max_run)) fetch_runs(min_run, max_run)
def add_oms_info_to_result(result): runs = set() for item in result: for trend in item['trends']: runs.add(trend['run']) runs = list(runs) db_access.dispose_engine() session = db_access.get_session() query = session.query(db_access.OMSDataCache)\ .filter(db_access.OMSDataCache.run.in_(tuple(runs)))\ .all() db_oms_data = list(query) session.close() oms_data_dict = defaultdict(list) for row in db_oms_data: oms_data_dict[row.run] = { 'start_time': row.start_time, 'end_time': row.end_time, 'b_field': row.b_field, 'energy': row.energy, 'delivered_lumi': row.delivered_lumi, 'end_lumi': row.end_lumi, 'recorded_lumi': row.recorded_lumi, 'l1_key': row.l1_key, 'l1_rate': row.l1_rate, 'hlt_key': row.hlt_key, 'hlt_physics_rate': row.hlt_physics_rate, 'duration': row.duration, 'fill_number': row.fill_number, 'injection_scheme': row.injection_scheme, 'era': row.era, } # Add oms_info to the respose for item in result: for trend in item['trends']: trend['oms_info'] = oms_data_dict[trend['run']] return result
def get_optional_me(eos_path, me_paths): sql = 'SELECT monitor_elements.id, monitor_elements.gui_url, monitor_elements.image_url, me_blob FROM monitor_elements JOIN me_blobs ON monitor_elements.me_blob_id= me_blobs.id WHERE eos_path=:eos_path AND me_path=:me_path;' for me_path in me_paths: session = db_access.get_session() me = None try: me = session.execute(sql, { 'eos_path': eos_path, 'me_path': me_path }) me = list(me) except Exception as e: print(e) finally: session.close() if me: return me[0]['id'], me[0]['me_blob'], me[0]['gui_url'], me[0][ 'image_url'] # None were found return None, None, None, None
def fetch_run(run): print("Fetching run %s..." % run) db_access.dispose_engine() runs_url = ( "https://cmsoms.cern.ch/agg/api/v1/runs?filter[run_number][eq]=%s&fields=start_time,end_time,b_field,energy,delivered_lumi,end_lumi,recorded_lumi,l1_key,hlt_key,l1_rate,hlt_physics_rate,duration,fill_number" % run) try: token = get_token.get_token() headers = {"Authorization": "Bearer %s" % (token)} response = requests.get(runs_url, headers=headers, verify=False).json() oms_runs_json = response fills_url = ( "https://cmsoms.cern.ch/agg/api/v1/fills?filter[fill_number][eq]=%s&fields=injection_scheme,era" % oms_runs_json["data"][0]["attributes"]["fill_number"]) oms_fills_json = requests.get(fills_url, headers=headers, verify=False).json() # TODO: Change to prod url, add cookies and certificate dcs_collisions_lumis_url = ( """https://cmsoms.cern.ch/agg/api/v1/lumisections?filter[run_number][eq]=%s &filter[cms_active][eq]=true &filter[bpix_ready][eq]=true &filter[fpix_ready][eq]=true &filter[tibtid_ready][eq]=true &filter[tecm_ready][eq]=true &filter[tecp_ready][eq]=true &filter[tob_ready][eq]=true &filter[ebm_ready][eq]=true &filter[ebp_ready][eq]=true &filter[eem_ready][eq]=true &filter[eep_ready][eq]=true &filter[esm_ready][eq]=true &filter[esp_ready][eq]=true &filter[hbhea_ready][eq]=true &filter[hbheb_ready][eq]=true &filter[hbhec_ready][eq]=true &filter[hf_ready][eq]=true &filter[ho_ready][eq]=true &filter[dtm_ready][eq]=true &filter[dtp_ready][eq]=true &filter[dt0_ready][eq]=true &filter[cscm_ready][eq]=true &filter[cscp_ready][eq]=true &filter[gem_ready][eq]=true #&filter[gemm_ready][eq]=true # gemm_ready and gemp_ready is not supported yet in oms #&filter[gemp_ready][eq]=true &fields=run_number &page[limit]=1""" % run) dcs_cosmics_lumis_url1 = ( """https://cmsoms.cern.ch/agg/api/v1/lumisections ?filter[run_number][eq]=%s &filter[dtm_ready][eq]=true &filter[dtp_ready][eq]=true &filter[dt0_ready][eq]=true &filter[gem_ready][eq]=true #&filter[gemm_ready][eq]=true # gemm_ready and gemp_ready is not supported yet in oms #&filter[gemp_ready][eq]=true &fields=run_number &page[limit]=1""" % run) dcs_cosmics_lumis_url2 = ( """https://cmsoms.cern.ch/agg/api/v1/lumisections ?filter[run_number][eq]=%s &filter[cscm_ready][eq]=true &filter[cscp_ready][eq]=true &fields=run_number &page[limit]=1""" % run) is_dcs = False # For cosmics, we need to OR two terms. If first query if false, make a second one. if "cosmic" in oms_runs_json["data"][0]["attributes"]["hlt_key"]: dcs_lumisections_json = requests.get( dcs_cosmics_lumis_url1.replace("\n", ""), headers=headers, verify=False).json() if len(dcs_lumisections_json["data"]): is_dcs = True else: dcs_lumisections_json = requests.get( dcs_cosmics_lumis_url2.replace("\n", ""), headers=headers, verify=False, ).json() if len(dcs_lumisections_json["data"]): is_dcs = True else: dcs_lumisections = requests.get( dcs_collisions_lumis_url.replace("\n", ""), headers=headers, verify=False, ) # PROBLEMATISKAS dcs_lumisections_json = json.loads(dcs_lumisections.text) # dcs_lumisections_json = {"data": []} if len(dcs_lumisections_json["data"]): is_dcs = True # Add to cache session = db_access.get_session() try: oms_item = db_access.OMSDataCache( run=run, lumi=0, start_time=datetime.datetime.strptime( oms_runs_json["data"][0]["attributes"]["start_time"], "%Y-%m-%dT%H:%M:%SZ", ), end_time=datetime.datetime.strptime( oms_runs_json["data"][0]["attributes"]["end_time"], "%Y-%m-%dT%H:%M:%SZ", ), b_field=oms_runs_json["data"][0]["attributes"]["b_field"], energy=oms_runs_json["data"][0]["attributes"]["energy"], delivered_lumi=oms_runs_json["data"][0]["attributes"] ["delivered_lumi"], end_lumi=oms_runs_json["data"][0]["attributes"]["end_lumi"], recorded_lumi=oms_runs_json["data"][0]["attributes"] ["recorded_lumi"], l1_key=oms_runs_json["data"][0]["attributes"]["l1_key"], l1_rate=oms_runs_json["data"][0]["attributes"]["l1_rate"], hlt_key=oms_runs_json["data"][0]["attributes"]["hlt_key"], hlt_physics_rate=oms_runs_json["data"][0]["attributes"] ["hlt_physics_rate"], duration=oms_runs_json["data"][0]["attributes"]["duration"], fill_number=oms_runs_json["data"][0]["attributes"] ["fill_number"], is_dcs=is_dcs, ) try: oms_item.injection_scheme = oms_fills_json["data"][0][ "attributes"]["injection_scheme"] oms_item.era = oms_fills_json["data"][0]["attributes"]["era"] except: oms_item.injection_scheme = None oms_item.era = None session.add(oms_item) session.commit() except IntegrityError as e: print("IntegrityError inserting OMS item: %s" % e) session.rollback() print("Updating...") try: oms_item_existing = (session.query( db_access.OMSDataCache).filter( db_access.OMSDataCache.run == oms_item.run, db_access.OMSDataCache.lumi == oms_item.lumi, ).one_or_none()) if oms_item_existing: if isinstance(oms_item.start_time, datetime.datetime): oms_item_existing.start_time = oms_item.start_time else: oms_item_existing.start_time = datetime.datetime.strptime( oms_item.start_time, "%Y-%m-%dT%H:%M:%SZ") if isinstance(oms_item.end_time, datetime.datetime): oms_item_existing.end_time = oms_item.end_time else: oms_item_existing.end_time = datetime.datetime.strptime( oms_item.end_time, "%Y-%m-%dT%H:%M:%SZ") oms_item_existing.b_field = oms_item.b_field oms_item_existing.energy = oms_item.energy oms_item_existing.delivered_lumi = oms_item.delivered_lumi oms_item_existing.end_lumi = oms_item.end_lumi oms_item_existing.recorded_lumi = oms_item.recorded_lumi oms_item_existing.l1_key = oms_item.l1_key oms_item_existing.l1_rate = oms_item.l1_rate oms_item_existing.hlt_key = oms_item.hlt_key oms_item_existing.hlt_physics_rate = oms_item.hlt_physics_rate oms_item_existing.duration = oms_item.duration oms_item_existing.fill_number = oms_item.fill_number oms_item_existing.injection_scheme = oms_item.injection_scheme oms_item_existing.era = oms_item.era oms_item_existing.is_dcs = oms_item.is_dcs session.commit() print("Updated.") except Exception as e: print(e) session.rollback() except Exception as e: print(e) session.rollback() finally: session.close() except Exception as e: print(e)
def fetch_runs(min_run, max_run): url = 'https://cmsrunregistry.web.cern.ch/api/runs_filtered_ordered' request = ''' { "page": 0, "page_size": 100000, "sortings":[], "filter": { "run_number": { "and": [ {">=": %s}, {"<=": %s} ] }, "rr_attributes.significant": true } } ''' % (min_run, max_run) try: cookies = get_sso_cookie(url) text = requests.post(url, json=json.loads(request), cookies=cookies, verify=True).text result_json = json.loads(text) for run in result_json['runs']: # Logic to determine if run is significant for HDQM: # 1. If HLT key contains string "special", run is not significant # 2. For collision runs integrated luminosity has to be greater than 0.1 1/pb # 3. For cosmic runs duration has to be longer than 1 hour significant = db_access.false_crossdb() if 'special' in run['oms_attributes']['hlt_key'].lower(): significant = db_access.false_crossdb() # For collision runs: elif 'collision' in run['rr_attributes']['class'].lower(): if run['oms_attributes'][ 'recorded_lumi'] >= MIN_LUMI_FOR_COLLISIONS: significant = db_access.true_crossdb() # For cosmic runs: elif 'cosmic' in run['rr_attributes']['class'].lower(): if run['oms_attributes'][ 'duration'] >= MIN_DURATION_FOR_COSMICS: significant = db_access.true_crossdb() else: # Not significant if neither cosmics nor collisions pass sql = db_access.returning_id_crossdb( 'UPDATE oms_data_cache SET significant=%s, run_class=:run_class WHERE run = :run_nr;' % significant) session = db_access.get_session() try: result = session.execute( sql, { 'run_nr': run['run_number'], 'run_class': run['rr_attributes']['class'] }) if result.returns_rows: result = list(result) if len(result) == 0: print( 'Run not present in OMS cache: %s. RR class: %s' % (run['run_number'], run['rr_attributes']['class'])) session.commit() except Exception as e: print(e) session.rollback() finally: session.close() except Exception as e: print(e)
def fetch_run(run): print('Fetching run %s...' % run) db_access.dispose_engine() runs_url = 'https://cmsoms.cern.ch/agg/api/v1/runs?filter[run_number][eq]=%s&fields=start_time,end_time,b_field,energy,delivered_lumi,end_lumi,recorded_lumi,l1_key,hlt_key,l1_rate,hlt_physics_rate,duration,fill_number' % run try: cookies = get_sso_cookie(runs_url) oms_runs_json = json.loads( requests.get(runs_url, cookies=cookies, verify=CACERT).text) fills_url = 'https://cmsoms.cern.ch/agg/api/v1/fills?filter[fill_number][eq]=%s&fields=injection_scheme,era' % oms_runs_json[ 'data'][0]['attributes']['fill_number'] oms_fills_json = json.loads( requests.get(fills_url, cookies=cookies, verify=CACERT).text) # TODO: Change to prod url, add cookies and certificate dcs_collisions_lumis_url = '''https://vocms0183.cern.ch/agg/api/v1/lumisections?filter[run_number][eq]=%s &filter[cms_active][eq]=true &filter[bpix_ready][eq]=true &filter[fpix_ready][eq]=true &filter[tibtid_ready][eq]=true &filter[tecm_ready][eq]=true &filter[tecp_ready][eq]=true &filter[tob_ready][eq]=true &filter[ebm_ready][eq]=true &filter[ebp_ready][eq]=true &filter[eem_ready][eq]=true &filter[eep_ready][eq]=true &filter[esm_ready][eq]=true &filter[esp_ready][eq]=true &filter[hbhea_ready][eq]=true &filter[hbheb_ready][eq]=true &filter[hbhec_ready][eq]=true &filter[hf_ready][eq]=true &filter[ho_ready][eq]=true &filter[dtm_ready][eq]=true &filter[dtp_ready][eq]=true &filter[dt0_ready][eq]=true &filter[cscm_ready][eq]=true &filter[cscp_ready][eq]=true &fields=run_number &page[limit]=1''' % run dcs_cosmics_lumis_url1 = '''https://vocms0183.cern.ch/agg/api/v1/lumisections ?filter[run_number][eq]=%s &filter[dtm_ready][eq]=true &filter[dtp_ready][eq]=true &filter[dt0_ready][eq]=true &fields=run_number &page[limit]=1''' % run dcs_cosmics_lumis_url2 = '''https://vocms0183.cern.ch/agg/api/v1/lumisections ?filter[run_number][eq]=%s &filter[cscm_ready][eq]=true &filter[cscp_ready][eq]=true &fields=run_number &page[limit]=1''' % run is_dcs = False # For cosmics, we need to OR two terms. If first query if false, make a second one. if 'cosmic' in oms_runs_json['data'][0]['attributes']['hlt_key']: dcs_lumisections_json = json.loads( requests.get(dcs_cosmics_lumis_url1.replace('\n', ''), verify=False).text) if len(dcs_lumisections_json['data']): is_dcs = True else: dcs_lumisections_json = json.loads( requests.get(dcs_cosmics_lumis_url2.replace('\n', ''), verify=False).text) if len(dcs_lumisections_json['data']): is_dcs = True else: dcs_lumisections_json = json.loads( requests.get(dcs_collisions_lumis_url.replace('\n', ''), verify=False).text) if len(dcs_lumisections_json['data']): is_dcs = True # Add to cache session = db_access.get_session() try: oms_item = db_access.OMSDataCache( run=run, lumi=0, start_time=datetime.datetime.strptime( oms_runs_json['data'][0]['attributes']['start_time'], "%Y-%m-%dT%H:%M:%SZ"), end_time=datetime.datetime.strptime( oms_runs_json['data'][0]['attributes']['end_time'], "%Y-%m-%dT%H:%M:%SZ"), b_field=oms_runs_json['data'][0]['attributes']['b_field'], energy=oms_runs_json['data'][0]['attributes']['energy'], delivered_lumi=oms_runs_json['data'][0]['attributes'] ['delivered_lumi'], end_lumi=oms_runs_json['data'][0]['attributes']['end_lumi'], recorded_lumi=oms_runs_json['data'][0]['attributes'] ['recorded_lumi'], l1_key=oms_runs_json['data'][0]['attributes']['l1_key'], l1_rate=oms_runs_json['data'][0]['attributes']['l1_rate'], hlt_key=oms_runs_json['data'][0]['attributes']['hlt_key'], hlt_physics_rate=oms_runs_json['data'][0]['attributes'] ['hlt_physics_rate'], duration=oms_runs_json['data'][0]['attributes']['duration'], fill_number=oms_runs_json['data'][0]['attributes'] ['fill_number'], is_dcs=is_dcs) try: oms_item.injection_scheme = oms_fills_json['data'][0][ 'attributes']['injection_scheme'] oms_item.era = oms_fills_json['data'][0]['attributes']['era'] except: oms_item.injection_scheme = None oms_item.era = None session.add(oms_item) session.commit() except IntegrityError as e: print('IntegrityError inserting OMS item: %s' % e) session.rollback() print('Updating...') try: oms_item_existing = session.query( db_access.OMSDataCache).filter( db_access.OMSDataCache.run == oms_item.run, db_access.OMSDataCache.lumi == oms_item.lumi).one_or_none() if oms_item_existing: if isinstance(oms_item.start_time, datetime.datetime): oms_item_existing.start_time = oms_item.start_time else: oms_item_existing.start_time = datetime.datetime.strptime( oms_item.start_time, "%Y-%m-%dT%H:%M:%SZ") if isinstance(oms_item.end_time, datetime.datetime): oms_item_existing.end_time = oms_item.end_time else: oms_item_existing.end_time = datetime.datetime.strptime( oms_item.end_time, "%Y-%m-%dT%H:%M:%SZ") oms_item_existing.b_field = oms_item.b_field oms_item_existing.energy = oms_item.energy oms_item_existing.delivered_lumi = oms_item.delivered_lumi oms_item_existing.end_lumi = oms_item.end_lumi oms_item_existing.recorded_lumi = oms_item.recorded_lumi oms_item_existing.l1_key = oms_item.l1_key oms_item_existing.l1_rate = oms_item.l1_rate oms_item_existing.hlt_key = oms_item.hlt_key oms_item_existing.hlt_physics_rate = oms_item.hlt_physics_rate oms_item_existing.duration = oms_item.duration oms_item_existing.fill_number = oms_item.fill_number oms_item_existing.injection_scheme = oms_item.injection_scheme oms_item_existing.era = oms_item.era oms_item_existing.is_dcs = oms_item.is_dcs session.commit() print('Updated.') except Exception as e: print(e) session.rollback() except Exception as e: print(e) session.rollback() finally: session.close() except Exception as e: print(e)
def calculate_all_trends(cfg_files, runs, nprocs): print('Processing %d configuration files...' % len(cfg_files)) db_access.setup_db() trend_count = 0 for cfg_file in cfg_files: subsystem = os.path.basename(os.path.dirname(cfg_file)) if not subsystem: subsystem = 'Unknown' parser = RawConfigParser() parser.read(unicode(cfg_file)) for section in parser: if not section.startswith('plot:'): if (section != 'DEFAULT'): print('Invalid configuration section: %s:%s, skipping.' % (cfg_file, section)) continue if not PLOTNAMEPATTERN.match(section.lstrip('plot:')): print( "Invalid plot name: '%s:%s' Plot names can contain only: [a-zA-Z0-9_+-]" % (cfg_file, section.lstrip('plot:'))) continue if 'metric' not in parser[section] or\ 'relativePath' not in parser[section] or\ 'yTitle' not in parser[section]: print('Plot missing required attributes: %s:%s, skipping.' % (cfg_file, section)) print('Required parameters: metric, relativePath, yTitle') continue parser[section]['subsystem'] = subsystem parser[section]['name'] = section.split(':')[1] CONFIG.append(parser[section]) trend_count += 1 print('Starting to process %s trends.' % trend_count) print('Updating configuration...') # Find out new and changed configuration last_config = [] session = db_access.get_session() try: last_config = list( session.execute('SELECT * FROM last_calculated_configs;')) except Exception as e: print('Exception getting config from the DB: %s' % e) return finally: session.close() new_configs = [] for current in CONFIG: # Find by subsystem and name of trend last = next( (x for x in last_config if current['subsystem'] == x['subsystem'] and current['name'] == x['name']), None) if last: obj = section_to_config_object(current) if not last['metric'] == obj.metric or\ not last['plot_title'] == obj.plot_title or\ not last['y_title'] == obj.y_title or\ not last['relative_path'] == obj.relative_path or\ not last['histo1_path'] == obj.histo1_path or\ not last['histo2_path'] == obj.histo2_path or\ not last['reference_path'] == obj.reference_path or\ not last['threshold'] == int(obj.threshold) if obj.threshold else None: # Changed! new_configs.append(obj) else: new_configs.append(section_to_config_object(current)) # Add new configs session = db_access.get_session() try: for new in new_configs: session.add(new) session.commit() except Exception as e: print('Exception adding new configs to the DB: %s' % e) session.rollback() return finally: session.close() # Recalculate everything if the configuration changed if len(new_configs) > 0: print('Configuration changed, reseting the calculation queue...') session = db_access.get_session() try: session.execute('DELETE FROM queue_to_calculate;') session.execute('DELETE FROM queue_to_calculate_later;') session.execute( 'INSERT INTO queue_to_calculate (me_id) SELECT id FROM monitor_elements;' ) session.commit() except Exception as e: print('Exception reseting the calculation queue in the DB: %s' % e) session.rollback() return finally: session.close() print('Calculation queue is ready.') else: # Move things from queue_to_calculate_later back to queue_to_calculate print('Moving items from second queue to the main one...') session = db_access.get_session() try: session.execute( 'INSERT INTO queue_to_calculate (me_id) SELECT me_id FROM queue_to_calculate_later;' ) session.execute('DELETE FROM queue_to_calculate_later;') session.commit() except Exception as e: print( 'Exception moving items from the second calculation queue to the first: %s' % e) session.rollback() finally: session.close() print('Calculation queue is ready.') print('Configuration updated.') # Start calculating trends if runs == None: runs_filter = '' else: runs_filter = 'WHERE monitor_elements.run IN (%s)' % ', '.join( str(x) for x in runs) limit = 10000 sql = ''' SELECT queue_to_calculate.id, monitor_elements.id as me_id, monitor_elements.run, monitor_elements.lumi, monitor_elements.eos_path, monitor_elements.me_path, monitor_elements.dataset FROM monitor_elements JOIN queue_to_calculate ON monitor_elements.id=queue_to_calculate.me_id %s LIMIT %s; ''' % (runs_filter, limit) # pool = Pool(nprocs) pool = ForkPool(nprocs) while True: db_access.dispose_engine() session = db_access.get_session() try: print('Fetching not processed data points from DB...') rows = session.execute(sql) rows = list(rows) print('Fetched: %s' % len(rows)) if len(rows) == 0: print('Queue to calculate is empty. Exiting.') break pool.map(calculate_trends, batch_iterable(rows, chunksize=400)) print('Finished calculating a batch of trends.') except OSError as e: if e.errno != errno.EINTR: raise else: print('[Errno 4] occurred. Continueing.') except Exception as e: print( 'Exception fetching elements from the calculation queue: %s' % e) raise finally: session.close()
def calculate_trends(rows): db_access.dispose_engine() for row in rows: print('Calculating trend:', row['eos_path'], row['me_path']) # All configs referencing row['me_path'] as main me configs = [ x for x in CONFIG if row['me_path'] in get_all_me_names(x['relativePath']) ] if not configs: print('ME not used is any config') # Remove ME from queue_to_calculate session = db_access.get_session() try: session.execute( 'DELETE FROM queue_to_calculate WHERE id = :id;', {'id': row['id']}) session.commit() except Exception as e: session.rollback() print(e) finally: session.close() continue for config in configs: tdirectories = [] try: try: metric = eval(config['metric'], METRICS_MAP) except Exception as e: print('Unable to load the metric: %s. %s' % (config['metric'], e)) move_to_second_queue(row['me_id'], row['id']) break histo1_id = None histo2_id = None reference_id = None histo1_gui_url = None histo2_gui_url = None reference_gui_url = None histo1_image_url = None histo2_image_url = None reference_image_url = None if 'histo1Path' in config: histo1_id, histo1, histo1_gui_url, histo1_image_url = get_optional_me( row['eos_path'], get_all_me_names(config['histo1Path'])) if not histo1: print( 'Unable to get an optional monitor element 1: %s:%s' % (row['eos_path'], config['histo1Path'])) move_to_second_queue(row['me_id'], row['id']) break plot, tdir = get_plot_from_blob(histo1) tdirectories.append(tdir) metric.setOptionalHisto1(plot) if 'histo2Path' in config: histo2_id, histo2, histo2_gui_url, histo2_image_url = get_optional_me( row['eos_path'], get_all_me_names(config['histo2Path'])) if not histo2: print( 'Unable to get an optional monitor element 2: %s:%s' % (row['eos_path'], config['histo2Path'])) move_to_second_queue(row['me_id'], row['id']) break plot, tdir = get_plot_from_blob(histo2) tdirectories.append(tdir) metric.setOptionalHisto2(plot) if 'reference' in config: reference_id, reference, reference_gui_url, reference_image_url = get_optional_me( row['eos_path'], get_all_me_names(config['reference'])) if not reference: print( 'Unable to get an optional reference monitor element: %s:%s' % (row['eos_path'], config['reference'])) move_to_second_queue(row['me_id'], row['id']) break plot, tdir = get_plot_from_blob(reference) tdirectories.append(tdir) metric.setReference(plot) if 'threshold' in config: metric.setThreshold(config['threshold']) # Get main plot blob from db main_me_blob, main_gui_url, main_image_url = get_me_blob_by_me_id( row['me_id']) if not main_me_blob: print('Unable to get me_blob %s from the DB.' % row['me_id']) move_to_second_queue(row['me_id'], row['id']) break main_plot, tdir = get_plot_from_blob(main_me_blob) tdirectories.append(tdir) # Get config id session = db_access.get_session() config_id = 0 try: config_id = session.execute( 'SELECT id FROM last_calculated_configs WHERE subsystem=:subsystem AND name=:name;', { 'subsystem': config['subsystem'], 'name': config['name'] }) config_id = list(config_id) config_id = config_id[0]['id'] except Exception as e: print('Unable to get config id from the DB: %s' % e) move_to_second_queue(row['me_id'], row['id']) break finally: session.close() # Calculate try: value, error = metric.calculate(main_plot) except Exception as e: print('Unable to calculate the metric: %s. %s' % (config['metric'], e)) move_to_second_queue(row['me_id'], row['id']) break # Write results to the DB historic_data_point = db_access.HistoricDataPoint( run=row['run'], lumi=row['lumi'], dataset=row['dataset'], subsystem=config['subsystem'], pd=row['dataset'].split('/')[1], processing_string=get_processing_string(row['dataset']), value=value, error=error, main_me_id=row['me_id'], optional_me1_id=histo1_id, optional_me2_id=histo2_id, reference_me_id=reference_id, config_id=config_id, name=config['name'], plot_title=config.get('plotTitle') or config['name'], y_title=config['yTitle'], main_me_path=config['relativePath'], optional1_me_path=config.get('histo1Path'), optional2_me_path=config.get('histo2Path'), reference_path=config.get('reference'), main_gui_url=main_gui_url, main_image_url=main_image_url, optional1_gui_url=histo1_gui_url, optional1_image_url=histo1_image_url, optional2_gui_url=histo2_gui_url, optional2_image_url=histo2_image_url, reference_gui_url=reference_gui_url, reference_image_url=reference_image_url, ) session = db_access.get_session() try: session.add(historic_data_point) session.execute( 'DELETE FROM queue_to_calculate WHERE id=:id;', {'id': row['id']}) session.execute( db_access.insert_or_ignore_crossdb( 'INSERT INTO selection_params (subsystem, pd, processing_string, config_id) VALUES (:subsystem, :pd, :ps, :config_id);' ), { 'subsystem': config['subsystem'], 'pd': historic_data_point.pd, 'ps': historic_data_point.processing_string, 'config_id': config_id }) session.commit() except IntegrityError as e: print('Insert HistoricDataPoint error: %s' % e) session.rollback() print('Updating...') try: historic_data_point_existing = session.query( db_access.HistoricDataPoint).filter( db_access.HistoricDataPoint.config_id == historic_data_point.config_id, db_access.HistoricDataPoint.main_me_id == historic_data_point.main_me_id, ).one_or_none() if historic_data_point_existing: historic_data_point_existing.run = historic_data_point.run historic_data_point_existing.lumi = historic_data_point.lumi historic_data_point_existing.dataset = historic_data_point.dataset historic_data_point_existing.subsystem = historic_data_point.subsystem historic_data_point_existing.pd = historic_data_point.pd historic_data_point_existing.processing_string = historic_data_point.processing_string historic_data_point_existing.value = historic_data_point.value historic_data_point_existing.error = historic_data_point.error historic_data_point_existing.optional_me1_id = historic_data_point.optional_me1_id historic_data_point_existing.optional_me2_id = historic_data_point.optional_me2_id historic_data_point_existing.reference_me_id = historic_data_point.reference_me_id historic_data_point_existing.name = historic_data_point.name historic_data_point_existing.plot_title = historic_data_point.plot_title historic_data_point_existing.y_title = historic_data_point.y_title historic_data_point_existing.main_me_path = historic_data_point.main_me_path historic_data_point_existing.optional1_me_path = historic_data_point.optional1_me_path historic_data_point_existing.optional2_me_path = historic_data_point.optional2_me_path historic_data_point_existing.reference_path = historic_data_point.reference_path historic_data_point_existing.main_gui_url = historic_data_point.main_gui_url historic_data_point_existing.main_image_url = historic_data_point.main_image_url historic_data_point_existing.optional1_gui_url = historic_data_point.optional1_gui_url historic_data_point_existing.optional1_image_url = historic_data_point.optional1_image_url historic_data_point_existing.optional2_gui_url = historic_data_point.optional2_gui_url historic_data_point_existing.optional2_image_url = historic_data_point.optional2_image_url historic_data_point_existing.reference_gui_url = historic_data_point.reference_gui_url historic_data_point_existing.reference_image_url = historic_data_point.reference_image_url session.execute( 'DELETE FROM queue_to_calculate WHERE id=:id;', {'id': row['id']}) session.commit() print('Updated.') except Exception as e: print('Update HistoricDataPoint error: %s' % e) session.rollback() move_to_second_queue(row['me_id'], row['id']) finally: session.close() except Exception as e: print('Exception calculating trend: %s' % e) move_to_second_queue(row['me_id'], row['id']) break finally: # Close all open TDirectories for tdirectory in tdirectories: if tdirectory: tdirectory.Close()
def data(): subsystem = request.args.get('subsystem') pd = request.args.get('pd') processing_string = request.args.get('processing_string') from_run = request.args.get('from_run', type=int) to_run = request.args.get('to_run', type=int) runs = request.args.get('runs') latest = request.args.get('latest', type=int) series = request.args.get('series') series_id = request.args.get('series_id', type=int) if series_id == None: if subsystem == None: return jsonify( {'message': 'Please provide a subsystem parameter.'}), 400 if pd == None: return jsonify({'message': 'Please provide a pd parameter.'}), 400 if processing_string == None: return jsonify( {'message': 'Please provide a processing_string parameter.'}), 400 modes = 0 if from_run != None and to_run != None: modes += 1 if latest != None: modes += 1 if runs != None: modes += 1 if modes > 1: return jsonify({ 'message': 'The combination of parameters you provided is invalid.' }), 400 if runs != None: try: runs = runs.split(',') runs = [int(x) for x in runs] except: return jsonify({ 'message': 'runs parameter is not valid. It has to be a comma separated list of integers.' }), 400 if series and series_id: return jsonify({ 'message': 'series and series_id can not be defined at the same time.' }), 400 db_access.setup_db() session = db_access.get_session() # Get series data by series_id if series_id: sql = ''' SELECT selection_params.subsystem, selection_params.pd, selection_params.processing_string, last_calculated_configs.name FROM selection_params JOIN last_calculated_configs ON config_id = last_calculated_configs.id WHERE selection_params.id = :id ; ''' rows = execute_with_retry(session, sql, {'id': series_id}) rows = list(rows) subsystem = rows[0]['subsystem'] pd = rows[0]['pd'] processing_string = rows[0]['processing_string'] series = rows[0]['name'] if runs == None: # Will need filtering runs_filter = '' latest_filter = '' if from_run != None and to_run != None: runs_filter = 'AND run >= %s AND run <= %s' % (from_run, to_run) else: if latest == None: latest = 50 latest_filter = 'LIMIT %s' % latest run_class_like = '%%collision%%' if 'cosmic' in pd.lower(): run_class_like = '%%cosmic%%' # UL2018 processing produced two processing strings: 12Nov2019_UL2018 and 12Nov2019_UL2018_rsb. # _rsb version is a resubmition because some runs were not processed (crashed?) in the initial processing. # Some runs might exist under both processing strings, some under just one of them! # A union of both of these processing strings contains all runs of UL2018. # So in HDQM, when 12Nov2019_UL2018 is requested, we need include 12Nov2019_UL2018_rsb as well!!! # This is a special case and is not used in any other occasion. processing_string_sql = 'AND processing_string=:processing_string' if processing_string == '12Nov2019_UL2018': processing_string_sql = 'AND (processing_string=:processing_string OR processing_string=:processing_string_rsb)' sql = ''' SELECT DISTINCT run FROM oms_data_cache WHERE run IN ( SELECT run FROM historic_data_points WHERE subsystem=:subsystem AND pd=:pd %s ) AND oms_data_cache.run_class %s :run_class AND oms_data_cache.significant=%s AND oms_data_cache.is_dcs=%s %s ORDER BY run DESC %s ; ''' % (processing_string_sql, db_access.ilike_crossdb(), db_access.true_crossdb(), db_access.true_crossdb(), runs_filter, latest_filter) print('Getting the list of runs...') start = timeit.default_timer() rows = execute_with_retry( session, sql, { 'subsystem': subsystem, 'pd': pd, 'processing_string': processing_string, 'processing_string_rsb': processing_string + '_rsb', 'run_class': run_class_like }) rows = list(rows) stop = timeit.default_timer() print('Runs retrieved in: ', stop - start) runs = [x[0] for x in rows] # Construct SQL query query_params = { 'subsystem': subsystem, 'pd': pd, 'processing_string': processing_string, 'processing_string_rsb': processing_string + '_rsb' } run_selection_sql = 'AND historic_data_points.run BETWEEN :from_run AND :to_run' if runs != None: run_selection_sql = 'AND historic_data_points.run IN (%s)' % ', '.join( str(x) for x in runs) query_params['runs'] = runs else: query_params['from_run'] = from_run query_params['to_run'] = to_run series_filter_sql = '' if series != None: series_filter_sql = 'AND historic_data_points.name IN (' series = series.split(',') for i in range(len(series)): key = 'series_%i' % i series_filter_sql += ':%s,' % key query_params[key] = series[i] series_filter_sql = series_filter_sql.rstrip(',') + ')' processing_string_sql = 'AND historic_data_points.processing_string=:processing_string' if processing_string == '12Nov2019_UL2018': processing_string_sql = 'AND (historic_data_points.processing_string=:processing_string OR historic_data_points.processing_string=:processing_string_rsb)' sql = ''' SELECT historic_data_points.id, historic_data_points.run, historic_data_points.value, historic_data_points.error, historic_data_points.name, historic_data_points.plot_title, historic_data_points.y_title FROM historic_data_points WHERE historic_data_points.subsystem=:subsystem AND historic_data_points.pd=:pd %s %s %s ORDER BY historic_data_points.run ASC ; ''' % (processing_string_sql, run_selection_sql, series_filter_sql) print('Getting the data...') start = timeit.default_timer() rows = execute_with_retry(session, sql, query_params) rows = list(rows) session.close() stop = timeit.default_timer() print('Data retrieved in: ', stop - start) result = {} for row in rows: # Names are unique within the subsystem key = '%s_%s' % (row['name'], subsystem) if key not in result: result[key] = { 'metadata': { 'y_title': row['y_title'], 'plot_title': row['plot_title'], 'name': row['name'], 'subsystem': subsystem, 'pd': pd, 'processing_string': processing_string, }, 'trends': [] } result[key]['trends'].append({ 'run': row['run'], 'value': row['value'], 'error': row['error'], 'id': row['id'], 'oms_info': {}, }) # Transform result to array result = [result[key] for key in sorted(result.keys())] result = add_oms_info_to_result(result) return jsonify(result)
def extract_all_mes(cfg_files, runs, nprocs, all_files): print('Processing %d configuration files...' % len(cfg_files)) mes_set = set() good_files = 0 for cfg_file in cfg_files: try: parser = RawConfigParser() parser.read(unicode(cfg_file)) for section in parser: if not section.startswith('plot:'): if section != 'DEFAULT': print('Invalid configuration section: %s:%s, skipping.' % (cfg_file, section)) continue if not PLOTNAMEPATTERN.match(section.lstrip('plot:')): print("Invalid plot name: '%s:%s' Plot names can contain only: [a-zA-Z0-9_+-]" % (cfg_file, section.lstrip('plot:'))) continue mes_set.update(get_all_me_names(parser[section]['relativePath'])) if 'histo1Path' in parser[section]: mes_set.update(get_all_me_names(parser[section]['histo1Path'])) if 'histo2Path' in parser[section]: mes_set.update(get_all_me_names(parser[section]['histo2Path'])) if 'reference' in parser[section]: mes_set.update(get_all_me_names(parser[section]['reference'])) good_files += 1 except: print('Could not read %s, skipping...' % cfg_file) print('Read %d configuration files.' % good_files) print('Read %d distinct ME paths.' % len(mes_set)) if not all_files: print('Listing files on EOS, this can take a while...') all_files = glob(ROOTFILES) if len(all_files) == 0: print('GLOB returned 0 files, probably EOS is down. Aborting.') return print('Done.') else: print('Using provided DQM files: %s' % len(all_files)) # Filter on the runs that were passed by the user if runs: filtered = [] for file in all_files: run_match = RUNPATTERN.findall(file) if not len(run_match) == 0: run = run_match[0] if int(run) in runs: filtered.append(file) all_files = filtered # Keep only the newest version of each file print('Removing old versions of files...') all_files = remove_old_versions(all_files) print('Found %s files in EOS' % len(all_files)) print('Gathering information about MEs to be extracted...') db_access.setup_db() # Get lists of existing mes and eos files. # Existing means that ME was extracted or is in the extraction queue. session = db_access.get_session() existing_me_paths = set(x.me_path for x in session.query(db_access.TrackedMEPathForMEExtraction).all()) existing_eos_paths = set(x.eos_path for x in session.query(db_access.TrackedEOSPathForMEExtraction).all()) session.close() # Single session (transaction) for queue manipulations session = db_access.get_session() # -------------------- Update the ME paths in the extraction queue -------------------- # new_mes = mes_set.difference(existing_me_paths) deleted_mes = existing_me_paths.difference(mes_set) print('New MEs: %s, deleted MEs: %s' % (len(new_mes), len(deleted_mes))) # Remove deleted MEs from the extraction queue if len(deleted_mes) > 0: sql = 'DELETE FROM queue_to_extract WHERE me_path = :me_path;' session.execute(sql, [{'me_path': x} for x in deleted_mes]) sql = 'DELETE FROM tracked_me_paths_for_me_extraction WHERE me_path = :me_path;' session.execute(sql, [{'me_path': x} for x in deleted_mes]) # Refresh new MEs table sql = 'DELETE FROM new_me_paths_for_me_extraction;' session.execute(sql) # Insert new ME paths if len(new_mes) > 0: sql = 'INSERT INTO new_me_paths_for_me_extraction (me_path) VALUES (:me_path);' session.execute(sql, [{'me_path': x} for x in new_mes]) # Will have to extract new MEs for every existing file sql_update_queue = ''' INSERT INTO queue_to_extract (eos_path, me_path) SELECT eos_path, me_path FROM tracked_eos_paths_for_me_extraction, new_me_paths_for_me_extraction ; ''' sql_update_existing = ''' INSERT INTO tracked_me_paths_for_me_extraction (me_path) SELECT me_path FROM new_me_paths_for_me_extraction ; ''' session.execute(sql_update_queue) session.execute(sql_update_existing) # -------------------- Update the eos paths in the extraction queue -------------------- # files_set = set(all_files) new_files = files_set.difference(existing_eos_paths) deleted_files = existing_eos_paths.difference(files_set) print('New files: %s, deleted files: %s' % (len(new_files), len(deleted_files))) # Remove deleted files from the extraction queue if len(deleted_files) > 0: sql = 'DELETE FROM queue_to_extract WHERE eos_path = :eos_path;' session.execute(sql, [{'eos_path': x} for x in deleted_files]) sql = 'DELETE FROM tracked_eos_paths_for_me_extraction WHERE eos_path = :eos_path;' session.execute(sql, [{'eos_path': x} for x in deleted_files]) # Refresh new files table sql = 'DELETE FROM new_eos_paths_for_me_extraction;' session.execute(sql) # Insert new eos paths if len(new_files) > 0: sql = 'INSERT INTO new_eos_paths_for_me_extraction (eos_path) VALUES (:eos_path);' session.execute(sql, [{'eos_path': x} for x in new_files]) # Will have to extract all existing MEs for newly added files sql_update_queue = ''' INSERT INTO queue_to_extract (eos_path, me_path) SELECT eos_path, me_path FROM new_eos_paths_for_me_extraction, tracked_me_paths_for_me_extraction ; ''' sql_update_existing = ''' INSERT INTO tracked_eos_paths_for_me_extraction (eos_path) SELECT eos_path FROM new_eos_paths_for_me_extraction ; ''' session.execute(sql_update_queue) session.execute(sql_update_existing) session.commit() session.close() print('Done.') print('Extracting missing MEs...') # ------------------- Start extracting MEs from the extraction queue ------------------- # sql = 'SELECT id, eos_path, me_path FROM queue_to_extract LIMIT 100000;' pool = Pool(nprocs) while True: db_access.dispose_engine() session = db_access.get_session() try: print('Fetching not processed MEs from DB...') rows = session.execute(sql) rows = list(rows) session.close() print('Fetched: %s' % len(rows)) if len(rows) == 0: break pool.map(extract_mes, batch_iterable(rows, chunksize=2000)) except OSError as e: if e.errno != errno.EINTR: raise else: print('[Errno 4] occurred. Continueing.') except Exception as e: print(e) session.close() print('Done.')
def extract_mes(rows): db_access.dispose_engine() tdirectory = None last_eos_path = None for row in rows: id = row['id'] eos_path = row['eos_path'] me_path = row['me_path'] pd_match = PDPATTERN.findall(eos_path) if len(pd_match) == 0: dataset = '' else: dataset = '/' + pd_match[0].replace('__', '/') filename = eos_path.split('/')[-1] run_match = RUNPATTERN.findall(filename) if not len(run_match) == 0: run = run_match[0] else: print('Skipping a malformatted DQM file that does not contain a run number: %s' % eos_path) exec_transaction('DELETE FROM queue_to_extract WHERE id = :id', {'id': id}) continue # Open root file only if it's different from the last one if eos_path != last_eos_path or tdirectory == None: if tdirectory != None: tdirectory.Close() tdirectory = ROOT.TFile.Open(eos_path) last_eos_path = eos_path if tdirectory == None: print("Unable to open file: '%s'" % eos_path) exec_transaction('DELETE FROM queue_to_extract WHERE id = :id', {'id': id}) continue fullpath = get_full_path(me_path, run) plot = tdirectory.Get(fullpath) if not plot: exec_transaction('DELETE FROM queue_to_extract WHERE id = :id', {'id': id}) continue plot_folder = '/'.join(me_path.split('/')[:-1]) gui_url = '%sstart?runnr=%s;dataset=%s;workspace=Everything;root=%s;focus=%s;zoom=yes;' % (DQMGUI, run, dataset, plot_folder, me_path) image_url = '%splotfairy/archive/%s%s/%s?v=1510330581101995531;w=1906;h=933' % (DQMGUI, run, dataset, me_path) monitor_element = db_access.MonitorElement( run = run, lumi = 0, eos_path = eos_path, me_path = me_path, dataset = dataset, gui_url = gui_url, image_url = image_url) session = db_access.get_session() try: me_blob = db_access.MeBlob( me_blob = get_binary(plot) ) session.add(me_blob) session.flush() monitor_element.me_blob_id = me_blob.id session.add(monitor_element) session.flush() session.execute('DELETE FROM queue_to_extract WHERE id = :id;', {'id': id}) session.execute('INSERT INTO queue_to_calculate (me_id) VALUES (:me_id);', {'me_id': monitor_element.id}) session.commit() print('Added ME %s to DB: %s:%s' % (monitor_element.id, eos_path, me_path)) except IntegrityError as e: print('Insert ME IntegrityError: %s' % e) # ME already exists. Remove it from the queue_to_extract and add to queue_to_calculate # because it is possible that it wasn't calculated yet session.rollback() monitor_element_id = monitor_element.id session.execute('DELETE FROM queue_to_extract WHERE id = :id;', {'id': id}) if monitor_element_id == None: res = session.execute('SELECT id FROM monitor_elements WHERE eos_path=:eos_path AND me_path=:me_path;', {'eos_path': eos_path, 'me_path': me_path}) monitor_element_id = list(res)[0][0] session.execute('INSERT INTO queue_to_calculate (me_id) VALUES (:me_id);', {'me_id': monitor_element_id}) session.commit() except Exception as e: print('Insert ME error: %s' % e) session.rollback() finally: session.close() if tdirectory: tdirectory.Close()