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