def index(page, run_from, run_to): start_time_stamp = int(time() * 1000) preparation_sw = StopWatchTimer() preparation_sw.start() condition_types = g.tdb.get_condition_types() query = g.tdb.session.query(Run) # Run range is defined? if run_from != -1 and run_to != -1: # Check what is max/min run run_min, run_max = run_from, run_to if run_max < run_min: run_min, run_max = run_max, run_min # Filter query and count results query = query.filter(Run.number >= run_min, Run.number <= run_max) count = g.tdb.session.query(func.count(Run.number)).filter(Run.number >= run_min, Run.number <= run_max).scalar() # we don't want pagination in this case, setting page size same/bigger than count per_page = run_max - run_min else: count = g.tdb.session.query(func.count(Run.number)).scalar() per_page = PER_PAGE # Create pagination pagination = Pagination(page, per_page, count) preparation_sw.stop() query_sw = StopWatchTimer() query_sw.start() # Get runs from query runs = query.options(subqueryload(Run.conditions))\ .order_by(Run.number.desc())\ .slice(pagination.item_limit_from, pagination.item_limit_to)\ .all() query_sw.stop() performance = { "preparation": preparation_sw.elapsed, "query": query_sw.elapsed, "selection": 0.0, "start_time_stamp": start_time_stamp, } return render_template("runs/index.html", runs=runs, DefaultConditions=DefaultConditions, pagination=pagination, condition_types=condition_types, run_from=-1, run_to=-1, search_query="", performance=performance)
def index(page, run_from, run_to): start_time_stamp = int(time() * 1000) preparation_sw = StopWatchTimer() condition_types = g.tdb.get_condition_types() query = g.tdb.session.query(Run) # Run range is defined? if run_from != -1 and run_to != -1: # Check what is max/min run run_min, run_max = run_from, run_to if run_max < run_min: run_min, run_max = run_max, run_min # Filter query and count results query = query.filter(Run.number >= run_min, Run.number <= run_max) count = g.tdb.session.query(func.count(Run.number)).filter( Run.number >= run_min, Run.number <= run_max).scalar() # we don't want pagination in this case, setting page size same/bigger than count per_page = run_max - run_min else: count = g.tdb.session.query(func.count(Run.number)).scalar() per_page = PER_PAGE # Create pagination pagination = Pagination(page, per_page, count) preparation_sw.stop() query_sw = StopWatchTimer() # Get runs from query runs = query.options(subqueryload(Run.conditions))\ .order_by(Run.number.desc())\ .slice(pagination.item_limit_from, pagination.item_limit_to)\ .all() query_sw.stop() performance = { "preparation": preparation_sw.elapsed, "query": query_sw.elapsed, "selection": 0.0, "start_time_stamp": start_time_stamp, } return render_template("runs/index.html", runs=runs, DefaultConditions=DefaultConditions, pagination=pagination, condition_types=condition_types, run_from=-1, run_to=-1, search_query="", performance=performance)
from sqlalchemy.orm import aliased if __name__ == "__main__": print sys.argv # Get connection string from arguments parser = argparse.ArgumentParser(description="This example shows basics of how to select runs using search queries") parser.add_argument("connection_string", help="RCDB connection string mysql://rcdb@localhost/rcdb") parser.add_argument('target_cnd_names', nargs='*') args = parser.parse_args() print "Conditions:", args.target_cnd_names # Open DB connection db = RCDBProvider(args.connection_string) sw = StopWatchTimer() sw.start() # Select production runs with event_count > 0.5M result = db.select_runs("event_count > 100", 0, 20000) #result.runs = db.get_runs(0, 20000) sw.stop() print "Selection took ", sw.elapsed print("Selected {} runs".format(len(result.runs))) print result.get_values(args.target_cnd_names, insert_run_number=True)
def get_values(self, condition_names, insert_run_number=False): if self.db is None or not self.runs: return [[]] # Condition names... are just one name? if isinstance(condition_names, basestring): condition_names = [condition_names] # No condition names? if not condition_names: if insert_run_number: return [[run.number] for run in self.runs] else: # noinspection PyUnusedLocal return [[] for run in self.runs] target_cnd_names = condition_names sw = StopWatchTimer() sw.start() all_cnt_types = self.db.get_condition_types() all_cnd_types_by_name = {cnd.name: cnd for cnd in all_cnt_types} # getting target conditions types and sorting them by id target_cnd_types = [all_cnd_types_by_name[cnd_name] for cnd_name in target_cnd_names] ct_id_to_user_defined_order = {} for cnd_type in target_cnd_types: for i in range(0, len(condition_names)): if cnd_type.name == condition_names[i]: ct_id_to_user_defined_order[cnd_type.id] = i break target_cnd_types = sorted(target_cnd_types, key=lambda x: x.id) target_cnd_types_len = len(target_cnd_types) ids = [ct.id for ct in target_cnd_types] runs_asc = self.runs if not self.sort_desc else list(reversed(self.runs)) run_numbers = [r.number for r in runs_asc] query = self.db.session.query(Condition) \ .filter(Condition.condition_type_id.in_(ids), Condition.run_number.in_(run_numbers)) \ .order_by(Condition.run_number, Condition.condition_type_id) conditions = query.all() # performance measurement sw.stop() self.performance["get_conditions"] = sw.elapsed sw = StopWatchTimer() sw.start() # create empty rows rows = [] def get_empty_row(run_number=0): """Creates empty rows properly :param run_number: run number to add """ if insert_run_number: # noinspection PyTypeChecker return [run_number] + ([None] * target_cnd_types_len) else: return [None] * target_cnd_types_len type_index = 0 prev_run = conditions[0].run_number row = get_empty_row(runs_asc[0].number) run_index = 0 while runs_asc[run_index].number != prev_run: rows.append(row) run_index += 1 row = get_empty_row(runs_asc[run_index].number) for condition in conditions: assert isinstance(condition, Condition) type_id = condition.condition_type_id if condition.run_number != prev_run: prev_run = condition.run_number while runs_asc[run_index].number != prev_run: rows.append(row) run_index += 1 row = get_empty_row(runs_asc[run_index].number) type_index = 0 while type_index < target_cnd_types_len and type_id != target_cnd_types[type_index].id: type_index += 1 if type_index == target_cnd_types_len: type_index = 0 cell_index = ct_id_to_user_defined_order[target_cnd_types[type_index].id] if insert_run_number: row[cell_index + 1] = condition.value else: row[cell_index] = condition.value # We have always have to rows.append(row) run_index += 1 # It may happen that we run out of selected conditions, because condition is not set for all runs after # for example we have run=> condition 1=>x, 2=>y, 3=>None, 4=>None. DB will select only x and y conditions # and 2 rows [[1],[2]], while it should [[1],[2],[None],[None]]. So we have to put missing rows in the end while run_index < len(runs_asc): rows.append(get_empty_row(runs_asc[run_index].number)) run_index += 1 # performance measure sw.stop() self.performance["tabling_values"] = sw.elapsed if self.sort_desc: rows.reverse() return rows
def select_runs(self, search_str="", run_min=0, run_max=sys.maxsize, sort_desc=False): """ Searches RCDB for runs with e :param sort_desc: if True result runs will by sorted descendant by run_number, ascendant if False :param run_min: minimum run to search :param run_max: maximum run to search :param search_str: Search pattern :type search_str: str :return: List of runs matching criteria :rtype: list(Run) """ start_time_stamp = int(mktime(datetime.datetime.now().timetuple()) * 1000) preparation_sw = StopWatchTimer() preparation_sw.start() if run_min > run_max: run_min, run_max = run_max, run_min # PHASE 0 - Maybe there is no query?! if not search_str or not search_str.strip(): # If no query, just use get_runs function and return the result preparation_sw.stop() query_sw = StopWatchTimer() query_sw.start() sel_runs = self.get_runs(run_min, run_max, sort_desc) query_sw.stop() result = RunSelectionResult(sel_runs, self) result.sort_desc = sort_desc result.filter_condition_names = [] result.filter_condition_types = [] result.performance["preparation"] = preparation_sw.elapsed result.performance["query"] = query_sw.elapsed result.performance["selection"] = 0 result.performance["start_time_stamp"] = start_time_stamp return result # get all condition types all_cnt_types = self.get_condition_types() all_cnd_types_by_name = {cnd.name: cnd for cnd in all_cnt_types} all_cnd_names = [str(key) for key in all_cnd_types_by_name.keys()] # PHASE 1: getting what to search from search_str search_str = str(search_str) if '__' in search_str: raise QueryFormatError("Query contains restricted symbol: '__'") for alias in self.aliases: al_name = "@" + alias.name if al_name in search_str: search_str = search_str.replace(al_name, '(' + alias.expression + ')') search_str = search_str.replace('\n', ' ') search_str = search_str.replace('\r', ' ') tokens = [token for token in lexer.tokenize(search_str)] target_cnd_types = [] names = [] aliased_cnd_types = [] aliased_cnd = [] names_count = 0 for token in tokens: if token.type in lexer.rcdb_query_restricted: raise QueryFormatError("Query contains restricted symbol: '{}'".format(token.value)) if token.type != "NAME": continue if token.value == 'math': continue if token.value == 'startswith': continue if token.value not in all_cnd_names: message = "Name '{}' is not found in ConditionTypes".format(token.value) raise QueryFormatError(message) else: cnd_name = token.value cnd_type = all_cnd_types_by_name[token.value] target_cnd_types.append(cnd_type) token.value = "value[{}].value".format(names_count) names_count += 1 names.append(cnd_name) aliased_cnd.append(aliased(Condition)) aliased_cnd_types.append(aliased(ConditionType)) # PHASE 2: Database query query = self.session.query() if not names_count: return None search_eval = " ".join([token.value for token in tokens if isinstance(token, LexToken)]) for (i, alias_cnd) in enumerate(aliased_cnd): query = query.add_entity(alias_cnd).filter(alias_cnd.condition_type_id == target_cnd_types[i].id) if i != 0: query = query.filter(alias_cnd.run_number == aliased_cnd[0].run_number) query = query.filter(aliased_cnd[0].run_number >= run_min, aliased_cnd[0].run_number <= run_max) \ .join(aliased_cnd[0].run) # apply sorting if not sort_desc: query = query.order_by(aliased_cnd[0].run_number) else: query = query.order_by(desc(aliased_cnd[0].run_number)) preparation_sw.stop() query_sw = StopWatchTimer() query_sw.start() values = query.all() query_sw.stop() selection_sw = StopWatchTimer() selection_sw.start() # PHASE 3: Selecting runs compiled_search_eval = compile(search_eval, '<string>', 'eval') sel_runs = [] for value in values: if isinstance(value, Condition): value = (value,) run = value[0].run if eval(compiled_search_eval): sel_runs.append(run) selection_sw.stop() result = RunSelectionResult(sel_runs, self) result.filter_condition_names = names result.filter_condition_types = target_cnd_types result.sort_desc = sort_desc result.performance["preparation"] = preparation_sw.elapsed result.performance["query"] = query_sw.elapsed result.performance["selection"] = selection_sw.elapsed result.performance["start_time_stamp"] = start_time_stamp return result
def print_run_selector(records, options, row): months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ] rcdb_inelig = [ 'detcom_02', 'RunPeriod-2014-10', 'RunPeriod-2015-03', 'RunPeriod-2015-06', 'RunPeriod-2015-12' ] print "<ul id=\"runList\">" dates = get_dates(options) query_sw = StopWatchTimer() if options[2] in rcdb_inelig or options[2] == "RunPeriod-2017-01": rcdb_runs = db.select_runs("@is_production", 30000, 39000) else: rcdb_runs = db.select_runs("@status_approved and @is_production", 30000, 39000) rcdb_run_numbers = [run.number for run in rcdb_runs] query_sw.stop() print("QUERY TOOK {}".format(query_sw.elapsed)) print("===========================================") if "mc_ver01" in options: newText = "MC" print( "<label><input type=\"radio\" id=\"run_number\" name=\"run_number\" value=\"%s\" onclick=\"changeRun(%s,%s);\"> %s</label>" % (10000, 10000, "MC_run", 10000)) # print ("<a href=\"/cgi-bin/data_monitoring/monitoring/browseJSRoot.py?run_number=%s&ver=%s&period=%s\" target=\"_blank\"><button type=\"button\"> ROOT </button></a>" % (row[0], options[1], options[2])) # print ("<a href=\"https://halldweb.jlab.org/rcdb/runs/info/%s\" target=\"_blank\"><button type=\"button\"> RCDB </button></a>" % (row[0])) return # print dates for date in dates: if date[0] == None: continue # format date (must be a better way) fulldate = str(date[0]) month = fulldate[5:7] day = fulldate[8:10] namedate = "%s %s" % (months[int(month) - 1], day) # get run range minRun = 9e9 maxRun = 0 rest_sw = StopWatchTimer() for row in records: if row[1] == None or row[1] == '0': continue if "recon" in options[1] and row[0] not in rcdb_run_numbers: continue rundate_obj = None try: rundate_obj = datetime.datetime.strptime( row[1], "%Y-%m-%d %H:%M:%S") except ValueError: pass if rundate_obj == None: continue # print rundate_obj try: rundate = rundate_obj.strftime("%Y-%m-%d") except ValueError: pass # print rundate if rundate == fulldate: if row[0] < minRun: minRun = row[0] if row[0] > maxRun: maxRun = row[0] rest_sw.stop() print("RECORD TOOK {}".format(rest_sw.elapsed)) print("===========================================") if minRun != 9e9 and maxRun != 0: print "<li>" print "<b>%s</b> (Run %s-%s)" % (namedate, minRun, maxRun) print "<ul>" # print runs for given date for row in records: if row[1] == None or row[1] == '0': continue if "recon" in options[1] and row[0] not in rcdb_run_numbers: continue rundate_obj = None try: rundate_obj = datetime.datetime.strptime( row[1], "%Y-%m-%d %H:%M:%S") except ValueError: pass if rundate_obj == None: continue # print rundate_obj try: rundate = rundate_obj.strftime("%Y-%m-%d") except ValueError: pass # print rundate numevents = row[2] if rundate == fulldate: if options[2] not in rcdb_inelig: if db.get_condition(row[0], "event_count"): numevents = db.get_condition( row[0], "event_count").value print "<li>" # print records print( "<label><input type=\"radio\" id=\"run_number\" name=\"run_number\" value=\"%s\" onclick=\"changeRun(%s,%s);changePath();showPlot()\"> %s (%s eve)</label>" % (row[0], row[0], row[0], row[0], numevents)) print( "<a href=\"/cgi-bin/data_monitoring/monitoring/browseJSRoot.py?run_number=%s&ver=%s&period=%s\" target=\"_blank\"><button type=\"button\"> ROOT </button></a>" % (row[0], options[1], options[2])) print( "<a href=\"https://halldweb.jlab.org/rcdb/runs/info/%s\" target=\"_blank\"><button type=\"button\"> RCDB </button></a>" % (row[0])) print "</li>" print "</ul>" print "</li>" print "</ul>" print "<script type=\"text/javascript\">make_tree_menu('runList');</script>"
# then create your loggers as always logger_a_b = logging.getLogger("a.b") logger_a_b_c = logging.getLogger("a.b.c") logger_a_b_c_d = logging.getLogger("a.b.c.d") logger_a.debug("One") logger_a_b.warning("two") logger_a_b.warning("three") logger_a_b_c.critical("Four") logger_a_b.warning("Five") logger_a.warning("Six") logger_a_b_c_d.warning("absd") sw = StopWatchTimer() runs = db.select_runs("@is_production", 0, 20000) sw.stop() print(sw.elapsed, len(runs)) sw = StopWatchTimer() runs = db.select_runs("@is_cosmic", 0, 20000) sw.stop() print(sw.elapsed, len(runs)) sw = StopWatchTimer() runs = db.select_runs("@is_production", 0, 20000) sw.stop() print(sw.elapsed, len(runs))