def get_data( conn: ConnectionPlus, table_name: str, columns: List[str], start: Optional[int] = None, end: Optional[int] = None, ) -> List[List[Any]]: """ Get data from the columns of a table. Allows to specify a range of rows (1-based indexing, both ends are included). Args: conn: database connection table_name: name of the table columns: list of columns start: start of range; if None, then starts from the top of the table end: end of range; if None, then ends at the bottom of the table Returns: the data requested in the format of list of rows of values """ if len(columns) == 0: warnings.warn( 'get_data: requested data without specifying parameters/columns.' 'Returning empty list.') return [[]] query = _build_data_query(table_name, columns, start, end) c = atomic_transaction(conn, query) res = many_many(c, *columns) return res
def get_parameters(conn: ConnectionPlus, run_id: int) -> List[ParamSpec]: """ Get the list of param specs for run Args: conn: the connection to the sqlite database run_id: The id of the run Returns: A list of param specs for this run """ sql = f""" SELECT parameter FROM layouts WHERE run_id={run_id} """ c = conn.execute(sql) param_names_temp = many_many(c, 'parameter') param_names = [p[0] for p in param_names_temp] param_names = cast(List[str], param_names) parspecs = [] for param_name in param_names: parspecs.append(get_paramspec(conn, run_id, param_name)) return parspecs
def get_dataIDs( db_name: str, stage: str, db_folder: Optional[str] = None, quality: Optional[int] = None, ) -> List[int]: """""" if db_name[-2:] != "db": db_name += ".db" if db_folder is None: db_folder = nt.config["db_folder"] db_path = os.path.join(db_folder, db_name) conn = connect(db_path) if quality is None: sql = f""" SELECT run_id FROM runs WHERE {stage}={1} OR {stage} LIKE {str(1)} """ else: sql = f""" SELECT run_id FROM runs WHERE ({stage}={1} OR {stage} LIKE {str(1)}) AND (good={quality} OR good LIKE {str(quality)}) """ c = conn.execute(sql) param_names_temp = many_many(c, "run_id") return list(flatten_list(param_names_temp))
def get_dependents(conn: ConnectionPlus, run_id: int) -> List[int]: """ Get dependent layout_ids for a certain run_id, i.e. the layout_ids of all the dependent variables """ sql = """ SELECT layout_id FROM layouts WHERE run_id=? and layout_id in (SELECT dependent FROM dependencies) """ c = atomic_transaction(conn, sql, run_id) res = [d[0] for d in many_many(c, 'layout_id')] return res
def get_dependencies(conn: ConnectionPlus, layout_id: int) -> List[List[int]]: """ Get the dependencies of a certain dependent variable (indexed by its layout_id) Args: conn: connection to the database layout_id: the layout_id of the dependent variable """ sql = """ SELECT independent, axis_num FROM dependencies WHERE dependent=? """ c = atomic_transaction(conn, sql, layout_id) res = many_many(c, 'independent', 'axis_num') return res
def perform_db_upgrade_0_to_1(conn: ConnectionPlus) -> None: """ Perform the upgrade from version 0 to version 1 Add a GUID column to the runs table and assign guids for all existing runs """ sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='runs'" cur = atomic_transaction(conn, sql) n_run_tables = len(cur.fetchall()) if n_run_tables == 1: with atomic(conn) as conn: sql = "ALTER TABLE runs ADD COLUMN guid TEXT" transaction(conn, sql) # now assign GUIDs to existing runs cur = transaction(conn, 'SELECT run_id FROM runs') run_ids = [r[0] for r in many_many(cur, 'run_id')] pbar = tqdm(range(1, len(run_ids) + 1), file=sys.stdout) pbar.set_description("Upgrading database; v0 -> v1") for run_id in pbar: query = f""" SELECT run_timestamp FROM runs WHERE run_id == {run_id} """ cur = transaction(conn, query) timestamp = one(cur, 'run_timestamp') timeint = int(np.round(timestamp * 1000)) sql = f""" UPDATE runs SET guid = ? where run_id == {run_id} """ sampleint = 3736062718 # 'deafcafe' cur.execute( sql, (generate_guid(timeint=timeint, sampleint=sampleint), )) else: raise RuntimeError(f"found {n_run_tables} runs tables expected 1")
def get_unlabelled_ids( db_name: str, db_folder: Optional[str] = None, ) -> List[int]: """""" if db_name[-2:] != "db": db_name += ".db" if db_folder is None: db_folder = nt.config["db_folder"] db_path = os.path.join(db_folder, db_name) conn = connect(db_path) sql = f""" SELECT run_id FROM runs WHERE good IS NULL """ c = conn.execute(sql) param_names_temp = many_many(c, "run_id") return list(flatten_list(param_names_temp))
def get_values(conn: ConnectionPlus, table_name: str, param_name: str) -> List[List[Any]]: """ Get the not-null values of a parameter Args: conn: Connection to the database table_name: Name of the table that holds the data param_name: Name of the parameter to get the setpoints of Returns: The values """ sql = f""" SELECT {param_name} FROM "{table_name}" WHERE {param_name} IS NOT NULL """ c = atomic_transaction(conn, sql) res = many_many(c, param_name) return res
def get_setpoints(conn: ConnectionPlus, table_name: str, param_name: str) -> Dict[str, List[List[Any]]]: """ Get the setpoints for a given dependent parameter Args: conn: Connection to the database table_name: Name of the table that holds the data param_name: Name of the parameter to get the setpoints of Returns: A list of returned setpoint values. Each setpoint return value is a list of lists of Any. The first list is a list of run points, the second list is a list of parameter values. """ # TODO: We do this in no less than 5 table lookups, surely # this number can be reduced # get run_id sql = """ SELECT run_id FROM runs WHERE result_table_name = ? """ c = atomic_transaction(conn, sql, table_name) run_id = one(c, 'run_id') # get the parameter layout id sql = """ SELECT layout_id FROM layouts WHERE parameter = ? and run_id = ? """ c = atomic_transaction(conn, sql, param_name, run_id) layout_id = one(c, 'layout_id') # get the setpoint layout ids sql = """ SELECT independent FROM dependencies WHERE dependent = ? """ c = atomic_transaction(conn, sql, layout_id) indeps = many_many(c, 'independent') indeps = [idp[0] for idp in indeps] # get the setpoint names sql = f""" SELECT parameter FROM layouts WHERE layout_id IN {str(indeps).replace('[', '(').replace(']', ')')} """ c = atomic_transaction(conn, sql) setpoint_names_temp = many_many(c, 'parameter') setpoint_names = [spn[0] for spn in setpoint_names_temp] setpoint_names = cast(List[str], setpoint_names) # get the actual setpoint data output: Dict[str, List[List[Any]]] = {} for sp_name in setpoint_names: sql = f""" SELECT {sp_name} FROM "{table_name}" WHERE {param_name} IS NOT NULL """ c = atomic_transaction(conn, sql) sps = many_many(c, sp_name) output[sp_name] = sps return output
def get_parameter_tree_values(conn: ConnectionPlus, result_table_name: str, toplevel_param_name: str, *other_param_names, start: Optional[int] = None, end: Optional[int] = None) -> List[List[Any]]: """ Get the values of one or more columns from a data table. The rows retrieved are the rows where the 'toplevel_param_name' column has non-NULL values, which is useful when retrieving a top level parameter and its setpoints (and inferred_from parameter values) Args: conn: Connection to the DB file result_table_name: The result table whence the values are to be retrieved toplevel_param_name: Name of the column that holds the top level parameter other_param_names: Names of additional columns to retrieve start: The (1-indexed) result to include as the first results to be returned. None is equivalent to 1. If start > end, nothing is returned. end: The (1-indexed) result to include as the last result to be returned. None is equivalent to "all the rest". If start > end, nothing is returned. Returns: A list of list. The outer list index is row number, the inner list index is parameter value (first toplevel_param, then other_param_names) """ offset = (start - 1) if start is not None else 0 limit = (end - offset) if end is not None else -1 if start is not None and end is not None and start > end: limit = 0 # Note: if we use placeholders for the SELECT part, then we get rows # back that have "?" as all their keys, making further data extraction # impossible # # Also, placeholders seem to be ignored in the WHERE X IS NOT NULL line columns = [toplevel_param_name] + list(other_param_names) columns_for_select = ','.join(columns) sql_subquery = f""" (SELECT {columns_for_select} FROM "{result_table_name}" WHERE {toplevel_param_name} IS NOT NULL) """ sql = f""" SELECT {columns_for_select} FROM {sql_subquery} LIMIT {limit} OFFSET {offset} """ cursor = conn.cursor() cursor.execute(sql, ()) res = many_many(cursor, *columns) return res